empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java

/*
 * 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.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.bean.BeanResult;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBDatabaseDriver;
import org.apache.empire.db.DBReader;
import org.apache.empire.db.DBRecord;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.derby.DBDatabaseDriverDerby;
import org.apache.empire.db.h2.DBDatabaseDriverH2;
import org.apache.empire.db.hsql.DBDatabaseDriverHSql;
import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL;
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 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" ));

			System.out.println("Running DB Sample...");

			// STEP 1: Get a JDBC Connection
			System.out.println("*** Step 1: getJDBCConnection() ***");
			
			Connection conn = getJDBCConnection();

			// STEP 2: Choose a driver
			System.out.println("*** Step 2: getDatabaseProvider() ***");
			DBDatabaseDriver driver = getDatabaseDriver(config.getDatabaseProvider(), conn);

            // STEP 3: Open Database (and create if not existing)
            System.out.println("*** Step 3: openDatabase() ***");
			try {
			    // Open the database
                db.open(driver, conn);
                // Check whether database exists
			    databaseExists(conn);
                System.out.println("*** Database already exists. Skipping Step4 ***");
                
			} catch(Exception e) {
                // STEP 4: Create Database
                System.out.println("*** Step 4: createDDL() ***");
                // postgre does not support DDL in transaction
                if(db.getDriver() instanceof DBDatabaseDriverPostgreSQL)
                {
                	conn.setAutoCommit(true);
                }
                createDatabase(driver, conn);
                if(db.getDriver() instanceof DBDatabaseDriverPostgreSQL)
                {
                	conn.setAutoCommit(false);
                }
                // Open again
                if (db.isOpen()==false)
                    db.open(driver, conn);
			}

			// STEP 5: Clear Database (Delete all records)
			System.out.println("*** Step 5: clearDatabase() ***");
			clearDatabase(conn);

			// STEP 6: Insert Departments
			System.out.println("*** Step 6: insertDepartment() & insertEmployee() ***");
			int idDevDep = insertDepartment(conn, "Development", "ITTK");
			int idSalDep = insertDepartment(conn, "Sales", "ITTK");
			// Insert Employees
			int idPers1 = insertEmployee(conn, "Peter", "Sharp", Gender.M, idDevDep);
			int idPers2 = insertEmployee(conn, "Fred", "Bloggs", Gender.M, idDevDep);
			int idPers3 = insertEmployee(conn, "Emma", "White",  Gender.F, idSalDep);

			// STEP 7: Update Records (by setting the phone Number)
			System.out.println("*** Step 7: updateEmployee() ***");
			updateEmployee(conn, idPers1, "+49-7531-457160");
			updateEmployee(conn, idPers2, "+49-5555-505050");
			updateEmployee(conn, idPers3, "+49-040-125486");

			// commit
			db.commit(conn);

			// STEP 8: Option 1: Query Records and print tab-separated
			System.out.println("*** Step 8 Option 1: queryRecords() / Tab-Output ***");
			queryRecords(conn, QueryType.Reader); // Tab-Output

            // STEP 8: Option 2: Query Records as a list of java beans
            System.out.println("*** Step 8 Option 2: queryRecords() / Bean-List-Output ***");
            queryRecords(conn, QueryType.BeanList); // Bean-List-Output

			// STEP 8: Option 3: Query Records as XML
			System.out.println("*** Step 8 Option 3: queryRecords() / XML-Output ***");
			queryRecords(conn, QueryType.XmlDocument); // XML-Output

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

		} catch (Exception e)
        {
			// Error
			System.out.println(e.toString());
			e.printStackTrace();
		}

	}

	/**
     * <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 is " + 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 driver specific configuration 
	 */
    private static DBDatabaseDriver getDatabaseDriver(String provider, Connection conn)
    {
        try
        {   // Get Driver Class Name
            String driverClassName = config.getEmpireDBDriverClass();
            if (StringUtils.isEmpty(driverClassName))
                throw new RuntimeException("Configuration error: Element 'empireDBDriverClass' not found in node 'properties-"+provider+"'");

            // Create driver
            DBDatabaseDriver driver = (DBDatabaseDriver) Class.forName(driverClassName).newInstance();

            // Configure driver
            config.readProperties(driver, "properties-"+provider, "empireDBDriverProperites");

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

            // done
            return driver;
            
        } 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(Connection conn)
    {
		// Check whether DB exists
		DBCommand cmd = db.createCommand();
		cmd.select(db.DEPARTMENTS.count());
		// Check using "select count(*) from DEPARTMENTS"
		System.out.println("Checking whether table DEPARTMENTS exists (SQLException will be logged if not - please ignore) ...");
		return (db.querySingleInt(cmd, -1, conn) >= 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(DBDatabaseDriver driver, Connection conn)
    {
		// create DDL for Database Definition
	    DBSQLScript script = new DBSQLScript();
		db.getCreateDDLScript(driver, script);
		// Show DDL Statement
		System.out.println(script.toString());
		// Execute Script
		script.run(driver, conn, false);
		// Commit
		db.commit(conn);
	}

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

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

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

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

	/**
	 * <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.DEPARTMENT_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(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);
        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)
			{
			    case Reader:
			        // Text-Output by iterating through all records.
	                while (reader.moveNext())
                    {
	                    System.out.println(reader.getString(EMP.EMPLOYEE_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));
	                }
			        break;
                case BeanList:
                    // Text-Output using a list of Java Beans supplied by the DBReader
                    List<SampleBean> beanList = reader.getBeanList(SampleBean.class);
                    System.out.println(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;
			}

		} finally
        {
			// always close Reader
			reader.close();
		}
	}
	
	private static void queryBeans(Connection conn)
	{
        // Query all males
	    BeanResult<SampleBean> result = new BeanResult<SampleBean>(SampleBean.class, db.EMPLOYEES);
        result.getCommand().where(db.EMPLOYEES.GENDER.is(Gender.M));
	    result.fetch(conn);
	    
	    System.out.println("Number of male employees is: "+result.size());

	    // And now, the females
	    result.getCommand().where(db.EMPLOYEES.GENDER.is(Gender.F));
	    result.fetch(conn);
	    
        System.out.println("Number of female employees is: "+result.size());
	}
	
}