| /* |
| * 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.io.Serializable; |
| import java.math.BigDecimal; |
| import java.math.RoundingMode; |
| import java.sql.Connection; |
| 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.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.DBCommand; |
| import org.apache.empire.db.DBDDLGenerator.DDLActionType; |
| 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.ConstraintViolationException; |
| import org.apache.empire.db.exceptions.RecordUpdateFailedException; |
| import org.apache.empire.db.exceptions.StatementFailedException; |
| 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; |
| import org.apache.empire.samples.db.advanced.records.BrandRecord; |
| import org.apache.empire.samples.db.advanced.records.DealerRecord; |
| import org.apache.empire.samples.db.advanced.records.ModelRecord; |
| import org.slf4j.Logger; |
| import org.slf4j.LoggerFactory; |
| |
| |
| public class SampleAdvApp |
| { |
| // Logger |
| private static final Logger log = LoggerFactory.getLogger(SampleAdvApp.class); |
| |
| private static SampleAdvConfig config = new SampleAdvConfig(); |
| |
| |
| private final CarSalesDB carSales = new CarSalesDB(); |
| |
| private SampleContext context; |
| |
| /** |
| * <PRE> |
| * This is the entry point of the Empire-DB Sample Application |
| * Please check the config.xml configuration file for Database and Connection settings. |
| * </PRE> |
| * @param args arguments |
| */ |
| public static void main(String[] args) |
| { |
| // Init Configuration |
| config.init((args.length > 0 ? args[0] : "config.xml" )); |
| // create the app |
| SampleAdvApp app = new SampleAdvApp(); |
| try |
| { // Run |
| log.info("Running DB Sample..."); |
| app.run(); |
| // Done |
| log.info("DB Sample finished successfully."); |
| } catch (Exception e) { |
| // Error |
| log.error("Running SampleApp failed with Exception" + e.toString(), e); |
| if (app.context!=null) |
| app.context.rollback(); |
| } finally { |
| if (app.context!=null) |
| app.context.discard(); |
| } |
| } |
| |
| /** |
| * This method runs all the example code |
| */ |
| public void run() |
| { |
| // STEP 1: Get a JDBC Connection |
| System.out.println("*** Step 1: getJDBCConnection() ***"); |
| Connection conn = getJDBCConnection(); |
| |
| // STEP 2: Choose a dbms |
| System.out.println("*** Step 2: getDatabaseProvider() ***"); |
| DBMSHandler dbms = getDBMSHandler(config.getDatabaseProvider()); |
| |
| // STEP 2.2: Create a Context |
| context = new SampleContext(carSales, dbms, conn); |
| // set optional context features |
| context.setPreparedStatementsEnabled(true); |
| context.setRollbackHandlingEnabled(false); |
| |
| // STEP 3: Open Database (and create if not existing) |
| System.out.println("*** Step 3: openDatabase() ***"); |
| // db.open(context); |
| carSales.open(context); |
| if (carSales.wasCreated()) |
| { // newly created |
| populateDatabase(); |
| insertSalesUsingBatch(); |
| context.commit(); |
| } |
| |
| // DQL-Demos |
| simpleQueryDemo(); |
| queryViewDemo(); |
| subqueryQueryDemo(); |
| paramQueryDemo(); |
| |
| // DML-Demos |
| modelPriceIncrease(); |
| modelPriceDecrease(); |
| dealerRatingUpdate(); |
| |
| // Remember RollbackHandling |
| boolean prevRBHandling = context.isRollbackHandlingEnabled(); |
| try { |
| // commit pending operations (just to be sure) |
| context.commit(); |
| // run transaction demo |
| transactionDemo(); |
| } finally { |
| context.setRollbackHandlingEnabled(prevRBHandling); |
| } |
| |
| cascadeDeleteDemo(); |
| |
| ddlDemo("Beadles Volkswagen", "www.group1auto.co.uk", "https://www.group1auto.co.uk/volkswagen/locations/beadles-volkswagen-dartford"); |
| |
| // Done |
| log.info("DB Sample Advanced finished successfully."); |
| } |
| |
| public void populateDatabase() |
| { |
| // Add some brands |
| BrandRecord brandVW = (new BrandRecord(context)).insert("WVW", "Volkswagen", "Germany"); |
| BrandRecord brandFord = (new BrandRecord(context)).insert("1F", "Ford", "USA"); |
| BrandRecord brandTesla = (new BrandRecord(context)).insert("5YJ", "Tesla", "USA"); |
| BrandRecord brandToy = (new BrandRecord(context)).insert("JT", "Toyota", "Japan"); |
| |
| // Add some models |
| ModelRecord model = new ModelRecord(context); |
| 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, 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, 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, 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"); |
| DealerRecord dealerUK = (new DealerRecord(context)).insert("Beadles Volkswagen", "Dartford", "United Kingdom"); |
| DealerRecord dealerUS = (new DealerRecord(context)).insert("Auto Nation", "Los Angeles", "USA"); |
| DealerRecord dealerFR = (new DealerRecord(context)).insert("Vauban Motors Argenteuil", "Paris", "France"); |
| DealerRecord dealerIT = (new DealerRecord(context)).insert("Autorigoldi S.p.A.", "Milan", "Italy"); |
| |
| // Add brands to dealers |
| CarSalesDB.DealerBrands DB = carSales.DEALER_BRANDS; |
| DBRecord rec = new DBRecord(context, DB); |
| rec.create(DBRecord.key(dealerDE, brandTesla)).set(DB.DEALERSHIP_TYPE, DealershipType.B).update(); |
| rec.create(DBRecord.key(dealerDE, brandVW)) .set(DB.DEALERSHIP_TYPE, DealershipType.U).update(); |
| rec.create(DBRecord.key(dealerUK, brandVW)) .set(DB.DEALERSHIP_TYPE, DealershipType.B).update(); |
| rec.create(DBRecord.key(dealerUK, brandFord)) .set(DB.DEALERSHIP_TYPE, DealershipType.I).update(); |
| rec.create(DBRecord.key(dealerUS, brandFord)) .set(DB.DEALERSHIP_TYPE, DealershipType.B).update(); |
| rec.create(DBRecord.key(dealerUS, brandTesla)).set(DB.DEALERSHIP_TYPE, DealershipType.I).update(); |
| rec.create(DBRecord.key(dealerFR, brandToy)) .set(DB.DEALERSHIP_TYPE, DealershipType.B).update(); |
| rec.create(DBRecord.key(dealerIT, brandToy)) .set(DB.DEALERSHIP_TYPE, DealershipType.G).update(); |
| rec.create(DBRecord.key(dealerIT, brandVW)) .set(DB.DEALERSHIP_TYPE, DealershipType.B).update(); |
| |
| } |
| |
| private void insertSalesUsingBatch() |
| { |
| CarSalesDB db = this.carSales; |
| DBSQLScript batch = new DBSQLScript(context); |
| // get list of all dealers |
| DBCommand cmd = context.createCommand(); |
| cmd.select(db.DEALER.ID); |
| List<Long> dealerIdList = context.getUtils().querySimpleList(Long.class, cmd); |
| // get all models |
| cmd.clear(); |
| cmd.select(db.MODEL.ID, db.MODEL.SPECIFICATION, db.MODEL.BASE_PRICE); // select the ones we need (optional) |
| // no constraints on model |
| List<DataListEntry> modelList = context.getUtils().queryDataList(cmd); |
| for (DataListEntry model : modelList) |
| { |
| int count = generateRandomSales(batch, model, dealerIdList); |
| log.info("{} Sales added for Model {}", count, model.getString(db.MODEL.SPECIFICATION)); |
| } |
| // execute the batch |
| int count = batch.executeBatch(); |
| log.info("{} Sales added with batch", count); |
| } |
| |
| private int generateRandomSales(DBSQLScript batch, DataListEntry model, List<Long> dealerIdList) |
| { |
| CarSalesDB db = this.carSales; |
| BigDecimal price = model.getDecimal(db.MODEL.BASE_PRICE); |
| if (ObjectUtils.isEmpty(price)) |
| return 0; |
| int count = 0; |
| int baseYear = LocalDate.now().getYear()-3; |
| DBCommand cmd = context.createCommand(); |
| for (int i = (int)(Math.random()*99)+25; i>0; i--) |
| { |
| int dealIdx = (int)(Math.random()*dealerIdList.size()); |
| int year = (int)(Math.random()*3)+baseYear; |
| int month = (int)(Math.random()*12)+1; |
| BigDecimal variation = new BigDecimal((Math.random()*200) - 100.0); |
| variation = variation.setScale(2, RoundingMode.HALF_UP); |
| cmd.set(db.SALES.MODEL_ID.to(model.getRecordId(db.MODEL))) |
| .set(db.SALES.DEALER_ID.to(dealerIdList.get(dealIdx))) |
| .set(db.SALES.YEAR.to(year)) |
| .set(db.SALES.MONTH.to(month)) |
| .set(db.SALES.PRICE.to(price.add(variation))); |
| // add to batch |
| batch.addInsert(cmd); |
| count++; |
| } |
| return count; |
| } |
| |
| /** |
| * The result Bean for the query Example |
| * @author rainer |
| */ |
| public static class QueryResult implements Serializable |
| { |
| private static final long serialVersionUID = 1L; |
| |
| private String brand; |
| private String model; |
| private BigDecimal basePrice; |
| private int salesCount; |
| private BigDecimal avgSalesPrice; |
| private BigDecimal priceDifference; |
| |
| /** |
| * Fields constructor for QueryResult |
| * Must match the SELECT phrase and will be used by queryBeanList() |
| * @param brand |
| * @param model |
| * @param basePrice |
| * @param salesCount |
| * @param avgSalesPrice |
| * @param priceDifference |
| */ |
| public QueryResult(String brand, String model, BigDecimal basePrice |
| , int salesCount, BigDecimal avgSalesPrice, BigDecimal priceDifference) |
| { |
| this.brand = brand; |
| this.model = model; |
| this.basePrice = basePrice; |
| this.salesCount = salesCount; |
| this.avgSalesPrice = avgSalesPrice; |
| this.priceDifference = priceDifference; |
| } |
| |
| public String getBrand() |
| { |
| return brand; |
| } |
| |
| public String getModel() |
| { |
| return model; |
| } |
| |
| public BigDecimal getBasePrice() |
| { |
| return basePrice; |
| } |
| |
| public int getSalesCount() |
| { |
| return salesCount; |
| } |
| |
| public BigDecimal getAvgSalesPrice() |
| { |
| return avgSalesPrice; |
| } |
| |
| public BigDecimal getPriceDifference() |
| { |
| return priceDifference; |
| } |
| } |
| |
| public void subqueryQueryDemo() |
| { |
| // shortcuts (for convenience) |
| CarSalesDB.Model MODEL = carSales.MODEL; |
| CarSalesDB.Sales SALES = carSales.SALES; |
| |
| // create command |
| // DBColumnExpr SALES_COUNT = SALES.count(); |
| DBCommand sub = context.createCommand() |
| .selectQualified(SALES.MODEL_ID, SALES.count()) |
| .groupBy(SALES.MODEL_ID); |
| DBQuery q = new DBQuery(sub); |
| |
| // create command |
| DBCommand cmd = context.createCommand() |
| .select(MODEL.SPECIFICATION, q.column(SALES.count())) |
| .join(MODEL.ID, q.column(SALES.MODEL_ID)); |
| |
| List<DataListEntry> list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| { |
| System.out.println(dle.toString()); |
| } |
| |
| } |
| |
| public void simpleQueryDemo() |
| { |
| // shortcuts (for convenience) |
| CarSalesDB.Brand BRAND = carSales.BRAND; |
| CarSalesDB.Model MODEL = carSales.MODEL; |
| CarSalesDB.Sales SALES = carSales.SALES; |
| |
| // .selectQualified(BRAND.NAME, MODEL.CONFIG_NAME) |
| |
| // create a command |
| DBCommand cmd = context.createCommand() |
| .select (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE) |
| .select (SALES.MODEL_ID.count(), SALES.PRICE.avg()) |
| .select (SALES.PRICE.avg().minus(MODEL.BASE_PRICE.avg()).round(2).as("DIFFERENCE")) |
| .join (MODEL.WMI, BRAND.WMI) |
| .joinLeft(MODEL.ID, SALES.MODEL_ID, SALES.YEAR.is(2021)) // only year 2021 |
| .where (MODEL.ENGINE_TYPE.in(EngineType.P, EngineType.H, EngineType.E)) // Petrol, Hybrid, Electric |
| .where (MODEL.BASE_PRICE.isGreaterThan(30000)) |
| .groupBy (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE) |
| .having (SALES.MODEL_ID.count().isGreaterThan(5)) // more than 5 sales |
| .orderBy (BRAND.NAME.desc(), MODEL.SPECIFICATION.asc()); |
| |
| // Return a list of Java beans (needs matching fields constructor or setter methods) |
| // This is just one of several options to obtain an process query results |
| List<QueryResult> list = context.getUtils().queryBeanList(cmd, QueryResult.class, null); |
| log.info("queryBeanList returnes {} items", list.size()); |
| |
| /* |
| List<DataListEntry> list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| { |
| System.out.println(dle.toString()); |
| } |
| */ |
| /* |
| DataListEntry entry = context.getUtils().queryDataEntry(cmd); |
| for (int i=0; i<entry.getFieldCount(); i++) |
| log.info("col {} -> {}", entry.getColumn(i).getName(), entry.getColumn(i).getBeanPropertyName()); |
| */ |
| } |
| |
| public void paramQueryDemo() |
| { |
| // shortcuts (for convenience) |
| CarSalesDB.Brand BRAND = carSales.BRAND; |
| CarSalesDB.Model MODEL = carSales.MODEL; |
| |
| // create command |
| DBCommand cmd = context.createCommand(); |
| // create params |
| DBCmdParam brandParam = cmd.addParam(); |
| DBCmdParam engineTypeParam = cmd.addParam(); |
| |
| // create the command |
| cmd.select (BRAND.NAME, MODEL.SPECIFICATION, MODEL.BASE_PRICE, MODEL.ENGINE_TYPE, MODEL.ENGINE_POWER) |
| .join (MODEL.WMI, BRAND.WMI) |
| .where (BRAND.NAME.is(brandParam)) |
| .where (MODEL.ENGINE_TYPE.is(engineTypeParam)) |
| .orderBy (BRAND.NAME.desc(), MODEL.SPECIFICATION.asc()); |
| |
| // set the params |
| brandParam.setValue("Tesla"); |
| engineTypeParam.setValue(EngineType.E); |
| // and run |
| List<DataListEntry> list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| System.out.println(dle.toString()); |
| |
| // change params |
| brandParam.setValue("Volkswagen"); |
| engineTypeParam.setValue(EngineType.P); |
| // and run again |
| list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| System.out.println(dle.toString()); |
| |
| } |
| |
| public void modelPriceIncrease() |
| { |
| // shortcuts (for convenience) |
| CarSalesDB.Brand BRAND = carSales.BRAND; |
| CarSalesDB.Model MODEL = carSales.MODEL; |
| // create command |
| |
| // 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(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()); |
| |
| /* |
| // 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 the change |
| int count = context.executeUpdate(cmd); |
| log.info("{} models affected", count); |
| 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() |
| { |
| // query all |
| /* |
| DBCommand cmd = context.createCommand() |
| .select (DSV.getColumns()) |
| .orderBy (DSV.SALE_YEAR, DSV.DEALER_COUNTRY); |
| List<DataListEntry> list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| { |
| System.out.println(dle.toString()); |
| } |
| */ |
| |
| // shortcuts (for convenience) |
| DealerSalesView DSV = carSales.DEALER_SALES_VIEW; |
| |
| DBCommand cmd = context.createCommand(); |
| // Detect if Brand sold is the Dealer's Brand |
| DBCompareExpr IS_DEALER_BRAND = DSV.DEALERSHIP_TYPE.in(DealershipType.B, DealershipType.F); |
| // select |
| cmd.select(DSV.SALE_YEAR, DSV.DEALER_NAME, DSV.DEALER_COUNTRY); |
| cmd.select(DSV.SALE_COUNT.sum().as("TOTAL_SALES")); |
| cmd.select(carSales.caseWhen(IS_DEALER_BRAND, DSV.TURNOVER, 0).sum().as("TURNOVER_BRAND")); |
| cmd.select(DSV.TURNOVER.sum().as("TURNOVER_TOTAL")); |
| // group |
| cmd.groupBy(DSV.SALE_YEAR, DSV.DEALER_NAME, DSV.DEALER_COUNTRY); |
| // order |
| cmd.orderBy(DSV.SALE_YEAR, DSV.DEALER_NAME); |
| |
| // query and print result |
| List<DataListEntry> list = context.getUtils().queryDataList(cmd); |
| for (DataListEntry dle : list) |
| { |
| System.out.println(dle.toString()); |
| } |
| } |
| |
| /** |
| * <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; |
| try |
| { |
| // Connect to the database |
| String jdbcDriverClass = config.getJdbcClass(); |
| log.info("Creating JDBC-Driver of type \"{}\"", jdbcDriverClass); |
| Class.forName(jdbcDriverClass).newInstance(); |
| |
| log.info("Connecting to Database'" + config.getJdbcURL() + "' / User=" + config.getJdbcUser()); |
| 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 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 dbms specific configuration |
| */ |
| private static DBMSHandler getDBMSHandler(String provider) |
| { |
| try |
| { // Get Driver Class Name |
| String dbmsHandlerClass = config.getDbmsHandlerClass(); |
| if (StringUtils.isEmpty(dbmsHandlerClass)) |
| throw new RuntimeException("Configuration error: Element 'dbmsHandlerClass' not found in node 'properties-"+provider+"'"); |
| |
| // Create dbms |
| DBMSHandler dbms = (DBMSHandler) Class.forName(dbmsHandlerClass).newInstance(); |
| |
| // Configure dbms |
| config.readProperties(dbms, "properties-"+provider, "dbmsHandlerProperites"); |
| |
| // done |
| return dbms; |
| |
| } catch (Exception e) |
| { // catch any checked exception and forward it |
| e.printStackTrace(); |
| throw new RuntimeException(e); |
| } |
| } |
| |
| /** |
| * 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> |
| * |
| */ |
| private void ddlDemo(String dealerName, String websiteDomain, String websiteUrl) |
| { |
| // create shortcuts |
| DBMSHandler dbms = context.getDbms(); |
| CarSalesDB.Dealer DEALER = carSales.DEALER; |
| |
| // First, add a new column to the Table object |
| DBTableColumn WEBSITE = DEALER.addColumn("WEBSITE", DataType.VARCHAR, 20, false); |
| |
| // Now create the corresponding DDL statement |
| log.info("Create new column named WEBSITE as varchar(20) for the DEALER table"); |
| DBSQLScript script = new DBSQLScript(context); |
| dbms.getDDLScript(DDLActionType.CREATE, WEBSITE, script); |
| script.executeAll(); |
| |
| // Now load a record from that table and set the value for the website |
| log.info("Set the value for the WEBSITE field for dealer \"{}\"", dealerName); |
| DealerRecord rec = new DealerRecord(context); |
| rec.read(DEALER.COMPANY_NAME.is(dealerName)); |
| rec.set (WEBSITE, websiteDomain); |
| rec.update(); |
| |
| // Now extend the size of the field from 40 to 80 characters |
| log.info("Extend the size of column WEBSITE from 20 to 80 characters"); |
| WEBSITE.setSize(80); |
| script.clear(); |
| dbms.getDDLScript(DDLActionType.ALTER, WEBSITE, script); |
| script.executeAll(); |
| |
| // Now set a longer value for the record |
| log.info("Change the value for the WEBSITE to a longer string \"{}\"", websiteUrl); |
| rec.set(WEBSITE, websiteUrl); |
| rec.update(); |
| |
| // Finally, drop the column again |
| log.info("Drop the WEBSITE column from the DEALER table:"); |
| script.clear(); |
| dbms.getDDLScript(DDLActionType.DROP, WEBSITE, script); |
| script.executeAll(); |
| } |
| |
| /** |
| * testTransactionCreate |
| * @param context |
| * @param idDep |
| */ |
| private void transactionDemo() |
| { |
| // Set RollbackHandlingEnabled to false if you want to play with fire! |
| // Note: You must set the RollbackHandling flag before creating a Record instance |
| context.setRollbackHandlingEnabled(true); |
| |
| // User a model record |
| CarSalesDB.Model MODEL = carSales.MODEL; |
| ModelRecord model = new ModelRecord(context); |
| |
| /* |
| * Part 1 |
| */ |
| |
| // Insert a new model |
| log.info("Insert a new Model"); |
| 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.TRIM , "Pro") |
| .set(MODEL.ENGINE_TYPE , EngineType.E) |
| .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(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| // Update even once again without committing |
| model.set(MODEL.BASE_PRICE, 44915); |
| model.update(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| // now, do the rollback |
| log.info("Performing rollback"); |
| context.rollback(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| log.info("Update Model again, even though its not in the database because of the rollback, hence Model will be inserted again."); |
| log.info("This will fail, if Rollback handling is not enabled!"); |
| try { |
| model.set(MODEL.BASE_PRICE, 44900); // Only to mark the record as modified if RollbackHandling is disabled! |
| model.update(); |
| } catch(RecordUpdateFailedException e) { |
| log.error("Model update failed since rollback handling was not enabled for this context!", e); |
| return; // the rest will fail too |
| } |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| /* |
| * Part 2 |
| */ |
| |
| // Delete the model |
| model.delete(); |
| log.debug("Record state={}", model.getState()); |
| |
| log.info("Performing rollback again"); |
| context.rollback(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| // Final update |
| log.info("Update Model again, even though a delete was executed but was not committed."); |
| log.info("This will once again lead to an insert as the insert was still not committed"); |
| log.info("This will also fail, if Rollback handling is not enabled!"); |
| try { |
| model.update(); |
| } catch(RecordUpdateFailedException e) { |
| log.error("Model update failed since rollback handling was not enabled for this context!", e); |
| return; // the rest will fail too |
| } |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| // Finally commit |
| log.info("Finally commit the transaction in order to finalize the Model insert"); |
| context.commit(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| /* |
| * Part 3 |
| */ |
| |
| // Update example with two consecutive updates |
| log.info("Fist update will change the timestamp"); |
| model.set(MODEL.BASE_PRICE, 44000); |
| model.update(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| log.info("Second update will change the timestamp again"); |
| model.set(MODEL.BASE_PRICE, 42660); |
| model.update(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| log.info("Performing rollback for the previous two updates"); |
| context.rollback(); |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| log.info("Update Model again, even though the previous two updates have been rolled back"); |
| log.info("This will again fail, if Rollback handling is not enabled!"); |
| try { |
| model.update(); |
| } catch(RecordUpdateFailedException e) { |
| log.error("Model update failed since rollback handling was not enabled for this context!", e); |
| } |
| log.debug("Record state={}, key={}, Timestamp={}", model.getState(), model.getKey(), model.get(MODEL.UPDATE_TIMESTAMP)); |
| |
| // But now delete the model again, so we can rerun the example later |
| model.delete(); |
| context.commit(); |
| } |
| |
| /** |
| * 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 void cascadeDeleteDemo() |
| { |
| // Read a brand record |
| BrandRecord brand = new BrandRecord(context); |
| brand.read(carSales.BRAND.WMI.is("WVW")); // = Volkswagen |
| |
| // Read dealer record |
| DealerRecord dealer = new DealerRecord(context); |
| dealer.read(carSales.DEALER.COMPANY_NAME.is("Autorigoldi S.p.A.")); |
| |
| try { |
| // This delete is designed to succeed since cascaded deletes are enabled for this relation. |
| // see DEALER_BRANDS and SALES foreign key definition in CarSalesDB: |
| // DEALER_ID = addForeignKey("DEALER_ID", db.DEALER, true, true ); // Delete Cascade on |
| log.info("Deleting DEALER expecting to succeed since relationship to DEALER_BRANDS and SALES is cascading"); |
| dealer.delete(); |
| |
| // This delete is designed to succeed since cascaded deletes are enabled for this relation. |
| log.info("Deleting BRAND expecting to fail since relationship to DEALER_BRANDS and MODEL is NOT cascading"); |
| brand.delete(); |
| |
| } catch(ConstraintViolationException e) { |
| log.info("Delete operation failed due to existing depending records."); |
| } catch(StatementFailedException e) { |
| // Oops, the driver threw a SQLException instead |
| log.info("Delete operation failed with SQLException {}", e.getMessage()); |
| } finally { |
| // we don't really want to do this |
| context.rollback(); |
| } |
| } |
| |
| /** |
| * 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 |
| DBReader reader = new DBReader(context); |
| try |
| { // Open Reader |
| System.out.println("Running Query:"); |
| System.out.println(cmd.getSelect()); |
| reader.open(cmd); |
| // Print column titles |
| System.out.println("---------------------------------"); |
| int count = reader.getFieldCount(); |
| for (int i=0; i<count; i++) |
| { // Print all column names |
| DBColumnExpr c = reader.getColumn(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.getColumn(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(); |
| } |
| } |
| */ |
| |
| |
| } |
