EMPIREDB-362 SampleAdvApp rework
diff --git a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
index 291bb32..f9b7981 100644
--- a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
+++ b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvApp.java
@@ -25,14 +25,14 @@
import java.sql.DriverManager;
import java.time.LocalDate;
import java.util.List;
+import java.util.Locale;
+import org.apache.empire.commons.DateUtils;
import org.apache.empire.commons.ObjectUtils;
-import org.apache.empire.commons.Options;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
import org.apache.empire.data.list.DataListEntry;
import org.apache.empire.db.DBCmdParam;
-import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBDDLGenerator.DDLActionType;
import org.apache.empire.db.DBQuery;
@@ -46,6 +46,7 @@
import org.apache.empire.db.expr.compare.DBCompareExpr;
import org.apache.empire.dbms.DBMSHandler;
import org.apache.empire.samples.db.advanced.db.CarSalesDB;
+import org.apache.empire.samples.db.advanced.db.CarSalesDB.DealerRating;
import org.apache.empire.samples.db.advanced.db.CarSalesDB.DealershipType;
import org.apache.empire.samples.db.advanced.db.CarSalesDB.EngineType;
import org.apache.empire.samples.db.advanced.db.DealerSalesView;
@@ -67,12 +68,6 @@
private final CarSalesDB carSales = new CarSalesDB();
private SampleContext context;
-
- // Shortcuts
- private final SampleAdvDB db = new SampleAdvDB();
- private SampleAdvDB.Employees T_EMP = db.T_EMPLOYEES;
- private SampleAdvDB.Departments T_DEP = db.T_DEPARTMENTS;
- private SampleAdvDB.EmployeeDepartmentHistory T_EDH = db.T_EMP_DEP_HIST;
/**
* <PRE>
@@ -134,14 +129,16 @@
context.commit();
}
- // do simple stuff
- simpleUpdateDemo();
-
-
+ // DQL-Demos
simpleQueryDemo();
queryViewDemo();
subqueryQueryDemo();
paramQueryDemo();
+
+ // DML-Demos
+ modelPriceIncrease();
+ modelPriceDecrease();
+ dealerRatingUpdate();
// Remember RollbackHandling
boolean prevRBHandling = context.isRollbackHandlingEnabled();
@@ -158,100 +155,8 @@
ddlDemo("Beadles Volkswagen", "www.group1auto.co.uk", "https://www.group1auto.co.uk/volkswagen/locations/beadles-volkswagen-dartford");
-
- /*
- // STEP 5: Clear Database (Delete all records)
- System.out.println("*** Step 5: clearDatabase() ***");
- clearDatabase();
-
- // STEP 6: Insert Records
- // Insert Departments
- System.out.println("*** Step 6: inserting departments, employees and employee_department_history records ***");
- int idDevDep = insertDepartment("Development", "ITTK");
- int idProdDep = insertDepartment("Production", "ITTK");
- int idSalDep = insertDepartment("Sales", "ITTK");
-
- // Insert Employees
- int idEmp1 = insertEmployee("Peter", "Sharp", "M");
- int idEmp2 = insertEmployee("Fred", "Bloggs", "M");
- int idEmp3 = insertEmployee("Emma", "White", "F");
-
- // Insert History as batch
- DBSQLScript batch = new DBSQLScript(context);
- 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();
-
- // commit
- context.commit();
-
- // STEP 7: read from Employee_Info_View
- System.out.println("--------------------------------------------------------");
- System.out.println("*** read from EMPLOYEE_INFO_VIEW ***");
- DBCommand cmd = context.createCommand();
- cmd.select (db.V_EMPLOYEE_INFO.getColumns());
- cmd.orderBy(db.V_EMPLOYEE_INFO.C_NAME_AND_DEP);
- printQueryResults(cmd);
-
- // 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(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.set(db.T_EMPLOYEES.C_SALUTATION, "Mr.");
- rec.update();
-
- // STEP 10: bulkProcessRecords
- System.out.println("--------------------------------------------------------");
- System.out.println("*** bulkProcessRecords: creates a checksum for every employee in the employees table ***");
- bulkProcessRecords();
-
- // STEP 11: querySample
- System.out.println("--------------------------------------------------------");
- System.out.println("*** querySample: shows how to use DBQuery class for subqueries and multi table records ***");
- querySample(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.getDbms() instanceof DBMSHandlerH2) {
- 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(context);
- db.getDbms().getDDLScript(DDLActionType.DROP, db.V_EMPLOYEE_INFO, script);
- script.executeAll();
- }
- ddlSample(idEmp2);
- if (db.getDbms() instanceof DBMSHandlerH2) {
- log.info("And put back the view");
- System.out.println("*** create EMPLOYEE_INFO_VIEW ***");
- DBSQLScript script = new DBSQLScript(context);
- db.getDbms().getDDLScript(DDLActionType.CREATE, db.V_EMPLOYEE_INFO, script);
- script.executeAll();
- }
-
- // STEP 13: delete records
- System.out.println("--------------------------------------------------------");
- System.out.println("*** deleteRecordSample: shows how to delete records (with and without cascade) ***");
- deleteRecordSample(idEmp3, idSalDep);
- */
-
// Done
- System.out.println("--------------------------------------------------------");
- System.out.println("DB Sample Advanced finished successfully.");
+ log.info("DB Sample Advanced finished successfully.");
}
public void populateDatabase()
@@ -264,23 +169,24 @@
// Add some models
ModelRecord model = new ModelRecord(context);
- model.insert(brandVW, "Golf", "Golf Style 1,5 l TSI", "Style", EngineType.P, 130, 30970d);
- model.insert(brandVW, "Golf", "Golf R-Line 2,0 l TSI 4MOTION","R-Line", EngineType.P, 190, 38650d);
- model.insert(brandVW, "Tiguan", "Tiguan Life 1,5 l TSI", "Life", EngineType.P, 150, 32545d);
- model.insert(brandVW, "Tiguan", "Tiguan Elegance 2,0 l TDI SCR","Elegance", EngineType.D, 150, 40845d);
- model.insert(brandVW, "Tiguan", "Tiguan R-Line 1,4 l eHybrid", "R-Line", EngineType.H, 150, 48090d);
+ model.insert(brandVW, "Golf", "Golf Style 1,5 l TSI", "Style", EngineType.P, 130, 30970d, LocalDate.of(2019, 10, 24));
+ model.insert(brandVW, "Golf", "Golf R-Line 2,0 l TSI 4MOTION","R-Line", EngineType.P, 190, 38650d, LocalDate.of(2019, 10, 24));
+ model.insert(brandVW, "Tiguan", "Tiguan Life 1,5 l TSI", "Life", EngineType.P, 150, 32545d, LocalDate.of(2016, 01, 01));
+ model.insert(brandVW, "Tiguan", "Tiguan Elegance 2,0 l TDI SCR","Elegance", EngineType.D, 150, 40845d, LocalDate.of(2016, 01, 01));
+ model.insert(brandVW, "Tiguan", "Tiguan R-Line 1,4 l eHybrid", "R-Line", EngineType.H, 150, 48090d, LocalDate.of(2016, 01, 01));
+ model.insert(brandVW, "Mulitvan", "Multivan 6.1 Highline 2.0TDI", "Highline", EngineType.D, 204, 84269d, LocalDate.of(2019, 10, 21));
// Tesla
- model.insert(brandTesla,"Model 3", "Model 3 LR", "Long Range", EngineType.E, 261, 45940d);
- model.insert(brandTesla,"Model 3", "Model 3 Performance", "Performance", EngineType.E, 487, 53940d);
- model.insert(brandTesla,"Model Y", "Model Y LR", "Long Range", EngineType.E, 345, 53940d);
- model.insert(brandTesla,"Model Y", "Model Y Performance", "Performance", EngineType.E, 450, 58940d);
- model.insert(brandTesla,"Model S", "Model S Plaid", "Plaid", EngineType.E, 1020,0d);
+ model.insert(brandTesla,"Model 3", "Model 3 LR", "Long Range", EngineType.E, 261, 45940d, LocalDate.of(2017, 07, 01));
+ model.insert(brandTesla,"Model 3", "Model 3 Performance", "Performance", EngineType.E, 487, 53940d, LocalDate.of(2017, 07, 01));
+ model.insert(brandTesla,"Model Y", "Model Y LR", "Long Range", EngineType.E, 345, 53940d, LocalDate.of(2020, 03, 13));
+ model.insert(brandTesla,"Model Y", "Model Y Performance", "Performance", EngineType.E, 450, 58940d, LocalDate.of(2020, 03, 13));
+ model.insert(brandTesla,"Model S", "Model S Plaid", "Plaid", EngineType.E, 1020, 0d, LocalDate.of(2021, 12, 01));
// Ford
- model.insert(brandFord, "Mustang", "Mustang GT 5,0 l Ti-VCT V8", "GT", EngineType.P, 449, 54300d);
- model.insert(brandFord, "Mustang", "Mustang Mach1 5,0 l Ti-VCT V8", "Mach1", EngineType.P, 460, 62800d);
+ model.insert(brandFord, "Mustang", "Mustang GT 5,0 l Ti-VCT V8", "GT", EngineType.P, 449, 54300d, LocalDate.of(2017, 01, 01));
+ model.insert(brandFord, "Mustang", "Mustang Mach1 5,0 l Ti-VCT V8", "Mach1", EngineType.P, 460, 62800d, LocalDate.of(2020, 10, 16));
// Toyota
- model.insert(brandToy, "Prius", "Prius Hybrid 1,8-l-VVT-i", "Basis", EngineType.H, 122, 38000d);
- model.insert(brandToy, "Supra", "GR Supra Pure 2,0 l Twin-Scroll Turbo","Pure", EngineType.P, 258, 49290d);
+ model.insert(brandToy, "Prius", "Prius Hybrid 1,8-l-VVT-i", "Basis", EngineType.H, 122, 38000d, LocalDate.of(2017, 01, 01));
+ model.insert(brandToy, "Supra", "GR Supra Pure 2,0 l Twin-Scroll Turbo","Pure", EngineType.P, 258, 49290d, LocalDate.of(2020, 03, 31));
// Add some dealers
DealerRecord dealerDE = (new DealerRecord(context)).insert("Autozentrum Schmitz", "Munich", "Germany");
@@ -526,62 +432,123 @@
}
- public void simpleUpdateDemo()
+ public void modelPriceIncrease()
{
// shortcuts (for convenience)
CarSalesDB.Brand BRAND = carSales.BRAND;
CarSalesDB.Model MODEL = carSales.MODEL;
// create command
- /*
- DBCommand cmd = context.createCommand()
- .set (MODEL.BASE_PRICE.to(55000)) // set the price-tag
- .join (MODEL.WMI, BRAND.WMI)
- .where(BRAND.NAME.is("Tesla"))
- .where(MODEL.NAME.is("Model 3").and(MODEL.TRIM.is("Performance")));
- */
-
- DBCommand sub = context.createCommand();
- sub.select(BRAND.WMI, BRAND.NAME);
- sub.where (BRAND.COUNTRY.is("Deutschland"));
- DBQuery qqry = new DBQuery(sub, "qt");
+ // Sales-Info
+ String salesInfo = "Price update "+DateUtils.formatDate(LocalDate.now(), Locale.US);
// create command
DBCommand cmd = context.createCommand()
// increase model base prices by 5%
- .select(MODEL.BASE_PRICE.multiplyWith(105).divideBy(100).round(0))
- .set (MODEL.BASE_PRICE.to(55225))
- .join (MODEL.WMI, BRAND.WMI) // , BRAND.NAME.is("Tesla")
- // .join (MODEL.WMI, qqry.column(BRAND.WMI), qqry.column(BRAND.NAME).like("V%"))
- // on all Volkswagen Tiguan with Diesel engine
- .where(BRAND.NAME.like("Volkswagen%"))
- .where(MODEL.NAME.is("Tiguan").and(MODEL.ENGINE_TYPE.is(EngineType.D)));
+ .select(BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE, MODEL.BASE_PRICE.multiplyWith(105).divideBy(100).round(2).as("NEW_PRICE"))
+ // set update fields
+ .set (MODEL.BASE_PRICE.to(MODEL.BASE_PRICE.multiplyWith(105).divideBy(100).round(2)))
+ .set (MODEL.SALES_INFO.to(salesInfo))
+ // join with BRANDS
+ .join (MODEL.WMI, BRAND.WMI) // , BRAND.NAME.is("Tesla")
+ // on all Volkswagen with Diesel engine
+ .where(BRAND.NAME.upper().like("VOLKSWAGEN"))
+ .where(MODEL.ENGINE_TYPE.is(EngineType.D)); // (MODEL.NAME.is("Tiguan").and(
+ // Preview the change
+ for (DataListEntry item : context.getUtils().queryDataList(cmd))
+ System.out.println(item.toString());
- String sql = cmd.getSelect();
- Object[] params = cmd.getParamValues();
- System.out.println(sql);
- System.out.println(StringUtils.arrayToString(params, "|"));
-
+ /*
// cmd.removeWhereConstraint(MODEL.NAME.is("Tiguan").and(MODEL.ENGINE_TYPE.is(EngineType.D)));
// cmd.removeWhereConstraintOn(BRAND.NAME);
sql = cmd.getUpdate();
params = cmd.getParamValues();
System.out.println(sql);
System.out.println(StringUtils.arrayToString(params, "|"));
+ */
- // execute Update statement
+ // Execute the change
int count = context.executeUpdate(cmd);
log.info("{} models affected", count);
-
- /*
- * Clone test
- DBCommand cl1 = cmd.clone();
- cl1.set(MODEL.BASE_PRICE.to(66000)); // set the price-tag
- cl1.where(BRAND.NAME.is("Foo"));
- log.info("cmd= {} params={}", cmd.getUpdate(), cmd.getParamValues());
- log.info("cmd= {} params={}", cl1.getUpdate(), cl1.getParamValues());
- */
+ context.commit();
+ }
+
+ public void modelPriceDecrease()
+ {
+ // shortcuts (for convenience)
+ CarSalesDB.Brand BRAND = carSales.BRAND;
+ CarSalesDB.Model MODEL = carSales.MODEL;
+ // create command
+ DBCommand cmd = context.createCommand()
+ .select(MODEL.getColumns())
+ .join (MODEL.WMI, BRAND.WMI)
+ .where(BRAND.NAME.upper().like("VOLKSWAGEN"))
+ .where(MODEL.ENGINE_TYPE.is(EngineType.D)); // (MODEL.NAME.is("Tiguan").and(
+ // Use DBReader to process query result
+ DBReader reader = new DBReader(context);
+ try
+ { // Open Reader
+ log.info("running modelPriceDecrease for cmd {}", cmd.getSelect());
+ reader.open(cmd);
+ // Print output
+ ModelRecord record = new ModelRecord(context);
+ // Disable rollback handling to improve performance
+ record.setRollbackHandlingEnabled(false);
+ BigDecimal factor = (new BigDecimal(1.05d)).setScale(2, RoundingMode.HALF_UP);
+ while (reader.moveNext())
+ { // Init updateable record
+ reader.initRecord(record);
+ // Calculate Base-Price
+ BigDecimal oldPrice = reader.getDecimal(MODEL.BASE_PRICE);
+ BigDecimal newPrice = oldPrice.divide(factor, 2, RoundingMode.HALF_UP);
+ record.set(MODEL.BASE_PRICE, newPrice);
+ // update
+ record.update();
+ }
+ // Done
+ context.commit();
+ } finally {
+ // always close Reader
+ reader.close();
+ }
+ }
+
+ private void dealerRatingUpdate()
+ {
+ CarSalesDB.Dealer DEALER = carSales.DEALER;
+ // Clear all dealer ratings
+ DBCommand cmd = context.createCommand()
+ .set(DEALER.RATING.to(DealerRating.X))
+ .where(DEALER.RATING.isNot(null));
+ context.executeUpdate(cmd);
+ context.commit();
+
+ // Subquery to find TOP 3 dealers
+ CarSalesDB.Sales SALES = carSales.SALES;
+ DBCommand qryCmd = context.createCommand()
+ .select(SALES.DEALER_ID)
+ .where(SALES.YEAR.is(LocalDate.now().getYear()-1))
+ .groupBy(SALES.DEALER_ID)
+ .orderBy(SALES.PRICE.sum().desc())
+ .limitRows(3);
+ DBQuery qryTop = new DBQuery(qryCmd, "qtop");
+
+ // Dealer-Query
+ cmd.clear();
+ cmd.join(DEALER.ID, qryTop.column(SALES.DEALER_ID));
+
+ int index = 0;
+ List<DealerRecord> list = context.getUtils().queryRecordList(cmd, DEALER, DealerRecord.class);
+ for (DealerRecord dealer : list)
+ {
+ DealerRating oldRating = dealer.getEnum(DEALER.RATING);
+ DealerRating newRating = DealerRating.values()[index++];
+ log.info("Dealer \"{}\" rating changed from {} to {}", dealer.getString(DEALER.COMPANY_NAME), oldRating, newRating);
+ dealer.set(DEALER.RATING, newRating);
+ dealer.update();
+ }
+ context.commit();
}
private void queryViewDemo()
@@ -685,70 +652,6 @@
}
/**
- * 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 void bulkProcessRecords()
- {
- // Define the query
- DBCommand cmd = context.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(context);
- try
- {
- // Open Reader
- System.out.println("Running Query:");
- System.out.println(cmd.getSelect());
- reader.open(cmd);
- // Print output
- DBRecord record = new DBRecord(context, EMP);
- // Disable rollback handling to improve performance
- record.setRollbackHandlingEnabled(false);
- 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(record);
- // reader
- record.set(T_EMP.C_CHECKSUM, sum);
- record.update();
- }
- // Done
- context.commit();
-
- } finally
- {
- // always close Reader
- reader.close();
- }
- }
-
- private 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;
- }
-
- /**
* This method demonstrates how to add, modify and delete a database column.<BR>
* This function demonstrates the use of the {@link DBMSHandler#getDDLScript(org.apache.empire.db.DDLActionType, org.apache.empire.db.DBObject, DBSQLScript)}<BR>
*
@@ -795,61 +698,6 @@
}
/**
- * 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 void querySample(int employeeId)
- {
- // Define the sub query
- DBCommand subCmd = context.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 = context.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.column(T_EDH.C_EMPLOYEE_ID),
- T_EDH.C_DATE_FROM.is(Q_MAX_DATE.column(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);
-
- // Define an updateable query
- DBQuery Q_EMP_DEP = new DBQuery(cmd, T_EMP.C_EMPLOYEE_ID);
- DBRecord rec = new DBRecord(context, Q_EMP_DEP);
- // Modify and Update fields from both Employee and Department
- rec.read(employeeId)
- .set(T_EMP.C_PHONE_NUMBER, "0815-4711")
- .set(T_DEP.C_BUSINESS_UNIT, "AUTO")
- .update();
- // Successfully updated
- System.out.println("The employee has been sucessfully updated");
- }
-
-
- /**
* testTransactionCreate
* @param context
* @param idDep
@@ -873,10 +721,11 @@
model.create()
.set(MODEL.WMI , "WVW") // = Volkswagen
.set(MODEL.NAME , "ID.4")
- .set(MODEL.SPECIFICATION , "ID.4 Pro Performance 150 kW 77 kWh")
+ .set(MODEL.SPECIFICATION , "ID.4 Pro Performance 150 kW 77 kWh")
.set(MODEL.TRIM , "Pro")
.set(MODEL.ENGINE_TYPE , EngineType.E)
- .set(MODEL.ENGINE_POWER , 204);
+ .set(MODEL.ENGINE_POWER , 204)
+ .set(MODEL.FIRST_SALE , LocalDate.of(2021, 04, 04));
// State and timestampe before and after insert
log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP));
model.update();
@@ -1009,7 +858,6 @@
* 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 void printQueryResults(DBCommand cmd)
{
// Query Records and print output
@@ -1058,6 +906,7 @@
reader.close();
}
}
+ */
}
diff --git a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvDB.java b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvDB.java
deleted file mode 100644
index 7a822e8..0000000
--- a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/SampleAdvDB.java
+++ /dev/null
@@ -1,359 +0,0 @@
-/*
- * 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.SQLException;
-
-import org.apache.empire.commons.Options;
-import org.apache.empire.data.DataType;
-import org.apache.empire.db.DBColumn;
-import org.apache.empire.db.DBColumnExpr;
-import org.apache.empire.db.DBCommand;
-import org.apache.empire.db.DBCommandExpr;
-import org.apache.empire.db.DBContext;
-import org.apache.empire.db.DBDatabase;
-import org.apache.empire.db.DBSQLScript;
-import org.apache.empire.db.DBTableColumn;
-import org.apache.empire.db.generic.TDatabase;
-import org.apache.empire.db.generic.TTable;
-import org.apache.empire.db.generic.TView;
-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.postgresql.DBMSHandlerPostgreSQL;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-
-/**
- * <PRE>
- * This file contains the definition of the data model in Java.
- * The SampleDB data model consists of two tables and a foreign key relation.
- * The tables are defined as nested classes here, but you may put the in separate files if you want.
- *
- * PLEASE NOTE THE NAMING CONVENTION:
- * Since all tables, views and columns are declared as "final" constants they are all in upper case.
- * We recommend using a prefix of T_ for tables and C_ for columns in order to keep them together
- * when listed in your IDE's code completion.
- * There is no need to stick to this convention but it makes life just another little bit easier.
- *
- * You may declare other database tables or views in the same way.
- * </PRE>
- */
-public class SampleAdvDB extends TDatabase<SampleAdvDB>
-{
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- // Logger
- private static final Logger log = LoggerFactory.getLogger(SampleAdvDB.class);
-
- /**
- * This class represents the definition of the Departments table.
- */
- public static class Departments extends TTable<SampleAdvDB>
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- public final DBTableColumn C_DEPARTMENT_ID;
- public final DBTableColumn C_NAME;
- public final DBTableColumn C_HEAD;
- public final DBTableColumn C_BUSINESS_UNIT;
- public final DBTableColumn C_UPDATE_TIMESTAMP;
-
- public Departments(SampleAdvDB db)
- {
- super("DEPARTMENTS", db);
- // ID
- C_DEPARTMENT_ID = addColumn("DEPARTMENT_ID", DataType.AUTOINC, 0, true, "DEP_ID_SEQUENCE");
- C_NAME = addColumn("NAME", DataType.VARCHAR, 80, true);
- C_HEAD = addColumn("HEAD", DataType.VARCHAR, 80, false);
- C_BUSINESS_UNIT = addColumn("BUSINESS_UNIT", DataType.VARCHAR, 4, true, "ITTK");
- C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP, 0, true);
-
- // Primary Key
- setPrimaryKey(C_DEPARTMENT_ID);
- // Set other Indexes
- addIndex("DEARTMENT_NAME_IDX", true, new DBColumn[] { C_NAME });
- }
- }
-
- /**
- * This class represents the definition of the Employees table.
- */
- public static class Employees extends TTable<SampleAdvDB>
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- public final DBTableColumn C_EMPLOYEE_ID;
- public final DBTableColumn C_SALUTATION;
- public final DBTableColumn C_FIRSTNAME;
- public final DBTableColumn C_LASTNAME;
- public final DBTableColumn C_DATE_OF_BIRTH;
- public final DBTableColumn C_GENDER;
- public final DBTableColumn C_PHONE_NUMBER;
- public final DBTableColumn C_EMAIL;
- public final DBTableColumn C_RETIRED;
- public final DBTableColumn C_CHECKSUM;
- public final DBTableColumn C_UPDATE_TIMESTAMP;
-
- // Useful column expressions
- public final DBColumnExpr C_FULLNAME;
-
- public Employees(SampleAdvDB db)
- {
- super("EMPLOYEES", db);
- // ID
- C_EMPLOYEE_ID = addColumn("EMPLOYEE_ID", DataType.AUTOINC, 0, true, "EMPLOYEE_ID_SEQUENCE");
- C_SALUTATION = addColumn("SALUTATION", DataType.VARCHAR, 20, false);
- C_FIRSTNAME = addColumn("FIRSTNAME", DataType.VARCHAR, 40, true);
- C_LASTNAME = addColumn("LASTNAME", DataType.VARCHAR, 40, true);
- C_DATE_OF_BIRTH = addColumn("DATE_OF_BIRTH", DataType.DATE, 0, false);
- C_GENDER = addColumn("GENDER", DataType.VARCHAR, 1, false);
- C_PHONE_NUMBER = addColumn("PHONE_NUMBER", DataType.VARCHAR, 40, false);
- C_EMAIL = addColumn("EMAIL", DataType.VARCHAR, 80, false);
- C_RETIRED = addColumn("RETIRED", DataType.BOOL, 0, true, false);
- C_CHECKSUM = addColumn("CHECKSUM", DataType.INTEGER, 0, false);
- C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP, 0, true);
-
- // Primary Key
- setPrimaryKey(C_EMPLOYEE_ID);
- // Set other Indexes
- addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { C_FIRSTNAME, C_LASTNAME, C_DATE_OF_BIRTH });
-
- // Create Options for GENDER column
- Options genders = new Options();
- genders.set("M", "Male");
- genders.set("F", "Female");
- C_GENDER.setOptions(genders);
-
- // Define Column Expressions
- C_FULLNAME = C_LASTNAME.append(", ").append(C_FIRSTNAME).as("FULL_NAME");
-
- }
- }
-
- /**
- * This class represents the definition of the Departments table.
- */
- public static class EmployeeDepartmentHistory extends TTable<SampleAdvDB>
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- public final DBTableColumn C_EMPLOYEE_ID;
- public final DBTableColumn C_DEPARTMENT_ID;
- public final DBTableColumn C_DATE_FROM;
-
- public EmployeeDepartmentHistory(SampleAdvDB db)
- {
- super("EMPLOYEE_DEPARTMENT_HIST", db);
- // ID
- C_EMPLOYEE_ID = addColumn("EMPLOYEE_ID", DataType.INTEGER, 0, true);
- C_DEPARTMENT_ID = addColumn("DEPARTMENT_ID", DataType.INTEGER, 0, true);
- C_DATE_FROM = addColumn("DATE_FROM", DataType.DATE, 0, true);
-
- // Primary Key
- setPrimaryKey(C_EMPLOYEE_ID, C_DATE_FROM);
- }
- }
-
- /**
- * This class represents the definition of the EmployeeDepSinceView table.
- */
- public static class EmployeeDepSinceView extends TView<SampleAdvDB>
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- public final DBViewColumn C_EMPLOYEE_ID;
- public final DBViewColumn C_MAX_DATE_FROM;
-
- public EmployeeDepSinceView(SampleAdvDB db, EmployeeDepartmentHistory T_EDH)
- {
- super("EMPLOYEE_DEP_SINCE_VIEW", db);
- // ID
- C_EMPLOYEE_ID = addColumn(T_EDH.C_EMPLOYEE_ID);
- C_MAX_DATE_FROM = addColumn("MAX_DATE_FROM", T_EDH.C_DATE_FROM);
-
- // set Key-column (if any)
- setKeyColumn(C_EMPLOYEE_ID);
- }
-
- @Override
- public DBCommandExpr createCommand()
- {
- /* Sample DDL for this View:
- CREATE VIEW EMPLOYEE_DEP_SINCE_VIEW (EMPLOYEE_ID, MAX_DATE_FROM)
- AS (SELECT t3.EMPLOYEE_ID, max(t3.DATE_FROM)
- FROM EMPLOYEE_DEPARTMENT_HIST t3
- GROUP BY t3.EMPLOYEE_ID);
- */
-
- SampleAdvDB.EmployeeDepartmentHistory EDH = DB.T_EMP_DEP_HIST;
-
- // Define the sub query
- DBCommand cmd = newCommand();
- cmd.select (EDH.C_EMPLOYEE_ID, EDH.C_DATE_FROM.max());
- cmd.groupBy(EDH.C_EMPLOYEE_ID);
- return cmd;
- }
- }
-
- /**
- * This class represents the definition of the EmployeeInfoView table.
- */
- public static class EmployeeInfoView extends TView<SampleAdvDB>
- {
- // *Deprecated* private static final long serialVersionUID = 1L;
-
- public final DBViewColumn C_EMPLOYEE_ID;
- public final DBViewColumn C_CURRENT_DEP_ID;
- public final DBViewColumn C_NAME_AND_DEP;
-
- public EmployeeInfoView(SampleAdvDB db, Employees T_EMP, Departments T_DEP)
- {
- super("EMPLOYEE_INFO_VIEW", db);
- // ID
- C_EMPLOYEE_ID = addColumn(T_EMP.C_EMPLOYEE_ID);
- C_CURRENT_DEP_ID = addColumn("CURRENT_DEP_ID", T_DEP.C_DEPARTMENT_ID);
- C_NAME_AND_DEP = addColumn("NAME_AND_DEP", DataType.VARCHAR);
-
- // set Key-column (if any)
- setKeyColumn(C_EMPLOYEE_ID);
- }
-
- @Override
- public DBCommandExpr createCommand()
- {
- /* Sample DDL for this View:
- CREATE VIEW EMPLOYEE_INFO_VIEW (EMPLOYEE_ID, CURRENT_DEP_ID, NAME_AND_DEP)
- AS (SELECT t2.EMPLOYEE_ID, t1.DEPARTMENT_ID, t2.LASTNAME + ', ' + coalesce(t2.FIRSTNAME, '') + ' (' + t1.NAME + ')'
- FROM EMPLOYEE_DEPARTMENT_HIST t3
- INNER JOIN EMPLOYEE_DEP_SINCE_VIEW v1 ON v1.EMPLOYEE_ID = t3.EMPLOYEE_ID AND t3.DATE_FROM=v1.MAX_DATE_FROM
- INNER JOIN EMPLOYEES t2 ON t2.EMPLOYEE_ID = t3.EMPLOYEE_ID
- INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = t3.DEPARTMENT_ID);
- */
-
- SampleAdvDB.Employees EMP = DB.T_EMPLOYEES;
- SampleAdvDB.EmployeeDepartmentHistory EDH = DB.T_EMP_DEP_HIST;
- SampleAdvDB.EmployeeDepSinceView EDS = DB.V_EMP_DEP_SINCE_VIEW;
- SampleAdvDB.Departments DEP = DB.T_DEPARTMENTS;
-
- // Define the query
- DBCommand cmd = newCommand();
- // Select required columns
- cmd.select(EMP.C_EMPLOYEE_ID);
- cmd.select(DEP.C_DEPARTMENT_ID);
- cmd.select(EMP.C_LASTNAME.append(", ")
- .append(EMP.C_FIRSTNAME.coalesce(DBDatabase.EMPTY_STRING))
- .append(" (").append(DEP.C_NAME).append(")"));
- // Set Joins
- cmd.join(EDH.C_EMPLOYEE_ID, EDS.C_EMPLOYEE_ID, EDH.C_DATE_FROM.is(EDS.C_MAX_DATE_FROM));
- cmd.join(EMP.C_EMPLOYEE_ID, EDH.C_EMPLOYEE_ID);
- cmd.join(DEP.C_DEPARTMENT_ID, EDH.C_DEPARTMENT_ID);
- // done
- return cmd;
- }
- }
-
- // Declare all Tables
- public final Departments T_DEPARTMENTS = new Departments(this);
- public final Employees T_EMPLOYEES = new Employees(this);
- public final EmployeeDepartmentHistory T_EMP_DEP_HIST = new EmployeeDepartmentHistory(this);
- // Declare all Views
- public final EmployeeDepSinceView V_EMP_DEP_SINCE_VIEW;
- public final EmployeeInfoView V_EMPLOYEE_INFO;
-
- /**
- * Constructor of the SampleDB data model description
- *
- * Put all foreign key relations here.
- */
- public SampleAdvDB()
- {
- // Create views
- V_EMP_DEP_SINCE_VIEW = new EmployeeDepSinceView(this, T_EMP_DEP_HIST);
- V_EMPLOYEE_INFO = new EmployeeInfoView(this, T_EMPLOYEES, T_DEPARTMENTS);
-
- // Define Foreign-Key Relations
- addRelation( T_EMP_DEP_HIST.C_EMPLOYEE_ID .referenceOn( T_EMPLOYEES.C_EMPLOYEE_ID )).onDeleteCascade();
- addRelation( T_EMP_DEP_HIST.C_DEPARTMENT_ID.referenceOn( T_DEPARTMENTS.C_DEPARTMENT_ID ));
- }
-
- @Override
- public void open(DBContext context)
- {
- // Enable prepared statements
- setPreparedStatementsEnabled(true);
- // Check exists
- if (checkExists(context))
- { // attach to driver
- super.open(context);
- // yes, it exists, then check the model
- checkDataModel(context);
- }
- else
- { // PostgreSQL does not support DDL in transaction
- DBMSHandler dbms = context.getDbms();
- if (dbms instanceof DBMSHandlerPostgreSQL)
- setAutoCommit(context, true);
- // create the database
- createDatabase(context);
- // PostgreSQL does not support DDL in transaction
- if (dbms instanceof DBMSHandlerPostgreSQL)
- setAutoCommit(context, false);
- // attach to driver
- super.open(context);
- }
- }
-
- private void createDatabase(DBContext context)
- {
- // create DDL for Database Definition
- DBSQLScript script = new DBSQLScript(context);
- getCreateDDLScript(script);
- // Show DDL Statement
- log.info(script.toString());
- // Execute Script
- script.executeAll(false);
- // Commit
- context.commit();
- }
-
- private void checkDataModel(DBContext context)
- { try {
- DBModelChecker modelChecker = context.getDbms().createModelChecker(this);
- // Check data model
- log.info("Checking DataModel for {} using {}", getClass().getSimpleName(), modelChecker.getClass().getSimpleName());
- // dbo schema
- DBModelErrorLogger logger = new DBModelErrorLogger();
- modelChecker.checkModel(this, 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!");
- }
- }
-
- private void setAutoCommit(DBContext context, boolean enable)
- { try {
- context.getConnection().setAutoCommit(enable);
- } catch (SQLException e) {
- log.error("Unable to set AutoCommit on Connection", e);
- }
- }
-
-}
diff --git a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/db/CarSalesDB.java b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/db/CarSalesDB.java
index a378dc3..30d32b2 100644
--- a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/db/CarSalesDB.java
+++ b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/db/CarSalesDB.java
@@ -20,6 +20,7 @@
import java.sql.SQLException;
+import org.apache.empire.commons.EnumValue;
import org.apache.empire.data.DataType;
import org.apache.empire.db.DBContext;
import org.apache.empire.db.DBSQLScript;
@@ -59,7 +60,7 @@
public enum EngineType
{
P("Petrol"),
- D("Diese"),
+ D("Diesel"),
H("Hybrid"),
E("Electric");
@@ -99,6 +100,34 @@
}
/**
+ * Dealer Rating
+ */
+ public enum DealerRating implements EnumValue
+ {
+ A,
+ B,
+ C,
+ X;
+
+ /**
+ * Override to use Minus '-' instead of 'X' in Database
+ */
+ @Override
+ public Object toValue(boolean numeric)
+ {
+ if (this==X)
+ return "-";
+ return (numeric ? ordinal() : name());
+ }
+
+ @Override
+ public String toString()
+ {
+ return name();
+ }
+ }
+
+ /**
* This class represents the Brand table.
*/
public static class Brand extends TTable<CarSalesDB>
@@ -135,6 +164,11 @@
public final DBTableColumn ENGINE_TYPE;
public final DBTableColumn ENGINE_POWER;
public final DBTableColumn BASE_PRICE;
+ public final DBTableColumn FIRST_SALE;
+ public final DBTableColumn LAST_SALE;
+ public final DBTableColumn MODEL_BINARY;
+ public final DBTableColumn MODEL_XML;
+ public final DBTableColumn SALES_INFO;
public final DBTableColumn UPDATE_TIMESTAMP;
public Model(CarSalesDB db)
@@ -150,6 +184,11 @@
ENGINE_TYPE = addColumn ("ENGINE_TYPE", DataType.CHAR, 1, true, EngineType.class);
ENGINE_POWER = addColumn ("ENGINE_POWER", DataType.DECIMAL, 4.0, true);
BASE_PRICE = addColumn ("BASE_PRICE", DataType.DECIMAL, 8.2, false);
+ FIRST_SALE = addColumn ("FIRST_SALE", DataType.DATE, 0, true);
+ LAST_SALE = addColumn ("LAST_SALE", DataType.DATE, 0, false);
+ MODEL_BINARY = addColumn ("MODEL_BINARY", DataType.BLOB, 0, false);
+ MODEL_XML = addColumn ("MODEL_XML", DataType.CLOB, 0, false);
+ SALES_INFO = addColumn ("SALES_INFO", DataType.VARCHAR, 80, false);
UPDATE_TIMESTAMP= addTimestamp ("UPDATE_TIMESTAMP");
// Primary Key (automatically set due to addIdentity()) otherwise use
@@ -168,6 +207,7 @@
public final DBTableColumn CITY;
public final DBTableColumn COUNTRY;
public final DBTableColumn YEAR_FOUNDED;
+ public final DBTableColumn RATING;
public final DBTableColumn UPDATE_TIMESTAMP;
public Dealer(CarSalesDB db)
@@ -181,6 +221,7 @@
CITY = addColumn ("CITY", DataType.VARCHAR, 20, true);
COUNTRY = addColumn ("COUNTRY", DataType.VARCHAR, 40, true);
YEAR_FOUNDED = addColumn ("YEAR_FOUNDED", DataType.DECIMAL, 4.0, false);
+ RATING = addColumn ("RATING", DataType.CHAR, 1, true, DealerRating.X);
UPDATE_TIMESTAMP= addTimestamp("UPDATE_TIMESTAMP");
// Primary Key (automatically set due to addIdentity()) otherwise use
diff --git a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/records/ModelRecord.java b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/records/ModelRecord.java
index 446bb72..31bc745 100644
--- a/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/records/ModelRecord.java
+++ b/empire-db-examples/empire-db-example-advanced/src/main/java/org/apache/empire/samples/db/advanced/records/ModelRecord.java
@@ -1,12 +1,24 @@
package org.apache.empire.samples.db.advanced.records;
+import java.io.ByteArrayOutputStream;
+import java.io.IOException;
+import java.io.ObjectOutputStream;
+import java.io.StringWriter;
+import java.time.LocalDate;
+
import org.apache.empire.db.generic.TRecord;
import org.apache.empire.samples.db.advanced.SampleContext;
import org.apache.empire.samples.db.advanced.db.CarSalesDB;
import org.apache.empire.samples.db.advanced.db.CarSalesDB.EngineType;
+import org.apache.empire.xml.XMLWriter;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.w3c.dom.Document;
public class ModelRecord extends TRecord<CarSalesDB.Model>
{
+ private static final Logger log = LoggerFactory.getLogger(ModelRecord.class);
+
private static final long serialVersionUID = 1L;
public ModelRecord(SampleContext context)
@@ -14,7 +26,8 @@
super(context, context.getDatabase().MODEL);
}
- public void insert(BrandRecord brand, String modelName, String configName, String trim, EngineType engineType, int enginePower, double basePrice)
+ public void insert(BrandRecord brand, String modelName, String configName, String trim
+ , EngineType engineType, int enginePower, double basePrice, LocalDate firstSale)
{
// T = RowSet (Table/View)
create();
@@ -25,6 +38,50 @@
set(T.ENGINE_TYPE , engineType);
set(T.ENGINE_POWER , enginePower);
set(T.BASE_PRICE , basePrice);
+ set(T.FIRST_SALE , firstSale);
update();
}
+
+ @Override
+ public void update()
+ {
+ if (isModified())
+ { // Record was modified
+ // Clear and reset MODEL_BINARY and MODEL_XML
+ set(T.MODEL_XML, null);
+ set(T.MODEL_BINARY, null);
+ // Reset
+ set(T.MODEL_XML, getModelXml());
+ set(T.MODEL_BINARY, getModelBinary());
+ }
+ super.update();
+ }
+
+ private String getModelXml()
+ {
+ Document modelDoc = this.getXmlDocument();
+ StringWriter writer = new StringWriter();
+ XMLWriter xmlWriter = new XMLWriter(writer, "utf-8");
+ xmlWriter.print(modelDoc);
+ return writer.toString();
+ }
+
+ private byte[] getModelBinary()
+ {
+ try
+ { // All fields, but without MODEL_BINARY
+ Object[] fields = this.getFields();
+ fields[getFieldIndex(T.MODEL_BINARY)]=null;
+ // Serialize the record fields
+ ByteArrayOutputStream baos = new ByteArrayOutputStream();
+ ObjectOutputStream oos = new ObjectOutputStream(baos);
+ oos.writeObject(fields);
+ return baos.toByteArray();
+ }
+ catch (IOException e)
+ {
+ log.error("Unable to serialize the record fields", e);
+ return null;
+ }
+ }
}
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
index 6883adb..9599bf0 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBCommand.java
@@ -30,8 +30,6 @@
import org.apache.empire.commons.StringUtils;
import org.apache.empire.commons.Unwrappable;
import org.apache.empire.data.DataType;
-import org.apache.empire.db.expr.column.DBAliasExpr;
-import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.compare.DBCompareAndOrExpr;
import org.apache.empire.db.expr.compare.DBCompareColExpr;
import org.apache.empire.db.expr.compare.DBCompareExpr;
@@ -45,7 +43,6 @@
import org.apache.empire.dbms.DBSqlPhrase;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ItemNotFoundException;
-import org.apache.empire.exceptions.NotSupportedException;
import org.apache.empire.exceptions.ObjectNotValidException;
import org.apache.empire.exceptions.UnspecifiedErrorException;
import org.slf4j.Logger;
@@ -61,172 +58,6 @@
implements Cloneable
{
// *Deprecated* private static final long serialVersionUID = 1L;
-
- /**
- * DBMergeCommand
- * @author rainer
- */
- protected static class DBMergeCommand extends DBCommand
- {
- private final DBCommand parent;
-
- protected DBMergeCommand(DBCommand parent)
- {
- super(parent.isAutoPrepareStmt());
- this.parent = parent;
- // set
- List<DBSetExpr> set = parent.getSetExpressions();
- if (set!=null)
- this.set = new ArrayList<DBSetExpr>(set);
- // joins
- List<DBJoinExpr> joins = parent.getJoins();
- if (joins!=null)
- this.joins = new ArrayList<DBJoinExpr>(joins);
- // where
- List<DBCompareExpr> where = parent.getWhereConstraints();
- if (where!=null)
- this.where = new ArrayList<DBCompareExpr>(where);
- // groupBy
- List<DBColumnExpr> groupBy = parent.getGroupBy();
- if (groupBy!=null)
- this.groupBy = new ArrayList<DBColumnExpr>(groupBy);
- // having
- List<DBCompareExpr> having = parent.getHavingConstraints();
- if (having!=null)
- this.having = new ArrayList<DBCompareExpr>(having);
- }
-
- @Override
- protected void notifyParamUsage(DBCmdParam param)
- {
- throw new NotSupportedException(this, "notifyParamUsage");
- }
-
- @Override
- protected void resetParamUsage()
- {
- /* Nothing */
- }
-
- @Override
- protected void completeParamUsage()
- {
- /* Nothing */
- }
-
- @Override
- protected void mergeSubqueryParams(Object[] subQueryParams)
- {
- parent.mergeSubqueryParams(subQueryParams);
- }
-
- @Override
- protected void addJoin(StringBuilder buf, DBJoinExpr join, long context, int whichParams)
- {
- parent.addJoin(buf, join, context, whichParams);
- }
-
- public List<DBSetExpr> addUsing(StringBuilder buf, DBRowSet table, DBColumnJoinExpr updateJoin)
- {
- buf.append("\r\nUSING ");
- // clearSelect();
- // clearOrderBy();
- DBRowSet outerTable = updateJoin.getOuterTable();
- if (outerTable==null)
- outerTable=table;
- Set<DBColumn> joinColumns = new HashSet<DBColumn>();
- updateJoin.addReferencedColumns(joinColumns);
- for (DBColumn jcol : joinColumns)
- { // Select join columns
- if (jcol.getRowSet().equals(outerTable)==false)
- select(jcol);
- }
- // find the source table
- DBColumnExpr left = updateJoin.getLeft();
- DBColumnExpr right = updateJoin.getRight();
- DBRowSet source = right.getUpdateColumn().getRowSet();
- if (source==table)
- source = left.getUpdateColumn().getRowSet();
- // Add set expressions
- String sourceAliasPrefix = source.getAlias()+".";
- List<DBSetExpr> mergeSet = new ArrayList<DBSetExpr>(set.size());
- for (DBSetExpr sex : set)
- { // Select set expressions
- Object val = sex.getValue();
- if (val instanceof DBColumnExpr)
- {
- DBColumnExpr expr = ((DBColumnExpr)val);
- if (!(expr instanceof DBColumn) && !(expr instanceof DBAliasExpr))
- { // rename column
- String name = "COL_"+String.valueOf(mergeSet.size());
- expr = expr.as(name);
- }
- // select
- select(expr);
- // Name
- DBValueExpr NAME_EXPR = getDatabase().getValueExpr(sourceAliasPrefix+expr.getName(), DataType.UNKNOWN);
- mergeSet.add(sex.getColumn().to(NAME_EXPR));
- }
- else
- { // add original
- mergeSet.add(sex);
- }
- }
- // remove join (if not necessary)
- if (hasConstraintOn(table)==false)
- removeJoinsOn(table);
- // add SQL for inner statement
- addSQL(buf, CTX_DEFAULT);
- // add Alias
- buf.append(" ");
- buf.append(source.getAlias());
- buf.append("\r\nON (");
- left.addSQL(buf, CTX_DEFAULT);
- buf.append(" = ");
- right.addSQL(buf, CTX_DEFAULT);
- // Compare Expression
- if (updateJoin.getWhere() != null)
- appendMergeConstraint(buf, table, updateJoin.getWhere());
- // More constraints
- for (DBCompareExpr cmpExpr : this.where)
- {
- appendMergeConstraint(buf, table, cmpExpr);
- }
- // done
- return mergeSet;
- }
-
- protected void appendMergeConstraint(StringBuilder buf, DBRowSet table, DBCompareExpr cmpExpr)
- {
- if (cmpExpr instanceof DBCompareColExpr)
- { // a compare column expression
- DBCompareColExpr cce = (DBCompareColExpr)cmpExpr;
- DBColumn ccecol = cce.getColumn().getUpdateColumn();
- if (table.isKeyColumn(ccecol)&& !isSetColumn(ccecol))
- { // yes, add
- buf.append(" AND ");
- cce.addSQL(buf, CTX_DEFAULT);
- }
- }
- // else if (cmpExpr instanceof DBCompareAndOrExpr)
- // else if (cmpExpr instanceof DBCompareNotExpr)
- else
- { // just add
- buf.append(" AND ");
- cmpExpr.addSQL(buf, CTX_DEFAULT);
- }
- }
-
- protected boolean isSetColumn(DBColumn col)
- {
- for (DBSetExpr se : this.set)
- {
- if (se.getColumn().equals(col))
- return true;
- }
- return false;
- }
- }
// Logger
protected static final Logger log = LoggerFactory.getLogger(DBCommand.class);
@@ -1465,16 +1296,6 @@
clearLimit();
resetParamUsage();
}
-
- /**
- * Create a special Merge-command
- * This forwards parameter usage to the parent command
- * @return the merge command
- */
- protected DBMergeCommand createMergeCommand()
- {
- return new DBMergeCommand(this);
- }
/**
* returns true if prepared statements are enabled for this command
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java b/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
index 6d32eda..bf1c688 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBDatabase.java
@@ -1088,8 +1088,8 @@
break;
default:
- if (log.isDebugEnabled())
- log.debug("No column validation has been implemented for data type " + type);
+ if (log.isTraceEnabled())
+ log.trace("No column validation has been implemented for data type " + type);
break;
}
return value;
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBTable.java b/empire-db/src/main/java/org/apache/empire/db/DBTable.java
index 556fa1d..d1f51a8 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBTable.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBTable.java
@@ -37,6 +37,7 @@
import org.apache.empire.db.exceptions.RecordUpdateFailedException;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ItemExistsException;
+import org.apache.empire.exceptions.NotSupportedException;
import org.apache.empire.exceptions.ObjectNotValidException;
import org.apache.empire.exceptions.UnexpectedReturnValueException;
import org.apache.empire.exceptions.UnspecifiedErrorException;
@@ -52,10 +53,10 @@
// *Deprecated* private static final long serialVersionUID = 1L;
// Integer size definitions
- public static final int DEFAULT = 0;
- public static final int SMALLINT = 2;
- public static final int MEDIUMINT = 4;
- public static final int BIGINT = 8;
+ public static final int INT_SIZE_DEFAULT = 0;
+ public static final int INT_SIZE_SMALL = 2;
+ public static final int INT_SIZE_MEDIUM = 4;
+ public static final int INT_SIZE_BIG = 8;
private static AtomicInteger tableCount = new AtomicInteger(0);
@@ -428,7 +429,7 @@
*/
public DBTableColumn addIdentity(String name, String seqName)
{
- return addColumn("ID", DataType.AUTOINC, 0, true, seqName);
+ return addColumn(name, DataType.AUTOINC, INT_SIZE_BIG, true, seqName);
}
/**
@@ -448,7 +449,7 @@
// Check key
DBColumn[] keyCols = target.getKeyColumns();
if (keyCols==null || keyCols.length!=1)
- throw new InvalidArgumentException("target", target);
+ throw new NotSupportedException(target, "addForeignKey");
// add column
DBTableColumn keyCol = (DBTableColumn)keyCols[0];
DataType keyDataType = keyCol.getDataType();
diff --git a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
index e65651b..1a29c3e 100644
--- a/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
+++ b/empire-db/src/main/java/org/apache/empire/db/DBUtils.java
@@ -76,6 +76,34 @@
}
/**
+ * Log Query Statement
+ * @param sqlCmd
+ * @param sqlParams
+ */
+ protected void logQueryStatement(String sqlCmd, Object[] sqlParams)
+ {
+ if (log.isDebugEnabled())
+ { log.debug("Executing DQL: " + sqlCmd);
+ if (sqlParams!=null && sqlParams.length>0)
+ log.debug("Parameters: " + StringUtils.arrayToString(sqlParams, "|"));
+ }
+ }
+
+ /**
+ * Log Update Statement
+ * @param sqlCmd
+ * @param sqlParams
+ */
+ protected void logUpdateStatement(String sqlCmd, Object[] sqlParams)
+ {
+ if (log.isInfoEnabled())
+ { log.info("Executing DML: " + sqlCmd);
+ if (sqlParams!=null && sqlParams.length>0)
+ log.info("Parameters: " + StringUtils.arrayToString(sqlParams, "|"));
+ }
+ }
+
+ /**
* Executes an update, insert or delete SQL-Statement.<BR>
* We recommend to use a DBCommand object in order to build the sqlCmd.<BR>
* <P>
@@ -88,8 +116,7 @@
{
try
{ // Debug
- if (log.isInfoEnabled())
- log.info("Executing: " + sqlCmd);
+ logUpdateStatement(sqlCmd, sqlParams);
// execute SQL
long start = System.currentTimeMillis();
int affected = dbms.executeSQL(sqlCmd, sqlParams, context.getConnection(), setGenKeys);
@@ -128,8 +155,7 @@
{
try
{ // Debug
- if (log.isDebugEnabled())
- log.debug("Executing: " + sqlCmd);
+ logQueryStatement(sqlCmd, sqlParams);
// Execute the Statement
long start = System.currentTimeMillis();
ResultSet rs = dbms.executeQuery(sqlCmd, sqlParams, scrollable, context.getConnection());
@@ -164,10 +190,9 @@
public Object querySingleValue(String sqlCmd, Object[] sqlParams, DataType dataType, boolean failOnNoResult)
{
// Debug
- long start = System.currentTimeMillis();
- if (log.isDebugEnabled())
- log.debug("Executing: " + sqlCmd);
+ logQueryStatement(sqlCmd, sqlParams);
// Read value
+ long start = System.currentTimeMillis();
Object result = dbms.querySingleValue(sqlCmd, sqlParams, dataType, context.getConnection());
if (result==ObjectUtils.NO_VALUE)
{ // Query returned no result
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
index be53a54..bc829a6 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/hsql/DBCommandHSql.java
@@ -18,10 +18,17 @@
*/
package org.apache.empire.dbms.hsql;
+import java.util.ArrayList;
import java.util.List;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBRowSet;
+import org.apache.empire.db.exceptions.NoPrimaryKeyException;
+import org.apache.empire.db.expr.column.DBAliasExpr;
+import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.join.DBColumnJoinExpr;
import org.apache.empire.db.expr.join.DBJoinExpr;
import org.apache.empire.db.expr.set.DBSetExpr;
@@ -88,27 +95,65 @@
@Override
protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
{
+ // The update table
+ DBColumn[] keyColumns = table.getKeyColumns();
+ if (keyColumns==null || keyColumns.length==0)
+ throw new NoPrimaryKeyException(table);
// Generate Merge expression
buf.setLength(0);
buf.append("MERGE INTO ");
table.addSQL(buf, CTX_FULLNAME|CTX_ALIAS);
- // join (only one allowed yet)
- DBColumnJoinExpr updateJoin = null;
- for (DBJoinExpr jex : joins)
- { // The join
- if (!(jex instanceof DBColumnJoinExpr))
- continue;
- if (jex.isJoinOn(table)==false)
- continue;
- // found the join
- updateJoin = (DBColumnJoinExpr)jex;
- break;
+ // Using
+ buf.append("\r\nUSING (");
+ // Add set expressions
+ List<DBColumnExpr> using = new ArrayList<DBColumnExpr>();
+ // Add key columns
+ for (DBColumn col : keyColumns)
+ using.add(col);
+ // Select Set-Expressions
+ List<DBSetExpr> mergeSet = new ArrayList<DBSetExpr>(set.size());
+ for (DBSetExpr sex : set)
+ { // Select set expressions
+ Object val = sex.getValue();
+ if (val instanceof DBColumnExpr)
+ {
+ DBColumnExpr expr = ((DBColumnExpr)val);
+ if (!(expr instanceof DBColumn) && !(expr instanceof DBAliasExpr))
+ { // rename column
+ String name = "COL_"+String.valueOf(mergeSet.size());
+ expr = expr.as(name);
+ }
+ // select
+ using.add(expr);
+ // Name
+ DBValueExpr NAME_EXPR = getDatabase().getValueExpr("q0."+expr.getName(), DataType.UNKNOWN);
+ mergeSet.add(sex.getColumn().to(NAME_EXPR));
+ }
+ else
+ { // add original
+ mergeSet.add(sex);
+ }
}
- if (updateJoin==null)
- throw new ObjectNotValidException(this);
- // using
- DBMergeCommand merge = createMergeCommand();
- List<DBSetExpr> mergeSet = merge.addUsing(buf, table, updateJoin);
+ // Add select
+ buf.append("SELECT ");
+ addListExpr(buf, using, CTX_ALL, ", ");
+ // From clause
+ addFrom(buf);
+ // Add Where
+ addWhere(buf);
+ // Add Grouping
+ addGrouping(buf);
+ // on
+ buf.append(") q0\r\nON (");
+ for (DBColumn col : keyColumns)
+ { // compare
+ buf.append(" q0.");
+ col.addSQL(buf, CTX_NAME);
+ buf.append("=");
+ buf.append(table.getAlias());
+ buf.append(".");
+ col.addSQL(buf, CTX_NAME);
+ }
// Set Expressions
buf.append(")\r\nWHEN MATCHED THEN UPDATE ");
buf.append("\r\nSET ");
diff --git a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
index 3791a1b..76885fd 100644
--- a/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
+++ b/empire-db/src/main/java/org/apache/empire/dbms/oracle/DBCommandOracle.java
@@ -18,15 +18,20 @@
*/
package org.apache.empire.dbms.oracle;
+import java.util.ArrayList;
import java.util.List;
import org.apache.empire.commons.StringUtils;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBIndex;
import org.apache.empire.db.DBRowSet;
+import org.apache.empire.db.exceptions.NoPrimaryKeyException;
+import org.apache.empire.db.expr.column.DBAliasExpr;
+import org.apache.empire.db.expr.column.DBValueExpr;
import org.apache.empire.db.expr.compare.DBCompareExpr;
-import org.apache.empire.db.expr.join.DBColumnJoinExpr;
-import org.apache.empire.db.expr.join.DBJoinExpr;
import org.apache.empire.db.expr.set.DBSetExpr;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.ObjectNotValidException;
@@ -259,27 +264,65 @@
@Override
protected void addUpdateWithJoins(StringBuilder buf, DBRowSet table)
{
+ // The update table
+ DBColumn[] keyColumns = table.getKeyColumns();
+ if (keyColumns==null || keyColumns.length==0)
+ throw new NoPrimaryKeyException(table);
// Generate Merge expression
buf.setLength(0);
buf.append("MERGE INTO ");
table.addSQL(buf, CTX_FULLNAME|CTX_ALIAS);
- // join (only one allowed yet)
- DBColumnJoinExpr updateJoin = null;
- for (DBJoinExpr jex : joins)
- { // The join
- if (!(jex instanceof DBColumnJoinExpr))
- continue;
- if (jex.isJoinOn(table)==false)
- continue;
- // found the join
- updateJoin = (DBColumnJoinExpr)jex;
- break;
+ // Using
+ buf.append("\r\nUSING (");
+ // Add set expressions
+ List<DBColumnExpr> using = new ArrayList<DBColumnExpr>();
+ // Add key columns
+ for (DBColumn col : keyColumns)
+ using.add(col);
+ // Select Set-Expressions
+ List<DBSetExpr> mergeSet = new ArrayList<DBSetExpr>(set.size());
+ for (DBSetExpr sex : set)
+ { // Select set expressions
+ Object val = sex.getValue();
+ if (val instanceof DBColumnExpr)
+ {
+ DBColumnExpr expr = ((DBColumnExpr)val);
+ if (!(expr instanceof DBColumn) && !(expr instanceof DBAliasExpr))
+ { // rename column
+ String name = "COL_"+String.valueOf(mergeSet.size());
+ expr = expr.as(name);
+ }
+ // select
+ using.add(expr);
+ // Name
+ DBValueExpr NAME_EXPR = getDatabase().getValueExpr("q0."+expr.getName(), DataType.UNKNOWN);
+ mergeSet.add(sex.getColumn().to(NAME_EXPR));
+ }
+ else
+ { // add original
+ mergeSet.add(sex);
+ }
}
- if (updateJoin==null)
- throw new ObjectNotValidException(this);
- // using
- DBMergeCommand merge = createMergeCommand();
- List<DBSetExpr> mergeSet = merge.addUsing(buf, table, updateJoin);
+ // Add select
+ buf.append("SELECT ");
+ addListExpr(buf, using, CTX_ALL, ", ");
+ // From clause
+ addFrom(buf);
+ // Add Where
+ addWhere(buf);
+ // Add Grouping
+ addGrouping(buf);
+ // on
+ buf.append(") q0\r\nON (");
+ for (DBColumn col : keyColumns)
+ { // compare
+ buf.append(" q0.");
+ col.addSQL(buf, CTX_NAME);
+ buf.append("=");
+ buf.append(table.getAlias());
+ buf.append(".");
+ col.addSQL(buf, CTX_NAME);
+ }
// Set Expressions
buf.append(")\r\nWHEN MATCHED THEN UPDATE ");
buf.append("\r\nSET ");
diff --git a/empire-db/src/main/java/org/apache/empire/xml/XMLWriter.java b/empire-db/src/main/java/org/apache/empire/xml/XMLWriter.java
index 3a2219e..21c8f31 100644
--- a/empire-db/src/main/java/org/apache/empire/xml/XMLWriter.java
+++ b/empire-db/src/main/java/org/apache/empire/xml/XMLWriter.java
@@ -163,7 +163,8 @@
public XMLWriter(Writer writer, String charsetEncoding)
{
this.out = new PrintWriter(writer);
- this.charsetEncoding = charsetEncoding;
+ if (charsetEncoding!=null)
+ this.charsetEncoding = charsetEncoding;
this.canonical = false;
}
