List of usage examples for java.sql DatabaseMetaData getPrimaryKeys
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
From source file:com.univocity.app.data.Dao.java
public Set<String> getPrimaryKeys() { if (primaryKeys != null) { return primaryKeys; }/*ww w. j av a2s . c o m*/ primaryKeys = new HashSet<String>(); 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) ResultSet rs = metadata.getPrimaryKeys(null, null, tableName.toUpperCase()); try { while (rs.next()) { primaryKeys.add(rs.getString("COLUMN_NAME")); } } finally { rs.close(); } return null; } }); return primaryKeys; }
From source file:org.jtester.module.database.support.DerbyDbSupport.java
/** * Gets the names of all primary columns of the given table. * <p/>// ww w. j a v a2 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 JTesterException("Error while querying for Derby primary keys for table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }
From source file:com.seer.datacruncher.profiler.spring.ProfilerInfoUpdateController.java
public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ServletOutputStream out = null;/*from ww w .j av a 2s.c om*/ response.setContentType("application/json"); out = response.getOutputStream(); @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"))); } String selectedValue = CommonUtil.notNullValue(request.getParameter("selectedValue")); request.setAttribute("selectedValue", selectedValue); String tableName = CommonUtil.notNullValue(request.getParameter("parent")); request.setAttribute("parentValue", tableName); ObjectMapper mapper = new ObjectMapper(); Vector vector = RdbmsConnection.getTable(); int i = vector.indexOf(tableName); Vector avector[] = (Vector[]) null; avector = TableMetaInfo.populateTable(5, i, i + 1, avector); QueryDialog querydialog = new QueryDialog(1, tableName, avector); try { querydialog.executeAction(""); } catch (Exception e) { e.printStackTrace(); } String strColumnName = ""; List<String> listPrimaryKeys = new ArrayList<String>(); Map<String, Integer> mapColumnNames = new HashMap<String, Integer>(); try { RdbmsConnection.openConn(); DatabaseMetaData dbmd = RdbmsConnection.getMetaData(); ResultSet resultset = dbmd.getPrimaryKeys(null, null, tableName); while (resultset.next()) { listPrimaryKeys.add(resultset.getString("COLUMN_NAME")); } resultset = dbmd.getColumns(null, null, tableName, null); while (resultset.next()) { strColumnName = resultset.getString(4); mapColumnNames.put(strColumnName, resultset.getInt(5)); } RdbmsConnection.closeConn(); } catch (Exception ex) { ex.printStackTrace(); } Map<String, Integer> mapPrimaryKeys = new HashMap<String, Integer>(); if (strColumnName.trim().length() > 0) { try { JSONArray array = new JSONArray(request.getParameter("data")); for (int count = 0; count < array.length(); count++) { JSONObject jsonObject = new JSONObject(array.get(count).toString()); StringBuilder queryString = new StringBuilder(); Iterator<String> keyIterator = jsonObject.keys(); while (keyIterator.hasNext()) { String strKey = keyIterator.next(); if (listPrimaryKeys.contains(strKey)) { mapPrimaryKeys.put(strKey, ((int) Double.parseDouble(jsonObject.get(strKey).toString()))); continue; } if (jsonObject.get(strKey) != null) { if (mapColumnNames.get(strKey) == 4 || mapColumnNames.get(strKey) == 5 || mapColumnNames.get(strKey) == -6) { queryString.append( strKey + "=" + Integer.parseInt(jsonObject.get(strKey).toString()) + ","); } else if (mapColumnNames.get(strKey) == 2 || mapColumnNames.get(strKey) == 3 || mapColumnNames.get(strKey) == 7 || mapColumnNames.get(strKey) == 6 || mapColumnNames.get(strKey) == -5) { queryString.append(strKey + "=" + jsonObject.get(strKey) + ","); } else if (mapColumnNames.get(strKey) == 91 || mapColumnNames.get(strKey) == 92 || mapColumnNames.get(strKey) == 93) { queryString.append(strKey + "=" + jsonObject.get(strKey) + ","); } else if (mapColumnNames.get(strKey) == -7 || mapColumnNames.get(strKey) == 16 || mapColumnNames.get(strKey) == -3 || mapColumnNames.get(strKey) == -4) { queryString.append(strKey + "=" + jsonObject.get(strKey) + ","); } else if (mapColumnNames.get(strKey) == -1 || mapColumnNames.get(strKey) == 1 || mapColumnNames.get(strKey) == 12) { queryString.append(strKey + "=\"" + jsonObject.get(strKey) + "\","); } } } StringBuilder whereClause = new StringBuilder(" where "); for (String primaryKey : listPrimaryKeys) { whereClause.append(primaryKey + "=" + mapPrimaryKeys.get(primaryKey).intValue()); whereClause.append(" and "); } String strWhereClause = whereClause.toString(); strWhereClause = strWhereClause.substring(0, strWhereClause.lastIndexOf("and")); queryString = new StringBuilder("UPDATE " + tableName + " SET ") .append(queryString.toString().substring(0, queryString.toString().length() - 1)); queryString.append(strWhereClause); RdbmsConnection.openConn(); RdbmsConnection.executeUpdate(queryString.toString()); RdbmsConnection.closeConn(); } } catch (Exception ex) { ex.printStackTrace(); } } Update update = new Update(); update.setSuccess(true); GridUtil gridUtil = new GridUtil(); gridUtil.generateGridData(querydialog.getTableGridDTO(), false, null); update.setResults(gridUtil.getData()); out.write(mapper.writeValueAsBytes(update)); out.flush(); out.close(); return null; }
From source file:org.dbmaintain.database.impl.DerbyDatabase.java
/** * Gets the names of all primary columns of the given table. * <p/>/*from w ww. jav a 2 s .c o m*/ * This info is not available in the Derby sys tables. The database meta data is used instead to retrieve it. * * @param schemaName The schema, not null * @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 schemaName, String tableName) { Connection connection = null; ResultSet resultSet = null; try { connection = getDataSource().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); resultSet = databaseMetaData.getPrimaryKeys(null, schemaName, tableName); Set<String> result = new HashSet<String>(); while (resultSet.next()) { result.add(resultSet.getString(4)); // COLUMN_NAME } return result; } catch (SQLException e) { throw new DatabaseException("Unable to get primary key column names for schema name: " + schemaName + ", table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }
From source file:org.batoo.jpa.core.jdbc.adapter.JdbcTable.java
private String readPrimaryKeyColumn(DatabaseMetaData dbMetadata) throws SQLException { String pkName = null;//from w ww .jav a 2s. c om ResultSet rs = null; try { rs = dbMetadata.getPrimaryKeys(this.catalog, this.schema, this.name); while (rs.next()) { pkName = rs.getString("PK_NAME"); this.pkColumns.add(rs.getString(JdbcTable.COLUMN_NAME).toUpperCase()); } } finally { DbUtils.closeQuietly(rs); } return pkName; }
From source file:org.apache.hadoop.sqoop.manager.SqlManager.java
@Override public String getPrimaryKey(String tableName) { try {//from ww w .ja v a2 s . c o m DatabaseMetaData metaData = this.getConnection().getMetaData(); ResultSet results = metaData.getPrimaryKeys(null, null, tableName); if (null == results) { return null; } if (results.next()) { return results.getString("COLUMN_NAME"); } } catch (SQLException sqlException) { LOG.error("Error reading primary key metadata: " + sqlException.toString()); return null; } return null; }
From source file:org.apache.syncope.core.util.ContentExporter.java
private void doExportTable(final TransformerHandler handler, final Connection conn, final String tableName, final String whereClause) throws SQLException, SAXException { LOG.debug("Export table {}", tableName); AttributesImpl attrs = new AttributesImpl(); PreparedStatement stmt = null; ResultSet rs = null;//w w w . j a v a 2 s . c o m ResultSet pkeyRS = null; try { // ------------------------------------ // retrieve primary keys to perform an ordered select final DatabaseMetaData meta = conn.getMetaData(); pkeyRS = meta.getPrimaryKeys(null, null, tableName); final StringBuilder orderBy = new StringBuilder(); while (pkeyRS.next()) { final String columnName = pkeyRS.getString("COLUMN_NAME"); if (columnName != null) { if (orderBy.length() > 0) { orderBy.append(","); } orderBy.append(columnName); } } // ------------------------------------ StringBuilder query = new StringBuilder(); query.append("SELECT * FROM ").append(tableName).append(" a"); if (StringUtils.isNotBlank(whereClause)) { query.append(" WHERE ").append(whereClause); } if (orderBy.length() > 0) { query.append(" ORDER BY ").append(orderBy); } stmt = conn.prepareStatement(query.toString()); rs = stmt.executeQuery(); while (rs.next()) { attrs.clear(); final ResultSetMetaData rsMeta = rs.getMetaData(); for (int i = 0; i < rsMeta.getColumnCount(); i++) { final String columnName = rsMeta.getColumnName(i + 1); final Integer columnType = rsMeta.getColumnType(i + 1); // Retrieve value taking care of binary values. String value = getValues(rs, columnName, columnType); if (value != null && (!COLUMNS_TO_BE_NULLIFIED.containsKey(tableName) || !COLUMNS_TO_BE_NULLIFIED.get(tableName).contains(columnName))) { attrs.addAttribute("", "", columnName, "CDATA", value); } } handler.startElement("", "", tableName, attrs); handler.endElement("", "", tableName); LOG.debug("Add record {}", attrs); } } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error("While closing result set", e); } } if (pkeyRS != null) { try { pkeyRS.close(); } catch (SQLException e) { LOG.error("While closing result set", e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { LOG.error("While closing result set", e); } } } }
From source file:com.splicemachine.mrio.api.core.SMSQLUtil.java
/** * Get primary key from 'tableName'/*ww w . j a v a 2 s. c om*/ * Return Column Name list : Column Seq list * Column Id here refers to Column Seq, where Id=1 means the first column in primary keys. * @throws SQLException * **/ public List<PKColumnNamePosition> getPrimaryKeys(String tableName) throws SQLException { if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getPrimaryKeys tableName=%s", tableName); ArrayList<PKColumnNamePosition> pkCols = new ArrayList<PKColumnNamePosition>(1); String[] schemaTable = parseTableName(tableName); ResultSet result = null; try { DatabaseMetaData databaseMetaData = connect.getMetaData(); result = databaseMetaData.getPrimaryKeys(null, schemaTable[0], schemaTable[1]); while (result.next()) { // Convert 1-based column index to 0-based index pkCols.add(new PKColumnNamePosition(result.getString(4), result.getInt(5) - 1)); } } finally { if (result != null) result.close(); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getPrimaryKeys returns=%s", Arrays.toString(pkCols.toArray())); return pkCols.size() != 0 ? pkCols : null; }
From source file:org.apache.torque.generator.source.jdbc.JdbcMetadataSource.java
/** * Retrieves a list of the columns composing the primary key for a given * table.//from w ww . jav a 2s. c om * * @param dbMeta JDBC metadata. * @param tableName Table from which to retrieve PK information. * @return A list of the primary key parts for <code>tableName</code>. * @throws SQLException */ Set<String> getPrimaryKeys(DatabaseMetaData dbMeta, String tableName, String schemaName) throws SQLException { Set<String> pk = new HashSet<String>(); ResultSet parts = null; try { parts = dbMeta.getPrimaryKeys(null, schemaName, tableName); while (parts.next()) { pk.add(parts.getString(COLUMN_NAME_POS_IN_PRIMARY_KEY_METADATA)); } } finally { if (parts != null) { parts.close(); } } return pk; }
From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfUniqueKeyExtractor.java
protected ResultSet extractPrimaryKeyMetaData(DatabaseMetaData dbMeta, UnifiedSchema unifiedSchema, String tableName, boolean retry) throws SQLException { try {/*from www .ja v a 2s .c o m*/ final String catalogName = unifiedSchema.getPureCatalog(); final String schemaName = unifiedSchema.getPureSchema(); return dbMeta.getPrimaryKeys(catalogName, schemaName, tableName); } catch (SQLException e) { if (retry) { // because the exception may be thrown when the table is not found // (for example, Sybase) return null; } else { throw e; } } }