/*
* 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());
}
}