Spring Examples moved to examples project Project: http://git-wip-us.apache.org/repos/asf/empire-db/repo Commit: http://git-wip-us.apache.org/repos/asf/empire-db/commit/cc86a1fd Tree: http://git-wip-us.apache.org/repos/asf/empire-db/tree/cc86a1fd Diff: http://git-wip-us.apache.org/repos/asf/empire-db/diff/cc86a1fd
Branch: refs/heads/master Commit: cc86a1fd7d80ffd5a81910e68a5fd07901a4786d Parents: f951502 Author: inemeth <[email protected]> Authored: Mon Oct 31 09:21:30 2016 +0100 Committer: inemeth <[email protected]> Committed: Mon Oct 31 09:21:30 2016 +0100 ---------------------------------------------------------------------- .../empire-db-example-spring/pom.xml | 7 +- .../empire/spring/example1/EmpireApp.java | 47 +++ .../empire/spring/example1/EmpireAppImpl.java | 336 +++++++++++++++++++ .../empire/spring/example1/SampleBean.java | 126 +++++++ .../apache/empire/spring/example1/SampleDB.java | 148 ++++++++ .../empire/spring/example1/SampleSpringApp.java | 86 +++++ .../empire/spring/example2/Department.java | 99 ++++++ .../apache/empire/spring/example2/Employee.java | 140 ++++++++ .../empire/spring/example2/EmployeeDao.java | 47 +++ .../empire/spring/example2/EmployeeDaoImpl.java | 231 +++++++++++++ .../spring/example2/EmployeeSpringApp.java | 227 +++++++++++++ .../resources/example1/applicationContext.xml | 92 +++++ .../example2/applicationContext-employee.xml | 85 +++++ .../src/main/resources/settings.properties | 2 +- empire-db-spring/pom.xml | 10 - .../empire/spring/example1/EmpireApp.java | 47 --- .../empire/spring/example1/EmpireAppImpl.java | 336 ------------------- .../empire/spring/example1/SampleBean.java | 126 ------- .../apache/empire/spring/example1/SampleDB.java | 148 -------- .../empire/spring/example1/SampleSpringApp.java | 86 ----- .../empire/spring/example2/Department.java | 99 ------ .../apache/empire/spring/example2/Employee.java | 140 -------- .../empire/spring/example2/EmployeeDao.java | 47 --- .../empire/spring/example2/EmployeeDaoImpl.java | 231 ------------- .../spring/example2/EmployeeSpringApp.java | 227 ------------- .../resources/example1/applicationContext.xml | 92 ----- .../example2/applicationContext-employee.xml | 85 ----- .../src/main/resources/log4j.properties | 21 -- .../src/main/resources/settings.properties | 44 --- 29 files changed, 1671 insertions(+), 1741 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/pom.xml ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/pom.xml b/empire-db-examples/empire-db-example-spring/pom.xml index 0eba0e9..101cb25 100644 --- a/empire-db-examples/empire-db-example-spring/pom.xml +++ b/empire-db-examples/empire-db-example-spring/pom.xml @@ -107,7 +107,12 @@ <version>1.0</version> </dependency> --> - + + <dependency> + <groupId>org.apache.empire-db</groupId> + <artifactId>empire-db-spring</artifactId> + <version>2.4.5-SNAPSHOT</version> + </dependency> </dependencies> <!-- better would be to add this repository in your settings.xml --> http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java new file mode 100644 index 0000000..ddf809f --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.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.spring.example1; + + +/** + * This is the interface to the application. It is usually some high level interface; for this basic + * example this is the dao object. + * + */ +public interface EmpireApp { + + void clearDatabase(); + + void setupDatabase(); + + Integer insertDepartment(String departmentName, String businessUnit); + + Integer insertEmployee(String firstName, String lastName, String gender, int departmentId); + + void updateEmployee(int idPers, String phoneNumber); + + void doQuery(QueryType type); + + public enum QueryType + { + Reader, + BeanList, + XmlDocument + } +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java new file mode 100644 index 0000000..ba5bd45 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java @@ -0,0 +1,336 @@ +/* + * 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.spring.example1; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.empire.db.DBColumn; +import org.apache.empire.db.DBColumnExpr; +import org.apache.empire.db.DBCommand; +import org.apache.empire.db.DBDatabase; +import org.apache.empire.db.DBDatabaseDriver; +import org.apache.empire.db.DBReader; +import org.apache.empire.db.DBRecord; +import org.apache.empire.db.DBRecordData; +import org.apache.empire.db.DBSQLScript; +import org.apache.empire.db.DBTable; +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.spring.DBReaderExtractor; +import org.apache.empire.spring.DBRecordCallbackHandler; +import org.apache.empire.spring.DBRecordMapper; +import org.apache.empire.spring.EmpireDaoSupport; +import org.apache.empire.xml.XMLWriter; +import org.springframework.dao.DataAccessException; +import org.springframework.jdbc.core.ConnectionCallback; +import org.springframework.transaction.annotation.Transactional; +import org.w3c.dom.Document; + +/** + * + */ +public class EmpireAppImpl extends EmpireDaoSupport implements EmpireApp { + + @Transactional + public void clearDatabase() { + SampleDB db = getDatabase(); + + DBCommand cmd = db.createCommand(); + // Delete all Employees (no constraints) + getEmpireTemplate().executeDelete(db.EMPLOYEES, cmd); + // Delete all Departments (no constraints) + getEmpireTemplate().executeDelete(db.DEPARTMENTS, cmd); + } + + + @Transactional + public Integer insertDepartment(String departmentName, String businessUnit) { + SampleDB db = getDatabase(); + + DBRecord rec = new DBRecord(); + rec.create(db.DEPARTMENTS); + rec.setValue(db.DEPARTMENTS.NAME, departmentName); + rec.setValue(db.DEPARTMENTS.BUSINESS_UNIT, businessUnit); + + getEmpireTemplate().updateRecord(rec); + // Return Department ID + return rec.getInt(db.DEPARTMENTS.DEPARTMENT_ID); + } + + @Transactional + public Integer insertEmployee(String firstName, String lastName, + String gender, int departmentId) { + SampleDB db = getDatabase(); + + 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); + getEmpireTemplate().updateRecord(rec); + // Return Employee ID + return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID); + } + + @Transactional + public void updateEmployee(int idPers, String phoneNumber) { + if (getEmployee(idPers) == null) { + // if you like more verbose exceptions for the app + throw new IllegalArgumentException( + "The specified employee does not exist."); + } + + SampleDB db = getDatabase(); + + DBRecord rec = getEmpireTemplate().openRecord(db.EMPLOYEES, idPers); + // Set + rec.setValue(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); + getEmpireTemplate().updateRecord(rec); + } + + @Transactional(readOnly = true) + public void doQuery(QueryType type) { + SampleDB db = getDatabase(); + + // Define the query + DBCommand cmd = db.createCommand(); + // Define shortcuts for tables used - not necessary but convenient + final SampleDB.Employees EMP = db.EMPLOYEES; + final SampleDB.Departments DEP = db.DEPARTMENTS; + + // The following expression concats lastname + ', ' + firstname + final 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 + final 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); + final 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); + if (db.getDriver() instanceof DBDatabaseDriverPostgreSQL) { + // postgres does not support the substring expression + cmd.select(EMP.GENDER, EMP.PHONE_NUMBER); + } else { + 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); + + // Query Records and print output + try { + // Open Reader + System.out.println("Running Query:"); + System.out.println(cmd.getSelect()); + // Print output + System.out.println("---------------------------------"); + switch (type) { + case BeanList: + // Text-Output by iterating through all records. + DBRecordCallbackHandler readerImpl = new DBRecordCallbackHandler() { + + public void processRecord(DBRecordData reader) { + 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)); + + } + }; + getEmpireTemplate().query(cmd, readerImpl); + break; + case Reader: + // Text-Output using a list of Java Beans supplied by the + // DBReader + DBReaderExtractor<List<SampleBean>> beanListImpl = new DBReaderExtractor<List<SampleBean>>() { + + public List<SampleBean> process(DBReader reader) { + return reader.getBeanList(SampleBean.class); + } + }; + List<SampleBean> beanList = getEmpireTemplate().query(cmd, + beanListImpl); + 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 + XmlDocumentExtractor xmlImpl = new XmlDocumentExtractor(); + Document doc = getEmpireTemplate().query(cmd, xmlImpl); + // Print XML Document to System.out + XMLWriter.debug(doc); + break; + } + + } finally { + + // always close Reader + + // reader is closed in EmpireTemplate's methods + + // reader.close(); + } + } + + public Map<Object, Object> getDepartment(int id) { + SampleDB db = getDatabase(); + return get(db.DEPARTMENTS, id); + } + + public Map<Object, Object> getEmployee(int id) { + SampleDB db = getDatabase(); + return get(db.EMPLOYEES, id); + } + + private Map<Object, Object> get(DBTable table, int pk) { + + SampleDB db = getDatabase(); + + DBCommand cmd = db.createCommand(); + cmd.select(table.getColumns()); + cmd.where(table.getPrimaryKey().getColumns()[0].is(pk)); // i know there + // is just + // one + // pk-column + // ;-) + Map<Object, Object> dep = getEmpireTemplate().queryForObject(cmd, + new RowToObjectMapDataMapper(table)); + + return dep; + } + + public static class XmlDocumentExtractor implements + DBReaderExtractor<Document> { + + public Document process(DBReader reader) { + return reader.getXmlDocument(); + } + + } + + public static class RowToObjectMapDataMapper implements + DBRecordMapper<Map<Object, Object>> { + + DBTable table; + + public RowToObjectMapDataMapper(DBTable table) { + super(); + this.table = table; + } + + public Map<Object, Object> mapRecord(DBRecordData record, int rowNum) { + Map<Object, Object> dep = new HashMap<Object, Object>(); + for (DBColumn col : table.getColumns()) { + dep.put(col.getName(), record.getValue(col)); + } + return dep; + } + + } + + public boolean databaseExists() { + Connection conn = getConnection(); + try { + DBDatabase db = getDatabase(); + if (db.getTables() == null || db.getTables().isEmpty()) { + throw new AssertionError( + "There are no tables in this database!"); + } + DBCommand cmd = db.createCommand(); + if (cmd == null) { + throw new AssertionError("The DBCommand object is null."); + } + DBTable t = db.getTables().get(0); + cmd.select(t.count()); + return (db.querySingleInt(cmd, -1, conn) >= 0); + } catch (Exception e) { + return false; + } + } + + public void initializeDatabase() { + if (!databaseExists()) { + createDatabase(); + } + } + + @Transactional + public void setupDatabase() { + initializeDatabase(); + } + + public void createDatabase() { + + // create DLL for Database Definition + final DBSQLScript script = new DBSQLScript(); + final DBDatabaseDriver driver = getDatabase().getDriver(); + getDatabase().getCreateDDLScript(driver, script); + + // Show DLL Statement + System.out.println(script.toString()); + // Execute Script + getEmpireTemplate().execute(new ConnectionCallback<Object>() { + + public Object doInConnection(Connection con) throws SQLException, + DataAccessException { + script.executeAll(driver, con, false); + return null; + } + }); + + } + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java new file mode 100644 index 0000000..efe89b1 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java @@ -0,0 +1,126 @@ +/* + * 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.spring.example1; + + +/** + * 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(); + } + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleDB.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleDB.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleDB.java new file mode 100644 index 0000000..c8f3284 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleDB.java @@ -0,0 +1,148 @@ +/* + * 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.spring.example1; + +import org.apache.empire.commons.Options; +import org.apache.empire.data.DataType; +import org.apache.empire.data.DataMode; +import org.apache.empire.db.DBColumn; +import org.apache.empire.db.DBDatabase; +import org.apache.empire.db.DBTable; +import org.apache.empire.db.DBTableColumn; + +/** + * <PRE> + * This file contains the definition of the data model in Java. + * The SampleDB data model consists of two tables and a foreign key relation. + * The tables are defined as nested classes here, but you may put the in separate files if you want. + * + * PLEASE NOTE THE NAMING CONVENTION: + * Since all tables, views and columns are declared as "final" constants they are all in upper case. + * We recommend using a prefix of T_ for tables and C_ for columns in order to keep them together + * when listed in your IDE's code completion. + * There is no need to stick to this convention but it makes life just another little bit easier. + * + * You may declare other database tables or views in the same way. + * </PRE> + */ +public class SampleDB extends DBDatabase +{ + private final static long serialVersionUID = 1L; + + /** + * 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.TEXT, 80, true); + HEAD = addColumn("HEAD", DataType.TEXT, 80, false); + BUSINESS_UNIT = addColumn("BUSINESS_UNIT", DataType.TEXT, 4, true, "ITTK"); + UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.DATETIME, 0, true); + + // Primary Key + setPrimaryKey(DEPARTMENT_ID); + // Set other Indexes + addIndex("DEARTMENT_NAME_IDX", true, new DBColumn[] { NAME }); + // Set timestamp column for save updates + setTimestampColumn(UPDATE_TIMESTAMP); + } + } + + /** + * 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 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.TEXT, 20, false); + FIRSTNAME = addColumn("FIRST_NAME", DataType.TEXT, 40, true); + LASTNAME = addColumn("LAST_NAME", DataType.TEXT, 40, true); + DATE_OF_BIRTH = addColumn("DATE_OF_BIRTH", DataType.DATE, 0, false); + DEPARTMENT_ID = addColumn("DEPARTMENT_ID", DataType.INTEGER, 0, true); + GENDER = addColumn("GENDER", DataType.TEXT, 1, false); + PHONE_NUMBER = addColumn("PHONE_NUMBER", DataType.TEXT, 40, false); + EMAIL = addColumn("EMAIL", DataType.TEXT, 80, false); + SALARY = addColumn("SALARY", DataType.DECIMAL, 10.2, false); + RETIRED = addColumn("RETIRED", DataType.BOOL, 0, true, false); + UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.DATETIME, 0, true); + + // Primary Key + setPrimaryKey(EMPLOYEE_ID); + // Set other Indexes + addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { FIRSTNAME, LASTNAME, DATE_OF_BIRTH }); + // Set timestamp column for save updates + setTimestampColumn(UPDATE_TIMESTAMP); + + // Create Options for GENDER column + Options genders = new Options(); + genders.set("M", "Male"); + genders.set("F", "Female"); + GENDER.setOptions(genders); + } + } + + // 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 )); + } + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleSpringApp.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleSpringApp.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleSpringApp.java new file mode 100644 index 0000000..3ff0230 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example1/SampleSpringApp.java @@ -0,0 +1,86 @@ +/* + * 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.spring.example1; + +import java.util.logging.Logger; + +import org.springframework.beans.factory.xml.XmlBeanDefinitionReader; +import org.springframework.context.ApplicationContext; +import org.springframework.context.support.GenericApplicationContext; +import org.springframework.core.io.ClassPathResource; + +/** + * + */ +public class SampleSpringApp { + private static final Logger log = Logger.getLogger(SampleSpringApp.class.getName()); + + //creates the application context + //this is usually in some bootstrapping code; so your application will + //just have one at runtime. + static ApplicationContext ctx = getContext(); + + //get the service that is the entry point into the application + //normally this is injected by spring into classes that need it + static EmpireApp appBean = ctx.getBean("empireApp", EmpireApp.class); + + public static void main(String[] args) throws Exception { + + System.out.println("Running Spring Example..."); + + appBean.setupDatabase(); + appBean.clearDatabase(); + + System.out.println("*** Step 6: insertDepartment() & insertEmployee() ***"); + int idDevDep = appBean.insertDepartment("Development", "ITTK"); + int idSalDep = appBean.insertDepartment("Sales", "ITTK"); + + int idPers1 = appBean.insertEmployee("Peter", "Sharp", "M", idDevDep); + int idPers2 = appBean.insertEmployee("Fred", "Bloggs", "M", idDevDep); + int idPers3 = appBean.insertEmployee("Emma", "White", "F", idSalDep); + + System.out.println("*** Step 7: updateEmployee() ***"); + appBean.updateEmployee(idPers1, "+49-7531-457160"); + appBean.updateEmployee(idPers2, "+49-5555-505050"); + appBean.updateEmployee(idPers3, "+49-040-125486"); + + System.out.println("*** Step 8 Option 1: queryRecords() / Tab-Output ***"); + appBean.doQuery(EmpireApp.QueryType.Reader); + + System.out.println("*** Step 8 Option 2: queryRecords() / Bean-List-Output ***"); + appBean.doQuery(EmpireApp.QueryType.BeanList); + + System.out.println("*** Step 8 Option 3: queryRecords() / XML-Output ***"); + appBean.doQuery(EmpireApp.QueryType.XmlDocument); + + + } + + + + static GenericApplicationContext getContext() { + log.info("Creating Spring Application Context ..."); + GenericApplicationContext ctx = new GenericApplicationContext(); + XmlBeanDefinitionReader reader = new XmlBeanDefinitionReader(ctx); + reader.loadBeanDefinitions(new ClassPathResource("/example1/applicationContext.xml")); + + ctx.refresh(); + return ctx; + } +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Department.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Department.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Department.java new file mode 100644 index 0000000..08d8943 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Department.java @@ -0,0 +1,99 @@ +/* + * 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.spring.example2; + +public class Department { + + private Integer departmentId; + private String name; + private String businessUnit; + private String head; + + public Integer getDepartmentId() { + return departmentId; + } + + public void setDepartmentId(Integer departmentId) { + this.departmentId = departmentId; + } + + public String getName() { + return name; + } + + public void setName(String name) { + this.name = name; + } + + public String getBusinessUnit() { + return businessUnit; + } + + public void setBusinessUnit(String businessUnit) { + this.businessUnit = businessUnit; + } + + public String getHead() { + return head; + } + + public void setHead(String head) { + this.head = head; + } + + @Override + public String toString() { + StringBuffer buf = new StringBuffer(); + buf.append(departmentId); + buf.append("\t"); + buf.append(name); + buf.append(" "); + buf.append(businessUnit); + buf.append("\t"); + buf.append(head); + return buf.toString(); + } + + @Override + public boolean equals(Object obj) { + if (this == obj) { + return true; + } + if (obj == null) { + return false; + } + + if (obj.getClass().equals(this.getClass())) { + Department other = (Department) obj; + if (other.departmentId == null || this.departmentId == null) { + return false; + } + return this.departmentId.equals(other.departmentId); + } + + return super.equals(obj); + } + + @Override + public int hashCode() { + return 1; + } + + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Employee.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Employee.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Employee.java new file mode 100644 index 0000000..5a07ce9 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/Employee.java @@ -0,0 +1,140 @@ +/* + * 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.spring.example2; + +public class Employee { + + public static enum Gender { + + M("Male"), F("Female"); + + private String label; + + private Gender(String label) { + this.label = label; + } + + @Override + public String toString() { + return this.label; + } + } + + private Integer employeeId; + private String firstName; + private String lastName; + private Gender gender; + private String phoneNumber; + + private Department department; + + public Integer getEmployeeId() { + return employeeId; + } + + public void setEmployeeId(Integer employeeId) { + this.employeeId = employeeId; + } + + 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 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 Department getDepartment() { + return department; + } + + public void setDepartment(Department department) { + this.department = department; + } + + @Override + public String toString() { + StringBuffer buf = new StringBuffer(); + buf.append(employeeId); + buf.append("\t"); + buf.append(firstName); + buf.append(" "); + buf.append(lastName); + buf.append("\t"); + buf.append(gender); + + if (department != null){ + buf.append("\t"); + buf.append(department.getName()); + buf.append("\t"); + buf.append(department.getBusinessUnit()); + } + return buf.toString(); + } + + @Override + public boolean equals(Object obj) { + if (this == obj) { + return true; + } + if (obj == null) { + return false; + } + + if (obj.getClass().equals(this.getClass())) { + Employee other = (Employee) obj; + if (other.employeeId == null || this.employeeId == null) { + return false; + } + return this.employeeId.equals(other.employeeId); + } + + return super.equals(obj); + } + + @Override + public int hashCode() { + return 1; + } + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDao.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDao.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDao.java new file mode 100644 index 0000000..b2dd39f --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDao.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.spring.example2; + +import java.util.List; + +public interface EmployeeDao { + + public List<Employee> getEmployees(); + + public List<Department> getDepartments(); + + public Integer createEmployee(Employee employee); + + public void updateEmployee(Employee employee); + + public Employee openEmployee(Integer id); + + public Employee findEmployee(String firstName, String lastName); + + public Department openDepartment(Integer id); + + public Department findDepartment(String name); + + public Integer createDepartment(Department department); + + public void updateDepartment(Department department); + + public void renameDepartment(Integer id, String name); + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDaoImpl.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDaoImpl.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDaoImpl.java new file mode 100644 index 0000000..37eb07b --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeDaoImpl.java @@ -0,0 +1,231 @@ +/* + * 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.spring.example2; + +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import org.apache.empire.db.DBCommand; +import org.apache.empire.db.DBJoinType; +import org.apache.empire.db.DBRecord; +import org.apache.empire.db.DBRecordData; +import org.apache.empire.spring.DBRecordMapper; +import org.apache.empire.spring.DBRecordWriter; +import org.apache.empire.spring.EmpireDaoSupport; +import org.apache.empire.spring.example1.SampleDB; +import org.apache.empire.spring.example1.SampleDB.Departments; +import org.apache.empire.spring.example1.SampleDB.Employees; +import org.springframework.transaction.annotation.Transactional; + +public class EmployeeDaoImpl extends EmpireDaoSupport implements EmployeeDao { + + private Employees EMPLOYEES; + private Departments DEPARTMENTS; + + @Override + protected void initEmpireDao() { + SampleDB db = getDatabase(); + this.EMPLOYEES = db.EMPLOYEES; + this.DEPARTMENTS = db.DEPARTMENTS; + } + + private DBCommand createEmployeeSelectCommand() { + DBCommand cmd = getDatabase().createCommand(); + cmd.select(EMPLOYEES.getColumns()); + cmd.select(DEPARTMENTS.getColumns()); + + cmd.join(EMPLOYEES.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_ID, DBJoinType.INNER); + return cmd; + } + + private DBCommand createDepartmentSelectCommand() { + DBCommand cmd = getDatabase().createCommand(); + cmd.select(DEPARTMENTS.getColumns()); + return cmd; + } + + @Transactional(readOnly = true) + public Employee openEmployee(Integer id) { + DBCommand cmd = createEmployeeSelectCommand(); + cmd.where(EMPLOYEES.EMPLOYEE_ID.is(id)); + return getEmpireTemplate().queryForObject(cmd, new EmployeeMapper()); + } + + @Transactional(readOnly = true) + public Employee findEmployee(String firstName, String lastName) { + DBCommand cmd = createEmployeeSelectCommand(); + cmd.where(EMPLOYEES.FIRSTNAME.is(firstName)); + cmd.where(EMPLOYEES.LASTNAME.is(lastName)); + return getEmpireTemplate().queryForObject(cmd, new EmployeeMapper()); + } + + @Transactional(readOnly = true) + public Department openDepartment(Integer id) { + DBCommand cmd = createDepartmentSelectCommand(); + cmd.where(DEPARTMENTS.DEPARTMENT_ID.is(id)); + return getEmpireTemplate().queryForBean(cmd, Department.class); + } + + @Transactional(readOnly = true) + public Department findDepartment(String name) { + DBCommand cmd = createDepartmentSelectCommand(); + cmd.where(DEPARTMENTS.NAME.is(name)); + return getEmpireTemplate().queryForBean(cmd, Department.class); + } + + @Transactional(readOnly = true) + public List<Department> getDepartments() { + DBCommand cmd = createDepartmentSelectCommand(); + return getEmpireTemplate().queryForBeanList(cmd, Department.class); + } + + + @Transactional + public void renameDepartment(Integer id, String name) { + DBCommand cmd = getDatabase().createCommand(); + cmd.where(DEPARTMENTS.DEPARTMENT_ID.is(id)); + cmd.set(DEPARTMENTS.NAME.to(name)); + getEmpireTemplate().executeUpdate(cmd); + } + + @Transactional(readOnly = true) + public List<Employee> getEmployees() { + DBCommand cmd = createEmployeeSelectCommand(); + return getEmpireTemplate().query(cmd, new EmployeeMapper()); + } + + @Transactional + public Integer createEmployee(Employee employee) { + DBRecord record = new DBRecord(); + record.create(EMPLOYEES); + new EmployeeWriter().write(record, employee); + getEmpireTemplate().updateRecord(record); + return record.getInt(EMPLOYEES.EMPLOYEE_ID); + } + + @Transactional + public void updateEmployee(Employee employee) { + DBRecord record = getEmpireTemplate().openRecord(EMPLOYEES, employee.getEmployeeId()); + new EmployeeWriter().write(record, employee); + getEmpireTemplate().updateRecord(record); + } + + @Transactional + public Integer createDepartment(Department department) { + DBRecord record = new DBRecord(); + record.create(DEPARTMENTS); + new DepartmentWriter().write(record, department); + getEmpireTemplate().updateRecord(record); + return record.getInt(DEPARTMENTS.DEPARTMENT_ID); + } + + @Transactional + public void updateDepartment(Department department) { + DBRecord record = getEmpireTemplate().openRecord(DEPARTMENTS, department.getDepartmentId()); + new DepartmentWriter().write(record, department); + getEmpireTemplate().updateRecord(record); + } + + private class EmployeeMapper implements DBRecordMapper<Employee> { + + DepartmentMapper departmentMapper = new DepartmentMapper(); + + @Override + public Employee mapRecord(DBRecordData record, int rowNum) { + Employee result = new Employee(); + // Auto-copy all properties + //record.setBeanProperties(result); + + result.setEmployeeId(record.getInt(EMPLOYEES.EMPLOYEE_ID)); + result.setFirstName(record.getString(EMPLOYEES.FIRSTNAME)); + result.setLastName(record.getString(EMPLOYEES.LASTNAME)); + result.setGender(Employee.Gender.valueOf(record.getString(EMPLOYEES.GENDER))); + result.setPhoneNumber(record.getString(EMPLOYEES.PHONE_NUMBER)); + + result.setDepartment(departmentMapper.mapRecord(record, rowNum)); + return result; + } + + } + + private class EmployeeWriter implements DBRecordWriter<Employee> { + + @Override + public void write(DBRecord record, Employee entity) { + // Auto-copy all properties + //record.setRecordValues(entity); + + record.setValue(EMPLOYEES.EMPLOYEE_ID, entity.getEmployeeId()); + record.setValue(EMPLOYEES.FIRSTNAME, entity.getFirstName()); + record.setValue(EMPLOYEES.LASTNAME, entity.getLastName()); + record.setValue(EMPLOYEES.GENDER, entity.getGender().name()); + record.setValue(EMPLOYEES.PHONE_NUMBER, entity.getPhoneNumber()); + + record.setValue(EMPLOYEES.DEPARTMENT_ID, entity.getDepartment().getDepartmentId()); + } + + } + + private class DepartmentMapper implements DBRecordMapper<Department> { + + // reader cache, in case of joined resultset the same object is returned + + Map<Integer, Department> cache = new HashMap<Integer, Department>(); + + @Override + public Department mapRecord(DBRecordData record, int rowNum) { + + Integer id = record.getInt(DEPARTMENTS.DEPARTMENT_ID); + + Department department = cache.get(id); + if (department == null) { + department = new Department(); + // Auto-copy all properties + //record.setBeanProperties(department); + + department.setDepartmentId(id); + department.setName(record.getString(DEPARTMENTS.NAME)); + department.setHead(record.getString(DEPARTMENTS.HEAD)); + department.setBusinessUnit(record.getString(DEPARTMENTS.BUSINESS_UNIT)); + + cache.put(id, department); + } + return department; + } + + } + + private class DepartmentWriter implements DBRecordWriter<Department> { + + @Override + public void write(DBRecord record, Department entity) { + // Auto-copy all properties + //record.setRecordValues(entity); + + record.setValue(DEPARTMENTS.DEPARTMENT_ID, entity.getDepartmentId()); + record.setValue(DEPARTMENTS.NAME, entity.getName()); + record.setValue(DEPARTMENTS.HEAD, entity.getHead()); + record.setValue(DEPARTMENTS.BUSINESS_UNIT, entity.getBusinessUnit()); + + } + + } + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeSpringApp.java ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeSpringApp.java b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeSpringApp.java new file mode 100644 index 0000000..3b641a7 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/java/org/apache/empire/spring/example2/EmployeeSpringApp.java @@ -0,0 +1,227 @@ +/* + * 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.spring.example2; + +import java.sql.Connection; +import java.sql.SQLException; +import java.util.Iterator; +import java.util.List; +import java.util.logging.Logger; + +import org.apache.empire.db.DBColumnExpr; +import org.apache.empire.db.DBCommand; +import org.apache.empire.db.DBDatabase; +import org.apache.empire.db.DBDatabaseDriver; +import org.apache.empire.db.DBSQLScript; +import org.apache.empire.db.DBTable; +import org.apache.empire.spring.EmpireTemplate; +import org.apache.empire.spring.example1.SampleDB; +import org.apache.empire.spring.example2.Employee.Gender; +import org.springframework.beans.factory.xml.XmlBeanDefinitionReader; +import org.springframework.context.ApplicationContext; +import org.springframework.context.support.GenericApplicationContext; +import org.springframework.core.io.ClassPathResource; +import org.springframework.dao.DataAccessException; +import org.springframework.jdbc.core.ConnectionCallback; + +/** + * + */ +public class EmployeeSpringApp { + private static final Logger log = Logger.getLogger(EmployeeSpringApp.class.getName()); + + //creates the application context + //this is usually in some bootstrapping code; so your application will + //just have one at runtime. + static ApplicationContext ctx = getContext(); + + //get the service that is the entry point into the application + //normally this is injected by spring into classes that need it + static EmployeeDao employeeDao = ctx.getBean("employeeDao", EmployeeDao.class); + static SampleDB sampleDb = ctx.getBean("sampleDb", SampleDB.class); + + public static void main(String[] args) throws Exception { + + System.out.println("Running Spring Example..."); + + setupDatabase(); + clearDatabase(); + + Department depDevelopment, depSales; + + System.out.println("*** Create Departments ***"); + + { + depDevelopment = new Department(); + depDevelopment.setName("Development"); + depDevelopment.setBusinessUnit("ITTK"); + Integer id = employeeDao.createDepartment(depDevelopment); + depDevelopment = employeeDao.openDepartment(id); + } + { + depSales = new Department(); + depSales.setName("Sales"); + depSales.setBusinessUnit("ITTK"); + Integer id = employeeDao.createDepartment(depSales); + depSales = employeeDao.openDepartment(id); + } + + System.out.println("*** Create Employees ***"); + + Employee peter = new Employee(); + peter.setFirstName("Peter"); + peter.setLastName("Sharp"); + peter.setGender(Gender.M); + peter.setDepartment(depDevelopment); + + Integer peterId = employeeDao.createEmployee(peter); + peter = employeeDao.openEmployee(peterId); + + + + Employee fred = new Employee(); + fred.setFirstName("Fred"); + fred.setLastName("Bloggs"); + fred.setGender(Gender.M); + fred.setDepartment(depDevelopment); + + Integer fredId = employeeDao.createEmployee(fred); + fred = employeeDao.openEmployee(fredId); + + + Employee emma = new Employee(); + emma.setFirstName("Emma"); + emma.setLastName("White"); + emma.setGender(Gender.F); + emma.setDepartment(depSales); + + Integer emmaId = employeeDao.createEmployee(emma); + emma = employeeDao.openEmployee(emmaId); + + + System.out.println("*** updateEmployees ***"); + + peter.setPhoneNumber("+49-7531-457160"); + employeeDao.updateEmployee(peter); + + fred.setPhoneNumber("+49-5555-505050"); + employeeDao.updateEmployee(fred); + + emma.setPhoneNumber("+49-040-125486"); + employeeDao.updateEmployee(emma); + + System.out.println("*** List employees ***"); + + List<Employee> employees = employeeDao.getEmployees(); + for (Iterator<Employee> iterator = employees.iterator(); iterator.hasNext();) { + Employee employee = iterator.next(); + System.out.println(employee); + } + + System.out.println("*** List departments ***"); + + List<Department> departments = employeeDao.getDepartments(); + for (Iterator<Department> iterator = departments.iterator(); iterator.hasNext();) { + Department department = iterator.next(); + System.out.println(department); + } + + } + + private static void clearDatabase() { + // Delete all Employees (no constraints) + + EmpireTemplate empireTemplate = ctx.getBean("empireTemplate", EmpireTemplate.class); + empireTemplate.executeDelete(sampleDb.EMPLOYEES, sampleDb.createCommand()); + empireTemplate.executeDelete(sampleDb.DEPARTMENTS, sampleDb.createCommand()); + + } + + + + + public static void setupDatabase() { + if (!databaseExists()) { + createDatabase(); + } + } + + + public static boolean databaseExists() { + try { + DBDatabase db = sampleDb; + if (db.getTables() == null || db.getTables().isEmpty()) { + throw new AssertionError( + "There are no tables in this database!"); + } + DBCommand cmd = db.createCommand(); + if (cmd == null) { + throw new AssertionError("The DBCommand object is null."); + } + DBTable t = db.getTables().get(0); + DBColumnExpr COUNT = t.count(); + + cmd.select(COUNT); + + EmpireTemplate empireTemplate = ctx.getBean("empireTemplate", EmpireTemplate.class); + return (empireTemplate.queryForInteger(cmd, COUNT, -1) >= 0); + } catch (Exception e) { + return false; + } + } + + + private static void createDatabase() { + + // create DLL for Database Definition + final DBSQLScript script = new DBSQLScript(); + final DBDatabaseDriver driver = sampleDb.getDriver(); + sampleDb.getCreateDDLScript(driver, script); + + // Show DLL Statement + System.out.println(script.toString()); + // Execute Script + EmpireTemplate empireTemplate = ctx.getBean("empireTemplate", EmpireTemplate.class); + empireTemplate.execute(new ConnectionCallback<Object>() { + + @Override + public Object doInConnection(Connection con) throws SQLException, + DataAccessException { + script.executeAll(driver, con, false); + return null; + } + }); + + } + + + + static GenericApplicationContext getContext() { + log.info("Creating Spring Application Context ..."); + GenericApplicationContext ctx = new GenericApplicationContext(); + XmlBeanDefinitionReader reader = new XmlBeanDefinitionReader(ctx); + reader.loadBeanDefinitions(new ClassPathResource("/example2/applicationContext-employee.xml")); + + ctx.refresh(); + return ctx; + } + + + +} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/resources/example1/applicationContext.xml ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/resources/example1/applicationContext.xml b/empire-db-examples/empire-db-example-spring/src/main/resources/example1/applicationContext.xml new file mode 100644 index 0000000..7987d5a --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/resources/example1/applicationContext.xml @@ -0,0 +1,92 @@ +<?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. +--> +<beans xmlns="http://www.springframework.org/schema/beans"; + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xmlns:tx="http://www.springframework.org/schema/tx"; + xmlns:aop="http://www.springframework.org/schema/aop"; + xsi:schemaLocation=" + http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd + http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd + http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd";> + + + <!-- one option is to use a class from spring that will read the properties file + and replaces the ${...} placeholders with the appropriate values --> + <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> + <property name="locations"> + <value>classpath:/settings.properties</value> + </property> + </bean> + + <!-- Data Source / DB Settings --> + <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> + <property name="driverClassName" value="${jdbc.driverClass}"/> + <property name="url" value="${jdbc.url}"/> + <property name="username" value="${jdbc.username}"/> + <property name="password" value="${jdbc.password}"/> + </bean> + + <!-- Transaction manager for a single JDBC DataSource (alternative to JTA) --> + <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> + <constructor-arg ref="dataSource"/> + </bean> + <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> + <property name="transactionManager" ref="transactionManager"/> + </bean> + + <!-- @Transactional --> + <tx:annotation-driven transaction-manager="transactionManager"/> + + <!-- Empire-DB classes --> + + <!-- application dao/services --> + <bean id="empireApp" class="org.apache.empire.spring.example1.EmpireAppImpl" parent="empireDao"/> + + + <bean id="empireDb" class="org.apache.empire.spring.DBDatabaseFactoryBean"> + <property name="driverClass" value="${empire.driver}"/> + <property name="schema" value="${empire.schemaname}"/> + <property name="databaseClass" value="org.apache.empire.spring.example1.SampleDB"/> + </bean> + + <bean id="empireDao" abstract="true"> + <property name="database" ref="empireDb"/> + <property name="empireTemplate" ref="empireTemplate"/> + </bean> + + + <bean id="empireTemplate" class="org.apache.empire.spring.EmpireTemplate"> + <property name="dataSource" ref="dataSource"/> + </bean> + + + + + + + + + + + + + + +</beans> \ No newline at end of file http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/resources/example2/applicationContext-employee.xml ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/resources/example2/applicationContext-employee.xml b/empire-db-examples/empire-db-example-spring/src/main/resources/example2/applicationContext-employee.xml new file mode 100644 index 0000000..08ce097 --- /dev/null +++ b/empire-db-examples/empire-db-example-spring/src/main/resources/example2/applicationContext-employee.xml @@ -0,0 +1,85 @@ +<?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. +--> +<beans xmlns="http://www.springframework.org/schema/beans"; + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xmlns:tx="http://www.springframework.org/schema/tx"; + xmlns:aop="http://www.springframework.org/schema/aop"; + xsi:schemaLocation=" + http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd + http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd + http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd";> + + + <!-- one option is to use a class from spring that will read the properties file + and replaces the ${...} placeholders with the appropriate values --> + <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> + <property name="locations"> + <value>classpath:/settings.properties</value> + </property> + </bean> + + <!-- Data Source / DB Settings --> + <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> + <property name="driverClassName" value="${jdbc.driverClass}"/> + <property name="url" value="${jdbc.url}"/> + <property name="username" value="${jdbc.username}"/> + <property name="password" value="${jdbc.password}"/> + </bean> + + <!-- Transaction manager for a single JDBC DataSource (alternative to JTA) --> + <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> + <constructor-arg ref="dataSource"/> + </bean> + <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> + <property name="transactionManager" ref="transactionManager"/> + </bean> + + <!-- @Transactional --> + <tx:annotation-driven transaction-manager="transactionManager"/> + + <!-- Empire-DB classes --> + + <!-- application dao/services --> + <bean id="employeeDao" class="org.apache.empire.spring.example2.EmployeeDaoImpl" parent="empireDao"/> + + + <bean id="sampleDb" class="org.apache.empire.spring.DBDatabaseFactoryBean"> + <property name="driverClass" value="${empire.driver}"/> + <property name="schema" value="${empire.schemaname}"/> + <property name="databaseClass" value="org.apache.empire.spring.example1.SampleDB"/> + </bean> + + <bean id="empireDao" abstract="true"> + <property name="database" ref="sampleDb"/> + <property name="empireTemplate" ref="empireTemplate"/> + </bean> + + + <bean id="empireTemplate" class="org.apache.empire.spring.EmpireTemplate"> + <property name="dataSource" ref="dataSource"/> + </bean> + + + + + + + +</beans> \ No newline at end of file http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-examples/empire-db-example-spring/src/main/resources/settings.properties ---------------------------------------------------------------------- diff --git a/empire-db-examples/empire-db-example-spring/src/main/resources/settings.properties b/empire-db-examples/empire-db-example-spring/src/main/resources/settings.properties index 6c36099..e5ba794 100644 --- a/empire-db-examples/empire-db-example-spring/src/main/resources/settings.properties +++ b/empire-db-examples/empire-db-example-spring/src/main/resources/settings.properties @@ -22,7 +22,7 @@ jdbc.username=sa jdbc.password= empire.driver= org.apache.empire.db.hsql.DBDatabaseDriverHSql -empire.schemaname=DBSAMPLE1 +empire.schemaname= ## Derby settings (tested) http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-spring/pom.xml ---------------------------------------------------------------------- diff --git a/empire-db-spring/pom.xml b/empire-db-spring/pom.xml index 0cdbad1..3579299 100644 --- a/empire-db-spring/pom.xml +++ b/empire-db-spring/pom.xml @@ -38,11 +38,6 @@ <artifactId>empire-db</artifactId> </dependency> <dependency> - <groupId>hsqldb</groupId> - <artifactId>hsqldb</artifactId> - </dependency> - - <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> @@ -54,11 +49,6 @@ <version>${spring.version}</version> </dependency> - <dependency> - <groupId>commons-dbcp</groupId> - <artifactId>commons-dbcp</artifactId> - <version>1.2.2</version> - </dependency> </dependencies> </project> \ No newline at end of file http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java ---------------------------------------------------------------------- diff --git a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java b/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java deleted file mode 100644 index ddf809f..0000000 --- a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireApp.java +++ /dev/null @@ -1,47 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -package org.apache.empire.spring.example1; - - -/** - * This is the interface to the application. It is usually some high level interface; for this basic - * example this is the dao object. - * - */ -public interface EmpireApp { - - void clearDatabase(); - - void setupDatabase(); - - Integer insertDepartment(String departmentName, String businessUnit); - - Integer insertEmployee(String firstName, String lastName, String gender, int departmentId); - - void updateEmployee(int idPers, String phoneNumber); - - void doQuery(QueryType type); - - public enum QueryType - { - Reader, - BeanList, - XmlDocument - } -} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java ---------------------------------------------------------------------- diff --git a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java b/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java deleted file mode 100644 index ba5bd45..0000000 --- a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/EmpireAppImpl.java +++ /dev/null @@ -1,336 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -package org.apache.empire.spring.example1; - -import java.sql.Connection; -import java.sql.SQLException; -import java.util.HashMap; -import java.util.List; -import java.util.Map; - -import org.apache.empire.db.DBColumn; -import org.apache.empire.db.DBColumnExpr; -import org.apache.empire.db.DBCommand; -import org.apache.empire.db.DBDatabase; -import org.apache.empire.db.DBDatabaseDriver; -import org.apache.empire.db.DBReader; -import org.apache.empire.db.DBRecord; -import org.apache.empire.db.DBRecordData; -import org.apache.empire.db.DBSQLScript; -import org.apache.empire.db.DBTable; -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.spring.DBReaderExtractor; -import org.apache.empire.spring.DBRecordCallbackHandler; -import org.apache.empire.spring.DBRecordMapper; -import org.apache.empire.spring.EmpireDaoSupport; -import org.apache.empire.xml.XMLWriter; -import org.springframework.dao.DataAccessException; -import org.springframework.jdbc.core.ConnectionCallback; -import org.springframework.transaction.annotation.Transactional; -import org.w3c.dom.Document; - -/** - * - */ -public class EmpireAppImpl extends EmpireDaoSupport implements EmpireApp { - - @Transactional - public void clearDatabase() { - SampleDB db = getDatabase(); - - DBCommand cmd = db.createCommand(); - // Delete all Employees (no constraints) - getEmpireTemplate().executeDelete(db.EMPLOYEES, cmd); - // Delete all Departments (no constraints) - getEmpireTemplate().executeDelete(db.DEPARTMENTS, cmd); - } - - - @Transactional - public Integer insertDepartment(String departmentName, String businessUnit) { - SampleDB db = getDatabase(); - - DBRecord rec = new DBRecord(); - rec.create(db.DEPARTMENTS); - rec.setValue(db.DEPARTMENTS.NAME, departmentName); - rec.setValue(db.DEPARTMENTS.BUSINESS_UNIT, businessUnit); - - getEmpireTemplate().updateRecord(rec); - // Return Department ID - return rec.getInt(db.DEPARTMENTS.DEPARTMENT_ID); - } - - @Transactional - public Integer insertEmployee(String firstName, String lastName, - String gender, int departmentId) { - SampleDB db = getDatabase(); - - 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); - getEmpireTemplate().updateRecord(rec); - // Return Employee ID - return rec.getInt(db.EMPLOYEES.EMPLOYEE_ID); - } - - @Transactional - public void updateEmployee(int idPers, String phoneNumber) { - if (getEmployee(idPers) == null) { - // if you like more verbose exceptions for the app - throw new IllegalArgumentException( - "The specified employee does not exist."); - } - - SampleDB db = getDatabase(); - - DBRecord rec = getEmpireTemplate().openRecord(db.EMPLOYEES, idPers); - // Set - rec.setValue(db.EMPLOYEES.PHONE_NUMBER, phoneNumber); - getEmpireTemplate().updateRecord(rec); - } - - @Transactional(readOnly = true) - public void doQuery(QueryType type) { - SampleDB db = getDatabase(); - - // Define the query - DBCommand cmd = db.createCommand(); - // Define shortcuts for tables used - not necessary but convenient - final SampleDB.Employees EMP = db.EMPLOYEES; - final SampleDB.Departments DEP = db.DEPARTMENTS; - - // The following expression concats lastname + ', ' + firstname - final 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 - final 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); - final 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); - if (db.getDriver() instanceof DBDatabaseDriverPostgreSQL) { - // postgres does not support the substring expression - cmd.select(EMP.GENDER, EMP.PHONE_NUMBER); - } else { - 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); - - // Query Records and print output - try { - // Open Reader - System.out.println("Running Query:"); - System.out.println(cmd.getSelect()); - // Print output - System.out.println("---------------------------------"); - switch (type) { - case BeanList: - // Text-Output by iterating through all records. - DBRecordCallbackHandler readerImpl = new DBRecordCallbackHandler() { - - public void processRecord(DBRecordData reader) { - 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)); - - } - }; - getEmpireTemplate().query(cmd, readerImpl); - break; - case Reader: - // Text-Output using a list of Java Beans supplied by the - // DBReader - DBReaderExtractor<List<SampleBean>> beanListImpl = new DBReaderExtractor<List<SampleBean>>() { - - public List<SampleBean> process(DBReader reader) { - return reader.getBeanList(SampleBean.class); - } - }; - List<SampleBean> beanList = getEmpireTemplate().query(cmd, - beanListImpl); - 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 - XmlDocumentExtractor xmlImpl = new XmlDocumentExtractor(); - Document doc = getEmpireTemplate().query(cmd, xmlImpl); - // Print XML Document to System.out - XMLWriter.debug(doc); - break; - } - - } finally { - - // always close Reader - - // reader is closed in EmpireTemplate's methods - - // reader.close(); - } - } - - public Map<Object, Object> getDepartment(int id) { - SampleDB db = getDatabase(); - return get(db.DEPARTMENTS, id); - } - - public Map<Object, Object> getEmployee(int id) { - SampleDB db = getDatabase(); - return get(db.EMPLOYEES, id); - } - - private Map<Object, Object> get(DBTable table, int pk) { - - SampleDB db = getDatabase(); - - DBCommand cmd = db.createCommand(); - cmd.select(table.getColumns()); - cmd.where(table.getPrimaryKey().getColumns()[0].is(pk)); // i know there - // is just - // one - // pk-column - // ;-) - Map<Object, Object> dep = getEmpireTemplate().queryForObject(cmd, - new RowToObjectMapDataMapper(table)); - - return dep; - } - - public static class XmlDocumentExtractor implements - DBReaderExtractor<Document> { - - public Document process(DBReader reader) { - return reader.getXmlDocument(); - } - - } - - public static class RowToObjectMapDataMapper implements - DBRecordMapper<Map<Object, Object>> { - - DBTable table; - - public RowToObjectMapDataMapper(DBTable table) { - super(); - this.table = table; - } - - public Map<Object, Object> mapRecord(DBRecordData record, int rowNum) { - Map<Object, Object> dep = new HashMap<Object, Object>(); - for (DBColumn col : table.getColumns()) { - dep.put(col.getName(), record.getValue(col)); - } - return dep; - } - - } - - public boolean databaseExists() { - Connection conn = getConnection(); - try { - DBDatabase db = getDatabase(); - if (db.getTables() == null || db.getTables().isEmpty()) { - throw new AssertionError( - "There are no tables in this database!"); - } - DBCommand cmd = db.createCommand(); - if (cmd == null) { - throw new AssertionError("The DBCommand object is null."); - } - DBTable t = db.getTables().get(0); - cmd.select(t.count()); - return (db.querySingleInt(cmd, -1, conn) >= 0); - } catch (Exception e) { - return false; - } - } - - public void initializeDatabase() { - if (!databaseExists()) { - createDatabase(); - } - } - - @Transactional - public void setupDatabase() { - initializeDatabase(); - } - - public void createDatabase() { - - // create DLL for Database Definition - final DBSQLScript script = new DBSQLScript(); - final DBDatabaseDriver driver = getDatabase().getDriver(); - getDatabase().getCreateDDLScript(driver, script); - - // Show DLL Statement - System.out.println(script.toString()); - // Execute Script - getEmpireTemplate().execute(new ConnectionCallback<Object>() { - - public Object doInConnection(Connection con) throws SQLException, - DataAccessException { - script.executeAll(driver, con, false); - return null; - } - }); - - } - -} http://git-wip-us.apache.org/repos/asf/empire-db/blob/cc86a1fd/empire-db-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java ---------------------------------------------------------------------- diff --git a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java b/empire-db-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java deleted file mode 100644 index efe89b1..0000000 --- a/empire-db-spring/src/main/java/org/apache/empire/spring/example1/SampleBean.java +++ /dev/null @@ -1,126 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, - * software distributed under the License is distributed on an - * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY - * KIND, either express or implied. See the License for the - * specific language governing permissions and limitations - * under the License. - */ -package org.apache.empire.spring.example1; - - -/** - * 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(); - } - -}
