List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:org.plasma.sdo.jdbc.service.GraphQuery.java
private List<List<PropertyPair>> findResults(Query query, PropertySelectionCollector collector, PlasmaType type, Connection con) {/* w w w . j a v a2s .c om*/ Object[] params = new Object[0]; RDBDataConverter converter = RDBDataConverter.INSTANCE; if (log.isDebugEnabled()) { log(query); } AliasMap aliasMap = new AliasMap(type); Map<Type, List<String>> selectMap = collector.getResult(); // construct a filter adding to alias map FilterAssembler filterAssembler = null; Where where = query.findWhereClause(); if (where != null) { filterAssembler = new FilterAssembler(where, type, aliasMap); params = filterAssembler.getParams(); } OrderingDeclarationAssembler orderingDeclAssembler = null; OrderBy orderby = query.findOrderByClause(); if (orderby != null) orderingDeclAssembler = new OrderingDeclarationAssembler(orderby, type, aliasMap); GroupingDeclarationAssembler groupingDeclAssembler = null; GroupBy groupby = query.findGroupByClause(); if (groupby != null) groupingDeclAssembler = new GroupingDeclarationAssembler(groupby, type, aliasMap); String rootAlias = aliasMap.getAlias(type); StringBuilder sqlQuery = new StringBuilder(); sqlQuery.append("SELECT "); int i = 0; List<String> names = selectMap.get(type); for (String name : names) { PlasmaProperty prop = (PlasmaProperty) type.getProperty(name); if (prop.isMany() && !prop.getType().isDataType()) continue; if (i > 0) sqlQuery.append(", "); sqlQuery.append(rootAlias); sqlQuery.append("."); sqlQuery.append(prop.getPhysicalName()); i++; } // construct a FROM clause from alias map sqlQuery.append(" FROM "); Iterator<PlasmaType> it = aliasMap.getTypes(); int count = 0; while (it.hasNext()) { PlasmaType aliasType = it.next(); String alias = aliasMap.getAlias(aliasType); if (count > 0) sqlQuery.append(", "); sqlQuery.append(getQualifiedPhysicalName(aliasType)); sqlQuery.append(" "); sqlQuery.append(alias); count++; } // append WHERE filter if (filterAssembler != null) { sqlQuery.append(" "); sqlQuery.append(filterAssembler.getFilter()); } // set the result range // FIXME: Oracle specific if (query.getStartRange() != null && query.getEndRange() != null) { if (where == null) sqlQuery.append(" WHERE "); else sqlQuery.append(" AND "); sqlQuery.append("ROWNUM >= "); sqlQuery.append(String.valueOf(query.getStartRange())); sqlQuery.append(" AND ROWNUM <= "); sqlQuery.append(String.valueOf(query.getEndRange())); } if (orderingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration()); } if (groupingDeclAssembler != null) { sqlQuery.append(" "); sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration()); } List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null; try { statement = con.prepareStatement(sqlQuery.toString(), ResultSet.TYPE_FORWARD_ONLY, /*ResultSet.TYPE_SCROLL_INSENSITIVE,*/ ResultSet.CONCUR_READ_ONLY); // set params // note params are pre-converted // to string in filter assembly // FIXME: are parameters relevant in SQL in this context? if (filterAssembler != null) { params = filterAssembler.getParams(); if (params != null) for (i = 0; i < params.length; i++) statement.setString(i + 1, String.valueOf(params[i])); } if (log.isDebugEnabled()) { if (params == null || params.length == 0) { log.debug("executing: " + sqlQuery.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("executing: " + sqlQuery.toString() + " " + paramBuf.toString()); } } statement.execute(); rs = statement.getResultSet(); int numcols = rs.getMetaData().getColumnCount(); ResultSetMetaData rsMeta = rs.getMetaData(); List<PropertyPair> row = null; PropertyPair pair = null; while (rs.next()) { row = new ArrayList<PropertyPair>(); rows.add(row); for (i = 1; i <= numcols; i++) { String columnName = rsMeta.getColumnName(i); int columnType = rsMeta.getColumnType(i); PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName); Object value = converter.fromJDBCDataType(rs, i, columnType, prop); pair = new PropertyPair(prop, value); pair.setColumn(i); row.add(pair); } } } catch (Throwable t) { StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler); log.error(buf.toString()); 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 rows; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;// ww w.java 2 s. c o m try { // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:org.apache.sqoop.connector.hbase.HbaseFromInitializer.java
@Override public Schema getSchema(InitializerContext context, LinkConfiguration linkConfig, FromJobConfiguration fromJobConfig) { executor = new HbaseExecutor(linkConfig.linkConfig); String schemaName = fromJobConfig.fromJobConfig.tableName; if (schemaName == null) { schemaName = "Query"; } else if (fromJobConfig.fromJobConfig.schemaName != null) { schemaName = fromJobConfig.fromJobConfig.schemaName + "." + schemaName; }//from w w w. j a v a 2 s . co m Schema schema = new Schema(schemaName); ResultSet rs = null; ResultSetMetaData rsmt = null; try { rs = executor.executeQuery(context.getString(HbaseConnectorConstants.CONNECTOR_JDBC_FROM_DATA_SQL) .replace(HbaseConnectorConstants.SQL_CONDITIONS_TOKEN, "1 = 0")); rsmt = rs.getMetaData(); for (int i = 1; i <= rsmt.getColumnCount(); i++) { String columnName = rsmt.getColumnLabel(i); if (StringUtils.isEmpty(columnName)) { columnName = rsmt.getColumnName(i); if (StringUtils.isEmpty(columnName)) { columnName = "Column " + i; } } Column column = SqlTypesUtils.sqlTypeToSchemaType(rsmt.getColumnType(i), columnName, rsmt.getPrecision(i), rsmt.getScale(i)); schema.addColumn(column); } return schema; } catch (SQLException e) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0016, e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.info("Ignoring exception while closing ResultSet", e); } } if (executor != null) { executor.close(); } } }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
public List<String> toStringList(ResultSet rs) throws Exception { List<String> list = new ArrayList<String>(); try {/*from w w w. java 2 s .co m*/ // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object StringBuilder builder = new StringBuilder(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { builder.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { builder.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { builder.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { builder.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { builder.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // builder.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { builder.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { builder.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { builder.append(rs.getTime(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { builder.append(rs.getTimestamp(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { builder.append(rs.getBigDecimal(column_name)); } else { builder.append(rs.getObject(column_name)); } } //end foreach list.add(builder.toString()); } //end while } catch (Exception e) { e.printStackTrace(); } return list; //return String list }
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 ww . jav a2 s. com*/ 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: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:com.googlecode.jdbcproc.daofactory.impl.block.service.ParametersSetterBlockServiceImpl.java
private Map<String, Integer> createTypes(JdbcTemplate jdbcTemplate, final String tableName) { return jdbcTemplate.execute(new StatementCallback<Map<String, Integer>>() { public Map<String, Integer> doInStatement(Statement stmt) throws SQLException, DataAccessException { ResultSet rs = stmt.executeQuery("select * from " + tableName); try { ResultSetMetaData meta = rs.getMetaData(); Map<String, Integer> types = new HashMap<String, Integer>(); int count = meta.getColumnCount(); for (int i = 1; i <= count; i++) { String name = meta.getColumnName(i); int type = meta.getColumnType(i); types.put(name, type); }/*from ww w .ja v a 2 s . c om*/ return types; } finally { rs.close(); } } }); }
From source file:rapture.repo.jdbc.JDBCStructuredStore.java
protected Boolean refreshColumnTypeCache(final String tableName) { return jdbc.query(sqlGenerator.constructSelect(schema, tableName, null, "1=0", null, null, -1), new ResultSetExtractor<Boolean>() { @Override// w w w . ja v a2 s. c om public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException { ResultSetMetaData rsmd = rs.getMetaData(); Map<String, Integer> columnType = new TreeMap<>(String.CASE_INSENSITIVE_ORDER); for (int i = 1; i <= rsmd.getColumnCount(); i++) { columnType.put(rsmd.getColumnLabel(i), rsmd.getColumnType(i)); } cache.putColumnTypes(tableName, columnType); return true; } }); }
From source file:org.plasma.sdo.jdbc.service.JDBCSupport.java
protected List<List<PropertyPair>> fetch(PlasmaType type, StringBuilder sql, Object[] params, Connection con) { List<List<PropertyPair>> result = new ArrayList<List<PropertyPair>>(); PreparedStatement statement = null; ResultSet rs = null;//from w w w. ja v a2 s. c o 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, String.valueOf(params[i])); statement.execute(); rs = statement.getResultSet(); ResultSetMetaData rsMeta = rs.getMetaData(); int numcols = rsMeta.getColumnCount(); 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); Object value = converter.fromJDBCDataType(rs, i, columnType, prop); if (value != null) { PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value); row.add(pair); } } } } 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:mondrian.spi.impl.JdbcDialectImpl.java
void logTypeInfo(ResultSetMetaData metaData, int columnIndex, SqlStatement.Type internalType) throws SQLException { if (LOGGER.isDebugEnabled()) { final int columnType = metaData.getColumnType(columnIndex + 1); final int precision = metaData.getPrecision(columnIndex + 1); final int scale = metaData.getScale(columnIndex + 1); final String columnName = metaData.getColumnName(columnIndex + 1); LOGGER.debug("JdbcDialectImpl.getType " + "Dialect- " + this.getDatabaseProduct() + ", Column-" + columnName + " is of internal type " + internalType + ". JDBC type was " + columnType + ". Column precision=" + precision + ". Column scale=" + scale); }// w w w. java 2 s . c o m }