Example usage for java.sql DatabaseMetaData getTables

List of usage examples for java.sql DatabaseMetaData getTables

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getTables

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getTables.

Prototype

ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
        throws SQLException;

Source Link

Document

Retrieves a description of the tables available in the given catalog.

Usage

From source file:it.fub.jardin.server.DbUtils.java

private List<ResultsetImproved> getCompleteResultsetImprovedList() throws HiddenException {

    List<ResultsetImproved> resultSetList = new ArrayList<ResultsetImproved>();
    Connection connection = this.dbConnectionHandler.getConn();

    // recupero i nomi delle view
    ArrayList<String> views = new ArrayList<String>();
    try {// w w  w . ja  v  a2  s.  c o  m
        ResultSet rs = null;
        DatabaseMetaData meta = connection.getMetaData();
        rs = meta.getTables(null, null, null, new String[] { "VIEW" });
        while (rs.next()) {
            views.add(rs.getString("TABLE_NAME"));
        }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

    String query = "SELECT res.statement as statement, r.id as resourceid, g.id AS groupid, res.id AS rsid, "
            + "f.id_resultset as resultsetid, " + "f.default_header as defaultheader, "
            + "f.search_grouping as searchgrouping, " + "f.id_grouping as idgrouping,"
            + "r.name as resourcename, " + "r.alias as resourcealias, m.readperm as readperm, "
            + "m.deleteperm as deleteperm, m.modifyperm as modifyperm, "
            + "m.insertperm as insertperm, f.defaultvalue as defaultvalue, f.type as type " + "FROM ((((("
            + T_USER + " u JOIN " + T_GROUP + " g ON (u.id_group=g.id)) " + "JOIN " + T_MANAGEMENT
            + " m ON (g.id = m.id_group)) " + "JOIN " + T_RESOURCE + " r ON r.id = m.id_resource) "
            + "LEFT JOIN " + T_RESULTSET + " res ON res.id=r.id) LEFT JOIN " + T_FIELD + " f ON  (r.id=f.id))"
            + " WHERE readperm = '1' " + " ORDER BY r.id ASC";
    try {

        List<ResultsetField> resultFieldList = new ArrayList<ResultsetField>();
        List<BaseModelData> PKs = null;
        ArrayList<String> UKs = new ArrayList<String>();

        ResultSet result = doQuery(connection, query);
        while (result.next()) {
            String statement = result.getString("statement");
            Integer id = Integer.valueOf(result.getInt("resourceid"));
            Integer resultsetid = Integer.valueOf(result.getInt("resultsetid"));
            boolean defaultheader = result.getBoolean("defaultheader");
            Integer searchgrouping = Integer.valueOf(result.getInt("searchgrouping"));
            Integer idgrouping = Integer.valueOf(result.getInt("idgrouping"));
            String name = result.getString("resourcename");
            String alias = result.getString("resourcealias");
            boolean readperm = result.getBoolean("readperm");
            boolean deleteperm = result.getBoolean("deleteperm");
            boolean modifyperm = result.getBoolean("modifyperm");
            boolean insertperm = result.getBoolean("insertperm");
            Integer rsid = Integer.valueOf(result.getInt("rsid"));
            Integer groupid = Integer.valueOf(result.getInt("groupid"));

            if (statement != null) {
                /* Gestione di un RESULTSET */

                ArrayList<Tool> tools = this.getToolbar(rsid, groupid);

                ResultsetImproved res = null;
                if (views.contains(name)) {
                    // System.out.println(name + "  una view");
                    res = new ResultsetImproved(id, name, alias, statement, readperm, false, false, false,
                            tools);
                } else {
                    res = new ResultsetImproved(id, name, alias, statement, readperm, deleteperm, modifyperm,
                            insertperm, tools);
                }
                resultSetList.add(res);

                List<BaseModelData> groupings = this.getReGroupings(id);
                for (BaseModelData grouping : groupings) {
                    ResultsetFieldGroupings rfg = new ResultsetFieldGroupings((Integer) grouping.get("id"),
                            (String) grouping.get("name"), (String) grouping.get("alias"));
                    res.addFieldGroupings(rfg);
                }

                PKs = this.dbProperties.getPrimaryKeys(name);
                UKs = this.dbProperties.getUniqueKeys(name);

            } else {
                /* Gestione di un CAMPO di un resultset */

                boolean visible = result.getInt("defaultheader") == 1;

                ResultsetField resField = new ResultsetField(id, name, alias, resultsetid, defaultheader,
                        searchgrouping, idgrouping, readperm, deleteperm, modifyperm, insertperm, visible);

                resField.setType(result.getString("type"));
                resField.setDefaultValue(result.getString("defaultvalue"));
                resField.setIsPK(false);
                resField.setUnique(false);

                resultFieldList.add(resField);

                if (PKs != null) {
                    for (BaseModelData pk : PKs) {
                        if (((String) pk.get("PK_NAME")).compareToIgnoreCase(name) == 0) {
                            resField.setIsPK(true);
                        }
                    }
                }

                if (UKs != null) {
                    if (UKs.contains(name)) {
                        resField.setUnique(true);
                    }
                }

            }
        }

        PKs = null;

        for (int i = 0; i < resultSetList.size(); i++) {
            for (int j = 0; j < resultFieldList.size(); j++) {
                if (resultFieldList.get(j).getResultsetid() == resultSetList.get(i).getId()) {

                    // aggiunta dell'eventuale foreignKEY
                    resultFieldList.get(j).setForeignKey(this.dbProperties
                            .getForeignKey(resultSetList.get(i).getName(), resultFieldList.get(j).getName()));
                    resultSetList.get(i).addField(resultFieldList.get(j));
                }
            }

            // aggiunta delle eventuali foreignKEY entranti
            resultSetList.get(i).setForeignKeyIn(
                    this.getForeignKeyInForATable(resultSetList.get(i).getId(), resultSetList));
        }
    } catch (SQLException e) {
        // Log.warn("Errore SQL", e);
        throw new HiddenException("Errore durante il recupero delle viste su database");
    } finally {
        this.dbConnectionHandler.closeConn(connection);
    }

    return resultSetList;
}

From source file:it.fub.jardin.server.DbUtils.java

/**
 * @param userId//from   w  ww.j  av a 2  s  . c o m
 * @return resultSetList
 * 
 *         Ritorna i resultset (id, alias e statement SQL) per i quali
 *         l'utente passato come parametro ha permesso 'read' uguale a 1
 * @throws HiddenException
 */
public List<ResultsetImproved> getUserResultsetImproved(final Integer uid, final Integer gid)
        throws HiddenException {

    List<ResultsetImproved> resultSetList = new ArrayList<ResultsetImproved>();
    Connection connection = this.dbConnectionHandler.getConn();

    // recupero i nomi delle view
    ArrayList<String> views = new ArrayList<String>();
    try {
        ResultSet rs = null;
        DatabaseMetaData meta = connection.getMetaData();
        rs = meta.getTables(null, null, null, new String[] { "VIEW" });
        while (rs.next()) {
            views.add(rs.getString("TABLE_NAME"));
        }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

    String query = "SELECT res.statement as statement, r.id as resourceid, g.id AS groupid, res.id AS rsid, "
            + "f.id_resultset as resultsetid, " + "f.default_header as defaultheader, "
            + "f.search_grouping as searchgrouping, " + "f.id_grouping as idgrouping,"
            + "r.name as resourcename, " + "r.alias as resourcealias, m.readperm as readperm, "
            + "m.deleteperm as deleteperm, m.modifyperm as modifyperm, "
            + "m.insertperm as insertperm, f.defaultvalue as defaultvalue, f.type as type " + "FROM ((((("
            + T_USER + " u JOIN " + T_GROUP + " g ON (u.id_group=g.id)) " + "JOIN " + T_MANAGEMENT
            + " m ON (g.id = m.id_group)) " + "JOIN " + T_RESOURCE + " r ON r.id = m.id_resource) "
            + "LEFT JOIN " + T_RESULTSET + " res ON res.id=r.id) LEFT JOIN " + T_FIELD + " f ON  (r.id=f.id))"
            + " WHERE u.id = '" + uid + "' AND readperm = '1' " + " ORDER BY r.id ASC";
    try {

        List<ResultsetField> resultFieldList = new ArrayList<ResultsetField>();
        List<BaseModelData> PKs = null;
        ArrayList<String> UKs = new ArrayList<String>();

        ResultSet result = doQuery(connection, query);
        while (result.next()) {
            String statement = result.getString("statement");
            Integer id = Integer.valueOf(result.getInt("resourceid"));
            Integer resultsetid = Integer.valueOf(result.getInt("resultsetid"));
            boolean defaultheader = result.getBoolean("defaultheader");
            Integer searchgrouping = Integer.valueOf(result.getInt("searchgrouping"));
            Integer idgrouping = Integer.valueOf(result.getInt("idgrouping"));
            String name = result.getString("resourcename");
            String alias = result.getString("resourcealias");
            boolean readperm = result.getBoolean("readperm");
            boolean deleteperm = result.getBoolean("deleteperm");
            boolean modifyperm = result.getBoolean("modifyperm");
            boolean insertperm = result.getBoolean("insertperm");
            Integer rsid = Integer.valueOf(result.getInt("rsid"));
            Integer groupid = Integer.valueOf(result.getInt("groupid"));

            if (statement != null) {
                /* Gestione di un RESULTSET */

                ArrayList<Tool> tools = this.getToolbar(rsid, groupid);

                ResultsetImproved res = null;
                if (views.contains(name)) {
                    // System.out.println(name + "  una view");
                    res = new ResultsetImproved(id, name, alias, statement, readperm, false, false, false,
                            tools);
                } else {
                    res = new ResultsetImproved(id, name, alias, statement, readperm, deleteperm, modifyperm,
                            insertperm, tools);
                }
                resultSetList.add(res);

                List<BaseModelData> groupings = this.getReGroupings(id);
                for (BaseModelData grouping : groupings) {
                    ResultsetFieldGroupings rfg = new ResultsetFieldGroupings((Integer) grouping.get("id"),
                            (String) grouping.get("name"), (String) grouping.get("alias"));
                    res.addFieldGroupings(rfg);
                }

                PKs = this.dbProperties.getPrimaryKeys(name);
                UKs = this.dbProperties.getUniqueKeys(name);

            } else {
                /* Gestione di un CAMPO di un resultset */

                boolean visible = result.getInt("defaultheader") == 1;

                ResultsetField resField = new ResultsetField(id, name, alias, resultsetid, defaultheader,
                        searchgrouping, idgrouping, readperm, deleteperm, modifyperm, insertperm, visible);

                resField.setType(result.getString("type"));
                resField.setDefaultValue(result.getString("defaultvalue"));
                resField.setIsPK(false);
                resField.setUnique(false);

                resultFieldList.add(resField);

                if (PKs != null) {
                    for (BaseModelData pk : PKs) {
                        if (((String) pk.get("PK_NAME")).compareToIgnoreCase(name) == 0) {
                            resField.setIsPK(true);
                        }
                    }
                }

                if (UKs != null) {
                    if (UKs.contains(name)) {
                        resField.setUnique(true);
                    }
                }

            }
        }

        PKs = null;

        for (int i = 0; i < resultSetList.size(); i++) {
            for (int j = 0; j < resultFieldList.size(); j++) {
                if (resultFieldList.get(j).getResultsetid() == resultSetList.get(i).getId()) {

                    // aggiunta dell'eventuale foreignKEY
                    resultFieldList.get(j).setForeignKey(this.dbProperties
                            .getForeignKey(resultSetList.get(i).getName(), resultFieldList.get(j).getName()));
                    resultSetList.get(i).addField(resultFieldList.get(j));
                }
            }

            // aggiunta delle eventuali foreignKEY entranti
            resultSetList.get(i).setForeignKeyIn(
                    this.getForeignKeyInForATable(resultSetList.get(i).getId(), resultSetList));
        }
    } catch (SQLException e) {
        // Log.warn("Errore SQL", e);
        throw new HiddenException("Errore durante il recupero delle viste su database");
    } finally {
        this.dbConnectionHandler.closeConn(connection);
    }

    return resultSetList;
}

From source file:jef.database.DbMetaData.java

/**
 * ?schema???/*from   w  w w .j a  v a  2s .  c o  m*/
 * 
 * @param types
 *            ??{@link ObjectType}????Table
 * @return ??
 * @throws SQLException
 */
public List<String> getTableNames(ObjectType... types) throws SQLException {
    if (types == null || types.length == 0) {
        types = new ObjectType[] { ObjectType.TABLE };
    }
    Connection conn = getConnection(false);
    DatabaseMetaData databaseMetaData = conn.getMetaData();
    DatabaseDialect trans = info.profile;
    String[] ts = new String[types.length];
    for (int i = 0; i < types.length; i++) {
        ts[i] = types[i].name();
    }
    ResultSet rs = databaseMetaData.getTables(trans.getCatlog(schema), trans.getSchema(schema), null, ts);
    try {
        List<String> result = new ArrayList<String>();
        while (rs.next()) {
            result.add(rs.getString("TABLE_NAME"));
        }
        return result;
    } finally {
        DbUtils.close(rs);
        releaseConnection(conn);
    }
}

From source file:it.fub.jardin.server.DbUtils.java

public ResultsetImproved getResultsetImproved(int resultsetId, int gid) throws HiddenException {
    // TODO Auto-generated method stub
    Connection connection = this.dbConnectionHandler.getConn();
    ResultsetImproved res = null;//from  w  ww. j  av  a 2  s. co m
    String resultsetName = null;

    // recupero i nomi delle view
    ArrayList<String> views = new ArrayList<String>();
    try {
        ResultSet rs = null;
        DatabaseMetaData meta = connection.getMetaData();
        rs = meta.getTables(null, null, null, new String[] { "VIEW" });
        while (rs.next()) {
            views.add(rs.getString("TABLE_NAME"));
        }
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

    // getFieldsForResultset(int resultsetId, int gid)
    // getPluginsForResultset(int resultsetId, int gid)
    // getForeignKeyForResultset(int resultsetId, int gid)
    // getForeignKeyINforResultset(int resultsetId, int gid)
    // getToolsResultset(int resultsetId, int gid)

    try {
        String queryResultset = "SELECT res.statement as statement, " + "r.id as resourceid, "
                + "g.id AS groupid, " + "r.name as resourcename, " + "r.alias as resourcealias, "
                + "m.readperm as readperm, " + "m.deleteperm as deleteperm, " + "m.modifyperm as modifyperm, "
                + "m.insertperm as insertperm " + "FROM " + T_GROUP + " g JOIN " + T_MANAGEMENT
                + " m ON g.id = m.id_group " + "JOIN " + T_RESOURCE + " r ON r.id = m.id_resource "
                + "LEFT JOIN " + T_RESULTSET + " res ON res.id=r.id " + " WHERE r.id = '" + resultsetId
                + "' AND g.id = '" + gid + "'";

        // JardinLogger.debug("costruzione resultset:" + queryResultset);

        List<ResultsetField> resultFieldList = new ArrayList<ResultsetField>();
        List<BaseModelData> PKs = null;
        ArrayList<String> UKs = new ArrayList<String>();

        ResultSet result = doQuery(connection, queryResultset);

        if (result.next()) {
            // System.out.println("RESULT SET RECUPERATO");
            String statement = result.getString("statement");
            Integer id = Integer.valueOf(result.getInt("resourceid"));
            resultsetName = result.getString("resourcename");
            String alias = result.getString("resourcealias");
            boolean readperm = result.getBoolean("readperm");
            boolean deleteperm = result.getBoolean("deleteperm");
            boolean modifyperm = result.getBoolean("modifyperm");
            boolean insertperm = result.getBoolean("insertperm");
            Integer groupid = Integer.valueOf(result.getInt("groupid"));

            /* Gestione di un RESULTSET */

            ArrayList<Tool> tools = this.getToolbar(id, groupid);

            if (views.contains(resultsetName)) {
                // System.out.println(name + "  una view");
                res = new ResultsetImproved(id, resultsetName, alias, statement, readperm, false, false, false,
                        tools);
            } else {
                // System.out.println(name + "  una tabella");
                res = new ResultsetImproved(id, resultsetName, alias, statement, readperm, deleteperm,
                        modifyperm, insertperm, tools);
            }

            // System.out.println("STATEMENT:" + res.getStatement());
            List<BaseModelData> groupings = this.getReGroupings(id);
            for (BaseModelData grouping : groupings) {
                ResultsetFieldGroupings rfg = new ResultsetFieldGroupings((Integer) grouping.get("id"),
                        (String) grouping.get("name"), (String) grouping.get("alias"));
                res.addFieldGroupings(rfg);
                // JardinLogger.debug("aggiunto raggruppamento: " + rfg.getId()
                // + rfg.getAlias());
            }

            PKs = this.dbProperties.getPrimaryKeys(resultsetName);
            UKs = this.dbProperties.getUniqueKeys(resultsetName);
        }

        String filedsQuery = "SELECT res.*, m.readperm, m.deleteperm, m.modifyperm,"
                + "m.insertperm, f.default_header , f.search_grouping,"
                + "f.id_grouping, f.type, f.defaultvalue " + "FROM `__system_resource` res join "
                + "__system_field f on res.id=f.id  join "
                + "__system_management m on res.id=m.id_resource join "
                + "__system_group g on g.id=m.id_group  " + "WHERE f.id_resultset=" + resultsetId + " and g.id="
                + gid;

        // JardinLogger.debug("costruzione campi:" + filedsQuery);
        ResultSet resultFields = doQuery(connection, filedsQuery);

        /* Gestione di un CAMPO di un resultset */
        while (resultFields.next()) {
            boolean visible = resultFields.getInt("default_header") == 1;

            ResultsetField resField = new ResultsetField(resultFields.getInt("id"),
                    resultFields.getString("name"), resultFields.getString("alias"), resultsetId,
                    resultFields.getBoolean("default_header"), resultFields.getInt("search_grouping"),
                    resultFields.getInt("id_grouping"), resultFields.getBoolean("readperm"),
                    resultFields.getBoolean("deleteperm"), resultFields.getBoolean("modifyperm"),
                    resultFields.getBoolean("insertperm"), visible);

            resField.setType(resultFields.getString("type"));
            resField.setDefaultValue(resultFields.getString("defaultvalue"));
            resField.setIsPK(false);
            resField.setUnique(false);
            resField.setForeignKey(dbProperties.getForeignKey(resultsetName, resField.getName()));

            resultFieldList.add(resField);

            res.addField(resField);
            // System.out.println("aggiunto campo " + resField.getName() + " la rs "
            // + res.getName());

            if (PKs != null) {
                for (BaseModelData pk : PKs) {
                    if (((String) pk.get("PK_NAME")).compareToIgnoreCase(resultFields.getString("name")) == 0) {
                        resField.setIsPK(true);
                    }
                }
            }

            if (UKs != null) {
                if (UKs.contains(resultFields.getString("name"))) {
                    resField.setUnique(true);
                }
            }
        }

        // FOREIGNEKEY

        // PKs = null;

        // aggiunta delle eventuali foreignKEY entranti
        // res.setForeignKeyIn(getForeignKeyInForATable(resultsetId));
        res.setForeignKeyIn(getForeignKeyInForATable(gid, res.getStatement()));
        // }
    } catch (SQLException e) {
        // Log.warn("Errore SQL", e);
        throw new HiddenException("Errore durante il recupero delle viste su database");
    } finally {
        this.dbConnectionHandler.closeConn(connection);
    }

    return res;

}

From source file:org.ramadda.repository.database.DatabaseManager.java

/**
 * Actually write the tables//from w  w w  . j a  v a2  s. c om
 *
 * @param pw What to write to
 * @param packageName Tables.java package name
 * @param what _more_
 *
 * @throws Exception on badness
 */

private void writeTables(PrintWriter pw, String packageName, String[] what) throws Exception {

    String sp1 = "    ";
    String sp2 = sp1 + sp1;
    String sp3 = sp1 + sp1 + sp1;

    pw.append(
            "/**Generated by running: java org.unavco.projects.gsac.repository.UnavcoGsacDatabaseManager**/\n\n");
    pw.append("package " + packageName + ";\n\n");
    pw.append("import org.ramadda.sql.SqlUtil;\n\n");
    pw.append("//J-\n");
    pw.append("public abstract class Tables {\n");
    pw.append(sp1 + "public abstract String getName();\n");
    pw.append(sp1 + "public abstract String getColumns();\n");
    Connection connection = getConnection();
    DatabaseMetaData dbmd = connection.getMetaData();
    ResultSet catalogs = dbmd.getCatalogs();
    ResultSet tables = dbmd.getTables(null, null, null, what);

    HashSet seenTables = new HashSet();
    while (tables.next()) {
        String tableName = tables.getString("TABLE_NAME");
        //            System.err.println ("NAME:" + tableName);
        String TABLENAME = tableName.toUpperCase();
        if (seenTables.contains(TABLENAME)) {
            continue;
        }
        seenTables.add(TABLENAME);
        String tableType = tables.getString("TABLE_TYPE");
        if (Misc.equals(tableType, "INDEX")) {
            continue;
        }
        if (tableName.indexOf("$") >= 0) {
            continue;
        }

        if (tableType == null) {
            continue;
        }

        if ((tableType != null) && tableType.startsWith("SYSTEM")) {
            continue;
        }

        ResultSet columns = dbmd.getColumns(null, null, tableName, null);

        List colNames = new ArrayList();
        pw.append("\n\n");
        pw.append(sp1 + "public static class " + TABLENAME + " extends Tables {\n");

        pw.append(sp2 + "public static final String NAME = \"" + tableName.toLowerCase() + "\";\n");
        pw.append("\n");
        pw.append(sp2 + "public String getName() {return NAME;}\n");
        pw.append(sp2 + "public String getColumns() {return COLUMNS;}\n");
        System.out.println("processing table:" + TABLENAME);

        String tableVar = null;
        List colVars = new ArrayList();
        HashSet seen = new HashSet();
        while (columns.next()) {
            String colName = columns.getString("COLUMN_NAME").toLowerCase();
            String colSize = columns.getString("COLUMN_SIZE");
            String COLNAME = colName.toUpperCase();
            if (seen.contains(COLNAME)) {
                continue;
            }
            seen.add(COLNAME);
            COLNAME = COLNAME.replace("#", "");
            colNames.add("COL_" + COLNAME);
            pw.append(sp2 + "public static final String COL_" + COLNAME + " =  NAME + \"." + colName + "\";\n");

            pw.append(sp2 + "public static final String COL_NODOT_" + COLNAME + " =   \"" + colName + "\";\n");
            /*
            pw.append(sp2 + "public static final String ORA_" + COLNAME
                  + " =  \"" + colName + "\";\n");
            */
        }

        pw.append("\n");
        pw.append(sp2 + "public static final String[] ARRAY = new String[] {\n");
        pw.append(sp3 + StringUtil.join(",", colNames));
        pw.append("\n");
        pw.append(sp2 + "};\n");
        pw.append(sp2 + "public static final String COLUMNS = SqlUtil.comma(ARRAY);\n");
        pw.append(sp2 + "public static final String NODOT_COLUMNS = SqlUtil.commaNoDot(ARRAY);\n");

        pw.append(sp2 + "public static final String INSERT =" + "SqlUtil.makeInsert(NAME, NODOT_COLUMNS,"
                + "SqlUtil.getQuestionMarks(ARRAY.length));\n");

        pw.append(sp1 + "public static final " + TABLENAME + " table  = new  " + TABLENAME + "();\n");
        pw.append(sp1 + "}\n\n");

    }

    pw.append("\n\n}\n");

}

From source file:org.ramadda.repository.database.DatabaseManager.java

/**
 * _more_//from  w w  w  . j  av  a 2 s  .  c o  m
 *
 * @param os _more_
 * @param all _more_
 *
 * @throws Exception _more_
 */
public void makeDatabaseCopyxxx(OutputStream os, boolean all) throws Exception {

    Connection connection = getConnection();
    try {
        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet catalogs = dbmd.getCatalogs();
        ResultSet tables = dbmd.getTables(null, null, null, new String[] { "TABLE" });

        ResultSetMetaData rsmd = tables.getMetaData();
        for (int col = 1; col <= rsmd.getColumnCount(); col++) {
            System.err.println(rsmd.getColumnName(col));
        }
        int totalRowCnt = 0;
        while (tables.next()) {
            //                String tableName = tables.getString("Tables.NAME.NAME");
            //                String tableType = tables.getString("Tables.TYPE.NAME");
            String tableName = tables.getString("TABLE_NAME");
            String tableType = tables.getString("TABLE_TYPE");
            if ((tableType == null) || Misc.equals(tableType, "INDEX") || tableType.startsWith("SYSTEM")) {
                continue;
            }

            String tn = tableName.toLowerCase();
            if (!all) {
                if (tn.equals(Tables.GLOBALS.NAME) || tn.equals(Tables.USERS.NAME)
                        || tn.equals(Tables.PERMISSIONS.NAME) || tn.equals(Tables.HARVESTERS.NAME)
                        || tn.equals(Tables.USERROLES.NAME)) {
                    continue;
                }
            }

            ResultSet cols = dbmd.getColumns(null, null, tableName, null);

            int colCnt = 0;

            String colNames = null;
            List types = new ArrayList();
            while (cols.next()) {
                String colName = cols.getString("COLUMN_NAME");
                if (colNames == null) {
                    colNames = " (";
                } else {
                    colNames += ",";
                }
                colNames += colName;
                int type = cols.getInt("DATA_TYPE");
                types.add(type);
                colCnt++;
            }
            colNames += ") ";

            Statement statement = execute("select * from " + tableName, 10000000, 0);
            SqlUtil.Iterator iter = getIterator(statement);
            ResultSet results;
            int rowCnt = 0;
            List valueList = new ArrayList();
            boolean didDelete = false;
            while ((results = iter.getNext()) != null) {
                if (!didDelete) {
                    didDelete = true;
                    IOUtil.write(os, "delete from  " + tableName.toLowerCase() + ";\n");
                }
                totalRowCnt++;
                rowCnt++;
                StringBuffer value = new StringBuffer("(");
                for (int i = 1; i <= colCnt; i++) {
                    int type = ((Integer) types.get(i - 1)).intValue();
                    if (i > 1) {
                        value.append(",");
                    }
                    if (type == java.sql.Types.TIMESTAMP) {
                        Timestamp ts = results.getTimestamp(i);
                        //                            sb.append(SqlUtil.format(new Date(ts.getTime())));
                        if (ts == null) {
                            value.append("null");
                        } else {
                            value.append(HtmlUtils.squote(ts.toString()));
                        }

                    } else if (type == java.sql.Types.VARCHAR) {
                        String s = results.getString(i);
                        if (s != null) {
                            //If the target isn't mysql:
                            //s = s.replace("'", "''");
                            //If the target is mysql:
                            s = s.replace("'", "\\'");
                            s = s.replace("\r", "\\r");
                            s = s.replace("\n", "\\n");
                            value.append("'" + s + "'");
                        } else {
                            value.append("null");
                        }
                    } else {
                        String s = results.getString(i);
                        value.append(s);
                    }
                }
                value.append(")");
                valueList.add(value.toString());
                if (valueList.size() > 50) {
                    IOUtil.write(os, "insert into " + tableName.toLowerCase() + colNames + " values ");
                    IOUtil.write(os, StringUtil.join(",", valueList));
                    IOUtil.write(os, ";\n");
                    valueList = new ArrayList();
                }
            }
            if (valueList.size() > 0) {
                if (!didDelete) {
                    didDelete = true;
                    IOUtil.write(os, "delete from  " + tableName.toLowerCase() + ";\n");
                }
                IOUtil.write(os, "insert into " + tableName.toLowerCase() + colNames + " values ");
                IOUtil.write(os, StringUtil.join(",", valueList));
                IOUtil.write(os, ";\n");
            }
        }
    } finally {
        closeConnection(connection);
    }

}

From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java

public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern)
        throws IOException, SQLException {
    Map dataTypesMap = prepareJdbcTypeInfoMap();
    DatabaseMetaData dbmd = conn.getMetaData();
    TextUtils textUtils = TextUtils.getInstance();

    writer.write("<?xml version=\"1.0\"?>\n\n");
    writer.write("<!-- Reverse engineered by Axiom\n");
    writer.write("     driver: " + dbmd.getDriverName() + "\n");
    writer.write("     driver-version: " + dbmd.getDriverVersion() + "\n");
    writer.write("     product: " + dbmd.getDatabaseProductName() + "\n");
    writer.write("     product-version: " + dbmd.getDatabaseProductVersion() + "\n");

    writer.write("     available catalogs:");
    ResultSet rs = null;//  ww w  .  ja v a 2 s  . c  om
    try {
        rs = dbmd.getCatalogs();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }

    writer.write("\n");

    writer.write("     available schemas:");
    try {
        rs = dbmd.getSchemas();
        while (rs.next()) {
            writer.write(" " + rs.getObject(1).toString());
        }
    } finally {
        if (rs != null)
            rs.close();
    }
    writer.write("\n");
    writer.write("-->\n\n");

    writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n");
    writer.write("    <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n");
    writer.write("    <schema name=\"" + catalog + "." + schemaPattern + "\">\n");

    Map dbmdTypeInfoByName = new HashMap();
    Map dbmdTypeInfoByJdbcType = new HashMap();
    ResultSet typesRS = null;
    try {
        typesRS = dbmd.getTypeInfo();
        while (typesRS.next()) {
            int colCount = typesRS.getMetaData().getColumnCount();
            Object[] typeInfo = new Object[colCount];
            for (int i = 1; i <= colCount; i++)
                typeInfo[i - 1] = typesRS.getObject(i);
            dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo);
            dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo);
        }
    } finally {
        if (typesRS != null)
            typesRS.close();
    }

    ResultSet tables = null;
    try {
        tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" });
        while (tables.next()) {
            String tableNameOrig = tables.getString(3);
            String tableName = textUtils.fixupTableNameCase(tableNameOrig);

            writer.write("        <table name=\"" + tableName + "\">\n");

            Map primaryKeys = new HashMap();
            ResultSet pkRS = null;
            try {
                pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig);
                while (pkRS.next()) {
                    primaryKeys.put(pkRS.getString(4), pkRS.getString(5));
                }

            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (pkRS != null)
                    pkRS.close();
            }

            Map fKeys = new HashMap();
            ResultSet fkRS = null;
            try {
                fkRS = dbmd.getImportedKeys(null, null, tableNameOrig);
                while (fkRS.next()) {
                    fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "."
                            + fkRS.getString(4).toLowerCase());
                }
            } catch (Exception e) {
                // driver may not support this function
            } finally {
                if (fkRS != null)
                    fkRS.close();
            }

            // we keep track of processed columns so we don't duplicate them in the XML
            Set processedColsMap = new HashSet();
            ResultSet columns = null;
            try {
                columns = dbmd.getColumns(null, null, tableNameOrig, null);
                while (columns.next()) {
                    String columnNameOrig = columns.getString(4);
                    if (processedColsMap.contains(columnNameOrig))
                        continue;
                    processedColsMap.add(columnNameOrig);

                    String columnName = columnNameOrig.toLowerCase();

                    writer.write("            <column name=\"" + columnName + "\"");
                    try {
                        if (fKeys.containsKey(columnNameOrig))
                            writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\"");
                        else {
                            short jdbcType = columns.getShort(5);
                            String dataType = (String) dataTypesMap.get(new Integer(jdbcType));
                            if (dataType == null)
                                dataType = Short.toString(jdbcType);
                            writer.write(" type=\"" + dataType + "\"");
                        }

                        if (primaryKeys.containsKey(columnNameOrig))
                            writer.write(" primary-key=\"yes\"");

                        if (columns.getString(18).equals("NO"))
                            writer.write(" required=\"yes\"");

                        String defaultValue = columns.getString(13);
                        if (defaultValue != null)
                            writer.write(" default=\"" + defaultValue + "\"");

                        String remarks = columns.getString(12);
                        if (remarks != null)
                            writer.write(" descr=\"" + remarks + "\"");

                    } catch (Exception e) {
                    }

                    writer.write("/>\n");
                }
            } finally {
                if (columns != null)
                    columns.close();
            }

            writer.write("        </table>\n");
        }
    } finally {
        tables.close();
    }

    writer.write("    </schema>\n");
    writer.write("</component>");
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testParentReferences() throws Exception {
    /* Test parent references from Statement */
    Statement s = this.con.createStatement();
    ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName);

    assertTrue(s.getConnection() == this.con);
    assertTrue(rs.getStatement() == s);//from   w w  w  .  j a v a  2  s  . co m

    rs.close();
    s.close();

    /* Test parent references from PreparedStatement */
    PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName);
    rs = ps.executeQuery();

    assertTrue(ps.getConnection() == this.con);
    assertTrue(rs.getStatement() == ps);

    rs.close();
    ps.close();

    /* Test DatabaseMetaData queries which do not have a parent Statement */
    DatabaseMetaData md = this.con.getMetaData();

    assertTrue(md.getConnection() == this.con);

    rs = md.getCatalogs();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getColumns(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getFunctions(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getImportedKeys(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getPrimaryKeys(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getProcedureColumns(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getProcedures(null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getSchemas();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTableTypes();
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTables(null, null, null, null);
    assertNull(rs.getStatement());
    rs.close();

    rs = md.getTypeInfo();
    assertNull(rs.getStatement());
    rs.close();
}

From source file:com.pari.nm.utils.db.ReportDBHelper.java

public static boolean checkTempTableExists(String reportId, String sessionId) throws Exception {
    String tblName = getTempPaginationTblName(reportId, sessionId);
    Connection c = null;//from ww w. ja  v  a 2  s  .c  om
    ResultSet rs = null;
    ResultSet tables = null;
    if (tblName == null) {
        throw new Exception("Unable to get Temp Pagination Table Name:");
    }
    // String query = "SELECT * FROM  " + tblName;
    boolean exists = false;
    try {
        // Use table metadata to check if tables exists in db schema.
        // Relying on exception, which is earlier implementation, is not a good idea.
        // Also it used to print exception stack trace in logs every time DBHelper throws exception, i.e. if table
        // doesn't exist
        c = DBHelper.getConnection();
        if (isPostgres()) {
            String query = "select count(*) from " + tblName;
            rs = DBHelper.executeQuery(query);
            if (rs.next()) {
                exists = true;
            }
        } else {
            DatabaseMetaData dbm = c.getMetaData();
            String[] tableType = { "TABLE", "VIEW" };
            tables = dbm.getTables(null, null, tblName, tableType);
            if (tables.next()) {
                exists = true;
            }
        }
        // rs = DBHelper.executeQuery(query);
        // exists = true;
    } catch (Exception e) {
        logger.error("Temp Table :" + tblName + " does not exist");
        // exists = false;
    } finally {
        try {
            if (tables != null) {
                tables.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex2) {
            logger.debug("Error while closing the result set.", ex2);
        }
        DBHelper.releaseConnection(c);
    }
    return exists;

}

From source file:org.apache.tajo.catalog.store.XMLCatalogSchemaManager.java

protected boolean checkExistence(Connection conn, DatabaseObjectType type, String... params)
        throws SQLException {
    boolean result = false;
    DatabaseMetaData metadata = null;
    PreparedStatement pstmt = null;
    BaseSchema baseSchema = catalogStore.getSchema();

    if (params == null || params.length < 1) {
        throw new IllegalArgumentException("checkExistence function needs at least one argument.");
    }/*from w  ww.  jav a 2  s  . c o m*/

    switch (type) {
    case DATA:
        metadata = conn.getMetaData();
        ResultSet data = metadata.getUDTs(null,
                baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()
                        ? baseSchema.getSchemaName().toUpperCase()
                        : null,
                params[0].toUpperCase(), null);
        result = data.next();
        CatalogUtil.closeQuietly(data);
        break;
    case FUNCTION:
        metadata = conn.getMetaData();
        ResultSet functions = metadata.getFunctions(null,
                baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()
                        ? baseSchema.getSchemaName().toUpperCase()
                        : null,
                params[0].toUpperCase());
        result = functions.next();
        CatalogUtil.closeQuietly(functions);
        break;
    case INDEX:
        if (params.length != 2) {
            throw new IllegalArgumentException(
                    "Finding index object is needed two strings, table name and index name");
        }

        pstmt = getExistQuery(conn, type);
        if (pstmt != null) {
            result = checkExistenceByQuery(pstmt, baseSchema, params);
        } else {
            metadata = conn.getMetaData();
            ResultSet indexes = metadata.getIndexInfo(null,
                    baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()
                            ? baseSchema.getSchemaName().toUpperCase()
                            : null,
                    params[0].toUpperCase(), false, true);
            while (indexes.next()) {
                if (indexes.getString("INDEX_NAME").equals(params[1].toUpperCase())) {
                    result = true;
                    break;
                }
            }
            CatalogUtil.closeQuietly(indexes);
        }
        break;
    case TABLE:
        pstmt = getExistQuery(conn, type);
        if (pstmt != null) {
            result = checkExistenceByQuery(pstmt, baseSchema, params);
        } else {
            metadata = conn.getMetaData();
            ResultSet tables = metadata.getTables(null,
                    baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()
                            ? baseSchema.getSchemaName().toUpperCase()
                            : null,
                    params[0].toUpperCase(), new String[] { "TABLE" });
            result = tables.next();
            CatalogUtil.closeQuietly(tables);
        }
        break;
    case DOMAIN:
    case OPERATOR:
    case RULE:
    case SEQUENCE:
    case TRIGGER:
    case VIEW:
        pstmt = getExistQuery(conn, type);

        if (pstmt == null) {
            throw new TajoInternalError(
                    "Finding " + type + " type of database object is not supported on this database system.");
        }

        result = checkExistenceByQuery(pstmt, baseSchema, params);
        break;
    }

    return result;
}