List of usage examples for java.sql DatabaseMetaData getPrimaryKeys
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
From source file:org.eclipse.ecr.core.storage.sql.extensions.H2Fulltext.java
private static int getPrimaryKeyType(DatabaseMetaData meta, String schema, String table) throws SQLException { // find primary key name String primaryKeyName = null; 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); }/*www.jav a 2 s. c o m*/ primaryKeyName = rs.getString("COLUMN_NAME"); } if (primaryKeyName == null) { throw new SQLException("No primary key for " + schema + '.' + table); } rs.close(); // find primary key type rs = meta.getColumns(null, schema, table, primaryKeyName); if (!rs.next()) { throw new SQLException("Could not find primary key"); } int primaryKeyType = rs.getInt("DATA_TYPE"); rs.close(); return primaryKeyType; }
From source file:jp.co.golorp.emarf.sql.MetaData.java
/** * @param cn//w w w. j a va 2s . c o m * ? * @param tableName * ?? * @return ???Set */ private static Set<String> getPrimaryKeys(final Connection cn, final String tableName) { List<String> pkList = null; ResultSet rs = null; try { // ?? DatabaseMetaData dmd = cn.getMetaData(); rs = dmd.getPrimaryKeys(null, null, tableName); while (rs.next()) { if (pkList == null) { pkList = new ArrayList<String>(); } String columnName = rs.getString("COLUMN_NAME"); // rdbms? 1,2,3, // sqlite?0,1,2, ?? int keySeq = rs.getShort("KEY_SEQ"); while (pkList.size() <= keySeq) { pkList.add(null); } pkList.set(keySeq, columnName); } } catch (SQLException e) { throw new SystemError(e); } finally { DbUtils.closeQuietly(rs); } List<String> primaryKeys = null; if (pkList != null) { for (String pk : pkList) { if (StringUtil.isNotBlank(pk)) { if (primaryKeys == null) { primaryKeys = new ArrayList<String>(); } primaryKeys.add(pk); } } } if (primaryKeys == null) { return null; } return new LinkedHashSet<String>(primaryKeys); }
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); }/*w w w. j a v a 2s .c o m*/ 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:org.openmrs.module.spreadsheetimport.DatabaseBackend.java
public static List<NameValue> getMapNameToAllowedValue(String tableName) throws Exception { List<NameValue> retVal = new ArrayList<NameValue>(); // Map<String, String> result = new LinkedHashMap<String, String>(); Connection conn = null;// w w w .j a v a 2 s . c o m Statement s = null; Exception exception = null; try { // Connect to db Class.forName("com.mysql.jdbc.Driver").newInstance(); Properties p = Context.getRuntimeProperties(); String url = p.getProperty("connection.url"); conn = DriverManager.getConnection(url, p.getProperty("connection.username"), p.getProperty("connection.password")); s = conn.createStatement(); // Primary key String primaryKey = tableName + "_id"; // Guess DatabaseMetaData dmd = conn.getMetaData(); ResultSet rsPrimaryKeys = dmd.getPrimaryKeys(null, null, tableName); if (rsPrimaryKeys.next()) { primaryKey = rsPrimaryKeys.getString("COLUMN_NAME"); } rsPrimaryKeys.close(); ResultSet rs = null; // Try 0: if table is person, then look for person_name if ("person".equals(tableName)) { try { rs = s.executeQuery( "SELECT CONCAT(given_name, ' ', family_name) name, `person_name`.`person_id` primary_key FROM `users` INNER JOIN `person_name` on `users`.`person_id` = `person_name`.`person_id` INNER JOIN `user_role` on `users`.`user_id` = `user_role`.`user_id` WHERE `user_role`.`role` = 'Provider'"); } catch (Exception e) { log.debug(e.toString()); } } // Try 1: name field in tableName if (rs == null) { try { rs = s.executeQuery("select name, " + primaryKey + " from " + tableName + " order by name"); } catch (Exception e) { log.debug(e.toString()); } } // Try 2: name field in table_name if (rs == null) { try { rs = s.executeQuery( "select name, " + primaryKey + " from " + tableName + "_name order by name"); } catch (Exception e) { log.debug(e.toString()); } } // Try 3: just use table_id as both key and value if (rs == null) { rs = s.executeQuery("select " + primaryKey + ", " + primaryKey + " from " + tableName); } while (rs.next()) { NameValue nameValue = new NameValue(); nameValue.setName(rs.getString(1)); nameValue.setValue(rs.getString(2)); retVal.add(nameValue); } rs.close(); } catch (Exception e) { log.debug(e.toString()); exception = e; } finally { if (s != null) { try { s.close(); } catch (Exception e) { } } if (conn != null) { try { conn.close(); } catch (Exception e) { } } } if (exception != null) { throw exception; } else { return retVal; } }
From source file:org.apache.cayenne.dbsync.reverse.dbload.PrimaryKeyLoader.java
@Override ResultSet getResultSet(DbEntity dbEntity, DatabaseMetaData metaData) throws SQLException { return metaData.getPrimaryKeys(dbEntity.getCatalog(), dbEntity.getSchema(), dbEntity.getName()); }
From source file:com.oracle2hsqldb.dialect.GenericDialect.java
public Iterator getPrimaryKeys(DataSource dataSource, final String schemaName) { final Map byTableName = new HashMap(); MetaDataJdbcTemplate template = new MetaDataJdbcTemplate(dataSource) { protected ResultSet getResults(DatabaseMetaData metaData) throws SQLException { return metaData.getPrimaryKeys(null, schemaName, null); }// w w w . j a va 2s . c o m }; template.query(new RowCallbackHandler() { public void processRow(ResultSet primaryKeys) throws SQLException { String tableName = primaryKeys.getString("TABLE_NAME"); if (!byTableName.containsKey(tableName)) { byTableName.put(tableName, new PrimaryKey.Spec(tableName, primaryKeys.getString("PK_NAME"))); } ((PrimaryKey.Spec) byTableName.get(tableName)).addColumnName(primaryKeys.getString("COLUMN_NAME")); } }); return byTableName.values().iterator(); }
From source file:org.jtalks.poulpe.util.databasebackup.persistence.DbTableKeys.java
/** * Obtain from the database a list of tables' primary keys. * // w w w . j a va2 s . co m * @return A list of {@link UniqueKey} object represented foreign keys. * @throws SQLException * Is thrown in case any errors during work with database occur. */ @SuppressWarnings("unchecked") public Set<UniqueKey> getPrimaryKeys() throws SQLException { if (primaryKeys != null) { return primaryKeys; } Set<UniqueKey> tablePrimaryKeySet = null; try { tablePrimaryKeySet = (Set<UniqueKey>) JdbcUtils.extractDatabaseMetaData(dataSource, new KeyListProcessor(tableName, new TableKeyPerformer() { @Override public ResultSet getResultSet(DatabaseMetaData dmd, String tableName) throws SQLException { return dmd.getPrimaryKeys(null, null, tableName); } @Override public void addKeyToSet(ResultSet rs, Set<TableKey> keySet) throws SQLException { if (rs.getString(PK_NAME) != null && rs.getString(COLUMN_NAME) != null) { keySet.add(new UniqueKey(rs.getString(PK_NAME), rs.getString(COLUMN_NAME))); } } })); } catch (MetaDataAccessException e) { throw new SQLException(e); } primaryKeys = tablePrimaryKeySet; return tablePrimaryKeySet; }
From source file:com.seer.datacruncher.profiler.spring.ProfilerInfoController.java
public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { @SuppressWarnings("unchecked") Hashtable<String, String> dbParams = (Hashtable<String, String>) request.getSession(true) .getAttribute("dbConnectionData"); if (dbParams != null) { request.setAttribute("serverName", CommonUtil.notNullValue(dbParams.get("Database_DSN"))); }/*from w w w.ja v a2s . c om*/ String selectedValue = CommonUtil.notNullValue(request.getParameter("selectedValue")); request.setAttribute("selectedValue", selectedValue); String parentNodeValue = CommonUtil.notNullValue(request.getParameter("parent")); request.setAttribute("parentValue", parentNodeValue); ObjectMapper mapper = new ObjectMapper(); if (CommonUtil.notNullValue(request.getParameter("action")).equals("minMaxValues")) { InfoBL ibl = new InfoBL(); String data = mapper.writeValueAsString( ibl.generateMinMaxValueGrid(CommonUtil.notNullValue(dbParams.get("Database_DSN")), parentNodeValue, selectedValue.split(":")[0])); PrintWriter out = response.getWriter(); out.println(data); return null; } else if (CommonUtil.notNullValue(request.getParameter("tab")).equals("analysis")) { String queryString = CommonUtil.notNullValue(request.getParameter("queryString")); Vector vector = RdbmsConnection.getTable(); String s = parentNodeValue; int i = vector.indexOf(s); Vector avector[] = (Vector[]) null; avector = TableMetaInfo.populateTable(5, i, i + 1, avector); QueryDialog querydialog = new QueryDialog(1, s, avector); List<String> listPrimaryKeys = new ArrayList<String>(); try { RdbmsConnection.openConn(); DatabaseMetaData dbmd = RdbmsConnection.getMetaData(); ResultSet resultset = dbmd.getPrimaryKeys(null, null, s); while (resultset.next()) { listPrimaryKeys.add(resultset.getString("COLUMN_NAME")); } RdbmsConnection.closeConn(); } catch (Exception ex) { ex.printStackTrace(); } try { querydialog.executeAction(queryString); } catch (Exception e) { e.printStackTrace(); } request.setAttribute("rowCount", querydialog.getRowCount()); GridUtil gridUtil = new GridUtil(); gridUtil.generateGridData(querydialog.getTableGridDTO(), false, listPrimaryKeys); request.setAttribute("gridColumns", gridUtil.getColumnNames()); GridUtil gridUtilEdit = new GridUtil(); gridUtilEdit.generateGridData(querydialog.getTableGridDTO(), true, listPrimaryKeys); request.setAttribute("gridColumnsEdit", gridUtilEdit.getColumnNames()); request.setAttribute("gridFields", gridUtil.getFieldNames()); request.setAttribute("gridData", gridUtil.getData()); request.setAttribute("systemDate", new Date(System.currentTimeMillis()).toString()); return new ModelAndView("jsp/profiler/analysis.jsp"); } else if (CommonUtil.notNullValue(request.getParameter("isLeaf")).equals("true")) { String s = dbParams.get("Database_DSN"); String s1 = parentNodeValue; String s2 = selectedValue.split(":")[0]; QueryBuilder querybuilder = new QueryBuilder(s, s1, s2, RdbmsConnection.getDBType()); Double profileValues[] = FirstInformation.getProfileValues(querybuilder); Vector patternValues[] = FirstInformation.getPatternValues(querybuilder); Vector distValues[] = getDistributionValues(querybuilder, request); // showBar(ad); // showPatternChart(avector); String pieChartData = ChartUtil.getChartDataForPieChart(distValues); String barChartData = ChartUtil.getChartDataForBarChart(profileValues); String patternChartData = ChartUtil.getChartDataForPieChart(patternValues); request.setAttribute("pieChartData", pieChartData); request.setAttribute("barChartData", barChartData); request.setAttribute("patternChartData", patternChartData); return new ModelAndView("jsp/profiler/charts.jsp"); } else { Vector vector = RdbmsConnection.getTable(); String s = selectedValue; int i = vector.indexOf(s); Vector avector[] = (Vector[]) null; avector = TableMetaInfo.populateTable(5, i, i + 1, avector); QueryDialog querydialog = new QueryDialog(1, s, avector); try { querydialog.executeAction(""); } catch (Exception e) { e.printStackTrace(); } request.setAttribute("rowCount", querydialog.getRowCount()); GridUtil gridUtil = new GridUtil(); gridUtil.generateGridData(querydialog.getTableGridDTO(), false, null); request.setAttribute("gridColumns", gridUtil.getColumnNames()); request.setAttribute("colCount", querydialog.getTableGridDTO().getColumnNames().length); request.setAttribute("gridFields", gridUtil.getFieldNames()); request.setAttribute("gridData", gridUtil.getData()); return new ModelAndView("jsp/profiler/info.jsp"); } }
From source file:org.unitils.core.dbsupport.DerbyDbSupport.java
/** * Gets the names of all primary columns of the given table. * <p/>//from w w w.j ava2 s. co m * This info is not available in the Derby sys tables. The database meta data is used instead to retrieve it. * * @param tableName The table, not null * @return The names of the primary key columns of the table with the given name */ protected Set<String> getPrimaryKeyColumnNames(String tableName) { Connection connection = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); resultSet = databaseMetaData.getPrimaryKeys(null, getSchemaName(), tableName); Set<String> result = new HashSet<String>(); while (resultSet.next()) { result.add(resultSet.getString(4)); // COLUMN_NAME } return result; } catch (SQLException e) { throw new UnitilsException("Error while querying for Derby primary keys for table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }
From source file:com.github.p4535992.database.datasource.database.data.Dao.java
public Set<String> getPrimaryKeys() { if (primaryKeys != null) { return primaryKeys; }//www .j a v a 2s.c o m primaryKeys = new HashSet<>(); database.getJdbcTemplate().execute(new ConnectionCallback<Void>() { @Override public Void doInConnection(Connection con) throws SQLException, DataAccessException { DatabaseMetaData metadata = con.getMetaData(); //if the table name is in lower case it won't work (at least not with HSQLDB) try (ResultSet rs = metadata.getPrimaryKeys(null, null, tableName.toUpperCase())) { while (rs.next()) { primaryKeys.add(rs.getString("COLUMN_NAME")); } } return null; } }); return primaryKeys; }