List of usage examples for java.sql DatabaseMetaData getTables
ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
throws SQLException;
From source file:org.apache.oozie.command.SchemaCheckXCommand.java
private boolean checkTables(DatabaseMetaData metaData, String catalog, final Collection<String> expectedTablesRaw) throws SQLException { boolean problem = false; Set<String> expectedTables = new HashSet<String>(expectedTablesRaw); expectedTables.add(caseTableName("oozie_sys")); expectedTables.add(caseTableName("openjpa_sequence_table")); expectedTables.add(caseTableName("validate_conn")); // Oracle returns > 1000 tables if we don't have the schema "OOZIE"; MySQL and Postgres don't want this String schema = null;/*w w w. j a va 2 s. c o m*/ if (dbType.equals("oracle")) { schema = "OOZIE"; } ResultSet rs = metaData.getTables(catalog, schema, null, new String[] { "TABLE" }); Set<String> foundTables = new HashSet<String>(); while (rs.next()) { String tabName = rs.getString("TABLE_NAME"); if (tabName != null) { foundTables.add(tabName); } } Collection missingTables = CollectionUtils.subtract(expectedTables, foundTables); if (!missingTables.isEmpty()) { LOG.error("Found [{0}] missing tables: {1}", missingTables.size(), Arrays.toString(missingTables.toArray())); problem = true; } else if (LOG.isDebugEnabled()) { LOG.debug("No missing tables found: {0}", Arrays.toString(expectedTables.toArray())); } if (!ignoreExtras) { Collection extraTables = CollectionUtils.subtract(foundTables, expectedTables); if (!extraTables.isEmpty()) { LOG.error("Found [{0}] extra tables: {1}", extraTables.size(), Arrays.toString(extraTables.toArray())); problem = true; } else { LOG.debug("No extra tables found"); } } return problem; }
From source file:com.googlecode.fascinator.portal.HouseKeeper.java
/** * Check if the given table exists in the database. * /*from www . ja v a2s .co m*/ * @param table The table to look for * @return boolean flag if the table was found or not * @throws SQLException if there was an error accessing the database */ private boolean findTable(String table) throws SQLException { boolean tableFound = false; DatabaseMetaData meta = dbConnection().getMetaData(); ResultSet result = meta.getTables(null, null, null, null); while (result.next() && !tableFound) { if (result.getString("TABLE_NAME").equalsIgnoreCase(table)) { tableFound = true; } } close(result); return tableFound; }
From source file:net.certifi.audittablegen.GenericDMR.java
@Override public void setAuditConfigTableName(String unverifiedTable) { this.unverifiedAuditConfigTable = unverifiedTable; this.verifiedAuditConfigTable = null; String candidate = null;/*from ww w .j ava 2s .c om*/ boolean multiMatch = false; if (unverifiedAuditConfigTable == null) { return; } if (null == verifiedSchema) { logger.error("attempting to verify auditConfigTable with unverified schema"); } try (Connection conn = dataSource.getConnection()) { DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getTables(null, null == verifiedSchema ? null : verifiedSchema, null, null); while (rs.next()) { if (rs.getString("TABLE_NAME").trim().equalsIgnoreCase(unverifiedTable)) { //store value with whatever case sensitivity it is returned as if (candidate == null) { candidate = rs.getString("TABLE_NAME").trim(); } else { multiMatch = true; } } } rs.close(); } catch (SQLException e) { logger.error("error verifying auditConfigTable", e); } /** Fails to set verified value if more than one match. * This can occur if schema is not set and there are multiple * tables in different schemas matching the table name. */ if (!multiMatch) { this.verifiedAuditConfigTable = candidate; } }
From source file:net.certifi.audittablegen.GenericDMR.java
/** * Get List of TableDef objects for all tables * in the targeted database/schema/* ww w . j a v a2 s. c o m*/ * * @return ArrayList of TableDef objects or an empty list if none are found. */ @Override public List getTables() { List<TableDef> tables = new ArrayList<>(); try (Connection conn = dataSource.getConnection()) { DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getTables(null, verifiedSchema, null, new String[] { "TABLE" }); while (rs.next()) { TableDef tableDef = new TableDef(); tableDef.setName(rs.getString("TABLE_NAME").trim()); tables.add(tableDef); // //ToDo: handle case where table full name matches the prefix or postfi // if ( table.toUpperCase().startsWith(configSource.getTablePrefix().toUpperCase()) // && table.toUpperCase().endsWith(configSource.getTablePostfix().toUpperCase())){ // configSource.addExistingAuditTable(table); // } // else { // configSource.ensureTableConfig(table); // // //just in case audit config has set up the table with the // //wrong case sensitivity, update the table name with the // //value returned from the db // TableConfig tc = configSource.getTableConfig(table); // tc.setTableName(table); // } } rs.close(); } catch (SQLException e) { logger.error("SQL error retrieving table list: ", e); return null; } for (TableDef tableDef : tables) { tableDef.setColumns(getColumns(tableDef.getName())); } return tables; }
From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java
public ContentSourceSpecs init(WGDatabase db, String path) throws WGInvalidDatabaseException { Map creationOptions = db.getCreationOptions(); // Try to get shared connection pool from server boolean useSharedPool = WGUtils.getBooleanMapValue(db.getCreationOptions(), WGDatabase.COPTION_SHAREDPOOL, true);/*from ww w .ja v a 2 s.c o m*/ if (useSharedPool && db.getCreationOptions().containsKey(Database.OPTION_PATH) && db.getServer() instanceof SharedPoolJDBCDatabaseServer) { SharedPoolJDBCDatabaseServer poolServer = (SharedPoolJDBCDatabaseServer) db.getServer(); if (poolServer.isPoolAvailable(new CSVersion(0, 0))) { try { _connProvider = poolServer.createPoolConnectionProvider( (String) db.getCreationOptions().get(Database.OPTION_PATH)); WGFactory.getLogger() .info("Database '" + db.getDbReference() + "' uses the shared connection pool of database server '" + db.getServer().getTitle(Locale.getDefault()) + "'"); } catch (WGInvalidDatabaseException e) { throw e; } catch (Exception e) { throw new WGInvalidDatabaseException("Exception connecting to shared database server pool", e); } } } // Create regular pool if (_connProvider == null) { // Default JDBC props Properties props = new Properties(); props.put("autocommit", "true"); // Gather configured JDBC props Iterator optsIt = creationOptions.keySet().iterator(); while (optsIt.hasNext()) { String option = (String) optsIt.next(); if (option.startsWith(("jdbc."))) { props.put(option.substring(5), creationOptions.get(option)); } } // Set login props if (db.getMasterLoginName() != null && !db.getMasterLoginName().equals("")) { props.put("user", db.getMasterLoginName()); if (db.getMasterLoginPassword() != null) { props.put("password", db.getMasterLoginPassword()); } } // Set dbkey property so we see DBCP metrics via JMX props.put("dbcp.dbkey", db.getDbReference()); // Build JDBC Connection Creator try { _connProvider = new JDBCConnectionProvider(path, (String) db.getCreationOptions().get(COPTION_DRIVER), props, true); } catch (JDBCConnectionException e3) { throw new WGInvalidDatabaseException("Exception setting up JDBC connection", e3); } } // Gather other options try { if (creationOptions.containsKey("ResultSetType")) { _resultSetType = Integer.parseInt((String) creationOptions.get("ResultSetType")); } } catch (NumberFormatException e2) { throw new WGInvalidDatabaseException( "Cannot parse db option 'ResultSetType' as integer: " + _resultSetType); } // Gather meta data try { Connection connection = getConnection(); if (connection == null) { throw new WGInvalidDatabaseException("Unable to get connection"); } DatabaseMetaData dbMeta = connection.getMetaData(); ResultSet resultSet = dbMeta.getTables(null, null, null, new String[] { "TABLE", "VIEW", "GLOBAL TEMPORARY", "LOCAL TEMPORARY" }); startResultSet(resultSet); while (resultSet.next()) { TableName tableName = new TableName(resultSet); ResultSet keyResultSet = dbMeta.getPrimaryKeys(tableName.getCatalog(), tableName.getSchema(), tableName.getName()); List keyColumns = new ArrayList(); startResultSet(keyResultSet); while (keyResultSet.next()) { keyColumns.add(keyResultSet.getString("COLUMN_NAME").toLowerCase()); } if (keyColumns.size() > 0) { _tables.put(tableName.getCompleteName().toLowerCase(), keyColumns); } keyResultSet.close(); } resultSet.close(); _server = dbMeta.getDatabaseProductName() + " Version " + dbMeta.getDatabaseProductVersion(); _title = _server; } catch (SQLException e) { throw new WGInvalidDatabaseException( "SQL Error building connection to path " + path + ": " + e.getMessage()); } // Last changed update process int refreshSeconds = 60; if (creationOptions.containsKey(COPTION_REFRESH)) { try { refreshSeconds = Integer.parseInt((String) creationOptions.get(COPTION_REFRESH)); } catch (NumberFormatException e1) { Logger.getLogger(LOGGER_NAME).error("Cannot parse option " + COPTION_REFRESH + " as integer: " + creationOptions.get(COPTION_REFRESH)); } } // Gather specs ContentSourceSpecs specs = new ContentSourceSpecs(); specs.setBrowseable(true); specs.setWritable(true); specs.setCalculatesKeys(true); specs.setMaintainsLastChanged(false); specs.setLowerCaseItems(true); specs.setServePropertiesAsMetas(false); specs.setContentReadProtected(false); return specs; }
From source file:org.apache.kylin.rest.service.QueryService.java
@SuppressWarnings("checkstyle:methodlength") protected List<TableMetaWithType> getMetadataV2(CubeManager cubeMgr, String project, boolean cubedOnly) throws SQLException, IOException { //Message msg = MsgPicker.getMsg(); Connection conn = null;/*from ww w . jav a2s. co m*/ ResultSet columnMeta = null; List<TableMetaWithType> tableMetas = null; Map<String, TableMetaWithType> tableMap = null; Map<String, ColumnMetaWithType> columnMap = null; if (StringUtils.isBlank(project)) { return Collections.emptyList(); } ResultSet JDBCTableMeta = null; try { DataSource dataSource = cacheService.getOLAPDataSource(project); conn = dataSource.getConnection(); DatabaseMetaData metaData = conn.getMetaData(); JDBCTableMeta = metaData.getTables(null, null, null, null); tableMetas = new LinkedList<TableMetaWithType>(); tableMap = new HashMap<String, TableMetaWithType>(); columnMap = new HashMap<String, ColumnMetaWithType>(); while (JDBCTableMeta.next()) { String catalogName = JDBCTableMeta.getString(1); String schemaName = JDBCTableMeta.getString(2); // Not every JDBC data provider offers full 10 columns, e.g., PostgreSQL has only 5 TableMetaWithType tblMeta = new TableMetaWithType( catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, JDBCTableMeta.getString(3), JDBCTableMeta.getString(4), JDBCTableMeta.getString(5), null, null, null, null, null); if (!cubedOnly || getProjectManager().isExposedTable(project, schemaName + "." + tblMeta.getTABLE_NAME())) { tableMetas.add(tblMeta); tableMap.put(tblMeta.getTABLE_SCHEM() + "#" + tblMeta.getTABLE_NAME(), tblMeta); } } columnMeta = metaData.getColumns(null, null, null, null); while (columnMeta.next()) { String catalogName = columnMeta.getString(1); String schemaName = columnMeta.getString(2); // kylin(optiq) is not strictly following JDBC specification ColumnMetaWithType colmnMeta = new ColumnMetaWithType( catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, columnMeta.getString(3), columnMeta.getString(4), columnMeta.getInt(5), columnMeta.getString(6), columnMeta.getInt(7), getInt(columnMeta.getString(8)), columnMeta.getInt(9), columnMeta.getInt(10), columnMeta.getInt(11), columnMeta.getString(12), columnMeta.getString(13), getInt(columnMeta.getString(14)), getInt(columnMeta.getString(15)), columnMeta.getInt(16), columnMeta.getInt(17), columnMeta.getString(18), columnMeta.getString(19), columnMeta.getString(20), columnMeta.getString(21), getShort(columnMeta.getString(22)), columnMeta.getString(23)); if (!cubedOnly || getProjectManager().isExposedColumn(project, schemaName + "." + colmnMeta.getTABLE_NAME(), colmnMeta.getCOLUMN_NAME())) { tableMap.get(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME()).addColumn(colmnMeta); columnMap.put(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME() + "#" + colmnMeta.getCOLUMN_NAME(), colmnMeta); } } } finally { close(columnMeta, null, conn); if (JDBCTableMeta != null) { JDBCTableMeta.close(); } } ProjectInstance projectInstance = getProjectManager().getProject(project); for (String modelName : projectInstance.getModels()) { DataModelDesc dataModelDesc = modelService.listAllModels(modelName, project, true).get(0); if (!dataModelDesc.isDraft()) { // update table type: FACT for (TableRef factTable : dataModelDesc.getFactTables()) { String factTableName = factTable.getTableIdentity().replace('.', '#'); if (tableMap.containsKey(factTableName)) { tableMap.get(factTableName).getTYPE().add(TableMetaWithType.tableTypeEnum.FACT); } else { // should be used after JDBC exposes all tables and columns // throw new BadRequestException(msg.getTABLE_META_INCONSISTENT()); } } // update table type: LOOKUP for (TableRef lookupTable : dataModelDesc.getLookupTables()) { String lookupTableName = lookupTable.getTableIdentity().replace('.', '#'); if (tableMap.containsKey(lookupTableName)) { tableMap.get(lookupTableName).getTYPE().add(TableMetaWithType.tableTypeEnum.LOOKUP); } else { // throw new BadRequestException(msg.getTABLE_META_INCONSISTENT()); } } // update column type: PK and FK for (JoinTableDesc joinTableDesc : dataModelDesc.getJoinTables()) { JoinDesc joinDesc = joinTableDesc.getJoin(); for (String pk : joinDesc.getPrimaryKey()) { String columnIdentity = (dataModelDesc.findTable(pk.substring(0, pk.indexOf("."))) .getTableIdentity() + pk.substring(pk.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.PK); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } for (String fk : joinDesc.getForeignKey()) { String columnIdentity = (dataModelDesc.findTable(fk.substring(0, fk.indexOf("."))) .getTableIdentity() + fk.substring(fk.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.FK); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } // update column type: DIMENSION AND MEASURE List<ModelDimensionDesc> dimensions = dataModelDesc.getDimensions(); for (ModelDimensionDesc dimension : dimensions) { for (String column : dimension.getColumns()) { String columnIdentity = (dataModelDesc.findTable(dimension.getTable()).getTableIdentity() + "." + column).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE() .add(ColumnMetaWithType.columnTypeEnum.DIMENSION); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } String[] measures = dataModelDesc.getMetrics(); for (String measure : measures) { String columnIdentity = (dataModelDesc.findTable(measure.substring(0, measure.indexOf("."))) .getTableIdentity() + measure.substring(measure.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.MEASURE); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } } return tableMetas; }
From source file:org.glite.security.voms.admin.persistence.deployer.SchemaDeployer.java
private ResultSet getTableNamesMatchingPattern(DatabaseMetaData md, String pattern) { String[] names = { "TABLE" }; ResultSet tableNames = null;/* w w w. j a va 2 s .c o m*/ try { tableNames = md.getTables(null, "%", pattern, names); } catch (SQLException e) { log.error("Error reading table names from database metadata object!", e); System.exit(-1); } return tableNames; }
From source file:pingpong.db.DBAccess.java
/** * DataBase? Table? ?./*www . ja va 2 s .co m*/ * They are ordered by TABLE_TYPE, TABLE_SCHEM and TABLE_NAME * @return * @throws SQLException * @see {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[])} */ public TResultSet getTables(String tableNamePattern, String[] types) throws SQLException { Connection con = getConnection(); DatabaseMetaData dbMeta = con.getMetaData(); TResultSet rs = new TResultSet(dbMeta.getTables(null, this.getSchema(), tableNamePattern, types)); // TResultSet rs = new TResultSet(dbMeta.getTables(null, "SESSION", tableNamePattern, types)); // TResultSet rs = new TResultSet(dbMeta.getTableTypes()); return rs; }
From source file:io.bibleget.BibleGetDB.java
public boolean initialize() { try {/* w ww .j a v a 2s . c om*/ instance.conn = DriverManager.getConnection("jdbc:derby:BIBLEGET;create=true", "bibleget", "bibleget"); if (instance.conn == null) { System.out.println("Careful there! Connection not established! BibleGetDB.java line 81"); } else { System.out.println("conn is not null, which means a connection was correctly established."); } DatabaseMetaData dbMeta; dbMeta = instance.conn.getMetaData(); try (ResultSet rs1 = dbMeta.getTables(null, null, "OPTIONS", null)) { if (rs1.next()) { //System.out.println("Table "+rs1.getString("TABLE_NAME")+" already exists !!"); listColNamesTypes(dbMeta, rs1); } else { //System.out.println("Table OPTIONS does not yet exist, now attempting to create..."); try (Statement stmt = instance.conn.createStatement()) { String defaultFont = ""; if (SystemUtils.IS_OS_WINDOWS) { defaultFont = "Times New Roman"; } else if (SystemUtils.IS_OS_MAC_OSX) { defaultFont = "Helvetica"; } else if (SystemUtils.IS_OS_LINUX) { defaultFont = "Arial"; } String tableCreate = "CREATE TABLE OPTIONS (" + "PARAGRAPHALIGNMENT VARCHAR(15), " + "PARAGRAPHLINESPACING INT, " + "PARAGRAPHFONTFAMILY VARCHAR(50), " + "PARAGRAPHLEFTINDENT INT, " + "TEXTCOLORBOOKCHAPTER VARCHAR(15), " + "BGCOLORBOOKCHAPTER VARCHAR(15), " + "BOLDBOOKCHAPTER BOOLEAN, " + "ITALICSBOOKCHAPTER BOOLEAN, " + "UNDERSCOREBOOKCHAPTER BOOLEAN, " + "FONTSIZEBOOKCHAPTER INT, " + "VALIGNBOOKCHAPTER VARCHAR(15), " + "TEXTCOLORVERSENUMBER VARCHAR(15), " + "BGCOLORVERSENUMBER VARCHAR(15), " + "BOLDVERSENUMBER BOOLEAN, " + "ITALICSVERSENUMBER BOOLEAN, " + "UNDERSCOREVERSENUMBER BOOLEAN, " + "FONTSIZEVERSENUMBER INT, " + "VALIGNVERSENUMBER VARCHAR(15), " + "TEXTCOLORVERSETEXT VARCHAR(15), " + "BGCOLORVERSETEXT VARCHAR(15), " + "BOLDVERSETEXT BOOLEAN, " + "ITALICSVERSETEXT BOOLEAN, " + "UNDERSCOREVERSETEXT BOOLEAN, " + "FONTSIZEVERSETEXT INT, " + "VALIGNVERSETEXT VARCHAR(15), " + "PREFERREDVERSIONS VARCHAR(50), " + "NOVERSIONFORMATTING BOOLEAN" + ")"; String tableInsert; tableInsert = "INSERT INTO OPTIONS (" + "PARAGRAPHALIGNMENT," + "PARAGRAPHLINESPACING," + "PARAGRAPHFONTFAMILY," + "PARAGRAPHLEFTINDENT," + "TEXTCOLORBOOKCHAPTER," + "BGCOLORBOOKCHAPTER," + "BOLDBOOKCHAPTER," + "ITALICSBOOKCHAPTER," + "UNDERSCOREBOOKCHAPTER," + "FONTSIZEBOOKCHAPTER," + "VALIGNBOOKCHAPTER," + "TEXTCOLORVERSENUMBER," + "BGCOLORVERSENUMBER," + "BOLDVERSENUMBER," + "ITALICSVERSENUMBER," + "UNDERSCOREVERSENUMBER," + "FONTSIZEVERSENUMBER," + "VALIGNVERSENUMBER," + "TEXTCOLORVERSETEXT," + "BGCOLORVERSETEXT," + "BOLDVERSETEXT," + "ITALICSVERSETEXT," + "UNDERSCOREVERSETEXT," + "FONTSIZEVERSETEXT," + "VALIGNVERSETEXT," + "PREFERREDVERSIONS, " + "NOVERSIONFORMATTING" + ") VALUES (" + "'justify',100,'" + defaultFont + "',0," + "'#0000FF','#FFFFFF',true,false,false,14,'initial'," + "'#AA0000','#FFFFFF',false,false,false,10,'super'," + "'#696969','#FFFFFF',false,false,false,12,'initial'," + "'NVBSE'," + "false" + ")"; boolean tableCreated = stmt.execute(tableCreate); boolean rowsInserted; int count; if (tableCreated == false) { //is false when it's an update count! count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: 0 rows affected //System.out.println("The Table Creation statement produced results: "+count+" rows affected."); try (Statement stmt2 = instance.conn.createStatement()) { rowsInserted = stmt2.execute(tableInsert); if (rowsInserted == false) { count = stmt2.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: n rows affected //System.out.println("The Row Insertion statement produced results: "+count+" rows affected."); dbMeta = instance.conn.getMetaData(); try (ResultSet rs2 = dbMeta.getTables(null, null, "OPTIONS", null)) { if (rs2.next()) { listColNamesTypes(dbMeta, rs2); } rs2.close(); } } } else { //is true when it returns a resultset, which shouldn't be the case here try (ResultSet rx = stmt2.getResultSet()) { while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } rx.close(); } } stmt2.close(); } } } else { //is true when it returns a resultset, which shouldn't be the case here try (ResultSet rx = stmt.getResultSet()) { while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } rx.close(); } } stmt.close(); } } rs1.close(); } //System.out.println("Finished with first ResultSet resource, now going on to next..."); try (ResultSet rs3 = dbMeta.getTables(null, null, "METADATA", null)) { if (rs3.next()) { //System.out.println("Table "+rs3.getString("TABLE_NAME")+" already exists !!"); } else { //System.out.println("Table METADATA does not exist, now attempting to create..."); try (Statement stmt = instance.conn.createStatement()) { String tableCreate = "CREATE TABLE METADATA ("; tableCreate += "ID INT, "; for (int i = 0; i < 73; i++) { tableCreate += "BIBLEBOOKS" + Integer.toString(i) + " VARCHAR(2000), "; } tableCreate += "LANGUAGES VARCHAR(500), "; tableCreate += "VERSIONS VARCHAR(2000)"; tableCreate += ")"; boolean tableCreated = stmt.execute(tableCreate); boolean rowsInserted; int count; if (tableCreated == false) { //this is the expected result, is false when it's an update count! count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: 0 rows affected //System.out.println("The Table Creation statement produced results: "+count+" rows affected."); //Insert a dummy row, because you cannot update what has not been inserted! try (Statement stmtX = instance.conn.createStatement()) { stmtX.execute("INSERT INTO METADATA (ID) VALUES (0)"); stmtX.close(); } HTTPCaller myHTTPCaller = new HTTPCaller(); String myResponse; myResponse = myHTTPCaller.getMetaData("biblebooks"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonArray arrayJson = json.getJsonArray("results"); if (arrayJson != null) { ListIterator pIterator = arrayJson.listIterator(); while (pIterator.hasNext()) { try (Statement stmt2 = instance.conn.createStatement()) { int index = pIterator.nextIndex(); JsonArray currentJson = (JsonArray) pIterator.next(); String biblebooks_str = currentJson.toString(); //.replaceAll("\"", "\\\\\""); //System.out.println("BibleGetDB line 267: BIBLEBOOKS"+Integer.toString(index)+"='"+biblebooks_str+"'"); String stmt_str = "UPDATE METADATA SET BIBLEBOOKS" + Integer.toString(index) + "='" + biblebooks_str + "' WHERE ID=0"; try { //System.out.println("executing update: "+stmt_str); int update = stmt2.executeUpdate(stmt_str); //System.out.println("executeUpdate resulted in: "+Integer.toString(update)); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } } } arrayJson = json.getJsonArray("languages"); if (arrayJson != null) { try (Statement stmt2 = instance.conn.createStatement()) { String languages_str = arrayJson.toString(); //.replaceAll("\"", "\\\\\""); String stmt_str = "UPDATE METADATA SET LANGUAGES='" + languages_str + "' WHERE ID=0"; try { int update = stmt2.executeUpdate(stmt_str); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } } } myResponse = myHTTPCaller.getMetaData("bibleversions"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonObject objJson = json.getJsonObject("validversions_fullname"); if (objJson != null) { String bibleversions_str = objJson.toString(); //.replaceAll("\"", "\\\\\""); try (Statement stmt2 = instance.conn.createStatement()) { String stmt_str = "UPDATE METADATA SET VERSIONS='" + bibleversions_str + "' WHERE ID=0"; try { int update = stmt2.executeUpdate(stmt_str); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } Set<String> versionsabbrev = objJson.keySet(); if (!versionsabbrev.isEmpty()) { String versionsabbrev_str = ""; for (String s : versionsabbrev) { versionsabbrev_str += ("".equals(versionsabbrev_str) ? "" : ",") + s; } myResponse = myHTTPCaller .getMetaData("versionindex&versions=" + versionsabbrev_str); if (myResponse != null) { jsonReader = Json.createReader(new StringReader(myResponse)); json = jsonReader.readObject(); objJson = json.getJsonObject("indexes"); if (objJson != null) { for (String name : objJson.keySet()) { JsonObjectBuilder tempBld = Json.createObjectBuilder(); JsonObject book_num = objJson.getJsonObject(name); tempBld.add("book_num", book_num.getJsonArray("book_num")); tempBld.add("chapter_limit", book_num.getJsonArray("chapter_limit")); tempBld.add("verse_limit", book_num.getJsonArray("verse_limit")); JsonObject temp = tempBld.build(); String versionindex_str = temp.toString(); //.replaceAll("\"", "\\\\\""); //add new column to METADATA table name+"IDX" VARCHAR(5000) //update METADATA table SET name+"IDX" = versionindex_str try (Statement stmt3 = instance.conn.createStatement()) { String sql = "ALTER TABLE METADATA ADD COLUMN " + name + "IDX VARCHAR(5000)"; boolean colAdded = stmt3.execute(sql); if (colAdded == false) { count = stmt3.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else if (count == 0) { //0 rows affected stmt3.close(); try (Statement stmt4 = instance.conn .createStatement()) { String sql1 = "UPDATE METADATA SET " + name + "IDX='" + versionindex_str + "' WHERE ID=0"; boolean rowsUpdated = stmt4.execute(sql1); if (rowsUpdated == false) { count = stmt4.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //should have affected only one row if (count == 1) { //System.out.println(sql1+" seems to have returned true"); stmt4.close(); } } } else { //returns true only when returning a resultset; should not be the case here } } } } else { //returns true only when returning a resultset; should not be the case here } stmt3.close(); } } } } } } } } } else { //is true when it returns a resultset, which shouldn't be the case here ResultSet rx = stmt.getResultSet(); while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } } stmt.close(); } } rs3.close(); } instance.conn.close(); return true; } catch (SQLException ex) { if (ex.getSQLState().equals("X0Y32")) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.INFO, null, "Table OPTIONS or Table METADATA already exists. No need to recreate"); return true; } else if (ex.getNextException().getErrorCode() == 45000) { //this means we already have a connection, so this is good too return true; } else { //Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex.getMessage() + " : " + Arrays.toString(ex.getStackTrace())); Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); return false; } } }
From source file:com.glaf.core.util.DBUtils.java
public static List<String> getTables(Connection connection) { List<String> tables = new java.util.ArrayList<String>(); String[] types = { "TABLE" }; try {/* w ww . ja v a2 s. c o m*/ DatabaseMetaData metaData = connection.getMetaData(); ResultSet rs = metaData.getTables(null, null, null, types); while (rs.next()) { tables.add(rs.getObject("TABLE_NAME").toString()); } } catch (Exception ex) { throw new RuntimeException(ex); } return tables; }