/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.empire.samples.db;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.DriverManager;
import java.time.LocalDate;
import java.util.List;

import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.bean.BeanResult;
import org.apache.empire.data.list.DataListEntry;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBContext;
import org.apache.empire.db.DBQuery;
import org.apache.empire.db.DBReader;
import org.apache.empire.db.DBRecord;
import org.apache.empire.db.DBRowSet.PartialMode;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.context.DBContextStatic;
import org.apache.empire.db.validation.DBModelChecker;
import org.apache.empire.db.validation.DBModelErrorLogger;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.dbms.derby.DBMSHandlerDerby;
import org.apache.empire.dbms.h2.DBMSHandlerH2;
import org.apache.empire.dbms.hsql.DBMSHandlerHSql;
import org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL;
import org.apache.empire.samples.db.SampleDB.Gender;
import org.apache.empire.xml.XMLWriter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;


public class SampleApp 
{
    // Logger
    private static final Logger log = LoggerFactory.getLogger(SampleApp.class);

	private static final SampleDB db = new SampleDB();

    private static SampleConfig config = new SampleConfig();
	
	private static DBContext context;

	private enum QueryType
    {
	    Reader,
        BeanList,
	    XmlDocument
	}

	/**
     * <PRE>
	 * This is the entry point of the Empire-DB Sample Application
	 * Please check the config.xml configuration file for Database and Connection settings.
     * </PRE>
	 * @param args arguments
	 */
	public static void main(String[] args)
    {
		try
        {
			// Init Configuration
			config.init((args.length > 0 ? args[0] : "config.xml" ));

			log.info("Running DB Sample...");

			// STEP 1: Get a JDBC Connection
			log.info("Step 1: getJDBCConnection()");
			
			Connection conn = getJDBCConnection();

			// STEP 2: Choose a DBMSHandler
			log.info("Step 2: getDatabaseProvider()");
			DBMSHandler dbms = getDBMSHandler(config.getDatabaseProvider(), conn);
			
            // STEP 2.2: Create a Context
			context = new DBContextStatic(dbms, conn, false, true); 

            // STEP 3: Open Database (and create if not existing)
            log.info("Step 3: openDatabase()");
			try {
			    // Open the database
                db.open(context);
                // Check whether database exists
			    databaseExists();
                log.info("Database already exists. Checking data model...");
                checkDataModel();
                
			} catch(Exception e) {
                // STEP 4: Create Database
                log.info("Step 4: createDDL()");
                // postgre does not support DDL in transaction
                if(db.getDbms() instanceof DBMSHandlerPostgreSQL)
                {
                	conn.setAutoCommit(true);
                }
                createDatabase();
                if(db.getDbms() instanceof DBMSHandlerPostgreSQL)
                {
                	conn.setAutoCommit(false);
                }
                // Open again
                if (db.isOpen()==false)
                    db.open(context);
			}

			// STEP 5: Clear Database (Delete all records)
			log.info("Step 5: clearDatabase()");
			clearDatabase();

			// STEP 6: Insert Departments
			log.info("Step 6: insertDepartment() & insertEmployee()");
			int idDevDep = insertDepartment("Development", "ITTK");
			int idSalDep = insertDepartment("Sales", "ITTK");
			// Insert Employees
			int idEmp1 = insertEmployee("Peter", "Sharp", Gender.M, idDevDep);
			int idEmp2 = insertEmployee("Fred", "Bloggs", Gender.M, idDevDep);
			int idEmp3 = insertEmployee("Emma", "White",  Gender.F, idSalDep);
            // Insert Payments
			insertPayments(idEmp1, new BigDecimal(2000));
			insertPayments(idEmp3, new BigDecimal(2500));

			// commit
			context.commit();
			
			/*
            int idEmp = testTransactionCreate(idDevDep);
            testTransactionUpdate(idEmp);
            testTransactionDelete(idEmp);
            */

			// STEP 7: Update Records (by setting the phone Number)
			log.info("Step 7: updateEmployee()");
			updateEmployee(idEmp1, "+49-7531-457160");
			updateEmployee(idEmp2, "+49-5555-505050");
			// Partial Record
            updatePartialRecord(idEmp3, "+49-040-125486");
			// Update Joined Records (Make Fred Bloggs head of department and set salary)
			updateJoinedRecords(idEmp2, 100000);

			// commit
			context.commit();

			// STEP 8: Option 1: Query Records and print tab-separated
			log.info("Step 8 Option 1: queryRecords() / Tab-Output");
			queryRecords(QueryType.Reader); // Tab-Output

            // STEP 8: Option 2: Query Records as a list of java beans
            log.info("Step 8 Option 2: queryRecords() / Bean-List-Output");
            queryRecords(QueryType.BeanList); // Bean-List-Output

			// STEP 8: Option 3: Query Records as XML
			log.info("Step 8 Option 3: queryRecords() / XML-Output");
			queryRecords(QueryType.XmlDocument); // XML-Output

			// STEP 9: Use Bean Result to query beans
			queryBeans(conn);
			
			// Done
			log.info("DB Sample finished successfully.");

		} catch (Exception e)
        {	// Error
			log.error("Running SampleApp failed with Exception" + e.toString(), e);
			
		} finally {
		    context.discard();
		}
	}

	/**
     * <PRE>
	 * Opens and returns a JDBC-Connection.
	 * JDBC url, user and password for the connection are obtained from the SampleConfig bean
	 * Please use the config.xml file to change connection params.
     * </PRE>
	 */
	private static Connection getJDBCConnection()
    {
		// Establish a new database connection
		Connection conn = null;
		log.info("Connecting to Database'" + config.getJdbcURL() + "' / User=" + config.getJdbcUser());
		try
        {
            // Connect to the database
			Class.forName(config.getJdbcClass()).newInstance();
			conn = DriverManager.getConnection(config.getJdbcURL(), config.getJdbcUser(), config.getJdbcPwd());
			log.info("Connected successfully");
			// set the AutoCommit to false for this connection. 
			// commit must be called explicitly! 
			conn.setAutoCommit(false);
			log.info("AutoCommit has been set to " + conn.getAutoCommit());

		} catch (Exception e)
        {
			log.error("Failed to connect directly to '" + config.getJdbcURL() + "' / User=" + config.getJdbcUser());
			log.error(e.toString());
			throw new RuntimeException(e);
		}
		return conn;
	}

	/**
	 * Creates an Empire-db DatabaseDriver for the given provider and applies dbms specific configuration 
	 */
    private static DBMSHandler getDBMSHandler(String provider, Connection conn)
    {
        try
        {   // Get Driver Class Name
            String dbmsHandlerClass = config.getDbmsHandlerClass();
            if (StringUtils.isEmpty(dbmsHandlerClass))
                throw new RuntimeException("Configuration error: Element 'dbmsHandlerClass' not found in node 'properties-"+provider+"'");

            // Create dbms
            DBMSHandler dbms = (DBMSHandler) Class.forName(dbmsHandlerClass).newInstance();

            // Configure dbms
            config.readProperties(dbms, "properties-"+provider, "dbmsHandlerProperites");

            // Special cases
            if (dbms instanceof DBMSHandlerPostgreSQL)
            {   // Create the reverse function that is needed by this sample
                ((DBMSHandlerPostgreSQL)dbms).createReverseFunction(conn);
            }

            // done
            return dbms;
            
        } catch (Exception e)
        {   // catch any checked exception and forward it
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

	/**
     * <PRE>
	 * Checks whether the database exists or not by executing
	 *     select count(*) from DEPARTMENTS
	 * If the Departments table does not exist the querySingleInt() function return -1 for failure.
	 * Please note that in this case an error will appear in the log which can be ignored.
     * </PRE>
	 */
	private static boolean databaseExists()
    {
		// Check whether DB exists
		DBCommand cmd = db.createCommand();
		cmd.select(db.DEPARTMENTS.count());
		// Check using "select count(*) from DEPARTMENTS"
		log.info("Checking whether table DEPARTMENTS exists (SQLException will be logged if not - please ignore) ...");
		return (context.getUtils().querySingleInt(cmd, -1) >= 0);
	}

	/**
     * <PRE>
	 * Creates a DDL Script for entire SampleDB Database and executes it line by line.
	 * Please make sure you uses the correct DatabaseDriver for your target DBMS.
     * </PRE>
	 */
	private static void createDatabase()
    {
		// create DDL for Database Definition
	    DBSQLScript script = new DBSQLScript(context);
		db.getCreateDDLScript(script);
		// Show DDL Statement
		log.info(script.toString());
		// Execute Script
		script.executeAll(false);
		// Commit
		context.commit();
	}
    
    private static void checkDataModel()
    {
        try {
            DBModelChecker modelChecker = context.getDbms().createModelChecker(db);
            // Check data model   
            log.info("Checking DataModel for {} using {}", db.getClass().getSimpleName(), modelChecker.getClass().getSimpleName());
            // dbo schema
            DBModelErrorLogger logger = new DBModelErrorLogger();
            modelChecker.checkModel(db, context.getConnection(), logger);
            // show result
            log.info("Data model check done. Found {} errors and {} warnings.", logger.getErrorCount(), logger.getWarnCount());
        } catch(Exception e) {
            log.error("FATAL error when checking data model. Probably not properly implemented by DBMSHandler!");
        }
    }

	/**
     * <PRE>
	 * Empties all Tables.
     * </PRE>
	 */
	private static void clearDatabase()
    {
		DBCommand cmd = context.createCommand(db);
        // Delete all Payments (no constraints)
        context.executeDelete(db.PAYMENTS, cmd);
		// Delete all Employees (no constraints)
		context.executeDelete(db.EMPLOYEES, cmd);
		// Delete all Departments (no constraints)
		context.executeDelete(db.DEPARTMENTS, cmd);
	}

	/**
     * <PRE>
	 * Insert a Department into the Departments table.
     * </PRE>
	 */
	private static int insertDepartment(String departmentName, String businessUnit)
    {
        SampleDB.Departments DEP = db.DEPARTMENTS;
		// Insert a Department
		DBRecord rec = new DBRecord(context, DEP);
		rec.create();
		rec.setValue(DEP.NAME, departmentName);
		rec.setValue(DEP.BUSINESS_UNIT, businessUnit);
		rec.update();
		// Return Department ID
		return rec.getInt(DEP.ID);
	}

	/**
     * <PRE>
	 * Inserts an Employee into the Employees table.
     * </PRE>
	 */
	private static int insertEmployee(String firstName, String lastName, Gender gender, int departmentId)
    {
        SampleDB.Employees EMP = db.EMPLOYEES;
		// Insert an Employee
		DBRecord rec = new DBRecord(context, EMP);
		rec.create(null);
		rec.setValue(EMP.FIRSTNAME, firstName);
		rec.setValue(EMP.LASTNAME, lastName);
		rec.setValue(EMP.GENDER, gender);
		rec.setValue(EMP.DEPARTMENT_ID, departmentId);
		rec.update();
		// Return Employee ID
		return rec.getInt(EMP.ID);
	}

    /**
     * <PRE>
     * Inserts an Payments for a particular Employee
     * </PRE>
     */
    private static void insertPayments(int employeeId, BigDecimal monthlySalary)
    {
        SampleDB.Payments PAY = db.PAYMENTS;
        // Insert an Employee
        LocalDate date = LocalDate.now();
        date = date.minusDays(date.getDayOfMonth()-1); // first day of this month
        // Add Payment for each month
        DBRecord rec = new DBRecord(context, PAY);
        for (LocalDate month=date.minusMonths(20); !month.isAfter(date); month=month.plusMonths(1))
        {
            BigDecimal variation = new BigDecimal((Math.random()*200) - 100.0);
            variation = variation.setScale(2, RoundingMode.HALF_UP);
            // insert
            rec.create(DBRecord.key(employeeId, month.getYear(), month.getMonth()));
            rec.setValue(PAY.AMOUNT, monthlySalary.add(variation));
            rec.update();
        }
    }

	/**
     * <PRE>
	 * Updates an employee record by setting the phone number.
     * </PRE>
	 */
	private static void updateEmployee(int idEmp, String phoneNumber)
    {
		// Update an Employee
		DBRecord rec = new DBRecord(context, db.EMPLOYEES);
		rec.read(idEmp);
		// Set
		rec.setValue(db.EMPLOYEES.PHONE_NUMBER, phoneNumber);
		rec.update();
	}

    /**
     * <PRE>
     * Updates an employee record by setting the phone number.
     * </PRE>
     */
    private static void updatePartialRecord(int idEmp, String phoneNumber)
    {
        // Shortcut for convenience
        SampleDB.Employees EMP = db.EMPLOYEES;
        // Update an Employee with partial record
        // this will only load the EMPLOYEE ID and the PHONE_NUMBER
        DBRecord rec = new DBRecord(context, EMP);
        EMP.readRecord(rec, DBRecord.key(idEmp), PartialMode.INCLUDE, EMP.PHONE_NUMBER);
        // Set
        rec.setValue(db.EMPLOYEES.PHONE_NUMBER, phoneNumber);
        rec.update();
    }

    /**
     * <PRE>
     * Updates an employee record by setting the phone number.
     * </PRE>
     */
    private static void updateJoinedRecords(int idEmp, int salary)
    {
        // Shortcuts for convenience
        SampleDB.Employees EMP = db.EMPLOYEES;
        SampleDB.Departments DEP = db.DEPARTMENTS;

        // Create DBQuery from command
        DBCommand cmd = db.createCommand();
        cmd.select(EMP.getColumns());
        cmd.select(DEP.getColumns());
        cmd.join(EMP.DEPARTMENT_ID, DEP.ID);
        DBQuery query = new DBQuery(cmd, EMP.ID);

        // Make employee Head of Department and update salary
        DBRecord rec = new DBRecord(context, query);
        rec.read(idEmp);
        rec.setValue(EMP.SALARY, salary);
        rec.setValue(DEP.HEAD, rec.getString(EMP.LASTNAME));
        rec.update();
    }

	/**
	 * @param context
	 * @param idDep
	 */
	private static int testTransactionCreate(int idDep)
    {
        // Shortcut for convenience
        SampleDB.Employees EMP = db.EMPLOYEES;

        DBRecord rec = new DBRecord(context, EMP);
        rec.create();
        rec.setValue(EMP.FIRSTNAME, "Foo");
        rec.setValue(EMP.LASTNAME, "Manchoo");
        rec.setValue(EMP.GENDER, Gender.M);
        rec.setValue(EMP.DEPARTMENT_ID, idDep);
        rec.update();
        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        
        rec.setValue(EMP.FIRSTNAME, "Foo 2");
        rec.setValue(EMP.LASTNAME, "Manchu");
        rec.setValue(EMP.PHONE_NUMBER, "0815/4711");
        rec.update();
        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        
        context.rollback();
        
        rec.setValue(EMP.FIRSTNAME, "Dr. Foo");
        rec.update();
        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));

        rec.delete();
        
        context.rollback();

        // insert final
        rec.update();
        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        
        log.info("testTransactionCreate performed OK");
        context.commit();
        
        return rec.getInt(EMP.ID);
    }
    /**
     * @param context
     * @param idDep
     */
    private static void testTransactionUpdate(int idEmp)
    {
        // Shortcut for convenience
        SampleDB.Employees EMP = db.EMPLOYEES;
        
        DBRecord rec = new DBRecord(context, EMP);        
        rec.read(idEmp);
        rec.setValue(EMP.PHONE_NUMBER, null);
        rec.setValue(EMP.SALARY, "100.000");
        rec.update();

        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        
        context.rollback();
        
        rec.setValue(EMP.PHONE_NUMBER, "07531-45716-0");
        rec.update();

        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        
        context.rollback();

        rec.update();

        log.info("Timestamp {}", rec.getString(EMP.UPDATE_TIMESTAMP));
        log.info("testTransactionUpdate performed OK");
        context.commit();
        
    }
    /**
     * @param context
     * @param idDep
     */
    private static void testTransactionDelete(int idEmp)
    {
        // Shortcut for convenience
        SampleDB.Employees T = db.EMPLOYEES;

        DBRecord rec = new DBRecord(context, T);
        rec.read(idEmp);
        /*
        log.info("Timestamp {}", rec.getString(T.UPDATE_TIMESTAMP));
        rec.setValue(T.SALARY, "100.001");
        rec.update();
        log.info("Timestamp {}", rec.getString(T.UPDATE_TIMESTAMP));
        */
        rec.delete();
        
        context.rollback();

        /*
        DBCommand cmd = db.createCommand();
        cmd.select(T.UPDATE_TIMESTAMP);
        cmd.where (T.EMPLOYEE_ID.is(idEmp));
        log.info("Timestamp {}", db.querySingleString(cmd, context.getConnection()));
        */
        
        rec.update();
        
        log.info("Transaction performed OK");
        
    }

	/**
	 * <PRE>
	 * Performs an SQL-Query and prints the result to System.out
	 * 
	 * First a DBCommand object is used to create the following SQL-Query (Oracle-Syntax):
     *     
     *   SELECT t2.EMPLOYEE_ID, t2.LASTNAME || ', ' || t2.FIRSTNAME AS FULL_NAME, t2.GENDER, t2.PHONE_NUMBER, 
     *          substr(t2.PHONE_NUMBER, length(t2.PHONE_NUMBER)-instr(reverse(t2.PHONE_NUMBER), '-')+2) AS PHONE_EXTENSION, 
     *          t1.NAME AS DEPARTMENT, t1.BUSINESS_UNIT
     *   FROM EMPLOYEES t2 INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = t2.ID
     *   WHERE length(t2.LASTNAME)>0
     *   ORDER BY t2.LASTNAME, t2.FIRSTNAME
     * 
	 * For processing the rows there are three options available:
	 * 
	 *   QueryType.Reader:
	 *     Iterates through all rows and prints field values as tabbed text.
	 *      
     *   QueryType.BeanList:
     *     Obtains the query result as a list of JavaBean objects of type SampleBean.
     *     It then iterates through the list of beans and uses bean.toString() for printing.
     *     
     *   QueryType.XmlDocument:
     *     Obtains the query result as an XML-Document and prints the document.
     *     Please note, that the XML not only contains the data but also the field metadata.
     * </PRE>
	 */
	private static void queryRecords(QueryType queryType)
    {
        int lastYear = LocalDate.now().getYear()-1;
	    
	    // Create a command
	    DBCommand cmd = db.createCommand();
	    
	    // Define shortcuts for tables used - not necessary but convenient
	    SampleDB.Employees   EMP = db.EMPLOYEES;
	    SampleDB.Departments DEP = db.DEPARTMENTS;
        SampleDB.Payments    PAY = db.PAYMENTS;

	    // The following expression concats lastname + ', ' + firstname
        DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.append(", ").append(EMP.FIRSTNAME).as("FULL_NAME");
        DBColumnExpr PAYMENTS_LAST_YEAR = PAY.AMOUNT.sum().as("PAYMENTS_LAST_YEAR");
        
        // 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.getDbms() instanceof DBMSHandlerHSql 
        		|| db.getDbms() instanceof DBMSHandlerDerby
        		|| db.getDbms() instanceof DBMSHandlerH2)
             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 Employee and Department columns
        cmd.select(EMP.ID, EMPLOYEE_FULLNAME);
        cmd.select(EMP.GENDER, EMP.PHONE_NUMBER, PHONE_EXT_NUMBER);
        cmd.select(DEP.NAME.as("DEPARTMENT"));
        cmd.select(DEP.BUSINESS_UNIT);
        // Add payment of last year using a SUM aggregation
        cmd.groupBy(cmd.getSelectExpressions());
        cmd.select(PAYMENTS_LAST_YEAR);
        // Joins
        cmd.join(EMP.DEPARTMENT_ID, DEP.ID);
        cmd.joinLeft(EMP.ID, PAY.EMPLOYEE_ID).where(PAY.YEAR.is(lastYear));
        // Where constraints
        cmd.where(EMP.RETIRED.is(false));
        cmd.where(EMP.LASTNAME.length().isGreaterThan(0));
        // Order by
        cmd.orderBy(EMPLOYEE_FULLNAME);
        
        /*
        List<DataListEntry> list = context.getUtils().queryDataList(cmd);
        for (DataListEntry dle : list)
        {
            System.out.println(dle.toString());
        }
        */

        /*
         * Example for limitRows() and skipRows()
         * Uncomment if you wish
         *
        if (db.getDbms().isSupported(DBMSFeature.QUERY_LIMIT_ROWS))
        {	// set maximum number of rows
        	cmd.limitRows(20);
            if (db.getDbms().isSupported(DBMSFeature.QUERY_SKIP_ROWS))
                cmd.skipRows(1);
        }
        */
        
		// Query Records and print output
		DBReader reader = new DBReader(context);
		try
        {
		    // log select statement (but only once)
		    if (queryType==QueryType.Reader)
		        log.info("Running Query: {}", cmd.getSelect());
		    // Open Reader 
			reader.open(cmd);
			// Print output
			System.out.println("---------------------------------");
			switch(queryType)
			{
			    case Reader:
			        // Text-Output by iterating through all records.
	                while (reader.moveNext())
                    {
	                    System.out.println(reader.getString(EMP.ID)
	                            + "\t" + reader.getString(EMPLOYEE_FULLNAME)
	                            + "\t" + EMP.GENDER.getOptions().get(reader.getString(EMP.GENDER))
                                + "\t" + reader.getString(PHONE_EXT_NUMBER)
	                            + "\t" + reader.getString(DEP.NAME)
	                            + "\t" + reader.getString(PAYMENTS_LAST_YEAR));
	                }
			        break;
                case BeanList:
                    // Text-Output using a list of Java Beans supplied by the DBReader
                    List<SampleBean> beanList = reader.getBeanList(SampleBean.class);
                    // log.info(String.valueOf(beanList.size()) + " SampleBeans returned from Query.");
                    for (SampleBean b : beanList)
                    {
                        System.out.println(b.toString());
                    }
                    break;
                case XmlDocument:
                    // XML Output
                    Document doc = reader.getXmlDocument();
                    // Print XML Document to System.out
                    XMLWriter.debug(doc);
                    break;
			}
            System.out.println("---------------------------------");
		} finally  {
			// Always close Reader!
			reader.close();
		}
	}
	
	private static void queryBeans(Connection conn)
	{
	    SampleDB.Employees EMP = db.EMPLOYEES;
        // Query all males
	    BeanResult<SampleBean> result = new BeanResult<SampleBean>(SampleBean.class, EMP);
        result.getCommand().where(EMP.GENDER.is(Gender.M));
	    result.fetch(context);
	    
	    log.info("Number of male employees is: "+result.size());

	    // And now, the females
	    result.getCommand().where(EMP.GENDER.is(Gender.F));
	    result.fetch(context);
	    
        log.info("Number of female employees is: "+result.size());
	}
	
}