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:InsertStores.java
public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con;/* ww w. java2 s . c o m*/ Statement stmt; try { Class.forName("myDriver.ClassName"); } catch (java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); } try { con = DriverManager.getConnection(url, "myLogin", "myPassword"); stmt = con.createStatement(); con.setAutoCommit(false); String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, " + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore1); String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, " + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))"; stmt.addBatch(insertStore2); String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, " + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore3); String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, " + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))"; stmt.addBatch(insertStore4); String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, " + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), " + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', " + "'Colombian_Decaf', 'French_Roast_Decaf'), " + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))"; stmt.addBatch(insertStore5); int[] updateCounts = stmt.executeBatch(); ResultSet rs = stmt.executeQuery("SELECT * FROM STORES"); System.out.println("Table STORES after insertion:"); System.out.println("STORE_NO LOCATION COF_TYPE MGR"); while (rs.next()) { int storeNo = rs.getInt("STORE_NO"); Struct location = (Struct) rs.getObject("LOCATION"); Object[] locAttrs = location.getAttributes(); Array coffeeTypes = rs.getArray("COF_TYPE"); String[] cofTypes = (String[]) coffeeTypes.getArray(); Ref managerRef = rs.getRef("MGR"); PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?"); pstmt.setRef(1, managerRef); ResultSet rs2 = pstmt.executeQuery(); rs2.next(); Struct manager = (Struct) rs2.getObject("MANAGER"); Object[] manAttrs = manager.getAttributes(); System.out.print(storeNo + " "); System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + " " + locAttrs[4] + " "); for (int i = 0; i < cofTypes.length; i++) System.out.print(cofTypes[i] + " "); System.out.println(manAttrs[1] + ", " + manAttrs[2]); rs2.close(); pstmt.close(); } rs.close(); stmt.close(); con.close(); } catch (BatchUpdateException b) { System.err.println("-----BatchUpdateException-----"); System.err.println("SQLState: " + b.getSQLState()); System.err.println("Message: " + b.getMessage()); System.err.println("Vendor: " + b.getErrorCode()); System.err.print("Update counts: "); int[] updateCounts = b.getUpdateCounts(); for (int i = 0; i < updateCounts.length; i++) { System.err.print(updateCounts[i] + " "); } System.err.println(""); } catch (SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); System.err.println("SQLState: " + ex.getSQLState()); System.err.println("Message: " + ex.getMessage()); System.err.println("Vendor: " + ex.getErrorCode()); } }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static JSONArray convert(ResultSet rs) throws SQLException, JSONException { JSONArray json = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); 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)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } 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, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } 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) { obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); }/*from w w w . ja v a 2s .co m*/ } json.put(obj); } return json; }
From source file:org.trafodion.rest.util.JdbcT4Util.java
public static JSONArray convertResultSetToJSON(java.sql.ResultSet rs) throws Exception { if (LOG.isDebugEnabled()) LOG.debug("Begin convertResultSetToJSON"); JSONArray json = new JSONArray(); try {//from ww w.java 2s. c o m java.sql.ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); 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)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getLong(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } 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, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } 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.CHAR || rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { //prevent obj.put from removing null key value from JSONObject String s = rs.getString(column_name); if (s == null) obj.put(column_name, new String("")); else obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.put(obj); } //end while if (json.length() == 0) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); obj.put(column_name, ""); } json.put(obj); } } catch (SQLException e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } catch (Exception e) { e.printStackTrace(); if (LOG.isDebugEnabled()) LOG.error(e.getMessage()); throw e; } if (LOG.isDebugEnabled()) LOG.debug("End convertResultSetToJSON"); return json; }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static JSONArray convertGeoJson(ResultSet rs) throws SQLException, JSONException { JSONArray json = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { int numColumns = rsmd.getColumnCount(); JSONObject obj = new JSONObject(); JSONObject feat = new JSONObject(); feat.put("type", "Feature"); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (StringUtils.equals(column_name, "the_geom")) { continue; }//ww w . j a v a 2 s . c o m if (StringUtils.equals(column_name, "geojson")) { continue; } if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, rs.getBoolean(column_name)); } 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, rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, rs.getInt(column_name)); } 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) { obj.put(column_name, rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, rs.getTimestamp(column_name)); } else { obj.put(column_name, rs.getObject(column_name)); } } feat.put("properties", obj); try { rs.findColumn("lon"); rs.findColumn("lat"); JSONObject geo = new JSONObject(); JSONArray coord = new JSONArray(); coord.put(rs.getDouble("lon")); coord.put(rs.getDouble("lat")); geo.put("type", "point"); geo.put("coordinates", coord); feat.put("geometry", geo); } catch (Exception ex1) { ; } json.put(feat); } return json; }
From source file:io.lightlink.types.ArrayConverter.java
@Override public Object readFromResultSet(ResultSet resultSet, int pos, RunnerContext runnerContext, String colName) throws SQLException, IOException { Array array = resultSet.getArray(pos); return array == null ? null : array.getArray(); }
From source file:ru.org.linux.user.ProfileDao.java
@Nonnull public Profile readProfile(@NotNull User user) { List<Profile> profiles = jdbcTemplate.query("SELECT settings, main FROM user_settings WHERE id=?", new RowMapper<Profile>() { @Override// w w w . j ava 2s . co m public Profile mapRow(ResultSet resultSet, int i) throws SQLException { Array boxes = resultSet.getArray("main"); if (boxes != null) { return new Profile( new ProfileHashtable(DefaultProfile.getDefaultProfile(), (Map<String, String>) resultSet.getObject("settings")), Arrays.asList((String[]) boxes.getArray())); } else { return new Profile(new ProfileHashtable(DefaultProfile.getDefaultProfile(), (Map<String, String>) resultSet.getObject("settings")), null); } } }, user.getId()); if (profiles.isEmpty()) { return new Profile( new ProfileHashtable(DefaultProfile.getDefaultProfile(), new HashMap<String, String>()), null); } else { return profiles.get(0); } }
From source file:org.smallmind.persistence.orm.hibernate.LongArrayUserType.java
@Override public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor sessionImplementor, final Object owner) throws HibernateException, SQLException { Array array = rs.getArray(names[0]); Long[] javaArray = (Long[]) array.getArray(); return ArrayUtils.toPrimitive(javaArray); }
From source file:annis.sqlgen.AnnotatedSpanExtractor.java
@Override public AnnotatedSpan mapRow(ResultSet resultSet, int rowNum) throws SQLException { long id = resultSet.getLong("id"); String coveredText = resultSet.getString("span"); Array arrayAnnotation = resultSet.getArray("annotations"); ResultSetMetaData rsMeta = resultSet.getMetaData(); Array arrayMeta = null;/*from w w w . j a v a 2s. com*/ for (int i = 1; i <= rsMeta.getColumnCount(); i++) { if ("metadata".equals(rsMeta.getColumnName(i))) { arrayMeta = resultSet.getArray(i); break; } } List<Annotation> annotations = extractAnnotations(arrayAnnotation); List<Annotation> metaData = arrayMeta == null ? new LinkedList<Annotation>() : extractAnnotations(arrayMeta); // create key Array sqlKey = resultSet.getArray("key"); Validate.isTrue(!resultSet.wasNull(), "Match group identifier must not be null"); Validate.isTrue(sqlKey.getBaseType() == Types.BIGINT, "Key in database must be from the type \"bigint\" but was \"" + sqlKey.getBaseTypeName() + "\""); List<Long> key = Arrays.asList((Long[]) sqlKey.getArray()); return new AnnotatedSpan(id, coveredText, annotations, metaData, key); }
From source file:org.georepublic.db.utils.ResultSetConverter.java
public static StringBuffer convertCsv(ResultSet rs) throws SQLException { String column_name = new String(); StringBuffer retval = new StringBuffer(); ResultSetMetaData rsmd = rs.getMetaData(); int numColumns = rsmd.getColumnCount(); for (int h = 1; h < numColumns + 1; h++) { column_name = rsmd.getColumnName(h); if (h > 1) { retval.append(","); }/* w w w .ja v a2s . c om*/ retval.append(column_name); } retval.append("\n"); while (rs.next()) { for (int i = 1; i < numColumns + 1; i++) { column_name = rsmd.getColumnName(i); if (StringUtils.equals(column_name, "the_geom")) { continue; } if (StringUtils.equals(column_name, "geojson")) { continue; } if (i > 1) { retval.append(","); } if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { retval.append(rs.getArray(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { retval.append(rs.getBoolean(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { retval.append(rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { retval.append(rs.getDouble(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { retval.append(rs.getFloat(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { retval.append(rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { retval.append(rs.getString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { retval.append(rs.getInt(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { retval.append(rs.getDate(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { retval.append(rs.getTimestamp(column_name)); } else { retval.append(rs.getObject(column_name)); } } retval.append("\n"); } return retval; }
From source file:org.kawanfw.test.api.client.ArrayTest.java
/** * @param connection//from w w w . j av a 2s . c o m * the AceQL Connection * * @throws SQLException * @throws Exception */ public void test(Connection connection) throws SQLException, Exception { MessageDisplayer.initClassDisplay(this.getClass().getSimpleName()); DatabaseMetaData databaseMetaData = connection.getMetaData(); MessageDisplayer.display( "databaseMetaData.getDatabaseProductName() : " + databaseMetaData.getDatabaseProductName()); if (connection instanceof RemoteConnection) { RemoteConnection connectionHttp = (RemoteConnection) connection; if (connectionHttp.isStatelessMode()) { MessageDisplayer.display("Arrays are not supported in stateless mode"); return; } } if (!new SqlUtil(connection).isPostgreSQL() && !new SqlUtil(connection).isHSQLDB() && !new SqlUtil(connection).isOracle()) { MessageDisplayer.display("Arrays tests are only supported in HSQLDB, Oracle Database & PostgreSQL"); return; } Object[] northEastRegion = { "10022", "02110", "07399" }; String sql = null; boolean doInsert = true; if (doInsert) { // Array aArray = connection.createArrayOf("varchar", // northEastRegion); Array aArray = null; if (new SqlUtil(connection).isOracle()) { aArray = connection.createArrayOf("VCARRAY", northEastRegion); } else { aArray = connection.createArrayOf("varchar", northEastRegion); } PreparedStatement pstmt = connection.prepareStatement("delete from REGIONS where region_name = ?"); pstmt.setString(1, "NorthEast"); pstmt.executeUpdate(); pstmt.close(); PreparedStatement pstmt2 = connection .prepareStatement("insert into REGIONS (region_name, zips) " + "VALUES (?, ?)"); pstmt2.setString(1, "NorthEast"); pstmt2.setArray(2, aArray); pstmt2.executeUpdate(); pstmt2.close(); } sql = "select * from REGIONS"; PreparedStatement prepStatement = connection.prepareStatement(sql); ResultSet rs = prepStatement.executeQuery(); while (rs.next()) { String region_name = rs.getString(1); MessageDisplayer.display(region_name + " "); Array z = rs.getArray("ZIPS"); Object[] zips = (Object[]) z.getArray(); for (int i = 0; i < zips.length; i++) { System.out.print(zips[i] + " "); } MessageDisplayer.display(""); if (region_name.equals("NorthEast")) { MessageDisplayer.display("Region is NorthEast. Test arrays equality"); Assert.assertArrayEquals("Region is NorthEast. Test arrays equality", northEastRegion, zips); } } rs.close(); MessageDisplayer.display("Done!"); }