* </PRE>
*/
private static void queryRecords(Connection conn, QueryType queryType)
{
// 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);
}
cmd.select(DEP.NAME.as("DEPARTMENT"));
cmd.select(DEP.BUSINESS_UNIT);
cmd.join(EMP.DEPARTMENT_ID, DEP.DEPARTMENT_ID);
// Set constraints and order
cmd.where(EMP.LASTNAME.length().isGreaterThan(0));
cmd.orderBy(EMP.LASTNAME, EMP.FIRSTNAME);
/*
// Example for limitRows() and skipRows()
if (db.getDriver().isSupported(DBDriverFeature.QUERY_LIMIT_ROWS))
{ // set maximum number of rows
cmd.limitRows(20);
if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS))
cmd.skipRows(1);
}
*/
// Query Records and print output
DBReader reader = new DBReader();
try
{
// Open Reader
System.out.println("Running Query:");
System.out.println(cmd.getSelect());
reader.open(cmd, conn);
// Print output
System.out.println("---------------------------------");
switch(queryType)
{