Examples with TableField io.clownfish.clownfish.jdbc.TableField used on opensource projects

Example 1 with TableField

use of io.clownfish.clownfish.jdbc.TableField 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;
    }
}

Example 2 with TableField

use of io.clownfish.clownfish.jdbc.TableField 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;
    }
}

Example 3 with TableField

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

the class DatabaseUtil method getTableFieldsList.

private TableFieldStructure getTableFieldsList(DatabaseMetaData dmd, String tablename, String default_order) {
    try {
        TableFieldStructure tfs = new TableFieldStructure();
        List<String> pkList = new ArrayList<>();
        ResultSet resultSetPK = dmd.getPrimaryKeys(null, null, tablename);
        int counter = 0;
        while (resultSetPK.next()) {
            pkList.add(resultSetPK.getString("COLUMN_NAME"));
            if (counter == 0) {
                default_order = resultSetPK.getString("COLUMN_NAME");
            }
            counter++;
        }
        ArrayList<TableField> tableFieldsList = new ArrayList<>();
        ResultSet columns = dmd.getColumns(null, null, tablename, null);
        while (columns.next()) {
            String columnName = columns.getString("COLUMN_NAME");
            String datatype = columns.getString("DATA_TYPE");
            String colomuntypename = columns.getString("TYPE_NAME");
            String columnsize = columns.getString("COLUMN_SIZE");
            String decimaldigits = columns.getString("DECIMAL_DIGITS");
            if (decimaldigits == null) {
                decimaldigits = "0";
            }
            String isNullable = columns.getString("IS_NULLABLE");
            // String is_autoIncrment = columns.getString("IS_AUTOINCREMENT");
            String is_autoIncrment = "";
            if ((default_order.isEmpty()) && (counter == 0)) {
                default_order = columnName;
            }
            TableField tf;
            switch(datatype) {
                // varchar -> String
                case "1":
                case "12":
                case "2005":
                    tf = new TableField(columnName, "STRING", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                // int
                case "2":
                case "4":
                case "5":
                    tf = new TableField(columnName, "INT", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                case // real
                "7":
                    tf = new TableField(columnName, "REAL", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                case // float
                "8":
                    tf = new TableField(columnName, "FLOAT", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                case // long
                "-5":
                    tf = new TableField(columnName, "LONG", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                case // bit
                "-7":
                    tf = new TableField(columnName, "BOOLEAN", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
                case // Date
                "93":
                    tf = new TableField(columnName, "DATE", colomuntypename, pkList.contains(columnName), Integer.parseInt(columnsize), Integer.parseInt(decimaldigits), isNullable);
                    tableFieldsList.add(tf);
                    break;
            }
        }
        tfs.setDefault_order(default_order);
        tfs.setTableFieldsList(tableFieldsList);
        return tfs;
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
        return null;
    }
}

Example 4 with TableField

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

the class DatabaseUtil method manageTableRead.

private void manageTableRead(Connection con, DatabaseMetaData dmd, String tablename, HashMap<String, DatatableProperties> datatableproperties, HashMap<String, ArrayList> dbtables, HashMap<String, Object> dbvalues) {
    Statement stmt = null;
    ResultSet result = null;
    try {
        long low_limit = 1;
        long high_limit = 50;
        // System.out.println(con.getMetaData().getDriverName());
        String default_order = "";
        TableFieldStructure tfs = getTableFieldsList(dmd, tablename, default_order);
        default_order = tfs.getDefault_order();
        String default_direction = "ASC";
        DatatableProperties dtp = datatableproperties.get(tablename);
        if (dtp != null) {
            if (dtp.getOrderby() != null) {
                default_order = datatableproperties.get(tablename).getOrderby();
            }
            if (dtp.getOrderdir() != null) {
                default_direction = datatableproperties.get(tablename).getOrderdir();
            }
            low_limit = 1L + (long) ((dtp.getPage() - 1) * dtp.getPagination());
            if (con.getMetaData().getDriverName().contains("MS SQL")) {
                high_limit = (long) dtp.getPage() * (long) dtp.getPagination();
            }
            if (con.getMetaData().getDriverName().contains("MySQL")) {
                high_limit = dtp.getPagination();
            }
        }
        StringBuilder sql_count = new StringBuilder();
        StringBuilder sql_outer = new StringBuilder();
        StringBuilder sql_inner = new StringBuilder();
        if (con.getMetaData().getDriverName().contains("MS SQL")) {
            sql_outer.append("SELECT ");
            sql_inner.append("SELECT ");
            sql_count.append("SELECT COUNT(*) AS count FROM ");
            if ((dtp != null) && (!dtp.getGroupbylist().isEmpty())) {
                sql_outer.append("count(*) AS groupbycount, ");
                tfs.getTableFieldsList().stream().filter((tf) -> (dtp.getGroupbylist().contains(tf.getName()))).map((tf) -> {
                    sql_outer.append(tf.getName());
                    return tf;
                }).map((tf) -> {
                    sql_outer.append(", ");
                    sql_inner.append(tf.getName());
                    return tf;
                }).forEach((_item) -> {
                    sql_inner.append(", ");
                });
            } else {
                tfs.getTableFieldsList().stream().map((tf) -> {
                    sql_outer.append(tf.getName());
                    return tf;
                }).map((tf) -> {
                    sql_outer.append(", ");
                    sql_inner.append(tf.getName());
                    return tf;
                }).forEach((_item) -> {
                    sql_inner.append(", ");
                });
            }
            sql_count.append(tablename);
            sql_outer.delete(sql_outer.length() - 2, sql_outer.length());
            sql_outer.append(" FROM (");
            sql_inner.append("ROW_NUMBER() OVER (ORDER BY ");
            sql_inner.append(default_order);
            sql_inner.append(" ");
            sql_inner.append(default_direction);
            sql_inner.append(" ) AS rownumber FROM ");
            sql_inner.append(tablename);
            StringBuilder sql_condition = null;
            if (dtp != null) {
                sql_condition = buildCondition(dtp.getConditionlist(), tfs.getTableFieldsList());
            }
            sql_inner.append(sql_condition);
            sql_count.append(sql_condition);
            StringBuilder sql_groupby = null;
            if (dtp != null) {
                sql_groupby = buildGroupBy(dtp.getGroupbylist());
            }
            sql_inner.append(sql_groupby);
            if (dtp != null) {
                if (!dtp.getGroupbycount().isEmpty()) {
                    sql_inner.append(" ,");
                    sql_inner.append(dtp.getGroupbycount());
                }
            }
            sql_count.append(sql_groupby);
            sql_outer.append(sql_inner);
            sql_outer.append(") orderedselection WHERE rownumber between ");
            sql_outer.append(low_limit);
            sql_outer.append(" AND ");
            sql_outer.append(high_limit);
            sql_outer.append(sql_groupby);
        }
        if (con.getMetaData().getDriverName().contains("MySQL")) {
            sql_outer.append("SELECT ");
            sql_count.append("SELECT COUNT(*) AS count FROM ");
            if ((dtp != null) && (!dtp.getGroupbylist().isEmpty())) {
                sql_outer.append("count(*) AS groupbycount, ");
                tfs.getTableFieldsList().stream().filter((tf) -> (dtp.getGroupbylist().contains(tf.getName()))).map((tf) -> {
                    sql_outer.append(tf.getName());
                    return tf;
                }).forEach((_item) -> {
                    sql_outer.append(", ");
                });
            } else {
                tfs.getTableFieldsList().stream().map((tf) -> {
                    sql_outer.append(tf.getName());
                    return tf;
                }).forEach((_item) -> {
                    sql_outer.append(", ");
                });
            }
            sql_count.append(tablename);
            sql_outer.delete(sql_outer.length() - 2, sql_outer.length());
            sql_outer.append(" FROM ");
            sql_outer.append(tablename);
            StringBuilder sql_condition = null;
            if (dtp != null) {
                sql_condition = buildCondition(dtp.getConditionlist(), tfs.getTableFieldsList());
            }
            sql_outer.append(sql_condition);
            sql_count.append(sql_condition);
            StringBuilder sql_groupby = null;
            if (dtp != null) {
                sql_groupby = buildGroupBy(dtp.getGroupbylist());
            }
            sql_outer.append(sql_groupby);
            if (dtp != null) {
                if (!dtp.getGroupbycount().isEmpty()) {
                    sql_outer.append(" ,");
                    sql_outer.append(dtp.getGroupbycount());
                }
            }
            sql_count.append(sql_groupby);
            sql_outer.append(" LIMIT ");
            sql_outer.append(low_limit - 1);
            sql_outer.append(", ");
            sql_outer.append(high_limit);
            sql_outer.append(sql_groupby);
        }
        if (dtp != null) {
            if (!dtp.getGroupbycount().isEmpty()) {
                TableField groupbycountfield = new TableField();
                groupbycountfield.setName("groupbycount");
                groupbycountfield.setType("INT");
                tfs.getTableFieldsList().add(groupbycountfield);
            }
        }
        stmt = con.createStatement();
        result = stmt.executeQuery(sql_outer.toString());
        ArrayList<HashMap> tablevalues = new ArrayList<>();
        while (result.next()) {
            HashMap<String, String> dbexportvalues = new HashMap<>();
            for (TableField tf : tfs.getTableFieldsList()) {
                try {
                    String value = result.getString(tf.getName());
                    dbexportvalues.put(tf.getName(), value);
                } catch (java.sql.SQLException ex) {
                }
            }
            tablevalues.add(dbexportvalues);
        }
        dbtables.put(tablename, tablevalues);
        try {
            result.close();
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        result = stmt.executeQuery(sql_count.toString());
        HashMap<String, String> dbexportvalues = new HashMap<>();
        while (result.next()) {
            String value = result.getString("count");
            dbexportvalues.put("count", value);
        }
        dbvalues.put(tablename, dbexportvalues);
    } catch (SQLException ex) {
        LOGGER.error(ex.getMessage());
    } finally {
        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        }
        if (null != result) {
            try {
                result.close();
            } catch (SQLException ex) {
                LOGGER.error(ex.getMessage());
            }
        }
    }
}