Return-Path: Delivered-To: apmail-incubator-empire-db-commits-archive@minotaur.apache.org Received: (qmail 82395 invoked from network); 2 Dec 2010 23:21:09 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 2 Dec 2010 23:21:09 -0000 Received: (qmail 93796 invoked by uid 500); 2 Dec 2010 23:21:09 -0000 Delivered-To: apmail-incubator-empire-db-commits-archive@incubator.apache.org Received: (qmail 93775 invoked by uid 500); 2 Dec 2010 23:21:09 -0000 Mailing-List: contact empire-db-commits-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: empire-db-dev@incubator.apache.org Delivered-To: mailing list empire-db-commits@incubator.apache.org Received: (qmail 93767 invoked by uid 99); 2 Dec 2010 23:21:09 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Dec 2010 23:21:09 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 02 Dec 2010 23:21:08 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 73DBC23889B1; Thu, 2 Dec 2010 23:19:36 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1041635 - in /incubator/empire-db/trunk/empire-db-examples: empire-db-example-advanced/ empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/ empire-db-example-basic/src/main/java/org/apache/empire/samples/db/ Date: Thu, 02 Dec 2010 23:19:36 -0000 To: empire-db-commits@incubator.apache.org From: doebele@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20101202231936.73DBC23889B1@eris.apache.org> Author: doebele Date: Thu Dec 2 23:19:35 2010 New Revision: 1041635 URL: http://svn.apache.org/viewvc?rev=1041635&view=rev Log: EMPIREDB-91 Extended advanced example to demostrate the use of command params for prepared statements Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/config.xml incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/config.xml URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/config.xml?rev=1041635&r1=1041634&r2=1041635&view=diff ============================================================================== --- incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/config.xml (original) +++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/config.xml Thu Dec 2 23:19:35 2010 @@ -123,7 +123,11 @@ - + + + + + Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java?rev=1041635&r1=1041634&r2=1041635&view=diff ============================================================================== --- incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java (original) +++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java Thu Dec 2 23:19:35 2010 @@ -27,8 +27,8 @@ import java.util.logging.Logger; import org.apache.empire.commons.DateUtils; import org.apache.empire.commons.ErrorObject; import org.apache.empire.commons.Options; -import org.apache.empire.data.DataType; import org.apache.empire.data.DataMode; +import org.apache.empire.data.DataType; import org.apache.empire.db.DBCmdType; import org.apache.empire.db.DBColumnExpr; import org.apache.empire.db.DBCommand; @@ -38,6 +38,7 @@ 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.DBCommand.DBCommandParam; import org.apache.empire.db.derby.DBDatabaseDriverDerby; import org.apache.empire.db.h2.DBDatabaseDriverH2; import org.apache.empire.db.hsql.DBDatabaseDriverHSql; @@ -45,6 +46,7 @@ import org.apache.empire.db.mysql.DBData import org.apache.empire.db.oracle.DBDatabaseDriverOracle; import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL; import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL; +import org.apache.empire.samples.db.advanced.SampleAdvDB.EmployeeInfoView; public class SampleAdvApp @@ -91,7 +93,12 @@ public class SampleAdvApp // 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 ***"); @@ -120,52 +127,62 @@ public class SampleAdvApp // 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 idDevDep = insertDepartment(conn, "Development", "ITTK"); int idProdDep = insertDepartment(conn, "Production", "ITTK"); - int idSalDep = insertDepartment(conn, "Sales", "ITTK"); + int idSalDep = insertDepartment(conn, "Sales", "ITTK"); // Insert Employees - int idPers1 = insertEmployee(conn, "Peter", "Sharp", "M"); - int idPers2 = insertEmployee(conn, "Fred", "Bloggs", "M"); - int idPers3 = insertEmployee(conn, "Emma", "White", "F"); + int idEmp1 = insertEmployee(conn, "Peter", "Sharp", "M"); + int idEmp2 = insertEmployee(conn, "Fred", "Bloggs", "M"); + int idEmp3 = insertEmployee(conn, "Emma", "White", "F"); - insertEmpDepHistory(conn, idPers1, idDevDep, DateUtils.getDate(2005, 11, 1)); - insertEmpDepHistory(conn, idPers1, idProdDep, DateUtils.getDate(2006, 8, 1)); - insertEmpDepHistory(conn, idPers1, idSalDep, DateUtils.getDate(2007, 4, 15)); - - insertEmpDepHistory(conn, idPers2, idSalDep, DateUtils.getDate(2004, 2, 1)); - insertEmpDepHistory(conn, idPers2, idDevDep, DateUtils.getDate(2006, 10, 15)); - - insertEmpDepHistory(conn, idPers3, idDevDep, DateUtils.getDate(2004, 8, 15)); - insertEmpDepHistory(conn, idPers3, idSalDep, DateUtils.getDate(2005, 7, 1)); - insertEmpDepHistory(conn, idPers3, idProdDep, DateUtils.getDate(2006, 6, 15)); + insertEmpDepHistory(conn, idEmp1, idDevDep, DateUtils.getDate(2007, 11, 1)); + insertEmpDepHistory(conn, idEmp1, idProdDep, DateUtils.getDate(2008, 8, 1)); + insertEmpDepHistory(conn, idEmp1, idSalDep, DateUtils.getDate(2009, 4, 15)); + + insertEmpDepHistory(conn, idEmp2, idSalDep, DateUtils.getDate(2006, 2, 1)); + insertEmpDepHistory(conn, idEmp2, idDevDep, DateUtils.getDate(2008, 10, 15)); + + insertEmpDepHistory(conn, idEmp3, idDevDep, DateUtils.getDate(2006, 8, 15)); + insertEmpDepHistory(conn, idEmp3, idSalDep, DateUtils.getDate(2007, 7, 1)); + insertEmpDepHistory(conn, idEmp3, idProdDep, DateUtils.getDate(2008, 6, 15)); // 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: bulkReadRecords + // 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 employeeMap = bulkReadRecords(conn); - DBRecord rec = employeeMap.get(idPers2); + DBRecord rec = employeeMap.get(idEmp2); rec.setValue(db.T_EMPLOYEES.C_SALUTATION, "Mr."); rec.update(conn); - // STEP 9: bulkProcessRecords + // STEP 10: bulkProcessRecords + System.out.println("--------------------------------------------------------"); System.out.println("*** bulkProcessRecords: creates a checksum for every employee in the employees table ***"); bulkProcessRecords(conn); - // STEP 10: querySample + // STEP 11: querySample + System.out.println("--------------------------------------------------------"); System.out.println("*** querySample: shows how to use DBQuery class for subqueries and multi table records ***"); - querySample(conn, idPers2); + querySample(conn, idEmp2); - // STEP 11: ddlSample + // 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) { logger.info("As H2 does not support changing a table with a view defined we remove the view"); @@ -174,7 +191,7 @@ public class SampleAdvApp db.getDriver().getDDLScript(DBCmdType.DROP, db.V_EMPLOYEE_INFO, script); script.run(db.getDriver(), conn, false); } - ddlSample(conn, idPers2); + ddlSample(conn, idEmp2); if (db.getDriver() instanceof DBDatabaseDriverH2) { logger.info("And put back the view"); System.out.println("*** create EMPLOYEE_INFO_VIEW ***"); @@ -183,8 +200,8 @@ public class SampleAdvApp script.run(db.getDriver(), conn, false); } - // Done + System.out.println("--------------------------------------------------------"); System.out.println("DB Sample finished successfully."); } catch (Exception e) @@ -210,7 +227,7 @@ public class SampleAdvApp logger.info("Connecting to Database'" + config.getJdbcURL() + "' / User=" + config.getJdbcUser()); try { - // Connect to the databse + // Connect to the database Class.forName(config.getJdbcClass()).newInstance(); conn = DriverManager.getConnection(config.getJdbcURL(), config.getJdbcUser(), config.getJdbcPwd()); logger.info("Connected successfully"); @@ -230,7 +247,7 @@ public class SampleAdvApp /** * 
-     * Returns the correspondig DatabaseDriver for a given database provider / vendor
+     * Returns the corresponding DatabaseDriver for a given database provider / vendor
      * Valid Providers are "oracle", "sqlserver" and "hsqldb".
      *
*/ @@ -299,7 +316,7 @@ public class SampleAdvApp */ private static boolean databaseExists(Connection conn) { - // Check wether DB exists + // Check whether DB exists DBCommand cmd = db.createCommand(); cmd.select(T_DEP.count()); // Check using "select count(*) from DEPARTMENTS" @@ -404,6 +421,56 @@ public class SampleAdvApp } } + /* 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 + DBCommandParam genderParam = cmd.addCmdParam(); // Gender ('M' or 'F') + DBCommandParam curDepParam = cmd.addCmdParam(); // Current Department + // 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,
* then it calculates a checksum for every record
@@ -466,7 +533,7 @@ public class SampleAdvApp } return sum; } - + private static HashMap bulkReadRecords(Connection conn) { // Define the query Modified: incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java?rev=1041635&r1=1041634&r2=1041635&view=diff ============================================================================== --- incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java (original) +++ incubator/empire-db/trunk/empire-db-examples/empire-db-example-basic/src/main/java/org/apache/empire/samples/db/SampleApp.java Thu Dec 2 23:19:35 2010 @@ -27,7 +27,6 @@ import org.apache.empire.commons.ErrorOb import org.apache.empire.db.DBColumnExpr; import org.apache.empire.db.DBCommand; import org.apache.empire.db.DBDatabaseDriver; -import org.apache.empire.db.DBDriverFeature; import org.apache.empire.db.DBReader; import org.apache.empire.db.DBRecord; import org.apache.empire.db.DBSQLScript; @@ -36,7 +35,6 @@ import org.apache.empire.db.h2.DBDatabas import org.apache.empire.db.hsql.DBDatabaseDriverHSql; import org.apache.empire.db.mysql.DBDatabaseDriverMySQL; import org.apache.empire.db.oracle.DBDatabaseDriverOracle; -import org.apache.empire.db.oracle.OracleRowNumExpr; import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL; import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL; import org.apache.empire.xml.XMLWriter; @@ -89,7 +87,9 @@ public class SampleApp // STEP 3: Open Database (and create if not existing) System.out.println("*** Step 3: openDatabase() ***"); try { - db.open(driver, conn); + // Open the database + db.open(driver, conn); + // Check whether database exists databaseExists(conn); System.out.println("*** Database already exists. Skipping Step4 ***");