List of usage examples for java.sql DatabaseMetaData getPrimaryKeys
ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;
From source file:com.nextep.designer.sqlgen.generic.impl.JDBCCapturer.java
/** * Returns a <code>UniqueKeyConstraint</code> object representing the * primary key of the specified table present in the data source pointed to * by the connection object provided by the specified <code>context</code> * and notifies the specified <code>monitor</code> while capturing. * /*from www .j a v a2 s. com*/ * @param context * a {@link ICaptureContext} to store the captured objects * @param monitor * the {@link IProgressMonitor} to notify while capturing objects * @param allTablesColumns * a <code>Map</code> of all columns previously captured * @param table * the {@link IBasicTable} for which the primary key must be * captured * @return a {@link UniqueKeyConstraint} object if the specified table has a * primary key, <code>null</code> otherwise */ private UniqueKeyConstraint getTablePrimaryKey(ICaptureContext context, IProgressMonitor monitor, Map<String, IBasicColumn> allTablesColumns, IBasicTable table) { UniqueKeyConstraint pk = null; final String tableName = table.getName(); try { final DatabaseMetaData md = ((Connection) context.getConnectionObject()).getMetaData(); ResultSet rset = null; if (md != null) { rset = md.getPrimaryKeys(getObjectOrContextCatalog(context, table), getObjectOrContextSchema(context, table), tableName); CaptureHelper.updateMonitor(monitor, getCounter(), 1, 1); } if (rset != null) { ColumnsSorter pkColumnsSorter = null; try { while (rset.next()) { final String pkName = rset.getString(COLUMN_NAME_PK_NAME); final String pkColumnName = rset.getString(COLUMN_NAME_COLUMN_NAME); /* * We need to fetch the column's index in the primary * key because columns are ordered by COLUMN_NAME in the * ResultSet. */ final short position = rset.getShort(COLUMN_NAME_KEY_SEQ); if (pkName != null && !"".equals(pkName.trim())) { //$NON-NLS-1$ if (LOGGER.isDebugEnabled()) { String logPrefix = "[" + tableName + "][" + pkName + "]"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ LOGGER.debug("= " + logPrefix + " Primary Key Metadata ="); //$NON-NLS-1$ //$NON-NLS-2$ LOGGER.debug(logPrefix + "[" + COLUMN_NAME_COLUMN_NAME + "] " //$NON-NLS-1$ //$NON-NLS-2$ + pkColumnName); LOGGER.debug(logPrefix + "[" + COLUMN_NAME_KEY_SEQ + "] " //$NON-NLS-1$ //$NON-NLS-2$ + position); } if (null == pk) { pk = typedObjFactory.create(UniqueKeyConstraint.class); pk.setName(getConnectionVendor(context).getNameFormatter().format(pkName)); pk.setConstraintType(ConstraintType.PRIMARY); pkColumnsSorter = new ColumnsSorter(); } if (pkColumnsSorter != null && pkColumnName != null && !"".equals(pkColumnName.trim())) { //$NON-NLS-1$ final IBasicColumn pkColumn = allTablesColumns .get(CaptureHelper.getUniqueObjectName(tableName, pkColumnName)); if (pkColumn != null) { pkColumnsSorter.addColumn(pkColumn, position); } else { // TODO [BGA]: Raise a warning and manage // the PK creation // cancellation } } } } } finally { CaptureHelper.safeClose(rset, null); } if (pk != null && pkColumnsSorter != null) { int index = 0; for (IBasicColumn column : pkColumnsSorter.getColumnsSortedArray()) { pk.addConstrainedColumn(index++, column); } } } } catch (SQLException sqle) { LOGGER.error("Unable to fetch primary key for table [" + tableName + "] from " + getConnectionVendorName(context) + " server: " + sqle.getMessage(), sqle); } return pk; }
From source file:de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.java
protected void reverseEngineerPrimaryKey(Model aModel, TableEntry aTableEntry, DatabaseMetaData aMetaData, Table aTable) throws SQLException, ReverseEngineeringException { ResultSet thePrimaryKeyResultSet = aMetaData.getPrimaryKeys(aTableEntry.getCatalogName(), aTableEntry.getSchemaName(), aTableEntry.getTableName()); Index thePrimaryKeyIndex = null;/* ww w .ja v a 2 s . com*/ while (thePrimaryKeyResultSet.next()) { String thePKName = thePrimaryKeyResultSet.getString("PK_NAME"); String theColumnName = thePrimaryKeyResultSet.getString("COLUMN_NAME"); if (thePrimaryKeyIndex == null) { thePrimaryKeyIndex = new Index(); thePrimaryKeyIndex.setIndexType(IndexType.PRIMARYKEY); thePrimaryKeyIndex.setName(convertIndexNameFor(aTable, thePKName)); thePrimaryKeyIndex.setOriginalName(thePKName); if (StringUtils.isEmpty(thePrimaryKeyIndex.getName())) { // Assume the default name is TABLE_NAME+"_PK" thePrimaryKeyIndex.setName(aTableEntry.getTableName() + "_PK"); } try { aTable.addIndex(aModel, thePrimaryKeyIndex); } catch (ElementAlreadyExistsException | ElementInvalidNameException e) { throw new ReverseEngineeringException(e.getMessage(), e); } } Attribute<Table> theIndexAttribute = aTable.getAttributes() .findByName(dialect.getCastType().cast(theColumnName)); if (theIndexAttribute == null) { throw new ReverseEngineeringException( "Cannot find attribute " + theColumnName + " in table " + aTable.getName()); } try { thePrimaryKeyIndex.getExpressions().addExpressionFor(theIndexAttribute); } catch (ElementAlreadyExistsException e) { throw new ReverseEngineeringException("Error adding index attribute", e); } } thePrimaryKeyResultSet.close(); }
From source file:com.nextep.designer.sqlgen.mysql.impl.MySqlCapturer.java
/** * Builds the map of unique keys for the specified table. Note that the * fetched unique keys will be added to the table * /*w w w . ja va2 s .co m*/ * @param md * {@link DatabaseMetaData} of the underlying database connection * @param monitor * a {@link IProgressMonitor} to report progress to * @param table * the {@link IBasicTable} to fetch unique keys for * @param columnsMap * the map of {@link IBasicColumn} hashed by their unique name * @return a map of {@link IKeyConstraint} hashed by their unique name * @throws SQLException */ private Map<String, IKeyConstraint> buildUniqueKeyMap(DatabaseMetaData md, IProgressMonitor monitor, IBasicTable table, Map<String, IBasicColumn> columnsMap) throws SQLException { final Map<String, IKeyConstraint> keysMap = new HashMap<String, IKeyConstraint>(); final String tabName = table.getName(); ResultSet rset = null; try { // Creating primary keys for this table rset = md.getPrimaryKeys(null, null, tabName); IKeyConstraint uk = null; List<MultiKey> pkCols = new ArrayList<MultiKey>(); // Because JDBC may not give us a sorted list, we first fill // a list with all pk columns, we sort it by KEY_SEQ, and we // fill our neXtep PK. while (rset.next()) { monitor.worked(1); final String pkName = rset.getString("PK_NAME"); //$NON-NLS-1$ final String colName = rset.getString("COLUMN_NAME"); //$NON-NLS-1$ final int colIndex = rset.getInt("KEY_SEQ") - 1; //$NON-NLS-1$ pkCols.add(new MultiKey(pkName, colIndex, colName)); } Collections.sort(pkCols, new Comparator<MultiKey>() { @Override public int compare(MultiKey o1, MultiKey o2) { if ((Integer) o1.getKeys()[1] > (Integer) o2.getKeys()[1]) { return 1; } return -1; } }); for (MultiKey pkCol : pkCols) { final String pkName = (String) pkCol.getKey(0); final String colName = (String) pkCol.getKey(2); monitor.worked(1); if (uk == null) { uk = new UniqueKeyConstraint(pkName, "", table); //$NON-NLS-1$ uk.setConstraintType(ConstraintType.PRIMARY); table.addConstraint(uk); keysMap.put(tabName.toUpperCase(), uk); } // Retrieving UK column and adding it to UK final String columnKey = CaptureHelper.getUniqueObjectName(tabName, colName); final IBasicColumn ukColumn = columnsMap.get(columnKey); if (ukColumn != null) { uk.addColumn(ukColumn); } else { LOGGER.warn(MessageFormat.format(MySQLMessages.getString("capturer.mysql.uniqueKeyNotFound"), //$NON-NLS-1$ columnKey)); } } } finally { CaptureHelper.safeClose(rset, null); } return keysMap; }
From source file:cz.lbenda.dataman.db.DbStructureFactory.java
private void generatePKColumns(Collection<CatalogDesc> catalogs, DatabaseMetaData dmd) throws SQLException { SQLDialect di = dbConfig.getJdbcConfiguration().getDialect(); StatusHelper.getInstance().progressNextStep(this, STEP_READ_PRIMARY_KEYS, catalogs.stream().mapToInt(//from w w w . j av a 2 s .c o m cat -> cat.getSchemas().stream().mapToInt(schema -> schema.getTables().size()).sum()) .sum()); for (CatalogDesc ch : catalogs) { for (SchemaDesc schema : ch.getSchemas()) { for (TableDesc td : schema.getTables()) { StatusHelper.getInstance().progress(this); try (ResultSet rsPk = dmd.getPrimaryKeys(ch.getName(), schema.getName(), td.getName())) { while (rsPk.next()) { ColumnDesc column = td.getColumn(rsPk.getString(di.pkColumnName())); if (column == null) { LOG.error("The primary column not exist in whole column set of table: " + di.pkColumnName()); } else { column.setPK(true); } } } } } } }
From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java
public String getPrimaryKey(String tableName, String schemaName) throws Exception { Exception error = null;/*from w w w . j a v a2 s .c o m*/ String primaryKey = null; Connection connection = null; try { DataSource dataSource = poolDataSources.get(schemaId); connection = dataSource.getConnection(); connection.setAutoCommit(false); // get primary keys DatabaseMetaData dataBaseMetaData = connection.getMetaData(); ResultSet rs = dataBaseMetaData.getPrimaryKeys(null, schemaName, tableName); if (rs.next()) { primaryKey = rs.getString("column_name"); } } catch (SQLException e) { error = e; } finally { if (connection != null) { try { if (error != null) { connection.rollback(); } } catch (SQLException se) { log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage())); } try { connection.close(); } catch (SQLException se) { log.warn("Se produjo un error al intentar cerrar la conexin: " .concat(se.getLocalizedMessage())); } } } if (error != null) { throw error; } return primaryKey; }
From source file:org.fao.geonet.services.dataprep.geocoding.GeoCoding.java
public Element exec(Element params, ServiceContext context) throws Exception { GeonetContext gc = (GeonetContext) context.getHandlerContext(Geonet.CONTEXT_NAME); // context.getUserSession(). DataManager dataMan = gc.getDataManager(); // Return response // Element results = new Element("results"); JSONObject responseJSONObj = new JSONObject(); if (cmd != null && "get".equals(cmd)) { String ezi_address = Util.getParam(params, "address"); String suburb = Util.getParam(params, "suburb"); if (StringUtils.isBlank(ezi_address)) return null; if (StringUtils.isBlank(suburb)) return null; ezi_address = ezi_address.trim(); suburb = suburb.trim();/* w w w . ja v a 2 s.com*/ HashMap<String, String> addrMap = extractAddressField(ezi_address, suburb); ResultSet eligibleRS = findRangeAddressInDB(addrMap, null); JSONArray resultsJSONArray = new JSONArray(); while (eligibleRS.next()) { JSONObject resultJSONObj = new JSONObject(); JSONObject geometryJSONObj = new JSONObject(); JSONObject locationJSONObj = new JSONObject(); locationJSONObj.put("lng", eligibleRS.getString("st_x_geom")); locationJSONObj.put("lat", eligibleRS.getString("st_y_geom")); geometryJSONObj.put("location", locationJSONObj); resultJSONObj.put("geometry", geometryJSONObj); // resultJSONObj.put("formatted_address", eligibleRS.getString("ezi_add")); // // JSONArray address_componentsJSONArray = new JSONArray(); HashMap<String, Object> mapp = new HashMap<String, Object>(); mapp.put("long_name", eligibleRS.getString("hse_num1")); mapp.put("short_name", eligibleRS.getString("hse_num1")); mapp.put("types", Arrays.asList("street_number")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", addrMap.get("roadName") + " " + addrMap.get("roadType")); mapp.put("short_name", addrMap.get("roadName") + " " + addrMap.get("roadType")); mapp.put("types", Arrays.asList("route")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", suburb); mapp.put("short_name", suburb); mapp.put("types", Arrays.asList("locality")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", eligibleRS.getString("postcode")); mapp.put("short_name", eligibleRS.getString("postcode")); mapp.put("types", Arrays.asList("postal_code")); address_componentsJSONArray.put(mapp); resultJSONObj.put("address_components", address_componentsJSONArray); resultsJSONArray.put(resultJSONObj); } responseJSONObj.put("results", resultsJSONArray); eligibleRS.close(); } else if (cmd != null && "fetchtablecontent".equals(cmd)) { String dbInfo = Util.getParam(params, "dbInfo"); String tableName = Util.getParam(params, "tableName"); JSONObject dbInfoJSONObj = new JSONObject(dbInfo); Connection conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection(dbInfoJSONObj.getString("ip"), dbInfoJSONObj.getString("port"), dbInfoJSONObj.getString("database_name"), dbInfoJSONObj.getString("username"), dbInfoJSONObj.getString("password")); // fetch columnnames JSONArray columnNamesJSONArray = getColumnNamesInJSON(tableName, dbInfoJSONObj, conn); // Statement stm = conn.createStatement(); // stm.setFetchSize(10); ResultSet eligibleRS = stm.executeQuery(String.format("select * from \"%s\" limit 100 ", tableName)); // ssss JSONArray resultsJSONArray = new JSONArray(); while (eligibleRS.next()) { JSONObject resultJSONObj = new JSONObject(); for (int i = 0; i < columnNamesJSONArray.length(); i++) { String columnName = columnNamesJSONArray.getJSONObject(i).getString("name"); resultJSONObj.put(columnName, eligibleRS.getString(columnName)); } resultsJSONArray.put(resultJSONObj); } responseJSONObj.put("records", resultsJSONArray); responseJSONObj.put("fields", columnNamesJSONArray); eligibleRS.close(); conn.close(); } else if (cmd != null && "fetchtablenames".equals(cmd)) { String dbInfo = Util.getParam(params, "dbInfo"); JSONObject dbInfoJSONObj = new JSONObject(dbInfo); Connection conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection(dbInfoJSONObj.getString("ip"), dbInfoJSONObj.getString("port"), dbInfoJSONObj.getString("database_name"), dbInfoJSONObj.getString("username"), dbInfoJSONObj.getString("password")); // conn.createStatement().executeUpdate("ALTER TABLE users ADD COLUMN column4 integer;"); DatabaseMetaData databaseMetadata = conn.getMetaData(); String[] types = new String[1]; types[0] = "TABLE"; // ResultSet rs = databaseMetadata.getColumns( null, null, // "address", null); ResultSet rs = conn.getMetaData().getTables(dbInfoJSONObj.getString("database_name"), "public", "%", types); JSONArray resultsJSONArray = new JSONArray(); while (rs.next()) { JSONObject resultJSONObj = new JSONObject(); System.out.println(rs.getObject("TABLE_NAME")); resultJSONObj.put("name", rs.getObject("TABLE_NAME")); resultsJSONArray.put(resultJSONObj); } responseJSONObj.put("results", resultsJSONArray); conn.close(); } else if (cmd != null && "fetchcolumnnames".equals(cmd)) { String dbInfo = Util.getParam(params, "dbInfo"); String tableName = Util.getParam(params, "tableName"); JSONObject dbInfoJSONObj = new JSONObject(dbInfo); Connection conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection(dbInfoJSONObj.getString("ip"), dbInfoJSONObj.getString("port"), dbInfoJSONObj.getString("database_name"), dbInfoJSONObj.getString("username"), dbInfoJSONObj.getString("password")); JSONArray resultsJSONArray = getColumnNamesInJSON(tableName, dbInfoJSONObj, conn); responseJSONObj.put("results", resultsJSONArray); conn.close(); } else if (cmd != null && "geocode_database".equals(cmd)) { String dbInfo = Util.getParam(params, "dbInfo"); String tableName = Util.getParam(params, "tableName"); String ezi_address_column_name = Util.getParam(params, "ezi_address_column_name"); String suburb_column_name = Util.getParam(params, "suburb_column_name"); JSONObject dbInfoJSONObj = new JSONObject(dbInfo); Connection candidatedb_conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection( dbInfoJSONObj.getString("ip"), dbInfoJSONObj.getString("port"), dbInfoJSONObj.getString("database_name"), dbInfoJSONObj.getString("username"), dbInfoJSONObj.getString("password")); try { candidatedb_conn.createStatement().executeUpdate(String .format("ALTER TABLE \"%s\" ADD COLUMN geocoded_x character varying(20);", tableName)); } catch (Exception e) { if (e instanceof PSQLException) { PSQLException plsqlExcep = (PSQLException) e; if (!"42701".equals(plsqlExcep.getSQLState())) { throw e; } } else throw e; } try { candidatedb_conn.createStatement().executeUpdate(String .format("ALTER TABLE \"%s\" ADD COLUMN geocoded_y character varying(20);", tableName)); } catch (Exception e) { if (e instanceof PSQLException) { PSQLException plsqlExcep = (PSQLException) e; if (!"42701".equals(plsqlExcep.getSQLState())) { throw e; } } else throw e; } try { candidatedb_conn.createStatement().executeUpdate( String.format("ALTER TABLE \"%s\" ADD COLUMN geocoded_log text;", tableName)); } catch (Exception e) { if (e instanceof PSQLException) { PSQLException plsqlExcep = (PSQLException) e; if (!"42701".equals(plsqlExcep.getSQLState())) { throw e; } } else throw e; } try { candidatedb_conn.createStatement().executeUpdate( String.format("ALTER TABLE \"%s\" ADD COLUMN geocoded_geom geometry;", tableName)); } catch (Exception e) { if (e instanceof PSQLException) { PSQLException plsqlExcep = (PSQLException) e; if (!"42701".equals(plsqlExcep.getSQLState())) { throw e; } } else throw e; } try { candidatedb_conn.createStatement().executeUpdate(String.format( "ALTER TABLE \"%s\" ADD COLUMN geocoded_result character varying(40);", tableName)); } catch (Exception e) { if (e instanceof PSQLException) { PSQLException plsqlExcep = (PSQLException) e; if (!"42701".equals(plsqlExcep.getSQLState())) { throw e; } } else throw e; } DatabaseMetaData candidatedb_Metadata = candidatedb_conn.getMetaData(); String candidate_primarykey_columnname = null; ResultSet candidatedb_primarykeys = candidatedb_Metadata .getPrimaryKeys(dbInfoJSONObj.getString("database_name"), "public", tableName); if (candidatedb_primarykeys.next()) { candidate_primarykey_columnname = candidatedb_primarykeys.getString("COLUMN_NAME"); } if (candidate_primarykey_columnname == null) throw new Exception( "No Primary key found for selected table [" + tableName + "] in specified database"); // String query = "select \"%s\" , \"%s\",\"%s\" from \"%s\" where ( (geocoded_x is null) or (geocoded_y is null) or (geocoded_geom is null) )"; String fomatted_query = query.format(query, candidate_primarykey_columnname, ezi_address_column_name, suburb_column_name, tableName); ResultSet candidatedb_rs = candidatedb_conn.createStatement().executeQuery(fomatted_query); // Statement candidatedb_statement = candidatedb_conn.createStatement(); // Connection geocoding_info_conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection("127.0.0.1", "5432", "geocoding", "postgres", "Qwert123"); Statement geocoding_info_statement = geocoding_info_conn.createStatement(); // JSONArray failureGeocodeRecords = new JSONArray(); Integer success_geocode_count = 0; Integer failure_geocode_count = 0; int count = 0; while (candidatedb_rs.next()) { String candidate_primarykey_value = candidatedb_rs.getString(candidate_primarykey_columnname); String candidate_ezi_address_value = candidatedb_rs.getString(ezi_address_column_name); String candidate_suburb_value = candidatedb_rs.getString(suburb_column_name); if (StringUtils.isNotEmpty(candidate_suburb_value)) candidate_suburb_value = candidate_suburb_value.trim(); if (StringUtils.isBlank(candidate_ezi_address_value)) { JSONObject _jsonObject = new JSONObject(); _jsonObject.put("id", candidate_primarykey_value); _jsonObject.put("failure reason", "EZI address column's value is empty"); failureGeocodeRecords.put(_jsonObject); failure_geocode_count++; continue; } if (StringUtils.isBlank(candidate_suburb_value)) { JSONObject _jsonObject = new JSONObject(); _jsonObject.put("id", candidate_primarykey_value); _jsonObject.put("failure reason", "suburb column's value is empty"); failureGeocodeRecords.put(_jsonObject); failure_geocode_count++; continue; } HashMap<String, String> addrMap = null; JSONArray resultsJSONArray = new JSONArray(); String candidate_st_x_geom = ""; String candidate_st_y_geom = ""; String candidate_geom = null; String geocoded_result = "FAILED"; try { ResultSet eligibleRS = null; addrMap = extractAddressField(candidate_ezi_address_value, candidate_suburb_value); if (StringUtils.isEmpty(addrMap.get("plaqNumber")) || StringUtils.isEmpty(addrMap.get("roadType")) || StringUtils.isEmpty(addrMap.get("roadName"))) { System.out.println("candidate_ezi_address_value:" + candidate_ezi_address_value); System.out.println("candidate_suburb_value:" + candidate_suburb_value); JSONObject _jsonObject = new JSONObject(); _jsonObject.put("id", candidate_primarykey_value); _jsonObject.put("addressMap", addrMap); _jsonObject.put("failure reason", "error in parsing address"); failureGeocodeRecords.put(_jsonObject); resultsJSONArray = new JSONArray(); resultsJSONArray.put(_jsonObject); failure_geocode_count++; } else { eligibleRS = findExactAddressInDB(addrMap, geocoding_info_statement); } boolean found_record = false; boolean exact_match = false; if (eligibleRS != null) { if (eligibleRS.next()) { found_record = true; exact_match = true; } } if (!found_record) { eligibleRS = findRangeAddressInDB(addrMap, geocoding_info_statement); if (eligibleRS != null) { if (eligibleRS.next()) { found_record = true; exact_match = false; } } } if (found_record) { candidate_st_x_geom = eligibleRS.getString("st_x_geom"); candidate_st_y_geom = eligibleRS.getString("st_y_geom"); candidate_geom = eligibleRS.getString("geom"); // JSONObject resultJSONObj = new JSONObject(); JSONObject geometryJSONObj = new JSONObject(); JSONObject locationJSONObj = new JSONObject(); locationJSONObj.put("lng", eligibleRS.getString("st_x_geom")); locationJSONObj.put("lat", eligibleRS.getString("st_y_geom")); geometryJSONObj.put("location", locationJSONObj); resultJSONObj.put("geometry", geometryJSONObj); // resultJSONObj.put("formatted_address", eligibleRS.getString("ezi_add")); // // JSONArray address_componentsJSONArray = new JSONArray(); HashMap<String, Object> mapp = new HashMap<String, Object>(); String hse_num2_rs = eligibleRS.getString("hse_num2"); boolean long_short_term_added = false; if (StringUtils.isNotEmpty(hse_num2_rs)) { Integer hse_num2 = Integer.parseInt(hse_num2_rs); if (!hse_num2.equals(0)) { mapp.put("long_name", eligibleRS.getString("hse_num1") + "-" + hse_num2_rs); mapp.put("short_name", eligibleRS.getString("hse_num1") + "-" + hse_num2_rs); long_short_term_added = true; } } if (!long_short_term_added) { mapp.put("long_name", eligibleRS.getString("hse_num1")); mapp.put("short_name", eligibleRS.getString("hse_num1")); } mapp.put("types", Arrays.asList("street_number")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", addrMap.get("roadName") + " " + addrMap.get("roadType")); mapp.put("short_name", addrMap.get("roadName") + " " + addrMap.get("roadType")); mapp.put("types", Arrays.asList("route")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", addrMap.get("suburb")); mapp.put("short_name", addrMap.get("suburb")); mapp.put("types", Arrays.asList("locality")); address_componentsJSONArray.put(mapp); mapp = new HashMap<String, Object>(); mapp.put("long_name", eligibleRS.getString("postcode")); mapp.put("short_name", eligibleRS.getString("postcode")); mapp.put("types", Arrays.asList("postal_code")); address_componentsJSONArray.put(mapp); resultJSONObj.put("address_components", address_componentsJSONArray); resultsJSONArray.put(resultJSONObj); if (exact_match) geocoded_result = "SUCCESSFUL_EXACT_MATCH"; else geocoded_result = "SUCCESSFUL_PARTIAL_MATCH"; } else { geocoded_result = "FAILED"; JSONObject _jsonObject = new JSONObject(); _jsonObject.put("id", candidate_primarykey_value); _jsonObject.put("addressMap", addrMap); _jsonObject.put("failure reason", "not found in geocoding info database"); failureGeocodeRecords.put(_jsonObject); resultsJSONArray = new JSONArray(); resultsJSONArray.put(_jsonObject); failure_geocode_count++; } } catch (Exception e) { System.out.println("candidate_ezi_address_value:" + candidate_ezi_address_value); System.out.println("candidate_suburb_value:" + candidate_suburb_value); e.printStackTrace(); JSONObject _jsonObject = new JSONObject(); _jsonObject.put("id", candidate_primarykey_value); _jsonObject.put("addressMap", addrMap); _jsonObject.put("failure reason", "exception in finding address in geocoding info database, exception is " + e.getMessage()); failureGeocodeRecords.put(_jsonObject); resultsJSONArray = new JSONArray(); resultsJSONArray.put(_jsonObject); failure_geocode_count++; geocoded_result = "FAILED"; continue; } if (StringUtils.isNotEmpty(candidate_geom)) { try { candidatedb_statement.executeUpdate(String.format( "update \"%s\" set \"%s\" ='%s' , \"%s\"='%s' ,\"%s\"='%s',\"%s\"='%s',\"%s\"='%s' where \"%s\" = '%s';", tableName, "geocoded_x", candidate_st_x_geom, "geocoded_y", candidate_st_y_geom, "geocoded_log", resultsJSONArray.toString(), "geocoded_geom", candidate_geom, "geocoded_result", geocoded_result, candidate_primarykey_columnname, candidate_primarykey_value)); success_geocode_count++; } catch (Exception e) { System.out.println("######"); e.printStackTrace(); } } else { try { candidatedb_statement.executeUpdate(String.format( "update \"%s\" set \"%s\"='%s',\"%s\"='%s' where \"%s\" = '%s';", tableName, "geocoded_log", resultsJSONArray.toString(), "geocoded_result", geocoded_result, candidate_primarykey_columnname, candidate_primarykey_value)); } catch (Exception e) { System.out.println("######"); e.printStackTrace(); } } if (count % 10 == 0) candidatedb_conn.commit(); count++; System.out.println("successfully geocode record with id=" + candidate_primarykey_value); } candidatedb_statement.close(); candidatedb_conn.close(); geocoding_info_statement.close(); geocoding_info_conn.close(); responseJSONObj.put("success_count", success_geocode_count); responseJSONObj.put("failure_count", failure_geocode_count); responseJSONObj.put("failure_records", failureGeocodeRecords); } Element root = new Element(Jeeves.Elem.ROOT); // Element success = new Element("success"); // success.setText("OK"); // root.addContent(results); // root.addContent(success); if (BooleanUtils.isTrue(this.jsonOutput)) { XMLOutputter xx = new XMLOutputter(); // String jsonContent = org.json.XML.toJSONObject( // xx.outputString(root)).toString(); Element jsonElement = new Element("jsonElement"); jsonElement.setText(responseJSONObj.toString(10)); return jsonElement; } else return root; }
From source file:org.executequery.databaseobjects.impl.DefaultDatabaseTable.java
/** * Returns the columns of this table.//from www. j a v a2 s.co m * * @return the columns */ public List<DatabaseColumn> getColumns() throws DataSourceException { if (!isMarkedForReload() && columns != null) { return columns; } // otherwise cleanup existing references if (columns != null) { columns.clear(); columns = null; } DatabaseHost host = getHost(); if (host != null) { ResultSet rs = null; try { List<DatabaseColumn> _columns = host.getColumns(getCatalogName(), getSchemaName(), getName()); if (_columns != null) { columns = databaseColumnListWithSize(_columns.size()); for (DatabaseColumn i : _columns) { columns.add(new DatabaseTableColumn(this, i)); } // reload and define the constraints String _catalog = host.getCatalogNameForQueries(getCatalogName()); String _schema = host.getSchemaNameForQueries(getSchemaName()); DatabaseMetaData dmd = host.getDatabaseMetaData(); rs = dmd.getPrimaryKeys(_catalog, _schema, getName()); while (rs.next()) { String pkColumn = rs.getString(4); for (DatabaseColumn i : columns) { if (i.getName().equalsIgnoreCase(pkColumn)) { DatabaseTableColumn column = (DatabaseTableColumn) i; TableColumnConstraint constraint = new TableColumnConstraint(column, ColumnConstraint.PRIMARY_KEY); constraint.setName(rs.getString(6)); constraint.setMetaData(resultSetRowToMap(rs)); column.addConstraint(constraint); break; } } } rs.close(); try { // TODO: XXX // sapdb amd maxdb dump on imported/exported keys // surround with try/catch hack to get at least a columns list rs = dmd.getImportedKeys(_catalog, _schema, getName()); while (rs.next()) { String fkColumn = rs.getString(8); for (DatabaseColumn i : columns) { if (i.getName().equalsIgnoreCase(fkColumn)) { DatabaseTableColumn column = (DatabaseTableColumn) i; TableColumnConstraint constraint = new TableColumnConstraint(column, ColumnConstraint.FOREIGN_KEY); constraint.setReferencedCatalog(rs.getString(1)); constraint.setReferencedSchema(rs.getString(2)); constraint.setReferencedTable(rs.getString(3)); constraint.setReferencedColumn(rs.getString(4)); constraint.setUpdateRule(rs.getShort(10)); constraint.setDeleteRule(rs.getShort(11)); constraint.setName(rs.getString(12)); constraint.setDeferrability(rs.getShort(14)); constraint.setMetaData(resultSetRowToMap(rs)); column.addConstraint(constraint); break; } } } } catch (SQLException e) { } } } catch (DataSourceException e) { // catch and re-throw here to create // an empty column list so we don't // keep hitting the same error columns = databaseColumnListWithSize(0); throw e; } catch (SQLException e) { // catch and re-throw here to create // an empty column list so we don't // keep hitting the same error columns = databaseColumnListWithSize(0); throw new DataSourceException(e); } finally { releaseResources(rs); setMarkedForReload(false); } } return columns; }
From source file:org.talend.core.model.metadata.builder.database.manager.ExtractManager.java
protected Map<String, String> retrievePrimaryKeys(DatabaseMetaData dbMetaData, String catalogName, String schemaName, String tableName) throws SQLException { Map<String, String> primaryKeys = new HashMap<String, String>(); try {//from www . j a v a 2 s.c o m ResultSet keys = null; try { keys = dbMetaData.getPrimaryKeys(catalogName, schemaName, tableName); primaryKeys.clear(); while (keys.next()) { primaryKeys.put(keys.getString("COLUMN_NAME"), "PRIMARY KEY"); //$NON-NLS-1$ //$NON-NLS-2$ } } finally { if (keys != null) { keys.close(); } } } catch (Exception e) { throw new SQLException(e); } checkUniqueKeyConstraint(tableName, primaryKeys, dbMetaData.getConnection()); return primaryKeys; }
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 . j a v a 2s.co 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.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java
/** * This method reads primary keys of the table. * * @param tableName Name of the table/*from w w w.j a va 2 s. com*/ * @return primary key list * @throws ODataServiceFault */ private List<String> readTablePrimaryKeys(String tableName, DatabaseMetaData metaData, String catalog) throws ODataServiceFault { ResultSet resultSet = null; List<String> keys = new ArrayList<>(); try { resultSet = metaData.getPrimaryKeys(catalog, "", tableName); while (resultSet.next()) { String primaryKey = resultSet.getString("COLUMN_NAME"); keys.add(primaryKey); } return keys; } catch (SQLException e) { throw new ODataServiceFault(e, "Error in reading table primary keys in " + tableName + " table. :" + e.getMessage()); } finally { releaseResources(resultSet, null); } }