List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:db.migration.V055__UpdateECTS.java
private int getNextHibernateSequence(JdbcTemplate jdbcTemplate) { // Returns next global id List<Map> resultSet = jdbcTemplate.query("SELECT nextval('public.hibernate_sequence')", new RowMapper<Map>() { @Override/* w w w. ja va 2 s .c om*/ public Map mapRow(ResultSet rs, int rowNum) throws SQLException { Map r = new HashMap<String, Object>(); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 1; i <= metadata.getColumnCount(); i++) { String cname = metadata.getColumnName(i); int ctype = metadata.getColumnType(i); switch (ctype) { case Types.BIGINT: // id r.put(cname, rs.getInt(cname)); break; default: break; } } return r; } }); for (Map m : resultSet) { return (int) m.get("nextval"); } return 0; }
From source file:uk.org.rbc1b.roms.controller.report.ReportsController.java
private ReportResults extractResults(String sql) throws SQLException { Connection con = DataSourceUtils.getConnection(dataSource); Statement s = con.createStatement(); ResultSet rs = s.executeQuery(sql); ReportResults reportResults = new ReportResults(); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); List<Integer> columnTypeIds = new ArrayList<Integer>(); for (int i = 0; i < columnCount; i++) { columnTypeIds.add(rsmd.getColumnType(i + 1)); }// ww w.j a va 2s . c o m reportResults.columnNames = new ArrayList<String>(); for (int i = 0; i < columnCount; i++) { reportResults.columnNames.add(rsmd.getColumnLabel(i + 1)); } reportResults.resultRows = new ArrayList<List<String>>(); while (rs.next()) { List<String> resultRow = new ArrayList<String>(); for (int i = 0; i < columnCount; i++) { Integer columnTypeId = columnTypeIds.get(i); if (columnTypeId.intValue() == Types.BOOLEAN || columnTypeId.intValue() == Types.BIT) { resultRow.add(Boolean.valueOf(rs.getBoolean(i + 1)).toString()); } else { resultRow.add(rs.getString(i + 1)); } } reportResults.resultRows.add(resultRow); } return reportResults; }
From source file:com.kumarvv.setl.utils.RowSetUtil.java
/** * get meta columns list with columnId/*from w ww . j av a2 s.c om*/ * * @param meta * @return */ public Map<String, Integer> getMetaColumns(ResultSetMetaData meta) { final Map<String, Integer> metaColumns = new HashMap<>(); if (meta == null) { return metaColumns; } try { int colCount = meta.getColumnCount(); for (int c = 1; c <= colCount; c++) { metaColumns.put(meta.getColumnName(c), meta.getColumnType(c)); } } catch (SQLException sqle) { Logger.error("error getting metaColumns:", sqle.getMessage()); Logger.trace(sqle); } return metaColumns; }
From source file:db.migration.V055__UpdateECTS.java
public void migrate(JdbcTemplate jdbcTemplate) throws Exception { LOG.info("migrate()..."); // Get all organisaatiometadatas where there are strings to process List<Map> resultSet = jdbcTemplate.query( "SELECT id,hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike FROM organisaatiometadata WHERE hakutoimistoectsemail<>'' OR hakutoimistoectsnimi<>'' OR hakutoimistoectspuhelin<>'' OR hakutoimistoectstehtavanimike<>''", new RowMapper<Map>() { @Override/*from w ww. j a v a2s . c o m*/ public Map mapRow(ResultSet rs, int rowNum) throws SQLException { Map r = new HashMap<String, Object>(); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 1; i <= metadata.getColumnCount(); i++) { String cname = metadata.getColumnName(i); int ctype = metadata.getColumnType(i); switch (ctype) { case Types.VARCHAR: // hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike r.put(cname, rs.getString(cname)); break; case Types.BIGINT: // id r.put(cname, rs.getInt(cname)); break; default: break; } } LOG.debug(" read from db : organisaatiometadata = {}", r); return r; } }); // Move strings to monikielinenteksti_values for (Map orgmd : resultSet) { handleOrganisaatiometadata(orgmd, jdbcTemplate); } LOG.info("migrate()... done."); }
From source file:org.protempa.backend.dsb.relationaldb.PrimitiveParameterResultProcessor.java
@Override public void process(ResultSet resultSet) throws SQLException { ResultCache<PrimitiveParameter> results = getResults(); EntitySpec entitySpec = getEntitySpec(); String entitySpecName = entitySpec.getName(); //boolean hasRefs = entitySpec.getInboundRefSpecs().length > 0; String[] propIds = entitySpec.getPropositionIds(); ColumnSpec codeSpec = entitySpec.getCodeSpec(); if (codeSpec != null) { List<ColumnSpec> codeSpecL = codeSpec.asList(); codeSpec = codeSpecL.get(codeSpecL.size() - 1); }/*from ww w .j ava2 s . co m*/ Logger logger = SQLGenUtil.logger(); PropertySpec[] propertySpecs = entitySpec.getPropertySpecs(); Value[] propertyValues = new Value[propertySpecs.length]; int count = 0; String[] uniqueIds = new String[entitySpec.getUniqueIdSpecs().length]; ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int[] columnTypes = new int[resultSetMetaData.getColumnCount()]; for (int i = 0; i < columnTypes.length; i++) { columnTypes[i] = resultSetMetaData.getColumnType(i + 1); } SourceSystem dsType = DataSourceBackendSourceSystem.getInstance(getDataSourceBackendId()); while (resultSet.next()) { int i = 1; String keyId = resultSet.getString(i++); if (keyId == null) { logger.warning("A keyId is null. Skipping record."); continue; } i = readUniqueIds(uniqueIds, resultSet, i); if (Arrays.contains(uniqueIds, null)) { if (logger.isLoggable(Level.WARNING)) { logger.log(Level.WARNING, "Unique ids contain null ({0}). Skipping record.", StringUtils.join(uniqueIds, ", ")); continue; } } UniqueId uniqueId = generateUniqueId(entitySpecName, uniqueIds); String propId = null; if (!isCasePresent()) { if (codeSpec == null) { assert propIds.length == 1 : "Don't know which proposition id to assign to"; propId = propIds[0]; } else { String code = resultSet.getString(i++); propId = sqlCodeToPropositionId(codeSpec, code); if (propId == null) { continue; } } } else { i++; } Long timestamp = null; try { timestamp = entitySpec.getPositionParser().toPosition(resultSet, i, columnTypes[i - 1]); i++; } catch (SQLException e) { logger.log(Level.WARNING, "Could not parse timestamp. Leaving timestamp unset.", e); } ValueType valueType = entitySpec.getValueType(); String cpValStr = resultSet.getString(i++); Value cpVal = valueType.parse(cpValStr); i = extractPropertyValues(resultSet, i, propertyValues, columnTypes); if (isCasePresent()) { propId = resultSet.getString(i++); } PrimitiveParameter p = new PrimitiveParameter(propId, uniqueId); p.setPosition(timestamp); p.setGranularity(entitySpec.getGranularity()); p.setValue(cpVal); for (int j = 0; j < propertySpecs.length; j++) { PropertySpec propertySpec = propertySpecs[j]; p.setProperty(propertySpec.getName(), propertyValues[j]); } p.setSourceSystem(dsType); logger.log(Level.FINEST, "Created primitive parameter {0}", p); results.add(keyId, p); if (++count % FLUSH_SIZE == 0) { try { results.flush(this); } catch (IOException ex) { throw new QueryResultsCacheException("Flushing primitive parameters to cache failed", ex); } if (logger.isLoggable(Level.FINE)) { Logging.logCount(logger, Level.FINE, count, "Retrieved {0} record", "Retrieved {0} records"); } } } try { results.flush(this); } catch (IOException ex) { throw new QueryResultsCacheException("Flushing primitive parameters to cache failed", ex); } if (logger.isLoggable(Level.FINE)) { Logging.logCount(logger, Level.FINE, count, "Retrieved {0} record total", "Retrieved {0} records total"); } }
From source file:com.flexive.core.search.genericSQL.GenericSQLForeignTableSelector.java
protected GenericSQLForeignTableSelector(String mainColumn, String tableName, String linksOn, boolean hasTranslationTable, String translatedColumn) { FxSharedUtils.checkParameterNull(tableName, "tableName"); FxSharedUtils.checkParameterNull(linksOn, "linksOn"); Connection con = null;/* ww w. ja v a 2s .c om*/ Statement stmt = null; this.tableName = tableName; this.linksOn = linksOn; this.mainColumn = mainColumn; this.hasTranslationTable = hasTranslationTable; this.translatedColumn = translatedColumn != null ? translatedColumn.toUpperCase() : null; try { con = Database.getDbConnection(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName + StorageManager.getLimit(false, 0)); ResultSetMetaData md = rs.getMetaData(); for (int pos = 1; pos <= md.getColumnCount(); pos++) { String columnName = md.getColumnName(pos); FxDataType columnType; switch (md.getColumnType(pos)) { case java.sql.Types.CHAR: if (md.getPrecision(pos) == 1) { columnType = FxDataType.Boolean; //oracle break; } case java.sql.Types.VARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.CLOB: columnType = FxDataType.String1024; break; case java.sql.Types.BOOLEAN: case java.sql.Types.BIT: columnType = FxDataType.Boolean; break; case java.sql.Types.TINYINT: case java.sql.Types.SMALLINT: case java.sql.Types.INTEGER: columnType = FxDataType.Number; break; case java.sql.Types.DECIMAL: columnType = FxDataType.Double; break; case java.sql.Types.FLOAT: columnType = FxDataType.Float; break; case java.sql.Types.NUMERIC: case java.sql.Types.BIGINT: if ("CREATED_AT".equalsIgnoreCase(columnName) || "MODIFIED_AT".equalsIgnoreCase(columnName)) columnType = FxDataType.DateTime; else if ("CAT_TYPE".equals(columnName) && "FXS_ACL".equals(tableName)) { columnType = FxDataType.Number; } else columnType = FxDataType.LargeNumber; break; case java.sql.Types.DATE: columnType = FxDataType.Date; break; case java.sql.Types.TIME: case java.sql.Types.TIMESTAMP: columnType = FxDataType.DateTime; break; default: if (LOG.isInfoEnabled()) { LOG.info("Assigning String to " + tableName + "." + columnName + " found type=" + md.getColumnType(pos)); } columnType = FxDataType.String1024; } columns.put(columnName.toUpperCase(), columnType); } } catch (Throwable t) { @SuppressWarnings({ "ThrowableInstanceNeverThrown" }) FxSqlSearchException ex = new FxSqlSearchException(LOG, "ex.sqlSearch.fieldSelector.initializeFailed", tableName, t.getMessage()); LOG.error(ex.getMessage(), ex); throw ex.asRuntimeException(); } finally { Database.closeObjects(GenericSQLForeignTableSelector.class, con, stmt); } }
From source file:org.apache.tika.parser.jdbc.JDBCTableReader.java
private void handleCell(ResultSetMetaData rsmd, int i, ContentHandler handler, ParseContext context) throws SQLException, IOException, SAXException { switch (rsmd.getColumnType(i)) { case Types.BLOB: handleBlob(tableName, rsmd.getColumnName(i), rows, results, i, handler, context); break;// w w w . ja va 2s . co m case Types.CLOB: handleClob(tableName, rsmd.getColumnName(i), rows, results, i, handler, context); break; case Types.BOOLEAN: handleBoolean(results, i, handler); break; case Types.DATE: handleDate(results, i, handler); break; case Types.TIMESTAMP: handleTimeStamp(results, i, handler); break; case Types.INTEGER: handleInteger(results, i, handler); break; case Types.FLOAT: //this is necessary to handle rounding issues in presentation //Should we just use getString(i)? float f = results.getFloat(i); if (!results.wasNull()) { addAllCharacters(Float.toString(f), handler); } break; case Types.DOUBLE: double d = results.getDouble(i); if (!results.wasNull()) { addAllCharacters(Double.toString(d), handler); } break; default: String s = results.getString(i); if (!results.wasNull()) { addAllCharacters(s, handler); } break; } }
From source file:nl.nn.adapterframework.util.JdbcUtil.java
public static String getValue(final ResultSet rs, final int colNum, final ResultSetMetaData rsmeta, String blobCharset, boolean decompressBlobs, String nullValue, boolean trimSpaces, boolean getBlobSmart, boolean encodeBlobBase64) throws JdbcException, IOException, SQLException, JMSException { switch (rsmeta.getColumnType(colNum)) { case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: try {//from w ww . jav a 2 s . c o m return JdbcUtil.getBlobAsString(rs, colNum, blobCharset, false, decompressBlobs, getBlobSmart, encodeBlobBase64); } catch (JdbcException e) { log.debug("Caught JdbcException, assuming no blob found", e); return nullValue; } case Types.CLOB: try { return JdbcUtil.getClobAsString(rs, colNum, false); } catch (JdbcException e) { log.debug("Caught JdbcException, assuming no clob found", e); return nullValue; } // return "undefined" for types that cannot be rendered to strings easily case Types.ARRAY: case Types.DISTINCT: case Types.BINARY: case Types.REF: case Types.STRUCT: return "undefined"; default: { String value = rs.getString(colNum); if (value == null) { return nullValue; } if (trimSpaces) { return value.trim(); } return value; } } }
From source file:it.greenvulcano.gvesb.utils.GVESBPropertyHandler.java
private String expandSQLProperties(String str, Map<String, Object> inProperties, Object object, Object extra) throws PropertiesHandlerException { PreparedStatement ps = null;// w w w . j a v a 2 s . c o m ResultSet rs = null; String sqlStatement = null; Connection conn = null; String connName = ""; boolean intConn = false; try { if (!PropertiesHandler.isExpanded(str)) { str = PropertiesHandler.expand(str, inProperties, object, extra); } int pIdx = str.indexOf("::"); if (pIdx != -1) { connName = str.substring(0, pIdx); sqlStatement = str.substring(pIdx + 2); intConn = true; } else { sqlStatement = str; } if (intConn) { conn = JDBCConnectionBuilder.getConnection(connName); } else if ((extra != null) && (extra instanceof Connection)) { conn = (Connection) extra; } else { throw new PropertiesHandlerException( "Error handling 'sql' metadata '" + str + "', Connection undefined."); } logger.debug("Executing SQL statement {" + sqlStatement + "} on connection [" + connName + "]"); ps = conn.prepareStatement(sqlStatement); rs = ps.executeQuery(); String paramValue = null; if (rs.next()) { ResultSetMetaData rsmeta = rs.getMetaData(); if (rsmeta.getColumnType(1) == Types.CLOB) { Clob clob = rs.getClob(1); if (clob != null) { Reader is = clob.getCharacterStream(); StringWriter strW = new StringWriter(); IOUtils.copy(is, strW); is.close(); paramValue = strW.toString(); } } else { paramValue = rs.getString(1); } } return (paramValue != null) ? paramValue.trim() : paramValue; } catch (Exception exc) { logger.warn("Error handling 'sql' metadata '" + sqlStatement + "'", exc); if (PropertiesHandler.isExceptionOnErrors()) { if (exc instanceof PropertiesHandlerException) { throw (PropertiesHandlerException) exc; } throw new PropertiesHandlerException("Error handling 'sql' metadata '" + str + "'", exc); } return "sql" + PROP_START + str + PROP_END; } finally { if (rs != null) { try { rs.close(); } catch (Exception exc) { // do nothing } } if (ps != null) { try { ps.close(); } catch (Exception exc) { // do nothing } } if (intConn && (conn != null)) { try { JDBCConnectionBuilder.releaseConnection(connName, conn); } catch (Exception exc) { // do nothing } } } }
From source file:org.apache.hadoop.sqoop.manager.SqlManager.java
@Override public Map<String, Integer> getColumnTypes(String tableName) { String stmt = "SELECT t.* FROM " + tableName + " AS t WHERE 1 = 1"; ResultSet results = execute(stmt); if (null == results) { return null; }/*from w w w . j a v a 2s . co m*/ try { Map<String, Integer> colTypes = new HashMap<String, Integer>(); int cols = results.getMetaData().getColumnCount(); ResultSetMetaData metadata = results.getMetaData(); for (int i = 1; i < cols + 1; i++) { int typeId = metadata.getColumnType(i); String colName = metadata.getColumnName(i); if (colName == null || colName.equals("")) { colName = metadata.getColumnLabel(i); } colTypes.put(colName, Integer.valueOf(typeId)); } return colTypes; } catch (SQLException sqlException) { LOG.error("Error reading from database: " + sqlException.toString()); return null; } }