| /* |
| * 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.advanced; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.util.Date; |
| import java.util.HashMap; |
| |
| import org.apache.empire.commons.DateUtils; |
| import org.apache.empire.commons.Options; |
| import org.apache.empire.commons.StringUtils; |
| import org.apache.empire.data.DataMode; |
| import org.apache.empire.data.DataType; |
| import org.apache.empire.db.DBCmdParam; |
| import org.apache.empire.db.DBCmdType; |
| import org.apache.empire.db.DBColumnExpr; |
| import org.apache.empire.db.DBCommand; |
| import org.apache.empire.db.DBDatabaseDriver; |
| import org.apache.empire.db.DBQuery; |
| import org.apache.empire.db.DBReader; |
| import org.apache.empire.db.DBRecord; |
| import org.apache.empire.db.DBSQLScript; |
| import org.apache.empire.db.DBTableColumn; |
| import org.apache.empire.db.exceptions.StatementFailedException; |
| import org.apache.empire.db.h2.DBDatabaseDriverH2; |
| import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL; |
| import org.slf4j.Logger; |
| import org.slf4j.LoggerFactory; |
| |
| |
| public class SampleAdvApp |
| { |
| // Logger |
| private static final Logger log = LoggerFactory.getLogger(SampleAdvApp.class); |
| |
| private static final SampleAdvDB db = new SampleAdvDB(); |
| |
| private static SampleAdvConfig config = new SampleAdvConfig(); |
| |
| // Shortcuts |
| private static SampleAdvDB.Employees T_EMP = db.T_EMPLOYEES; |
| private static SampleAdvDB.Departments T_DEP = db.T_DEPARTMENTS; |
| private static SampleAdvDB.EmployeeDepartmentHistory T_EDH = db.T_EMP_DEP_HIST; |
| |
| /** |
| * <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 command line 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 Advanced..."); |
| |
| // 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()); |
| |
| // STEP 3: Open Database (and create if not existing) |
| System.out.println("*** Step 3: openDatabase() ***"); |
| try { |
| // Enable the use of prepared statements for update and insert commands as well as for read operations on a DBRecord. |
| // Note: For custom SQL commands parameters must be explicitly declared using cmd.addCmdParam(); |
| db.setPreparedStatementsEnabled(true); |
| // 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 Records |
| // Insert Departments |
| System.out.println("*** Step 6: inserting departments, employees and employee_department_history records ***"); |
| int idDevDep = insertDepartment(conn, "Development", "ITTK"); |
| int idProdDep = insertDepartment(conn, "Production", "ITTK"); |
| int idSalDep = insertDepartment(conn, "Sales", "ITTK"); |
| |
| // Insert Employees |
| int idEmp1 = insertEmployee(conn, "Peter", "Sharp", "M"); |
| int idEmp2 = insertEmployee(conn, "Fred", "Bloggs", "M"); |
| int idEmp3 = insertEmployee(conn, "Emma", "White", "F"); |
| |
| // Insert History as batch |
| DBSQLScript batch = new DBSQLScript(); |
| insertEmpDepHistory(batch, idEmp1, idDevDep, DateUtils.getDate(2007, 12, 1)); |
| insertEmpDepHistory(batch, idEmp1, idProdDep, DateUtils.getDate(2008, 9, 1)); |
| insertEmpDepHistory(batch, idEmp1, idSalDep, DateUtils.getDate(2009, 5, 15)); |
| |
| insertEmpDepHistory(batch, idEmp2, idSalDep, DateUtils.getDate(2006, 3, 1)); |
| insertEmpDepHistory(batch, idEmp2, idDevDep, DateUtils.getDate(2008, 11, 15)); |
| |
| insertEmpDepHistory(batch, idEmp3, idDevDep, DateUtils.getDate(2006, 9, 15)); |
| insertEmpDepHistory(batch, idEmp3, idSalDep, DateUtils.getDate(2007, 6, 1)); |
| insertEmpDepHistory(batch, idEmp3, idProdDep, DateUtils.getDate(2008, 7, 31)); |
| batch.executeBatch(db.getDriver(), conn); |
| |
| // commit |
| db.commit(conn); |
| |
| // STEP 7: read from Employee_Info_View |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** read from EMPLOYEE_INFO_VIEW ***"); |
| DBCommand cmd = db.createCommand(); |
| cmd.select (db.V_EMPLOYEE_INFO.getColumns()); |
| cmd.orderBy(db.V_EMPLOYEE_INFO.C_NAME_AND_DEP); |
| printQueryResults(cmd, conn); |
| |
| // STEP 8: prepared Statement sample |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** commandParamsSample: shows how to use command parameters for the generation of prepared statements ***"); |
| commandParamsSample(conn, idProdDep, idDevDep); |
| |
| // STEP 9: bulkReadRecords |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** bulkReadRecords: reads employee records into a hashmap, reads employee from hashmap and updates employee ***"); |
| HashMap<Integer, DBRecord> employeeMap = bulkReadRecords(conn); |
| DBRecord rec = employeeMap.get(idEmp2); |
| rec.setValue(db.T_EMPLOYEES.C_SALUTATION, "Mr."); |
| rec.update(conn); |
| |
| // STEP 10: bulkProcessRecords |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** bulkProcessRecords: creates a checksum for every employee in the employees table ***"); |
| bulkProcessRecords(conn); |
| |
| // STEP 11: querySample |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** querySample: shows how to use DBQuery class for subqueries and multi table records ***"); |
| querySample(conn, idEmp2); |
| |
| // STEP 12: ddlSample |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** ddlSample: shows how to add a column at runtime and update a record with the added column ***"); |
| if (db.getDriver() instanceof DBDatabaseDriverH2) { |
| log.info("As H2 does not support changing a table with a view defined we remove the view"); |
| System.out.println("*** drop EMPLOYEE_INFO_VIEW ***"); |
| DBSQLScript script = new DBSQLScript(); |
| db.getDriver().getDDLScript(DBCmdType.DROP, db.V_EMPLOYEE_INFO, script); |
| script.executeAll(db.getDriver(), conn); |
| } |
| ddlSample(conn, idEmp2); |
| if (db.getDriver() instanceof DBDatabaseDriverH2) { |
| log.info("And put back the view"); |
| System.out.println("*** create EMPLOYEE_INFO_VIEW ***"); |
| DBSQLScript script = new DBSQLScript(); |
| db.getDriver().getDDLScript(DBCmdType.CREATE, db.V_EMPLOYEE_INFO, script); |
| script.executeAll(db.getDriver(), conn); |
| } |
| |
| // STEP 13: delete records |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("*** deleteRecordSample: shows how to delete records (with and without cascade) ***"); |
| deleteRecordSample(idEmp3, idSalDep, conn); |
| |
| // Done |
| System.out.println("--------------------------------------------------------"); |
| System.out.println("DB Sample Advanced 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 this session. You must commit |
| // explicitly now |
| 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) |
| { |
| 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"); |
| |
| // 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 wich can be ingored. |
| * </PRE> |
| */ |
| private static boolean databaseExists(Connection conn) |
| { |
| // Check whether DB exists |
| DBCommand cmd = db.createCommand(); |
| cmd.select(T_DEP.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 DLL for Database Definition |
| DBSQLScript script = new DBSQLScript(); |
| db.getCreateDDLScript(driver, script); |
| // Show DLL Statement |
| System.out.println(script.toString()); |
| // Execute Script |
| script.executeAll(driver, conn); |
| // Commit |
| db.commit(conn); |
| } |
| |
| /** |
| * <PRE> |
| * Empties all Tables. |
| * </PRE> |
| */ |
| private static void clearDatabase(Connection conn) |
| { |
| DBCommand cmd = db.createCommand(); |
| // Delete all Employee Department History records |
| db.executeDelete(T_EDH, cmd, conn); |
| // Delete all Employees (no constraints) |
| db.executeDelete(T_EMP, cmd, conn); |
| // Delete all Departments (no constraints) |
| db.executeDelete(T_DEP, 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(T_DEP); |
| rec.setValue(T_DEP.C_NAME, departmentName); |
| rec.setValue(T_DEP.C_BUSINESS_UNIT, businessUnit); |
| rec.update(conn); |
| // Return Department ID |
| return rec.getInt(T_DEP.C_DEPARTMENT_ID); |
| } |
| |
| /** |
| * <PRE> |
| * Inserts an Employee into the Employees table. |
| * </PRE> |
| */ |
| private static int insertEmployee(Connection conn, String firstName, String lastName, String gender) |
| { |
| // Insert an Employee |
| DBRecord rec = new DBRecord(); |
| rec.create(T_EMP); |
| rec.setValue(T_EMP.C_FIRSTNAME, firstName); |
| rec.setValue(T_EMP.C_LASTNAME, lastName); |
| rec.setValue(T_EMP.C_GENDER, gender); |
| rec.update(conn); |
| // Return Employee ID |
| return rec.getInt(T_EMP.C_EMPLOYEE_ID); |
| } |
| |
| /** |
| * <PRE> |
| * Inserts an Employee into the Employees table. |
| * </PRE> |
| */ |
| private static void insertEmpDepHistory(DBSQLScript script, int employeeId, int departmentId, Date dateFrom) |
| { |
| // Insert an Employee |
| /* |
| DBRecord rec = new DBRecord(); |
| rec.create(T_EDH); |
| rec.setValue(T_EDH.C_EMPLOYEE_ID, employeeId); |
| rec.setValue(T_EDH.C_DEPARTMENT_ID, departmentId); |
| rec.setValue(T_EDH.C_DATE_FROM, dateFrom); |
| rec.update(conn); |
| */ |
| DBCommand cmd = db.createCommand(); |
| cmd.set(T_EDH.C_EMPLOYEE_ID.to(employeeId)); |
| cmd.set(T_EDH.C_DEPARTMENT_ID.to(departmentId)); |
| cmd.set(T_EDH.C_DATE_FROM.to(dateFrom)); |
| // Add to script for batch execution |
| script.addInsert(cmd); |
| } |
| |
| /* This procedure demonstrates the use of command parameter for prepared statements */ |
| private static void commandParamsSample(Connection conn, int idProdDep, int idDevDep) |
| { |
| // create a command |
| DBCommand cmd = db.createCommand(); |
| // Create cmd parameters |
| DBCmdParam curDepParam = cmd.addParam(); // Current Department |
| DBCmdParam genderParam = cmd.addParam(); // Gender ('M' or 'F') |
| // Define the query |
| cmd.select(T_EMP.C_FULLNAME); |
| cmd.join (T_EMP.C_EMPLOYEE_ID, db.V_EMPLOYEE_INFO.C_EMPLOYEE_ID); |
| cmd.where (T_EMP.C_GENDER.is(genderParam)); |
| cmd.where (db.V_EMPLOYEE_INFO.C_CURRENT_DEP_ID.is(curDepParam)); |
| |
| System.out.println("Perfoming two queries using a the same command with different parameter values."); |
| |
| DBReader r = new DBReader(); |
| try { |
| // Query all females currently working in the Production department |
| System.out.println("1. Query all females currently working in the production department"); |
| // Set command parameter values |
| genderParam.setValue('F'); // set gender to female |
| curDepParam.setValue(idProdDep); // set department id to production department |
| // Open reader using a prepared statement (due to command parameters!) |
| r.open(cmd, conn); |
| // print all results |
| System.out.println("Females working in the production department are:"); |
| while (r.moveNext()) |
| System.out.println(" " + r.getString(T_EMP.C_FULLNAME)); |
| r.close(); |
| |
| // Second query |
| // Now query all males currently working in the development department |
| System.out.println("2. Query all males currently working in the development department"); |
| // Set command parameter values |
| genderParam.setValue('M'); // set gender to female |
| curDepParam.setValue(idDevDep); // set department id to production department |
| // Open reader using a prepared statement (due to command parameters!) |
| r.open(cmd, conn); |
| // print all results |
| System.out.println("Males currently working in the development department are:"); |
| while (r.moveNext()) |
| System.out.println(" " + r.getString(T_EMP.C_FULLNAME)); |
| |
| } finally { |
| r.close(); |
| } |
| |
| } |
| |
| /** |
| * This function performs a query to select non-retired employees,<BR> |
| * then it calculates a checksum for every record<BR> |
| * and writes that checksum back to the database.<BR> |
| * <P> |
| * @param conn a connection to the database |
| */ |
| private static void bulkProcessRecords(Connection conn) |
| { |
| // Define the query |
| DBCommand cmd = db.createCommand(); |
| // Define shortcuts for tables used - not necessary but convenient |
| SampleAdvDB.Employees EMP = T_EMP; |
| // Select required columns |
| cmd.select(T_EMP.getColumns()); |
| // Set Constraints |
| cmd.where(T_EMP.C_RETIRED.is(false)); |
| |
| // 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 |
| DBRecord record = new DBRecord(); |
| while (reader.moveNext()) |
| { |
| // Calculate sum |
| int sum = 0; |
| for (int i=0; i<reader.getFieldCount(); i++) |
| sum += calcCharSum(reader.getString(i)); |
| // Init updateable record |
| reader.initRecord(EMP, record); |
| // reader |
| record.setValue(T_EMP.C_CHECKSUM, sum); |
| record.update(conn); |
| } |
| // Done |
| db.commit(conn); |
| |
| } finally |
| { |
| // always close Reader |
| reader.close(); |
| } |
| } |
| |
| private static int calcCharSum(String value) |
| { |
| int sum = 0; |
| if (value!=null) |
| { // calcCharSum |
| int len = value.length(); |
| for (int i=0; i<len; i++) |
| sum += value.charAt(i); |
| } |
| return sum; |
| } |
| |
| private static HashMap<Integer, DBRecord> bulkReadRecords(Connection conn) |
| { |
| // Define the query |
| DBCommand cmd = db.createCommand(); |
| // Select required columns |
| cmd.select(T_EMP.getColumns()); |
| // Set Constraints |
| cmd.where(T_EMP.C_RETIRED.is(false)); |
| |
| // 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 |
| HashMap<Integer, DBRecord> employeeMap = new HashMap<Integer, DBRecord>(); |
| while (reader.moveNext()) |
| { |
| DBRecord rec = new DBRecord(); |
| reader.initRecord(T_EMP, rec); |
| employeeMap.put(reader.getInt(T_EMP.C_EMPLOYEE_ID), rec); |
| } |
| return employeeMap; |
| |
| } finally |
| { // always close Reader |
| reader.close(); |
| } |
| } |
| |
| /** |
| * This method demonstrates how to add, modify and delete a database column.<BR> |
| * This function demonstrates the use of the {@link DBDatabaseDriver#getDDLScript(org.apache.empire.db.DBCmdType, org.apache.empire.db.DBObject, DBSQLScript)}<BR> |
| * |
| */ |
| private static void ddlSample(Connection conn, int idTestPerson) |
| { |
| // Enable Column default for the database |
| // This is needed for adding required fields to non-empty tables |
| db.getDriver().setDDLColumnDefaults(true); |
| |
| // First, add a new column to the Table object |
| DBTableColumn C_FOO = db.T_EMPLOYEES.addColumn("FOO", DataType.VARCHAR, 20, false); |
| |
| // Now create the corresponding DDL statement |
| System.out.println("Creating new column named FOO as varchar(20) for the EMPLOYEES table:"); |
| DBSQLScript script = new DBSQLScript(); |
| db.getDriver().getDDLScript(DBCmdType.CREATE, C_FOO, script); |
| script.executeAll(db.getDriver(), conn); |
| |
| // Now load a record from that table and set the value for foo |
| System.out.println("Changing the value for the FOO field of a particular employee:"); |
| DBRecord rec = new DBRecord(); |
| rec.read(db.T_EMPLOYEES, idTestPerson, conn); |
| rec.setValue(C_FOO, "Hello World"); |
| rec.update(conn); |
| |
| // Now extend the size of the field from 20 to 40 characters |
| System.out.println("Extending size of column FOO to 40 characters:"); |
| C_FOO.setSize(40); |
| script.clear(); |
| db.getDriver().getDDLScript(DBCmdType.ALTER, C_FOO, script); |
| script.executeAll(db.getDriver(), conn); |
| |
| // Now set a longer value for the record |
| System.out.println("Changing the value for the FOO field for the above employee to a longer string:"); |
| rec.setValue(C_FOO, "This is a very long field value!"); |
| rec.update(conn); |
| |
| // Finally, drop the column again |
| System.out.println("Dropping the FOO column from the employee table:"); |
| script.clear(); |
| db.getDriver().getDDLScript(DBCmdType.DROP, C_FOO, script); |
| script.executeAll(db.getDriver(), conn); |
| } |
| |
| /** |
| * This function demonstrates the use of the DBQuery object.<BR> |
| * First a DBQuery is used to define a subquery that gets the latest employee department history record.<BR> |
| * This subquery is then used inside another query to list all employees with the current department.<BR> |
| * <P> |
| * In the second part, another DBQuery object is used to read a record that holds information from both |
| * the employee and department table. When the information is modified and the record's update method is |
| * called, then both tables are updated. |
| * <P> |
| * @param conn |
| * @param employeeId |
| */ |
| private static void querySample(Connection conn, int employeeId) |
| { |
| // Define the sub query |
| DBCommand subCmd = db.createCommand(); |
| DBColumnExpr MAX_DATE_FROM = T_EDH.C_DATE_FROM.max().as(T_EDH.C_DATE_FROM); |
| subCmd.select(T_EDH.C_EMPLOYEE_ID, MAX_DATE_FROM); |
| subCmd.groupBy(T_EDH.C_EMPLOYEE_ID); |
| DBQuery Q_MAX_DATE = new DBQuery(subCmd); |
| |
| // Define the query |
| DBCommand cmd = db.createCommand(); |
| // Select required columns |
| cmd.select(T_EMP.C_EMPLOYEE_ID, T_EMP.C_FULLNAME); |
| cmd.select(T_EMP.C_GENDER, T_EMP.C_PHONE_NUMBER); |
| cmd.select(T_DEP.C_DEPARTMENT_ID, T_DEP.C_NAME, T_DEP.C_BUSINESS_UNIT); |
| cmd.select(T_EMP.C_UPDATE_TIMESTAMP, T_DEP.C_UPDATE_TIMESTAMP); |
| // Set Joins |
| cmd.join(T_EDH.C_EMPLOYEE_ID, Q_MAX_DATE.findQueryColumn(T_EDH.C_EMPLOYEE_ID)) |
| .where(T_EDH.C_DATE_FROM.is(Q_MAX_DATE.findQueryColumn(MAX_DATE_FROM))); |
| cmd.join(T_EMP.C_EMPLOYEE_ID, T_EDH.C_EMPLOYEE_ID); |
| cmd.join(T_DEP.C_DEPARTMENT_ID, T_EDH.C_DEPARTMENT_ID); |
| // Set Constraints |
| cmd.where(T_EMP.C_RETIRED.is(false)); |
| // Set Order |
| cmd.orderBy(T_EMP.C_LASTNAME); |
| cmd.orderBy(T_EMP.C_FIRSTNAME); |
| |
| // Query Records and print output |
| printQueryResults(cmd, conn); |
| |
| // Define an updateable query |
| DBQuery Q_EMP_DEP = new DBQuery(cmd, T_EMP.C_EMPLOYEE_ID); |
| DBRecord rec = new DBRecord(); |
| rec.read(Q_EMP_DEP, employeeId, conn); |
| // Modify and Update fields from both Employee and Department |
| rec.setValue(T_EMP.C_PHONE_NUMBER, "0815-4711"); |
| rec.setValue(T_DEP.C_BUSINESS_UNIT, "AUTO"); |
| rec.update(conn); |
| // Successfully updated |
| System.out.println("The employee has been sucessfully updated"); |
| } |
| |
| /** |
| * This function demonstrates cascaded deletes. |
| * See DBRelation.setOnDeleteAction() |
| * |
| * @param idEmployee the id of the employee to delete |
| * @param idDepartment the id of the department to delete |
| * @param conn the connection |
| */ |
| private static void deleteRecordSample(int idEmployee, int idDepartment, Connection conn) |
| { |
| db.commit(conn); |
| // Delete an employee |
| // This statement is designed to succeed since cascaded deletes are enabled for this relation. |
| db.T_EMPLOYEES.deleteRecord(idEmployee, conn); |
| System.out.println("The employee has been sucessfully deleted"); |
| |
| // Delete a department |
| // This statement is designed to fail since cascaded deletes are not on! |
| try { |
| db.T_DEPARTMENTS.deleteRecord(idDepartment, conn); |
| } catch(StatementFailedException e) { |
| System.out.println("Delete of department failed as expected due to existing depending records."); |
| } |
| } |
| |
| /** |
| * This functions prints the results of a query which is performed using the supplied command |
| * @param cmd the command to be used for performing the query |
| * @param conn the connection |
| */ |
| private static void printQueryResults(DBCommand cmd, Connection conn) |
| { |
| // 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 column titles |
| System.out.println("---------------------------------"); |
| int count = reader.getFieldCount(); |
| for (int i=0; i<count; i++) |
| { // Print all column names |
| DBColumnExpr c = reader.getColumnExpr(i); |
| if (i>0) |
| System.out.print("\t"); |
| System.out.print(c.getName()); |
| } |
| // Print output |
| System.out.println(""); |
| // Text-Output by iterating through all records. |
| while (reader.moveNext()) |
| { |
| for (int i=0; i<count; i++) |
| { // Print all field values |
| if (i>0) |
| System.out.print("\t"); |
| // Check if conversion is necessary |
| DBColumnExpr c = reader.getColumnExpr(i); |
| Options opt = c.getOptions(); |
| if (opt!=null) |
| { // Option Lookup |
| System.out.print(opt.get(reader.getValue(i))); |
| } |
| else |
| { // Print String |
| System.out.print(reader.getString(i)); |
| } |
| } |
| System.out.println(""); |
| } |
| |
| } finally |
| { // always close Reader |
| reader.close(); |
| } |
| } |
| } |
