Oh, was not aware of this. Will fix it.
Rainer Döbele <[email protected]> schrieb am Mo., 2. März 2026, 13:08: > Hello Ralf, > > I have seen, that you have committed some new files. > Thank you for that. > > However, these new files are missing the Apache license header, which is > required for all files! > Please take care, that all files contain the following license header at > the top: > > /* > * 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. > */ > > Otherwise we cannot deploy. > > Thanks. > Rainer > > -----Original message----- > From: [email protected] <[email protected]> > Sent: Thursday, Feb 19 16:42 > To: [email protected] > Subject: (empire-db) 01/03: externalize generic spring configuration to > sample independent classes > > This is an automated email from the ASF dual-hosted git repository. > > datazuul pushed a commit to branch master > in repository https://gitbox.apache.org/repos/asf/empire-db.git > > commit 23352dc0cdefa762a168d8e3816fa7ffb2efb82e > Author: Ralf Eichinger <[email protected]> > AuthorDate: Thu Feb 19 16:28:32 2026 +0100 > > externalize generic spring configuration to sample independent classes > --- > .../empire-db-example-spring-boot/pom.xml | 2 + > .../empire/samples/springboot/SampleApp.java | 698 > ++------------------- > .../apache/empire/samples/springboot/SampleDB.java | 269 ++++---- > .../empire/samples/springboot/SampleDBConfig.java | 29 + > .../empire/samples/springboot/SampleService.java | 507 +++++++++++++++ > .../apache/empire/springboot/DBContextSpring.java | 113 ++++ > .../apache/empire/springboot/EmpireDBConfig.java | 47 ++ > .../springboot/EmpireDBConfigProperties.java | 2 +- > 8 files changed, 865 insertions(+), 802 deletions(-) > > diff --git a/empire-db-examples/empire-db-example-spring-boot/pom.xml > b/empire-db-examples/empire-db-example-spring-boot/pom.xml > index b23e2367..2b2e9238 100644 > --- a/empire-db-examples/empire-db-example-spring-boot/pom.xml > +++ b/empire-db-examples/empire-db-example-spring-boot/pom.xml > @@ -31,6 +31,8 @@ > > <properties> > <spring-boot.version>4.0.2</spring-boot.version> > + <maven.compiler.source>17</maven.compiler.source> > + <maven.compiler.target>17</maven.compiler.target> > </properties> > > <dependencyManagement> > diff --git > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java > index 0f3c3d28..69484c90 100644 > --- > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java > +++ > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java > @@ -18,40 +18,7 @@ > */ > package org.apache.empire.samples.springboot; > > -import java.math.BigDecimal; > -import java.math.RoundingMode; > -import java.sql.Connection; > -import java.sql.SQLException; > -import java.time.LocalDate; > -import java.util.List; > -import java.util.Map; > - > -import javax.sql.DataSource; > - > -import org.apache.empire.commons.BeanPropertyUtils; > -import org.apache.empire.commons.ObjectUtils; > -import org.apache.empire.commons.StringUtils; > -import org.apache.empire.data.Record; > -import org.apache.empire.data.bean.BeanResult; > -import org.apache.empire.data.list.DataListEntry; > -import org.apache.empire.db.DBColumnExpr; > -import org.apache.empire.db.DBCommand; > -import org.apache.empire.db.DBContext; > -import org.apache.empire.db.DBQuery; > -import org.apache.empire.db.DBReader; > -import org.apache.empire.db.DBRecord; > -import org.apache.empire.db.DBRecordBean; > -import org.apache.empire.db.DBRowSet; > -import org.apache.empire.db.context.DBContextStatic; > -import org.apache.empire.dbms.DBMSHandler; > -import org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL; > -import org.apache.empire.exceptions.InvalidArgumentException; > -import org.apache.empire.samples.db.beans.Department; > -import org.apache.empire.samples.db.beans.Employee; > -import org.apache.empire.samples.db.beans.EmployeeQuery; > -import org.apache.empire.samples.db.beans.Payment; > -import org.apache.empire.samples.springboot.SampleDB.Gender; > -import org.apache.empire.xml.XMLWriter; > +import org.apache.empire.samples.springboot.SampleService.QueryType; > import org.slf4j.Logger; > import org.slf4j.LoggerFactory; > import org.springframework.beans.factory.annotation.Autowired; > @@ -59,651 +26,66 @@ import org.springframework.boot.ApplicationArguments; > import org.springframework.boot.ApplicationRunner; > import org.springframework.boot.SpringApplication; > import org.springframework.boot.autoconfigure.SpringBootApplication; > -import org.springframework.core.env.Environment; > -import org.w3c.dom.Document; > +import org.springframework.context.annotation.Import; > > /** > * Implementing ApplicationRunner interface tells Spring Boot to > automatically call the run method AFTER the application context has been > loaded. > */ > @SpringBootApplication > +@Import(SampleDBConfig.class) > public class SampleApp implements ApplicationRunner { > > - private static final Logger LOGGER = > LoggerFactory.getLogger(SampleApp.class); > - > - /** > - * <PRE> > - * This is the entry point of the Empire-DB Spring Boot Sample > Application > - * Please check the application.yml configuration file for Database and > Connection settings. > - * > - * See run() method below for what is executed. > - * </PRE> > - * > - * @param args arguments > - */ > - public static void main(String[] args) { > - SpringApplication.run(SampleApp.class, args); > - } > - > - private DBContext context = null; > - > - @Autowired > - private DataSource dataSource; > - > - @Autowired > - private SampleDB db; > - > - @Autowired > - private EmpireDBConfigProperties empireDBConfigProperties; > - > - @Autowired > - private Environment environment; > - > - /** > - * <PRE> > - * Empties all Tables. > - * </PRE> > - */ > - private void clearDatabase() { > - DBCommand cmd = context.createCommand(); > - // Delete all Payments (no constraints) > - context.executeDelete(db.PAYMENTS, cmd); > - // Delete all Employees (no constraints) > - context.executeDelete(db.EMPLOYEES, cmd); > - // Delete all Departments (no constraints) > - context.executeDelete(db.DEPARTMENTS, cmd); > - // commit > - context.commit(); > - } > - > - /** > - * Creates an Empire-db DBMSHandler for the DBMS configured in > application.yml and applies driver specific configuration > - */ > - private DBMSHandler getDBMSHandler(Connection conn) { > - try { > - // Get DBMSHandler Class Name > - String dbmsHandlerClass = > empireDBConfigProperties.getDbmsHandlerClass(); > - if (StringUtils.isEmpty(dbmsHandlerClass)) { > - throw new RuntimeException("Configuration error: Element > 'empiredb.dbmsHandlerClass' not found in properties of profile '" + > environment.getActiveProfiles().toString() + "'"); > - } > - > - // Create dbms > - DBMSHandler dbms = (DBMSHandler) > Class.forName(dbmsHandlerClass).newInstance(); > - > - // Configure dbms > - readProperties(dbms); > - > - // Special cases > - if (dbms instanceof DBMSHandlerPostgreSQL) { > - // Create the reverse function that is needed by this sample > - ((DBMSHandlerPostgreSQL) > dbms).getDDLGenerator().setCreateReverseFunction(true); > - } > - > - // done > - return dbms; > - } catch (Exception e) { > - // catch any checked exception and forward it > - e.printStackTrace(); > - throw new RuntimeException(e); > - } > - } > - > - /** > - * <PRE> > - * Opens and returns a JDBC-Connection. > - * JDBC url, user and password for the connection are obtained from the > DataSource bean > - * Please use the application.yml file to change connection params. > - * </PRE> > - */ > - private Connection getJDBCConnection() { > - // Establish a new database connection > - Connection conn = null; > - try { > - conn = dataSource.getConnection(); > - LOGGER.info("Connected successfully"); > - // set the AutoCommit to false for this connection. > - // commit must be called explicitly! > - conn.setAutoCommit(false); > - LOGGER.info("AutoCommit has been set to " + conn.getAutoCommit()); > - } catch (SQLException e) { > - // LOGGER.error("Failed to connect directly to '" + > config.getJdbcURL() + "' / User=" + config.getJdbcUser()); > - LOGGER.error(e.toString()); > - throw new RuntimeException(e); > - } > - return conn; > - } > - > - /** > - * <PRE> > - * Insert a Department into the Departments table. > - * </PRE> > - */ > - private long insertDepartment(String departmentName, String > businessUnit) { > - SampleDB.Departments DEP = db.DEPARTMENTS; > - // Insert a Department > - DBRecord rec = new DBRecord(context, DEP); > - rec.create() > - .set(DEP.NAME, departmentName) > - .set(DEP.BUSINESS_UNIT, businessUnit) > - .update(); > - // Return Department ID > - return rec.getIdentity(); > - } > - > - /** > - * <PRE> > - * Inserts an Employee into the Employees table. > - * </PRE> > - */ > - private long insertEmployee(long departmentId, String firstName, String > lastName, Gender gender, int salary) { > - SampleDB.Employees EMP = db.EMPLOYEES; > - // Insert an Employee > - DBRecord rec = new DBRecord(context, EMP); > - rec.create(null) > - .set(EMP.DEPARTMENT_ID, departmentId) > - .set(EMP.FIRST_NAME, firstName) > - .set(EMP.LAST_NAME, lastName) > - .set(EMP.GENDER, gender) > - .set(EMP.SALARY, salary) > - .update(); > - // insert payments > - if (salary > 0) { > - insertPayments(rec); > - } > - // Return Employee ID > - return rec.getIdentity(); > - } > - > - /** > - * <PRE> > - * Inserts an Payments for a particular Employee > - * </PRE> > - */ > - private void insertPayments(DBRecord employee) { > - if (employee.isNull(db.EMPLOYEES.SALARY)) { > - return; // No salary > - } // monthlySalary > - BigDecimal monthlySalary = > employee.getDecimal(db.EMPLOYEES.SALARY).divide(new BigDecimal(12), 2, > RoundingMode.HALF_UP); > - // Insert an Employee > - LocalDate date = LocalDate.now(); > - date = date.minusDays(date.getDayOfMonth() - 1); // first day of this > month > - // Add Payment for each month > - SampleDB.Payments PAY = db.PAYMENTS; > - DBRecord rec = new DBRecord(context, PAY); > - int months = (int) (Math.random() * 6) + 17; > - for (LocalDate month = date.minusMonths(months); > !month.isAfter(date); month = month.plusMonths(1)) { > - BigDecimal variation = new BigDecimal((Math.random() * 200) - > 100.0); > - variation = variation.setScale(2, RoundingMode.HALF_UP); > - // insert > - rec.create(DBRecord.key(employee.getIdentity(), month.getYear(), > month.getMonth())); > - rec.set(PAY.AMOUNT, monthlySalary.add(variation)); > - rec.update(); > - } > - } > - > - @SuppressWarnings("unused") > - private void populateAndModify() { > - clearDatabase(); > - > - LOGGER.info("Step 5: insertDepartment() & insertEmployee()"); > - long idDevDep = insertDepartment("Development", "ITTK"); > - long idSalDep = insertDepartment("Sales", "ITTK"); > - // Insert Employees > - long idEmp1 = insertEmployee(idDevDep, "Peter", "Sharp", Gender.M, > 25000); > - long idEmp2 = insertEmployee(idDevDep, "Fred", "Bloggs", Gender.M, 0); > - long idEmp3 = insertEmployee(idSalDep, "Emma", "White", Gender.F, > 19500); > - long idEmp4 = insertEmployee(idSalDep, "John", "Doe", Gender.M, > 18800); > - long idEmp5 = insertEmployee(idDevDep, "Sarah", "Smith", Gender.F, > 44000); > - > - // commit > - context.commit(); > - > - // SECTION 6: Modify some data > - LOGGER.info("Step 6: updateEmployee()"); > - updateEmployee(idEmp1, "+49-7531-457160", true); > - updateEmployee(idEmp2, "+49-5555-505050", false); > - // Partial Record > - updatePartialRecord(idEmp3, "+49-040-125486"); > - // Update Joined Records (Make Fred Bloggs head of department and set > salary) > - updateJoinedRecords(idEmp2, 100000); > - } > - > - private void queryBeans() { > - SampleDB.Employees EMP = db.EMPLOYEES; > - > - DBCommand cmd = context.createCommand(); > - cmd.where(EMP.GENDER.is(Gender.M)); > - cmd.orderBy(EMP.LAST_NAME.desc()); > - List<Employee> list = context.getUtils().queryBeanList(cmd, > Employee.class, null); > - for (Employee emp : list) { > - System.out.println(emp.toString()); > - } > - > - // load department > - Department department = > context.getUtils().queryBean(Department.class, db.DEPARTMENTS.NAME.is > ("Sales")); > - Payment first = department.getEmployees().get(0).getPayments().get(0); > - LOGGER.info("First payment amount is {}", first.getAmount()); > - > - // Query all males > - BeanResult<Employee> result = new > BeanResult<Employee>(Employee.class, EMP); > - result.getCommand().where(EMP.GENDER.is(Gender.M)); > - result.fetch(context); > - > - LOGGER.info("Number of male employees is: " + result.size()); > - > - // And now, the females > - result.getCommand().where(EMP.GENDER.is(Gender.F)); > - result.fetch(context); > - > - LOGGER.info("Number of female employees is: " + result.size()); > - } > - > - private void queryDataList() { > - int lastYear = LocalDate.now().getYear() - 1; > - > - // Define shortcuts for tables used - not necessary but convenient > - SampleDB.Employees EMP = db.EMPLOYEES; > - SampleDB.Departments DEP = db.DEPARTMENTS; > - SampleDB.Payments PAY = db.PAYMENTS; > + private static final Logger LOGGER = > LoggerFactory.getLogger(SampleApp.class); > > - // Employee total query > - DBColumnExpr EMP_TOTAL = PAY.AMOUNT.sum().as("EMP_TOTAL"); > - DBCommand cmdEmpTotal = context.createCommand() > - .select(PAY.EMPLOYEE_ID, EMP_TOTAL) > - .where(PAY.YEAR.is(lastYear)) > - .groupBy(PAY.EMPLOYEE_ID); > - DBQuery Q_EMP_TOTAL = new DBQuery(cmdEmpTotal, "qet"); > + @Autowired > + private SampleService sampleService; > > - // Department total query > - DBColumnExpr DEP_TOTAL = PAY.AMOUNT.sum().as("DEP_TOTAL"); > - DBCommand cmdDepTotal = context.createCommand() > - .select(EMP.DEPARTMENT_ID, DEP_TOTAL) > - .join(PAY.EMPLOYEE_ID, EMP.ID) > - .where(PAY.YEAR.is(lastYear)) > - .groupBy(EMP.DEPARTMENT_ID); > - DBQuery Q_DEP_TOTAL = new DBQuery(cmdDepTotal, "qdt"); > - > - // Percentage of department > - DBColumnExpr PCT_OF_DEP_COST = > Q_EMP_TOTAL.column(EMP_TOTAL).multiplyWith(100).divideBy(Q_DEP_TOTAL.column(DEP_TOTAL)); > - // Create the employee query > - DBCommand cmd = context.createCommand() > - .select(EMP.ID, EMP.FIRST_NAME, EMP.LAST_NAME, DEP.NAME.as > ("DEPARTMENT")) > - .select(Q_EMP_TOTAL.column(EMP_TOTAL)) > - .select(PCT_OF_DEP_COST.as("PCT_OF_DEPARTMENT_COST")) > - // join Employee with Department > - .join(EMP.DEPARTMENT_ID, DEP.ID) > - // Join with Subqueries > - .joinLeft(EMP.ID, Q_EMP_TOTAL.column(PAY.EMPLOYEE_ID)) > - .joinLeft(DEP.ID, Q_DEP_TOTAL.column(EMP.DEPARTMENT_ID)) > - // Order by > - .orderBy(DEP.NAME.desc()) > - .orderBy(EMP.LAST_NAME); > - > - List<DataListEntry> list = context.getUtils().queryDataList(cmd); > - /* uncomment this to print full list > - for (DataListEntry dle : list) > - System.out.println(dle.toString()); > + /** > + * <PRE> > + * This is the entry point of the Empire-DB Spring Boot Sample > Application > + * Please check the application.yml configuration file for Database > and Connection settings. > + * <p> > + * See run() method below for what is executed. > + * </PRE> > + * > + * @param args arguments > */ > - for (DataListEntry dle : list) { > - long empId = dle.getRecordId(EMP); > - // int depId = dle.getId(DEP); > - // Put the comma between last and first name (Last, First) > - String empName = StringUtils.concat(dle.getString(EMP.LAST_NAME), > ", ", dle.getString(EMP.FIRST_NAME)); > - String depName = dle.getString(DEP.NAME); > - boolean hasPayments = !dle.isNull(Q_EMP_TOTAL.column(EMP_TOTAL)); > - if (hasPayments) { // report > - BigDecimal empTotal = > dle.getDecimal(Q_EMP_TOTAL.column(EMP_TOTAL)); > - BigDecimal pctOfDep = dle.getDecimal(PCT_OF_DEP_COST).setScale(1, > RoundingMode.HALF_UP); > - LOGGER.info("Employee[{}]: {}\tDepartment: {}\tPayments: {} ({}% > of Department)", empId, empName, depName, empTotal, pctOfDep); > - } else { > - LOGGER.info("Employee[{}]: {}\tDepartment: {}\tPayments: [No data > avaiable]", empId, empName, depName); > - } > + public static void main(String[] args) { > + SpringApplication.run(SampleApp.class, args); > } > > - /* > - cmd.where(EMP.ID.is(list.get(0).getRecordId(EMP))); > - DataListEntry emp1 = context.getUtils().queryDataEntry(cmd); > - System.out.println(emp1.toString()); > > - cmd.where(EMP.ID.is(list.get(1).getRecordId(EMP))); > - DataListEntry emp2 = context.getUtils().queryDataEntry(cmd); > - System.out.println(emp2.toString()); > - */ > - } > + @Override > + public void run(ApplicationArguments args) throws Exception { > + LOGGER.info("STARTING THE APPLICATION"); > > - /** > - * <PRE> > - * Performs an SQL-Query and prints the result to System.out > - * > - * First a DBCommand object is used to create the following SQL-Query > (Oracle-Syntax): > - * > - * SELECT t2.EMPLOYEE_ID, t2.LASTNAME || ', ' || t2.FIRSTNAME AS > FULL_NAME, t2.GENDER, t2.PHONE_NUMBER, > - * substr(t2.PHONE_NUMBER, > length(t2.PHONE_NUMBER)-instr(reverse(t2.PHONE_NUMBER), '-')+2) AS > PHONE_EXTENSION, > - * t1.NAME AS DEPARTMENT, t1.BUSINESS_UNIT > - * FROM EMPLOYEES t2 INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = > t2.ID > - * WHERE length(t2.LASTNAME)>0 > - * ORDER BY t2.LASTNAME, t2.FIRSTNAME > - * > - * For processing the rows there are three options available: > - * > - * QueryType.Reader: > - * Iterates through all rows and prints field values as tabbed text. > - * > - * QueryType.BeanList: > - * Obtains the query result as a list of JavaBean objects of type > SampleBean. > - * It then iterates through the list of beans and uses > bean.toString() for printing. > - * > - * QueryType.XmlDocument: > - * Obtains the query result as an XML-Document and prints the > document. > - * Please note, that the XML not only contains the data but also > the field metadata. > - * </PRE> > - */ > - private void queryExample(QueryType queryType) { > - int lastYear = LocalDate.now().getYear() - 1; > + LOGGER.info("Running DB Sample..."); > > - // Define shortcuts for tables used - not necessary but convenient > - SampleDB.Employees EMP = db.EMPLOYEES; > - SampleDB.Departments DEP = db.DEPARTMENTS; > - SampleDB.Payments PAY = db.PAYMENTS; > + // SECTION 1 - 4: Get a JDBC Connection, Choose a DBMSHandler, > Create a Context, Open Database: done in Spring Configuration classes > + LOGGER.info("*** Step 1 - 4: done. ***"); > > - // The following expression concats lastname + ', ' + firstname > - DBColumnExpr EMPLOYEE_NAME = EMP.LAST_NAME.append(", > ").append(EMP.FIRST_NAME).as("EMPLOYEE_NAME"); > - DBColumnExpr PAYMENTS_LAST_YEAR = > PAY.AMOUNT.sum().as("PAYMENTS_LAST_YEAR"); > + // SECTION 5 AND 6: Populate Database and modify Data > + sampleService.populateAndModify(); > > - /* > - // Example: Extracts the extension number from the phone field > - // e.g. substr(PHONE_NUMBER, > length(PHONE_NUMBER)-instr(reverse(PHONE_NUMBER), '-')+2) AS PHONE_EXTENSION > - // Hint: Since the reverse() function is not supported by HSQLDB > there is special treatment for HSQL > - DBColumnExpr PHONE_LAST_DASH; > - if ( db.getDbms() instanceof DBMSHandlerHSql > - || db.getDbms() instanceof DBMSHandlerDerby > - || db.getDbms() instanceof DBMSHandlerH2) > - PHONE_LAST_DASH = EMP.PHONE_NUMBER.indexOf("-", > EMP.PHONE_NUMBER.indexOf("-").plus(1)).plus(1); // HSQLDB only > - else PHONE_LAST_DASH = > EMP.PHONE_NUMBER.length().minus(EMP.PHONE_NUMBER.reverse().indexOf("-")).plus(2); > > - DBColumnExpr PHONE_EXT_NUMBER = > EMP.PHONE_NUMBER.substring(PHONE_LAST_DASH).as("PHONE_EXTENSION"); > - */ > + // SECTION 7: Option 1: Query Records and print tab-separated > + LOGGER.info("Step 8 Option 1: queryRecords() / Tab-Output"); > + sampleService.queryExample(QueryType.Reader); // Tab-Output > > - /* > - // Example: Select the Gender-Enum as String > - // e.g. case t2.GENDER when 'U' then 'Unknown' when 'M' then > 'Male' when 'F' then 'Female' end > - DBColumnExpr GENDER_NAME = > EMP.GENDER.decode(EMP.GENDER.getOptions()).as("GENDER_NAME"); > - */ > - // Select Employee and Department columns > - DBCommand cmd = context.createCommand() > - .selectQualified(EMP.ID) // select "EMPLOYEE_ID" > - .select(EMPLOYEE_NAME, EMP.GENDER, EMP.PHONE_NUMBER, > EMP.SALARY) > - .selectQualified(DEP.NAME) // "DEPARMENT_NAME" > - .select(DEP.BUSINESS_UNIT) // "BUSINESS_UNIT" > - // Joins > - .join(EMP.DEPARTMENT_ID, DEP.ID) > - .joinLeft(EMP.ID, PAY.EMPLOYEE_ID, PAY.YEAR.is(lastYear)) > - // Where constraints > - .where(EMP.LAST_NAME.length().isGreaterThan(0)) // always > true, just for show > - .where(EMP.GENDER.in(Gender.M, Gender.F)) // always true, > just for show > - .where(EMP.RETIRED.is(false)) // always true, just for show > - // Order by > - .orderBy(EMPLOYEE_NAME); > - > - // Add payment of last year using a SUM aggregation > - cmd.groupBy(cmd.getSelectExpressions()); > - cmd.select(PAYMENTS_LAST_YEAR); > - > - /* > - * Example for limitRows() and skipRows() > - * Uncomment if you wish > - * > - if (db.getDbms().isSupported(DBMSFeature.QUERY_LIMIT_ROWS)) > - { // set maximum number of rows > - cmd.limitRows(20); > - if (db.getDbms().isSupported(DBMSFeature.QUERY_SKIP_ROWS)) > - cmd.skipRows(1); > - } > - */ > - // Query Records and print output > - DBReader reader = new DBReader(context); > - try { > - // log select statement (but only once) > - if (queryType == QueryType.Reader) { > - LOGGER.info("Running Query: {}", cmd.getSelect()); > - } > - // Open Reader > - reader.open(cmd); > - // Print output > - System.out.println("---------------------------------"); > - switch (queryType) { > - case Reader: > - // Text-Output by iterating through all records. > - while (reader.moveNext()) { > - System.out.println(reader.getText(EMP.ID) > - + "\t" + reader.getText(EMPLOYEE_NAME) > - + "\t" + reader.getText(EMP.GENDER) > - + "\t" + reader.getText(EMP.SALARY) > - + "\t" + reader.getText(PAYMENTS_LAST_YEAR) > - + "\t" + reader.getText(DEP.NAME)); > - } > - break; > - case BeanList: > - // Text-Output using a list of Java Beans supplied by the > DBReader > - List<EmployeeQuery> beanList = > reader.getBeanList(EmployeeQuery.class); > - // log.info(String.valueOf(beanList.size()) + " SampleBeans > returned from Query."); > - for (EmployeeQuery b : beanList) { > - System.out.println(b.toString()); > - } > - break; > - case XmlDocument: > - // XML Output > - Document doc = reader.getXmlDocument(); > - // Print XML Document to System.out > - XMLWriter.debug(doc); > - break; > - } > - System.out.println("---------------------------------"); > - } finally { > - // Always close Reader! > - reader.close(); > - } > - } > + // SECTION 7: Option 2: Query Records as a list of java beans > + LOGGER.info("Step 8 Option 2: queryRecords() / Bean-List-Output"); > + sampleService.queryExample(QueryType.BeanList); // > Bean-List-Output > > - private void queryRecordList() { > - SampleDB.Departments DEP = db.DEPARTMENTS; > - SampleDB.Employees EMP = db.EMPLOYEES; > - /* > - * Test RecordList > - */ > - DBCommand cmd = context.createCommand(); > - cmd.join(EMP.DEPARTMENT_ID, DEP.ID); > - cmd.where(DEP.NAME.is("Development")); > - // query now > - List<DBRecordBean> list = context.getUtils().queryRecordList(cmd, > EMP, DBRecordBean.class); > - LOGGER.info("RecordList query found {} employees in Development > department", list.size()); > - for (DBRecordBean record : list) { > - Object[] key = record.getKey(); > - // print info > - String empName = > StringUtils.concat(record.getString(EMP.LAST_NAME), ", ", > record.getString(EMP.FIRST_NAME)); > - String phone = record.getString(EMP.PHONE_NUMBER); > - BigDecimal salary = record.getDecimal(EMP.SALARY); > - LOGGER.info("Employee[{}]: {}\tPhone: {}\tSalary: {}", > StringUtils.toString(key), empName, phone, salary); > - // modify salary > - BigDecimal newSalary = new BigDecimal(2000 + ((Math.random() * 200) > - 100.0)); > - record.set(EMP.SALARY, newSalary); > - // check > - if (record.wasModified(EMP.SALARY)) { // Salary was modified > - LOGGER.info("Salary was modified for {}. New salary is {}", > empName, record.getDecimal(EMP.SALARY)); > - } > - // udpate the record > - record.update(context); > + // SECTION 7: Option 3: Query Records as XML > + LOGGER.info("Step 8 Option 3: queryRecords() / XML-Output"); > + sampleService.queryExample(QueryType.XmlDocument); // XML-Output > > - // convert to bean > - Employee employee = new Employee(); > - record.setBeanProperties(employee); > - System.out.println(employee.toString()); > - } > - } > + // SECTION 8: Use DataList query > + sampleService.queryDataList(); > > - /** > - * reads all properties for given profile and applies them to the given > bean > - * > - * @param bean the bean to which to apply the configuration > - */ > - private void readProperties(Object bean) { > - // Check arguments > - if (bean == null) { > - throw new InvalidArgumentException("bean", bean); > - } > + // SECTION 9: Use RecordList query > + sampleService.queryRecordList(); > > - Map<String, String> dbmsHandlerProperties = > empireDBConfigProperties.getDbmsHandlerProperties(); > - if (dbmsHandlerProperties != null) { > - for (Map.Entry<String, String> entry : > dbmsHandlerProperties.entrySet()) { > - String name = entry.getKey(); > - String newValue = entry.getValue(); > - // set property > - BeanPropertyUtils.setProperty(bean, name, newValue); > - // Check > - Object value = BeanPropertyUtils.getProperty(bean, name); > - if (ObjectUtils.compareEqual(newValue, value)) { > - LOGGER.info("Configuration property '{}' set to \"{}\"", > name, newValue); > - } else { > - LOGGER.error("Failed to set property '{}'. Value is \"{}\"", > name, value); > - } > - } > + // SECTION 10: Use Bean Result to query beans > + sampleService.queryBeans(); > } > - } > - > - @Override > - public void run(ApplicationArguments args) throws Exception { > - LOGGER.info("STARTING THE APPLICATION"); > - > - LOGGER.info("Running DB Sample..."); > - > - // SECTION 1: Get a JDBC Connection > - LOGGER.info("*** Step 1: getJDBCConnection() ***"); > - Connection conn = getJDBCConnection(); > - > - // SECTION 2: Choose a DBMSHandler > - LOGGER.info("*** Step 2: getDBMSHandler() ***"); > - DBMSHandler dbms = getDBMSHandler(conn); > - > - // SECTION 3: Create a Context > - LOGGER.info("*** Step 3: create context ***"); > - context = new DBContextStatic(dbms, conn, true) > - // set optional context features > - .setPreparedStatementsEnabled(false) > - .setRollbackHandlingEnabled(false); > - > - // SECTION 4: Open Database > - LOGGER.info("*** Step 4: Open database (and create if not existing) > ***"); > - db.open(context); > - > - // SECTION 5 AND 6: Populate Database and modify Data > - DBCommand cmd = context.createCommand(); > - cmd.select(db.EMPLOYEES.count()); > - if (context.getUtils().querySingleInt(cmd) == 0) { // Employess > table is empty. Populate now > - populateAndModify(); > - } > - > - // SECTION 7: Option 1: Query Records and print tab-separated > - LOGGER.info("Step 8 Option 1: queryRecords() / Tab-Output"); > - queryExample(QueryType.Reader); // Tab-Output > - > - // SECTION 7: Option 2: Query Records as a list of java beans > - LOGGER.info("Step 8 Option 2: queryRecords() / Bean-List-Output"); > - queryExample(QueryType.BeanList); // Bean-List-Output > - > - // SECTION 7: Option 3: Query Records as XML > - LOGGER.info("Step 8 Option 3: queryRecords() / XML-Output"); > - queryExample(QueryType.XmlDocument); // XML-Output > - > - // SECTION 8: Use DataList query > - queryDataList(); > - > - // SECTION 9: Use RecordList query > - queryRecordList(); > - > - // SECTION 10: Use Bean Result to query beans > - queryBeans(); > - > - > - /* > - int idEmp = testTransactionCreate(idDevDep); > - testTransactionUpdate(idEmp); > - testTransactionDelete(idEmp); > - */ > - // Finally, commit any changes > - context.commit(); > - } > - > - /** > - * <PRE> > - * Updates an employee record by setting the phone number. > - * </PRE> > - */ > - private void updateEmployee(long idEmp, String phoneNumber, boolean > useRecord) > - { > - // Update an Employee > - if (useRecord) > - { > - // Use a DBRecord (recommended) > - DBRecord rec = new DBRecord(context, db.EMPLOYEES); > - rec.read(idEmp); > - // Set > - rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > - rec.update(); > - > - } > - else > - { // Or use a DBRecordBean: > - DBRecordBean rec = new DBRecordBean(); > - rec.read(context, db.EMPLOYEES, idEmp); > - // Set > - rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > - rec.update(context); > - } > - } > - > - /** > - * <PRE> > - * Updates an employee record by setting the phone number. > - * </PRE> > - */ > - private void updateJoinedRecords(long idEmp, int salary) { > - // Shortcuts for convenience > - SampleDB.Employees EMP = db.EMPLOYEES; > - SampleDB.Departments DEP = db.DEPARTMENTS; > - > - // Create DBQuery from command > - DBCommand cmd = context.createCommand(); > - cmd.select(EMP.getColumns()); > - cmd.select(DEP.getColumns()); > - cmd.join(EMP.DEPARTMENT_ID, DEP.ID); > - DBQuery query = new DBQuery(cmd, EMP.ID); > - > - // Make employee Head of Department and update salary > - DBRecord rec = new DBRecord(context, query); > - rec.read(idEmp); > - rec.set(EMP.SALARY, salary); > - rec.set(DEP.HEAD, rec.getString(EMP.LAST_NAME)); > - rec.update(); > - } > - > - /** > - * <PRE> > - * Updates an employee record by setting the phone number. > - * </PRE> > - */ > - private void updatePartialRecord(long employeeId, String phoneNumber) { > - // Shortcut for convenience > - SampleDB.Employees EMP = db.EMPLOYEES; > - // Update an Employee with partial record > - // this will only load the EMPLOYEE ID and the PHONE_NUMBER > - DBRecord rec = new DBRecord(context, EMP); > - rec.read(Record.key(employeeId), DBRowSet.PartialMode.INCLUDE, > EMP.SALUTATION, EMP.FIRST_NAME, EMP.LAST_NAME, EMP.PHONE_NUMBER, EMP.EMAIL); > - // Set > - rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > - rec.update(); > - } > - > - private enum QueryType { > - Reader, > - BeanList, > - XmlDocument > - } > } > diff --git > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.java > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.java > index bcd7dfee..82d68ff9 100644 > --- > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.java > +++ > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.java > @@ -18,8 +18,6 @@ > */ > package org.apache.empire.samples.springboot; > > -import java.sql.SQLException; > - > import org.apache.empire.data.DataType; > import org.apache.empire.db.DBColumn; > import org.apache.empire.db.DBContext; > @@ -36,46 +34,118 @@ import org.apache.empire.samples.db.beans.Employee; > import org.apache.empire.samples.db.beans.Payment; > import org.slf4j.Logger; > import org.slf4j.LoggerFactory; > -import org.springframework.stereotype.Component; > + > +import java.sql.SQLException; > > /** > * <PRE> > * This file contains the definition of the data model in Java. > * The SampleDB data model consists of three tables and two foreign key > relations. > * The tables are defined as nested classes here, but you may put them in > separate files if you want to. > - * > - * The class also provided methods to create the database (if it doesn't > exist) > + * <p> > + * The class also provided methods to create the database (if it doesn't > exist) > * and to check the datamodel i.e. to compare this definition to the > database in the DBMS > - * > + * <p> > * PLEASE NOTE THE NAMING CONVENTION: > * Since all tables, views and columns are declared as "final" constants > we have all put them in upper case. > - * > + * <p> > * You may declare other database tables or views in the same way. > * </PRE> > */ > -@Component > -public class SampleDB extends DBDatabase > -{ > +public class SampleDB extends DBDatabase { > // Logger > private static final Logger log = > LoggerFactory.getLogger(SampleDB.class); > - > + // Declare all Tables and Views here > + public final Departments DEPARTMENTS = new Departments(this); > + public final Employees EMPLOYEES = new Employees(this); > + public final Payments PAYMENTS = new Payments(this); > + > + /** > + * Constructor of the SampleDB data model > + * <p> > + * Put all foreign key relations here. > + */ > + public SampleDB() { > + // Define additional Foreign-Key Relations here > + // which have not already been defined by addForeignKey() > + // addRelation( {Source Column}.referenceOn( {Target Column} )); > + log.info("SampleDB has been created with {} Tables and {} > Relations", getTables().size(), getRelations().size()); > + } > + > + @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); > + } > + } > + > /** > * Gender enum > */ > - public enum Gender > - { > - M("Male"), > - F("Female"), > + public enum Gender { > + M("Male"), > + F("Female"), > U("Unknown"); > - > + > private final String title; > - private Gender(String title) > - { > + > + Gender(String title) { > this.title = title; > } > + > @Override > - public String toString() > - { > + public String toString() { > return title; > } > } > @@ -83,30 +153,28 @@ public class SampleDB extends DBDatabase > /** > * This class represents the Departments table. > */ > - public static class Departments extends DBTable > - { > + public static class Departments extends DBTable { > public final DBTableColumn ID; > public final DBTableColumn NAME; > public final DBTableColumn HEAD; > public final DBTableColumn BUSINESS_UNIT; > public final DBTableColumn UPDATE_TIMESTAMP; > > - public Departments(SampleDB db) > - { > + public Departments(SampleDB db) { > super("DEPARTMENTS", db); > // ID > - ID = addIdentity ("ID", > "DEP_ID_SEQUENCE"); // Optional Sequence for some DBMS (e.g. Oracle) > - NAME = addColumn ("NAME", > DataType.VARCHAR, 80, true); > - HEAD = addColumn ("HEAD", > DataType.VARCHAR, 80, false); > - BUSINESS_UNIT = addColumn ("BUSINESS_UNIT", > DataType.VARCHAR, 4, true, "ITTK"); > - UPDATE_TIMESTAMP= addTimestamp("UPDATE_TIMESTAMP"); > + ID = addIdentity("ID", "DEP_ID_SEQUENCE"); // Optional > Sequence for some DBMS (e.g. Oracle) > + NAME = addColumn("NAME", DataType.VARCHAR, 80, true); > + HEAD = addColumn("HEAD", DataType.VARCHAR, 80, false); > + BUSINESS_UNIT = addColumn("BUSINESS_UNIT", DataType.VARCHAR, > 4, true, "ITTK"); > + UPDATE_TIMESTAMP = addTimestamp("UPDATE_TIMESTAMP"); > > - // Primary Key (automatically set due to addIdentity()) > otherwise use > + // Primary Key (automatically set due to addIdentity()) > otherwise use > // setPrimaryKey(...); > > // Set other Indexes > - addIndex("DEPARTMENT_NAME_IDX", true, new DBColumn[] { NAME > }); > - > + addIndex("DEPARTMENT_NAME_IDX", true, NAME); > + > // Set beanType (optional) > setBeanType(Department.class); > } > @@ -115,8 +183,7 @@ public class SampleDB extends DBDatabase > /** > * This class represents the Employees table. > */ > - public static class Employees extends DBTable > - { > + public static class Employees extends DBTable { > public final DBTableColumn ID; > public final DBTableColumn SALUTATION; > public final DBTableColumn FIRST_NAME; > @@ -130,29 +197,28 @@ public class SampleDB extends DBDatabase > public final DBTableColumn RETIRED; > public final DBTableColumn UPDATE_TIMESTAMP; > > - public Employees(SampleDB db) > - { > + public Employees(SampleDB db) { > super("EMPLOYEES", db); > - > + > // ID > - ID = addIdentity ("ID", > "EMPLOYEE_ID_SEQUENCE"); // Optional Sequence name for some DBMS (e.g. > Oracle) > - SALUTATION = addColumn ("SALUTATION", > DataType.VARCHAR, 5, false); > - FIRST_NAME = addColumn ("FIRST_NAME", > DataType.VARCHAR, 40, true); > - LAST_NAME = addColumn ("LAST_NAME", > DataType.VARCHAR, 40, true); > - DATE_OF_BIRTH = addColumn ("DATE_OF_BIRTH", > DataType.DATE, 0, false); > - DEPARTMENT_ID = addForeignKey("DEPARTMENT_ID", > db.DEPARTMENTS, true); > - GENDER = addColumn ("GENDER", > DataType.VARCHAR, 1, true, Gender.class); > - PHONE_NUMBER = addColumn ("PHONE_NUMBER", > DataType.VARCHAR, 40, false); > - EMAIL = addColumn ("EMAIL", > DataType.VARCHAR, 80, false); > - SALARY = addColumn ("SALARY", > DataType.DECIMAL, 10.2, false); > - RETIRED = addColumn ("RETIRED", > DataType.BOOL, 0, true, false); > - UPDATE_TIMESTAMP= addTimestamp ("UPDATE_TIMESTAMP"); > - > - // Primary Key (automatically set due to addIdentity()) > otherwise use > + ID = addIdentity("ID", "EMPLOYEE_ID_SEQUENCE"); // Optional > Sequence name for some DBMS (e.g. Oracle) > + SALUTATION = addColumn("SALUTATION", DataType.VARCHAR, 5, > false); > + FIRST_NAME = addColumn("FIRST_NAME", DataType.VARCHAR, 40, > true); > + LAST_NAME = addColumn("LAST_NAME", DataType.VARCHAR, 40, > true); > + DATE_OF_BIRTH = addColumn("DATE_OF_BIRTH", DataType.DATE, 0, > false); > + DEPARTMENT_ID = addForeignKey("DEPARTMENT_ID", > db.DEPARTMENTS, true); > + GENDER = addColumn("GENDER", DataType.VARCHAR, 1, true, > Gender.class); > + PHONE_NUMBER = addColumn("PHONE_NUMBER", DataType.VARCHAR, > 40, false); > + EMAIL = addColumn("EMAIL", DataType.VARCHAR, 80, false); > + SALARY = addColumn("SALARY", DataType.DECIMAL, 10.2, false); > + RETIRED = addColumn("RETIRED", DataType.BOOL, 0, true, false); > + UPDATE_TIMESTAMP = addTimestamp("UPDATE_TIMESTAMP"); > + > + // Primary Key (automatically set due to addIdentity()) > otherwise use > // setPrimaryKey(...); > > // Set other Indexes > - addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { > FIRST_NAME, LAST_NAME, DATE_OF_BIRTH }); > + addIndex("EMPLOYEE_NAME_IDX", true, FIRST_NAME, LAST_NAME, > DATE_OF_BIRTH); > > // Set beanType (optional) > setBeanType(Employee.class); > @@ -162,109 +228,26 @@ public class SampleDB extends DBDatabase > /** > * This class represents the Payments table. > */ > - public static class Payments extends DBTable > - { > + public static class Payments extends DBTable { > public final DBTableColumn EMPLOYEE_ID; > public final DBTableColumn YEAR; > public final DBTableColumn MONTH; > public final DBTableColumn AMOUNT; > > - public Payments(SampleDB db) > - { > + public Payments(SampleDB db) { > super("PAYMENTS", db); > - > + > // ID > - EMPLOYEE_ID = addForeignKey("EMPLOYEE_ID", > db.EMPLOYEES, true); > - YEAR = addColumn("YEAR", > DataType.DECIMAL, 4.0, true); > - MONTH = addColumn("MONTH", > DataType.DECIMAL, 2.0, true); > - AMOUNT = addColumn("AMOUNT", > DataType.DECIMAL, 8.2, true); > + EMPLOYEE_ID = addForeignKey("EMPLOYEE_ID", db.EMPLOYEES, > true); > + YEAR = addColumn("YEAR", DataType.DECIMAL, 4.0, true); > + MONTH = addColumn("MONTH", DataType.DECIMAL, 2.0, true); > + AMOUNT = addColumn("AMOUNT", DataType.DECIMAL, 8.2, true); > > - // Primary Key > + // Primary Key > setPrimaryKey(EMPLOYEE_ID, YEAR, MONTH); > > // Set beanType (optional) > setBeanType(Payment.class); > } > } > - > - // Declare all Tables and Views here > - public final Departments DEPARTMENTS = new Departments(this); > - public final Employees EMPLOYEES = new Employees(this); > - public final Payments PAYMENTS = new Payments(this); > - > - /** > - * Constructor of the SampleDB data model > - * > - * Put all foreign key relations here. > - */ > - public SampleDB() > - { > - // Define additional Foreign-Key Relations here > - // which have not already been defined by addForeignKey() > - // addRelation( {Source Column}.referenceOn( {Target Column} )); > - log.info("SampleDB has been created with {} Tables and {} > Relations", getTables().size(), getRelations().size()); > - } > - > - @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-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDBConfig.java > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDBConfig.java > new file mode 100644 > index 00000000..421b4fef > --- /dev/null > +++ > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDBConfig.java > @@ -0,0 +1,29 @@ > +package org.apache.empire.samples.springboot; > + > +import org.apache.empire.db.DBContext; > +import org.apache.empire.springboot.EmpireDBConfig; > +import org.slf4j.Logger; > +import org.slf4j.LoggerFactory; > +import org.springframework.context.annotation.Bean; > +import org.springframework.context.annotation.Configuration; > +import org.springframework.context.annotation.Import; > + > +@Configuration > +@Import(EmpireDBConfig.class) > +public class SampleDBConfig { > + private static final Logger LOGGER = > LoggerFactory.getLogger(SampleDBConfig.class); > + > + @Bean > + public SampleDB empireDatabase(DBContext context) { > + SampleDB db = new SampleDB(); > + db.open(context); > + if (context instanceof AutoCloseable closeable) { > + try { > + closeable.close(); > + } catch (Exception e) { > + LOGGER.warn("Failed to close Empire DB context after > initialization", e); > + } > + } > + return db; > + } > +} > diff --git > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleService.java > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleService.java > new file mode 100644 > index 00000000..3bf686bf > --- /dev/null > +++ > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleService.java > @@ -0,0 +1,507 @@ > +package org.apache.empire.samples.springboot; > + > +import org.apache.empire.commons.StringUtils; > +import org.apache.empire.data.Record; > +import org.apache.empire.data.bean.BeanResult; > +import org.apache.empire.data.list.DataListEntry; > +import org.apache.empire.db.DBColumnExpr; > +import org.apache.empire.db.DBCommand; > +import org.apache.empire.db.DBContext; > +import org.apache.empire.db.DBQuery; > +import org.apache.empire.db.DBReader; > +import org.apache.empire.db.DBRecord; > +import org.apache.empire.db.DBRecordBean; > +import org.apache.empire.db.DBRowSet; > +import org.apache.empire.samples.db.beans.Department; > +import org.apache.empire.samples.db.beans.Employee; > +import org.apache.empire.samples.db.beans.EmployeeQuery; > +import org.apache.empire.samples.db.beans.Payment; > +import org.apache.empire.samples.springboot.SampleDB.Gender; > +import org.apache.empire.xml.XMLWriter; > +import org.slf4j.Logger; > +import org.slf4j.LoggerFactory; > +import org.springframework.stereotype.Service; > +import org.springframework.transaction.annotation.Transactional; > +import org.w3c.dom.Document; > + > +import java.math.BigDecimal; > +import java.math.RoundingMode; > +import java.time.LocalDate; > +import java.util.List; > + > +@Transactional(readOnly = false) > +@Service > +public class SampleService { > + private static final Logger LOGGER = > LoggerFactory.getLogger(SampleService.class); > + > + private final DBContext context; > + private final SampleDB db; > + > + public SampleService(DBContext context, SampleDB db) { > + this.context = context; > + this.db = db; > + } > + > + /** > + * <PRE> > + * Empties all Tables. > + * </PRE> > + */ > + public void clearDatabase() { > + DBCommand cmd = context.createCommand(); > + // Delete all Payments (no constraints) > + context.executeDelete(db.PAYMENTS, cmd); > + // Delete all Employees (no constraints) > + context.executeDelete(db.EMPLOYEES, cmd); > + // Delete all Departments (no constraints) > + context.executeDelete(db.DEPARTMENTS, cmd); > + // commit > + context.commit(); > + } > + > + public int countEmployees() { > + DBCommand cmd = context.createCommand(); > + cmd.select(db.EMPLOYEES.count()); > + return context.getUtils().querySingleInt(cmd); > + } > + > + public void populateAndModify() { > + if (countEmployees() == 0) { > + clearDatabase(); > + > + LOGGER.info("Step 5: insertDepartment() & insertEmployee()"); > + long idDevDep = insertDepartment("Development", "ITTK"); > + long idSalDep = insertDepartment("Sales", "ITTK"); > + // Insert Employees > + long idEmp1 = insertEmployee(idDevDep, "Peter", "Sharp", > Gender.M, 25000); > + long idEmp2 = insertEmployee(idDevDep, "Fred", "Bloggs", > Gender.M, 0); > + long idEmp3 = insertEmployee(idSalDep, "Emma", "White", > Gender.F, 19500); > + long idEmp4 = insertEmployee(idSalDep, "John", "Doe", > Gender.M, 18800); > + long idEmp5 = insertEmployee(idDevDep, "Sarah", "Smith", > Gender.F, 44000); > + > + // commit > + context.commit(); > + > + // SECTION 6: Modify some data > + LOGGER.info("Step 6: updateEmployee()"); > + updateEmployee(idEmp1, "+49-7531-457160", true); > + updateEmployee(idEmp2, "+49-5555-505050", false); > + // Partial Record > + updatePartialRecord(idEmp3, "+49-040-125486"); > + // Update Joined Records (Make Fred Bloggs head of department > and set salary) > + updateJoinedRecords(idEmp2, 100000); > + } > + } > + > + /** > + * <PRE> > + * Insert a Department into the Departments table. > + * </PRE> > + */ > + private long insertDepartment(String departmentName, String > businessUnit) { > + SampleDB.Departments DEP = db.DEPARTMENTS; > + // Insert a Department > + DBRecord rec = new DBRecord(context, DEP); > + rec.create() > + .set(DEP.NAME, departmentName) > + .set(DEP.BUSINESS_UNIT, businessUnit) > + .update(); > + // Return Department ID > + return rec.getIdentity(); > + } > + > + /** > + * <PRE> > + * Inserts an Employee into the Employees table. > + * </PRE> > + */ > + private long insertEmployee(long departmentId, String firstName, > String lastName, Gender gender, int salary) { > + SampleDB.Employees EMP = db.EMPLOYEES; > + // Insert an Employee > + DBRecord rec = new DBRecord(context, EMP); > + rec.create(null) > + .set(EMP.DEPARTMENT_ID, departmentId) > + .set(EMP.FIRST_NAME, firstName) > + .set(EMP.LAST_NAME, lastName) > + .set(EMP.GENDER, gender) > + .set(EMP.SALARY, salary) > + .update(); > + // insert payments > + if (salary > 0) { > + insertPayments(rec); > + } > + // Return Employee ID > + return rec.getIdentity(); > + } > + > + /** > + * <PRE> > + * Inserts an Payments for a particular Employee > + * </PRE> > + */ > + private void insertPayments(DBRecord employee) { > + if (employee.isNull(db.EMPLOYEES.SALARY)) { > + return; // No salary > + } // monthlySalary > + BigDecimal monthlySalary = > employee.getDecimal(db.EMPLOYEES.SALARY).divide(new BigDecimal(12), 2, > RoundingMode.HALF_UP); > + // Insert an Employee > + LocalDate date = LocalDate.now(); > + date = date.minusDays(date.getDayOfMonth() - 1); // first day of > this month > + // Add Payment for each month > + SampleDB.Payments PAY = db.PAYMENTS; > + DBRecord rec = new DBRecord(context, PAY); > + int months = (int) (Math.random() * 6) + 17; > + for (LocalDate month = date.minusMonths(months); > !month.isAfter(date); month = month.plusMonths(1)) { > + BigDecimal variation = new BigDecimal((Math.random() * 200) - > 100.0); > + variation = variation.setScale(2, RoundingMode.HALF_UP); > + // insert > + rec.create(DBRecord.key(employee.getIdentity(), > month.getYear(), month.getMonth())); > + rec.set(PAY.AMOUNT, monthlySalary.add(variation)); > + rec.update(); > + } > + } > + > + /** > + * <PRE> > + * Updates an employee record by setting the phone number. > + * </PRE> > + */ > + private void updateEmployee(long idEmp, String phoneNumber, boolean > useRecord) { > + // Update an Employee > + if (useRecord) { > + // Use a DBRecord (recommended) > + DBRecord rec = new DBRecord(context, db.EMPLOYEES); > + rec.read(idEmp); > + // Set > + rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > + rec.update(); > + > + } else { // Or use a DBRecordBean: > + DBRecordBean rec = new DBRecordBean(); > + rec.read(context, db.EMPLOYEES, idEmp); > + // Set > + rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > + rec.update(context); > + } > + } > + > + /** > + * <PRE> > + * Updates an employee record by setting the phone number. > + * </PRE> > + */ > + private void updateJoinedRecords(long idEmp, int salary) { > + // Shortcuts for convenience > + SampleDB.Employees EMP = db.EMPLOYEES; > + SampleDB.Departments DEP = db.DEPARTMENTS; > + > + // Create DBQuery from command > + DBCommand cmd = context.createCommand(); > + cmd.select(EMP.getColumns()); > + cmd.select(DEP.getColumns()); > + cmd.join(EMP.DEPARTMENT_ID, DEP.ID); > + DBQuery query = new DBQuery(cmd, EMP.ID); > + > + // Make employee Head of Department and update salary > + DBRecord rec = new DBRecord(context, query); > + rec.read(idEmp); > + rec.set(EMP.SALARY, salary); > + rec.set(DEP.HEAD, rec.getString(EMP.LAST_NAME)); > + rec.update(); > + } > + > + /** > + * <PRE> > + * Updates an employee record by setting the phone number. > + * </PRE> > + */ > + private void updatePartialRecord(long employeeId, String phoneNumber) > { > + // Shortcut for convenience > + SampleDB.Employees EMP = db.EMPLOYEES; > + // Update an Employee with partial record > + // this will only load the EMPLOYEE ID and the PHONE_NUMBER > + DBRecord rec = new DBRecord(context, EMP); > + rec.read(Record.key(employeeId), DBRowSet.PartialMode.INCLUDE, > EMP.SALUTATION, EMP.FIRST_NAME, EMP.LAST_NAME, EMP.PHONE_NUMBER, EMP.EMAIL); > + // Set > + rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); > + rec.update(); > + } > + > + public void queryBeans() { > + SampleDB.Employees EMP = db.EMPLOYEES; > + > + DBCommand cmd = context.createCommand(); > + cmd.where(EMP.GENDER.is(Gender.M)); > + cmd.orderBy(EMP.LAST_NAME.desc()); > + List<Employee> list = context.getUtils().queryBeanList(cmd, > Employee.class, null); > + for (Employee emp : list) { > + System.out.println(emp.toString()); > + } > + > + // load department > + Department department = > context.getUtils().queryBean(Department.class, db.DEPARTMENTS.NAME.is > ("Sales")); > + Payment first = > department.getEmployees().get(0).getPayments().get(0); > + LOGGER.info("First payment amount is {}", first.getAmount()); > + > + // Query all males > + BeanResult<Employee> result = new > BeanResult<Employee>(Employee.class, EMP); > + result.getCommand().where(EMP.GENDER.is(Gender.M)); > + result.fetch(context); > + > + LOGGER.info("Number of male employees is: " + result.size()); > + > + // And now, the females > + result.getCommand().where(EMP.GENDER.is(Gender.F)); > + result.fetch(context); > + > + LOGGER.info("Number of female employees is: " + result.size()); > + } > + > + public void queryDataList() { > + int lastYear = LocalDate.now().getYear() - 1; > + > + // Define shortcuts for tables used - not necessary but convenient > + SampleDB.Employees EMP = db.EMPLOYEES; > + SampleDB.Departments DEP = db.DEPARTMENTS; > + SampleDB.Payments PAY = db.PAYMENTS; > + > + // Employee total query > + DBColumnExpr EMP_TOTAL = PAY.AMOUNT.sum().as("EMP_TOTAL"); > + DBCommand cmdEmpTotal = context.createCommand() > + .select(PAY.EMPLOYEE_ID, EMP_TOTAL) > + .where(PAY.YEAR.is(lastYear)) > + .groupBy(PAY.EMPLOYEE_ID); > + DBQuery Q_EMP_TOTAL = new DBQuery(cmdEmpTotal, "qet"); > + > + // Department total query > + DBColumnExpr DEP_TOTAL = PAY.AMOUNT.sum().as("DEP_TOTAL"); > + DBCommand cmdDepTotal = context.createCommand() > + .select(EMP.DEPARTMENT_ID, DEP_TOTAL) > + .join(PAY.EMPLOYEE_ID, EMP.ID) > + .where(PAY.YEAR.is(lastYear)) > + .groupBy(EMP.DEPARTMENT_ID); > + DBQuery Q_DEP_TOTAL = new DBQuery(cmdDepTotal, "qdt"); > + > + // Percentage of department > + DBColumnExpr PCT_OF_DEP_COST = > Q_EMP_TOTAL.column(EMP_TOTAL).multiplyWith(100).divideBy(Q_DEP_TOTAL.column(DEP_TOTAL)); > + // Create the employee query > + DBCommand cmd = context.createCommand() > + .select(EMP.ID, EMP.FIRST_NAME, EMP.LAST_NAME, > DEP.NAME.as("DEPARTMENT")) > + .select(Q_EMP_TOTAL.column(EMP_TOTAL)) > + .select(PCT_OF_DEP_COST.as("PCT_OF_DEPARTMENT_COST")) > + // join Employee with Department > + .join(EMP.DEPARTMENT_ID, DEP.ID) > + // Join with Subqueries > + .joinLeft(EMP.ID, Q_EMP_TOTAL.column(PAY.EMPLOYEE_ID)) > + .joinLeft(DEP.ID, Q_DEP_TOTAL.column(EMP.DEPARTMENT_ID)) > + // Order by > + .orderBy(DEP.NAME.desc()) > + .orderBy(EMP.LAST_NAME); > + > + List<DataListEntry> list = context.getUtils().queryDataList(cmd); > + /* uncomment this to print full list > + for (DataListEntry dle : list) > + System.out.println(dle.toString()); > + */ > + for (DataListEntry dle : list) { > + long empId = dle.getRecordId(EMP); > + // int depId = dle.getId(DEP); > + // Put the comma between last and first name (Last, First) > + String empName = > StringUtils.concat(dle.getString(EMP.LAST_NAME), ", ", > dle.getString(EMP.FIRST_NAME)); > + String depName = dle.getString(DEP.NAME); > + boolean hasPayments = > !dle.isNull(Q_EMP_TOTAL.column(EMP_TOTAL)); > + if (hasPayments) { // report > + BigDecimal empTotal = > dle.getDecimal(Q_EMP_TOTAL.column(EMP_TOTAL)); > + BigDecimal pctOfDep = > dle.getDecimal(PCT_OF_DEP_COST).setScale(1, RoundingMode.HALF_UP); > + LOGGER.info("Employee[{}]: {}\tDepartment: {}\tPayments: > {} ({}% of Department)", empId, empName, depName, empTotal, pctOfDep); > + } else { > + LOGGER.info("Employee[{}]: {}\tDepartment: {}\tPayments: > [No data avaiable]", empId, empName, depName); > + } > + } > + > + /* > + cmd.where(EMP.ID.is(list.get(0).getRecordId(EMP))); > + DataListEntry emp1 = context.getUtils().queryDataEntry(cmd); > + System.out.println(emp1.toString()); > + > + cmd.where(EMP.ID.is(list.get(1).getRecordId(EMP))); > + DataListEntry emp2 = context.getUtils().queryDataEntry(cmd); > + System.out.println(emp2.toString()); > + */ > + } > + > + /** > + * <PRE> > + * Performs an SQL-Query and prints the result to System.out > + * <p> > + * First a DBCommand object is used to create the following SQL-Query > (Oracle-Syntax): > + * <p> > + * SELECT t2.EMPLOYEE_ID, t2.LASTNAME || ', ' || t2.FIRSTNAME AS > FULL_NAME, t2.GENDER, t2.PHONE_NUMBER, > + * substr(t2.PHONE_NUMBER, > length(t2.PHONE_NUMBER)-instr(reverse(t2.PHONE_NUMBER), '-')+2) AS > PHONE_EXTENSION, > + * t1.NAME AS DEPARTMENT, t1.BUSINESS_UNIT > + * FROM EMPLOYEES t2 INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = > t2.ID > + * WHERE length(t2.LASTNAME)>0 > + * ORDER BY t2.LASTNAME, t2.FIRSTNAME > + * <p> > + * For processing the rows there are three options available: > + * <p> > + * QueryType.Reader: > + * Iterates through all rows and prints field values as tabbed text. > + * <p> > + * QueryType.BeanList: > + * Obtains the query result as a list of JavaBean objects of type > SampleBean. > + * It then iterates through the list of beans and uses > bean.toString() for printing. > + * <p> > + * QueryType.XmlDocument: > + * Obtains the query result as an XML-Document and prints the > document. > + * Please note, that the XML not only contains the data but also the > field metadata. > + * </PRE> > + */ > + public void queryExample(QueryType queryType) { > + int lastYear = LocalDate.now().getYear() - 1; > + > + // Define shortcuts for tables used - not necessary but convenient > + SampleDB.Employees EMP = db.EMPLOYEES; > + SampleDB.Departments DEP = db.DEPARTMENTS; > + SampleDB.Payments PAY = db.PAYMENTS; > + > + // The following expression concats lastname + ', ' + firstname > + DBColumnExpr EMPLOYEE_NAME = EMP.LAST_NAME.append(", > ").append(EMP.FIRST_NAME).as("EMPLOYEE_NAME"); > + DBColumnExpr PAYMENTS_LAST_YEAR = > PAY.AMOUNT.sum().as("PAYMENTS_LAST_YEAR"); > + > + /* > + // Example: Extracts the extension number from the phone field > + // e.g. substr(PHONE_NUMBER, > length(PHONE_NUMBER)-instr(reverse(PHONE_NUMBER), '-')+2) AS PHONE_EXTENSION > + // Hint: Since the reverse() function is not supported by HSQLDB > there is special treatment for HSQL > + DBColumnExpr PHONE_LAST_DASH; > + if ( db.getDbms() instanceof DBMSHandlerHSql > + || db.getDbms() instanceof DBMSHandlerDerby > + || db.getDbms() instanceof DBMSHandlerH2) > + PHONE_LAST_DASH = EMP.PHONE_NUMBER.indexOf("-", > EMP.PHONE_NUMBER.indexOf("-").plus(1)).plus(1); // HSQLDB only > + else PHONE_LAST_DASH = > EMP.PHONE_NUMBER.length().minus(EMP.PHONE_NUMBER.reverse().indexOf("-")).plus(2); > + DBColumnExpr PHONE_EXT_NUMBER = > EMP.PHONE_NUMBER.substring(PHONE_LAST_DASH).as("PHONE_EXTENSION"); > + */ > + > + /* > + // Example: Select the Gender-Enum as String > + // e.g. case t2.GENDER when 'U' then 'Unknown' when 'M' then > 'Male' when 'F' then 'Female' end > + DBColumnExpr GENDER_NAME = > EMP.GENDER.decode(EMP.GENDER.getOptions()).as("GENDER_NAME"); > + */ > + // Select Employee and Department columns > + DBCommand cmd = context.createCommand() > + .selectQualified(EMP.ID) // select "EMPLOYEE_ID" > + .select(EMPLOYEE_NAME, EMP.GENDER, EMP.PHONE_NUMBER, > EMP.SALARY) > + .selectQualified(DEP.NAME) // "DEPARMENT_NAME" > + .select(DEP.BUSINESS_UNIT) // "BUSINESS_UNIT" > + // Joins > + .join(EMP.DEPARTMENT_ID, DEP.ID) > + .joinLeft(EMP.ID, PAY.EMPLOYEE_ID, PAY.YEAR.is(lastYear)) > + // Where constraints > + .where(EMP.LAST_NAME.length().isGreaterThan(0)) // always > true, just for show > + .where(EMP.GENDER.in(Gender.M, Gender.F)) // always > true, just for show > + .where(EMP.RETIRED.is(false)) // always true, just for > show > + // Order by > + .orderBy(EMPLOYEE_NAME); > + > + // Add payment of last year using a SUM aggregation > + cmd.groupBy(cmd.getSelectExpressions()); > + cmd.select(PAYMENTS_LAST_YEAR); > + > + /* > + * Example for limitRows() and skipRows() > + * Uncomment if you wish > + * > + if (db.getDbms().isSupported(DBMSFeature.QUERY_LIMIT_ROWS)) > + { // set maximum number of rows > + cmd.limitRows(20); > + if (db.getDbms().isSupported(DBMSFeature.QUERY_SKIP_ROWS)) > + cmd.skipRows(1); > + } > + */ > + // Query Records and print output > + DBReader reader = new DBReader(context); > + try { > + // log select statement (but only once) > + if (queryType == QueryType.Reader) { > + LOGGER.info("Running Query: {}", cmd.getSelect()); > + } > + // Open Reader > + reader.open(cmd); > + // Print output > + System.out.println("---------------------------------"); > + switch (queryType) { > + case Reader: > + // Text-Output by iterating through all records. > + while (reader.moveNext()) { > + System.out.println(reader.getText(EMP.ID) > + + "\t" + reader.getText(EMPLOYEE_NAME) > + + "\t" + reader.getText(EMP.GENDER) > + + "\t" + reader.getText(EMP.SALARY) > + + "\t" + > reader.getText(PAYMENTS_LAST_YEAR) > + + "\t" + reader.getText(DEP.NAME)); > + } > + break; > + case BeanList: > + // Text-Output using a list of Java Beans supplied by > the DBReader > + List<EmployeeQuery> beanList = > reader.getBeanList(EmployeeQuery.class); > + // log.info(String.valueOf(beanList.size()) + " > SampleBeans returned from Query."); > + for (EmployeeQuery b : beanList) { > + System.out.println(b.toString()); > + } > + break; > + case XmlDocument: > + // XML Output > + Document doc = reader.getXmlDocument(); > + // Print XML Document to System.out > + XMLWriter.debug(doc); > + break; > + } > + System.out.println("---------------------------------"); > + } finally { > + // Always close Reader! > + reader.close(); > + } > + } > + > + protected void queryRecordList() { > + SampleDB.Departments DEP = db.DEPARTMENTS; > + SampleDB.Employees EMP = db.EMPLOYEES; > + /* > + * Test RecordList > + */ > + DBCommand cmd = context.createCommand(); > + cmd.join(EMP.DEPARTMENT_ID, DEP.ID); > + cmd.where(DEP.NAME.is("Development")); > + // query now > + List<DBRecordBean> list = context.getUtils().queryRecordList(cmd, > EMP, DBRecordBean.class); > + LOGGER.info("RecordList query found {} employees in Development > department", list.size()); > + for (DBRecordBean record : list) { > + Object[] key = record.getKey(); > + // print info > + String empName = > StringUtils.concat(record.getString(EMP.LAST_NAME), ", ", > record.getString(EMP.FIRST_NAME)); > + String phone = record.getString(EMP.PHONE_NUMBER); > + BigDecimal salary = record.getDecimal(EMP.SALARY); > + LOGGER.info("Employee[{}]: {}\tPhone: {}\tSalary: {}", > StringUtils.toString(key), empName, phone, salary); > + // modify salary > + BigDecimal newSalary = new BigDecimal(2000 + ((Math.random() > * 200) - 100.0)); > + record.set(EMP.SALARY, newSalary); > + // check > + if (record.wasModified(EMP.SALARY)) { // Salary was modified > + LOGGER.info("Salary was modified for {}. New salary is > {}", empName, record.getDecimal(EMP.SALARY)); > + } > + // udpate the record > + record.update(context); > + > + // convert to bean > + Employee employee = new Employee(); > + record.setBeanProperties(employee); > + System.out.println(employee); > + } > + } > + > + public enum QueryType { > + Reader, > + BeanList, > + XmlDocument > + } > +} > diff --git > a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/springboot/DBContextSpring.java > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/springboot/DBContextSpring.java > new file mode 100644 > index 00000000..f16ead33 > --- /dev/null > +++ > b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/springboot/DBContextSpring.java > @@ -0,0 +1,113 @@ > +package org.apache.empire.springboot; > + > +import jakarta.annotation.PreDestroy; > +import org.apache.empire.db.context.DBContextBase; > +import org.apache.empire.db.context.DBRollbackManager; > +import org.apache.empire.db.exceptions.EmpireSQLException; > +import org.apache.empire.dbms.DBMSHandler; > +import org.slf4j.Logger; > +import org.slf4j.LoggerFactory; > +import org.springframework.jdbc.datasource.DataSourceUtils; > + > +import javax.sql.DataSource; > +import java.sql.Connection; > +import java.sql.SQLException; > + > +public class DBContextSpring extends DBContextBase implements > AutoCloseable { > + private static final Logger LOGGER = > LoggerFactory.getLogger(DBContextSpring.class); > + > + private final DataSource dataSource; > + private final DBMSHandler dbmsHandler; > + > + private final ThreadLocal<Connection> connectionHolder = new > ThreadLocal<>(); > + > + public DBContextSpring(DataSource dataSource, DBMSHandler > dbmsHandler) { > + this.dataSource = dataSource; > + this.dbmsHandler = dbmsHandler; > + } > + > + /** > + * Get a connection from the Spring-managed DataSource. > + * <p> > + * the Spring Boot autoconfigured DataSource provides connections > that work with @Transactional, provided Spring manages the transaction. > + * Key points: > + * Spring binds the Connection to the transaction (e.g., > DataSourceTransactionManager for JDBC). Repeated access within the same > @Transactional method returns the same Connection. > + * Use Spring abstractions (e.g., JdbcTemplate, > NamedParameterJdbcTemplate, JPA/EntityManager) or > DataSourceUtils.getConnection(...). Then you are transaction-aware > automatically. > + * If you call dataSource.getConnection() directly you can bypass > Spring's transaction binding — that Connection won’t automatically > participate in the @Transactional transaction. > + * Hikari provides proxy connections (pooling, auto-commit handling) > and is compatible with Spring transactions. > + * Do not call conn.setAutoCommit(false) manually when Spring manages > the transaction. > + * <p> > + * Summary: Spring Boot provided DataSources work with @Transactional > as long as you use Spring’s access paths or DataSourceUtils and have the > appropriate TransactionManager active. > + * > + * @param readOnly if true, the connection will be set to read-only > mode. > + * @return a Connection object that is managed by Spring's > transaction management. > + */ > + @Override > + protected Connection getConnection(boolean readOnly) { > + Connection conn = connectionHolder.get(); > + try { > + if (conn == null || conn.isClosed()) { > + conn = DataSourceUtils.getConnection(dataSource); > + if (readOnly) { > + conn.setReadOnly(true); > + } > + connectionHolder.set(conn); > + LOGGER.debug("Obtained Spring-managed connection {}", > conn); > + } > + return conn; > + } catch (SQLException e) { > + throw new EmpireSQLExcep
