List of usage examples for java.sql ResultSetMetaData getColumnTypeName
String getColumnTypeName(int column) throws SQLException;
From source file:org.exoprax.assay.input.BasicDataSourceInput.java
public void runInner() throws SQLException { if (this.sql == null) { if (this.rowLimit > 0) { this.sql = "SELECT * FROM " + this.tableName + " WHERE ROWNUM <= " + this.rowLimit; } else {/*w ww . jav a 2 s . c o m*/ this.sql = "SELECT * FROM " + this.tableName; } } final Connection connection = this.dataSource.getConnection(); try { final Statement statement = connection.createStatement(); try { final ResultSet results = statement.executeQuery(this.sql); try { final ResultSetMetaData metadata = results.getMetaData(); final int columnCount = metadata.getColumnCount(); for (int i = 1; i <= columnCount; i++) { final String columnName = metadata.getColumnName(i); final String columnType = metadata.getColumnTypeName(i); final int columnPrecision = metadata.getPrecision(i); final int columnScale = metadata.getScale(i); final String columnTypeString = formatTypeString(columnType, columnPrecision, columnScale); this.assay.addAttribute(columnName, columnTypeString); } long rows = 0; boolean hitLimit = false; while (results.next()) { if ((this.rowLimit > 0) && (rows >= this.rowLimit)) { hitLimit = true; break; } for (int i = 1; i <= columnCount; i++) { final String columnName = metadata.getColumnName(i); final String value = results.getString(i); this.assay.addAttributeValue(columnName, value); } rows++; } if (hitLimit) { this.assay.setTitle("Table: " + this.tableName + " (subset of " + rows + " rows)"); } else { this.assay.setTitle("Table: " + this.tableName + " (all " + rows + " rows)"); } } finally { try { results.close(); } catch (final Exception e) { // no-op. Closing is best-effort only. } } } finally { try { statement.close(); } catch (final Exception e) { // no-op. Closing is best-effort only. } } } finally { try { connection.close(); } catch (final Exception e) { // no-op. Closing is best-effort only. } } }
From source file:org.rhq.plugins.mysql.MySqlDatabaseComponent.java
@Override public OperationResult invokeOperation(String name, Configuration parameters) throws InterruptedException, Exception { if ("invokeSql".equals(name)) { Statement stmt = null;//from w ww. j a va 2 s . c o m ResultSet rs = null; try { stmt = getConnection().createStatement(); String sql = parameters.getSimple("sql").getStringValue(); OperationResult result = new OperationResult(); if (parameters.getSimple("type").getStringValue().equals("update")) { int updateCount = stmt.executeUpdate(sql); result.getComplexResults() .put(new PropertySimple("result", "Query updated " + updateCount + " rows")); } else { rs = stmt.executeQuery(parameters.getSimple("sql").getStringValue()); ResultSetMetaData md = rs.getMetaData(); StringBuilder buf = new StringBuilder(); int rowCount = 0; buf.append("<table>"); buf.append("<th>"); for (int i = 1; i <= md.getColumnCount(); i++) { buf.append("<td>"); buf.append(md.getColumnName(i) + " (" + md.getColumnTypeName(i) + ")"); buf.append("</td>"); } buf.append("</th>"); while (rs.next()) { rowCount++; buf.append("<tr>"); for (int i = 1; i <= md.getColumnCount(); i++) { buf.append("<td>"); buf.append(rs.getString(i)); buf.append("</td>"); } buf.append("</tr>"); } buf.append("</table>"); result.getComplexResults() .put(new PropertySimple("result", "Query returned " + rowCount + " rows")); result.getComplexResults().put(new PropertySimple("contents", buf.toString())); } return result; } finally { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } } } else { throw new UnsupportedOperationException("Operation [" + name + "] is not supported yet."); } }
From source file:org.acmsl.queryj.customsql.handlers.customsqlvalidation.RetrieveResultPropertiesHandler.java
/** * Creates a property from given {@link ResultSetMetaData}. * @param metadata the result set metadata. * @param index the index.// w ww.j av a2s.com * @return the associated {@link Property}. * @throws SQLException if the property information is unavailable. */ @NotNull protected Property<String> createPropertyFrom(@NotNull final ResultSetMetaData metadata, final int index) throws SQLException { @NotNull final String t_strColumnName = metadata.getColumnName(index); @NotNull final String t_strType = metadata.getColumnTypeName(index); final boolean t_bNullable = (metadata.isNullable(index) == ResultSetMetaData.columnNullable); return new PropertyElement<>(t_strColumnName, t_strColumnName, index, t_strType, t_bNullable); }
From source file:org.pentaho.metadata.SQLModelGeneratorTest.java
/** * The following method returns an array of String(java.sql.Types) containing the column types for * a given ResultSetMetaData object.//from w w w .j a v a2 s. co m */ private String[] getColumnTypesNames(ResultSetMetaData resultSetMetaData) throws SQLException { int columnCount = resultSetMetaData.getColumnCount(); String[] columnTypes = new String[columnCount]; for (int colIndex = 1; colIndex <= columnCount; colIndex++) { columnTypes[colIndex - 1] = resultSetMetaData.getColumnTypeName(colIndex); } return columnTypes; }
From source file:com.mirth.connect.connectors.jdbc.JdbcConnectorService.java
public Object invoke(String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {//from ww w .j a v a 2s . c o m Properties properties = (Properties) object; String driver = properties.getProperty(DatabaseReaderProperties.DATABASE_DRIVER); String address = properties.getProperty(DatabaseReaderProperties.DATABASE_URL); String user = properties.getProperty(DatabaseReaderProperties.DATABASE_USERNAME); String password = properties.getProperty(DatabaseReaderProperties.DATABASE_PASSWORD); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = properties .getProperty(DatabaseReaderProperties.DATABASE_TABLE_NAME_PATTERN_EXPRESSION); String selectLimit = properties.getProperty(DatabaseReaderProperties.DATABASE_SELECT_LIMIT); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, password); DriverManager.setLoginTimeout(oldLoginTimeout); DatabaseMetaData dbMetaData = connection.getMetaData(); // the sorted set to hold the table information SortedSet<Table> tableInfoList = new TreeSet<Table>(); // Use a schema if the user name matches one of the schemas. // Fix for Oracle: MIRTH-1045 ResultSet schemasResult = null; try { schemasResult = dbMetaData.getSchemas(); while (schemasResult.next()) { String schemaResult = schemasResult.getString(1); if (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { ResultSet rs = null; try { rs = dbMetaData.getTables(null, schema, tableNamePattern, TABLE_TYPES); // based on the result set, loop through to store the table name so it can be used to // retrieve the table's column information while (rs.next()) { tableNameList.add(rs.getString("TABLE_NAME")); } } finally { if (rs != null) { rs.close(); } } } // for each table, grab their column information for (String tableName : tableNameList) { ResultSet rs = null; ResultSet backupRs = null; boolean fallback = false; try { // apparently it's much more efficient to use ResultSetMetaData to retrieve // column information. So each driver is defined with their own unique SELECT // statement to query the table columns and use ResultSetMetaData to retrieve // the column information. If driver is not defined with the select statement // then we'll define to the generic method of getting column information, but // this could be extremely slow List<Column> columnList = new ArrayList<Column>(); if (StringUtils.isEmpty(selectLimit)) { logger.debug("No select limit is defined, using generic method"); rs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; rs.next(); i++) { Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")); columnList.add(column); } } else { logger.debug( "Select limit is defined, using specific select query : '" + selectLimit + "'"); // replace the '?' with the appropriate schema.table name, and use ResultSetMetaData to // retrieve column information final String schemaTableName = StringUtils.isNotEmpty(schema) ? schema + "." + tableName : tableName; final String queryString = selectLimit.trim().replaceAll("\\?", schemaTableName); Statement statement = connection.createStatement(); try { rs = statement.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); // retrieve all relevant column information for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { Column column = new Column(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i)); columnList.add(column); } } catch (SQLException sqle) { logger.info("Failed to execute '" + queryString + "', fall back to generic approach to retrieve column information"); fallback = true; } finally { if (statement != null) { statement.close(); } } // failed to use selectLimit method, so we need to fall back to generic // if this generic approach fails, then there's nothing we can do if (fallback) { // Re-initialize in case some columns were added before failing columnList = new ArrayList<Column>(); logger.debug("Using fallback method for retrieving columns"); backupRs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { Column column = new Column(backupRs.getString("COLUMN_NAME"), backupRs.getString("TYPE_NAME"), backupRs.getInt("COLUMN_SIZE")); columnList.add(column); } } } // create table object and add to the list of table definitions Table table = new Table(tableName, columnList); tableInfoList.add(table); } finally { if (rs != null) { rs.close(); } if (backupRs != null) { backupRs.close(); } } } return tableInfoList; } catch (Exception e) { throw new Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:com.svds.resttest.services.GenericDataService.java
/** * Obtain metaDataSet from rs and add to genericResultsOutput * /*from w ww. j a v a 2 s . co m*/ * @param rs result set containing metadata * @param metaDataSet metadata map to add to * @param genericResultsOutput output object to add metadata to * @throws SQLException */ private void getMetaData(ResultSet rs, Map<String, Integer> metaDataSet, GenericResultsOutput genericResultsOutput) throws SQLException { LOG.info(">>>>Starting getMetaData"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); List<MetaData> metaDataArrayList = genericResultsOutput.getMetaData(); for (int i = 1; i <= columnCount; i++) { metaDataSet.put(rsmd.getColumnName(i), Integer.valueOf(i)); Map<String, Object> columnAttributes = new HashMap<>(); columnAttributes.put("ColumnTypeName", rsmd.getColumnTypeName(i)); columnAttributes.put("IndexNumber", i); MetaData metaData = new MetaData(); metaData.setColumnName(rsmd.getColumnName(i)); metaData.setColumnAttributes(columnAttributes); LOG.info("getColumnType : " + rsmd.getColumnType(i)); LOG.info("getColumnTypeName : " + rsmd.getColumnTypeName(i)); LOG.info("index : " + i); metaDataArrayList.add(metaData); } LOG.info(">>>>Ending getMetaData"); }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorService.java
public Object invoke(String channelId, String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {// w w w . java 2s .co m DatabaseConnectionInfo databaseConnectionInfo = (DatabaseConnectionInfo) object; String driver = databaseConnectionInfo.getDriver(); String address = replacer.replaceValues(databaseConnectionInfo.getUrl(), channelId); String user = replacer.replaceValues(databaseConnectionInfo.getUsername(), channelId); String password = replacer.replaceValues(databaseConnectionInfo.getPassword(), channelId); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = databaseConnectionInfo.getTableNamePatternExpression(); String selectLimit = databaseConnectionInfo.getSelectLimit(); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, password); DriverManager.setLoginTimeout(oldLoginTimeout); DatabaseMetaData dbMetaData = connection.getMetaData(); // the sorted set to hold the table information SortedSet<Table> tableInfoList = new TreeSet<Table>(); // Use a schema if the user name matches one of the schemas. // Fix for Oracle: MIRTH-1045 ResultSet schemasResult = null; try { schemasResult = dbMetaData.getSchemas(); while (schemasResult.next()) { String schemaResult = schemasResult.getString(1); if (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { ResultSet rs = null; try { rs = dbMetaData.getTables(null, schema, tableNamePattern, TABLE_TYPES); // based on the result set, loop through to store the table name so it can be used to // retrieve the table's column information while (rs.next()) { tableNameList.add(rs.getString("TABLE_NAME")); } } finally { if (rs != null) { rs.close(); } } } // for each table, grab their column information for (String tableName : tableNameList) { ResultSet rs = null; ResultSet backupRs = null; boolean fallback = false; try { // apparently it's much more efficient to use ResultSetMetaData to retrieve // column information. So each driver is defined with their own unique SELECT // statement to query the table columns and use ResultSetMetaData to retrieve // the column information. If driver is not defined with the select statement // then we'll define to the generic method of getting column information, but // this could be extremely slow List<Column> columnList = new ArrayList<Column>(); if (StringUtils.isEmpty(selectLimit)) { logger.debug("No select limit is defined, using generic method"); rs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; rs.next(); i++) { Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")); columnList.add(column); } } else { logger.debug( "Select limit is defined, using specific select query : '" + selectLimit + "'"); // replace the '?' with the appropriate schema.table name, and use ResultSetMetaData to // retrieve column information final String schemaTableName = StringUtils.isNotEmpty(schema) ? schema + "." + tableName : tableName; final String queryString = selectLimit.trim().replaceAll("\\?", schemaTableName); Statement statement = connection.createStatement(); try { rs = statement.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); // retrieve all relevant column information for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { Column column = new Column(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i)); columnList.add(column); } } catch (SQLException sqle) { logger.info("Failed to execute '" + queryString + "', fall back to generic approach to retrieve column information"); fallback = true; } finally { if (statement != null) { statement.close(); } } // failed to use selectLimit method, so we need to fall back to generic // if this generic approach fails, then there's nothing we can do if (fallback) { // Re-initialize in case some columns were added before failing columnList = new ArrayList<Column>(); logger.debug("Using fallback method for retrieving columns"); backupRs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { Column column = new Column(backupRs.getString("COLUMN_NAME"), backupRs.getString("TYPE_NAME"), backupRs.getInt("COLUMN_SIZE")); columnList.add(column); } } } // create table object and add to the list of table definitions Table table = new Table(tableName, columnList); tableInfoList.add(table); } finally { if (rs != null) { rs.close(); } if (backupRs != null) { backupRs.close(); } } } return tableInfoList; } catch (Exception e) { throw new Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorServlet.java
@Override public SortedSet<Table> getTables(String channelId, String channelName, String driver, String url, String username, String password, Set<String> tableNamePatterns, String selectLimit, Set<String> resourceIds) { CustomDriver customDriver = null;/*from www . j a va 2 s . c o m*/ Connection connection = null; try { url = replacer.replaceValues(url, channelId, channelName); username = replacer.replaceValues(username, channelId, channelName); password = replacer.replaceValues(password, channelId, channelName); String schema = null; try { MirthContextFactory contextFactory = contextFactoryController.getContextFactory(resourceIds); try { ClassLoader isolatedClassLoader = contextFactory.getIsolatedClassLoader(); if (isolatedClassLoader != null) { customDriver = new CustomDriver(isolatedClassLoader, driver); logger.debug("Custom driver created: " + customDriver.toString() + ", Version " + customDriver.getMajorVersion() + "." + customDriver.getMinorVersion()); } else { logger.debug("Custom classloader is not being used, defaulting to DriverManager."); } } catch (Exception e) { logger.debug("Error creating custom driver, defaulting to DriverManager.", e); } } catch (Exception e) { logger.debug("Error retrieving context factory, defaulting to DriverManager.", e); } if (customDriver == null) { Class.forName(driver); } int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); if (customDriver != null) { connection = customDriver.connect(url, username, password); } else { connection = DriverManager.getConnection(url, username, password); } DriverManager.setLoginTimeout(oldLoginTimeout); DatabaseMetaData dbMetaData = connection.getMetaData(); // the sorted set to hold the table information SortedSet<Table> tableInfoList = new TreeSet<Table>(); // Use a schema if the user name matches one of the schemas. // Fix for Oracle: MIRTH-1045 ResultSet schemasResult = null; try { schemasResult = dbMetaData.getSchemas(); while (schemasResult.next()) { String schemaResult = schemasResult.getString(1); if (username.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information tableNamePatterns = translateTableNamePatterns(tableNamePatterns); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tableNamePatterns) { ResultSet rs = null; try { rs = dbMetaData.getTables(null, schema, tableNamePattern, TABLE_TYPES); // based on the result set, loop through to store the table name so it can be used to // retrieve the table's column information while (rs.next()) { tableNameList.add(rs.getString("TABLE_NAME")); } } finally { if (rs != null) { rs.close(); } } } // for each table, grab their column information for (String tableName : tableNameList) { ResultSet rs = null; ResultSet backupRs = null; boolean fallback = false; try { // apparently it's much more efficient to use ResultSetMetaData to retrieve // column information. So each driver is defined with their own unique SELECT // statement to query the table columns and use ResultSetMetaData to retrieve // the column information. If driver is not defined with the select statement // then we'll define to the generic method of getting column information, but // this could be extremely slow List<Column> columnList = new ArrayList<Column>(); if (StringUtils.isEmpty(selectLimit)) { logger.debug("No select limit is defined, using generic method"); rs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; rs.next(); i++) { Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")); columnList.add(column); } } else { logger.debug( "Select limit is defined, using specific select query : '" + selectLimit + "'"); // replace the '?' with the appropriate schema.table name, and use ResultSetMetaData to // retrieve column information final String schemaTableName = StringUtils.isNotEmpty(schema) ? "\"" + schema + "\".\"" + tableName + "\"" : "\"" + tableName + "\""; final String queryString = selectLimit.trim().replaceAll("\\?", Matcher.quoteReplacement(schemaTableName)); Statement statement = connection.createStatement(); try { rs = statement.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); // retrieve all relevant column information for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { Column column = new Column(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i)); columnList.add(column); } } catch (SQLException sqle) { logger.info("Failed to execute '" + queryString + "', fall back to generic approach to retrieve column information"); fallback = true; } finally { if (statement != null) { statement.close(); } } // failed to use selectLimit method, so we need to fall back to generic // if this generic approach fails, then there's nothing we can do if (fallback) { // Re-initialize in case some columns were added before failing columnList = new ArrayList<Column>(); logger.debug("Using fallback method for retrieving columns"); backupRs = dbMetaData.getColumns(null, null, tableName.replace("/", "//"), null); // retrieve all relevant column information while (backupRs.next()) { Column column = new Column(backupRs.getString("COLUMN_NAME"), backupRs.getString("TYPE_NAME"), backupRs.getInt("COLUMN_SIZE")); columnList.add(column); } } } // create table object and add to the list of table definitions Table table = new Table(tableName, columnList); tableInfoList.add(table); } finally { if (rs != null) { rs.close(); } if (backupRs != null) { backupRs.close(); } } } return tableInfoList; } catch (Exception e) { throw new MirthApiException(new Exception("Could not retrieve database tables and columns.", e)); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { } } } }
From source file:org.h2gis.drivers.geojson.GeoJsonWriteDriver.java
/** * Cache the column name and its index.//from ww w .j a v a 2 s .c o m * * @param resultSetMetaData * @throws SQLException */ private void cacheMetadata(ResultSetMetaData resultSetMetaData) throws SQLException { cachedColumnNames = new HashMap<String, Integer>(); for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) { final String fieldTypeName = resultSetMetaData.getColumnTypeName(i); if (!fieldTypeName.equalsIgnoreCase("geometry") && isSupportedPropertyType(resultSetMetaData.getColumnType(i), fieldTypeName)) { cachedColumnNames.put(resultSetMetaData.getColumnName(i).toUpperCase(), i); columnCountProperties++; } } }
From source file:esavo.tap.formatter.ResultSet2JsonFormatter.java
@Override protected DBColumn[] writeMetadata(UwsJob job, ResultSet queryResult, JSONWriter out, TAPExecutionReport execReport) throws IOException, TAPException, InterruptedException, JSONException { out.array();//ww w . j a v a2 s . c o m DBColumn[] selectedColumns = execReport.resultingColumns; try { ResultSetMetaData meta = queryResult.getMetaData(); int indField = 1; if (selectedColumns != null) { for (DBColumn field : selectedColumns) { if (job.isPhaseAborted()) { return selectedColumns; } TAPColumn tapCol = null; try { tapCol = (TAPColumn) field; } catch (ClassCastException ex) { tapCol = new TAPColumn(field.getADQLName()); tapCol.setDatatype(meta.getColumnTypeName(indField), TAPTypes.NO_SIZE); service.getFactory().getLogger() .warning("Unknown DB datatype for the field \"" + tapCol.getName() + "\" ! It is supposed to be \"" + tapCol.getDatatype() + "\" (original value: \"" + meta.getColumnTypeName(indField) + "\")."); selectedColumns[indField - 1] = tapCol; } writeFieldMeta(tapCol, out); indField++; // if (thread.isInterrupted()) // throw new InterruptedException(); } } } catch (SQLException e) { service.getFactory().getLogger().error( "Job N" + execReport.jobID + " - Impossible to get the metadata of the given ResultSet !", e); } out.endArray(); return selectedColumns; }