initializeDatabase();
}
@Transactional(readOnly = true)
public void doQuery(QueryType type) {
SampleDB db = getDB();
Connection conn = getConnection();
// Define the query
DBCommand cmd = db.createCommand();
// Define shortcuts for tables used - not necessary but convenient
SampleDB.Employees EMP = db.EMPLOYEES;
SampleDB.Departments DEP = db.DEPARTMENTS;
// The following expression concats lastname + ', ' + firstname
DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.append(", ").append(EMP.FIRSTNAME).as("FULL_NAME");
// The following expression extracts the extension number from the phone field
// e.g. substr(PHONE_NUMBER, length(PHONE_NUMBER)-instr(reverse(PHONE_NUMBER), '-')+2) AS PHONE_EXTENSION
// Hint: Since the reverse() function is not supported by HSQLDB there is special treatment for HSQL
DBColumnExpr PHONE_LAST_DASH;
if ( db.getDriver() instanceof DBDatabaseDriverHSql
|| db.getDriver() instanceof DBDatabaseDriverDerby
|| db.getDriver() instanceof DBDatabaseDriverH2)
PHONE_LAST_DASH = EMP.PHONE_NUMBER.indexOf("-", EMP.PHONE_NUMBER.indexOf("-").plus(1)).plus(1); // HSQLDB only
else PHONE_LAST_DASH = EMP.PHONE_NUMBER.length().minus(EMP.PHONE_NUMBER.reverse().indexOf("-")).plus(2);
DBColumnExpr PHONE_EXT_NUMBER = EMP.PHONE_NUMBER.substring(PHONE_LAST_DASH).as("PHONE_EXTENSION");
// DBColumnExpr genderExpr = cmd.select(EMP.GENDER.decode(EMP.GENDER.getOptions()).as(EMP.GENDER.getName()));
// Select required columns
cmd.select(EMP.EMPLOYEE_ID, EMPLOYEE_FULLNAME);
if(db.getDriver() instanceof DBDatabaseDriverPostgreSQL)
{
// postgres does not support the substring expression
cmd.select(EMP.GENDER, EMP.PHONE_NUMBER);
}else{
cmd.select(EMP.GENDER, EMP.PHONE_NUMBER, PHONE_EXT_NUMBER);