Java tutorial
/* * Permission is hereby granted, free of charge, to any person obtaining a copy * of this software and associated documentation files (the "Software"), to deal * in the Software without restriction, including without limitation the rights * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell * copies of the Software, and to permit persons to whom the Software is * furnished to do so, subject to the following conditions: * The above copyright notice and this permission notice shall be included in * all copies or substantial portions of the Software. * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN * THE SOFTWARE. */ package mil.army.usace.data.dataquery.rdbms; import com.google.gson.Gson; import mil.army.usace.data.dataquery.rdbms.annotation.GenerationType; import mil.army.usace.data.dataquery.rdbms.annotation.Schema; import java.lang.reflect.*; import java.sql.*; import java.text.SimpleDateFormat; import java.util.*; import mil.army.usace.data.dataquery.GeneratedKey; import mil.army.usace.data.dataquery.rdbms.annotation.DbStruct; import mil.army.usace.data.dataquery.DataQuery; import mil.army.usace.data.dataquery.DataQueryException; import mil.army.usace.data.dataquery.DataQueryFactory.DB; import mil.army.usace.data.dataquery.rdbms.annotation.Column; import mil.army.usace.data.dataquery.rdbms.annotation.Entity; import mil.army.usace.data.dataquery.rdbms.annotation.GeneratedValue; import mil.army.usace.data.dataquery.rdbms.annotation.Id; import mil.army.usace.data.dataquery.rdbms.annotation.Optional; import mil.army.usace.data.dataquery.rdbms.annotation.Transient; import mil.army.usace.data.dataquery.rdbms.implementations.OracleConverter; import mil.army.usace.data.dataquery.utility.ConversionUtility; import mil.army.usace.data.dataquery.utility.DbConverter; import mil.army.usace.data.dataquery.utility.DefaultConverter; import mil.army.usace.data.dataquery.utility.Tuple; import mil.army.usace.data.dataquery.utility.Tuple3; import org.apache.commons.convert.*; import org.apache.commons.lang.StringEscapeUtils; public abstract class RdbmsDataQuery implements DataQuery, AutoCloseable { Connection conn; private final int GET = 0; private final int SET = 1; public DB db = null; protected final DbConverter converter; private int batchSize = 100; private boolean useBatch = true; private enum DML { INSERT, UPDATE, DELETE } //@TODO...log errors, etc. remove println.... public RdbmsDataQuery(Driver driver, String url, DB db) { try { DriverManager.registerDriver(driver); DriverManager.setLoginTimeout(10); conn = DriverManager.getConnection(url); conn.setAutoCommit(true); this.db = db; switch (db) { case ORACLE: this.converter = new OracleConverter(conn); break; default: this.converter = new DefaultConverter(); } } catch (Exception ex) { System.out.println("Error Making Connection"); throw new RuntimeException(ex.getMessage()); } } public RdbmsDataQuery(Connection conn, DB db) { this.conn = conn; this.db = db; switch (db) { case ORACLE: this.converter = new OracleConverter(conn); break; default: this.converter = new DefaultConverter(); } } public Connection getConnection() { return this.conn; } @Override public void close() { try { conn.close(); } catch (SQLException ex) { throw new RuntimeException(ex); } } public void startTransaction() { try { conn.setAutoCommit(false); } catch (SQLException ex) { throw new RuntimeException(ex); } } public void commitTransaction() { try { conn.commit(); conn.setAutoCommit(true); } catch (SQLException ex) { throw new RuntimeException(ex); } } public void rollbackTransaction() { try { conn.rollback(); } catch (SQLException ex) { throw new RuntimeException(ex); } } public boolean inTransaction() { try { return (conn.getAutoCommit()) ? false : true; } catch (SQLException ex) { throw new RuntimeException(ex); } } public Savepoint setSavepoint(String name) { try { return conn.setSavepoint(name); } catch (SQLException ex) { throw new RuntimeException(ex); } } public void rollbackSavepoint(Savepoint savepoint) { try { conn.rollback(savepoint); } catch (SQLException ex) { throw new RuntimeException(ex); } } public void releaseSavepoint(Savepoint savepoint) { try { conn.releaseSavepoint(savepoint); } catch (SQLException ex) { throw new RuntimeException(ex); } } public abstract Long getNextSequenceVal(String sequenceName); protected abstract String getSequenceSql(String sequenceName); public int executeUpdate(String sql, Object... params) { PreparedStatement st = null; try { st = conn.prepareStatement(sql); setParams(st, params); int rowsAffected = st.executeUpdate(); return rowsAffected; } catch (SQLException ex) { throw new RuntimeException(ex); } finally { if (st != null) { try { st.close(); } catch (Exception ex) { } } } } public void insertRecord(Object record) { ArrayList records = new ArrayList(); records.add(record); insertRecords(records); } public GeneratedKey insertRecord(Object record, boolean useDeclaredOnly, boolean returnGeneratedKey) { ArrayList records = new ArrayList(); if (returnGeneratedKey) { return insertWithGeneratedKeyReturn(record, useDeclaredOnly); } else { records.add(record); insertRecords(records, useDeclaredOnly); return null; } } public void insertRecords(List records) { insertRecords(records, false); } public void insertRecords(List records, boolean useDeclaredOnly) { runDml(DML.INSERT, records, useDeclaredOnly, null); } public void updateRecords(List records) { updateRecords(records, false); } public void updateRecords(List records, boolean useDeclaredOnly) { runDml(DML.UPDATE, records, useDeclaredOnly, null); } public void updateRecords(List records, boolean useDeclaredOnly, List<String> includeFields) { runDml(DML.UPDATE, records, useDeclaredOnly, includeFields); } public void updateRecord(Object record) { ArrayList records = new ArrayList(); records.add(record); updateRecords(records); } public void updateRecord(Object record, boolean useDeclaredOnly) { ArrayList records = new ArrayList(); records.add(record); updateRecords(records, useDeclaredOnly); } public void deleteRecord(Object record) { List records = new ArrayList(); records.add(record); deleteRecords(records); } public void deleteRecords(List records) { runDml(DML.DELETE, records, false, null); } public int deleteRecords(Class objClass, String criteria, Object... criteriaParams) { PreparedStatement st = null; boolean inTrans = inTransaction(); if (!inTrans) startTransaction(); try { String schema = getEntitySchema(objClass); Boolean isCamelCased = useCamelCase(objClass); String tableName = getTableName(objClass); if (tableName == null) tableName = ((schema == null) ? "" : (schema + ".")) + getDbName(isCamelCased, objClass.getSimpleName(), null); String sql = "delete from " + tableName; if (criteria != null && (!criteria.equals(""))) { sql += " " + criteria; } st = conn.prepareStatement(sql); setParams(st, criteriaParams); int rowsDeleted = st.executeUpdate(); if (!inTrans) commitTransaction(); return rowsDeleted; } catch (Exception ex) { ex.printStackTrace(); if (!inTrans) rollbackTransaction(); throw new RuntimeException(ex); } finally { if (st != null) { try { st.close(); } catch (Exception ex) { } } } } public <T> RecordsetStream<T> getRecordsStream(Class objClass, String criteria, Object[] params) { return getRecordsStream(objClass, criteria, false, false, params); } @Override public <T> RecordsetStream<T> getRecordsStream(Class objClass, String command, boolean useDeclaredOnly, boolean isFullCommand, Object[] criteriaParams) { HashMap<Method, String> fieldMapping; if (isFullCommand) { Boolean isCamelCased = useCamelCase(objClass); fieldMapping = getFieldMapping(objClass, SET, isCamelCased, useDeclaredOnly); } else { Tuple<String, HashMap<Method, String>> sqlCommand = prepareSelectCommand(objClass, command, useDeclaredOnly); command = sqlCommand.getA(); fieldMapping = sqlCommand.getB(); } return commandToStream(objClass, command, fieldMapping, criteriaParams); } public <T> List<T> getDbStructs(Class objClass, String command, boolean isFullCommand, Object[] params) { HashMap<Field, String> fieldMapping; if (isFullCommand) { Boolean isCamelCased = useCamelCase(objClass); fieldMapping = getDbStructFieldMapping(objClass); } else { Tuple<String, HashMap<Field, String>> sqlCommand = prepareDbStructSelectCommand(objClass, command); command = sqlCommand.getA(); fieldMapping = sqlCommand.getB(); } return commandToDbStruct(objClass, command, fieldMapping, params); } public <T> T getDbStruct(Class objClass, String command, boolean isFullCommand, Object[] params) { HashMap<Field, String> fieldMapping; if (isFullCommand) { Boolean isCamelCased = useCamelCase(objClass); fieldMapping = getDbStructFieldMapping(objClass); } else { Tuple<String, HashMap<Field, String>> sqlCommand = prepareDbStructSelectCommand(objClass, command); command = sqlCommand.getA(); fieldMapping = sqlCommand.getB(); } List<T> records = commandToDbStruct(objClass, command, fieldMapping, params); if (records.size() > 0) return records.get(0); else return null; } @Override public <T> List<T> getRecords(Class objClass, String criteria, Object[] criteriaParams) { return getRecords(objClass, criteria, false, false, criteriaParams); } @Override public <T> List<T> getRecords(Class objClass, String command, boolean useDeclaredOnly, boolean isFullCommand, Object[] params) { HashMap<Method, String> fieldMapping; if (isFullCommand) { Boolean isCamelCased = useCamelCase(objClass); fieldMapping = getFieldMapping(objClass, SET, isCamelCased, useDeclaredOnly); } else { Tuple<String, HashMap<Method, String>> sqlCommand = prepareSelectCommand(objClass, command, useDeclaredOnly); command = sqlCommand.getA(); fieldMapping = sqlCommand.getB(); } return commandToRecords(objClass, command, fieldMapping, params); } public <T> T getRecord(Class objClass, String criteria, Object[] criteriaParams) { return getRecord(objClass, criteria, false, false, criteriaParams); } public <T> T getRecord(Class objClass, String criteria, boolean useDeclaredOnly, boolean isFullCommand, Object[] criteriaParams) { List<T> records = getRecords(objClass, criteria, useDeclaredOnly, isFullCommand, criteriaParams); if (records.size() == 1) { return records.get(0); } else if (records.size() == 0) { return null; } else { throw new RuntimeException("Query returned more than 1 record."); } } public <T> T getSingleSqlValue(String sql, Object[] params) { return getSingleSqlValue(sql, null, params, false); } public <T> T getSingleSqlValue(String sql, Class returnType, Object[] params, boolean returnEmptyRecordsetAsNull) { PreparedStatement st = null; ResultSet rs = null; try { st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); Object val = null; //if there are no records, check returnEmptyRecordsetIsNull boolean //and allow error to be thrown if returnEmptyRecordsetIsNull==false //fyi...returnEmptyRecordsetIsNull==false is the default behavior if (rs.next() || !returnEmptyRecordsetAsNull) { val = rs.getObject(1); } if (val == null) { return null; } else { if (returnType != null) return (T) ConversionUtility.convertType(rs.getObject(1), returnType); else return (T) rs.getObject(1); } } catch (Exception ex) { throw new DataQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } } public enum JsonKeyCase { UPPER, LOWER, DEFAULT } public String getRecordsAsJson(String sql, JsonKeyCase jsonKeyCase, Boolean useCamelCase, Boolean escapeHtml, SimpleDateFormat dateFormatter, Object... params) { PreparedStatement st = null; ResultSet rs = null; ResultSetMetaData rsMetaData = null; Gson gson = new Gson(); try { StringBuilder stringBuilder = null; stringBuilder = new StringBuilder("["); st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); rsMetaData = rs.getMetaData(); while (rs.next()) { stringBuilder.append("{"); for (int i = 1; i <= rsMetaData.getColumnCount(); i++) { String attrName; switch (jsonKeyCase) { case UPPER: attrName = rsMetaData.getColumnName(i).toUpperCase(); break; case LOWER: attrName = rsMetaData.getColumnName(i).toLowerCase(); break; default: attrName = rsMetaData.getColumnName(i); } String test = dbToFieldName(attrName); stringBuilder.append("\"").append((useCamelCase) ? dbToFieldName(attrName) : attrName) .append("\":"); Object val = rs.getObject(i); if (val == null) { stringBuilder.append("null,"); } else if (val instanceof Number) { stringBuilder.append(val.toString()).append(","); } else if (val instanceof java.sql.Date) { stringBuilder.append("\"") .append(dateFormatter.format(new java.util.Date(((java.sql.Date) val).getTime()))) .append("\","); } else if (val instanceof java.sql.Timestamp) { stringBuilder.append("\"") .append(dateFormatter .format(new java.util.Date(((java.sql.Timestamp) val).getTime()))) .append("\","); } else { if (escapeHtml) stringBuilder.append(gson.toJson(rs.getObject(i).toString())).append(","); else stringBuilder.append("\"").append(rs.getObject(i).toString()).append("\","); } } stringBuilder.deleteCharAt(stringBuilder.length() - 1); stringBuilder.append("},"); } if (stringBuilder.length() > 1) stringBuilder.deleteCharAt(stringBuilder.length() - 1); stringBuilder.append("]"); return stringBuilder.toString(); } catch (Exception ex) { throw new DataQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } } public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class returnTypeClass, Object[] params) { if (returnType != ReturnType.ARRAYLIST) { throw new UnsupportedOperationException( "Invalid method signature. Single class return type is only valid for a return type of ArrayList"); } else { return getRecordsAsSimpleCollection(returnType, sql, new Class[] { returnTypeClass }, params); } } public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Object[] params) { return getRecordsAsSimpleCollection(returnType, sql, new Class[] {}, params); } public <T> List<T> getRecordsAsSimpleCollection(ReturnType returnType, String sql, Class[] returnTypes, Object[] params) { if (returnTypes.length == 0) returnTypes = null; PreparedStatement st = null; ResultSet rs = null; ResultSetMetaData rsMetaData = null; try { List<T> records = new ArrayList<>(); st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); if (returnType == ReturnType.RECORDSET || returnType == ReturnType.RECORDSETMAP) rsMetaData = rs.getMetaData(); while (rs.next()) { switch (returnType) { case ARRAYLIST: if (returnTypes != null) records.add((T) ConversionUtility.convertType(rs.getObject(1), returnTypes[0])); else records.add((T) rs.getObject(1)); break; case ARRAYLIST_TUPLE: if (returnTypes != null) records.add((T) new Tuple(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]), ConversionUtility.convertType(rs.getObject(2), returnTypes[1]))); else records.add((T) new Tuple(rs.getObject(1), rs.getObject(2))); break; case ARRAYLIST_TUPLE3: if (returnTypes != null) records.add((T) new Tuple3(ConversionUtility.convertType(rs.getObject(1), returnTypes[0]), ConversionUtility.convertType(rs.getObject(2), returnTypes[1]), ConversionUtility.convertType(rs.getObject(3), returnTypes[2]))); else records.add((T) new Tuple3(rs.getObject(1), rs.getObject(2), rs.getObject(3))); break; case RECORDSET: ArrayList record = new ArrayList(); for (int i = 0; i < rsMetaData.getColumnCount(); i++) { record.add((returnTypes == null) ? rs.getObject(i + 1) : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i])); } ((ArrayList) records).add(record); break; case RECORDSETMAP: HashMap<String, Object> recordmap = new HashMap<>(); for (int i = 0; i < rsMetaData.getColumnCount(); i++) { recordmap.put(rsMetaData.getColumnName(i + 1), (returnTypes == null) ? rs.getObject(i + 1) : ConversionUtility.convertType(rs.getObject(i + 1), returnTypes[i])); } ((ArrayList) records).add(recordmap); break; } } return records; } catch (Exception ex) { throw new DataQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } } public <T, S> HashMap<T, S> getRecordsAsHashmap(String sql, Object[] params, Class[] returnTypes) { PreparedStatement st = null; ResultSet rs = null; ResultSetMetaData rsMetaData = null; try { HashMap<T, S> records = new HashMap<>(); st = conn.prepareStatement(sql); setParams(st, params); rs = st.executeQuery(); while (rs.next()) { if (returnTypes != null) records.put((T) ConversionUtility.convertType(rs.getObject(1), returnTypes[0]), (S) ConversionUtility.convertType(rs.getObject(2), returnTypes[1])); else records.put((T) rs.getObject(1), (S) rs.getObject(2)); } return records; } catch (Exception ex) { throw new DataQueryException(sql, null, ex); } finally { closeOnFinally(rs, st); } } /////////////////////////////PRIVATE////////////////////////////////////////// //@TODO create version of method for DbStructs private Tuple<String, HashMap<Method, String>> prepareSelectCommand(Class objClass, String criteria, boolean useDeclaredOnly) { String command = null; String schema = getEntitySchema(objClass); Boolean isCamelCased = useCamelCase(objClass); HashMap<Method, String> fieldMapping = getFieldMapping(objClass, SET, isCamelCased, useDeclaredOnly); command = this.getSql(objClass); if (command == null) { String tableName = getTableName(objClass); if (tableName == null) tableName = getDbName(isCamelCased, objClass.getSimpleName(), null); command = "select " + getSqlFields(fieldMapping) + " from " + ((schema == null) ? "" : (schema + ".")) + tableName; } if (criteria != null && (!criteria.equals(""))) { command += " " + criteria; } return new Tuple<>(command, fieldMapping); } private Tuple<String, HashMap<Field, String>> prepareDbStructSelectCommand(Class objClass, String criteria) { String command = null; String schema = getEntitySchema(objClass); Boolean isCamelCased = useCamelCase(objClass); HashMap<Field, String> fieldMapping = getDbStructFieldMapping(objClass); command = this.getSql(objClass); if (command == null) { String tableName = getTableName(objClass); if (tableName == null) tableName = getDbName(isCamelCased, objClass.getSimpleName(), null); command = "select " + getSqlFields(fieldMapping) + " from " + ((schema == null) ? "" : (schema + ".")) + tableName; } if (criteria != null && (!criteria.equals(""))) { command += " " + criteria; } return new Tuple<>(command, fieldMapping); } private GeneratedKey insertWithGeneratedKeyReturn(Object record, boolean useDeclaredOnly) { PreparedStatement st = null; boolean inTrans = inTransaction(); String command = null; int batchCount = 0; GeneratedKey gk = null; if (!inTrans) startTransaction(); try { Class objClass = record.getClass(); String schema = getEntitySchema(objClass); Boolean isCamelCased = useCamelCase(objClass); HashMap<Method, String> fieldMapping = getFieldMapping(objClass, GET, isCamelCased, useDeclaredOnly); String idFieldName = getIdFieldName(fieldMapping); HashMap<Integer, Method> indexMapping = new HashMap(); String tableName = getTableName(objClass); if (tableName == null) tableName = getDbName(isCamelCased, objClass.getSimpleName(), null); command = getInsertCommand(tableName, schema, fieldMapping, indexMapping); if (idFieldName != null) { st = conn.prepareStatement(command, new String[] { idFieldName }); } else { st = conn.prepareStatement(command); } for (int index : indexMapping.keySet()) { Object value = indexMapping.get(index).invoke(record, null); if (value instanceof java.util.Date) { value = new java.sql.Date(((java.util.Date) value).getTime()); } st.setObject((Integer) index, value); } st.execute(); ResultSet rs = st.getGeneratedKeys(); if (rs.next()) { gk = new GeneratedKey(idFieldName, rs.getObject(1)); } if (!inTrans) commitTransaction(); return gk; } catch (Exception ex) { ex.printStackTrace(); if (!inTrans) rollbackTransaction(); throw new DataQueryException(command, "insertWithGeneratedKeyReturn", ex); } finally { if (st != null) { try { st.close(); } catch (Exception ex) { } } } } //@TODO prepare DML methods for DbStruct private void runDml(DML dmlType, List records, boolean useDeclaredOnly, List<String> includeFields) { PreparedStatement st = null; boolean inTrans = inTransaction(); int batchCount = 0; String command = null; if (!inTrans) startTransaction(); try { Object obj = records.get(0); Class objClass = obj.getClass(); String schema = getEntitySchema(objClass); Boolean isCamelCased = useCamelCase(objClass); HashMap<Method, String> fieldMapping = getFieldMapping(objClass, GET, isCamelCased, useDeclaredOnly); HashMap<Integer, Method> indexMapping = new HashMap(); String tableName = getTableName(objClass); if (tableName == null) tableName = getDbName(isCamelCased, objClass.getSimpleName(), null); if (dmlType == DML.UPDATE) command = getUpdateCommand(tableName, schema, fieldMapping, indexMapping, includeFields); else if (dmlType == DML.INSERT) command = getInsertCommand(tableName, schema, fieldMapping, indexMapping); else command = getDeleteCommand(tableName, schema, fieldMapping, indexMapping); st = conn.prepareStatement(command); for (Object record : records) { for (int index : indexMapping.keySet()) { Object value = indexMapping.get(index).invoke(record, null); if (value instanceof java.util.Date) { value = new java.sql.Date(((java.util.Date) value).getTime()); } st.setObject((Integer) index, value); } if (useBatch == true) st.addBatch(); else st.executeUpdate(); if (useBatch == true && ++batchCount % batchSize == 0) { st.executeBatch(); } } if (useBatch == true) st.executeBatch(); //flush out remaining records if (!inTrans) commitTransaction(); } catch (Exception ex) { ex.printStackTrace(); if (!inTrans) rollbackTransaction(); throw new DataQueryException(command, "runDml", ex); } finally { if (st != null) { try { st.close(); } catch (Exception ex) { } } } } private String getDeleteCommand(String tableName, String schema, HashMap<Method, String> fieldMapping, HashMap<Integer, Method> indexMapping) { String command = "delete from " + ((schema == null) ? "" : (schema + ".")) + tableName; String fields = ""; int paramIndex = 1; String pkField = null; Method pkFieldMethod = null; for (Method dbFieldMethod : fieldMapping.keySet()) { if (dbFieldMethod.isAnnotationPresent(Id.class)) { pkFieldMethod = dbFieldMethod; pkField = (String) fieldMapping.get(pkFieldMethod); } } if (pkFieldMethod == null) { throw new DataQueryException(String.format("Missing Entity Primary Key Field for %s", tableName)); } indexMapping.put(paramIndex, pkFieldMethod); command += " where " + pkField + " = ?"; return command; } private String getUpdateCommand(String tableName, String schema, HashMap<Method, String> fieldMapping, HashMap<Integer, Method> indexMapping, List<String> includeFields) { String command = "update " + ((schema == null) ? "" : (schema + ".")) + tableName + " set "; String fields = ""; int paramIndex = 1; String pkField = null; Method pkFieldMethod = null; for (Method dbFieldMethod : fieldMapping.keySet()) { if (dbFieldMethod.isAnnotationPresent(Id.class)) { pkFieldMethod = dbFieldMethod; pkField = (String) fieldMapping.get(pkFieldMethod); } else { String field = (String) fieldMapping.get(dbFieldMethod); if (includeFields != null) { String incField = dbToFieldName(field); if (includeFields.contains(incField)) { fields += field + "=?,"; indexMapping.put(paramIndex, dbFieldMethod); paramIndex++; } } else { fields += field + "=?,"; indexMapping.put(paramIndex, dbFieldMethod); paramIndex++; } } } if (pkFieldMethod == null) { throw new DataQueryException(String.format("Missing Entity Primary Key Field for %s", tableName)); } indexMapping.put(paramIndex, pkFieldMethod); //@TODO if there is no pkfield..throw exception! fields = fields.substring(0, fields.length() - 1); command += fields + " where " + pkField + " = ?"; return command; } private String getSqlFields(HashMap<?, String> fieldMapping) { String fields = null; for (String fieldName : fieldMapping.values()) { if (fields == null) { fields = fieldName; } else { fields += "," + fieldName; } } return fields; } private String getInsertCommand(String tableName, String schema, HashMap<Method, String> fieldMapping, HashMap<Integer, Method> indexMapping) { String command = "insert into " + ((schema == null) ? "" : (schema + ".")) + tableName; String fields = "("; String values = "("; int paramIndex = 1; for (Method dbFieldMethod : fieldMapping.keySet()) { String fieldName = fieldMapping.get(dbFieldMethod); if (dbFieldMethod.isAnnotationPresent(Id.class)) { if (dbFieldMethod.isAnnotationPresent(GeneratedValue.class)) { //generally do nothing here..simply omit pk field.. GeneratedValue gv = dbFieldMethod.getAnnotation(GeneratedValue.class); fields += fieldName + ","; if (gv.strategy() == GenerationType.AUTO) { values += ((schema == null) ? "" : (schema + ".")) + getSequenceSql("seq_" + tableName) + ","; } else { values += ((schema == null) ? "" : (schema + ".")) + getSequenceSql(gv.generator()) + ","; } } else { fields += fieldName + ","; values += "?,"; indexMapping.put(paramIndex, dbFieldMethod); paramIndex++; } } else { fields += fieldName + ","; values += "?,"; indexMapping.put(paramIndex, dbFieldMethod); paramIndex++; } } fields = fields.substring(0, fields.length() - 1) + ")"; values = values.substring(0, values.length() - 1) + ")"; command += fields + " values " + values; return command; } private <T> RecordsetStream<T> commandToStream(Class objClass, String command, HashMap<Method, String> fieldMapping, Object[] params) { PreparedStatement st = null; ResultSet rs = null; try { st = conn.prepareStatement(command); setParams(st, params); rs = st.executeQuery(); RecordsetStream<T> stream = new RecordsetStream<>(command, this, objClass, fieldMapping, st, rs, conn); return stream; } catch (Exception ex) { closeOnFinally(rs, st); if (ex instanceof DataQueryException) throw (DataQueryException) ex; else throw new DataQueryException(command, null, ex); } } private <T> List<T> commandToDbStruct(Class dbStructClazz, String command, HashMap<Field, String> fieldMapping, Object[] params) { PreparedStatement st = null; ResultSet rs = null; try { ArrayList<T> records = new ArrayList(); st = conn.prepareStatement(command); setParams(st, params); rs = st.executeQuery(); while (rs.next()) { T newObj = (T) dbStructClazz.newInstance(); for (Field field : fieldMapping.keySet()) { try { Object val = converter.convertType(rs.getObject((String) fieldMapping.get(field)), field); if (val != null) field.set(newObj, val); } catch (Exception ex) { throw new DataQueryException(command, "Unable to map field:" + field.getName(), ex); } } records.add(newObj); } return records; } catch (Exception ex) { if (ex instanceof DataQueryException) throw (DataQueryException) ex; else throw new DataQueryException(command, null, ex); } finally { closeOnFinally(rs, st); } } private <T> List<T> commandToRecords(Class objClass, String command, HashMap<Method, String> fieldMapping, Object[] params) { PreparedStatement st = null; ResultSet rs = null; try { ArrayList<T> records = new ArrayList(); st = conn.prepareStatement(command); setParams(st, params); rs = st.executeQuery(); while (rs.next()) { T newObj = (T) objClass.newInstance(); for (Method method : fieldMapping.keySet()) { try { Object val = converter.convertType(rs.getObject((String) fieldMapping.get(method)), method); if (val != null) method.invoke(newObj, val); } catch (Exception ex) { if (!method.isAnnotationPresent(Optional.class)) { throw new DataQueryException(command, "Unable to map method:" + method.getName(), ex); } } } records.add(newObj); } return records; } catch (Exception ex) { if (ex instanceof DataQueryException) throw (DataQueryException) ex; else throw new DataQueryException(command, null, ex); } finally { closeOnFinally(rs, st); } } private void closeOnFinally(ResultSet rs, PreparedStatement st) { if (rs != null) { try { rs.close(); } catch (Exception ex) { } } if (st != null) { try { st.close(); } catch (Exception ex) { } } } public void setParams(PreparedStatement st, Object[] params) throws SQLException { if (params != null) { for (int i = 0; i < params.length; i++) { if (params[i] instanceof java.util.Date) { st.setDate(i + 1, new java.sql.Date(((java.util.Date) params[i]).getTime())); } else { st.setObject(i + 1, (Object) params[i]); } } } } private HashMap<Field, String> getDbStructFieldMapping(Class dbStructClazz) { HashMap<Field, String> fieldMapping = new HashMap<>(); Field[] fields = dbStructClazz.getDeclaredFields(); for (Field field : fields) { if (!Modifier.isStatic(field.getModifiers())) { //exclude static fields if (field.getAnnotation(Transient.class) == null) { fieldMapping.put(field, getDbName(field)); } } } return fieldMapping; } private HashMap<Method, String> getFieldMapping(Class cls, int type, Boolean isCamelCased, Boolean useDeclaredOnly) { HashMap<Method, String> fieldMapping = new HashMap<>(); Method[] methods; if (useDeclaredOnly) { methods = cls.getDeclaredMethods(); } else { methods = cls.getMethods(); } for (Method method : methods) { if (method.getAnnotation(Transient.class) == null) { String methodName = method.getName(); if (type == GET) { if (methodName.startsWith("get") && !methodName.equals("getClass")) { fieldMapping.put(method, getDbName(isCamelCased, methodName.substring(3), method)); } } else { if (methodName.startsWith("set") && !methodName.equals("getClass")) { fieldMapping.put(method, getDbName(isCamelCased, methodName.substring(3), method)); } } } } return fieldMapping; } public String getDbName(String attributeName) { String fieldName = ""; for (int i = 0; i < attributeName.length(); i++) { if (i == 0) fieldName += Character.toLowerCase(attributeName.charAt(i)); else { char c = attributeName.charAt(i); if (Character.isUpperCase(c)) fieldName += "_" + Character.toLowerCase(c); else fieldName += c; } } return fieldName; } public String getDbName(Field field) { if (field.isAnnotationPresent(Column.class)) { Column c = field.getAnnotation(Column.class); return c.name(); } else { return getDbName(field.getName()); } } public String getDbName(Boolean isCamelCase, String attributeName, Method method) { if (method != null && method.isAnnotationPresent(Column.class)) { Column c = method.getAnnotation(Column.class); return c.name(); } else { String fieldName = ""; if (isCamelCase) { fieldName = attributeName; } else { fieldName = getDbName(attributeName); } return fieldName; } } public String getDbName(Class clazz) { Boolean useCamelCase = useCamelCase(clazz); return getDbName(useCamelCase, clazz.getSimpleName(), null); } public String dbToClassName(String dbName) throws ClassNotFoundException { String className = ""; for (int i = 0; i < dbName.length(); i++) { if (i == 0) className += Character.toUpperCase(dbName.charAt(i)); else { char c = dbName.charAt(i); if (c == '_') { i++; className += Character.toUpperCase(dbName.charAt(i)); } else { className += Character.toLowerCase(c); } } } return className; } public String dbToFieldName(String dbName) { String fieldName = ""; for (int i = 0; i < dbName.length(); i++) { char c = dbName.charAt(i); if (c == '_') { i++; fieldName += Character.toUpperCase(dbName.charAt(i)); } else { fieldName += Character.toLowerCase(c); } } return fieldName; } public String getIdFieldName(Class clazz) { Boolean useCamelCase = this.useCamelCase(clazz); HashMap<Method, String> fieldMap = this.getFieldMapping(clazz, GET, useCamelCase, Boolean.TRUE); for (Method method : fieldMap.keySet()) { if (method.isAnnotationPresent(Id.class)) { return getDbName(useCamelCase, method.getName().substring(3), method); } } return null; } public String getIdFieldName(HashMap<Method, String> fieldMap) { for (Method method : fieldMap.keySet()) { if (method.isAnnotationPresent(Id.class)) { return fieldMap.get(method); } } return null; } public Boolean useCamelCase(Class cls) { Entity entityAnno = (Entity) cls.getAnnotation(Schema.class); if (entityAnno != null) { return entityAnno.useCamelCase(); } else { return false; } } public Boolean isDbStruct(Class cls) { DbStruct dbStructAnno = (DbStruct) cls.getAnnotation(DbStruct.class); return dbStructAnno != null; } public String getEntitySchema(Class cls) { Entity entityAnno = (Entity) cls.getAnnotation(Entity.class); if (entityAnno != null && !entityAnno.schema().equals("")) { return entityAnno.schema(); } else { return null; } } public String getTableName(Class cls) { Entity entityAnno = (Entity) cls.getAnnotation(Entity.class); if (entityAnno != null && !entityAnno.table().equals("")) { return entityAnno.table(); } else { return null; } } public String getSql(Class cls) { Entity entityAnno = (Entity) cls.getAnnotation(Entity.class); if (entityAnno != null && !entityAnno.sql().equals("")) { return entityAnno.sql(); } else { return null; } } public void setJdbcBatchSize(int batchSize) { this.batchSize = batchSize; } public int getJdbcBatchSize() { return this.batchSize; } public void setUseJdbcBatching(boolean useBatch) { this.useBatch = useBatch; } public boolean getUseJdbcBatching() { return this.useBatch; } }