List of usage examples for java.sql ResultSetMetaData getScale
int getScale(int column) throws SQLException;
From source file:org.openiot.gsn.storage.StorageManager.java
/** * Returns false if the table doesnt exist. If the table exists but the * structure is not compatible with the specified fields the method throws * GSNRuntimeException. Note that this method doesn't close the connection * * @param tableName//from w ww. j av a 2 s .c om * @param connection (this method will not close it and the caller is responsible * for closing the connection) * @return * @throws SQLException * @Throws GSNRuntimeException */ public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection) throws SQLException, GSNRuntimeException { if (!ValidityTools.isValidJavaVariable(tableName)) throw new GSNRuntimeException("Table name is not valid"); StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName)) .append(" where 1=0 "); ResultSet rs = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); if (fields != null && fields.length > 0) nextField: for (DataField field : fields) { for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); int colType = structure.getColumnType(i); int colTypeScale = structure.getScale(i); if (field.getName().equalsIgnoreCase(colName)) if (field.getDataTypeID() == convertLocalTypeToGSN(colType, colTypeScale)) continue nextField; else throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName + "< table is not compatible with type : " + field.getType() + ". The actual type for this table (currently in the database): " + colType); } throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >" + field.getName() + "< column."); } } catch (SQLException e) { if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist")) return false; else { logger.error(e.getErrorCode()); throw e; } } finally { close(rs); } return true; }
From source file:gsn.storage.StorageManager.java
/** * Returns false if the table doesnt exist. If the table exists but the * structure is not compatible with the specified fields the method throws * GSNRuntimeException. Note that this method doesn't close the connection * * @param tableName/*from ww w. j av a2 s .c o m*/ * @param connection (this method will not close it and the caller is responsible * for closing the connection) * @return * @throws SQLException * @Throws GSNRuntimeException */ public boolean tableExists(CharSequence tableName, DataField[] fields, Connection connection) throws SQLException, GSNRuntimeException { if (!ValidityTools.isValidJavaVariable(tableName)) throw new GSNRuntimeException("Table name is not valid"); StringBuilder sb = new StringBuilder("select * from ").append(tableNameGeneratorInString(tableName)) .append(" where 1=0 "); ResultSet rs = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); if (fields != null && fields.length > 0) nextField: for (DataField field : fields) { for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); int colType = structure.getColumnType(i); int colTypeScale = structure.getScale(i); if (field.getName().equalsIgnoreCase(colName)) { byte gsnType = convertLocalTypeToGSN(colType, colTypeScale); if (gsnType == -100) { logger.error( "The type can't be converted to GSN form - error description: virtual sensor name is: " + tableName + ", field name is: " + colName + ", query is: " + sb); } if (field.getDataTypeID() == gsnType) continue nextField; else throw new GSNRuntimeException("The column : " + colName + " in the >" + tableName + "< table is not compatible with type : " + field.getType() + ". The actual type for this table (currently in the database): " + colType); } } throw new GSNRuntimeException("The table " + tableName + " in the database, doesn't have the >" + field.getName() + "< column."); } } catch (SQLException e) { if (e.getErrorCode() == getTableNotExistsErrNo() || e.getMessage().contains("does not exist")) return false; else { logger.error(e.getMessage()); throw e; } } finally { close(rs); } return true; }
From source file:mondrian.spi.impl.JdbcDialectImpl.java
public SqlStatement.Type getType(ResultSetMetaData metaData, int columnIndex) throws SQLException { final int columnType = metaData.getColumnType(columnIndex + 1); SqlStatement.Type internalType = null; if (columnType != Types.NUMERIC && columnType != Types.DECIMAL) { internalType = DEFAULT_TYPE_MAP.get(columnType); } else {/*from w w w . ja v a 2 s .c om*/ final int precision = metaData.getPrecision(columnIndex + 1); final int scale = metaData.getScale(columnIndex + 1); if (scale == 0 && precision <= 9) { // An int (up to 2^31 = 2.1B) can hold any NUMBER(10, 0) value // (up to 10^9 = 1B). internalType = SqlStatement.Type.INT; } else { internalType = SqlStatement.Type.DOUBLE; } } internalType = internalType == null ? SqlStatement.Type.OBJECT : internalType; logTypeInfo(metaData, columnIndex, internalType); return internalType; }
From source file:com.streamsets.pipeline.stage.processor.parser.sql.SqlParserProcessor.java
private void resolveSchema(SchemaAndTable schemaAndTable) throws StageException { Map<String, Integer> columns = new HashMap<>(); String schema = schemaAndTable.getSchema(); String table = schemaAndTable.getTable(); try (Statement s = connection.createStatement()) { ResultSetMetaData md = s .executeQuery(Utils.format("SELECT * FROM {}{} WHERE 1 = 0", StringUtils.isNotEmpty(schema) ? "\"" + schema + "\"." : "", "\"" + table + "\"")) .getMetaData();//ww w .j a v a2 s .c om int colCount = md.getColumnCount(); for (int i = 1; i <= colCount; i++) { int colType = md.getColumnType(i); String colName = md.getColumnName(i); if (!configBean.caseSensitive) { colName = colName.toUpperCase(); } if (colType == Types.DATE || colType == Types.TIME || colType == Types.TIMESTAMP) { dateTimeColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()); dateTimeColumns.get(schemaAndTable).put(colName, md.getColumnTypeName(i)); } if (colType == Types.DECIMAL || colType == Types.NUMERIC) { decimalColumns.computeIfAbsent(schemaAndTable, k -> new HashMap<>()).put(colName, new PrecisionAndScale(md.getPrecision(i), md.getScale(i))); } columns.put(md.getColumnName(i), md.getColumnType(i)); } tableSchemas.put(schemaAndTable, columns); } catch (SQLException ex) { throw new StageException(JDBC_00, configBean.hikariConfigBean.connectionString); } }
From source file:kenh.xscript.database.beans.ResultSetBean.java
/** * Use result set to initial a bean.// w w w .j a v a 2 s . c o m * * @param rs * @param includeFieldName * @throws SQLException * @throws IllegalAccessException * @throws InstantiationException */ public ResultSetBean(ResultSet rs, boolean includeFieldName) throws SQLException, IllegalAccessException, InstantiationException { include_field_name = includeFieldName; LazyDynaClass beanClass = new LazyDynaClass(); ResultSetMetaData m = rs.getMetaData(); for (int i = 1; i <= m.getColumnCount(); i++) { Column c = new Column(); try { c.catalogName = m.getCatalogName(i); } catch (SQLException e) { } try { c.className = m.getColumnClassName(i); } catch (SQLException e) { } try { c.displaySize = m.getColumnDisplaySize(i); } catch (SQLException e) { } try { c.label = m.getColumnLabel(i); } catch (SQLException e) { } try { c.name = m.getColumnName(i); } catch (SQLException e) { } try { c.type = m.getColumnType(i); } catch (SQLException e) { } try { c.typeName = m.getColumnTypeName(i); } catch (SQLException e) { } try { c.precision = m.getPrecision(i); } catch (SQLException e) { } try { c.scale = m.getScale(i); } catch (SQLException e) { } try { c.schemaName = m.getSchemaName(i); } catch (SQLException e) { } try { c.tableName = m.getTableName(i); } catch (SQLException e) { } beanClass.add(m.getColumnLabel(i).toLowerCase()); beanClass.add("" + i); cols.add(c); } DynaBean colBean = beanClass.newInstance(); int i = 1; for (Column col : cols) { String field = col.getLabel().toLowerCase(); colBean.set(field, col.getLabel()); colBean.set("" + i, col.getLabel()); i++; } if (include_field_name) rows.add(colBean); while (rs.next()) { DynaBean bean = beanClass.newInstance(); i = 1; for (Column c : cols) { String field = c.getLabel().toLowerCase(); Object obj = rs.getObject(field); bean.set(field, obj); bean.set("" + i, obj); i++; } rows.add(bean); } }
From source file:net.ymate.platform.persistence.jdbc.scaffold.EntityGenerator.java
/** * @param dbName ???/* w ww. j av a2s. c om*/ * @param dbUserName ?? * @param tableName ?? * @return ????? */ private TableMeta getTableMeta(String dbName, String dbUserName, String tableName) { IConnectionHolder _connHolder = null; Statement _statement = null; ResultSet _resultSet = null; Map<String, ColumnInfo> _tableFields = new LinkedHashMap<String, ColumnInfo>(); List<String> _pkFields = new LinkedList<String>(); TableMeta _meta = new TableMeta(_pkFields, _tableFields); try { _connHolder = __jdbc.getDefaultConnectionHolder(); String _dbType = _connHolder.getDialect().getName(); DatabaseMetaData _dbMetaData = _connHolder.getConnection().getMetaData(); System.out.println(">>> Catalog: " + dbName); System.out.println(">>> Schema: " + dbUserName); System.out.println(">>> Table: " + tableName); _resultSet = _dbMetaData.getPrimaryKeys(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName); if (_resultSet == null) { System.err.println("Database table \"" + tableName + "\" primaryKey resultSet is null, ignored"); return null; } else { while (_resultSet.next()) { _pkFields.add(_resultSet.getString(4).toLowerCase()); } if (_pkFields.isEmpty()) { System.err .println("Database table \"" + tableName + "\" does not set the primary key, ignored"); return null; } else { // System.out.println(">>> " + "COLUMN_NAME / " + "COLUMN_CLASS_NAME / " + "PRIMARY_KEY / " + "AUTO_INCREMENT / " + "SIGNED / " + "PRECISION / " + "SCALE / " + "NULLABLE / " + "DEFAULT / " + "REMARKS"); // _statement = _connHolder.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); _resultSet = _statement.executeQuery( "SELECT * FROM ".concat(_connHolder.getDialect().wrapIdentifierQuote(tableName))); ResultSetMetaData _rsMetaData = _resultSet.getMetaData(); // for (int _idx = 1; _idx <= _rsMetaData.getColumnCount(); _idx++) { // ?? ResultSet _column = _dbMetaData.getColumns(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName, _rsMetaData.getColumnName(_idx)); if (_column.next()) { // ???? _tableFields.put(_rsMetaData.getColumnName(_idx).toLowerCase(), new ColumnInfo(_rsMetaData.getColumnName(_idx).toLowerCase(), _rsMetaData.getColumnClassName(_idx), _rsMetaData.isAutoIncrement(_idx), _rsMetaData.isSigned(_idx), _rsMetaData.getPrecision(_idx), _rsMetaData.getScale(_idx), _rsMetaData.isNullable(_idx), _column.getString("COLUMN_DEF"), _column.getString("REMARKS"))); System.out.println("--> " + _rsMetaData.getColumnName(_idx).toLowerCase() + "\t" + _rsMetaData.getColumnClassName(_idx) + "\t" + _pkFields.contains(_rsMetaData.getColumnName(_idx).toLowerCase()) + "\t" + _rsMetaData.isAutoIncrement(_idx) + "\t" + _rsMetaData.isSigned(_idx) + "\t" + _rsMetaData.getPrecision(_idx) + "\t" + _rsMetaData.getScale(_idx) + "\t" + _rsMetaData.isNullable(_idx) + "\t" + _column.getString("COLUMN_DEF") + "\t" + _column.getString("REMARKS")); } _column.close(); } } } } catch (Exception e) { if (e instanceof RuntimeException) { throw (RuntimeException) e; } throw new RuntimeException(e); } finally { if (_statement != null) { try { _statement.close(); } catch (SQLException e) { _LOG.warn("", e); } } if (_resultSet != null) { try { _resultSet.close(); } catch (SQLException e) { _LOG.warn("", e); } } if (_connHolder != null) { _connHolder.release(); } } return _meta; }
From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java
public void setColumnSpecificHeaders(Record record, Set<String> knownTableNames, ResultSetMetaData metaData, String jdbcNameSpacePrefix) throws SQLException { Record.Header header = record.getHeader(); Set<String> tableNames = new HashSet<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".jdbcType", String.valueOf(metaData.getColumnType(i))); // Additional headers per various types switch (metaData.getColumnType(i)) { case Types.DECIMAL: case Types.NUMERIC: header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".scale", String.valueOf(metaData.getScale(i))); header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".precision", String.valueOf(metaData.getPrecision(i))); break; }//from w ww. j a va2 s .com String tableName = metaData.getTableName(i); // Store the column's table name (if not empty) if (StringUtils.isNotEmpty(tableName)) { tableNames.add(tableName); } } if (tableNames.isEmpty()) { tableNames.addAll(knownTableNames); } header.setAttribute(jdbcNameSpacePrefix + "tables", Joiner.on(",").join(tableNames)); }
From source file:org.apache.bigtop.itest.hive.TestJdbc.java
@Test public void preparedStmtAndResultSet() throws SQLException { final String tableName = "bigtop_jdbc_psars_test_table"; try (Statement stmt = conn.createStatement()) { stmt.execute("drop table if exists " + tableName); stmt.execute("create table " + tableName + " (bo boolean, ti tinyint, db double, fl float, " + "i int, lo bigint, sh smallint, st varchar(32))"); }/*from www . j ava 2s . c o m*/ // NOTE Hive 1.2 theoretically support binary, Date & Timestamp in JDBC, but I get errors when I // try to put them in the query. try (PreparedStatement ps = conn .prepareStatement("insert into " + tableName + " values (?, ?, ?, ?, ?, ?, ?, ?)")) { ps.setBoolean(1, true); ps.setByte(2, (byte) 1); ps.setDouble(3, 3.141592654); ps.setFloat(4, 3.14f); ps.setInt(5, 3); ps.setLong(6, 10L); ps.setShort(7, (short) 20); ps.setString(8, "abc"); ps.executeUpdate(); } try (PreparedStatement ps = conn.prepareStatement("insert into " + tableName + " (i, st) " + "values(?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) { ps.setNull(1, Types.INTEGER); ps.setObject(2, "mary had a little lamb"); ps.executeUpdate(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.clearParameters(); ps.setNull(1, Types.INTEGER, null); ps.setString(2, "its fleece was white as snow"); ps.execute(); } try (Statement stmt = conn.createStatement()) { ResultSet rs = stmt.executeQuery("select * from " + tableName); ResultSetMetaData md = rs.getMetaData(); int colCnt = md.getColumnCount(); LOG.debug("Column count is " + colCnt); for (int i = 1; i <= colCnt; i++) { LOG.debug("Looking at column " + i); String strrc = md.getColumnClassName(i); LOG.debug("Column class name is " + strrc); int intrc = md.getColumnDisplaySize(i); LOG.debug("Column display size is " + intrc); strrc = md.getColumnLabel(i); LOG.debug("Column label is " + strrc); strrc = md.getColumnName(i); LOG.debug("Column name is " + strrc); intrc = md.getColumnType(i); LOG.debug("Column type is " + intrc); strrc = md.getColumnTypeName(i); LOG.debug("Column type name is " + strrc); intrc = md.getPrecision(i); LOG.debug("Precision is " + intrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); boolean boolrc = md.isAutoIncrement(i); LOG.debug("Is auto increment? " + boolrc); boolrc = md.isCaseSensitive(i); LOG.debug("Is case sensitive? " + boolrc); boolrc = md.isCurrency(i); LOG.debug("Is currency? " + boolrc); intrc = md.getScale(i); LOG.debug("Scale is " + intrc); intrc = md.isNullable(i); LOG.debug("Is nullable? " + intrc); boolrc = md.isReadOnly(i); LOG.debug("Is read only? " + boolrc); } while (rs.next()) { LOG.debug("bo = " + rs.getBoolean(1)); LOG.debug("bo = " + rs.getBoolean("bo")); LOG.debug("ti = " + rs.getByte(2)); LOG.debug("ti = " + rs.getByte("ti")); LOG.debug("db = " + rs.getDouble(3)); LOG.debug("db = " + rs.getDouble("db")); LOG.debug("fl = " + rs.getFloat(4)); LOG.debug("fl = " + rs.getFloat("fl")); LOG.debug("i = " + rs.getInt(5)); LOG.debug("i = " + rs.getInt("i")); LOG.debug("lo = " + rs.getLong(6)); LOG.debug("lo = " + rs.getLong("lo")); LOG.debug("sh = " + rs.getShort(7)); LOG.debug("sh = " + rs.getShort("sh")); LOG.debug("st = " + rs.getString(8)); LOG.debug("st = " + rs.getString("st")); LOG.debug("tm = " + rs.getObject(8)); LOG.debug("tm = " + rs.getObject("st")); LOG.debug("tm was null " + rs.wasNull()); } LOG.debug("bo is column " + rs.findColumn("bo")); int intrc = rs.getConcurrency(); LOG.debug("concurrency " + intrc); intrc = rs.getFetchDirection(); LOG.debug("fetch direction " + intrc); intrc = rs.getType(); LOG.debug("type " + intrc); Statement copy = rs.getStatement(); SQLWarning warning = rs.getWarnings(); while (warning != null) { LOG.debug("Found a warning: " + warning.getMessage()); warning = warning.getNextWarning(); } rs.clearWarnings(); } }
From source file:org.apache.kylin.rest.service.QueryService.java
/** * @param correctedSql//from w w w . j ava 2 s . com * @param sqlRequest * @return * @throws Exception */ private SQLResponse execute(String correctedSql, SQLRequest sqlRequest) throws Exception { Connection conn = null; Statement stat = null; ResultSet resultSet = null; Boolean isPushDown = false; List<List<String>> results = Lists.newArrayList(); List<SelectedColumnMeta> columnMetas = Lists.newArrayList(); try { conn = cacheService.getOLAPDataSource(sqlRequest.getProject()).getConnection(); // special case for prepare query. if (BackdoorToggles.getPrepareOnly()) { return getPrepareOnlySqlResponse(correctedSql, conn, isPushDown, results, columnMetas); } stat = conn.createStatement(); processStatementAttr(stat, sqlRequest); resultSet = stat.executeQuery(correctedSql); ResultSetMetaData metaData = resultSet.getMetaData(); int 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), 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.getCatalogName(i), metaData.getTableName(i), metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), metaData.isWritable(i), metaData.isDefinitelyWritable(i))); } // fill in results while (resultSet.next()) { List<String> oneRow = Lists.newArrayListWithCapacity(columnCount); for (int i = 0; i < columnCount; i++) { oneRow.add((resultSet.getString(i + 1))); } results.add(oneRow); } } catch (SQLException sqlException) { isPushDown = PushDownUtil.doPushDownQuery(sqlRequest.getProject(), correctedSql, results, columnMetas, sqlException); } finally { close(resultSet, stat, conn); } return getSqlResponse(isPushDown, results, columnMetas); }
From source file:com.glaf.dts.transform.MxTransformManager.java
@SuppressWarnings("unchecked") public TableDefinition toTableDefinition(QueryDefinition query, String currentSql) { if (query.getId() != null && query.getParentId() != null) { query = this.fill(query.getId(), currentSql); }/*from ww w.ja va2 s .co m*/ if (query.getParentId() != null) { QueryDefinition parent = this.fill(query.getParentId(), null); if (parent != null) { logger.debug("parent:" + parent.getTitle()); logger.debug("resultList:" + parent.getResultList()); query.setParent(parent); } } String sql = currentSql; List<Object> values = null; logger.debug("currentSql:" + currentSql); if (query.getParentId() != null) { if (query.getParent() != null && query.getParent().getResultList() != null && !query.getParent().getResultList().isEmpty()) { for (Map<String, Object> paramMap : query.getParent().getResultList()) { SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, paramMap); sql = sqlExecutor.getSql(); sql = QueryUtils.replaceSQLVars(sql); values = (List<Object>) sqlExecutor.getParameter(); break; } } } else { if (sql != null && sql.indexOf("${") != -1) { sql = QueryUtils.replaceSQLVars(sql); SqlExecutor sqlExecutor = JdbcUtils.rebuildSQL(sql, new java.util.HashMap<String, Object>()); if (sqlExecutor != null) { sql = sqlExecutor.getSql(); sql = QueryUtils.replaceSQLVars(sql); values = (List<Object>) sqlExecutor.getParameter(); } } } logger.debug("sql:" + sql); logger.debug("values:" + values); TableDefinition table = new TableDefinition(); Connection conn = null; PreparedStatement psmt = null; ResultSet rs = null; ResultSetMetaData rsmd = null; try { Database database = databaseService.getDatabaseById(query.getDatabaseId()); if (database != null) { conn = DBConnectionFactory.getConnection(database.getName()); } else { conn = DBConnectionFactory.getConnection(); } sql = QueryUtils.replaceSQLVars(sql); psmt = conn.prepareStatement(sql); if (values != null && !values.isEmpty()) { JdbcUtils.fillStatement(psmt, values); } rs = psmt.executeQuery(); rsmd = rs.getMetaData(); int count = rsmd.getColumnCount(); for (int i = 1; i <= count; i++) { int sqlType = rsmd.getColumnType(i); ColumnDefinition column = new ColumnDefinition(); column.setColumnName(rsmd.getColumnName(i)); column.setColumnLabel(rsmd.getColumnLabel(i)); column.setJavaType(FieldType.getJavaType(sqlType)); column.setPrecision(rsmd.getPrecision(i)); column.setScale(rsmd.getScale(i)); table.addColumn(column); logger.debug("----------------------------------------"); logger.debug("sqlType:" + sqlType); logger.debug("javaType:" + FieldType.getJavaType(sqlType)); logger.debug("columnName:" + rsmd.getColumnName(i)); logger.debug("columnTypeName:" + rsmd.getColumnTypeName(i)); logger.debug("columnClassName:" + rsmd.getColumnClassName(i)); logger.debug("columnLabel:" + rsmd.getColumnLabel(i)); logger.debug("columnDisplaySize:" + rsmd.getColumnDisplaySize(i)); logger.debug("precision:" + rsmd.getPrecision(i)); logger.debug("scale:" + rsmd.getScale(i)); } } catch (Exception ex) { logger.error(ex); ex.printStackTrace(); throw new RuntimeException(ex); } finally { JdbcUtils.close(rs); JdbcUtils.close(psmt); JdbcUtils.close(conn); } return table; }