List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.hyperic.hq.product.JDBCMeasurementPlugin.java
protected void processColumns(ResultSet rs) throws SQLException { while (rs.next()) { _numRows++;/* w w w. jav a 2 s. c om*/ ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) { Integer ind = new Integer(i); String val = null; if (rs.getObject(i) == null) { val = "()"; } else { try { // XXX ignoring BLOBs for now if (rsmd.getColumnType(i) == -2) { } else { val = rs.getString(i).trim(); } if (_data == null) { _data = new Double(val); } } catch (Exception e) { val = ""; } } ((List) _valMap.get(ind)).add(val); if (val.length() > ((Integer) _colMap.get(ind)).intValue()) { _colMap.put(ind, new Integer(val.length())); } } } }
From source file:io.lightlink.oracle.AbstractOracleType.java
protected STRUCT createStruct(Connection con, Object value, String type) throws SQLException { if (value == null) return null; Map mapValue;//from w w w . j a va 2 s . co m if (value instanceof Map) { mapValue = (Map) value; mapValue = new CaseInsensitiveMap(mapValue); } else { // create a Map from bean Map map = new CaseInsensitiveMap(new BeanMap(value)); map.remove("class"); mapValue = map; } STRUCT struct; StructDescriptor structType = safeCreateStructureDescriptor(type, con); ResultSetMetaData stuctMeteData = structType.getMetaData(); List<Object> orderedValues = new ArrayList<Object>(); if (stuctMeteData.getColumnCount() == 1 && mapValue.size() == 1) { orderedValues.add(mapValue.values().iterator().next()); } else { for (int col = 1; col <= stuctMeteData.getColumnCount(); col++) { Object v = mapValue.get(stuctMeteData.getColumnName(col)); if (v == null) { v = mapValue.get(stuctMeteData.getColumnName(col).replaceAll("_", "")); } String typeName = stuctMeteData.getColumnTypeName(col); int columnType = stuctMeteData.getColumnType(col); if (columnType == OracleTypes.ARRAY) { v = createArray(con, v, typeName); } else if (columnType == OracleTypes.JAVA_STRUCT || columnType == OracleTypes.JAVA_OBJECT || columnType == OracleTypes.STRUCT) { v = createStruct(con, v, typeName); } orderedValues.add(v); } } Object[] values = orderedValues.toArray(); for (int j = 0; j < values.length; j++) { Object v = values[j]; if (v instanceof Long && stuctMeteData.getColumnTypeName(j + 1).equalsIgnoreCase("TIMESTAMP")) { values[j] = new Timestamp((Long) v); } else if (v instanceof Long && stuctMeteData.getColumnTypeName(j + 1).equalsIgnoreCase("DATE")) { values[j] = new Date((Long) v); } } struct = new STRUCT(structType, con, values); return struct; }
From source file:CSVWriter.java
public String[] getColumnValues(ResultSet rs) throws SQLException, IOException { List<String> values = new ArrayList<String>(); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { values.add(getColumnValue(rs, metadata.getColumnType(i + 1), i + 1)); }//from w ww. ja v a2 s.c o m String[] valueArray = new String[values.size()]; return values.toArray(valueArray); }
From source file:com.cnd.greencube.server.dao.jdbc.JdbcDAO.java
@SuppressWarnings("rawtypes") private Object getColumnValue(ResultSet rs, ResultSetMetaData meta, int index, Class clazz) throws Exception { Object value = null;//from ww w .ja va 2 s. c o m int type = meta.getColumnType(index); if (clazz == String.class) { value = rs.getString(index); } else if (clazz == Integer.class) { value = rs.getInt(index); } else if (clazz == Boolean.class) { value = rs.getBoolean(index); } else if (clazz == byte[].class) { if (type == Types.BLOB) value = rs.getBlob(index); else value = rs.getBytes(index); } else if (clazz == Long.class) { value = rs.getLong(index); } else if (clazz == BigInteger.class) { value = rs.getBigDecimal(index); } else if (clazz == Float.class) { value = rs.getFloat(index); } else if (clazz == Double.class) { value = rs.getDouble(index); } else if (clazz == java.util.Date.class) { Timestamp time = rs.getTimestamp(index); if (time == null) value = null; else { value = new java.util.Date(time.getTime()); } } else if (clazz == java.sql.Date.class) { value = rs.getDate(index); } else if (clazz == java.sql.Time.class) { value = rs.getTime(index); } else if (clazz == java.sql.Timestamp.class) { value = rs.getTimestamp(index); } else { throw new Exception("Cannote determin this column type:" + meta.getColumnName(index)); } return value; }
From source file:at.ac.tuwien.inso.subcat.reporter.Reporter.java
private void exportWordStats(final ExporterConfig config, Project project, int commitDictId, int bugDictId, Settings settings, final ReportWriter formatter, String outputPath, Map<String, Object> vars) throws SQLException, Exception { formatter.init(project, settings, outputPath); model.rawForeach(config.getQuery(), vars, new ResultCallback() { @Override// w w w. j a v a 2s .c om public void processResult(ResultSet res) throws SemanticException, SQLException, Exception { ResultSetMetaData meta = res.getMetaData(); String[] titles = new String[meta.getColumnCount()]; if (titles.length != 2) { throw new SemanticException( "semantic error: invalid column count, expected: (<string>, <string>)", config.getStart(), config.getEnd()); } if (meta.getColumnType(1) != Types.VARCHAR || meta.getColumnType(2) != Types.VARCHAR) { throw new SemanticException( "semantic error: invalid column type, expected: (<string>, <string>), got " + "(<" + meta.getColumnTypeName(1) + ">, <" + meta.getColumnTypeName(2) + ">)", config.getStart(), config.getEnd()); } Map<String, Map<String, Integer>> data = new HashMap<String, Map<String, Integer>>(); Lemmatizer lemmatiser = new Lemmatizer(); Set<String> categoryNames = new HashSet<String>(); while (res.next()) { String category = res.getString(1); categoryNames.add(category); List<String> lemma = lemmatiser.lemmatize(res.getString(2)); for (String word : lemma) { Map<String, Integer> counter = data.get(word); if (counter == null) { counter = new HashMap<String, Integer>(); data.put(word, counter); } Integer wordCount = counter.get(category); if (wordCount == null) { wordCount = 0; } counter.put(category, wordCount + 1); } } String[] header = new String[categoryNames.size() + 1]; header[0] = "word"; int i = 1; for (String catName : categoryNames) { header[i] = catName; i++; } formatter.writeHeader(header); for (Entry<String, Map<String, Integer>> entry : data.entrySet()) { Map<String, Integer> scores = entry.getValue(); String[] row = new String[header.length]; row[0] = entry.getKey(); i = 1; for (String cat : categoryNames) { Integer score = scores.get(cat); if (score == null) { score = 0; } row[i] = score.toString(); i++; } formatter.writeSet(row); } formatter.writeFooter(header); } }); }
From source file:org.apache.hadoop.hive.jdbc.storagehandler.AtsdDBRecordReader.java
private ResultSet replaceDotsInColumnNames(ResultSet resultSet) throws SQLException { ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); if (columnCount > 0) { CachedRowSetImpl crs = new CachedRowSetImpl(); crs.populate(resultSet);// ww w . j a va 2 s . c om RowSetMetaDataImpl rwsm = new RowSetMetaDataImpl(); rwsm.setColumnCount(columnCount); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); if (columnName.contains(".")) { columnName = columnName.replaceAll("\\.", "\\$"); } rwsm.setColumnName(i, columnName); rwsm.setColumnLabel(i, metaData.getColumnLabel(i)); rwsm.setCatalogName(i, metaData.getCatalogName(i)); rwsm.setColumnType(i, metaData.getColumnType(i)); rwsm.setColumnTypeName(i, metaData.getColumnTypeName(i)); rwsm.setSchemaName(i, metaData.getSchemaName(i)); rwsm.setTableName(i, metaData.getTableName(i)); } crs.setMetaData(rwsm); return crs; } return resultSet; }
From source file:com.xpfriend.fixture.cast.temp.Database.java
private DynaClass getDynaClass(ResultSet resultSet) throws SQLException { ResultSetMetaData md = resultSet.getMetaData(); int count = md.getColumnCount(); DynaProperty[] properties = new DynaProperty[count]; for (int i = 0; i < properties.length; i++) { int column = i + 1; Class<?> type = TypeConverter.getJavaType(md.getColumnType(column), md.getColumnTypeName(column), md.getPrecision(column), md.getScale(column)); String name = getColumnLabel(md, column); properties[i] = new DynaProperty(name, type); }/*w ww . j a v a 2 s . c o m*/ return new BasicDynaClass(null, null, properties); }
From source file:org.openadaptor.auxil.connector.jdbc.writer.AbstractSQLWriter.java
/** * Determine the types of the columns in a table. * <br>//from w w w .ja v a2s. c om * It does not check that the table exists, or that the columns actually * exist in the table. * @param tableName * @param connection * @param columnNames * @return int[] of database types. * @throws SQLException */ protected int[] getPreparedStatementTypes(String tableName, Connection connection, String[] columnNames) throws SQLException { //Execute a dummy sql statement against database purely to collect table metadata String sql = "SELECT * FROM " + tableName + " WHERE 1=2"; Statement s = connection.createStatement(); log.debug("Executing SQL: " + sql); ResultSet rs = s.executeQuery(sql); int[] types; ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); types = new int[columnNames.length]; List nameList = Arrays.asList(columnNames); int mapped = 0; for (int i = 0; i < cols; i++) { int type = rsmd.getColumnType(i + 1); String name = rsmd.getColumnName(i + 1); int location = nameList.indexOf(name); if (location >= 0) { types[location] = type; mapped++; } else { if (log.isDebugEnabled()) { log.debug("Ignoring column " + i + "[" + name + " (" + rsmd.getColumnTypeName(i + 1) + ")]"); } } } if (mapped < types.length) { log.warn("Not all column names were mapped. This is probably a configuration error"); } return types; }
From source file:org.apache.sqoop.tool.ImportTool.java
/** * Return the max value in the incremental-import test column. This * value must be numeric./*from w w w. ja v a2 s . c om*/ */ private Object getMaxColumnId(SqoopOptions options) throws SQLException { StringBuilder sb = new StringBuilder(); String query; sb.append("SELECT MAX("); sb.append(options.getIncrementalTestColumn()); sb.append(") FROM "); if (options.getTableName() != null) { // Table import sb.append(options.getTableName()); String where = options.getWhereClause(); if (null != where) { sb.append(" WHERE "); sb.append(where); } query = sb.toString(); } else { // Free form table based import sb.append("("); sb.append(options.getSqlQuery()); sb.append(") sqoop_import_query_alias"); query = sb.toString().replaceAll("\\$CONDITIONS", "(1 = 1)"); } Connection conn = manager.getConnection(); Statement s = null; ResultSet rs = null; try { LOG.info("Maximal id query for free form incremental import: " + query); s = conn.createStatement(); rs = s.executeQuery(query); if (!rs.next()) { // This probably means the table is empty. LOG.warn("Unexpected: empty results for max value query?"); return null; } ResultSetMetaData rsmd = rs.getMetaData(); checkColumnType = rsmd.getColumnType(1); if (checkColumnType == Types.TIMESTAMP) { return rs.getTimestamp(1); } else if (checkColumnType == Types.DATE) { return rs.getDate(1); } else if (checkColumnType == Types.TIME) { return rs.getTime(1); } else { return rs.getObject(1); } } finally { try { if (null != rs) { rs.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing resultset: " + sqlE); } try { if (null != s) { s.close(); } } catch (SQLException sqlE) { LOG.warn("SQL Exception closing statement: " + sqlE); } } }
From source file:org.apache.nifi.processors.standard.util.JdbcCommon.java
/** * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value. * * @param rs The result set to convert to Avro * @param recordName The a priori record name to use if it cannot be determined from the result set. * @return A Schema object representing the result set converted to an Avro record * @throws SQLException if any error occurs during conversion *//* w ww. jav a2 s .c o m*/ public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames) throws SQLException { final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName; if (nrOfColumns > 0) { String tableNameFromMeta = meta.getTableName(1); if (!StringUtils.isBlank(tableNameFromMeta)) { tableName = tableNameFromMeta; } } if (convertNames) { tableName = normalizeNameForAvro(tableName); } final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields(); /** * Some missing Avro types - Decimal, Date types. May need some additional work. */ for (int i = 1; i <= nrOfColumns; i++) { /** * as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name. * so it may be a better option to check for columnlabel first and if in case it is null is someimplementation, * check for alias. Postgres is the one that has the null column names for calculated fields. */ String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i) : meta.getColumnName(i); String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel; switch (meta.getColumnType(i)) { case CHAR: case LONGNVARCHAR: case LONGVARCHAR: case NCHAR: case NVARCHAR: case VARCHAR: case CLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BIT: case BOOLEAN: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion() .noDefault(); break; case INTEGER: if (meta.isSigned(i) || (meta.getPrecision(i) > 0 && meta.getPrecision(i) <= MAX_DIGITS_IN_INT)) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; case SMALLINT: case TINYINT: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); break; case BIGINT: // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted // to strings as necessary int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; // java.sql.RowId is interface, is seems to be database // implementation specific, let's convert to String case ROWID: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case FLOAT: case REAL: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion() .noDefault(); break; case DOUBLE: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DECIMAL: case NUMERIC: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DATE: case TIME: case TIMESTAMP: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BINARY: case VARBINARY: case LONGVARBINARY: case ARRAY: case BLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion() .noDefault(); break; default: throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i) + " / " + meta.getColumnTypeName(i) + " (table: " + tableName + ", column: " + columnName + ") cannot be converted to Avro type"); } } return builder.endRecord(); }