List of usage examples for java.sql ResultSetMetaData getPrecision
int getPrecision(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 {//from ww w . j av a2s . 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:jongo.handler.ResultSetMetaDataHandler.java
@Override public List<Row> handle(ResultSet rs) throws SQLException { List<Row> results = new ArrayList<Row>(); int rowId = 0; ResultSetMetaData metaData = rs.getMetaData(); Map<String, String> map = null; for (int i = 1; i <= metaData.getColumnCount(); i++) { map = new HashMap<String, String>(2); map.put("tableName", metaData.getTableName(i)); map.put("columnName", metaData.getColumnName(i)); map.put("columnLabel", metaData.getColumnLabel(i)); map.put("columnType", metaData.getColumnTypeName(i)); map.put("columnSize", String.valueOf(metaData.getColumnDisplaySize(i))); map.put("precision", String.valueOf(metaData.getPrecision(i))); map.put("scale", String.valueOf(metaData.getScale(i))); // map.put("catalog_name", metaData.getCatalogName(i)); // map.put("column_class_name", metaData.getColumnClassName(i)); // map.put("schema_name", metaData.getSchemaName(i)); // map.put("column_type", String.valueOf(metaData.getColumnType(i))); if (map != null) results.add(new Row(rowId++, map)); }//from ww w. j a v a 2s . co m return results; }
From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java
/** * Convert from JDBC metadata to Avatica columns. *///w w w .jav a2 s .co m protected static List<ColumnMetaData> columns(ResultSetMetaData metaData) throws SQLException { if (metaData == null) { return Collections.emptyList(); } final List<ColumnMetaData> columns = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { final SqlType sqlType = SqlType.valueOf(metaData.getColumnType(i)); final ColumnMetaData.Rep rep = ColumnMetaData.Rep.of(sqlType.internal); ColumnMetaData.AvaticaType t = ColumnMetaData.scalar(metaData.getColumnType(i), metaData.getColumnTypeName(i), rep); ColumnMetaData md = new ColumnMetaData(i - 1, metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i), metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), metaData.getSchemaName(i), metaData.getPrecision(i), metaData.getScale(i), metaData.getTableName(i), metaData.getCatalogName(i), t, metaData.isReadOnly(i), metaData.isWritable(i), metaData.isDefinitelyWritable(i), metaData.getColumnClassName(i)); columns.add(md); } return columns; }
From source file:org.apache.kylin.rest.util.HiveReroute.java
private void extractColumnMetadata(ResultSet resultSet, List<SelectedColumnMeta> columnMetas) throws SQLException { ResultSetMetaData metaData = null; int columnCount = 0; metaData = resultSet.getMetaData();//from w w w . j av a 2 s .com columnCount = metaData.getColumnCount(); // fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false, metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false)); } }
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 w w w.j a v a2s . 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.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 {//from w w w . jav a 2 s . 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:org.apache.nifi.processors.standard.util.TestJdbcCommon.java
@Test public void testUnsignedIntShouldBeLong() throws SQLException, IllegalArgumentException, IllegalAccessException { final ResultSetMetaData metadata = mock(ResultSetMetaData.class); when(metadata.getColumnCount()).thenReturn(1); when(metadata.getColumnType(1)).thenReturn(Types.INTEGER); when(metadata.getPrecision(1)).thenReturn(10); when(metadata.isSigned(1)).thenReturn(false); when(metadata.getColumnName(1)).thenReturn("Col1"); when(metadata.getTableName(1)).thenReturn("Table1"); final ResultSet rs = mock(ResultSet.class); when(rs.getMetaData()).thenReturn(metadata); Schema schema = JdbcCommon.createSchema(rs); Assert.assertNotNull(schema);//from ww w .j ava 2s . c om Schema.Field field = schema.getField("Col1"); Schema fieldSchema = field.schema(); Assert.assertEquals(2, fieldSchema.getTypes().size()); boolean foundLongSchema = false; boolean foundNullSchema = false; for (Schema type : fieldSchema.getTypes()) { if (type.getType().equals(Schema.Type.LONG)) { foundLongSchema = true; } else if (type.getType().equals(Schema.Type.NULL)) { foundNullSchema = true; } } assertTrue(foundLongSchema); assertTrue(foundNullSchema); }
From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java
@Test public void testMediumUnsignedIntShouldBeInt() throws SQLException, IllegalArgumentException, IllegalAccessException { final ResultSetMetaData metadata = mock(ResultSetMetaData.class); when(metadata.getColumnCount()).thenReturn(1); when(metadata.getColumnType(1)).thenReturn(Types.INTEGER); when(metadata.getPrecision(1)).thenReturn(8); when(metadata.isSigned(1)).thenReturn(false); when(metadata.getColumnName(1)).thenReturn("Col1"); when(metadata.getTableName(1)).thenReturn("Table1"); final ResultSet rs = mock(ResultSet.class); when(rs.getMetaData()).thenReturn(metadata); Schema schema = JdbcCommon.createSchema(rs); Assert.assertNotNull(schema);/*from w w w . j av a 2 s. c om*/ Schema.Field field = schema.getField("Col1"); Schema fieldSchema = field.schema(); Assert.assertEquals(2, fieldSchema.getTypes().size()); boolean foundIntSchema = false; boolean foundNullSchema = false; for (Schema type : fieldSchema.getTypes()) { if (type.getType().equals(Schema.Type.INT)) { foundIntSchema = true; } else if (type.getType().equals(Schema.Type.NULL)) { foundNullSchema = true; } } assertTrue(foundIntSchema); assertTrue(foundNullSchema); }
From source file:org.apache.kylin.rest.adhoc.AdHocRunnerJdbcImpl.java
@Override public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas) throws Exception { Statement statement = null;//from w w w . j a va 2 s. c o m Connection connection = this.getConnection(); ResultSet resultSet = null; try { statement = connection.createStatement(); resultSet = statement.executeQuery(query); extractResults(resultSet, results); } catch (SQLException sqlException) { throw sqlException; } //extract column metadata ResultSetMetaData metaData = null; int columnCount = 0; try { metaData = resultSet.getMetaData(); columnCount = metaData.getColumnCount(); // fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false, metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false)); } } catch (SQLException sqlException) { throw sqlException; } closeConnection(connection); }
From source file:org.ralasafe.db.DBView.java
private static TableView getTable(Connection conn, String dsName, String schema, String tableName) throws SQLException { String mySchema = ""; if (!StringUtil.isEmpty(schema)) { mySchema = schema + "."; }//from w w w .j a v a 2s.c om if (log.isDebugEnabled()) { log.debug("Get table/view definition: dsName=" + dsName + ", table/view Name=" + mySchema + tableName); } Statement stmt = null; ResultSet rs = null; ResultSet primaryKeys = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("select * from " + mySchema + tableName + " where 1=2"); ResultSetMetaData metaData = rs.getMetaData(); TableView table = new TableView(); table.setSchema(schema); table.setName(tableName); DatabaseMetaData metaData2 = conn.getMetaData(); String databaseProductName = DBUtil.getDatabaseProductName(conn); if (databaseProductName == DBUtil.MYSQL) { primaryKeys = metaData2.getPrimaryKeys(schema, null, tableName); } else { primaryKeys = metaData2.getPrimaryKeys(null, null, tableName); } Map pkColumnViewMap = new HashMap(); while (primaryKeys.next()) { pkColumnViewMap.put(primaryKeys.getString("COLUMN_NAME"), null); } List columnList = new ArrayList(metaData.getColumnCount()); for (int i = 1, columnCount = metaData.getColumnCount(); i <= columnCount; i++) { ColumnView column = new ColumnView(); String columnName = metaData.getColumnName(i); column.setName(columnName); String sqlType = metaData.getColumnTypeName(i); if (sqlType.equalsIgnoreCase("blob") || sqlType.equalsIgnoreCase("clob") || sqlType.equalsIgnoreCase("text")) { // DO NOTHING } else { int precision = metaData.getPrecision(i); int scale = metaData.getScale(i); if (precision != 0) { if (scale == 0) { sqlType = sqlType + "(" + precision + ")"; } else { sqlType = sqlType + "(" + precision + "," + scale + ")"; } } } column.setSqlType(sqlType); columnList.add(column); // it's a primary key? if (pkColumnViewMap.containsKey(columnName)) { pkColumnViewMap.put(columnName, column); } } table.setColumnViews(columnList); // sometimes, oracle jdbc driver returns pk info is redundance, // actually the column does exist at all. Clear them. clearInvalidPK(pkColumnViewMap); if (pkColumnViewMap.size() > 0) { table.setPkColumnViews(pkColumnViewMap.values()); } return table; } finally { DBUtil.close(primaryKeys); DBUtil.close(rs); DBUtil.close(stmt); } }