List of usage examples for java.sql ResultSet getArray
Array getArray(String columnLabel) throws SQLException;
ResultSet object as an Array object in the Java programming language. From source file:org.apache.cocoon.util.JDBCTypeConversions.java
/** * Get the Statement column so that the results are mapped correctly. * (this has been copied from AbstractDatabaseAction and modified slightly) *///from w ww. j a v a2 s. c o m public static Object getColumn(ResultSet set, Configuration column) throws Exception { Integer type = (Integer) JDBCTypeConversions.typeConstants.get(column.getAttribute("type")); String dbcol = column.getAttribute("name"); Object value = null; switch (type.intValue()) { case Types.CLOB: case Types.CHAR: Clob dbClob = set.getClob(dbcol); int length = (int) dbClob.length(); InputStream asciiStream = new BufferedInputStream(dbClob.getAsciiStream()); byte[] buffer = new byte[length]; asciiStream.read(buffer); String str = new String(buffer); asciiStream.close(); value = str; break; case Types.BIGINT: value = set.getBigDecimal(dbcol); break; case Types.TINYINT: value = new Byte(set.getByte(dbcol)); break; case Types.VARCHAR: value = set.getString(dbcol); break; case Types.DATE: value = set.getDate(dbcol); break; case Types.DOUBLE: value = new Double(set.getDouble(dbcol)); break; case Types.FLOAT: value = new Float(set.getFloat(dbcol)); break; case Types.INTEGER: value = new Integer(set.getInt(dbcol)); break; case Types.NUMERIC: value = new Long(set.getLong(dbcol)); break; case Types.SMALLINT: value = new Short(set.getShort(dbcol)); break; case Types.TIME: value = set.getTime(dbcol); break; case Types.TIMESTAMP: value = set.getTimestamp(dbcol); break; case Types.ARRAY: value = set.getArray(dbcol); // new Integer(set.getInt(dbcol)); break; case Types.BIT: value = BooleanUtils.toBooleanObject(set.getBoolean(dbcol)); break; case Types.STRUCT: value = (Struct) set.getObject(dbcol); break; case Types.OTHER: value = set.getObject(dbcol); break; default: // The blob types have to be requested separately, via a Reader. value = ""; break; } return value; }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;//from ww w . j a v a 2s. co 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:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
public List<String> toStringList(ResultSet rs) throws Exception { List<String> list = new ArrayList<String>(); try {/* ww w. j av a 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:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.java
@Override public Set<Serializable> getAncestorsIds(Collection<Serializable> ids) throws StorageException { SQLInfoSelect select = sqlInfo.getSelectAncestorsIds(); if (select == null) { return getAncestorsIdsIterative(ids); }/*from www . j av a2 s . c om*/ Serializable whereIds = newIdArray(ids); Set<Serializable> res = new HashSet<Serializable>(); PreparedStatement ps = null; try { if (logger.isLogEnabled()) { logger.logSQL(select.sql, Collections.singleton(whereIds)); } Column what = select.whatColumns.get(0); ps = connection.prepareStatement(select.sql); setToPreparedStatementIdArray(ps, 1, whereIds); ResultSet rs = ps.executeQuery(); countExecute(); List<Serializable> debugIds = null; if (logger.isLogEnabled()) { debugIds = new LinkedList<Serializable>(); } while (rs.next()) { if (dialect.supportsArraysReturnInsteadOfRows()) { Serializable[] resultIds = dialect.getArrayResult(rs.getArray(1)); for (Serializable id : resultIds) { if (id != null) { res.add(id); if (logger.isLogEnabled()) { debugIds.add(id); } } } } else { Serializable id = what.getFromResultSet(rs, 1); if (id != null) { res.add(id); if (logger.isLogEnabled()) { debugIds.add(id); } } } } if (logger.isLogEnabled()) { logger.logIds(debugIds, false, 0); } return res; } catch (Exception e) { checkConnectionReset(e); throw new StorageException("Failed to get ancestors ids", e); } finally { if (ps != null) { try { closeStatement(ps); } catch (SQLException e) { log.error(e.getMessage(), e); } } } }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectPostgreSQL.java
@Override @SuppressWarnings("boxing") public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { int jdbcType = rs.getMetaData().getColumnType(index); if (column.getJdbcType() == Types.ARRAY && jdbcType != Types.ARRAY) { jdbcType = column.getJdbcBaseType(); } else {//w w w . ja v a2 s . c om jdbcType = column.getJdbcType(); } switch (jdbcType) { case Types.VARCHAR: case Types.CLOB: return getFromResultSetString(rs, index, column); case Types.BIT: return rs.getBoolean(index); case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: return rs.getLong(index); case Types.DOUBLE: return rs.getDouble(index); case Types.TIMESTAMP: return getCalendarFromTimestamp(rs.getTimestamp(index)); case Types.ARRAY: Array array = rs.getArray(index); if (array == null) { return null; } if (array.getBaseType() == Types.TIMESTAMP) { return getCalendarFromTimestamp((Timestamp[]) array.getArray()); } else { return (Serializable) array.getArray(); } case Types.OTHER: ColumnType type = column.getType(); if (type.isId()) { return getId(rs, index); } throw new SQLException("Unhandled type: " + column.getType()); } throw new SQLException( "Unhandled JDBC type: " + column.getJdbcType() + " for type " + column.getType().toString()); }
From source file:org.mayocat.shop.catalog.store.jdbi.mapper.ProductMapper.java
@Override public Product map(int index, ResultSet resultSet, StatementContext statementContext) throws SQLException { try {// w ww . ja v a 2 s . c o m Product product = new Product((UUID) resultSet.getObject("id")); product.setTenantId((UUID) resultSet.getObject("tenant_id")); if (resultSet.getObject("parent_id") != null) { product.setParentId((UUID) resultSet.getObject("parent_id")); } product.setSlug(resultSet.getString("slug")); product.setTitle(resultSet.getString("title")); product.setDescription(resultSet.getString("description")); product.setCreationDate(resultSet.getTimestamp("creation_date")); if (resultSet.getObject("on_shelf") != null) { product.setOnShelf(resultSet.getBoolean("on_shelf")); } product.setPrice(resultSet.getBigDecimal("price")); if (!Strings.isNullOrEmpty(resultSet.getString("taxes"))) { ObjectMapper mapper = new ObjectMapper(); Map<String, String> taxes = mapper.readValue(resultSet.getString("taxes"), new TypeReference<Map<String, String>>() { }); if (taxes.containsKey("vat")) { product.setVatRateId(taxes.get("vat")); } } product.setWeight(resultSet.getBigDecimal("weight")); if (resultSet.getObject("stock") != null) { product.setStock(resultSet.getInt("stock")); } product.setVirtual(resultSet.getBoolean("virtual")); UUID featuredImageId = (UUID) resultSet.getObject("featured_image_id"); if (featuredImageId != null) { product.setFeaturedImageId(featuredImageId); } if (MapperUtils.hasColumn("localization_data", resultSet) && !Strings.isNullOrEmpty(resultSet.getString("localization_data"))) { ObjectMapper mapper = new ObjectMapper(); Map<Locale, Map<String, Object>> localizedVersions = Maps.newHashMap(); Map[] data = mapper.readValue(resultSet.getString("localization_data"), Map[].class); for (Map map : data) { localizedVersions.put(Locale.forLanguageTag((String) map.get("locale")), (Map) map.get("entity")); } product.setLocalizedVersions(localizedVersions); } String model = resultSet.getString("model"); if (!Strings.isNullOrEmpty(model)) { product.setModel(model); } String type = resultSet.getString("product_type"); if (!Strings.isNullOrEmpty(type)) { product.setType(type); } if (resultSet.getArray("features") != null) { // There's no support for getting the pg uuid array as a Java UUID array (or even String array) at the time // this is written, we have to iterate over the array own result set and construct the Java array ourselves List<UUID> ids = new ArrayList<>(); Array array = resultSet.getArray("features"); if (array != null) { ResultSet featuresResultSet = array.getResultSet(); while (featuresResultSet.next()) { ids.add((UUID) featuresResultSet.getObject("value")); } product.setFeatures(ids); } } return product; } catch (IOException e) { throw new SQLException("Failed to de-serialize JSON data", e); } }
From source file:org.intermine.bio.dataconversion.SgdConverter.java
private void processPhenotypes(Connection connection) throws SQLException, ObjectStoreException { ResultSet res = PROCESSOR.getPhenotypeResults(connection); System.out.println("Processing Phenotypes..."); while (res.next()) { String geneFeatureNo = res.getString("dbentity_id"); String phenotypeAnnotNo = res.getString("annotation_id"); String groupNo = res.getString("group_id"); String experimentType = res.getString("experiment"); String experimentComment = res.getString("experiment_comment"); String alleleComment = res.getString("allele_comment"); String reporterComment = res.getString("reporter_comment"); String mutantType = res.getString("mutant"); String qualifier_observable = res.getString("phenotype"); String strainBackground = res.getString("strain_name"); String reporter = res.getString("reporter"); String allele = res.getString("allele"); String assay = res.getString("assay"); String details = res.getString("details"); String pmid = res.getString("pmid"); String refNo = res.getString("refNo"); Array cond_class = res.getArray("condclass"); String[] str_cond_class = (String[]) cond_class.getArray(); Array cond_name = res.getArray("condname"); String[] str_cond_name = (String[]) cond_name.getArray(); Array cond_value = res.getArray("condvalue"); String[] str_cond_value = (String[]) cond_value.getArray(); Array cond_unit = res.getArray("condunit"); String[] str_cond_unit = (String[]) cond_unit.getArray(); String qualifier = ""; String observable = ""; if (qualifier_observable.contains(":")) { String t[] = qualifier_observable.split(":"); qualifier = t[1].trim();//from w w w.j a v a 2 s . c o m observable = t[0].trim(); } else { observable = qualifier_observable.trim(); } String strain = ""; if (StringUtils.isNotEmpty(strainBackground)) { String q[] = strainBackground.split("_"); int len = q.length; if (len == 3) { strain = q[len - 1]; } else { strain = "Other"; } } Item gene = genes.get(geneFeatureNo); String chemical = ""; String condition = ""; String chemcond = getPhenotypeCondition(str_cond_class, str_cond_name, str_cond_value, str_cond_unit); //System.out.println("chemcond is...." + chemcond); String cc[] = chemcond.split("_"); chemical = cc[0]; condition = cc[1]; getPhenotype(phenotypeAnnotNo, groupNo, qualifier, observable, experimentType, experimentComment, alleleComment, reporterComment, strain, mutantType, reporter, allele, assay, chemical, condition, details, pmid, refNo, gene); } }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Convert the specified column of the SQL ResultSet to the proper * java type.//from ww w . ja v a 2 s. co m */ public Array getArray(ResultSet rs, int column) throws SQLException { return rs.getArray(column); }
From source file:org.apache.ddlutils.platform.PlatformImplBase.java
/** * This is the core method to retrieve a value for a column from a result set. Its primary * purpose is to call the appropriate method on the result set, and to provide an extension * point where database-specific implementations can change this behavior. * /*from w w w . jav a 2s.c o m*/ * @param resultSet The result set to extract the value from * @param columnName The name of the column; can be <code>null</code> in which case the * <code>columnIdx</code> will be used instead * @param columnIdx The index of the column's value in the result set; is only used if * <code>columnName</code> is <code>null</code> * @param jdbcType The jdbc type to extract * @return The value * @throws SQLException If an error occurred while accessing the result set */ protected Object extractColumnValue(ResultSet resultSet, String columnName, int columnIdx, int jdbcType) throws SQLException { boolean useIdx = (columnName == null); Object value; switch (jdbcType) { case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: value = useIdx ? resultSet.getString(columnIdx) : resultSet.getString(columnName); break; case Types.NUMERIC: case Types.DECIMAL: value = useIdx ? resultSet.getBigDecimal(columnIdx) : resultSet.getBigDecimal(columnName); break; case Types.BIT: case Types.BOOLEAN: value = new Boolean(useIdx ? resultSet.getBoolean(columnIdx) : resultSet.getBoolean(columnName)); break; case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: value = new Integer(useIdx ? resultSet.getInt(columnIdx) : resultSet.getInt(columnName)); break; case Types.BIGINT: value = new Long(useIdx ? resultSet.getLong(columnIdx) : resultSet.getLong(columnName)); break; case Types.REAL: value = new Float(useIdx ? resultSet.getFloat(columnIdx) : resultSet.getFloat(columnName)); break; case Types.FLOAT: case Types.DOUBLE: value = new Double(useIdx ? resultSet.getDouble(columnIdx) : resultSet.getDouble(columnName)); break; case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: value = useIdx ? resultSet.getBytes(columnIdx) : resultSet.getBytes(columnName); break; case Types.DATE: value = useIdx ? resultSet.getDate(columnIdx) : resultSet.getDate(columnName); break; case Types.TIME: value = useIdx ? resultSet.getTime(columnIdx) : resultSet.getTime(columnName); break; case Types.TIMESTAMP: value = useIdx ? resultSet.getTimestamp(columnIdx) : resultSet.getTimestamp(columnName); break; case Types.CLOB: Clob clob = useIdx ? resultSet.getClob(columnIdx) : resultSet.getClob(columnName); if (clob == null) { value = null; } else { long length = clob.length(); if (length > Integer.MAX_VALUE) { value = clob; } else if (length == 0) { // the javadoc is not clear about whether Clob.getSubString // can be used with a substring length of 0 // thus we do the safe thing and handle it ourselves value = ""; } else { value = clob.getSubString(1l, (int) length); } } break; case Types.BLOB: Blob blob = useIdx ? resultSet.getBlob(columnIdx) : resultSet.getBlob(columnName); if (blob == null) { value = null; } else { long length = blob.length(); if (length > Integer.MAX_VALUE) { value = blob; } else if (length == 0) { // the javadoc is not clear about whether Blob.getBytes // can be used with for 0 bytes to be copied // thus we do the safe thing and handle it ourselves value = new byte[0]; } else { value = blob.getBytes(1l, (int) length); } } break; case Types.ARRAY: value = useIdx ? resultSet.getArray(columnIdx) : resultSet.getArray(columnName); break; case Types.REF: value = useIdx ? resultSet.getRef(columnIdx) : resultSet.getRef(columnName); break; default: value = useIdx ? resultSet.getObject(columnIdx) : resultSet.getObject(columnName); break; } return resultSet.wasNull() ? null : value; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public Partition getPartition(String dbName, String tableName, int level) throws MetaException { boolean success = false; Connection con = null;/* ww w .j a va 2 s .c om*/ Statement ps = null; Partition part = null; dbName = dbName.toLowerCase(); tableName = tableName.toLowerCase(); Map<String, List<String>> partNameMap = new LinkedHashMap<String, List<String>>(); try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("get partition error, db=" + dbName + ", tbl=" + tableName + ", level=" + level + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("get partition error, db=" + dbName + ", tbl=" + tableName + ", level=" + level + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); ps = con.createStatement(); long tblID = 0; boolean isTblFind = false; String priPartType = null; String subPartType = null; boolean hasPriPart = false; boolean hasSubPart = false; String priPartKey = null; String subPartKey = null; String partKey = null; String sql = "SELECT tbl_id, pri_part_type, pri_part_key, sub_part_type, sub_part_key from TBLS where db_name='" + dbName + "' and tbl_name='" + tableName + "'"; ResultSet tblSet = ps.executeQuery(sql); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); priPartType = tblSet.getString(2); priPartKey = tblSet.getString(3); subPartType = tblSet.getString(4); subPartKey = tblSet.getString(5); if (priPartType != null && !priPartType.isEmpty()) { hasPriPart = true; } if (subPartType != null && !subPartType.isEmpty()) { hasSubPart = true; } if (hasPriPart && level == 0) { part = new Partition(); part.setParType(priPartType); partKey = priPartKey; break; } if (hasSubPart && level == 1) { part = new Partition(); part.setParType(subPartType); partKey = subPartKey; break; } con.commit(); return null; } tblSet.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tableName); } FieldSchema field = null; sql = "select type_name, comment from columns where tbl_id=" + tblID + " and column_name='" + partKey + "'"; ResultSet colSet = ps.executeQuery(sql); while (colSet.next()) { field = new FieldSchema(); field.setType(colSet.getString(1)); field.setComment(colSet.getString(2)); field.setName(partKey); break; } colSet.close(); sql = "select part_name, part_values from partitions where tbl_id=" + tblID + " and level=" + level; ResultSet partSet = ps.executeQuery(sql); while (partSet.next()) { String partName = partSet.getString(1); List<String> valueList = new ArrayList<String>(); Array spaceArray = partSet.getArray(2); ResultSet priValueSet = spaceArray.getResultSet(); if (priValueSet != null) { while (priValueSet.next()) { valueList.add(priValueSet.getString(2)); } } partNameMap.put(partName, valueList); } partSet.close(); part.setParSpaces(partNameMap); part.setDbName(dbName); part.setTableName(tableName); part.setLevel(level); part.setParKey(field); con.commit(); success = true; } catch (SQLException sqlex) { sqlex.printStackTrace(); LOG.error("get partition error, db=" + dbName + ", tbl=" + tableName + ", level=" + level + ", msg=" + sqlex.getMessage()); throw new MetaException(sqlex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } if (success) return part; else return null; }