Search in sources :

Example 1 with TableFieldStructure

use of io.clownfish.clownfish.jdbc.TableFieldStructure in project Clownfish by rawdog71.

the class DatabaseUtil method manageTableUpdate.

private boolean manageTableUpdate(Connection con, DatabaseMetaData dmd, String tablename, HashMap<String, DatatableUpdateProperties> datatableproperties, HashMap<String, ArrayList> dbtables, HashMap<String, Object> dbvalues) {
    Statement stmt = null;
    try {
        TableFieldStructure tfs = getTableFieldsList(dmd, tablename, "");
        DatatableUpdateProperties dtup = datatableproperties.get(tablename);
        StringBuilder sql_update_values = new StringBuilder();
        dtup.getValuelist().stream().map((dtuv) -> {
            sql_update_values.append(dtuv.getField());
            return dtuv;
        }).forEach((dtuv) -> {
            sql_update_values.append(" = ");
            String fieldType = getFieldType(tfs.getTableFieldsList(), dtuv.getField());
            if (null != fieldType) {
                if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                    sql_update_values.append("'");
                }
                sql_update_values.append(dtuv.getValue());
                if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                    sql_update_values.append("'");
                }
                sql_update_values.append(", ");
            }
        });
        sql_update_values.delete(sql_update_values.length() - 2, sql_update_values.length());
        StringBuilder sql_update = new StringBuilder();
        sql_update.append("UPDATE ");
        sql_update.append(tablename);
        sql_update.append(" SET ");
        sql_update.append(sql_update_values);
        StringBuilder sql_condition = new StringBuilder();
        if (null != dtup) {
            sql_condition = buildCondition(dtup.getConditionlist(), tfs.getTableFieldsList());
        }
        sql_update.append(sql_condition);
        stmt = con.createStatement();
        int count = stmt.executeUpdate(sql_update.toString());
        boolean ok = false;
        if (count > 0) {
            ok = true;
        }
        return ok;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return false;
    } finally {
        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        }
    }
}
Also used : Connection(java.sql.Connection) Logger(org.slf4j.Logger) LoggerFactory(org.slf4j.LoggerFactory) CfDatasource(io.clownfish.clownfish.dbentities.CfDatasource) Autowired(org.springframework.beans.factory.annotation.Autowired) DatabaseMetaData(java.sql.DatabaseMetaData) HashMap(java.util.HashMap) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) ArrayList(java.util.ArrayList) DatatableUpdateProperties(io.clownfish.clownfish.jdbc.DatatableUpdateProperties) DatatableCondition(io.clownfish.clownfish.jdbc.DatatableCondition) DatatableProperties(io.clownfish.clownfish.jdbc.DatatableProperties) TableField(io.clownfish.clownfish.jdbc.TableField) SQLException(java.sql.SQLException) List(java.util.List) Component(org.springframework.stereotype.Component) JDBCUtil(io.clownfish.clownfish.jdbc.JDBCUtil) ResultSet(java.sql.ResultSet) CfDatasourceService(io.clownfish.clownfish.serviceinterface.CfDatasourceService) Statement(java.sql.Statement) DatatableNewProperties(io.clownfish.clownfish.jdbc.DatatableNewProperties) DatatableDeleteProperties(io.clownfish.clownfish.jdbc.DatatableDeleteProperties) CfSitedatasource(io.clownfish.clownfish.dbentities.CfSitedatasource) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) SQLException(java.sql.SQLException) Statement(java.sql.Statement) DatatableUpdateProperties(io.clownfish.clownfish.jdbc.DatatableUpdateProperties)

Example 2 with TableFieldStructure

use of io.clownfish.clownfish.jdbc.TableFieldStructure in project Clownfish by rawdog71.

the class DatabaseUtil method manageTableInsert.

private boolean manageTableInsert(Connection con, DatabaseMetaData dmd, String tablename, HashMap<String, DatatableNewProperties> datatablenewproperties, HashMap<String, ArrayList> dbtables, HashMap<String, Object> dbvalues) {
    Statement stmt = null;
    try {
        TableFieldStructure tfs = getTableFieldsList(dmd, tablename, "");
        DatatableNewProperties dtnp = datatablenewproperties.get(tablename);
        StringBuilder sql_insert_fields = new StringBuilder();
        StringBuilder sql_insert_values = new StringBuilder();
        dtnp.getValuelist().stream().map((dtnv) -> {
            sql_insert_fields.append(dtnv.getField());
            return dtnv;
        }).forEach((dtnv) -> {
            sql_insert_fields.append(", ");
            String fieldType = getFieldType(tfs.getTableFieldsList(), dtnv.getField());
            if (null != fieldType) {
                if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                    sql_insert_values.append("'");
                }
                sql_insert_values.append(dtnv.getValue());
                if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                    sql_insert_values.append("'");
                }
                sql_insert_values.append(", ");
            }
        });
        sql_insert_fields.delete(sql_insert_fields.length() - 2, sql_insert_fields.length());
        sql_insert_values.delete(sql_insert_values.length() - 2, sql_insert_values.length());
        StringBuilder sql_insert = new StringBuilder();
        sql_insert.append("INSERT INTO ");
        sql_insert.append(tablename);
        sql_insert.append(" (");
        sql_insert.append(sql_insert_fields);
        sql_insert.append(") VALUES (");
        sql_insert.append(sql_insert_values);
        sql_insert.append(")");
        stmt = con.createStatement();
        int count = stmt.executeUpdate(sql_insert.toString());
        boolean ok = false;
        if (count > 0) {
            ok = true;
        }
        return ok;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return false;
    } finally {
        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        }
    }
}
Also used : DatatableNewProperties(io.clownfish.clownfish.jdbc.DatatableNewProperties) Connection(java.sql.Connection) Logger(org.slf4j.Logger) LoggerFactory(org.slf4j.LoggerFactory) CfDatasource(io.clownfish.clownfish.dbentities.CfDatasource) Autowired(org.springframework.beans.factory.annotation.Autowired) DatabaseMetaData(java.sql.DatabaseMetaData) HashMap(java.util.HashMap) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) ArrayList(java.util.ArrayList) DatatableUpdateProperties(io.clownfish.clownfish.jdbc.DatatableUpdateProperties) DatatableCondition(io.clownfish.clownfish.jdbc.DatatableCondition) DatatableProperties(io.clownfish.clownfish.jdbc.DatatableProperties) TableField(io.clownfish.clownfish.jdbc.TableField) SQLException(java.sql.SQLException) List(java.util.List) Component(org.springframework.stereotype.Component) JDBCUtil(io.clownfish.clownfish.jdbc.JDBCUtil) ResultSet(java.sql.ResultSet) CfDatasourceService(io.clownfish.clownfish.serviceinterface.CfDatasourceService) Statement(java.sql.Statement) DatatableNewProperties(io.clownfish.clownfish.jdbc.DatatableNewProperties) DatatableDeleteProperties(io.clownfish.clownfish.jdbc.DatatableDeleteProperties) CfSitedatasource(io.clownfish.clownfish.dbentities.CfSitedatasource) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) SQLException(java.sql.SQLException) Statement(java.sql.Statement)

Example 3 with TableFieldStructure

use of io.clownfish.clownfish.jdbc.TableFieldStructure in project Clownfish by rawdog71.

the class DatabaseUtil method manageTableDelete.

private boolean manageTableDelete(Connection con, DatabaseMetaData dmd, String tablename, HashMap<String, DatatableDeleteProperties> datatabledeleteproperties, HashMap<String, ArrayList> dbtables, HashMap<String, Object> dbvalues) {
    Statement stmt = null;
    try {
        TableFieldStructure tfs = getTableFieldsList(dmd, tablename, "");
        DatatableDeleteProperties dtdp = datatabledeleteproperties.get(tablename);
        StringBuilder sql_condition = new StringBuilder();
        if (dtdp != null) {
            sql_condition.append(" WHERE ");
            dtdp.getValuelist().stream().map((dtdv) -> {
                sql_condition.append("(");
                sql_condition.append(dtdv.getField());
                return dtdv;
            }).forEach((dtdv) -> {
                String fieldType = getFieldType(tfs.getTableFieldsList(), dtdv.getField());
                sql_condition.append(" = ");
                if (null != fieldType) {
                    if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                        sql_condition.append("'");
                    }
                    sql_condition.append(dtdv.getValue());
                    if ((fieldType.compareToIgnoreCase("string") == 0) || (fieldType.compareToIgnoreCase("date") == 0)) {
                        sql_condition.append("'");
                    }
                    sql_condition.append(") AND ");
                }
            });
            sql_condition.delete(sql_condition.length() - 4, sql_condition.length());
        }
        StringBuilder sql_delete = new StringBuilder();
        sql_delete.append("DELETE FROM ");
        sql_delete.append(tablename);
        sql_delete.append(sql_condition);
        stmt = con.createStatement();
        int count = stmt.executeUpdate(sql_delete.toString());
        boolean ok = false;
        if (count > 0) {
            ok = true;
        }
        return ok;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return false;
    } finally {
        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        }
    }
}
Also used : Connection(java.sql.Connection) Logger(org.slf4j.Logger) LoggerFactory(org.slf4j.LoggerFactory) CfDatasource(io.clownfish.clownfish.dbentities.CfDatasource) Autowired(org.springframework.beans.factory.annotation.Autowired) DatabaseMetaData(java.sql.DatabaseMetaData) HashMap(java.util.HashMap) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) ArrayList(java.util.ArrayList) DatatableUpdateProperties(io.clownfish.clownfish.jdbc.DatatableUpdateProperties) DatatableCondition(io.clownfish.clownfish.jdbc.DatatableCondition) DatatableProperties(io.clownfish.clownfish.jdbc.DatatableProperties) TableField(io.clownfish.clownfish.jdbc.TableField) SQLException(java.sql.SQLException) List(java.util.List) Component(org.springframework.stereotype.Component) JDBCUtil(io.clownfish.clownfish.jdbc.JDBCUtil) ResultSet(java.sql.ResultSet) CfDatasourceService(io.clownfish.clownfish.serviceinterface.CfDatasourceService) Statement(java.sql.Statement) DatatableNewProperties(io.clownfish.clownfish.jdbc.DatatableNewProperties) DatatableDeleteProperties(io.clownfish.clownfish.jdbc.DatatableDeleteProperties) CfSitedatasource(io.clownfish.clownfish.dbentities.CfSitedatasource) TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) DatatableDeleteProperties(io.clownfish.clownfish.jdbc.DatatableDeleteProperties) SQLException(java.sql.SQLException) Statement(java.sql.Statement)

Example 4 with TableFieldStructure

use of io.clownfish.clownfish.jdbc.TableFieldStructure in project Clownfish by rawdog71.

the class DatabaseTemplateBean method getTableFieldsList.

private TableFieldStructure getTableFieldsList(ResultSetMetaData dmd) {
    try {
        TableFieldStructure tfs = new TableFieldStructure();
        ArrayList<TableField> tableFieldsList = new ArrayList<>();
        int columncount = dmd.getColumnCount();
        for (int i = 1; i <= columncount; i++) {
            String columnName = dmd.getColumnName(i);
            int colomuntype = dmd.getColumnType(i);
            String colomuntypename = dmd.getColumnTypeName(i);
            int columnsize = dmd.getColumnDisplaySize(i);
            int decimaldigits = dmd.getPrecision(i);
            /*
                if (decimaldigits == null) {
                    decimaldigits = "0";
                }
                 */
            int isNullable = dmd.isNullable(i);
            // String is_autoIncrment = columns.getString("IS_AUTOINCREMENT");
            String is_autoIncrment = "";
            switch(colomuntype) {
                case // TEXT -> String
                -1:
                    tableFieldsList.add(new TableField(columnName, "STRING", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // varchar -> String
                1:
                    tableFieldsList.add(new TableField(columnName, "STRING", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // int
                2:
                    tableFieldsList.add(new TableField(columnName, "INT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // int
                4:
                    tableFieldsList.add(new TableField(columnName, "INT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // smallint
                5:
                    tableFieldsList.add(new TableField(columnName, "INT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // real
                7:
                    tableFieldsList.add(new TableField(columnName, "REAL", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // float
                8:
                    tableFieldsList.add(new TableField(columnName, "FLOAT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // varchar -> String
                12:
                    tableFieldsList.add(new TableField(columnName, "STRING", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // long
                -5:
                    tableFieldsList.add(new TableField(columnName, "LONG", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // bit
                -7:
                    tableFieldsList.add(new TableField(columnName, "BOOLEAN", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // text -> String
                2005:
                    tableFieldsList.add(new TableField(columnName, "STRING", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // Date
                93:
                    tableFieldsList.add(new TableField(columnName, "DATE", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
            }
        }
        tfs.setDefault_order("");
        tfs.setTableFieldsList(tableFieldsList);
        return tfs;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return null;
    }
}
Also used : TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) SQLException(java.sql.SQLException) ArrayList(java.util.ArrayList) TableField(io.clownfish.clownfish.jdbc.TableField)

Example 5 with TableFieldStructure

use of io.clownfish.clownfish.jdbc.TableFieldStructure in project Clownfish by rawdog71.

the class ImportTemplateBean method getTableFieldsList.

private TableFieldStructure getTableFieldsList(ResultSetMetaData dmd) {
    try {
        TableFieldStructure tfs = new TableFieldStructure();
        ArrayList<TableField> tableFieldsList = new ArrayList<>();
        int columncount = dmd.getColumnCount();
        for (int i = 1; i <= columncount; i++) {
            String columnName = dmd.getColumnName(i);
            int colomuntype = dmd.getColumnType(i);
            String colomuntypename = dmd.getColumnTypeName(i);
            int columnsize = dmd.getColumnDisplaySize(i);
            int decimaldigits = dmd.getPrecision(i);
            /*
                if (decimaldigits == null)
                {
                    decimaldigits = "0";
                }
                 */
            int isNullable = dmd.isNullable(i);
            // String is_autoIncrment = columns.getString("IS_AUTOINCREMENT");
            String is_autoIncrment = "";
            switch(colomuntype) {
                // varchar -> String
                case 1:
                case 12:
                case // text -> String
                2005:
                    tableFieldsList.add(new TableField(columnName, "STRING", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                // int
                case 2:
                case 4:
                case // smallint
                5:
                    tableFieldsList.add(new TableField(columnName, "INT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // real
                7:
                    tableFieldsList.add(new TableField(columnName, "REAL", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // float
                8:
                    tableFieldsList.add(new TableField(columnName, "FLOAT", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // long
                -5:
                    tableFieldsList.add(new TableField(columnName, "LONG", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // bit
                -7:
                    tableFieldsList.add(new TableField(columnName, "BOOLEAN", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
                case // Date
                93:
                    tableFieldsList.add(new TableField(columnName, "DATE", colomuntypename, false, columnsize, decimaldigits, String.valueOf(isNullable)));
                    break;
            }
        }
        tfs.setDefault_order("");
        tfs.setTableFieldsList(tableFieldsList);
        return tfs;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return null;
    }
}
Also used : TableFieldStructure(io.clownfish.clownfish.jdbc.TableFieldStructure) TableField(io.clownfish.clownfish.jdbc.TableField)

Aggregations

TableFieldStructure (io.clownfish.clownfish.jdbc.TableFieldStructure)9 SQLException (java.sql.SQLException)8 ArrayList (java.util.ArrayList)8 TableField (io.clownfish.clownfish.jdbc.TableField)7 ResultSet (java.sql.ResultSet)7 CfDatasource (io.clownfish.clownfish.dbentities.CfDatasource)6 JDBCUtil (io.clownfish.clownfish.jdbc.JDBCUtil)6 Connection (java.sql.Connection)6 Statement (java.sql.Statement)6 HashMap (java.util.HashMap)6 CfSitedatasource (io.clownfish.clownfish.dbentities.CfSitedatasource)5 DatatableCondition (io.clownfish.clownfish.jdbc.DatatableCondition)4 DatatableDeleteProperties (io.clownfish.clownfish.jdbc.DatatableDeleteProperties)4 DatatableNewProperties (io.clownfish.clownfish.jdbc.DatatableNewProperties)4 DatatableProperties (io.clownfish.clownfish.jdbc.DatatableProperties)4 DatatableUpdateProperties (io.clownfish.clownfish.jdbc.DatatableUpdateProperties)4 CfDatasourceService (io.clownfish.clownfish.serviceinterface.CfDatasourceService)4 DatabaseMetaData (java.sql.DatabaseMetaData)4 List (java.util.List)4 Logger (org.slf4j.Logger)4