This is an automated email from the ASF dual-hosted git repository. doebele pushed a commit to branch spring-boot in repository https://gitbox.apache.org/repos/asf/empire-db.git
commit d48a07d1381ddb828827b50ce7c24c577b53b322 Author: Ralf Eichinger <[email protected]> AuthorDate: Mon Aug 9 22:55:06 2021 +0200 Add Spring Boot example --- .../empire-db-example-spring-boot/pom.xml | 93 +++++ .../springboot/EmpireDBConfigProperties.java | 29 ++ .../empire/samples/springboot/SampleApp.java | 453 +++++++++++++++++++++ .../empire/samples/springboot/SampleBean.java | 110 +++++ .../empire/samples/springboot/SampleConfig.java | 8 + .../apache/empire/samples/springboot/SampleDB.java | 158 +++++++ .../src/main/resources/application.yml | 57 +++ 7 files changed, 908 insertions(+) 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..8edea72 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/pom.xml @@ -0,0 +1,93 @@ +<?xml version="1.0" encoding="UTF-8"?> +<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.5.3</version> + <relativePath/> <!-- lookup parent from repository --> + </parent> + + <name>Apache Empire-db Spring Boot Example</name> + <artifactId>empire-db-example-spring-boot</artifactId> + <packaging>jar</packaging> + + <properties> + <!-- The spring-boot-starter-parent chooses fairly conservative Java compatibility. If you want to follow our recommendation and use a later Java version you can add a java.version property --> + <java.version>11</java.version> + <maven.compiler.source>11</maven.compiler.source> + <maven.compiler.target>11</maven.compiler.target> + <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> + </properties> + + <dependencies> + <dependency> + <groupId>org.apache.empire-db</groupId> + <artifactId>empire-db</artifactId> + <version>2.5.1-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> + + <!-- This dependency should be declared as runtime but the exec plugin is broken atm... --> + <dependency> + <groupId>org.hsqldb</groupId> + <artifactId>hsqldb</artifactId> + <scope>runtime</scope> + </dependency> + <!-- <dependency> + <groupId>postgresql</groupId> + <artifactId>postgresql</artifactId> + <version>8.3-603.jdbc3</version> + <scope>runtime</scope> + </dependency> --> + <!-- <dependency> + <groupId>com.h2database</groupId> + <artifactId>h2</artifactId> + <scope>runtime</scope> + </dependency> --> + <!-- <dependency> + <groupId>org.apache.derby</groupId> + <artifactId>derby</artifactId> + <scope>runtime</scope> + </dependency> --> + <!-- <dependency> + <groupId>mysql</groupId> + <artifactId>mysql-connector-java</artifactId> + <version>5.1.6</version> + <scope>runtime</scope> + </dependency>--> + <!-- msssql --> + <!-- <dependency> + <groupId>com.microsoft.sqlserver</groupId> + <artifactId>mssql-jdbc</artifactId> + <version>7.0.0.jre8</version> + <scope>runtime</scope> + </dependency>--> + <!-- ojdbc + <dependency> + <groupId>com.oracle</groupId> + <artifactId>ojdbc6</artifactId> + <version>11.2.0.3.0</version> + <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> \ No newline at end of file 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..c296d1b --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/EmpireDBConfigProperties.java @@ -0,0 +1,29 @@ +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 driverClass; + private Map<String, String> driverProperties; + + public String getDriverClass() { + return driverClass; + } + + public Map<String, String> getDriverProperties() { + return driverProperties; + } + + public void setDriverClass(String driverClass) { + this.driverClass = driverClass; + } + + public void setDriverProperties(Map<String, String> driverProperties) { + this.driverProperties = driverProperties; + } +} 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..a28ba43 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleApp.java @@ -0,0 +1,453 @@ +package org.apache.empire.samples.springboot; + +import java.lang.reflect.InvocationTargetException; +import java.sql.Connection; +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.bean.BeanResult; +import org.apache.empire.db.DBColumnExpr; +import org.apache.empire.db.DBCommand; +import org.apache.empire.db.DBDatabaseDriver; +import org.apache.empire.db.DBReader; +import org.apache.empire.db.DBRecord; +import org.apache.empire.db.DBSQLScript; +import org.apache.empire.db.derby.DBDatabaseDriverDerby; +import org.apache.empire.db.h2.DBDatabaseDriverH2; +import org.apache.empire.db.hsql.DBDatabaseDriverHSql; +import org.apache.empire.db.postgresql.DBDatabaseDriverPostgreSQL; +import org.apache.empire.exceptions.InvalidArgumentException; +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); + + @Autowired + private EmpireDBConfigProperties empireDBConfigProperties; + + @Autowired + private DataSource dataSource; + + @Autowired + private Environment environment; + + @Autowired + private SampleDB db; + + private enum QueryType { + Reader, + BeanList, + XmlDocument + } + + public static void main(String[] args) { + SpringApplication.run(SampleApp.class, args); + } + + @Override + public void run(ApplicationArguments args) throws Exception { + LOGGER.info("STARTING THE APPLICATION"); + + System.out.println("Running DB Sample..."); + + // STEP 1: Get a JDBC Connection + System.out.println("*** Step 1: getJDBCConnection() ***"); + Connection conn = getJDBCConnection(); + + // STEP 2: Choose a driver + System.out.println("*** Step 2: getDatabaseDriver() ***"); + DBDatabaseDriver driver = getDatabaseDriver(conn); + + try { + // STEP 3: Open Database (and create if not existing) + System.out.println("*** Step 3: openDatabase() ***"); + // Open the database + db.open(driver, conn); + // Check whether database exists + databaseExists(conn); + System.out.println("*** Database already exists. Skipping Step4 ***"); + } catch (Exception e) { + // STEP 4: Create Database + System.out.println("*** Step 4: createDDL() ***"); + // postgre does not support DDL in transaction + if (db.getDriver() instanceof DBDatabaseDriverPostgreSQL) { + conn.setAutoCommit(true); + } + createDatabase(driver, conn); + if (db.getDriver() instanceof DBDatabaseDriverPostgreSQL) { + conn.setAutoCommit(false); + } + // Open again + if (db.isOpen() == false) { + db.open(driver, conn); + } + + // STEP 5: Clear Database (Delete all records) + System.out.println("*** Step 5: clearDatabase() ***"); + clearDatabase(conn); + + // STEP 6: Insert Departments + System.out.println("*** Step 6: insertDepartment() & insertEmployee() ***"); + int idDevDep = insertDepartment(conn, "Development", "ITTK"); + int idSalDep = insertDepartment(conn, "Sales", "ITTK"); + // Insert Employees + int idPers1 = insertEmployee(conn, "Peter", "Sharp", Gender.M, idDevDep); + int idPers2 = insertEmployee(conn, "Fred", "Bloggs", Gender.M, idDevDep); + int idPers3 = insertEmployee(conn, "Emma", "White", Gender.F, idSalDep); + + // STEP 7: Update Records (by setting the phone Number) + System.out.println("*** Step 7: updateEmployee() ***"); + updateEmployee(conn, idPers1, "+49-7531-457160"); + updateEmployee(conn, idPers2, "+49-5555-505050"); + updateEmployee(conn, idPers3, "+49-040-125486"); + + // commit + db.commit(conn); + + // STEP 8: Option 1: Query Records and print tab-separated + System.out.println("*** Step 8 Option 1: queryRecords() / Tab-Output ***"); + queryRecords(conn, QueryType.Reader); // Tab-Output + + // STEP 8: Option 2: Query Records as a list of java beans + System.out.println("*** Step 8 Option 2: queryRecords() / Bean-List-Output ***"); + queryRecords(conn, QueryType.BeanList); // Bean-List-Output + + // STEP 8: Option 3: Query Records as XML + System.out.println("*** Step 8 Option 3: queryRecords() / XML-Output ***"); + queryRecords(conn, QueryType.XmlDocument); // XML-Output + + // STEP 9: Use Bean Result to query beans + queryBeans(conn); + + // Done + System.out.println("DB Sample finished successfully."); + } + + } + + /** + * Creates an Empire-db DatabaseDriver for the given provider and applies driver specific configuration + */ + private DBDatabaseDriver getDatabaseDriver(Connection conn) { + try { // Get Driver Class Name + String driverClassName = empireDBConfigProperties.getDriverClass(); + if (StringUtils.isEmpty(driverClassName)) { + throw new RuntimeException("Configuration error: Element 'empiredb.driverClass' not found in properties of profile '" + environment.getActiveProfiles().toString() + "'"); + } + + // Create driver + DBDatabaseDriver driver = (DBDatabaseDriver) Class.forName(driverClassName).getDeclaredConstructor().newInstance(); + + // Configure driver + readProperties(driver); + + // Special cases + if (driver instanceof DBDatabaseDriverPostgreSQL) { + // Create the reverse function that is needed by this sample + ((DBDatabaseDriverPostgreSQL) driver).createReverseFunction(conn); + } + + // done + return driver; + } catch (Exception e) { + // catch any checked exception and forward it + e.printStackTrace(); + throw new RuntimeException(e); + } + } + + /** + * <PRE> + * Empties all Tables. + * </PRE> + */ + private void clearDatabase(Connection conn) { + DBCommand cmd = db.createCommand(); + // Delete all Employees (no constraints) + db.executeDelete(db.EMPLOYEES, cmd, conn); + // Delete all Departments (no constraints) + db.executeDelete(db.DEPARTMENTS, cmd, conn); + } + + /** + * <PRE> + * Creates a DDL Script for entire SampleDB Database and executes it line by line. + * Please make sure you uses the correct DatabaseDriver for your target DBMS. + * </PRE> + */ + private void createDatabase(DBDatabaseDriver driver, Connection conn) { + // create DDL for Database Definition + DBSQLScript script = new DBSQLScript(); + db.getCreateDDLScript(driver, script); + // Show DDL Statement + System.out.println(script.toString()); + // Execute Script + script.executeAll(driver, conn, false); + // Commit + db.commit(conn); + } + + /** + * <PRE> + * Checks whether the database exists or not by executing + * select count(*) from DEPARTMENTS + * If the Departments table does not exist the querySingleInt() function return -1 for failure. + * Please note that in this case an error will appear in the log which can be ignored. + * </PRE> + */ + private boolean databaseExists(Connection conn) { + // Check whether DB exists + DBCommand cmd = db.createCommand(); + cmd.select(db.DEPARTMENTS.count()); + // Check using "select count(*) from DEPARTMENTS" + System.out.println("Checking whether table DEPARTMENTS exists (SQLException will be logged if not - please ignore) ..."); + return (db.querySingleInt(cmd, -1, conn) >= 0); + } + + /** + * <PRE> + * Opens and returns a JDBC-Connection. + * JDBC url, user and password for the connection are obtained from the SampleConfig bean + * Please use the config.xml file to change connection params. + * </PRE> + */ + private 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 is " + conn.getAutoCommit()); + } catch (Exception e) { +// LOGGER.error("Failed to connect directly to '" + config.getJdbcURL() + "' / User=" + config.getJdbcUser()); + LOGGER.error(e.toString()); + throw new RuntimeException(e); + } + return conn; + } + + private void readProperties(Object bean) { + // Check arguments + if (bean == null) { + throw new InvalidArgumentException("bean", bean); + } + + Map<String, String> driverProperties = empireDBConfigProperties.getDriverProperties(); + if (driverProperties != null) { + for (Map.Entry<String, String> entry : driverProperties.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 | InvocationTargetException ex) { + LOGGER.error(null, ex); + } catch (NoSuchMethodException ex) { + LOGGER.error("Property '{}' not found in {}", name, bean.getClass().getName()); + } + } + } + } + + /** + * <PRE> + * Insert a Department into the Departments table. + * </PRE> + */ + private int insertDepartment(Connection conn, String departmentName, String businessUnit) { + // Insert a Department + DBRecord rec = new DBRecord(); + rec.create(db.DEPARTMENTS); + rec.setValue(db.DEPARTMENTS.NAME, departmentName); + rec.setValue(db.DEPARTMENTS.BUSINESS_UNIT, businessUnit); + rec.update(conn); + // Return Department ID + return rec.getInt(db.DEPARTMENTS.DEPARTMENT_ID); + } + + /** + * <PRE> + * Inserts an Employee into the Employees table. + * </PRE> + */ + private int insertEmployee(Connection conn, String firstName, String lastName, Gender gender, int departmentId) { + // Insert an Employee + DBRecord rec = new DBRecord(); + rec.create(db.EMPLOYEES); + rec.setValue(db.EMPLOYEES.FIRSTNAME, firstName); + rec.setValue(db.EMPLOYEES.LASTNAME, lastName); + rec.setValue(db.EMPLOYEES.GENDER, gender); + rec.setValue(db.EMPLOYEES.DEPARTMENT_ID, departmentId); + rec.update(conn); + // Return Employee ID + return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID); + } + + /** + * <PRE> + * Updates an employee record by setting the phone number. + * </PRE> + */ + private void updateEmployee(Connection conn, int idPers, String phoneNumber) { + // Update an Employee + DBRecord rec = new DBRecord(); + rec.read(db.EMPLOYEES, idPers, conn); + // Set + rec.setValue(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); + rec.update(conn); + } + + /** + * <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.DEPARTMENT_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 queryRecords(Connection conn, QueryType queryType) { + // Define the query + DBCommand cmd = db.createCommand(); + // Define shortcuts for tables used - not necessary but convenient + SampleDB.Employees EMP = db.EMPLOYEES; + SampleDB.Departments DEP = db.DEPARTMENTS; + + // The following expression concats lastname + ', ' + firstname + DBColumnExpr EMPLOYEE_FULLNAME = EMP.LASTNAME.append(", ").append(EMP.FIRSTNAME).as("FULL_NAME"); + + // The following expression 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.getDriver() instanceof DBDatabaseDriverHSql + || db.getDriver() instanceof DBDatabaseDriverDerby + || db.getDriver() instanceof DBDatabaseDriverH2) { + 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"); + + // DBColumnExpr genderExpr = cmd.select(EMP.GENDER.decode(EMP.GENDER.getOptions()).as(EMP.GENDER.getName())); + // Select required columns + cmd.select(EMP.EMPLOYEE_ID, EMPLOYEE_FULLNAME); + cmd.select(EMP.GENDER, EMP.PHONE_NUMBER, PHONE_EXT_NUMBER); + cmd.select(DEP.NAME.as("DEPARTMENT")); + cmd.select(DEP.BUSINESS_UNIT); + cmd.join(EMP.DEPARTMENT_ID, DEP.DEPARTMENT_ID); + // Set constraints and order + cmd.where(EMP.LASTNAME.length().isGreaterThan(0)); + cmd.orderBy(EMP.LASTNAME, EMP.FIRSTNAME); + + /* + // Example for limitRows() and skipRows() + if (db.getDriver().isSupported(DBDriverFeature.QUERY_LIMIT_ROWS)) + { // set maximum number of rows + cmd.limitRows(20); + if (db.getDriver().isSupported(DBDriverFeature.QUERY_SKIP_ROWS)) + cmd.skipRows(1); + } + */ + // Query Records and print output + DBReader reader = new DBReader(); + try { + // Open Reader + System.out.println("Running Query:"); + System.out.println(cmd.getSelect()); + reader.open(cmd, conn); + // Print output + System.out.println("---------------------------------"); + switch (queryType) { + case Reader: + // Text-Output by iterating through all records. + while (reader.moveNext()) { + System.out.println(reader.getString(EMP.EMPLOYEE_ID) + + "\t" + reader.getString(EMPLOYEE_FULLNAME) + + "\t" + EMP.GENDER.getOptions().get(reader.getString(EMP.GENDER)) + + "\t" + reader.getString(PHONE_EXT_NUMBER) + + "\t" + reader.getString(DEP.NAME)); + } + break; + case BeanList: + // Text-Output using a list of Java Beans supplied by the DBReader + List<SampleBean> beanList = reader.getBeanList(SampleBean.class); + System.out.println(String.valueOf(beanList.size()) + " SampleBeans returned from Query."); + for (SampleBean 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; + } + + } finally { + // always close Reader + reader.close(); + } + } + + private void queryBeans(Connection conn) { + // Query all males + BeanResult<SampleBean> result = new BeanResult<SampleBean>(SampleBean.class, db.EMPLOYEES); + result.getCommand().where(db.EMPLOYEES.GENDER.is(Gender.M)); + result.fetch(conn); + + System.out.println("Number of male employees is: " + result.size()); + + // And now, the females + result.getCommand().where(db.EMPLOYEES.GENDER.is(Gender.F)); + result.fetch(conn); + + System.out.println("Number of female employees is: " + result.size()); + } +} diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleBean.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleBean.java new file mode 100644 index 0000000..ac2b6cb --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleBean.java @@ -0,0 +1,110 @@ +/* + * 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; + +/** + * The SampleBean class is used to demonstrate JavaBean support for SQL-Queries. The SampleBean is used in the SampleApp's queryRecords function. + */ +public class SampleBean { + + private int employeeId; + private String fullName; + private String gender; + private String phoneNumber; + private String department; + private String businessUnit; + + /* + * Uncomment this if you want to use constructor instead of setters + * However, number of arguments and data types must match query! + * + public SampleBean(int employeeId, String fullName, String gender, String phoneNumber, String department, String businessUnit) + { + this.employeeId = employeeId; + this.fullName = fullName; + this.gender = gender; + this.phoneNumber = phoneNumber; + this.department = department; + this.businessUnit = businessUnit; + } + */ + public int getEmployeeId() { + return employeeId; + } + + public void setEmployeeId(int employeeId) { + this.employeeId = employeeId; + } + + public String getFullName() { + return fullName; + } + + public void setFullName(String fullName) { + this.fullName = fullName; + } + + public String getGender() { + return gender; + } + + public void setGender(String gender) { + this.gender = gender; + } + + public String getPhoneNumber() { + return phoneNumber; + } + + public void setPhoneNumber(String phoneNumber) { + this.phoneNumber = phoneNumber; + } + + public String getDepartment() { + return department; + } + + public void setDepartment(String department) { + this.department = department; + } + + public String getBusinessUnit() { + return businessUnit; + } + + public void setBusinessUnit(String businessUnit) { + this.businessUnit = businessUnit; + } + + @Override + public String toString() { + StringBuffer buf = new StringBuffer(); + buf.append(employeeId); + buf.append("\t"); + buf.append(fullName); + buf.append("\t"); + buf.append(gender); + buf.append("\t"); + buf.append(department); + buf.append("\t"); + buf.append(businessUnit); + return buf.toString(); + } + +} diff --git a/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleConfig.java b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleConfig.java new file mode 100644 index 0000000..f976c94 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleConfig.java @@ -0,0 +1,8 @@ +package org.apache.empire.samples.springboot; + +import org.springframework.context.annotation.Configuration; + +@Configuration +public class SampleConfig { + +} 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..d2329a3 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/java/org/apache/empire/samples/springboot/SampleDB.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.springboot; + +import org.apache.empire.data.DataType; +import org.apache.empire.db.DBColumn; +import org.apache.empire.db.DBDatabase; +import org.apache.empire.db.DBTable; +import org.apache.empire.db.DBTableColumn; +import org.springframework.stereotype.Component; + +/** + * <PRE> + * This file contains the definition of the data model in Java. + * The SampleDB data model consists of two tables and a foreign key relation. + * The tables are defined as nested classes here, but you may put them in separate files if you want to. + * + * PLEASE NOTE THE NAMING CONVENTION: + * Since all tables, views and columns are declared as "final" constants they are all in upper case. + * We recommend using a prefix of T_ for tables and C_ for columns in order to keep them together + * when listed in your IDE's code completion. + * There is no need to stick to this convention but it makes life just another little bit easier. + * + * You may declare other database tables or views in the same way. + * </PRE> + */ +@Component +public class SampleDB extends DBDatabase +{ + private final static long serialVersionUID = 1L; + + 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 definition of the Departments table. + */ + public static class Departments extends DBTable + { + private final static long serialVersionUID = 1L; + + public final DBTableColumn DEPARTMENT_ID; + public final DBTableColumn NAME; + public final DBTableColumn HEAD; + public final DBTableColumn BUSINESS_UNIT; + public final DBTableColumn UPDATE_TIMESTAMP; + + public Departments(DBDatabase db) + { + super("DEPARTMENTS", db); + // ID + DEPARTMENT_ID = addColumn("DEPARTMENT_ID", DataType.AUTOINC, 0, true, "DEP_ID_SEQUENCE"); + 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= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP, 0, true); + + // Primary Key (automatically set due to AUTOINC column) + // setPrimaryKey(DEPARTMENT_ID); + // Set other Indexes + addIndex("DEPARTMENT_NAME_IDX", true, new DBColumn[] { NAME }); + } + } + + /** + * This class represents the definition of the Employees table. + */ + public static class Employees extends DBTable + { + private final static long serialVersionUID = 1L; + + public final DBTableColumn EMPLOYEE_ID; + public final DBTableColumn SALUTATION; + public final DBTableColumn FIRSTNAME; + public final DBTableColumn LASTNAME; + public final DBTableColumn DATE_OF_BIRTH; + public final DBTableColumn DATE_WITH_TIME; + 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(DBDatabase db) + { + super("EMPLOYEES", db); + + // ID + EMPLOYEE_ID = addColumn("EMPLOYEE_ID", DataType.AUTOINC, 0, true, "EMPLOYEE_ID_SEQUENCE"); + SALUTATION = addColumn("SALUTATION", DataType.VARCHAR, 20, false); + FIRSTNAME = addColumn("FIRSTNAME", DataType.VARCHAR, 40, true); + LASTNAME = addColumn("LASTNAME", DataType.VARCHAR, 40, true); + DATE_OF_BIRTH = addColumn("DATE_OF_BIRTH", DataType.DATE, 0, false); + DATE_WITH_TIME = addColumn("DATE_WITH_TIME", DataType.DATETIME, 0, false); + DEPARTMENT_ID = addColumn("DEPARTMENT_ID", DataType.INTEGER, 0, true); + GENDER = addColumn("GENDER", DataType.VARCHAR, 1, false, 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= addColumn("UPDATE_TIMESTAMP", DataType.TIMESTAMP, 0, true); + + // Primary Key (automatically set due to AUTOINC column) + // setPrimaryKey(EMPLOYEE_ID); + // Set other Indexes + addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { FIRSTNAME, LASTNAME, DATE_OF_BIRTH }); + } + } + + // Declare all Tables and Views here + public final Departments DEPARTMENTS = new Departments(this); + public final Employees EMPLOYEES = new Employees(this); + + /** + * Constructor of the SampleDB data model description + * + * Put all foreign key relations here. + */ + public SampleDB() + { + // Define Foreign-Key Relations + addRelation( EMPLOYEES.DEPARTMENT_ID.referenceOn( DEPARTMENTS.DEPARTMENT_ID )); + } + +} 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..ef268f8 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring-boot/src/main/resources/application.yml @@ -0,0 +1,57 @@ +spring: + profiles: + active: hsqldb + datasource: + url: 'jdbc:hsqldb:file:hsqldb/sample;shutdown=true' + username: 'sa' + password: '' + +empiredb: + driverClass: 'org.apache.empire.db.hsql.DBDatabaseDriverHSql' + +--- + +spring: + config: + activate: + on-profile: mysql + +empiredb: + driverClass: 'org.apache.empire.db.mysql.DBDatabaseDriverMySQL' + driverProperties: + databaseName: 'DBSAMPLE' + +--- + +spring: + config: + activate: + on-profile: postgresql + +--- + +spring: + config: + activate: + on-profile: h2 + +--- + +spring: + config: + activate: + on-profile: derby + +--- + +spring: + config: + activate: + on-profile: oracle + +--- + +spring: + config: + activate: + on-profile: sqlserver
