Add Spring Boot example
diff --git a/empire-db-examples/empire-db-example-spring-boot/README.md b/empire-db-examples/empire-db-example-spring-boot/README.md
new file mode 100644
index 0000000..f6817f6
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/README.md
@@ -0,0 +1,13 @@
+# Empire-DB Spring Boot Example
+
+This example uses the convenience configuration options provided by Spring Boot.
+Instead of `config.xml` all configuration can be found in `application.yml`.
+
+This example builds an executable JAR you can build and execute from this folder like this:
+
+```sh
+$ mvn clean install
+$ java -jar target/empire-db-example-spring-boot-3.0.0-SNAPSHOT.jar
+```
+
+An embedded hsqldb is used by default.
\ No newline at end of file
diff --git a/empire-db-examples/empire-db-example-spring-boot/pom.xml b/empire-db-examples/empire-db-example-spring-boot/pom.xml
new file mode 100644
index 0000000..59b1132
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/pom.xml
@@ -0,0 +1,69 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ 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.
+-->
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
+ <modelVersion>4.0.0</modelVersion>
+
+ <parent>
+ <groupId>org.springframework.boot</groupId>
+ <artifactId>spring-boot-starter-parent</artifactId>
+ <version>2.6.3</version>
+ <relativePath/> <!-- lookup parent from repository -->
+ </parent>
+
+ <name>Apache Empire-db Spring Boot Example</name>
+ <groupId>org.apache.empire-db</groupId>
+ <artifactId>empire-db-example-spring-boot</artifactId>
+ <version>3.0.0-SNAPSHOT</version>
+ <packaging>jar</packaging>
+
+ <dependencies>
+ <dependency>
+ <groupId>org.apache.empire-db</groupId>
+ <artifactId>empire-db</artifactId>
+ <version>3.0.0-SNAPSHOT</version>
+ </dependency>
+
+ <dependency>
+ <groupId>org.springframework.boot</groupId>
+ <artifactId>spring-boot-starter</artifactId>
+ </dependency>
+
+ <dependency>
+ <groupId>org.springframework.boot</groupId>
+ <artifactId>spring-boot-starter-jdbc</artifactId>
+ </dependency>
+
+ <dependency>
+ <groupId>org.hsqldb</groupId>
+ <artifactId>hsqldb</artifactId>
+ <scope>runtime</scope>
+ </dependency>
+ </dependencies>
+
+ <build>
+ <plugins>
+ <!-- Package as an executable jar -->
+ <plugin>
+ <groupId>org.springframework.boot</groupId>
+ <artifactId>spring-boot-maven-plugin</artifactId>
+ </plugin>
+ </plugins>
+ </build>
+</project>
diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Department.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Department.java
new file mode 100644
index 0000000..46e5292
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Department.java
@@ -0,0 +1,75 @@
+/*
+ * 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.beans;
+
+import java.util.List;
+
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBContext;
+import org.apache.empire.db.list.DataBean;
+import org.apache.empire.samples.springboot.SampleDB;
+
+public class Department implements DataBean<SampleDB>
+{
+ private long id; // "ID"
+ private String name; // "FIRSTNAME"
+ private String head; // "FIRSTNAME"
+
+ private List<Employee> employees;
+
+ public long getId()
+ {
+ return id;
+ }
+ public void setId(long id)
+ {
+ this.id = id;
+ }
+ public String getName()
+ {
+ return name;
+ }
+ public void setName(String name)
+ {
+ this.name = name;
+ }
+ public String getHead()
+ {
+ return head;
+ }
+ public void setHead(String head)
+ {
+ this.head = head;
+ }
+
+ public List<Employee> getEmployees()
+ {
+ return employees;
+ }
+
+ @Override
+ public void initialize(SampleDB db, DBContext context, int rownum, Object parent)
+ {
+ DBCommand cmd = context.createCommand();
+ cmd.where(db.EMPLOYEES.DEPARTMENT_ID.is(this.id));
+ cmd.orderBy(db.EMPLOYEES.FIRST_NAME, db.EMPLOYEES.LAST_NAME);
+ employees = context.getUtils().queryBeanList(cmd, Employee.class, db.EMPLOYEES, this);
+ }
+
+}
diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Employee.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Employee.java
new file mode 100644
index 0000000..3f41c49
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Employee.java
@@ -0,0 +1,294 @@
+/*
+ * 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.beans;
+
+import java.math.BigDecimal;
+import java.util.Date;
+import java.util.List;
+import java.util.Locale;
+
+import org.apache.empire.commons.DateUtils;
+import org.apache.empire.commons.StringUtils;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBContext;
+import org.apache.empire.db.list.DataBean;
+import org.apache.empire.samples.springboot.SampleDB;
+import org.apache.empire.samples.springboot.SampleDB.Gender;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+/**
+ * This is an employee entity bean
+ * @author doebele
+ *
+ */
+public class Employee implements DataBean<SampleDB>
+{
+ private static final Logger log = LoggerFactory.getLogger(Employee.class);
+
+ private long id; // "ID"
+ private String salutation; // "SALUTATION"
+ private String firstName; // "FIRST_NAME"
+ private String lastName; // "LAST_NAME"
+ private Date dateOfBirth; // "DATE_OF_BIRTH"
+ private long departmentId;// "DEPARTMENT_ID"
+ private Gender gender; // "GENDER"
+ private String phoneNumber; // "PHONE_NUMBER"
+ private String email; // "EMAIL"
+ private BigDecimal salary; // "SALARY"
+ private boolean retired; // "RETIRED"
+
+ private Department department;
+ private List<Payment> payments;
+
+ int rownum;
+
+ /**
+ * Constructor using all fields from the table EMPLOYEES
+ * @param id
+ * @param firstname
+ * @param lastname
+ * @param dateOfBirth
+ * @param departmentId
+ * @param gender
+ * @param phoneNumber
+ * @param salary
+ * @param retired
+ public Employee(long id, String salutation, String firstName, String lastName, Date dateOfBirth, long departmentId, Gender gender,
+ String phoneNumber, String email, BigDecimal salary, boolean retired, Timestamp timestamp)
+ {
+ this.id = id;
+ this.salutation = salutation;
+ this.firstName = firstName;
+ this.lastName = lastName;
+ this.dateOfBirth = dateOfBirth;
+ this.departmentId = departmentId;
+ this.gender = gender;
+ this.phoneNumber = phoneNumber;
+ this.email = email;
+ this.salary = salary;
+ this.retired = retired;
+
+ log.info("Employee bean created using fields constructor");
+ }
+ */
+
+ /**
+ * Constructor using fields but without timestamp
+ */
+ public Employee(long id, String salutation, String firstName, String lastName, Date dateOfBirth, long departmentId, Gender gender,
+ String phoneNumber, String email, BigDecimal salary, boolean retired)
+ {
+ this.id = id;
+ this.salutation = salutation;
+ this.firstName = firstName;
+ this.lastName = lastName;
+ this.dateOfBirth = dateOfBirth;
+ this.departmentId = departmentId;
+ this.gender = gender;
+ this.phoneNumber = phoneNumber;
+ this.email = email;
+ this.salary = salary;
+ this.retired = retired;
+
+ log.info("Employee bean created using fields constructor without timestamp");
+ }
+
+ /**
+ * Constructor using primary key fields
+ */
+ public Employee(long id)
+ {
+ this.id = id;
+ log.info("Employee bean created using primary key constructor");
+ }
+
+ /**
+ * Standard Constructor
+ */
+ public Employee()
+ {
+ // Standard constructor
+ log.info("Employee bean created using standard constructor");
+ }
+
+ public String getSalutation()
+ {
+ return salutation;
+ }
+
+ public void setSalutation(String salutation)
+ {
+ this.salutation = salutation;
+ }
+
+ public String getFirstName()
+ {
+ return firstName;
+ }
+
+ public void setFirstName(String firstName)
+ {
+ this.firstName = firstName;
+ }
+
+ public String getLastName()
+ {
+ return lastName;
+ }
+
+ public void setLastName(String lastName)
+ {
+ this.lastName = lastName;
+ }
+
+ public Date getDateOfBirth()
+ {
+ return dateOfBirth;
+ }
+
+ public void setDateOfBirth(Date dateOfBirth)
+ {
+ this.dateOfBirth = dateOfBirth;
+ }
+
+ public long getDepartmentId()
+ {
+ return departmentId;
+ }
+
+ public void setDepartmentId(long departmentId)
+ {
+ this.departmentId = departmentId;
+ }
+
+ public Gender getGender()
+ {
+ return gender;
+ }
+
+ public void setGender(Gender gender)
+ {
+ this.gender = gender;
+ }
+
+ public String getPhoneNumber()
+ {
+ return phoneNumber;
+ }
+
+ public void setPhoneNumber(String phoneNumber)
+ {
+ this.phoneNumber = phoneNumber;
+ }
+
+ public String getEmail()
+ {
+ return email;
+ }
+
+ public void setEmail(String email)
+ {
+ this.email = email;
+ }
+
+ public BigDecimal getSalary()
+ {
+ return salary;
+ }
+
+ public void setSalary(BigDecimal salary)
+ {
+ this.salary = salary;
+ }
+
+ public boolean isRetired()
+ {
+ return retired;
+ }
+
+ public void setRetired(boolean retired)
+ {
+ this.retired = retired;
+ }
+
+ public long getId()
+ {
+ return id;
+ }
+
+ public int getRownum()
+ {
+ return rownum;
+ }
+
+ public Department getDepartment()
+ {
+ return department;
+ }
+
+ public List<Payment> getPayments()
+ {
+ return payments;
+ }
+
+ @Override
+ public String toString()
+ {
+ StringBuffer buf = new StringBuffer();
+ buf.append(rownum);
+ buf.append("\t");
+ buf.append(id);
+ buf.append("\t");
+ buf.append(firstName);
+ buf.append("\t");
+ buf.append(lastName);
+ buf.append("\t");
+ buf.append(DateUtils.formatDate(dateOfBirth, Locale.US));
+ buf.append("\t");
+ buf.append(gender);
+ buf.append("\t");
+ buf.append(salary);
+ buf.append("\t");
+ buf.append(retired);
+ return buf.toString();
+ }
+
+ @Override
+ public void initialize(SampleDB db, DBContext context, int rownum, Object parent)
+ {
+ this.rownum = rownum;
+
+ if (parent instanceof Department)
+ department = ((Department)parent);
+ else
+ log.warn("Department Entity has not been provided.");
+ // Don't to that: (because of recursion)
+ // else department = context.getUtils().queryBean(Department.class, DBRecord.key(this.departmentId));
+
+ DBCommand cmd = context.createCommand();
+ cmd.where(db.PAYMENTS.EMPLOYEE_ID.is(this.id));
+ cmd.orderBy(db.PAYMENTS.YEAR.desc());
+ cmd.orderBy(db.PAYMENTS.MONTH.desc());
+ payments = context.getUtils().queryBeanList(cmd, Payment.class, this);
+ log.info("{} Payments have been loaded for Employee {}.", payments.size(), StringUtils.concat(" ", this.firstName, this.lastName));
+ }
+
+}
+
diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/EmployeeQuery.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/EmployeeQuery.java
new file mode 100644
index 0000000..ad7fdd0
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/EmployeeQuery.java
@@ -0,0 +1,158 @@
+/*
+ * 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.beans;
+
+import java.math.BigDecimal;
+
+import org.apache.empire.samples.springboot.SampleDB.Gender;
+
+/**
+ * The SampleBean class is used to demonstrate JavaBean support for SQL-Queries.
+ * The SampleBean is used in the SampleApp's queryRecords function.
+ */
+public class EmployeeQuery
+{
+ private int employeeId;
+ private String employeeName;
+ private Gender gender;
+ private String phoneNumber;
+ private BigDecimal salary;
+ private String departmentName;
+ private String businessUnit;
+ private BigDecimal paymentsLastYear;
+
+ /*
+ * Uncomment this if you want to use constructor instead of setters
+ * Number of arguments and data types must match query!
+ *
+ public EmployeeQuery(int employeeId, String employeeName, Gender gender, String phoneNumber, BigDecimal salary
+ , String departmentName, String businessUnit, BigDecimal paymentsLastYear)
+ {
+ this.employeeId = employeeId;
+ this.employeeName = employeeName;
+ this.gender = gender;
+ this.phoneNumber = phoneNumber;
+ this.salary = salary;
+ this.departmentName = departmentName;
+ this.businessUnit = businessUnit;
+ this.paymentsLastYear = paymentsLastYear;
+ }
+ */
+
+ public int getEmployeeId()
+ {
+ return employeeId;
+ }
+
+ public void setEmployeeId(int employeeId)
+ {
+ this.employeeId = employeeId;
+ }
+
+ public String getEmployeeName()
+ {
+ return employeeName;
+ }
+
+ public void setEmployeeName(String employeeName)
+ {
+ this.employeeName = employeeName;
+ }
+
+ public Gender getGender()
+ {
+ return gender;
+ }
+
+ public void setGender(Gender gender)
+ {
+ this.gender = gender;
+ }
+
+ public String getPhoneNumber()
+ {
+ return phoneNumber;
+ }
+
+ public void setPhoneNumber(String phoneNumber)
+ {
+ this.phoneNumber = phoneNumber;
+ }
+
+ public BigDecimal getSalary()
+ {
+ return salary;
+ }
+
+ public void setSalary(BigDecimal salary)
+ {
+ this.salary = salary;
+ }
+
+ public String getDepartmentName()
+ {
+ return departmentName;
+ }
+
+ public void setDepartmentName(String departmentName)
+ {
+ this.departmentName = departmentName;
+ }
+
+ public String getBusinessUnit()
+ {
+ return businessUnit;
+ }
+
+ public void setBusinessUnit(String businessUnit)
+ {
+ this.businessUnit = businessUnit;
+ }
+
+ public BigDecimal getPaymentsLastYear()
+ {
+ return paymentsLastYear;
+ }
+
+ public void setPaymentsLastYear(BigDecimal paymentsLastYear)
+ {
+ this.paymentsLastYear = paymentsLastYear;
+ }
+
+ @Override
+ public String toString()
+ {
+ StringBuffer buf = new StringBuffer();
+ buf.append(employeeId);
+ buf.append("\t");
+ buf.append(employeeName);
+ buf.append("\t");
+ buf.append(gender);
+ buf.append("\t");
+ buf.append(departmentName);
+ buf.append("\t");
+ buf.append(businessUnit);
+ buf.append("\t");
+ buf.append(salary);
+ buf.append("\t");
+ buf.append(paymentsLastYear);
+ return buf.toString();
+ }
+
+}
diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Payment.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Payment.java
new file mode 100644
index 0000000..d1c0027
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/db/beans/Payment.java
@@ -0,0 +1,83 @@
+/*
+ * 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.beans;
+
+import java.math.BigDecimal;
+
+import org.apache.empire.db.DBContext;
+import org.apache.empire.db.list.DataBean;
+import org.apache.empire.samples.springboot.SampleDB;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class Payment implements DataBean<SampleDB>
+{
+ private static final Logger log = LoggerFactory.getLogger(Payment.class);
+
+ private long employeeId;
+ private BigDecimal year;
+ private BigDecimal month;
+ private BigDecimal amount;
+
+ private Employee employee;
+
+ public Payment(long employeeId, BigDecimal year, BigDecimal month, BigDecimal amount)
+ {
+ super();
+ this.employeeId = employeeId;
+ this.year = year;
+ this.month = month;
+ this.amount = amount;
+ }
+
+ public long getEmployeeId()
+ {
+ return employeeId;
+ }
+
+ public BigDecimal getYear()
+ {
+ return year;
+ }
+
+ public BigDecimal getMonth()
+ {
+ return month;
+ }
+
+ public BigDecimal getAmount()
+ {
+ return amount;
+ }
+
+ public Employee getEmployee()
+ {
+ return employee;
+ }
+
+ @Override
+ public void initialize(SampleDB db, DBContext context, int rownum, Object parent)
+ {
+ if (parent instanceof Employee)
+ this.employee = (Employee)parent;
+ else
+ log.warn("Employee Entity has not been provided.");
+ }
+
+}
diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/EmpireDBConfigProperties.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/EmpireDBConfigProperties.java
new file mode 100644
index 0000000..ab4d427
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/EmpireDBConfigProperties.java
@@ -0,0 +1,47 @@
+/*
+ * 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.springboot;
+
+import java.util.Map;
+import org.springframework.boot.context.properties.ConfigurationProperties;
+import org.springframework.context.annotation.Configuration;
+
+@Configuration
+@ConfigurationProperties(prefix = "empiredb")
+public class EmpireDBConfigProperties {
+
+ private String dbmsHandlerClass;
+ private Map<String, String> dbmsHandlerProperties;
+
+ public String getDbmsHandlerClass() {
+ return dbmsHandlerClass;
+ }
+
+ public Map<String, String> getDbmsHandlerProperties() {
+ return dbmsHandlerProperties;
+ }
+
+ public void setDbmsHandlerClass(String dbmsHandlerClass) {
+ this.dbmsHandlerClass = dbmsHandlerClass;
+ }
+
+ public void setDbmsHandlerProperties(Map<String, String> dbmsHandlerProperties) {
+ this.dbmsHandlerProperties = dbmsHandlerProperties;
+ }
+}
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
new file mode 100644
index 0000000..92075d0
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java
@@ -0,0 +1,708 @@
+/*
+ * 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.springboot;
+
+import java.lang.reflect.InvocationTargetException;
+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.commons.beanutils.BeanUtils;
+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.db.generic.TRecord;
+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.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.beans.factory.annotation.Autowired;
+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;
+
+/**
+ * Implementing ApplicationRunner interface tells Spring Boot to automatically call the run method AFTER the application context has been loaded.
+ */
+@SpringBootApplication
+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).createReverseFunction(conn);
+ }
+
+ // 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
+ TRecord<SampleDB.Departments> rec = new TRecord<SampleDB.Departments>(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();
+ }
+ }
+
+ 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");
+ updateEmployee(idEmp2, "+49-5555-505050");
+ // 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;
+
+ // 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);
+ 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("Eployee[{}]: {}\tDepartment: {}\tPayments: {} ({}% of Department)", empId, empName, depName, empTotal, pctOfDep);
+ } else {
+ LOGGER.info("Eployee[{}]: {}\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
+ *
+ * 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;
+
+ // 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();
+ }
+ }
+
+ 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("Eployee[{}]: {}\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.toString());
+ }
+ }
+
+ /**
+ * 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);
+ }
+
+ 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();
+ try {
+ BeanUtils.setProperty(bean, name, newValue);
+
+ Object value = BeanUtils.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);
+ }
+ } catch (IllegalAccessException ex) {
+ LOGGER.error(null, ex);
+ } catch (InvocationTargetException ex) {
+ LOGGER.error(null, ex);
+ } catch (NoSuchMethodException ex) {
+ LOGGER.error("Property '{}' not found in {}", name, bean.getClass().getName());
+ }
+ }
+ }
+ }
+
+ @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) {
+ /*
+ // Update an Employee
+ DBRecord rec = new DBRecord(context, db.EMPLOYEES);
+ rec.read(idEmp);
+ // Set
+ rec.set(db.EMPLOYEES.PHONE_NUMBER, phoneNumber);
+ rec.update();
+ */
+
+ 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
new file mode 100644
index 0000000..bcd7dfe
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.java
@@ -0,0 +1,270 @@
+/*
+ * 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.springboot;
+
+import java.sql.SQLException;
+
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBContext;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+import org.apache.empire.db.validation.DBModelChecker;
+import org.apache.empire.db.validation.DBModelErrorLogger;
+import org.apache.empire.dbms.DBMSHandler;
+import org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL;
+import org.apache.empire.samples.db.beans.Department;
+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;
+
+/**
+ * <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)
+ * and to check the datamodel i.e. to compare this definition to the database in the DBMS
+ *
+ * PLEASE NOTE THE NAMING CONVENTION:
+ * Since all tables, views and columns are declared as "final" constants we have all put them in upper case.
+ *
+ * You may declare other database tables or views in the same way.
+ * </PRE>
+ */
+@Component
+public class SampleDB extends DBDatabase
+{
+ // Logger
+ private static final Logger log = LoggerFactory.getLogger(SampleDB.class);
+
+ /**
+ * Gender enum
+ */
+ public enum Gender
+ {
+ M("Male"),
+ F("Female"),
+ U("Unknown");
+
+ private final String title;
+ private Gender(String title)
+ {
+ this.title = title;
+ }
+ @Override
+ public String toString()
+ {
+ return title;
+ }
+ }
+
+ /**
+ * This class represents the Departments table.
+ */
+ 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)
+ {
+ 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");
+
+ // Primary Key (automatically set due to addIdentity()) otherwise use
+ // setPrimaryKey(...);
+
+ // Set other Indexes
+ addIndex("DEPARTMENT_NAME_IDX", true, new DBColumn[] { NAME });
+
+ // Set beanType (optional)
+ setBeanType(Department.class);
+ }
+ }
+
+ /**
+ * This class represents the Employees table.
+ */
+ public static class Employees extends DBTable
+ {
+ public final DBTableColumn ID;
+ public final DBTableColumn SALUTATION;
+ public final DBTableColumn FIRST_NAME;
+ public final DBTableColumn LAST_NAME;
+ public final DBTableColumn DATE_OF_BIRTH;
+ public final DBTableColumn DEPARTMENT_ID;
+ public final DBTableColumn GENDER;
+ public final DBTableColumn PHONE_NUMBER;
+ public final DBTableColumn EMAIL;
+ public final DBTableColumn SALARY;
+ public final DBTableColumn RETIRED;
+ public final DBTableColumn UPDATE_TIMESTAMP;
+
+ 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
+ // setPrimaryKey(...);
+
+ // Set other Indexes
+ addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { FIRST_NAME, LAST_NAME, DATE_OF_BIRTH });
+
+ // Set beanType (optional)
+ setBeanType(Employee.class);
+ }
+ }
+
+ /**
+ * This class represents the Payments table.
+ */
+ 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)
+ {
+ 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);
+
+ // 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/resources/application.yml b/empire-db-examples/empire-db-example-spring-boot/src/main/resources/application.yml
new file mode 100644
index 0000000..0f944d4
--- /dev/null
+++ b/empire-db-examples/empire-db-example-spring-boot/src/main/resources/application.yml
@@ -0,0 +1,111 @@
+spring:
+ profiles:
+ active: hsqldb
+ datasource:
+ # Required jar file: hsqldb.jar
+ url: 'jdbc:hsqldb:file:hsqldb/sample;shutdown=true'
+ username: 'sa'
+ password: ''
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.hsql.DBMSHandlerHSql'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: mysql
+ datasource:
+ # Required jar file: mysql-connector-java-5.1.6-bin.jar
+ url: 'jdbc:mysql://localhost'
+ username: 'root'
+ password: 'esteam'
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.mysql.DBMSHandlerMySQL'
+ dbmsHandlerProperties:
+ databaseName: 'DBSAMPLE'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: postgresql
+ datasource:
+ # Required jar file: postgresql-8.3-603.jdbc4.jar
+ url: 'jdbc:postgresql://localhost/dbsample'
+ username: 'dbsample'
+ password: 'DBSAMPLE'
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: h2
+ datasource:
+ # Required jar file: h2-1.1.107.jar
+ url: 'jdbc:h2:h2/DBSAMPLE'
+ username: 'sa'
+ password: ''
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.h2.DBMSHandlerH2'
+ dbmsHandlerProperties:
+ databaseName: 'DBSAMPLE'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: derby
+ datasource:
+ # Required jar file: derby-10.4.2.0.jar
+ url: 'jdbc:derby:derby/DBSAMPLE;create=true'
+ username: ''
+ password: ''
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.derby.DBMSHandlerDerby'
+ dbmsHandlerProperties:
+ databaseName: 'DBSAMPLE'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: oracle
+ datasource:
+ # Required jar file: ojdbc14.jar
+ url: 'jdbc:oracle:thin:@esteams12:1521:ora12'
+ username: 'DBSAMPLE'
+ password: 'DBSAMPLE'
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.oracle.DBMSHandlerOracle'
+
+---
+
+spring:
+ config:
+ activate:
+ on-profile: sqlserver
+ datasource:
+ url: 'jdbc:sqlserver://esteams12\sql17'
+ username: 'jdbcUser'
+ password: 'jdbcPwd'
+
+empiredb:
+ dbmsHandlerClass: 'org.apache.empire.dbms.sqlserver.DBMSHandlerMSSQL'
+ dbmsHandlerProperties:
+ databaseName: 'DBSAMPLE'
+ # useDateTime2: set to false for databases using DATETIME for timestamps instead of DATETIME2
+ useDateTime2: true
+ useSequenceTable: false
\ No newline at end of file
