mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java Source code

Java tutorial

Introduction

Here is the source code for mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

Source

/*
 * 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;
    }
}