List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.apache.gobblin.source.jdbc.JdbcExtractor.java
private String parseColumnAsString(final ResultSet resultset, final ResultSetMetaData resultsetMetadata, int i) throws SQLException { if (isBlob(resultsetMetadata.getColumnType(i))) { return readBlobAsString(resultset.getBlob(i)); }//from ww w.j a va2s . c o m if (isClob(resultsetMetadata.getColumnType(i))) { return readClobAsString(resultset.getClob(i)); } if ((resultsetMetadata.getColumnType(i) == Types.BIT || resultsetMetadata.getColumnType(i) == Types.BOOLEAN) && convertBitToBoolean()) { return Boolean.toString(resultset.getBoolean(i)); } return resultset.getString(i); }
From source file:org.dspace.storage.rdbms.DatabaseManager.java
/** * Convert the current row in a ResultSet into a TableRow object. * * @param results//w ww .j a va 2 s . c o m * A ResultSet to process * @param table * The name of the table * @param pColumnNames * The name of the columns in this resultset * @return A TableRow object with the data from the ResultSet * @exception SQLException * If a database error occurs */ static TableRow process(ResultSet results, String table, List<String> pColumnNames) throws SQLException { ResultSetMetaData meta = results.getMetaData(); int columns = meta.getColumnCount() + 1; // If we haven't been passed the column names try to generate them from the metadata / table List<String> columnNames = pColumnNames != null ? pColumnNames : ((table == null) ? getColumnNames(meta) : getColumnNames(table)); TableRow row = new TableRow(canonicalize(table), columnNames); // Process the columns in order // (This ensures maximum backwards compatibility with // old JDBC drivers) for (int i = 1; i < columns; i++) { String name = meta.getColumnName(i); int jdbctype = meta.getColumnType(i); switch (jdbctype) { case Types.BIT: row.setColumn(name, results.getBoolean(i)); break; case Types.INTEGER: case Types.NUMERIC: if (isOracle) { long longValue = results.getLong(i); if (longValue <= (long) Integer.MAX_VALUE) { row.setColumn(name, (int) longValue); } else { row.setColumn(name, longValue); } } else { row.setColumn(name, results.getInt(i)); } break; case Types.DECIMAL: case Types.BIGINT: row.setColumn(name, results.getLong(i)); break; case Types.DOUBLE: row.setColumn(name, results.getDouble(i)); break; case Types.CLOB: if (isOracle) { row.setColumn(name, results.getString(i)); } else { throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } break; case Types.VARCHAR: try { byte[] bytes = results.getBytes(i); if (bytes != null) { String mystring = new String(results.getBytes(i), "UTF-8"); row.setColumn(name, mystring); } else { row.setColumn(name, results.getString(i)); } } catch (UnsupportedEncodingException e) { log.error("Unable to parse text from database", e); } break; case Types.DATE: row.setColumn(name, results.getDate(i)); break; case Types.TIME: row.setColumn(name, results.getTime(i)); break; case Types.TIMESTAMP: row.setColumn(name, results.getTimestamp(i)); break; default: throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype); } // Determines if the last column was null, and sets the tablerow accordingly if (results.wasNull()) { row.setColumnNull(name); } } // Now that we've prepped the TableRow, reset the flags so that we can detect which columns have changed row.resetChanged(); return row; }
From source file:org.openiot.gsn.storage.StorageManager.java
public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null;//from w w w . j a va 2 s . com DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; if (colName.equalsIgnoreCase("timed")) continue; int colType = structure.getColumnType(i); String colTypeName = structure.getColumnTypeName(i); int precision = structure.getPrecision(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR)) toReturnArr.add(new DataField(colName, colTypeName, precision, colName)); else toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; }
From source file:org.dspace.storage.rdbms.MockDatabaseManager.java
/** * Convert the current row in a ResultSet into a TableRow object. * * @param results//from w ww .j a va 2s .com * A ResultSet to process * @param table * The name of the table * @param pColumnNames * The name of the columns in this resultset * @return A TableRow object with the data from the ResultSet * @exception SQLException * If a database error occurs */ @Mock static TableRow process(ResultSet results, String table, List<String> pColumnNames) throws SQLException { String dbName = ConfigurationManager.getProperty("db.name"); ResultSetMetaData meta = results.getMetaData(); int columns = meta.getColumnCount() + 1; // If we haven't been passed the column names try to generate them from the metadata / table List<String> columnNames = pColumnNames != null ? pColumnNames : ((table == null) ? getColumnNames(meta) : getColumnNames(table)); TableRow row = new TableRow(canonicalize(table), columnNames); // Process the columns in order // (This ensures maximum backwards compatibility with // old JDBC drivers) for (int i = 1; i < columns; i++) { String name = meta.getColumnName(i); int jdbctype = meta.getColumnType(i); if (jdbctype == Types.BIT || jdbctype == Types.BOOLEAN) { row.setColumn(name, results.getBoolean(i)); } else if ((jdbctype == Types.INTEGER) || (jdbctype == Types.NUMERIC) || (jdbctype == Types.DECIMAL)) { // If we are using oracle if ("oracle".equals(dbName)) { // Test the value from the record set. If it can be represented using an int, do so. // Otherwise, store it as long long longValue = results.getLong(i); if (longValue <= (long) Integer.MAX_VALUE) row.setColumn(name, (int) longValue); else row.setColumn(name, longValue); } else row.setColumn(name, results.getInt(i)); } else if (jdbctype == Types.BIGINT) { row.setColumn(name, results.getLong(i)); } else if (jdbctype == Types.DOUBLE) { row.setColumn(name, results.getDouble(i)); } else if (jdbctype == Types.CLOB && "oracle".equals(dbName)) { // Support CLOBs in place of TEXT columns in Oracle row.setColumn(name, results.getString(i)); } else if (jdbctype == Types.VARCHAR) { /*try { byte[] bytes = results.getBytes(i); if (bytes != null) { String mystring = new String(results.getBytes(i), "UTF-8"); row.setColumn(name, mystring); } else { row.setColumn(name, results.getString(i)); } } catch (UnsupportedEncodingException e) { // do nothing, UTF-8 is built in! }*/ //removing issue with H2 and getBytes row.setColumn(name, results.getString(i)); } else if (jdbctype == Types.DATE) { row.setColumn(name, results.getDate(i)); } else if (jdbctype == Types.TIME) { row.setColumn(name, results.getTime(i)); } else if (jdbctype == Types.TIMESTAMP) { row.setColumn(name, results.getTimestamp(i)); } else { throw new IllegalArgumentException("Unsupported JDBC type: " + jdbctype + " (" + name + ")"); } if (results.wasNull()) { row.setColumnNull(name); } } // Now that we've prepped the TableRow, reset the flags so that we can detect which columns have changed row.resetChanged(); return row; }
From source file:org.cloudgraph.rdb.service.JDBCSupport.java
protected List<List<PropertyPair>> fetch(PlasmaType type, StringBuilder sql, Set<Property> props, Object[] params, Connection con) { List<List<PropertyPair>> result = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null;/*from w w w . j ava2s. co m*/ try { if (log.isDebugEnabled()) { if (params == null || params.length == 0) { log.debug("fetch: " + sql.toString()); } else { StringBuilder paramBuf = new StringBuilder(); paramBuf.append(" ["); for (int p = 0; p < params.length; p++) { if (p > 0) paramBuf.append(", "); paramBuf.append(String.valueOf(params[p])); } paramBuf.append("]"); log.debug("fetch: " + sql.toString() + " " + paramBuf.toString()); } } statement = con.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, /* * ResultSet * . * TYPE_SCROLL_INSENSITIVE * , */ ResultSet.CONCUR_READ_ONLY); for (int i = 0; i < params.length; i++) statement.setString(i + 1, // FIXME String.valueOf(params[i])); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); int count = 0; while (rs.next()) { List<PropertyPair> row = new ArrayList<PropertyPair>(numcols); result.add(row); for (int i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); PlasmaProperty valueProp = prop; while (!valueProp.getType().isDataType()) { valueProp = getOppositePriKeyProperty(valueProp); } Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); if (!valueProp.equals(prop)) pair.setValueProp(valueProp); if (!props.contains(prop)) pair.setQueryProperty(false); row.add(pair); } } count++; } if (log.isDebugEnabled()) log.debug("returned " + count + " results"); } catch (Throwable t) { throw new DataAccessException(t); } finally { try { if (rs != null) rs.close(); if (statement != null) statement.close(); } catch (SQLException e) { log.error(e.getMessage(), e); } } return result; }
From source file:gsn.storage.StorageManager.java
public DataField[] tableToStructureByString(String tableName, Connection connection) throws SQLException { StringBuilder sb = new StringBuilder("select * from ").append(tableName).append(" where 1=0 "); ResultSet rs = null;//from w w w .j a v a 2s . c o m DataField[] toReturn = null; try { rs = executeQueryWithResultSet(sb, connection); ResultSetMetaData structure = rs.getMetaData(); ArrayList<DataField> toReturnArr = new ArrayList<DataField>(); for (int i = 1; i <= structure.getColumnCount(); i++) { String colName = structure.getColumnLabel(i); if (colName.equalsIgnoreCase("pk")) continue; if (colName.equalsIgnoreCase("timed")) continue; int colType = structure.getColumnType(i); String colTypeName = structure.getColumnTypeName(i); int precision = structure.getPrecision(i); byte colTypeInGSN = convertLocalTypeToGSN(colType); if (colTypeInGSN == -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 ((colTypeInGSN == DataTypes.VARCHAR) || (colTypeInGSN == DataTypes.CHAR)) toReturnArr.add(new DataField(colName, colTypeName, precision, colName)); else toReturnArr.add(new DataField(colName, colTypeInGSN)); } toReturn = toReturnArr.toArray(new DataField[] {}); } finally { if (rs != null) close(rs); } return toReturn; }
From source file:org.kawanfw.sql.servlet.sql.ResultSetWriter.java
/** * Code extracted and modified for oreilly jdbc book in french. * //from w w w. j av a2 s . c o m * Process the ResultSet and print it on the outPutStream <br> * - Each row is a line of a List of column values <br> * * @param resultSet * the Result Set to process and print on the output stream * @param br * the writer where to redirect the result set content, one Json * line per rs.next(); * */ public void write(ResultSet resultSet) throws SQLException, IOException { try { if (resultSet == null) { throw new SQLException("SQL Connection is null!"); } String productName = getDatabaseProductName(resultSet); isTerradata = productName.equals(SqlUtil.TERADATA) ? true : false; isPostgreSQL = productName.equals(SqlUtil.POSTGRESQL) ? true : false; ResultSetMetaData meta = resultSet.getMetaData(); int cols = meta.getColumnCount(); @SuppressWarnings("unused") int row_count = 0; List<Integer> columnTypeList = new Vector<Integer>(); List<String> columnTypeNameList = new Vector<String>(); List<String> columnNameList = new Vector<String>(); List<String> columnTableList = new Vector<String>(); // Loop on Columns for (int i = 1; i <= cols; i++) { columnTypeList.add(meta.getColumnType(i)); columnNameList.add(meta.getColumnName(i).toLowerCase()); columnTypeNameList.add(meta.getColumnTypeName(i)); if (isPostgreSQL) { columnTableList.add(PostgreSqlUtil.getTableName(resultSet, i)); } else { columnTableList.add(meta.getTableName(i)); } debug(""); debug("meta.getColumnType(" + i + ") : " + meta.getColumnType(i)); debug("meta.getColumnTypeName(" + i + "): " + meta.getColumnTypeName(i)); debug("meta.getColumnName(" + i + ") : " + meta.getColumnName(i)); debug("meta.getTableName(" + i + ") : " + meta.getTableName(i)); } // Ok, dump the column Map<String, Integer> == (Column name, column // pos starting 9) Map<String, Integer> mapColumnNames = new LinkedHashMap<String, Integer>(); for (int i = 0; i < columnNameList.size(); i++) { mapColumnNames.put(columnNameList.get(i), i); } String jsonString = JsonColPosition.toJson(mapColumnNames); debug("JsonColPosition.toJson(mapColumnNames) jsonString" + jsonString); boolean doEncryptResultSet = SqlConfiguratorCall.encryptResultSet(sqlConfigurator); // Maybe encryption asked if (doEncryptResultSet) { jsonString = JsonLineEncrypter.encrypt(jsonString, commonsConfigurator); } //br.write(jsonString + CR_LF); ServerSqlManager.writeLine(out, jsonString); // Loop result Set while (resultSet.next()) { row_count++; // The List of column values for one row List<String> values = new Vector<String>(); for (int i = 0; i < columnTypeList.size(); i++) { int columnIndex = i + 1; int columnType = columnTypeList.get(i); String columnName = columnNameList.get(i); String columnTable = columnTableList.get(i); debug(""); debug("columnIndex: " + columnIndex); debug("columnType : " + columnType); debug("columnName : " + columnName); debug("columnTable: " + columnTable); Object columnValue = null; String columnValueStr = null; if (isBinaryColumn(resultSet, columnType, columnName, columnTable)) { columnValueStr = formatBinaryColumn(resultSet, columnIndex, columnType, columnName, columnTable); } else if (isNStringColumn(columnType)) { columnValueStr = resultSet.getNString(columnIndex); columnValueStr = HtmlConverter.toHtml(columnValueStr); } else if (isClobColumn(columnType)) { columnValueStr = formatClobColumn(resultSet, columnIndex); } else if (columnType == Types.ARRAY) { columnValueStr = formatArrayColumn(resultSet, columnIndex); } else if (columnType == Types.ROWID) { columnValueStr = formatRowIdColumn(resultSet, columnIndex); } else { try { columnValue = resultSet.getObject(columnIndex); debug("columnValue: " + columnValue); } catch (Exception e) { // int intValue = resultSet.getInt(columnName); debug("Exception : " + e.toString()); debug("columnType : " + columnType); debug("columnTypeName: " + columnTypeNameList.get(i)); debug("columnName : " + columnName); throw new SQLException(columnType + "Type/TypeName/ColName " + columnTypeNameList.get(i) + " " + columnName, e); } if (resultSet.wasNull()) { columnValueStr = "NULL"; } else if (columnValue == null) { columnValueStr = null; } else { columnValueStr = columnValue.toString(); } debug("columnValueStr : " + columnValueStr); // Case we - maybe - have an URL: columnValueStr = urlFormater(resultSet, columnIndex, columnValueStr); if (isCharacterType(columnType)) { debugStringType(columnValueStr); columnValueStr = HtmlConverter.toHtml(columnValueStr); } } // Add the value to the list of values: values.add(columnValueStr); } jsonString = StringListTransport.toJson(values); debug("ResultSetLineTransport.toJson(values) jsonString" + jsonString); // Maybe encryption asked if (doEncryptResultSet) { jsonString = JsonLineEncrypter.encrypt(jsonString, commonsConfigurator); } //br.write(jsonString + CR_LF); ServerSqlManager.writeLine(out, jsonString); } // Maybe we send also the ResultSet meta data, if asked by client // side if (JoinResultSetMetaData) { //br.write(FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP // + CR_LF); ServerSqlManager.writeLine(out, FileSplitSeparatorLine.RESULT_SET_GET_METADATA_SEP); ResultSetMetaDataWriter resultSetMetaDataWriter = new ResultSetMetaDataWriter(out, commonsConfigurator, sqlConfigurator); resultSetMetaDataWriter.write(resultSet); } } finally { // NO! resultSet.close(); // NO! IOUtils.closeQuietly(br); } }
From source file:ch.rgw.tools.JdbcLink.java
public boolean dumpTable(BufferedWriter w, String name) throws Exception { Stm stm = getStatement();//from w w w. j a v a2 s. co m ResultSet res = stm.query("SELECT * from " + name); ResultSetMetaData rm = res.getMetaData(); int cols = rm.getColumnCount(); String[] ColNames = new String[cols]; int[] colTypes = new int[cols]; w.write("CREATE TABLE " + name + "("); for (int i = 0; i < cols; i++) { ColNames[i] = rm.getColumnName(i + 1); colTypes[i] = rm.getColumnType(i + 1); w.write(ColNames[i] + " " + colTypes[i] + ",\n"); } w.write(");"); while ((res != null) && (res.next() == true)) { w.write("INSERT INTO " + name + " ("); for (int i = 0; i < cols; i++) { w.write(ColNames[i]); if (i < cols - 1) { w.write(","); } } w.write(") VALUES ("); for (int i = 0; i < cols; i++) { Object o = res.getObject(ColNames[i]); switch (JdbcLink.generalType(colTypes[i])) { case JdbcLink.INTEGRAL: if (o == null) { w.write("0"); } else { w.write(Integer.toString(((Integer) o).intValue())); } break; case JdbcLink.TEXT: if (o == null) { w.write(JdbcLink.wrap("null")); } else { w.write(JdbcLink.wrap((String) o)); } break; default: String t = o.getClass().getName(); log.log("Unknown type " + t, Log.ERRORS); throw new Exception("Cant write " + t); } if (i < cols - 1) { w.write(","); } } w.write(");"); w.newLine(); } res.close(); releaseStatement(stm); return true; }
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 w w w .j a v a 2s . c o 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; }
From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java
public List<ColumnVO> getColumnsMetaData(String sql) throws Exception { Exception error = null;//from w w w. j a v a 2s . c o m List<ColumnVO> tableColumns = new LinkedList<ColumnVO>(); Connection connection = null; PreparedStatement preparedStmnt = null; try { DataSource dataSource = poolDataSources.get(schemaId); connection = dataSource.getConnection(); connection.setAutoCommit(false); preparedStmnt = connection.prepareStatement(sql); ResultSet rs = preparedStmnt.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String columnName = rsmd.getColumnName(i); String columnType = rsmd.getColumnTypeName(i); int columnSqlType = rsmd.getColumnType(i); int columnLength = rsmd.getColumnDisplaySize(i); int columnPrecision = rsmd.getPrecision(i); ColumnVO column = new ColumnVO(); column.setNameOnTable(columnName); column.setType(columnType); column.setSqlType(columnSqlType); column.setLength(columnLength); column.setPrecision(columnPrecision); column.setInTable(true); tableColumns.add(column); } } catch (SQLException e) { error = e; } finally { if (preparedStmnt != null) { try { preparedStmnt.close(); } catch (SQLException se2) { log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage())); } } 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 tableColumns; }