Java Code Examples for java.sql.DatabaseMetaData#getTables()

public List<String> getAllTableName(String database) {
    db = database;
    Connection conn = getConnection(db);
    try {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
        List<String> ls = new ArrayList<>();
        while (rs.next()) {
            String s = rs.getString("TABLE_NAME");
            ls.add(s);
        }
        return ls;
    } catch (SQLException e) {
        throw new RuntimeException(e.getMessage(), e);
    } finally {
        closeConnection(conn);
    }
}
 

public boolean existTable(String table) {
  try {
    String[] splitNames = dequalify(table);

    DatabaseMetaData dbmd = connection.getMetaData();
    ResultSet rs = dbmd.getTables(null, splitNames[0], splitNames[1], null);

    if (rs.next()) {
      return true;
    } else {
      return false;
    }

  } catch (SQLException e) {
    logSQLException(e);
    throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0003, e);
  }
}
 

public void testQuotedGunk() throws Exception {
    createTable("testQuotedGunk", "(field1 int)");

    String quotedCatalog = "`" + this.conn.getCatalog() + "`";
    String unquotedCatalog = this.conn.getCatalog();

    DatabaseMetaData dbmd = this.conn.getMetaData();
    this.rs = dbmd.getTables(quotedCatalog, null, "testQuotedGunk", new String[] { "TABLE" });
    assertTrue(this.rs.next());
    this.rs = dbmd.getTables(unquotedCatalog, null, "testQuotedGunk", new String[] { "TABLE" });
    assertTrue(this.rs.next());
    this.rs = dbmd.getColumns(quotedCatalog, null, "testQuotedGunk", "field1");
    assertTrue(this.rs.next());
    this.rs = dbmd.getColumns(unquotedCatalog, null, "testQuotedGunk", "field1");
    assertTrue(this.rs.next());

}
 

/**
 * If the metadata schema does not exist in the database, creates it and inserts the pre-defined metadata values.
 * The current implementation has the following restrictions:
 *
 * <ul>
 *   <li>Metadata standard must be {@link MetadataStandard#ISO_19115} or compatible.</li>
 *   <li>The schema name must be {@code "metadata"}, as this is the name used unquoted in SQL scripts.</li>
 * </ul>
 *
 * Maintenance note: this method is invoked by reflection in {@code non-free:sis-embedded-data} module.
 * If we make this method public in a future Apache SIS version, then we can remove the reflection code.
 *
 * @throws SQLException if an error occurred while inserting the metadata.
 */
final synchronized void install() throws IOException, SQLException {
    final Connection connection = connection();
    final DatabaseMetaData md = connection.getMetaData();
    if (md.storesUpperCaseIdentifiers()) {
        schema = schema.toUpperCase(Locale.US);
    } else if (md.storesLowerCaseIdentifiers()) {
        schema = schema.toLowerCase(Locale.US);
    }
    quoteSchema = false;
    try (ResultSet result = md.getTables(catalog, schema, "Citation", null)) {
        if (result.next()) {
            return;
        }
    }
    final Installer installer = new Installer(connection);
    installer.run();
}
 

private Table _getTable(String catalog, String schema, String tableName) throws SQLException {
    if (tableName == null || tableName.trim().length() == 0)
        throw new IllegalArgumentException("tableName must be not empty");
    catalog = StringHelper.defaultIfEmpty(catalog, null);
    schema = StringHelper.defaultIfEmpty(schema, null);

    Connection conn = DataSourceProvider.getConnection();
    DatabaseMetaData dbMetaData = conn.getMetaData();
    ResultSet rs = dbMetaData.getTables(catalog, schema, tableName, null);
    try {
        while (rs.next()) {
            Table table = new TableCreateProcessor(conn, getSchema(), getCatalog()).createTable(rs);
            return table;
        }
    } finally {
        DBHelper.close(conn, rs);
    }
    return null;
}
 

/**
 * Checks whether the system application table exists in the database.
 *
 * @return : True if exists, false otherwise.
 */
public boolean isTableExists() throws APIMgtDAOException {

    boolean isExists = false;
    Connection connection = null;
    ResultSet resultSet = null;
    DatabaseMetaData databaseMetaData;

    try {
        connection = APIMgtDBUtil.getConnection();
        databaseMetaData = connection.getMetaData();

        resultSet = databaseMetaData.getTables(null, null, SYSTEM_APP_TABLE_NAME, null);
        if (resultSet.next()) {
            isExists = true;
        }
    } catch (SQLException e) {
        if (log.isDebugEnabled()) {
            log.debug("Error while retrieving database information. ", e);
        }
        handleException("Error retrieving Database information", e);
    } finally {
        APIMgtDBUtil.closeAllConnections(null, connection, resultSet);
    }
    return isExists;
}
 

@Override
public boolean isTableExists(String tableName) throws SQLException {
	DatabaseMetaData metaData = connection.getMetaData();
	logger.trace("Got meta data from connection");
	ResultSet results = null;
	try {
		results = metaData.getTables(null, null, "%", new String[] { "TABLE" });
		// we do it this way because some result sets don't like us to findColumn if no results
		if (!results.next()) {
			return false;
		}
		int col = results.findColumn(JDBC_META_TABLE_NAME_COLUMN);
		do {
			String dbTableName = results.getString(col);
			if (tableName.equalsIgnoreCase(dbTableName)) {
				return true;
			}
		} while (results.next());
		return false;
	} finally {
		if (results != null) {
			results.close();
		}
	}
}
 

@Test
public void testMetadataTenantSpecific() throws SQLException {
	// create multi-tenant table
	String tableName = generateUniqueName();
    try (Connection conn = DriverManager.getConnection(getUrl())) {
    	String baseTableDdl = "CREATE TABLE %s (K1 VARCHAR NOT NULL, K2 VARCHAR NOT NULL, V VARCHAR CONSTRAINT PK PRIMARY KEY(K1, K2)) MULTI_TENANT=true";
    	conn.createStatement().execute(String.format(baseTableDdl, tableName));
    }
	
    // create tenant specific view and execute metdata data call with tenant specific connection
    String tenantId = generateUniqueName();
    Properties tenantProps = new Properties();
    tenantProps.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
    try (Connection tenantConn = DriverManager.getConnection(getUrl(), tenantProps)) {
    	String viewName = generateUniqueName();
    	String viewDdl = "CREATE VIEW %s AS SELECT * FROM %s";
    	tenantConn.createStatement().execute(String.format(viewDdl, viewName, tableName));
    	DatabaseMetaData dbmd = tenantConn.getMetaData();
    	ResultSet rs = dbmd.getTables(tenantId, "", viewName, null);
        assertTrue(rs.next());
        assertEquals(rs.getString("TABLE_NAME"), viewName);
        assertEquals(PTableType.VIEW.toString(), rs.getString("TABLE_TYPE"));
        assertFalse(rs.next());
    }
}
 

private LinkedList<String> getTableNames(Connection c) throws SQLException {
    LinkedList<String> tableNames = new LinkedList<>();
    DatabaseMetaData md = c.getMetaData();

    try (
        ResultSet rs = md.getTables(null, null, "%", null)) {
        while (rs.next()) {
            String tableName = rs.getString(THREE);
            tableNames.add(tableName);
        }
    }
    return tableNames;
}
 

/**
 * Call {@link DatabaseMetaData#getTables(String, String, String,
 * String[])}, wrapping any internal runtime exception into an
 * {@link SQLException}.
 */
public static ResultSet getTables(DatabaseMetaData dmd,
        String catalog, String schemaPattern, String tableNamePattern,
        String[] types) throws SQLException {
    try {
        return dmd.getTables(catalog, schemaPattern, tableNamePattern,
                types);
    } catch (SQLException e) {
        throw e;
    } catch (Throwable t) {
        throw new SQLException(t);
    }
}
 

private boolean checkTableExists(String tableName) throws Exception {
    DatabaseMetaData md = conn.getMetaData();
    ResultSet rs = md.getTables(null, null, tableName, null);

    // ResultSet.last() followed by ResultSet.getRow() will give you the row count
    rs.last();
    int rowCount = rs.getRow();
    return rowCount == 1;
}
 

private void createJobRegisterTableIfNeeded(final Connection conn) throws SQLException {
    DatabaseMetaData dbMetaData = conn.getMetaData();
    try (ResultSet resultSet = dbMetaData.getTables(null, null, TABLE_JOB_REGISTER_STATISTICS, new String[]{"TABLE"})) {
        if (!resultSet.next()) {
            createJobRegisterTable(conn);
        }
    }
}
 

@Override
protected void createTables() {
    LOGGER.log(Level.FINE, "Initializing tables in {0}", this);
    Map<String, Table> newTables = new LinkedHashMap<String, Table>();
    try {
        DatabaseMetaData dmd = jdbcCatalog.getJDBCMetadata().getDmd();
        Set<String> recycleBinTables = getRecycleBinObjects(dmd, "TABLE"); // NOI18N
        ResultSet rs = dmd.getTables(jdbcCatalog.getName(), name, "%", new String[]{"TABLE"}); // NOI18N
        if (rs != null) {
            try {
                while (rs.next()) {
                    String type = MetadataUtilities.trimmed(rs.getString("TABLE_TYPE")); //NOI18N
                    String tableName = rs.getString("TABLE_NAME"); // NOI18N
                    if (!recycleBinTables.contains(tableName)) {
                        Table table = createJDBCTable(tableName, type.contains("SYSTEM")).getTable(); //NOI18N
                        newTables.put(tableName, table);
                        LOGGER.log(Level.FINE, "Created table {0}", table);
                    } else {
                        LOGGER.log(Level.FINE, "Ignoring recycle bin table ''{0}''", tableName);
                    }
                }
            } finally {
                rs.close();
            }
        }
    } catch (SQLException e) {
        throw new MetadataException(e);
    }
    tables = Collections.unmodifiableMap(newTables);
}
 

@Override
public Set<String> getUserEntities(DatabaseMetaData databaseMetadata)
       throws SQLException {
  ResultSet resultSet = databaseMetadata.getTables(
          null, databaseMetadata.getUserName(), "%", DbConstants.TABLE_TYPES);
  HashSet<String> userEntitiesSet = new HashSet<String>();
  while (resultSet.next()) {
    userEntitiesSet.add(resultSet.getString(DbConstants.TABLE_NAME));
  }
  return userEntitiesSet;
}
 

private ResultSet getTables(final Connection connection, final String schemaName)
        throws SQLException
{
    DatabaseMetaData metadata = connection.getMetaData();
    String escape = metadata.getSearchStringEscape();
    return metadata.getTables(
            connection.getCatalog(),
            escapeNamePattern(schemaName, escape),
            null,
            new String[] {"TABLE", "VIEW"});
}
 

/**
 * Removes all tables in schema SPLICE which has the prefix 'T', before calling
 * super.tearDown().
 * @throws java.lang.Exception
 */
public void tearDown() throws Exception {
    DatabaseMetaData meta = getConnection().getMetaData();
    ResultSet tables = meta.getTables(null, "SPLICE", "T%", null);
    Statement s = createStatement();
    while (tables.next()) {
        s.execute("DROP TABLE " + tables.getString("TABLE_NAME"));
    }
    tables.close();
    s.close();
    commit();
    super.tearDown();
}
 

public static void getTables(DatabaseMetaData dmd,PrintStream out)
	throws SQLException
{
	String types[] = new String[1];
	types[0] = "TABLE";
	ResultSet rs = dmd.getTables(null, null, null, types);
	while (rs.next())
	{
		// 1.TABLE_CAT String => table catalog (may be null)
		String tableCat = rs.getString(1);

		// 2.TABLE_SCHEM String => table schema (may be null)
		String tableSchem = rs.getString(2);

		// 3.TABLE_NAME String => table name
		String tableName = rs.getString(3);

		// 4.TABLE_TYPE String => table type.
		// Typical types are "TABLE", "VIEW",
		//  "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY",
		//  "ALIAS", "SYNONYM".
		String tableType = rs.getString(4);

		// 5.REMARKS String => explanatory comment on the table
		String remarks = rs.getString(5);
	}
	rs.close();
}
 

public void testDerby3000() throws SQLException, IOException {
	ResultSet rs;
	// Derby-3000 make sure we process only valid TableType values and
	// process them correctly.
	DatabaseMetaData dmd = getConnection().getMetaData();

	Statement s = createStatement();
	s.executeUpdate("CREATE TABLE SPLICE.TAB (i int)");
	s.executeUpdate("CREATE VIEW  SPLICE.V  as SELECT * FROM TAB");
	s.executeUpdate("CREATE SYNONYM TSYN FOR SPLICE.TAB");

	String[] withInvalidTableTypes = {"SYNONYM","TABLE","VIEW",
	"GLOBAL TEMPORARY"};
	// just ignore invalid types
	rs = dmd.getTables( "%", "%", "%", withInvalidTableTypes);			
	JDBC.assertFullResultSet(rs,
		new String[][] {{"","SPLICE","TSYN","SYNONYM","",null,null,null,null,null},
		{"","SPLICE","TAB","TABLE","",null,null,null,null,null},
		{"","SPLICE","V","VIEW","",null,null,null,null,null}});


	rs = dmd.getTables("%", "%", "%", new String[] {"GLOBAL TEMPORARY"});
	JDBC.assertEmpty(rs);
	
	rs = dmd.getTables("%", "%", "%", new String[] {"VIEW"});
	JDBC.assertUnorderedResultSet(rs, new String[][] 
	            {{"","SPLICE","V","VIEW","",null,null,null,null,null}});

	
	rs = dmd.getTables("%", "%", "%", new String[] {"TABLE"});
	JDBC.assertUnorderedResultSet(rs,new String[][]
	          {{"","SPLICE","TAB","TABLE","",null,null,null,null,null}} );
	
	rs = dmd.getTables("%", "%", "%", new String[] {"SYNONYM"});
	JDBC.assertUnorderedResultSet(rs, new String[][]
                  {{"","SPLICE","TSYN","SYNONYM","",null,null,null,null,null}});

	rs = dmd.getTables( "%", "%", "%", new String[] {"SYSTEM TABLE"});
	assertEquals(23, JDBC.assertDrainResults(rs));
	s.executeUpdate("DROP VIEW SPLICE.V");
	s.executeUpdate("DROP TABLE SPLICE.TAB");
	s.executeUpdate("DROP SYNONYM SPLICE.TSYN");
}
 

@Override
protected void importData(final ProgressCallback<ImportStatus> progressionCallback, String... additionalsArgs) throws Exception{
    ResultSet res = null;
    try (Connection conn = BAT.getConnection()) {
        // Check if the bungee suite tables are here
        final DatabaseMetaData dbm = conn.getMetaData();
        for(final String table : Arrays.asList("BungeeBans", "BungeePlayers")){
            final ResultSet tables = dbm.getTables(null, null, table, null);
            if (!tables.next()) {
                throw new IllegalArgumentException("The table " + table + " wasn't found. Import aborted ...");
            }
        }

        // Count the number of entries (use to show the progression)
        final ResultSet resCount = conn.prepareStatement("SELECT COUNT(*) FROM BungeeBans;").executeQuery();
        if(resCount.next()){
            status = new ImportStatus(resCount.getInt("COUNT(*)"));
        }

        final PreparedStatement insertBans = conn.prepareStatement("INSERT INTO `" + SQLQueries.Ban.table
                + "`(UUID, ban_ip, ban_staff, ban_server, ban_begin, ban_end, ban_reason) VALUES (?, ?, ?, ?, ?, ?, ?);");
        final PreparedStatement getIP = conn.prepareStatement("SELECT ipaddress FROM BungeePlayers WHERE playername = ?;");

        res = conn.createStatement().executeQuery("SELECT * FROM BungeeBans;");
        int uncomittedEntries = 0;
        conn.setAutoCommit(false);
        
        while (res.next()) {
            final boolean ipBan = "ipban".equals(res.getString("type"));

            final String pName = res.getString("player");
            final String server = IModule.GLOBAL_SERVER;
            final String staff = res.getString("banned_by");
            final String reason = res.getString("reason");
            final Timestamp ban_begin = res.getTimestamp("banned_on");
            Timestamp ban_end = res.getTimestamp("banned_until");
            
            /* For unknown reason BungeeBans table contained (hardly ever but it did) date with a year > 3000,
             * not sure if that was some kind of joke from a staff member ... Anyways this code convert long-duration
               tempban to definitive ban */
            if(ban_end == null || ban_end.getTime() > System.currentTimeMillis() + 10 * (365 * (24 * 3600))){
                ban_end = null;
            }

            // Get the ip
            String ip = null;
            getIP.setString(1, pName);  
            final ResultSet resIP = getIP.executeQuery();
            if(resIP.next()){
                ip = resIP.getString("ipaddress");
            }
            resIP.close();
            if(ipBan && ip == null){
                continue;
            }

            // Get UUID
            String UUID = null;
            try{
                UUID = uuidCache.get(pName);
            } catch (UncheckedExecutionException e) {
                if(e.getCause() instanceof UUIDNotFoundException){
                    continue;
                }else{
                    throw e;
                }
            }

            // Insert the ban
            insertBans.setString(1, (ipBan) ? null : UUID);
            insertBans.setString(2, (ipBan) ? ip : null);
            insertBans.setString(3, staff);
            insertBans.setString(4, server);
            insertBans.setTimestamp(5, ban_begin);
            insertBans.setTimestamp(6, ban_end);
            insertBans.setString(7, reason);
            insertBans.execute();
            insertBans.clearParameters();
            getIP.clearParameters();
            uncomittedEntries++;
            
            initPlayerRowInBatPlayer(conn, pName, UUID);
            if(uncomittedEntries % 100 == 0){
                conn.commit();
                status.incrementConvertedEntries(uncomittedEntries);
                uncomittedEntries = 0;
                progressionCallback.onProgress(status);
            }
        }

        conn.commit();
        status.incrementConvertedEntries(uncomittedEntries);
        progressionCallback.done(status, null);
    }finally{
        if(res != null){
            DataSourceHandler.close(res);
        }
    }
}
 

/**
 * 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
 */
public ResultSet getTableMetadata(Connection connection, String schema, String tableName) throws SQLException {
  DatabaseMetaData metadata = connection.getMetaData();
  return metadata.getTables(getCatalog(connection, schema), schema, tableName, METADATA_TABLE_TYPE);
}