List of usage examples for java.sql DatabaseMetaData getTables
ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
throws SQLException;
From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java
/** * Test the meta-data logic for the database. This should allow you to query the * schema information dynamically. Previously this was <i>Issue 40</i>. *///from ww w . java 2 s .co m @Test public void testDatabaseMetaData() throws Exception { DatabaseMetaData md = con.getMetaData(); // test various retrieval methods ResultSet result = md.getTables(con.getCatalog(), null, "%", new String[] { "TABLE" }); assertTrue("Make sure we have found a table", result.next()); result = md.getTables(null, KEYSPACE, TABLE, null); assertTrue("Make sure we have found the table asked for", result.next()); result = md.getTables(null, KEYSPACE, TABLE, new String[] { "TABLE" }); assertTrue("Make sure we have found the table asked for", result.next()); result = md.getTables(con.getCatalog(), KEYSPACE, TABLE, new String[] { "TABLE" }); assertTrue("Make sure we have found the table asked for", result.next()); // check the table name String tn = result.getString("TABLE_NAME"); assertEquals("Table name match", TABLE, tn); System.out.println("Found table via dmd : " + tn); // load the columns result = md.getColumns(con.getCatalog(), KEYSPACE, TABLE, null); assertTrue("Make sure we have found first column", result.next()); assertEquals("Make sure table name match", TABLE, result.getString("TABLE_NAME")); String cn = result.getString("COLUMN_NAME"); System.out.println("Found (default) PK column : " + cn); assertEquals("Column name check", "keyname", cn); assertEquals("Column type check", Types.VARCHAR, result.getInt("DATA_TYPE")); assertTrue("Make sure we have found second column", result.next()); cn = result.getString("COLUMN_NAME"); System.out.println("Found column : " + cn); assertEquals("Column name check", "bvalue", cn); assertEquals("Column type check", Types.BOOLEAN, result.getInt("DATA_TYPE")); assertTrue("Make sure we have found thirth column", result.next()); cn = result.getString("COLUMN_NAME"); System.out.println("Found column : " + cn); assertEquals("Column name check", "ivalue", cn); assertEquals("Column type check", Types.INTEGER, result.getInt("DATA_TYPE")); // make sure we filter result = md.getColumns(con.getCatalog(), KEYSPACE, TABLE, "bvalue"); result.next(); assertFalse("Make sure we have found requested column only", result.next()); }
From source file:de.griffel.confluence.plugins.plantuml.AbstractDatabaseStructureMacroImpl.java
private Map<String, TableDef> getTables(DatabaseMetaData dbmd) { final Map<String, TableDef> result = new HashMap<String, TableDef>(); if (_errorMessage == null) { ResultSet rs = null;/*w w w.j a va 2s . c om*/ try { rs = dbmd.getTables(null, _macroParams.getSchemaName(), _macroParams.getTableNameFilter(), null); while (rs.next()) { TableDef tmp = new TableDef(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)); result.put(tmp.getTableId(), tmp); if (log.isDebugEnabled()) { log.debug(tmp.display()); } } } catch (SQLException e) { sqlException(_macroParams.getDatasource(), e); } finally { closeResource(rs); } } return result; }
From source file:org.efaps.db.databases.PostgreSQLDatabase.java
/** * <p>This is the PostgreSQL specific implementation of an all deletion. * Following order is used to remove all eFaps specific information: * <ul>/*from w w w . j ava 2 s . c o m*/ * <li>remove all views of the user</li> * <li>remove all tables of the user</li> * <li>remove all sequences of the user</li> * </ul></p> * <p>The table are dropped with cascade, so all depending sequences etc. * are also dropped automatically. </p> * <p>Attention! If application specific tables, views or constraints are * defined, this database objects are also removed!</p> * * @param _con sql connection * @throws SQLException on error while executing sql statements */ @Override @SuppressFBWarnings("SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE") public void deleteAll(final Connection _con) throws SQLException { final Statement stmtSel = _con.createStatement(); final Statement stmtExec = _con.createStatement(); try { if (PostgreSQLDatabase.LOG.isInfoEnabled()) { PostgreSQLDatabase.LOG.info("Remove all Tables"); } final DatabaseMetaData metaData = _con.getMetaData(); // delete all views final ResultSet rsViews = metaData.getTables(null, null, "%", new String[] { "VIEW" }); while (rsViews.next()) { final String viewName = rsViews.getString("TABLE_NAME"); if (PostgreSQLDatabase.LOG.isDebugEnabled()) { PostgreSQLDatabase.LOG.debug(" - View '" + viewName + "'"); } stmtExec.execute("drop view " + viewName); } rsViews.close(); // delete all tables final ResultSet rsTables = metaData.getTables(null, null, "%", new String[] { "TABLE" }); while (rsTables.next()) { final String tableName = rsTables.getString("TABLE_NAME"); if (PostgreSQLDatabase.LOG.isDebugEnabled()) { PostgreSQLDatabase.LOG.debug(" - Table '" + tableName + "'"); } stmtExec.execute("drop table " + tableName + " cascade"); } rsTables.close(); //delete all sequences final ResultSet rsSeq = stmtSel.executeQuery("SELECT sequence_name FROM information_schema.sequences"); while (rsSeq.next()) { final String seqName = rsSeq.getString("sequence_name"); if (PostgreSQLDatabase.LOG.isDebugEnabled()) { PostgreSQLDatabase.LOG.debug(" - Sequence '" + seqName + "'"); } stmtExec.execute("drop sequence " + seqName); } rsSeq.close(); } finally { stmtSel.close(); stmtExec.close(); } }
From source file:org.jboss.dashboard.ui.panel.dataSourceManagement.DataSourceManagementHandler.java
/** * Get all tables of the datasource selected. *///from ww w . ja va 2s .c o m public List getIntrospectedTables(String datasource) throws Exception { List result = new ArrayList(); Connection connection = getConnection(); ResultSet tables = null; try { DatabaseMetaData metadata = connection.getMetaData(); String catalog = connection.getCatalog(); String[] types = { "TABLE" }; tables = metadata.getTables(catalog, null, "%", types); while (tables.next()) { String schema = tables.getString(TABLE_SCHEMA); String tableName = tables.getString(TABLE_NAME); DataSourceTableEntry dataSourceTableEntry = new DataSourceTableEntry(); dataSourceTableEntry.setDatasource(getName()); dataSourceTableEntry.setName(tableName); result.add(dataSourceTableEntry); } } finally { try { if (tables != null) tables.close(); if (connection != null) connection.close(); } catch (SQLException ignore) { } } return result; }
From source file:com.nridge.core.ds.rdbms.SQLConnection.java
/** * Returns an array of RDBMS table names associated with * an opened connection.// ww w . j a va2s.c o m * * @return An array of schema names. * * @throws NSException Catch-all exception for any SQL related issue. */ public ArrayList<String> getTableNames() throws NSException { Logger appLogger = mAppMgr.getLogger(this, "getTableNames"); appLogger.trace(mAppMgr.LOGMSG_TRACE_ENTER); mSQLStatement = StringUtils.EMPTY; ArrayList<String> tableNames = new ArrayList<String>(); try { String tableName; DatabaseMetaData dbMetaData = mConnection.getMetaData(); ResultSet rsTables = dbMetaData.getTables(null, null, null, new String[] { "TABLE" }); while (rsTables.next()) { tableName = rsTables.getString("TABLE_NAME"); if (!StringUtils.isEmpty(tableName)) tableNames.add(tableName); } rsTables.close(); } catch (SQLException e) { throw new NSException("Unable to retrieve RDBMS table names: " + e.getMessage(), e); } appLogger.trace(mAppMgr.LOGMSG_TRACE_DEPART); return tableNames; }
From source file:org.pepstock.jem.node.StartUpSystem.java
/** * Checks if the necessary tables exists on database. If not, it creates them. * @param md metadata of the database/* ww w . j a v a 2s . c o m*/ * @param manager the DB manager which contains the SQL container and all SQL statements and the table name * @throws SQLException if any error occurs cheching the existence of tables */ private static void checkAndCreateTable(DatabaseMetaData md, AbstractDBManager<?, ?> manager) throws SQLException { ResultSet rs = null; try { // gets SQL container SQLContainer container = manager.getSqlContainer(); // gets a result set which searches for the table anme rs = md.getTables(null, null, container.getTableName(), new String[] { "TABLE" }); // if result set is empty, it creates the table if (!rs.next()) { // creates table and return a empty set because if empty of // course DBPoolManager.getInstance().create(container.getCreateTableStatement()); } } finally { // if result set is not null // it closes the result set if (rs != null) { try { rs.close(); } catch (Exception e) { // ignoring any exception LogAppl.getInstance().ignore(e.getMessage(), e); } } } }
From source file:com.glaf.core.web.springmvc.MxSystemDbTableController.java
@ResponseBody @RequestMapping("/json") public byte[] json(HttpServletRequest request) throws IOException { JSONObject result = new JSONObject(); JSONArray rowsJSON = new JSONArray(); String[] types = { "TABLE" }; Connection connection = null; try {// w w w .j a va 2 s. com connection = DBConnectionFactory.getConnection(); DatabaseMetaData metaData = connection.getMetaData(); ResultSet rs = metaData.getTables(null, null, null, types); int startIndex = 1; while (rs.next()) { String tableName = rs.getObject("TABLE_NAME").toString(); if (!DBUtils.isAllowedTable(tableName)) { continue; } if (tableName.toLowerCase().startsWith("cell_useradd")) { continue; } if (tableName.toLowerCase().startsWith("sys_data_log")) { continue; } if (tableName.toLowerCase().startsWith("temp")) { continue; } if (tableName.toLowerCase().startsWith("tmp")) { continue; } if (DBUtils.isTemoraryTable(tableName)) { continue; } JSONObject json = new JSONObject(); json.put("startIndex", startIndex++); json.put("cat", rs.getObject("TABLE_CAT")); json.put("schem", rs.getObject("TABLE_SCHEM")); json.put("tablename", tableName); json.put("tableName_enc", RequestUtils.encodeString(tableName)); rowsJSON.add(json); } } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(connection); } result.put("rows", rowsJSON); result.put("total", rowsJSON.size()); return result.toJSONString().getBytes("UTF-8"); }
From source file:cz.lbenda.dataman.db.DbStructureFactory.java
public void generateStructure() { try (Connection conn = connectionProvider.getConnection()) { Map<String, CatalogDesc> catalogs = new HashMap<>(); DatabaseMetaData dmd = conn.getMetaData(); SQLDialect dialect = dbConfig.getDialect(); StatusHelper.getInstance().progressStart(this, TASK_NAME, progressStepCount); try (ResultSet tabs = dmd.getTables(null, null, null, null)) { tabs.last();//from www . j a v a 2 s . c om StatusHelper.getInstance().progressNextStep(this, STEP_READ_TABLES, tabs.getRow()); } catch (SQLException e) { StatusHelper.getInstance().progressNextStep(this, STEP_READ_TABLES, 200); } try (ResultSet tabs = dmd.getTables(null, null, null, null)) { while (tabs.next()) { StatusHelper.getInstance().progress(this); String catalogName = tabs.getString(dialect.tableCatalog()); String schemaName = tabs.getString(dialect.tableSchema()); String tableName = tabs.getString(dialect.tableName()); CatalogDesc catalogDesc = catalogs.get(catalogName); if (catalogDesc == null) { catalogDesc = new CatalogDesc(catalogName); catalogs.put(catalogDesc.getName(), catalogDesc); } SchemaDesc schema = catalogDesc.getSchema(schemaName); if (schema == null) { schema = new SchemaDesc(catalogDesc, schemaName); catalogDesc.getSchemas().add(schema); } TableDesc tableDesc = schema.getTable(tableName); if (tableDesc == null) { tableDesc = new TableDesc(schema, tabs.getString(dialect.tableType()), tableName); tableDesc.setDbConfig(dbConfig); schema.getTables().add(tableDesc); } tableDesc.setSavableRegister(connectionProvider.getSavableRegistry()); tableDesc.setComment(dialect.tableRemarks()); } generateStructureColumns(catalogs, dmd); generatePKColumns(catalogs.values(), dmd); generateStructureForeignKeys(catalogs, dmd); dbConfig.getCatalogs().clear(); if (Platform.isAccessibilityActive()) { Platform.runLater(() -> dbConfig.getCatalogs().addAll(catalogs.values())); } else { dbConfig.getCatalogs().addAll(catalogs.values()); } StatusHelper.getInstance().progressFinish(this, STEP_FINISH); } } catch (Exception e) { LOG.error("Problem with read database structure", e); throw new RuntimeException(e); } }
From source file:eionet.cr.dao.virtuoso.VirtuosoStagingDatabaseDAO.java
@Override public boolean exists(String dbName) throws DAOException { ResultSet rs = null;/* w w w. j a va 2s. c o m*/ Connection conn = null; try { conn = getSQLConnection(); DatabaseMetaData metaData = conn.getMetaData(); rs = metaData.getTables(dbName, metaData.getUserName(), null, null); return rs != null && rs.next(); } catch (SQLException e) { throw new DAOException(e.getMessage(), e); } finally { SQLUtil.close(rs); SQLUtil.close(conn); } }
From source file:org.easyrec.store.dao.web.impl.LoaderDAOMysqlImpl.java
@Override public void createDB() throws Exception { HikariDataSource bds = (HikariDataSource) getDataSource(); boolean tablesOk = false; DatabaseMetaDataCallback callback = new DatabaseMetaDataCallback() { public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException { ResultSet rs = dbmd.getTables(null, null, "operator", null); return rs.next(); }/*from w w w . j a va 2 s . c o m*/ }; tablesOk = (Boolean) JdbcUtils.extractDatabaseMetaData(bds, callback); sqlScriptService.executeSqlScript(dbCreationFile.getInputStream()); }