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

The following examples show how to use java.sql.DatabaseMetaData#getTables() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DbHelper.java    From crud-intellij-plugin with Apache License 2.0 6 votes vote down vote up
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);
    }
}
 
Example 2
Source File: GenericJdbcExecutor.java    From sqoop-on-spark with Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 3
Source File: MetaDataRegressionTest.java    From r-course with MIT License 6 votes vote down vote up
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());

}
 
Example 4
Source File: MetadataSource.java    From sis with Apache License 2.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 5
Source File: TableFactory.java    From stategen with GNU Affero General Public License v3.0 6 votes vote down vote up
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;
}
 
Example 6
Source File: SystemApplicationDAO.java    From carbon-apimgt with Apache License 2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example 7
Source File: JdbcDatabaseConnection.java    From ormlite-jdbc with ISC License 6 votes vote down vote up
@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();
		}
	}
}
 
Example 8
Source File: QueryDatabaseMetaDataIT.java    From phoenix with Apache License 2.0 6 votes vote down vote up
@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());
    }
}
 
Example 9
Source File: SQLiteDAO.java    From ofexport2 with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 10
Source File: MetadataUtilities.java    From netbeans with Apache License 2.0 5 votes vote down vote up
/**
 * 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);
    }
}
 
Example 11
Source File: PostgreSQLServiceBrokerV2IntegrationTests.java    From postgresql-cf-service-broker with Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 12
Source File: StatisticRdbRepository.java    From shardingsphere-elasticjob-cloud with Apache License 2.0 5 votes vote down vote up
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);
        }
    }
}
 
Example 13
Source File: OracleSchema.java    From netbeans with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 14
Source File: OracleDbConnector.java    From TripleGeo with GNU General Public License v3.0 5 votes vote down vote up
@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;
}
 
Example 15
Source File: JdbcMetadataHandler.java    From aws-athena-query-federation with Apache License 2.0 5 votes vote down vote up
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"});
}
 
Example 16
Source File: CacheSessionDataTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 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();
}
 
Example 17
Source File: metadataMultiConn.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
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();
}
 
Example 18
Source File: DMDBugsTest.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
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");
}
 
Example 19
Source File: BungeeSuiteImporter.java    From BungeeAdminTools with GNU General Public License v3.0 4 votes vote down vote up
@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);
        }
    }
}
 
Example 20
Source File: JdbcUtil.java    From datacollector with Apache License 2.0 2 votes vote down vote up
/**
 * 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);
}