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:org.finra.herd.service.impl.RelationalTableRegistrationHelperServiceImpl.java

/**
 * Retrieves a list of actual schema columns for the specified relational table. This method uses actual JDBC connection to retrieve a description of table
 * columns.//from   w w w .  ja  v a  2  s.  co m
 *
 * @param relationalStorageAttributesDto the relational storage attributes DTO
 * @param relationalSchemaName the name of the relational database schema
 * @param relationalTableName the name of the relational table
 *
 * @return the list of schema columns for the specified relational table
 */
List<SchemaColumn> retrieveRelationalTableColumnsImpl(
        RelationalStorageAttributesDto relationalStorageAttributesDto, String relationalSchemaName,
        String relationalTableName) {
    // Get the JDBC password value.
    String password = getPassword(relationalStorageAttributesDto);

    // Create and initialize a driver manager data source (a simple implementation of the standard JDBC interface).
    // We only support PostgreSQL database type.
    DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
    driverManagerDataSource.setUrl(relationalStorageAttributesDto.getJdbcUrl());
    driverManagerDataSource.setUsername(relationalStorageAttributesDto.getJdbcUsername());
    driverManagerDataSource.setPassword(password);
    driverManagerDataSource.setDriverClassName(JdbcServiceImpl.DRIVER_POSTGRES);

    // Create an empty result list.
    List<SchemaColumn> schemaColumns = new ArrayList<>();

    // Connect to the database and retrieve the relational table columns.
    try (Connection connection = driverManagerDataSource.getConnection()) {
        DatabaseMetaData databaseMetaData = connection.getMetaData();

        // Check if the specified relational table exists in the database.
        try (ResultSet tables = databaseMetaData.getTables(null, relationalSchemaName, relationalTableName,
                null)) {
            Assert.isTrue(tables.next(), String.format(
                    "Relational table with \"%s\" name not found under \"%s\" schema at jdbc.url=\"%s\" for jdbc.username=\"%s\".",
                    relationalTableName, relationalSchemaName, driverManagerDataSource.getUrl(),
                    driverManagerDataSource.getUsername()));
        }

        // Retrieve the relational table columns.
        try (ResultSet columns = databaseMetaData.getColumns(null, relationalSchemaName, relationalTableName,
                null)) {
            while (columns.next()) {
                SchemaColumn schemaColumn = new SchemaColumn();
                schemaColumn.setName(columns.getString("COLUMN_NAME"));
                schemaColumn.setType(columns.getString("TYPE_NAME"));
                schemaColumn.setSize(columns.getString("COLUMN_SIZE"));
                schemaColumn.setRequired(columns.getInt("NULLABLE") == 0);
                schemaColumn.setDefaultValue(columns.getString("COLUMN_DEF"));
                schemaColumns.add(schemaColumn);
            }
        }
    } catch (SQLException e) {
        throw new IllegalArgumentException(String.format(
                "Failed to retrieve description of a relational table with \"%s\" name under \"%s\" schema "
                        + "at jdbc.url=\"%s\" using jdbc.username=\"%s\". Reason: %s",
                relationalTableName, relationalSchemaName, driverManagerDataSource.getUrl(),
                driverManagerDataSource.getUsername(), e.getMessage()), e);
    }

    return schemaColumns;
}

From source file:com.cloudera.sqoop.manager.SqlManager.java

@Override
public String[] listTables() {
    ResultSet results = null;/* w  w  w .jav a  2s  . com*/
    String[] tableTypes = { "TABLE" };
    try {
        try {
            DatabaseMetaData metaData = this.getConnection().getMetaData();
            results = metaData.getTables(null, null, null, tableTypes);
        } catch (SQLException sqlException) {
            LOG.error("Error reading database metadata: " + sqlException.toString());
            return null;
        }

        if (null == results) {
            return null;
        }

        try {
            ArrayList<String> tables = new ArrayList<String>();
            while (results.next()) {
                String tableName = results.getString("TABLE_NAME");
                tables.add(tableName);
            }

            return tables.toArray(new String[0]);
        } catch (SQLException sqlException) {
            LOG.error("Error reading from database: " + sqlException.toString());
            return null;
        }
    } finally {
        if (null != results) {
            try {
                results.close();
                getConnection().commit();
            } catch (SQLException sqlE) {
                LOG.warn("Exception closing ResultSet: " + sqlE.toString());
            }
        }
    }
}

From source file:org.apache.bigtop.itest.hive.TestJdbc.java

@Test
public void setSchema() throws SQLException {
    try (Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {

        final String dbName = "bigtop_jdbc_test_db";

        final String tableName = "bigtop_jdbc_test_table";
        stmt.execute("drop table if exists " + tableName);

        stmt.execute("drop database if exists " + dbName + " cascade");
        stmt.execute("create database " + dbName);

        conn.setSchema(dbName);//from   w ww.  jav  a  2 s. com

        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = md.getSchemas(null, dbName);

        while (rs.next()) {
            String schemaName = rs.getString(2);
            LOG.debug("Schema name is " + schemaName);
        }

        stmt.execute("create table " + tableName + " (i int, s varchar(32))");

        rs = md.getTables(null, dbName, tableName, null);
        while (rs.next()) {
            String tName = rs.getString(3);
            LOG.debug("Schema name is " + tName);
        }

        rs = md.getColumns(null, dbName, tableName, "i");
        while (rs.next()) {
            String colName = rs.getString(4);
            LOG.debug("Schema name is " + colName);
        }

        rs = md.getFunctions(null, dbName, "foo");
        while (rs.next()) {
            String funcName = rs.getString(3);
            LOG.debug("Schema name is " + funcName);
        }
    }
}

From source file:org.geoserver.security.jdbc.AbstractJDBCService.java

/**
 * Checks if the tables are already created
 * //  w  ww .  ja  v  a2s .c o m
 * @param con
 * @return
 * @throws IOException
 */
public boolean tablesAlreadyCreated() throws IOException {
    ResultSet rs = null;
    Connection con = null;
    try {
        con = getConnection();
        DatabaseMetaData md = con.getMetaData();
        String schemaName = null;
        String tableName = ddlProps.getProperty("check.table");
        if (tableName.contains(".")) {
            StringTokenizer tok = new StringTokenizer(tableName, ".");
            schemaName = tok.nextToken();
            tableName = tok.nextToken();
        }
        // try exact match
        rs = md.getTables(null, schemaName, tableName, null);
        if (rs.next())
            return true;

        // try with upper case letters
        rs.close();
        schemaName = schemaName == null ? null : schemaName.toUpperCase();
        tableName = tableName.toUpperCase();
        rs = md.getTables(null, schemaName, tableName, null);
        if (rs.next())
            return true;

        // try with lower case letters
        rs.close();
        schemaName = schemaName == null ? null : schemaName.toLowerCase();
        tableName = tableName.toLowerCase();
        rs = md.getTables(null, schemaName, tableName, null);
        if (rs.next())
            return true;

        return false;

    } catch (SQLException ex) {
        throw new IOException(ex);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (con != null)
                closeConnection(con);
        } catch (SQLException e) {
            // do nothing
        }
    }
}

From source file:org.ensembl.healthcheck.util.DBUtils.java

public static boolean tableExists(Connection con, String table) {

    boolean result = false;
    ResultSet rs = null;//from   w w w.  j av  a  2  s.c  om
    try {

        DatabaseMetaData dbm = con.getMetaData();
        rs = dbm.getTables(null, null, table, null);

        if (rs.next()) {
            result = true;
        }

    } catch (SQLException e) {
        throw new SqlUncheckedException("Could not check for table " + table, e);
    } finally {
        closeQuietly(rs);
    }

    return result;

}

From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java

/**
 * Wrapper for {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[])}
 *
 * @param connection open JDBC connection
 * @param schema schema name, can be null
 * @param tableName table name or pattern, optionally fully qualified in the form schema.tableName
 * @return ResultSet containing the table metadata
 *
 * @throws SQLException// ww w .  j av a 2 s  .c  o m
 */
public ResultSet getTableMetadata(Connection connection, String schema, String tableName) throws SQLException {
    DatabaseMetaData metadata = connection.getMetaData();
    return metadata.getTables(connection.getCatalog(), schema, tableName, METADATA_TABLE_TYPE);
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testUpdateDataSource3() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    final String comment = "commentOnTable";
    final String tblName = "employee";

    stmt.execute("COMMENT ON TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".\"" + tblName + "\" IS '" + comment
            + "'");
    DatabaseMetaData metaData = conn.getMetaData();

    ResultSet rs = metaData.getTables(dataMemDb.name, "%DEFAULT", tblName, null);
    while (rs.next()) {
        if (rs.getString("TABLE_NAME").equals(tblName))
            assertTrue(rs.getString("REMARKS").equals(comment));
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("REMARKS"));
    }/*  w w w  .  ja v  a 2  s . c  om*/
    rs.close();

    stmt.execute("ALTER SYSTEM UPDATE DATASOURCE \"" + dataMemDb.name + '"');

    rs = metaData.getTables(dataMemDb.name, "%DEFAULT", tblName, null);
    while (rs.next()) {
        if (rs.getString("TABLE_NAME").equals(tblName))
            assertTrue(rs.getString("REMARKS").equals(comment));
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("REMARKS"));
    }
    rs.close();

    stmt.close();
    conn.close();
}

From source file:it.doqui.index.ecmengine.business.personalization.multirepository.bootstrap.SchemaBootstrap.java

/**
 * @return Returns the number of applied patches
 *//*from   ww w  .j  a  v a  2 s.  c o  m*/
private int countAppliedPatches(Connection connection) throws Exception {
    DatabaseMetaData dbMetadata = connection.getMetaData();

    ResultSet tableRs = dbMetadata.getTables(null, null, "%", null);
    boolean newPatchTable = false;
    boolean oldPatchTable = false;
    try {
        while (tableRs.next()) {
            String tableName = tableRs.getString("TABLE_NAME");
            if (tableName.equalsIgnoreCase("applied_patch")) {
                oldPatchTable = true;
                break;
            } else if (tableName.equalsIgnoreCase("alf_applied_patch")) {
                newPatchTable = true;
                break;
            }
        }
    } finally {
        try {
            tableRs.close();
        } catch (Throwable e) {
            e.printStackTrace();
        }
    }

    if (newPatchTable) {
        Statement stmt = connection.createStatement();
        try {
            ResultSet rs = stmt.executeQuery("select count(id) from alf_applied_patch");
            rs.next();
            int count = rs.getInt(1);
            return count;
        } finally {
            try {
                stmt.close();
            } catch (Throwable e) {
            }
        }
    } else if (oldPatchTable) {
        // found the old style table name
        Statement stmt = connection.createStatement();
        try {
            ResultSet rs = stmt.executeQuery("select count(id) from applied_patch");
            rs.next();
            int count = rs.getInt(1);
            return count;
        } finally {
            try {
                stmt.close();
            } catch (Throwable e) {
            }
        }
    } else {
        // The applied patches table is not present
        throw new NoSchemaException();
    }
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testUpdateDataSource1() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    final String tblName = "TMP";
    dataMemDb.runExecuteUpdate("CREATE TABLE \"" + tblName + "\" (\"ID\" INTEGER, \"NAME\" STRING)");
    dataMemDb.runExecuteUpdate("INSERT INTO \"" + tblName + "\" VALUES (1, 'yjchoi')");

    boolean exceptionCaught = false;
    try {/*www.jav  a  2  s.c o m*/
        checkNumRows(stmt, tblName);
    } catch (SQLException e) {
        exceptionCaught = true;
    }
    assertTrue(exceptionCaught);

    int rows = checkNumRows(stmt, "employee");
    assertEquals(rows, 10);

    DatabaseMetaData metaData = conn.getMetaData();
    ResultSet rs = metaData.getTables(dataMemDb.name, "%DEFAULT", "%", null);
    while (rs.next())
        System.out.println(" *** " + rs.getString("TABLE_NAME"));

    stmt.execute("ALTER SYSTEM UPDATE DATASOURCE \"" + dataMemDb.name + '"');

    metaData = conn.getMetaData();
    rs = metaData.getTables(dataMemDb.name, "%DEFAULT", "%", null);
    while (rs.next())
        System.out.println(" *** " + rs.getString("TABLE_NAME"));

    rows = checkNumRows(stmt, tblName);
    assertEquals(rows, 1);

    stmt.close();
    conn.close();
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

private boolean existTable(DatabaseMetaData metaData, String dsName, String name) throws SQLException {
    ResultSet rs = metaData.getTables(dsName, "%DEFAULT", "%", null);
    while (rs.next()) {
        String tblName = rs.getString("TABLE_NAME");
        System.out.println(" *** " + tblName);
        if (tblName.equals(name))
            return true;
    }//w w w .  java  2  s . c om
    return false;
}