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:madgik.exareme.master.queryProcessor.analyzer.fanalyzer.FederatedAnalyzer.java

private Map<String, Set<String>> specifyTables() throws Exception {
    Map<String, Set<String>> info = new HashMap<String, Set<String>>();
    DatabaseMetaData dbmd = con.getMetaData(); // dtabase metadata object
    // listing tables and columns
    String catalog = null;/* w  w w .j  a  v a 2 s  . c o m*/
    String schemaPattern = null;
    String tableNamePattern = null;
    String[] types = null;
    String columnNamePattern = null;

    ResultSet resultTables = dbmd.getTables(catalog, schemaPattern, tableNamePattern, types);

    while (resultTables.next()) {
        String tableName = StringEscapeUtils.escapeJava(resultTables.getString(3));

        if (tableName.contains("_sample"))
            break;
        if (tableName.equals("sqlite_stat1"))
            continue;

        tableNamePattern = tableName;
        ResultSet resultColumns = dbmd.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
        Set<String> columns = new HashSet<String>();

        while (resultColumns.next()) {
            String columnName = StringEscapeUtils.escapeJava(resultColumns.getString(4));
            columns.add(columnName);
        }
        info.put(tableName, columns);
    }

    return info;
}

From source file:net.solarnetwork.node.dao.jdbc.AbstractJdbcDao.java

/**
 * Test if a table exists in the database.
 * //from   ww w . j  a  v a  2  s . c o m
 * @param conn
 *        the connection
 * @param aSchemaName
 *        the schema name to look for (or <em>null</em> for any schema)
 * @param aTableName
 *        the table name to look for
 * @return boolean if table is found
 * @throws SQLException
 *         if any SQL error occurs
 */
protected boolean tableExists(Connection conn, String aSchemaName, String aTableName) throws SQLException {
    DatabaseMetaData dbMeta = conn.getMetaData();
    ResultSet rs = null;
    try {
        rs = dbMeta.getTables(null, null, null, null);
        while (rs.next()) {
            String schema = rs.getString(2);
            String table = rs.getString(3);
            if ((aSchemaName == null || (aSchemaName.equalsIgnoreCase(schema)))
                    && aTableName.equalsIgnoreCase(table)) {
                if (log.isDebugEnabled()) {
                    log.debug("Found table " + schema + '.' + table);
                }
                return true;
            }
        }
        return false;
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // ignore this
            }
        }
    }
}

From source file:org.msec.LogQuery.java

public List<String> getTableList(String startDateStr, String endDateStr) throws ParseException, SQLException {
    Date startDate = dateFormatter.parse(startDateStr);
    Date endDate = dateFormatter.parse(endDateStr);
    List<String> ret = new ArrayList<String>();

    Calendar start = Calendar.getInstance();
    start.setTime(startDate);/*w  w  w.  j av a2 s  .co m*/
    Calendar end = Calendar.getInstance();
    end.setTime(endDate);

    for (Date date = start.getTime(); !start.after(end); start.add(Calendar.DATE, 1), date = start.getTime()) {
        //
        String tableName = tableNamePrefix + dayFormatter.format(date);
        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = md.getTables(null, null, tableName, null);
        if (rs.next()) {
            ret.add(tableNamePrefix + dayFormatter.format(date));
        }
    }
    return ret;
}

From source file:weave.utils.SQLUtils.java

/**
 * @param conn An existing SQL Connection
 * @param schemaName A schema name accessible through the given connection
 * @return A List of table names in the given schema
 * @throws SQLException If the query fails.
 *//*ww w.ja  v a2  s .  c  o  m*/
public static List<String> getTables(Connection conn, String schemaName) throws SQLException {
    List<String> tables = new Vector<String>();
    ResultSet rs = null;
    try {
        DatabaseMetaData md = conn.getMetaData();
        String[] types = new String[] { "TABLE", "VIEW" };

        // MySQL uses "catalogs" instead of "schemas"
        if (conn.getMetaData().getDatabaseProductName().equalsIgnoreCase(MYSQL))
            rs = md.getTables(schemaName, null, null, types);
        else if (SQLUtils.isOracleServer(conn))
            rs = md.getTables(null, schemaName.toUpperCase(), null, types);
        else
            rs = md.getTables(null, schemaName, null, types);

        // use column index instead of name because sometimes the names are lower case, sometimes upper.
        // column indices: 1=table_cat,2=table_schem,3=table_name,4=table_type,5=remarks
        while (rs.next())
            tables.add(rs.getString(3)); // table_name

        Collections.sort(tables, String.CASE_INSENSITIVE_ORDER);
    } finally {
        // close everything in reverse order
        cleanup(rs);
    }
    return tables;
}

From source file:adapter.gbase.signalling.SignallingAdapter.java

private boolean tableExist(String tableName) {
    boolean result = false;
    ResultSet resultSet = null;//from www.  jav a 2  s . c  o m
    try {
        Connection conn = DbKit.getConfig("gbase").getConnection();
        DatabaseMetaData dma = conn.getMetaData();
        resultSet = dma.getTables(null, null, tableName.toUpperCase(), null);
        boolean more = resultSet.next();
        if (more) {
            result = true;
        }
    } catch (Exception ex) {

    }
    return result;

}

From source file:org.msec.LogQuery.java

public void showRecords() throws SQLException {
    String tableName = tableNamePrefix + dayFormatter.format(new Date());
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = md.getTables(null, null, tableName, null);
    if (rs.next()) {
        System.out.println("table " + tableName + " exist");
    } else {/*from  w  w w .j  av  a 2 s.c  o m*/
        System.out.println("table " + tableName + " not exist");
        return;
    }

    String columnList = "";
    rs = md.getColumns(null, null, tableName, null);
    while (rs.next()) {
        columnList += rs.getString("COLUMN_NAME");
        if (!rs.isLast())
            columnList += ",";
    }
    System.out.println(columnList);

    String sql = "select * from " + tableName; //SQL
    rs = stmt.executeQuery(sql);//
    System.out.println("ip" + "\t" + "level" + "\t" + "rpcname" + "\t\t" + "time" + "\t" + "content");
    while (rs.next()) {
        System.out.print(rs.getString(1) + "\t");
        System.out.print(rs.getString(2) + "\t");
        System.out.print(rs.getString(3) + "\t");
        System.out.print(rs.getString(4) + "\t");
        System.out.print(rs.getString(5) + "\t");
        System.out.println();
    }
    rs.close();
}

From source file:fr.cnes.sitools.datasource.jdbc.business.SitoolsDataSource.java

/**
 * Return the list of tables in the database
 * //from  w w  w . j  a  v a2s  . com
 * @return the list of table names
 * @deprecated use getTables(String schemaPattern) instead
 */
@Deprecated
public List<String> getMetadata() {
    if (tableNameList != null) {
        return tableNameList;
    }
    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = getConnection();

        tableNameList = new ArrayList<String>();
        DatabaseMetaData metaData = conn.getMetaData();
        rs = metaData.getTables(null, schemaOnConnection, null, new String[] { "TABLE" });
        while (rs.next()) {
            tableNameList.add(rs.getString("TABLE_NAME"));
        }
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, null, ex);
    } finally {
        closeConnection(conn);
        closeResultSet(rs);
    }
    return tableNameList;
}

From source file:com.kylinolap.rest.service.QueryService.java

protected List<TableMeta> getMetadata(CubeManager cubeMgr, String project, boolean cubedOnly)
        throws SQLException {

    Connection conn = null;/*from   w  w w . j  a v a 2s .  co  m*/
    ResultSet columnMeta = null;
    List<TableMeta> tableMetas = null;

    try {
        DataSource dataSource = getOLAPDataSource(project);
        conn = dataSource.getConnection();
        DatabaseMetaData metaData = conn.getMetaData();

        logger.debug("getting table metas");
        ResultSet JDBCTableMeta = metaData.getTables(null, null, null, null);

        tableMetas = new LinkedList<TableMeta>();
        Map<String, TableMeta> tableMap = new HashMap<String, TableMeta>();
        while (JDBCTableMeta.next()) {
            String catalogName = JDBCTableMeta.getString(1);
            String schemaName = JDBCTableMeta.getString(2);

            // Not every JDBC data provider offers full 10 columns, for
            // example,
            // PostgreSQL has only 5
            TableMeta tblMeta = new TableMeta(catalogName == null ? Constant.FakeCatalogName : catalogName,
                    schemaName == null ? Constant.FakeSchemaName : schemaName, JDBCTableMeta.getString(3),
                    JDBCTableMeta.getString(4), JDBCTableMeta.getString(5), null, null, null, null, null);

            if (!cubedOnly || getProjectManager().isExposedTable(project, tblMeta.getTABLE_NAME())) {
                tableMetas.add(tblMeta);
                tableMap.put(tblMeta.getTABLE_SCHEM() + "#" + tblMeta.getTABLE_NAME(), tblMeta);
            }
        }

        logger.debug("getting column metas");
        columnMeta = metaData.getColumns(null, null, null, null);

        while (columnMeta.next()) {
            String catalogName = columnMeta.getString(1);
            String schemaName = columnMeta.getString(2);

            // kylin(optiq) is not strictly following JDBC specification
            ColumnMeta colmnMeta = new ColumnMeta(catalogName == null ? Constant.FakeCatalogName : catalogName,
                    schemaName == null ? Constant.FakeSchemaName : schemaName, columnMeta.getString(3),
                    columnMeta.getString(4), columnMeta.getInt(5), columnMeta.getString(6),
                    columnMeta.getInt(7), getInt(columnMeta.getString(8)), columnMeta.getInt(9),
                    columnMeta.getInt(10), columnMeta.getInt(11), columnMeta.getString(12),
                    columnMeta.getString(13), getInt(columnMeta.getString(14)),
                    getInt(columnMeta.getString(15)), columnMeta.getInt(16), columnMeta.getInt(17),
                    columnMeta.getString(18), columnMeta.getString(19), columnMeta.getString(20),
                    columnMeta.getString(21), getShort(columnMeta.getString(22)), columnMeta.getString(23));

            if (!cubedOnly || getProjectManager().isExposedColumn(project, colmnMeta.getTABLE_NAME(),
                    colmnMeta.getCOLUMN_NAME())) {
                tableMap.get(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME()).addColumn(colmnMeta);
            }
        }
        logger.debug("done column metas");
    } finally {
        close(columnMeta, null, conn);
    }

    return tableMetas;
}

From source file:fr.cnes.sitools.datasource.jdbc.business.SitoolsDataSource.java

/**
 * Return the list of tables in the database
 * //  ww  w.  j a  va2 s  .  c om
 * @param schemaPattern
 *          the schema pattern to access tables
 * @return the list of table names
 */
public List<Table> getTables(String schemaPattern) {
    String schema = (schemaPattern == null) ? schemaOnConnection : schemaPattern;

    // Oracle : pour supprimer le caractre vide dans chaine 
    if (null == schema || schema.equals("")) {
        schema = null;
    }

    ArrayList<Table> tables = new ArrayList<Table>();

    Connection conn = null;
    ResultSet rs = null;
    try {
        conn = getConnection();

        DatabaseMetaData metaData = conn.getMetaData();

        rs = metaData.getTables(null, schema, null, new String[] { "TABLE", "VIEW" });
        while (rs.next()) {
            tables.add(new Table(rs.getString("TABLE_NAME"), rs.getString("TABLE_SCHEM")));
        }
    } catch (SQLException ex) {
        LOG.log(Level.SEVERE, null, ex);
    } finally {
        closeConnection(conn);
        closeResultSet(rs);
    }
    return tables;
}

From source file:org.apache.syncope.core.util.ImportExport.java

public void export(final OutputStream os) throws SAXException, TransformerConfigurationException {

    StreamResult streamResult = new StreamResult(os);
    final SAXTransformerFactory transformerFactory = (SAXTransformerFactory) SAXTransformerFactory
            .newInstance();//from w w  w  .ja va  2  s.  c o m

    TransformerHandler handler = transformerFactory.newTransformerHandler();
    Transformer serializer = handler.getTransformer();
    serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
    serializer.setOutputProperty(OutputKeys.INDENT, "yes");
    handler.setResult(streamResult);
    handler.startDocument();
    handler.startElement("", "", ROOT_ELEMENT, new AttributesImpl());

    final Connection conn = DataSourceUtils.getConnection(dataSource);

    ResultSet rs = null;

    try {
        final DatabaseMetaData meta = conn.getMetaData();

        final String schema = readSchema();

        rs = meta.getTables(null, schema, null, new String[] { "TABLE" });

        final Set<String> tableNames = new HashSet<String>();

        while (rs.next()) {
            String tableName = rs.getString("TABLE_NAME");

            // these tables must be ignored
            if (!tableName.toUpperCase().startsWith("QRTZ_")
                    && !tableName.toUpperCase().startsWith("LOGGING_")) {
                tableNames.add(tableName);
            }
        }

        // then sort tables based on foreign keys and dump
        for (String tableName : sortByForeignKeys(conn, tableNames, schema)) {
            doExportTable(handler, conn, tableName);
        }
    } catch (SQLException e) {
        LOG.error("While exporting database content", e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error("While closing tables result set", e);
            }
        }
        DataSourceUtils.releaseConnection(conn, dataSource);
    }

    handler.endElement("", "", ROOT_ELEMENT);
    handler.endDocument();
}