List of usage examples for java.sql DatabaseMetaData getTables
ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
throws SQLException;
From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java
/** * @param connection//www. j av a2 s. co m * @return */ public static List<String> getTableNames(final Connection connection) { try { ArrayList<String> fields = new ArrayList<String>(); DatabaseMetaData mdm = connection.getMetaData(); ResultSet rs = mdm.getTables(connection.getCatalog(), connection.getCatalog(), null, new String[] { "TABLE" }); while (rs.next()) { /*System.out.println("-------- " + rs.getString("TABLE_NAME")+" ----------"); for (int i=1;i<=rs.getMetaData().getColumnCount();i++) { System.out.println(rs.getMetaData().getColumnName(i)+"="+rs.getObject(i)); }*/ fields.add(rs.getString("TABLE_NAME")); } rs.close(); return fields; } catch (SQLException ex) { ex.printStackTrace(); } return null; }
From source file:net.hydromatic.optiq.test.JdbcTest.java
/** Tests a JDBC connection that provides a model that contains a view. */ @Test/*ww w .jav a 2 s .c o m*/ public void testModelView() throws Exception { final OptiqAssert.AssertThat with = OptiqAssert.assertThat().withModel("{\n" + " version: '1.0',\n" + " schemas: [\n" + " {\n" + " name: 'adhoc',\n" + " tables: [\n" + " {\n" + " name: 'EMPLOYEES',\n" + " type: 'custom',\n" + " factory: '" + EmpDeptTableFactory.class.getName() + "',\n" + " operand: {'foo': true, 'bar': 345}\n" + " },\n" + " {\n" + " name: 'V',\n" + " type: 'view',\n" + " sql: 'select * from \"EMPLOYEES\" where \"deptno\" = 10'\n" + " }\n" + " ]\n" + " }\n" + " ]\n" + "}"); with.query("select * from \"adhoc\".V order by \"name\" desc") .returns("empid=150; deptno=10; name=Sebastian; commission=null\n" + "empid=100; deptno=10; name=Bill; commission=1000\n"); // Make sure that views appear in metadata. with.doWithConnection(new Function1<OptiqConnection, Void>() { public Void apply(OptiqConnection a0) { try { final DatabaseMetaData metaData = a0.getMetaData(); // all table types assertEquals( "TABLE_CAT=null; TABLE_SCHEM=adhoc; TABLE_NAME=EMPLOYEES; TABLE_TYPE=TABLE; REMARKS=null; TYPE_CAT=null; TYPE_SCHEM=null; TYPE_NAME=null; SELF_REFERENCING_COL_NAME=null; REF_GENERATION=null\n" + "TABLE_CAT=null; TABLE_SCHEM=adhoc; TABLE_NAME=V; TABLE_TYPE=VIEW; REMARKS=null; TYPE_CAT=null; TYPE_SCHEM=null; TYPE_NAME=null; SELF_REFERENCING_COL_NAME=null; REF_GENERATION=null\n", JdbcTest.toString(metaData.getTables(null, "adhoc", null, null))); // views only assertEquals( "TABLE_CAT=null; TABLE_SCHEM=adhoc; TABLE_NAME=V; TABLE_TYPE=VIEW; REMARKS=null; TYPE_CAT=null; TYPE_SCHEM=null; TYPE_NAME=null; SELF_REFERENCING_COL_NAME=null; REF_GENERATION=null\n", JdbcTest.toString(metaData.getTables(null, "adhoc", null, new String[] { Schema.TableType.VIEW.name() }))); return null; } catch (SQLException e) { throw new RuntimeException(e); } } }); }
From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java
/** * Check if LoggingData table exists/* w w w .j a v a 2 s . c om*/ * * @return */ private boolean CheckIfDatabaseTablesExists() { Connection conn = null; boolean flag = false; try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password); DatabaseMetaData meta = conn.getMetaData(); ResultSet res = meta.getTables(null, null, null, new String[] { "TABLE" }); while (res.next()) { if (res.getString("TABLE_TYPE").equalsIgnoreCase("TABLE")) { if (res.getString("TABLE_NAME").equalsIgnoreCase("MetricData")) { flag = true; break; } } } res.close(); conn.close(); } catch (ClassNotFoundException e) { System.err.println(e.getClass().getName() + ": " + e.getMessage()); e.printStackTrace(); } catch (SQLException e) { System.err.println(e.getClass().getName() + ": " + e.getMessage()); e.printStackTrace(); } return flag; }
From source file:org.apache.jackrabbit.core.fs.db.DatabaseFileSystem.java
/** * Checks if the required schema objects exist and creates them if they * don't exist yet./* ww w . j av a 2 s .co m*/ * * @throws Exception if an error occurs */ protected void checkSchema() throws Exception { DatabaseMetaData metaData = con.getMetaData(); String tableName = schemaObjectPrefix + "FSENTRY"; if (metaData.storesLowerCaseIdentifiers()) { tableName = tableName.toLowerCase(); } else if (metaData.storesUpperCaseIdentifiers()) { tableName = tableName.toUpperCase(); } ResultSet rs = metaData.getTables(null, null, tableName, null); boolean schemaExists; try { schemaExists = rs.next(); } finally { rs.close(); } if (!schemaExists) { // read ddl from resources InputStream in = DatabaseFileSystem.class.getResourceAsStream(schema + ".ddl"); if (in == null) { String msg = "Configuration error: unknown schema '" + schema + "'"; log.debug(msg); throw new RepositoryException(msg); } BufferedReader reader = new BufferedReader(new InputStreamReader(in)); Statement stmt = con.createStatement(); try { String sql = reader.readLine(); while (sql != null) { // Skip comments and empty lines if (!sql.startsWith("#") && sql.length() > 0) { // replace prefix variable sql = createSchemaSql(sql); stmt.executeUpdate(sql); } // read next sql stmt sql = reader.readLine(); } } finally { IOUtils.closeQuietly(in); closeStatement(stmt); } } }
From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java
private void addTables(DatabaseMetaData dbMeta, PhysicalModel model, List<String> selectedTables) { boolean filterTable = false; if (selectedTables != null) { filterTable = true;/*from www . j av a 2s . c om*/ } Map<String, PhysicalTable> tabesLookupMap; ResultSet tableRs, columnRs; PhysicalTable table; List<PhysicalPrimaryKey> primaryKeys; PhysicalPrimaryKey primaryKey; List<PhysicalForeignKey> foreignKeys; PhysicalForeignKey foreignKey; try { tableRs = dbMeta.getTables(model.getCatalog(), model.getSchema(), null, new String[] { "TABLE", "VIEW" }); /* * -------------------------------------------------- resultset's structure -------------------------------------------------- 1. TABLE_CAT String * => table catalog (may be null) 2. TABLE_SCHEM String => table schema (may be null) 3. TABLE_NAME String => table name * * Data Warehouse Management Model 181 4. TABLE_TYPE String => table type. Typical types are TABLE, VIEW, SYSTEM TABLE,GLOBAL TEMPORARY, * LOCAL TEMPORARY, ALIAS, SYNONYM. 5. REMARKS String => explanatory comment on the table 6. TYPE_CAT String => the types catalog (may be * null) 7. TYPE_SCHEM String => the types schema (may be null) 8. TYPE_NAME String => type name (may be null) 9. SELF_REFERENCING_COL_NAME String * => name of the designated identifier column of a typed table (may be null) 10. REF_GENERATION String => specifies how values in * SELF_REFERENCING_COL_NAME are created. Values are SYSTEM, USER, DERIVED. (may be null) */ while (tableRs.next()) { if ((!filterTable) || ((selectedTables != null) && (selectedTables.contains(tableRs.getString("TABLE_NAME"))))) { table = FACTORY.createPhysicalTable(); table.setModel(model); table.setName(tableRs.getString("TABLE_NAME")); table.setComment(getEscapedMetadataPropertyValue(tableRs, "REMARKS")); table.setType(tableRs.getString("TABLE_TYPE")); log("Table: " + table.getName() + "[" + table.getType() + "]"); initColumnsMeta(dbMeta, model, table); model.getTables().add(table); getPropertiesInitializer().addProperties(table); } } tableRs.close(); } catch (Throwable t) { throw new RuntimeException("Impossible to initialize tables metadata", t); } }
From source file:it.unibas.spicy.persistence.relational.DAORelational.java
public void loadInstance(int scenarioNo, AccessConfiguration accessConfiguration, IDataSourceProxy dataSource, DBFragmentDescription dataDescription, IConnectionFactory dataSourceDB, boolean source) throws DAOException, SQLException { Connection connection = dataSourceDB.getConnection(accessConfiguration); DatabaseMetaData databaseMetaData = null; String catalog = null;/*from w ww . ja va2s . c om*/ String schemaName = accessConfiguration.getSchemaName(); Connection connectionPostgres = null; this.dataDescription = dataDescription; AccessConfiguration accessConfigurationPostgres = new AccessConfiguration(); accessConfigurationPostgres.setDriver(SpicyEngineConstants.ACCESS_CONFIGURATION_DRIVER); accessConfigurationPostgres .setUri(SpicyEngineConstants.ACCESS_CONFIGURATION_URI + SpicyEngineConstants.MAPPING_TASK_DB_NAME); accessConfigurationPostgres.setLogin(SpicyEngineConstants.ACCESS_CONFIGURATION_LOGIN); accessConfigurationPostgres.setPassword(SpicyEngineConstants.ACCESS_CONFIGURATION_PASS); connectionPostgres = dataSourceDB.getConnection(accessConfigurationPostgres); try { databaseMetaData = connection.getMetaData(); catalog = connection.getCatalog(); String[] tableTypes = new String[] { "TABLE" }; ResultSet tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, tableTypes); Statement statement = connection.createStatement(); Statement statementPostgres = connectionPostgres.createStatement(); while (tableResultSet.next()) { String tableName = tableResultSet.getString("TABLE_NAME"); if (!this.dataDescription.checkLoadTable(tableName)) { continue; } String tablePath = tableName; if (!schemaName.equals("")) { tablePath = schemaName + ".\"" + tableName + "\""; } String newTablePath = tableName; if (source) { newTablePath = SpicyEngineConstants.SOURCE_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } else { newTablePath = SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\""; } ResultSet countResult = statement .executeQuery("SELECT COUNT(*) AS instancesCount FROM " + tablePath + ";"); int instancesCount = 1; while (countResult.next()) { instancesCount = countResult.getInt("instancesCount"); } ResultSet pKList = null; pKList = databaseMetaData.getPrimaryKeys(null, null, tableName); // ResultSet pKList = statement.executeQuery("SELECT c.column_name as keyname\n" + "FROM information_schema.key_column_usage AS c\n" + // "LEFT JOIN information_schema.table_constraints AS t\n" + // "ON t.constraint_name = c.constraint_name\n" + // "WHERE t.table_name = '" + tablePath + "' AND t.constraint_type = 'PRIMARY KEY';"); String pKListString = ""; while (pKList.next()) { pKListString += pKList.getString("COLUMN_NAME") + ","; } if (pKListString != "") pKListString = pKListString.substring(0, pKListString.length() - 1); int inCount = 0; String viewName = tableName + "_MIPMapView"; String orderByClause = ""; if (pKListString != "") orderByClause = " ORDER BY " + pKListString; statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); statement.executeUpdate( "CREATE VIEW " + viewName + " AS SELECT * FROM " + tablePath + orderByClause + ";"); for (int i = 0; i <= ((instancesCount - 1) / BATCH_SIZE); i++) { ResultSet instancesSet = statement.executeQuery("SELECT * FROM " + viewName + " LIMIT " + BATCH_SIZE + " OFFSET " + (BATCH_SIZE * i) + ";"); ResultSetMetaData rsmd = instancesSet.getMetaData(); int columnsNumber = rsmd.getColumnCount(); String sql_insert_stmnt = ""; while (instancesSet.next()) { String tmp_sql_insert_stmnt = "("; for (int j = 1; j <= columnsNumber; j++) { String columnValue = instancesSet.getString(j); if (columnValue == null) { tmp_sql_insert_stmnt += " null,"; } else { if (isTextColumn(rsmd.getColumnTypeName(j))) { tmp_sql_insert_stmnt += "'" + columnValue.replaceAll("'", "''") + "',"; } else { tmp_sql_insert_stmnt += "" + columnValue + ","; } } } //take out the last ',' character tmp_sql_insert_stmnt = tmp_sql_insert_stmnt.substring(0, tmp_sql_insert_stmnt.length() - 1); tmp_sql_insert_stmnt += "),"; // if (!inserted.contains(tmp_sql_insert_stmnt)) { sql_insert_stmnt += tmp_sql_insert_stmnt; // inserted.add(tmp_sql_insert_stmnt); // } } if (!sql_insert_stmnt.equals("")) { //take out the last ',' character sql_insert_stmnt = sql_insert_stmnt.substring(0, sql_insert_stmnt.length() - 1); inCount += statementPostgres .executeUpdate("insert into " + newTablePath + " values " + sql_insert_stmnt + ";"); } } statement.executeUpdate("DROP VIEW IF EXISTS " + viewName + ";"); } dataSource.addAnnotation(SpicyEngineConstants.LOADED_INSTANCES_FLAG, true); } finally { if (connection != null) dataSourceDB.close(connection); if (connectionPostgres != null) dataSourceDB.close(connectionPostgres); } }
From source file:org.apache.jackrabbit.core.persistence.bundle.BundleDbPersistenceManager.java
/** * Checks if the database table exist.//from w w w .ja v a2 s.com * * @return <code>true</code> if the tables exist; * <code>false</code> otherwise. * * @throws SQLException if a database error occurs. * @throws RepositoryException if a repository exception occurs. */ protected boolean checkTablesExist() throws SQLException, RepositoryException { DatabaseMetaData metaData = connectionManager.getConnection().getMetaData(); String tableName = schemaObjectPrefix + "BUNDLE"; if (metaData.storesLowerCaseIdentifiers()) { tableName = tableName.toLowerCase(); } else if (metaData.storesUpperCaseIdentifiers()) { tableName = tableName.toUpperCase(); } String userName = checkTablesWithUser() ? metaData.getUserName() : null; ResultSet rs = metaData.getTables(null, userName, tableName, null); try { return rs.next(); } finally { rs.close(); } }
From source file:org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.java
public boolean isTablePresent(String tableName) { // ACT-1610: in case the prefix IS the schema itself, we don't add the // prefix, since the check is already aware of the schema DbSqlSession dbSqlSession = getDbSqlSession(); DbSqlSessionFactory dbSqlSessionFactory = dbSqlSession.getDbSqlSessionFactory(); if (!dbSqlSession.getDbSqlSessionFactory().isTablePrefixIsSchema()) { tableName = prependDatabaseTablePrefix(tableName); }/*from w w w. j a v a 2s. co m*/ Connection connection = null; try { connection = dbSqlSession.getSqlSession().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); ResultSet tables = null; String catalog = dbSqlSession.getConnectionMetadataDefaultCatalog(); if (dbSqlSessionFactory.getDatabaseCatalog() != null && dbSqlSessionFactory.getDatabaseCatalog().length() > 0) { catalog = dbSqlSessionFactory.getDatabaseCatalog(); } String schema = dbSqlSession.getConnectionMetadataDefaultSchema(); if (dbSqlSessionFactory.getDatabaseSchema() != null && dbSqlSessionFactory.getDatabaseSchema().length() > 0) { schema = dbSqlSessionFactory.getDatabaseSchema(); } else if (dbSqlSessionFactory.isTablePrefixIsSchema() && StringUtils.isNotEmpty(dbSqlSessionFactory.getDatabaseTablePrefix())) { schema = dbSqlSessionFactory.getDatabaseTablePrefix(); if (StringUtils.isNotEmpty(schema) && schema.endsWith(".")) { schema = schema.substring(0, schema.length() - 1); } } String databaseType = dbSqlSessionFactory.getDatabaseType(); if ("postgres".equals(databaseType)) { tableName = tableName.toLowerCase(); } if (schema != null && "oracle".equals(databaseType)) { schema = schema.toUpperCase(); } if (catalog != null && catalog.length() == 0) { catalog = null; } try { tables = databaseMetaData.getTables(catalog, schema, tableName, JDBC_METADATA_TABLE_TYPES); return tables.next(); } finally { try { if (tables != null) { tables.close(); } } catch (Exception e) { logger.error("Error closing meta data tables", e); } } } catch (Exception e) { throw new FlowableException( "couldn't check if tables are already present using metadata: " + e.getMessage(), e); } }
From source file:org.getobjects.eoaccess.EOAdaptorChannel.java
public String[] describeTableNames() { DatabaseMetaData meta = this.fetchDatabaseMetaData(); if (meta == null) return null; this.lastException = null; /* fetch table names */ String[] tableNames = null;/* w ww.j a va 2s . c om*/ try { ResultSet rs = meta.getTables(null /* catalog */, null /* schema */, "%" /* tables */, tableTypes /* types */); /* loop over results and convert them to records */ List<String> lNames = new ArrayList<String>(64); while (rs.next()) lNames.add(rs.getString(3 /* TABLE_NAME */)); tableNames = lNames.toArray(new String[lNames.size()]); } catch (SQLException e) { this.lastException = e; } return tableNames; }