List of usage examples for java.sql DatabaseMetaData getPrimaryKeys
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
From source file:com.dbsvg.models.JdbcMainDAO.java
/** * Grabs all the Columns and fills them with the information from the JDBC. * Also detects Primary keys//from w w w . j a va 2 s . c om * * @param table * @param conn * @return * @throws java.lang.Exception */ private Table populateTable(Table table, Connection conn) throws Exception { int maxWidth = 0; maxWidth = (int) (table.getName().length() * 1.5); DatabaseMetaData meta = conn.getMetaData(); ResultSet rs = meta.getColumns(null, null, table.getName(), null); while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); if (columnName.length() > maxWidth) maxWidth = columnName.length(); Column c = new ColumnObject(columnName); table.getColumns().put(columnName, c); c.setTable(table); populateColumn(c, rs); } table.setWidth(CHAR_WIDTH * maxWidth + PAD_WIDTH); table.setHeight(CHAR_HEIGHT * table.getColumns().size() + PAD_HEIGHT); try { rs = meta.getPrimaryKeys(null, null, table.getName()); while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); PrimaryKey pk = table.getColumns().get(columnName).transformToPK(); table.getColumns().put(columnName, pk); table.getPrimaryKeys().put(columnName, pk); } } catch (Exception e) { LOG.error(table.getName() + " Has Primary Key Issues.", e); } rs.close(); return table; }
From source file:com.cloudera.sqoop.manager.SqlManager.java
@Override public String getPrimaryKey(String tableName) { try {//from w w w . j av a 2s . co m DatabaseMetaData metaData = this.getConnection().getMetaData(); ResultSet results = metaData.getPrimaryKeys(null, null, tableName); if (null == results) { return null; } try { if (results.next()) { return results.getString("COLUMN_NAME"); } else { return null; } } finally { results.close(); getConnection().commit(); } } catch (SQLException sqlException) { LOG.error("Error reading primary key metadata: " + sqlException.toString()); return null; } }
From source file:ro.nextreports.designer.dbviewer.DefaultDBViewer.java
public List<DBColumn> getColumns(String schema, String table) throws NextSqlException, MalformedTableNameException { Connection con;//from w ww. j a va2 s . c o m List<DBColumn> columns = new ArrayList<DBColumn>(); String schemaName; String escapedTableName; try { con = Globals.getConnection(); if (schema == null) { schemaName = Globals.getConnection().getMetaData().getUserName(); } else { schemaName = schema; } Dialect dialect = Globals.getDialect(); if (dialect.isKeyWord(table)) { escapedTableName = dialect.getEscapedKeyWord(table); } else { escapedTableName = table; } } catch (Exception e) { throw new NextSqlException("Could not retrieve connection.", e); } ResultSet rs = null; Statement stmt = null; List<String> keyColumns = new ArrayList<String>(); try { // primary keys DatabaseMetaData dbmd = con.getMetaData(); rs = dbmd.getPrimaryKeys(null, schemaName, table); while (rs.next()) { keyColumns.add(rs.getString("COLUMN_NAME")); } closeResultSet(rs); // foreign keys rs = dbmd.getImportedKeys(null, schemaName, table); List<String> foreignColumns = new ArrayList<String>(); HashMap<String, DBForeignColumnInfo> fkMap = new HashMap<String, DBForeignColumnInfo>(); while (rs.next()) { String fkSchema = rs.getString("FKTABLE_SCHEM"); String fkTable = rs.getString("FKTABLE_NAME"); String fkColumn = rs.getString("FKCOLUMN_NAME"); String pkSchema = rs.getString("PKTABLE_SCHEM"); String pkTable = rs.getString("PKTABLE_NAME"); String pkColumn = rs.getString("PKCOLUMN_NAME"); DBForeignColumnInfo fkInfo = new DBForeignColumnInfo(fkSchema, fkTable, fkColumn, pkSchema, pkTable, pkColumn); //System.out.println("fkInfo : " + fkInfo); foreignColumns.add(fkColumn); fkMap.put(fkColumn, fkInfo); } closeResultSet(rs); // column names with index rs = dbmd.getIndexInfo(null, schemaName, table, false, true); List<String> indexes = new ArrayList<String>(); while (rs.next()) { String indexName = rs.getString(9); if (indexName != null) { indexes.add(indexName); } } closeResultSet(rs); DataSource ds = DefaultDataSourceManager.getInstance().getConnectedDataSource(); String header = ""; stmt = con.createStatement(); try { // workaround if a table name contains spaces if (escapedTableName.indexOf(" ") != -1) { escapedTableName = "\"" + escapedTableName + "\""; } String prefix = ""; if (!NO_SCHEMA_NAME.equals(schemaName)) { prefix = schemaName; } if (prefix.indexOf(" ") != -1) { prefix = "\"" + prefix + "\""; } if (!"".equals(prefix)) { prefix = prefix + "."; } if (ds.getDriver().equals(CSVDialect.DRIVER_CLASS)) { header = (String) ds.getProperties().get("headerline"); if (header == null) { header = ""; } } if (header.isEmpty()) { String s = "SELECT * FROM " + prefix + escapedTableName + " WHERE 1 = 0"; LOG.info("getColumns[ " + s + "]"); rs = stmt.executeQuery(s); } } catch (SQLException e) { e.printStackTrace(); throw new MalformedTableNameException(e); } if (header.isEmpty()) { ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); for (int col = 1; col <= columnCount; col++) { String name = rsmd.getColumnLabel(col); int length = rsmd.getColumnDisplaySize(col); int precision = rsmd.getPrecision(col); int scale = rsmd.getScale(col); boolean isPrimaryKey = false; boolean isForeignKey = false; boolean isIndex = false; if (keyColumns.contains(name)) { isPrimaryKey = true; } DBForeignColumnInfo fkInfo = null; if (foreignColumns.contains(name)) { isForeignKey = true; fkInfo = fkMap.get(name); } if (indexes.contains(name)) { isIndex = true; } DBColumn column = new DBColumn(schemaName, table, name, rsmd.getColumnTypeName(col), isPrimaryKey, isForeignKey, isIndex, fkInfo, length, precision, scale); columns.add(column); } } else { String columnTypes = (String) ds.getProperties().get("columnTypes"); String[] names = header.split(","); String[] types = new String[names.length]; for (int i = 0; i < types.length; i++) { types[i] = "String"; } if ((columnTypes != null) && !columnTypes.isEmpty()) { types = columnTypes.split(","); } for (int i = 0; i < names.length; i++) { DBColumn column = new DBColumn(schemaName, table, names[i], types[i], false, false, false, null, 20, 0, 0); columns.add(column); } } } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(rs); closeStatement(stmt); } return columns; }
From source file:org.wso2.carbon.dataservices.core.script.DSGenerator.java
private String getPrimaryKey(DatabaseMetaData meta, String dbName, String schema, String tableName) throws SQLException { String pKey = null;//from www . j a va 2 s. c o m ResultSet resultSet = meta.getPrimaryKeys(dbName, schema, tableName); if (resultSet.next()) { resultSet = meta.getPrimaryKeys(dbName, schema, tableName); } else { try { resultSet = meta.getPrimaryKeys(null, schema, tableName); } catch (SQLException e) { log.error("Failed to extract primary key info ", e); throw new SQLException("Failed to extract primary key info"); } } while (resultSet.next()) { pKey = resultSet.getString(DBConstants.DataServiceGenerator.COLUMN_NAME); return pKey; } return pKey; }
From source file:org.dspace.storage.rdbms.DatabaseManager.java
/** * Read metadata about a table from the database. * /*w w w. j a va2 s . co m*/ * @param table * The RDBMS table. * @return A map of information about the columns. The key is the name of * the column, a String; the value is a ColumnInfo object. * @exception SQLException * If there is a problem retrieving information from the * RDBMS. */ private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException { Connection connection = null; ResultSet pkcolumns = null; ResultSet columns = null; try { String schema = ConfigurationManager.getProperty("db.schema"); if (StringUtils.isBlank(schema)) { schema = null; } String catalog = null; int dotIndex = table.indexOf('.'); if (dotIndex > 0) { catalog = table.substring(0, dotIndex); table = table.substring(dotIndex + 1, table.length()); log.warn("catalog: " + catalog); log.warn("table: " + table); } connection = getConnection(); DatabaseMetaData metadata = connection.getMetaData(); Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>(); int max = metadata.getMaxTableNameLength(); String tname = ((max > 0) && (table.length() >= max)) ? table.substring(0, max - 1) : table; pkcolumns = metadata.getPrimaryKeys(catalog, schema, tname); Set<String> pks = new HashSet<String>(); while (pkcolumns.next()) { pks.add(pkcolumns.getString(4)); } columns = metadata.getColumns(catalog, schema, tname, null); while (columns.next()) { String column = columns.getString(4); ColumnInfo cinfo = new ColumnInfo(); cinfo.setName(column); cinfo.setType((int) columns.getShort(5)); if (pks.contains(column)) { cinfo.setIsPrimaryKey(true); } results.put(column, cinfo); } return Collections.unmodifiableMap(results); } finally { if (pkcolumns != null) { try { pkcolumns.close(); } catch (SQLException sqle) { } } if (columns != null) { try { columns.close(); } catch (SQLException sqle) { } } if (connection != null) { try { connection.close(); } catch (SQLException sqle) { } } } }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
/** * Wrapper for {@link java.sql.DatabaseMetaData#getPrimaryKeys(String, String, String)} * * @param connection An open JDBC connection * @param tableName table name that is optionally fully qualified with a schema in the form schema.tableName * @return List of primary key column names for a table * * @throws SQLException/*from w w w. j a v a 2s . co m*/ */ public List<String> getPrimaryKeys(Connection connection, String schema, String tableName) throws SQLException { String table = tableName; DatabaseMetaData metadata = connection.getMetaData(); List<String> keys = new ArrayList<>(); try (ResultSet result = metadata.getPrimaryKeys(connection.getCatalog(), schema, table)) { while (result.next()) { keys.add(result.getString(COLUMN_NAME)); } } return keys; }
From source file:org.kuali.core.db.torque.KualiTorqueJDBCTransformTask.java
public List<TableIndex> getIndexes(DatabaseMetaData dbMeta, String tableName) throws SQLException { List<TableIndex> indexes = new ArrayList<TableIndex>(); ResultSet pkInfo = null;// w ww.j a v a 2s .co m String pkName = null; //ArrayList<String> pkFields = new ArrayList<String>(); ResultSet indexInfo = null; try { indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName, false, true); // need to ensure that the PK is not returned as an index pkInfo = dbMeta.getPrimaryKeys(null, dbSchema, tableName); if (pkInfo.next()) { pkName = pkInfo.getString("PK_NAME"); } //Map<Integer,String> tempPk = new HashMap<Integer,String>(); //while ( pkInfo.next() ) { // tempPk.put( pkInfo.getInt( "KEY_SEQ" ), pkInfo.getString( "COLUMN_NAME" ) ); //} TableIndex currIndex = null; while (indexInfo.next()) { if (indexInfo.getString("INDEX_NAME") == null) continue; //System.out.println( "Row: " + indexInfo.getString( "INDEX_NAME" ) + "/" + indexInfo.getString( "COLUMN_NAME" ) ); if (currIndex == null || !indexInfo.getString("INDEX_NAME").equals(currIndex.name)) { currIndex = new TableIndex(); currIndex.name = indexInfo.getString("INDEX_NAME"); currIndex.unique = !indexInfo.getBoolean("NON_UNIQUE"); // if has the same name as the PK, skip adding it to the index list if (pkName == null || !pkName.equals(currIndex.name)) { indexes.add(currIndex); //System.out.println( "Added " + currIndex.name + " to index list"); } else { //System.out.println( "Skipping PK: " + currIndex.name ); } } currIndex.columns.add(indexInfo.getString("COLUMN_NAME")); } } catch (SQLException e) { log("WARN: Could not read indexes for Table " + tableName + " : " + e.getMessage(), Project.MSG_WARN); } finally { if (indexInfo != null) { indexInfo.close(); } if (pkInfo != null) { pkInfo.close(); } } return indexes; }
From source file:jp.co.tis.gsp.tools.dba.s2jdbc.gen.DbTableMetaReaderWithView.java
@Override protected Set<String> getPrimaryKeySet(DatabaseMetaData metaData, DbTableMeta tableMeta) { Set<String> result = new HashSet<String>(); Dialect gspDialect = DialectUtil.getDialect(); try {//from ww w .ja va 2 s . co m String typeName = getObjectTypeName(metaData, tableMeta); String tableName = tableMeta.getName(); ViewAnalyzer viewAnalyzer = null; if (StringUtils.equals(typeName, "VIEW")) { String sql = gspDialect.getViewDefinition(metaData.getConnection(), tableName, tableMeta); viewAnalyzer = new ViewAnalyzer(); viewAnalyzer.parse(sql); if (viewAnalyzer.isSimple()) { tableName = viewAnalyzer.getTableName().toUpperCase(); } else { return Collections.emptySet(); } } ResultSet rs = metaData.getPrimaryKeys(tableMeta.getCatalogName(), tableMeta.getSchemaName(), tableName); try { while (rs.next()) { result.add(rs.getString("COLUMN_NAME")); } } finally { ResultSetUtil.close(rs); } if (viewAnalyzer != null && !result.isEmpty()) { Set<String> viewPKs = new TreeSet<String>(); for (String pkColumn : result) { if (viewAnalyzer.getColumnNames().contains(pkColumn.toUpperCase())) { String alias = viewAnalyzer.getAlias((pkColumn.toUpperCase())); viewPKs.add(StringUtils.isEmpty(alias) ? pkColumn : alias); } } System.out.println("View Pks" + viewPKs); if (viewPKs.size() == result.size()) return viewPKs; else return Collections.emptySet(); } return result; } catch (SQLException ex) { throw new SQLRuntimeException(ex); } }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
/** * Read metadata about a table from the database. * * @param table//from w ww. j ava 2 s. co m * The RDBMS table. * @return A map of information about the columns. The key is the name of * the column, a String; the value is a ColumnInfo object. * @exception SQLException * If there is a problem retrieving information from the * RDBMS. */ @Mock private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException { Connection connection = null; ResultSet pkcolumns = null; ResultSet columns = null; try { String schema = ConfigurationManager.getProperty("db.schema"); String catalog = null; int dotIndex = table.indexOf("."); if (dotIndex > 0) { catalog = table.substring(0, dotIndex); table = table.substring(dotIndex + 1, table.length()); log.warn("catalog: " + catalog); log.warn("table: " + table); } connection = getConnection(); DatabaseMetaData metadata = connection.getMetaData(); Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>(); //H2 database has no limit or is unknown, so the result is 0. We //have to comment to avoid errors //int max = metadata.getMaxTableNameLength(); //String tname = (table.length() >= max) ? table // .substring(0, max - 1) : table; pkcolumns = metadata.getPrimaryKeys(catalog, schema, table); Set<String> pks = new HashSet<String>(); while (pkcolumns.next()) pks.add(pkcolumns.getString(4)); columns = metadata.getColumns(catalog, schema, table, null); while (columns.next()) { String column = columns.getString(4); ColumnInfo cinfo = new ColumnInfo(); cinfo.setName(column); cinfo.setType((int) columns.getShort(5)); if (pks.contains(column)) { cinfo.setIsPrimaryKey(true); } results.put(column, cinfo); } return results; } finally { if (pkcolumns != null) { try { pkcolumns.close(); } catch (SQLException sqle) { } } if (columns != null) { try { columns.close(); } catch (SQLException sqle) { } } if (connection != null) { try { connection.close(); } catch (SQLException sqle) { } } } }
From source file:org.dbmfs.DatabaseAccessor.java
private List<String> getPrimaryKeyColumnNames(String tableName) throws Exception { if (pKeyColumnNameCacheFolder.containsKey(tableName)) return (List<String>) pKeyColumnNameCacheFolder.get(tableName); List<String> primaryKeyColumnNames = null; try {//from www. j av a 2s . c o m primaryKeyColumnNames = new ArrayList(); DatabaseMetaData dbmd = injectConn.getMetaData(); // ? ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName); while (rs.next()) { primaryKeyColumnNames.add(rs.getString("COLUMN_NAME")); } Collections.sort(primaryKeyColumnNames); pKeyColumnNameCacheFolder.put(tableName, primaryKeyColumnNames); rs.close(); } catch (Exception e) { e.printStackTrace(); throw e; } return primaryKeyColumnNames; }