List of usage examples for java.sql DatabaseMetaData getColumns
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
From source file:org.nuxeo.ecm.core.storage.sql.db.H2Fulltext.java
private static int getPrimaryKeyType(DatabaseMetaData meta, String schema, String table) throws SQLException { // find primary key name String primaryKeyName = null; try (ResultSet rs = meta.getPrimaryKeys(null, schema, table)) { while (rs.next()) { if (primaryKeyName != null) { throw new SQLException("Can only index primary keys on one column for " + schema + '.' + table); }/*from w ww . j a va 2s . c om*/ primaryKeyName = rs.getString("COLUMN_NAME"); } if (primaryKeyName == null) { throw new SQLException("No primary key for " + schema + '.' + table); } } // find primary key type try (ResultSet rs = meta.getColumns(null, schema, table, primaryKeyName)) { if (!rs.next()) { throw new SQLException("Could not find primary key"); } return rs.getInt("DATA_TYPE"); } }
From source file:databaseadapter.GenerateMojo.java
protected Map<Table, List<Column>> collectColumns(Collection<Table> tables) throws MojoExecutionException { ResultSet rscolumns = null;//from ww w . jav a2 s . c o m try { Map<Table, List<Column>> map = new LinkedHashMap<Table, List<Column>>(); DatabaseMetaData metaData = connection.getMetaData(); rscolumns = metaData.getColumns(null, schemaPattern, "%", "%"); while (rscolumns.next()) { String tableName = rscolumns.getString("TABLE_NAME"); Table table = find(tables, tableName); if (table == null) continue; List<Column> columns = map.get(table); if (columns == null) { columns = new LinkedList<Column>(); map.put(table, columns); } String columnName = rscolumns.getString("COLUMN_NAME"); int dataType = rscolumns.getInt("DATA_TYPE"); int columnSize = rscolumns.getInt("COLUMN_SIZE"); int decimalDigits = rscolumns.getInt("DECIMAL_DIGITS"); int nullable = rscolumns.getInt("NULLABLE"); String remarks = rscolumns.getString("REMARKS"); Column column = new Column(columnName, dataType, columnSize, decimalDigits, nullable == DatabaseMetaData.columnNullable, remarks); columns.add(column); } return map; } catch (SQLException e) { throw new MojoExecutionException("Unable to generate database adapter due to a '" + e.getClass().getName() + "' with message '" + e.getMessage() + "'", e); } finally { if (rscolumns != null) { try { rscolumns.close(); } catch (SQLException ignore) { } } } }
From source file:org.rhq.plugins.postgres.PostgresTableComponent.java
public Configuration loadResourceConfiguration() throws Exception { Configuration config = new Configuration(); config.put(new PropertySimple("schemaName", resourceContext.getPluginConfiguration().getSimple("schemaName").getStringValue())); config.put(new PropertySimple("tableName", resourceContext.getPluginConfiguration().getSimple("tableName").getStringValue())); Connection connection = null; ResultSet columns;//from ww w. j av a 2s . c om try { connection = this.resourceContext.getParentResourceComponent().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); columns = databaseMetaData.getColumns("", getSchemaNameFromContext(resourceContext), getTableNameFromContext(resourceContext), ""); PropertyList columnList = new PropertyList("columns"); while (columns.next()) { PropertyMap col = new PropertyMap("columnDefinition"); col.put(new PropertySimple("columnName", columns.getString("COLUMN_NAME"))); col.put(new PropertySimple("columnType", columns.getString("TYPE_NAME"))); col.put(new PropertySimple("columnLength", columns.getInt("COLUMN_SIZE"))); col.put(new PropertySimple("columnPrecision", columns.getInt("DECIMAL_DIGITS"))); col.put(new PropertySimple("columnDefault", columns.getString("COLUMN_DEF"))); col.put(new PropertySimple("columnNullable", Boolean.valueOf(isNullableToBoolean(columns.getInt("NULLABLE"))))); columnList.add(col); } config.put(columnList); } finally { safeClose(connection); } return config; }
From source file:org.apache.hadoop.chukwa.database.Macro.java
public String computeMacro(String macro) throws SQLException { Pattern p = Pattern.compile("past_(.*)_minutes"); Matcher matcher = p.matcher(macro); if (macro.indexOf("avg(") == 0 || macro.indexOf("group_avg(") == 0 || macro.indexOf("sum(") == 0) { String meta = ""; String[] table = null;/* w w w. j a v a 2 s .c o m*/ if (forCharting) { table = dbc.findTableNameForCharts(macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")), start, end); } else { table = dbc.findTableName(macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")), start, end); } try { String cluster = System.getProperty("CLUSTER"); if (cluster == null) { cluster = "unknown"; } db = new DatabaseWriter(cluster); DatabaseMetaData dbMetaData = db.getConnection().getMetaData(); ResultSet rs = dbMetaData.getColumns(null, null, table[0], null); boolean first = true; while (rs.next()) { if (!first) { meta = meta + ","; } String name = rs.getString(4); int type = rs.getInt(5); if (type == java.sql.Types.VARCHAR) { if (macro.indexOf("group_avg(") < 0) { meta = meta + "count(" + name + ") as " + name; } else { meta = meta + name; } first = false; } else if (type == java.sql.Types.DOUBLE || type == java.sql.Types.FLOAT || type == java.sql.Types.INTEGER) { if (macro.indexOf("sum(") == 0) { meta = meta + "sum(" + name + ")"; } else { meta = meta + "avg(" + name + ")"; } first = false; } else if (type == java.sql.Types.TIMESTAMP) { meta = meta + name; first = false; } else { if (macro.indexOf("sum(") == 0) { meta = meta + "SUM(" + name + ")"; } else { meta = meta + "AVG(" + name + ")"; } first = false; } } db.close(); if (first) { throw new SQLException("Table is undefined."); } } catch (SQLException ex) { throw new SQLException("Table does not exist:" + table[0]); } return meta; } else if (macro.indexOf("now") == 0) { SimpleDateFormat sdf = new SimpleDateFormat(); return DatabaseWriter.formatTimeStamp(current); } else if (macro.intern() == "start".intern()) { return DatabaseWriter.formatTimeStamp(start); } else if (macro.intern() == "end".intern()) { return DatabaseWriter.formatTimeStamp(end); } else if (matcher.find()) { int period = Integer.parseInt(matcher.group(1)); long timestamp = current - (current % (period * 60 * 1000L)) - (period * 60 * 1000L); return DatabaseWriter.formatTimeStamp(timestamp); } else if (macro.indexOf("past_hour") == 0) { return DatabaseWriter.formatTimeStamp(current - 3600 * 1000L); } else if (macro.endsWith("_week")) { long partition = current / DatabaseConfig.WEEK; if (partition <= 0) { partition = 1; } String[] buffers = macro.split("_"); StringBuffer tableName = new StringBuffer(); for (int i = 0; i < buffers.length - 1; i++) { tableName.append(buffers[i]); tableName.append("_"); } tableName.append(partition); tableName.append("_week"); return tableName.toString(); } else if (macro.endsWith("_month")) { long partition = current / DatabaseConfig.MONTH; if (partition <= 0) { partition = 1; } String[] buffers = macro.split("_"); StringBuffer tableName = new StringBuffer(); for (int i = 0; i < buffers.length - 1; i++) { tableName.append(buffers[i]); tableName.append("_"); } tableName.append(partition); tableName.append("_month"); return tableName.toString(); } else if (macro.endsWith("_quarter")) { long partition = current / DatabaseConfig.QUARTER; if (partition <= 0) { partition = 1; } String[] buffers = macro.split("_"); StringBuffer tableName = new StringBuffer(); for (int i = 0; i < buffers.length - 1; i++) { tableName.append(buffers[i]); tableName.append("_"); } tableName.append(partition); tableName.append("_quarter"); return tableName.toString(); } else if (macro.endsWith("_year")) { long partition = current / DatabaseConfig.YEAR; if (partition <= 0) { partition = 1; } String[] buffers = macro.split("_"); StringBuffer tableName = new StringBuffer(); for (int i = 0; i < buffers.length - 1; i++) { tableName.append(buffers[i]); tableName.append("_"); } tableName.append(partition); tableName.append("_year"); return tableName.toString(); } else if (macro.endsWith("_decade")) { long partition = current / DatabaseConfig.DECADE; if (partition <= 0) { partition = 1; } String[] buffers = macro.split("_"); StringBuffer tableName = new StringBuffer(); for (int i = 0; i < buffers.length - 1; i++) { tableName.append(buffers[i]); tableName.append("_"); } tableName.append(partition); tableName.append("_decade"); return tableName.toString(); } if (forCharting) { if (macro.startsWith("session(") && request != null) { String keyword = macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")); String[] objects = null; if (request.getSession().getAttribute(keyword) != null) { objects = ((String) request.getSession().getAttribute(keyword)).split(","); } StringBuffer buf = new StringBuffer(); boolean first = true; if (objects != null) { for (String object : objects) { if (!first) { buf.append(" or "); } first = false; buf.append( macro.substring(macro.indexOf("(") + 1, macro.indexOf(")")) + "='" + object + "'"); } return buf.toString(); } return ""; } else { String[] tableList = dbc.findTableNameForCharts(macro, start, end); StringBuffer buf = new StringBuffer(); boolean first = true; for (String table : tableList) { if (!first) { buf.append("|"); } first = false; buf.append(table); } return buf.toString(); } } String[] tableList = dbc.findTableName(macro, current, current); return tableList[0]; }
From source file:org.openconcerto.sql.model.JDBCStructureSource.java
protected void _fillTables(final Set<String> newSchemas, final Connection conn) throws SQLException { // for new tables, add ; for existing, refresh final DatabaseMetaData metaData = conn.getMetaData(); // getColumns() only supports pattern (eg LIKE) so we must make multiple calls for (final String s : newSchemas) { final ResultSet rs = metaData.getColumns(this.getBase().getMDName(), s, "%", null); // handle tables becoming empty (possible in pg) final Set<SQLName> tablesWithColumns = new HashSet<SQLName>(); boolean hasNext = rs.next(); while (hasNext) { final String schemaName = rs.getString("TABLE_SCHEM"); final String tableName = rs.getString("TABLE_NAME"); tablesWithColumns.add(new SQLName(schemaName, tableName)); final Boolean moved; if (newSchemas.contains(schemaName)) { final SQLSchema schema = getNewSchema(schemaName); moved = schema.refreshTable(metaData, rs); } else { moved = null;//from w w w . java 2 s. c om } hasNext = moved == null ? rs.next() : moved; } // tables with no column = all tables - tables with column final SQLSchema schema = getNewSchema(s); for (final String t : schema.getTableNames()) { if (!tablesWithColumns.contains(new SQLName(s, t))) { // empty table with no db access schema.getTable(t).emptyFields(); } } } // type & comment for (final SQLName tName : getTablesNames()) { final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName()); final List<String> l = (List<String>) this.tableNames.getNonNull(tName); t.setType(l.get(0)); t.setComment(l.get(1)); } final SQLSystem system = getBase().getServer().getSQLSystem(); // procedures final CollectionMap<String, String> proceduresBySchema = new CollectionMap<String, String>(); for (final String s : newSchemas) { final ResultSet rsProc = metaData.getProcedures(this.getBase().getMDName(), s, "%"); while (rsProc.next()) { // to ignore case : pg.AbstractJdbc2DatabaseMetaData doesn't quote aliases final Map map = BasicRowProcessor.instance().toMap(rsProc); final String schemaName = (String) map.get("PROCEDURE_SCHEM"); if (newSchemas.contains(schemaName)) { final String procName = (String) map.get("PROCEDURE_NAME"); proceduresBySchema.put(schemaName, procName); getNewSchema(schemaName).addProcedure(procName); } } } // try to find out more about those procedures if (proceduresBySchema.size() > 0) { final String sel = system.getSyntax().getFunctionQuery(getBase(), proceduresBySchema.keySet()); if (sel != null) { // don't cache since we don't listen on system tables for (final Object o : (List) getBase().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false))) { final Map m = (Map) o; final SQLSchema newSchema = getNewSchema((String) m.get("schema")); if (newSchema != null) newSchema.setProcedureSource((String) m.get("name"), (String) m.get("src")); } } } // if no schemas exist, there can be no triggers // (avoid a query and a special case since "in ()" is not valid) if (newSchemas.size() > 0) { final ITransformer<Tuple2<String, String>, SQLTable> tableFinder = new ITransformer<Tuple2<String, String>, SQLTable>() { @Override public SQLTable transformChecked(Tuple2<String, String> input) { return getNewTable(input.get0(), input.get1()); } }; new JDBCStructureSource.TriggerQueryExecutor(tableFinder).apply(getBase(), newSchemas); new JDBCStructureSource.ColumnsQueryExecutor(tableFinder).apply(getBase(), newSchemas); try { new JDBCStructureSource.ConstraintsExecutor(tableFinder).apply(getBase(), newSchemas); } catch (QueryExn e1) { // constraints are not essentials, continue e1.printStackTrace(); for (final SQLName tName : getTablesNames()) { final SQLTable t = getNewSchema(tName.getItemLenient(-2)).getTable(tName.getName()); t.addConstraint(null); } } } }
From source file:com.wipro.ats.bdre.clustermigration.MigrationPreprocessor.java
private List<String> getCurrentSourceColumnList(String sourceDb, String table, String sourceHiveConnection) throws SQLException { List<String> sourceColumnList = new ArrayList<>(); DatabaseMetaData metaData = getHiveJDBCConnection(sourceDb, sourceHiveConnection).getMetaData(); ResultSet rsColumns = metaData.getColumns(null, sourceDb, table, null); while (rsColumns.next()) { String columnName = rsColumns.getString("COLUMN_NAME"); String dataType = rsColumns.getString("TYPE_NAME"); sourceColumnList.add(columnName + " " + dataType); }/*from w ww . ja va 2s . c om*/ rsColumns.close(); return sourceColumnList; }
From source file:org.ensembl.healthcheck.testcase.eg_compara.AbstractControlledRows.java
/** * // www. ja v a2 s . c o m * Returns the names of all columns for a given table. * * @param conn * @param table * @return names of columns */ protected List<String> getColumnsOfTable(Connection conn, String table) { List<String> columnsOfTable; try { DatabaseMetaData md = conn.getMetaData(); columnsOfTable = new ArrayList<String>(); ResultSet rs = md.getColumns(null, null, table, null); while (rs.next()) { columnsOfTable.add(rs.getString(4)); } } catch (SQLException e) { throw new RuntimeException(e); } if (columnsOfTable.size() == 0) { throw new RuntimeException("Got no columns for table " + table); } return columnsOfTable; }
From source file:org.executequery.databaseobjects.impl.AbstractDatabaseObject.java
public ResultSet getMetaData() throws DataSourceException { try {/*from w w w . j a v a 2s . c o m*/ DatabaseHost databaseHost = getHost(); String _catalog = databaseHost.getCatalogNameForQueries(getCatalogName()); String _schema = databaseHost.getSchemaNameForQueries(getSchemaName()); DatabaseMetaData dmd = databaseHost.getDatabaseMetaData(); return dmd.getColumns(_catalog, _schema, getName(), null); } catch (SQLException e) { throw new DataSourceException(e); } }
From source file:com.splicemachine.mrio.api.core.SMSQLUtil.java
/** * * Get table structure of 'tableName'/*from ww w . j a va 2 s .c om*/ * Return Column Name list : Column Type list * @throws SQLException * * */ public List<NameType> getTableStructure(String tableName) throws SQLException { if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getTableStructure tableName=%s", tableName); List<NameType> colType = new ArrayList<NameType>(); ResultSet result = null; try { String[] schemaTableName = parseTableName(tableName); DatabaseMetaData databaseMetaData = connect.getMetaData(); result = databaseMetaData.getColumns(null, schemaTableName[0], schemaTableName[1], null); while (result.next()) { colType.add(new NameType(result.getString(4), result.getInt(5))); } } finally { if (result != null) result.close(); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getTableStructure returns=%s", Arrays.toString(colType.toArray())); return colType; }
From source file:org.rhq.plugins.postgres.PostgresTableComponent.java
public void updateResourceConfiguration(ConfigurationUpdateReport report) { try {/*from ww w . j a v a 2 s.co m*/ Configuration updatedConfiguration = report.getConfiguration(); PropertyList updatedColumns = updatedConfiguration.getList("columns"); Connection connection = this.resourceContext.getParentResourceComponent().getConnection(); DatabaseMetaData dmd = connection.getMetaData(); ResultSet rs = dmd.getColumns("", "", getTableNameFromContext(resourceContext), ""); Map<String, ColumnDefinition> existingDefs = new HashMap<String, ColumnDefinition>(); try { while (rs.next()) { ColumnDefinition def = new ColumnDefinition(rs); existingDefs.put(def.columnName, def); } } finally { rs.close(); } for (Property newColumnDefinition : updatedColumns.getList()) { PropertyMap colDef = (PropertyMap) newColumnDefinition; ColumnDefinition existingDef = existingDefs.get(colDef.getSimple("columnName").getStringValue()); ColumnDefinition newDef = new ColumnDefinition(colDef); if (existingDef == null) { // This is a new column to add String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ADD COLUMN " + newDef.getColumnSql(); if (DatabaseQueryUtility.executeUpdate(this, sql) != 0) { throw new RuntimeException("Couldn't add column using SQL: " + sql); } } else { existingDefs.remove(existingDef.columnName); boolean columnLengthChanged = ((existingDef.columnLength != null && !existingDef.columnLength.equals(newDef.columnLength)) || (existingDef.columnLength == null && existingDef.columnLength != null)); boolean columnPrecisionChanged = ((existingDef.columnPrecision != null && !existingDef.columnPrecision.equals(newDef.columnPrecision)) || (existingDef.columnPrecision == null && existingDef.columnPrecision != null)); if (!existingDef.columnType.equals(newDef.columnType) || columnLengthChanged || columnPrecisionChanged) { String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ALTER COLUMN " + getQuoted(newDef.columnName) + " TYPE " + newDef.columnType; if (newDef.columnLength != null) { sql += " ( " + newDef.columnLength; // TODO: Implement a more robust check to figure out if this column has a numeric type. if (newDef.columnPrecision != null && !newDef.columnType.startsWith("varchar")) sql += ", " + newDef.columnPrecision; sql += " ) "; } if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) { throw new RuntimeException("Couldn't alter column type using SQL: " + sql); } } // Set default separately. boolean columnDefaultChanged = ((existingDef.columnDefault != null && !existingDef.columnDefault.equals(newDef.columnDefault)) || (existingDef.columnDefault == null && newDef.columnDefault != null)); if (columnDefaultChanged) { String sql = "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " ALTER COLUMN " + getQuoted(newDef.columnName); if (newDef.columnDefault == null) { sql += " DROP DEFAULT"; } else { sql += " SET DEFAULT " + newDef.columnDefault; } if (DatabaseQueryUtility.executeUpdate(this, sql) != 1) { throw new RuntimeException("Couldn't update column default using SQL: " + sql); } } } } // Cols left in existdef map have been removed and need to be dropped for (ColumnDefinition def : existingDefs.values()) { DatabaseQueryUtility.executeUpdate(this, "ALTER TABLE " + getQuoted(getTableNameFromContext(resourceContext)) + " DROP COLUMN " + getQuoted(def.columnName)); } report.setStatus(ConfigurationUpdateStatus.SUCCESS); } catch (SQLException e) { report.setErrorMessageFromThrowable(e); report.setStatus(ConfigurationUpdateStatus.FAILURE); } }