@SuppressWarnings("unchecked") public static final List<Record> build(Config config, ResultSet rs) throws SQLException { List<Record> result = new ArrayList<Record>(); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); String[] labelNames = new String[columnCount + 1]; int[] types = new int[columnCount + 1]; buildLabelNamesAndTypes(rsmd, labelNames, types); while (rs.next()) { Record record = new Record(); record.setColumnsMap(config.containerFactory.getColumnsMap()); Map<String, Object> columns = record.getColumns(); for (int i = 1; i <= columnCount; i++) { Object value; if (types[i] < Types.BLOB) value = rs.getObject(i); else if (types[i] == Types.CLOB) value = ModelBuilder.handleClob(rs.getClob(i)); else if (types[i] == Types.NCLOB) value = ModelBuilder.handleClob(rs.getNClob(i)); else if (types[i] == Types.BLOB) value = ModelBuilder.handleBlob(rs.getBlob(i)); else value = rs.getObject(i); columns.put(labelNames[i], value); } result.add(record); } return result; }
private void testUnsupportedOperations() throws Exception { Connection conn = getConnection(); stat = conn.createStatement(); stat.execute("create table test(id int, c clob, b blob)"); stat.execute("insert into test values(1, 'x', x'00')"); ResultSet rs = stat.executeQuery("select * from test order by id"); rs.next(); Clob clob = rs.getClob(2); byte[] data = IOUtils.readBytesAndClose(clob.getAsciiStream(), -1); assertEquals("x", new String(data, "UTF-8")); assertTrue(clob.toString().endsWith("'x'")); clob.free(); assertTrue(clob.toString().endsWith("null")); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).truncate(0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).setAsciiStream(1); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).setString(1, "", 0, 1); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).position("", 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).position((Clob) null, 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, clob).getCharacterStream(1, 1); Blob blob = rs.getBlob(3); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).truncate(0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).setBytes(1, new byte[0], 0, 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).position(new byte[1], 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).position((Blob) null, 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, blob).getBinaryStream(1, 1); assertTrue(blob.toString().endsWith("X'00'")); blob.free(); assertTrue(blob.toString().endsWith("null")); stat.execute("drop table test"); conn.close(); }
private void listUser(ReqCtx ctx, HttpServletResponse response) throws IOException { try { // String sessionId = ctx.getArgVal(JSESSIONID_tag); // HttpSession session = SessionCounterListener.getSession(sessionId); OutputStream s = response.getOutputStream(); // outstmt = con.prepareStatement("SELECT Password,Status FROM User WHERE MobileNumber = ?"); // PreparedStatement stmt = con.prepareStatement("SELECT * FROM User where Name=?"); PreparedStatement stmt = con.prepareStatement("SELECT * FROM User where Status!='deleted'"); // stmt.setString(1,"First"); ResultSet rs = stmt.executeQuery(); ArrayList<HashMap<String, String>> set = new ArrayList<HashMap<String, String>>(); HashMap<String, String> cur; log(Level.INFO, "Listing User Start"); while (rs.next()) { cur = new HashMap<String, String>(); cur.put("userKeyID", rs.getString("UserId")); cur.put("usrName", rs.getString("Name")); cur.put("mobNum", rs.getString("MobileNumber")); cur.put("alias", rs.getString("Alias")); cur.put("usrEmail", rs.getString("EmailAddress")); cur.put("address", rs.getString("Address")); cur.put("notes", rs.getString("AddressVerificationNotes")); cur.put("remainingCreditPostAudit", rs.getString("RemainingCreditPostAudit")); cur.put("lastAuditedActivityAt", rs.getString("LastAuditedActivityAt")); java.sql.Blob bT = rs.getBlob("ImageFile"); String str = getBase64OfBlob(bT); cur.put("imageFile", str); cur.put("profilePic", getBase64OfBlob(rs.getBlob("ProfilePic"))); cur.put("idProofFile", getBase64OfBlob(rs.getBlob("IdProofFile"))); cur.put("addressProofFile", getBase64OfBlob(rs.getBlob("AddrProofFile"))); // Getting the MAC address corresponding to the current User Id from the Device table stmt = con.prepareStatement( "SELECT MAC FROM Device where UserId=?", Statement.RETURN_GENERATED_KEYS); stmt.setInt(1, rs.getInt("UserId")); ResultSet rs2 = stmt.executeQuery(); rs2.next(); cur.put("custDeviceId", rs2.getString(1)); // o.println("Writing : "+cur); set.add(cur); } log(INFO, "Sending the data of " + set.size() + " users"); byte objBytes[] = SerializationUtils.serialize(set); s.write(objBytes, 0, objBytes.length); s.close(); // out.close(); } catch (Exception e) { log(Level.INFO, "Listing User Failed"); errorResponse(ctx, response, e.getMessage()); log(WARNING, "Exception in listUser of Oprtr: " + e); log.error("stack trace", e); } }
public OIRulesGroup mapRow(ResultSet rs, int rowNum) throws SQLException { return new OIRulesGroup( rs.getInt("id"), blobToString(rs.getBlob("label")), blobToString(rs.getBlob("description")), rs.getInt("authorId"), rs.getBoolean("isUncommitted"), blobToString(rs.getBlob("username"))); }
public Pipeline mapRow(ResultSet rs, int rowNum) throws SQLException { return new Pipeline( rs.getInt("id"), blobToString(rs.getBlob("label")), blobToString(rs.getBlob("description")), rs.getBoolean("isDefault"), rs.getBoolean("isLocked"), rs.getInt("authorId"), blobToString(rs.getBlob("username"))); }
/** * This method is used to get a song by its id * * @param songid The id of the song we want to return * @return Returns a song if the id exists, or else null if it doesn't exist */ @Override public Song getSongById(int songid) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = getConnection(); String query = "SELECT * FROM " + TABLE_NAME + " WHERE " + SONGID + " = ?"; ps = con.prepareStatement(query); ps.setInt(1, songid); rs = ps.executeQuery(); if (rs.next()) { Blob songDataBlob = rs.getBlob(SONGDATA); byte songdata[] = null; if (songDataBlob != null) songdata = songDataBlob.getBytes(1, (int) songDataBlob.length()); Blob artworkBlob = rs.getBlob(ARTWORK); byte art[] = null; if (artworkBlob != null) art = artworkBlob.getBytes(1, (int) artworkBlob.length()); return new Song( rs.getInt(SONGID), rs.getString(FILENAME), rs.getString(TITLE), rs.getString(ARTIST), rs.getString(ALBUM), rs.getString(GENRE), rs.getInt(YEAR), rs.getInt(DURATION), rs.getDouble(PRICE), rs.getString(LICENSE), rs.getInt(PLAYCOUNT), rs.getDate(UPLOADDATE), art, songdata); } } catch (SQLException ex1) { if (DEBUG) ex1.printStackTrace(); } finally { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (con != null) freeConnection(con); } catch (SQLException e) { if (DEBUG) e.printStackTrace(); } } return null; }
public void testBlobA() { try { String ddl0 = "DROP TABLE BLOBTEST IF EXISTS"; String ddl1 = "CREATE TABLE BLOBTEST(ID IDENTITY, BLOBFIELD BLOB(1000))"; statement.execute(ddl0); statement.execute(ddl1); } catch (SQLException e) { e.printStackTrace(); } try { String dml0 = "insert into blobtest(blobfield) values(?)"; String dql0 = "select * from blobtest;"; PreparedStatement ps = connection.prepareStatement(dml0); byte[] data = new byte[] {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; Blob blob = new JDBCBlob(data); ps.setBlob(1, blob); ps.executeUpdate(); data[4] = 50; blob = new JDBCBlob(data); ps.setBlob(1, blob); ps.executeUpdate(); ps.close(); ps = connection.prepareStatement(dql0); ResultSet rs = ps.executeQuery(); rs.next(); Blob blob1 = rs.getBlob(2); rs.next(); Blob blob2 = rs.getBlob(2); byte[] data1 = blob1.getBytes(1, 10); byte[] data2 = blob2.getBytes(1, 10); assertTrue(data1[4] == 5 && data2[4] == 50); } catch (SQLException e) { e.printStackTrace(); } }
@Override public Object fetchBLOB(ResultSet rs, int column, EOAttribute attribute, boolean materialize) throws SQLException { NSData data = null; Blob blob = rs.getBlob(column); if (blob == null) { return null; } if (!materialize) { return blob; } InputStream stream = blob.getBinaryStream(); try { int chunkSize = (int) blob.length(); if (chunkSize == 0) { data = NSData.EmptyData; } else { data = new NSData(stream, chunkSize); } } catch (IOException e) { throw new JDBCAdaptorException(e.getMessage(), null); } finally { try { if (stream != null) stream.close(); } catch (IOException e) { /* Nothing we can do */ } ; } return data; }
static void read() throws SQLException, IOException { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); // conn = JdbcUtilsSingle.getInstance().getConnection(); st = conn.createStatement(); rs = st.executeQuery("select text from textdemo"); while (rs.next()) { Blob blob = rs.getBlob(1); InputStream in = blob.getBinaryStream(); // reader = rs.getCharacterStream(1); File file = new File("JdbcUtils_bak.java"); OutputStream out = new BufferedOutputStream(new FileOutputStream(file)); byte[] buff = new byte[1024]; for (int i = 0; (i = in.read(buff)) > 0; ) { out.write(buff, 0, i); } out.close(); in.close(); } } finally { JdbcUtils.free(rs, st, conn); } }
public String getValue(int idx) { String val = ""; try { int cType = getColumnType(idx); if (cType == 2004) { // BLOB val = "BLOB"; Blob blob = rs.getBlob(idx); if (blob == null) { val = null; } else { val = "BLOB size=" + blob.length(); } } else val = rs.getString(idx); } catch (SQLException e) { val = e.getMessage(); int cType = getColumnType(idx); System.err.print("Column type: " + cType); } if (val != null && val.endsWith(" 00:00:00.0")) val = val.substring(0, val.length() - 11); return val; }
// // Examine BLOBs and CLOBs. // private void vetLargeObjects( Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList) throws Exception { Statement stmt = conn.createStatement(); stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))"); stmt.execute( "INSERT INTO t (id, b, c) VALUES (1, " + "CAST (" + TestUtil.stringToHexLiteral("101010001101") + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))"); ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t"); rs.next(); Blob blob = rs.getBlob(2); Clob clob = rs.getClob(3); vetObject(blob, unsupportedList, notUnderstoodList); vetObject(clob, unsupportedList, notUnderstoodList); stmt.close(); conn.rollback(); }
public String getBlob(int idx) { String val = ""; try { int cType = getColumnType(idx); if (cType == 2004) { // BLOB val = "BLOB"; Blob blob = rs.getBlob(idx); if (blob == null) { val = null; } else { byte[] bdata = blob.getBytes(1, (int) blob.length()); val = new String(bdata); } } else val = rs.getString(idx); } catch (SQLException e) { val = e.getMessage(); int cType = getColumnType(idx); System.err.print("Column type: " + cType); } // if (val != null && val.endsWith(" 00:00:00.0")) val = val.substring(0, val.length()-11); // System.out.print("BLOB=" + val); return val; }
/* * (non-Javadoc) * @see org.datanucleus.store.rdbms.mapping.AbstractLargeBinaryRDBMSMapping#getObject(java.lang.Object, * int) */ @Override public Object getObject(ResultSet rs, int param) { byte[] bytes = null; try { // Retrieve the bytes of the object directly bytes = rs.getBytes(param); if (bytes == null) { return null; } } catch (SQLException sqle) { try { // Retrieve the bytes using the Blob (if getBytes not supported e.g HSQLDB 2.0) Blob blob = rs.getBlob(param); if (blob == null) { return null; } bytes = blob.getBytes(1, (int) blob.length()); if (bytes == null) { return null; } } catch (SQLException sqle2) { throw new NucleusDataStoreException( Localiser.msg("055002", "Object", "" + param, column, sqle2.getMessage()), sqle2); } } return getObjectForBytes(bytes, param); }
/** 读取数据库带图片的一条记录 */ @Test public void getImage() { Connection conn = null; Statement stmt = null; ResultSet rs = null; conn = JDBCUtils.getConnection(); try { stmt = conn.createStatement(); String sql = "select * from bt_user where id=1"; rs = stmt.executeQuery(sql); if (rs.next()) { Blob blob = rs.getBlob("headimage"); InputStream is = blob.getBinaryStream(); String path = "D:\\work\\Workspaces\\day14_jdbc\\src\\cn\\itcast\\mysql\\bt\\mm2.jpg"; OutputStream os = new FileOutputStream(path); byte[] buffer = new byte[1024]; int len = -1; while ((len = is.read(buffer)) != -1) { os.write(buffer, 0, len); } // os.flush(); os.close(); // close中有flush is.close(); } } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { JDBCUtils.closeResource(conn, stmt, rs); } }
@Override public Article mapRow(ResultSet rs, int rowNum) throws SQLException { Article a = new Article(); a.setId(rs.getInt("id")); a.setTitle(rs.getString("title")); Blob blob = rs.getBlob("content"); int len = (int) blob.length(); byte[] data = blob.getBytes(0, len); String content = new String(data); a.setContent(content); a.setCtime(rs.getTimestamp("ctime")); a.setUptime(rs.getTimestamp("uptime")); a.setTags(rs.getString("tags")); a.setCategory(rs.getString("category")); a.setAuthorId(rs.getInt("authorId")); a.setEditorId(rs.getInt("editorId")); a.setMediaId(rs.getInt("mediaId")); a.setPics(rs.getString("pics")); a.setExtra(rs.getString("extra")); return a; }
private void testBlob(int length) throws Exception { Random r = new Random(length); byte[] data = new byte[length]; r.nextBytes(data); Blob b = conn.createBlob(); OutputStream out = b.setBinaryStream(1); out.write(data, 0, data.length); out.close(); stat.execute("delete from test"); PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)"); prep.setInt(1, 1); prep.setBlob(2, b); prep.execute(); prep.setInt(1, 2); b = conn.createBlob(); b.setBytes(1, data); prep.setBlob(2, b); prep.execute(); prep.setInt(1, 3); prep.setBlob(2, new ByteArrayInputStream(data)); prep.execute(); prep.setInt(1, 4); prep.setBlob(2, new ByteArrayInputStream(data), -1); prep.execute(); ResultSet rs; rs = stat.executeQuery("select * from test"); rs.next(); Blob b2 = rs.getBlob(2); assertEquals(length, b2.length()); byte[] bytes = b.getBytes(1, length); byte[] bytes2 = b2.getBytes(1, length); assertEquals(bytes, bytes2); rs.next(); b2 = rs.getBlob(2); assertEquals(length, b2.length()); bytes2 = b2.getBytes(1, length); assertEquals(bytes, bytes2); while (rs.next()) { bytes2 = rs.getBytes(2); assertEquals(bytes, bytes2); } }
/** * @throws SQLException * @throws IOException * @see com.tl.db.dialect.Dialect#getBlob(java.sql.ResultSet, java.lang.String) */ public IBlob getBlob(ResultSet rs, String name) throws SQLException, IOException { Blob blob = rs.getBlob(name); if (blob != null) { byte[] content = LobHelper.readBlob(blob); return LobHelper.createBlob(content); } return null; }
public InputStream getBlobStream(int columnIndex) throws SQLException { Blob blob = resultSet.getBlob(columnIndex + 1); if (blob == null) { return null; } else { return blob.getBinaryStream(); } }
@Override public Blob getBlob(int i) throws SQLException { try { return _res.getBlob(i); } catch (SQLException e) { handleException(e); return null; } }
@Override public Blob getBlob(String colName) throws SQLException { try { return _res.getBlob(colName); } catch (SQLException e) { handleException(e); return null; } }
@Override public ArrayList<TenantBean> getTenantByExpectedYearofGrad(int year) { try { Connector c = new Connector(); Connection connection = c.getConnection(); String query = "select * from tenant where expectedyearofgrad = ?"; PreparedStatement ps = connection.prepareStatement(query); ps.setInt(1, year); ResultSet resultSet = ps.executeQuery(); ArrayList<TenantBean> list = new ArrayList<TenantBean>(); TenantBean bean = new TenantBean(); int tenantID, expectedyearofgrad; Long contact; String fname, lname, gender, address, degree, school; boolean status; Blob image; while (resultSet.next()) { tenantID = resultSet.getInt("tenantID"); contact = resultSet.getLong("contact"); expectedyearofgrad = resultSet.getInt("expectedyearofgrad"); fname = resultSet.getString("fname"); lname = resultSet.getString("lname"); gender = resultSet.getString("gender"); address = resultSet.getString("address"); degree = resultSet.getString("degree"); school = resultSet.getString("school"); status = resultSet.getBoolean("status"); image = resultSet.getBlob("image"); bean = new TenantBean(); bean.setTenantID(tenantID); bean.setContact(contact); bean.setExpectedyearofgrad(expectedyearofgrad); bean.setFname(fname); bean.setLname(lname); bean.setGender(gender); bean.setDegree(degree); bean.setAddress(address); bean.setSchool(school); bean.setStatus(status); bean.setImage(image); list.add(bean); } return list; } catch (SQLException ex) { Logger.getLogger(TenantDAOImplementation.class.getName()).log(Level.SEVERE, null, ex); } return null; }
@Override protected byte[] getBlobAsBytes(ResultSet rs, int col) throws SQLException { if (_useBytesMethodsForBlob) { return rs.getBytes(col); } else { Blob dataAsBlob = rs.getBlob(col); return dataAsBlob.getBytes(1, (int) dataAsBlob.length()); } }
/** * Gets a blob with its current version from the database. If an exception or error occures, this * method closes the database connection. * * @param blobId The id of the blob. * @param forUpdate Whether a pessimistic lock should be applied on the blob or not. * @param connection The database connection. * @return The blob and its current version. */ protected ConnectedBlob connectBlob( final long blobId, final boolean forUpdate, final Connection connection) { QBlobstoreBlob qBlob = QBlobstoreBlob.blobstoreBlob; try { SQLQuery<Tuple> query = new SQLQuery<>(connection, querydslConfiguration) .select( qBlob.blobId, qBlob.version_.as("version_"), Expressions.as(blobSelectionExpression, "blob_")) .from(qBlob) .where(qBlob.blobId.eq(blobId)); if (forUpdate) { query.forUpdate(); } else if (lockBlobForShareQueryFlag != null) { query.addFlag(lockBlobForShareQueryFlag); } SQLBindings sqlBindings = query.getSQL(); String sql = sqlBindings.getSQL(); PreparedStatement preparedStatement = connection.prepareStatement(sql); int paramIndex = 0; for (Object binding : (List<Object>) sqlBindings.getBindings()) { paramIndex++; preparedStatement.setObject(paramIndex, binding); } long version; Blob blob; ResultSet resultSet = preparedStatement.executeQuery(); try { if (!resultSet.next()) { throw new NoSuchBlobException(blobId); } version = resultSet.getLong("version_"); blob = resultSet.getBlob("blob_"); } finally { final boolean closeResultSetIsNotNecessaryAsItWillBeClosedByStatement = false; closeResultSet(resultSet, closeResultSetIsNotNecessaryAsItWillBeClosedByStatement); } BlobChannel blobChannel; if (blobAccessMode == BlobAccessMode.BYTES) { blobChannel = new BytesBlobChannel(blob); } else { blobChannel = new StreamBlobChannel(blob); } return new ConnectedBlob(blobId, blobChannel, version, preparedStatement); } catch (SQLException | RuntimeException | Error e) { closeCloseableDueToThrowable(connection, e); // TODO throw unchecked sql exception throw new RuntimeException(e); } }
public DataSourceVO<?> mapRow(ResultSet rs, int rowNum) throws SQLException { DataSourceVO<?> ds = (DataSourceVO<?>) SerializationHelper.readObjectInContext(rs.getBlob(5).getBinaryStream()); ds.setId(rs.getInt(1)); ds.setXid(rs.getString(2)); ds.setName(rs.getString(3)); ds.setDefinition(ModuleRegistry.getDataSourceDefinition(rs.getString(4))); return ds; }
/** * According to the JDBC spec, BLOB and CLOB objects must stay open even if the result set is * closed (see ResultSet.close). */ private void testLobStaysOpenUntilCommitted() throws Exception { Connection conn = getConnection(); stat = conn.createStatement(); stat.execute("create table test(id identity, c clob, b blob)"); PreparedStatement prep = conn.prepareStatement("insert into test values(null, ?, ?)"); prep.setString(1, ""); prep.setBytes(2, new byte[0]); prep.execute(); Random r = new Random(1); char[] chars = new char[100000]; for (int i = 0; i < chars.length; i++) { chars[i] = (char) r.nextInt(10000); } String d = new String(chars); prep.setCharacterStream(1, new StringReader(d), -1); byte[] bytes = new byte[100000]; r.nextBytes(bytes); prep.setBinaryStream(2, new ByteArrayInputStream(bytes), -1); prep.execute(); conn.setAutoCommit(false); ResultSet rs = stat.executeQuery("select * from test order by id"); rs.next(); Clob c1 = rs.getClob(2); Blob b1 = rs.getBlob(3); rs.next(); Clob c2 = rs.getClob(2); Blob b2 = rs.getBlob(3); assertFalse(rs.next()); // now close rs.close(); // but the LOBs must stay open assertEquals(0, c1.length()); assertEquals(0, b1.length()); assertEquals(chars.length, c2.length()); assertEquals(bytes.length, b2.length()); assertEquals("", c1.getSubString(1, 0)); assertEquals(new byte[0], b1.getBytes(1, 0)); assertEquals(d, c2.getSubString(1, (int) c2.length())); assertEquals(bytes, b2.getBytes(1, (int) b2.length())); stat.execute("drop table test"); conn.close(); }
@Override public List<barang> getBarangbyKeterangan(String keterangan) throws BarangException { List<barang> list = new ArrayList<barang>(); PreparedStatement statement = null; barang barang = null; try { connection.setAutoCommit(false); statement = connection.prepareStatement(getByket); statement.setString(1, "%" + keterangan + "%"); ResultSet rs = statement.executeQuery(); while (rs.next()) { barang = new barang(); barang.setIdBarang(rs.getString("idbarang")); barang.setIdBarcode(rs.getString("idbarcode")); barang.setNamaBarang(rs.getString("namabarang")); barang.setTipe(rs.getString("tipe")); barang.setMerek(rs.getString("merek")); barang.setHargamodal(rs.getInt("hargamodal")); barang.setEceran(rs.getInt("eceran")); barang.setGrosir(rs.getInt("grosir")); barang.setSatuan(rs.getString("satuan")); barang.setStok(rs.getInt("stok")); barang.setStokMinimum(rs.getInt("stok_minimum")); barang.setSupplier(rs.getString("supplier")); barang.setKeterangan(rs.getString("keterangan")); barang.setGambarHasil(rs.getBlob("gambar")); barang.setKategori(rs.getString("kategori")); list.add(barang); } connection.commit(); // return list; } catch (SQLException exception) { try { connection.rollback(); } catch (SQLException ex) { } // throw new barangException(exception.getMessage()); } finally { try { connection.setAutoCommit(true); } catch (SQLException ex) { } if (statement != null) { try { statement.close(); } catch (SQLException exception) { } } } return list; }
public static String getBlobValue(ResultSet result, String strField) throws java.sql.SQLException { String strValueReturn = ""; Blob blob = result.getBlob(strField); if (result.wasNull()) { strValueReturn = ""; } else { int length = (int) blob.length(); if (length > 0) strValueReturn = new String(blob.getBytes(1, length)); } return strValueReturn; }
private static JSONObject toJSONObject(ResultSetMetaData rsmd, int numColumns, ResultSet rs) throws JSONException, SQLException { JSONObject obj = new JSONObject(); for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); switch (rsmd.getColumnType(i)) { case java.sql.Types.ARRAY: obj.put(column_name, rs.getArray(i)); break; case java.sql.Types.BIGINT: obj.put(column_name, rs.getInt(i)); break; case java.sql.Types.BOOLEAN: obj.put(column_name, rs.getBoolean(i)); break; case java.sql.Types.BLOB: obj.put(column_name, rs.getBlob(i)); break; case java.sql.Types.DOUBLE: obj.put(column_name, rs.getDouble(i)); break; case java.sql.Types.FLOAT: obj.put(column_name, rs.getFloat(i)); break; case java.sql.Types.INTEGER: obj.put(column_name, rs.getInt(i)); break; case java.sql.Types.NVARCHAR: obj.put(column_name, rs.getNString(i)); break; case java.sql.Types.VARCHAR: obj.put(column_name, rs.getString(i)); break; case java.sql.Types.TINYINT: obj.put(column_name, rs.getInt(i)); break; case java.sql.Types.SMALLINT: obj.put(column_name, rs.getInt(i)); break; case java.sql.Types.DATE: obj.put(column_name, rs.getDate(i)); break; case java.sql.Types.TIMESTAMP: obj.put(column_name, rs.getTimestamp(i)); break; default: obj.put(column_name, rs.getObject(i)); break; } } return obj; }
/** * Get value from given ResultSet at given index with given SQL type. * * @param rs The ResultSet to get the value from. * @param index The index of the value in the ResultSet. * @param sqlType The SQL type of the value. * @return The value. * @throws SQLException If a database access error occurs. */ public static Object getValue(final ResultSet rs, final int index, final int sqlType) throws SQLException { switch (sqlType) { case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: return rs.getString(index); case Types.DECIMAL: case Types.NUMERIC: return rs.getBigDecimal(index); case Types.INTEGER: int intVal = rs.getInt(index); return (rs.wasNull() ? null : new Integer(intVal)); case Types.TIME: return rs.getTime(index, getCalendar()); case Types.DATE: return rs.getDate(index); case Types.TIMESTAMP: return rs.getTimestamp(index, getCalendar()); case Types.FLOAT: case Types.DOUBLE: double doubleVal = rs.getDouble(index); return (rs.wasNull() ? null : new Double(doubleVal)); case Types.REAL: float floatVal = rs.getFloat(index); return (rs.wasNull() ? null : new Float(floatVal)); case Types.SMALLINT: short shortVal = rs.getShort(index); return (rs.wasNull() ? null : new Short(shortVal)); case Types.TINYINT: byte byteVal = rs.getByte(index); return (rs.wasNull() ? null : new Byte(byteVal)); case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BINARY: return rs.getBytes(index); case Types.BLOB: Blob blob = rs.getBlob(index); return (blob == null ? null : blob.getBinaryStream()); case Types.CLOB: return rs.getClob(index); case Types.BIGINT: long longVal = rs.getLong(index); return (rs.wasNull() ? null : new Long(longVal)); case Types.BIT: boolean boolVal = rs.getBoolean(index); return (rs.wasNull() ? null : new Boolean(boolVal)); default: Object value = rs.getObject(index); return (rs.wasNull() ? null : value); } }
public void testSerialize() throws IOException, ClassNotFoundException { SparseDoubleMatrix2D matrix2D = new SparseDoubleMatrix2D( new double[][] { new double[] {1, 2, 3}, new double[] {4, 5, 6}, new double[] {7, 8, 9} }); System.out.println(String.format("matrix2D: %s", matrix2D)); try { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex) { System.err.println("Could not initiate JDBC driver."); ex.printStackTrace(); } try { Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/wittcarl_recurrence_plot_clustering?user=root&password=mysql"); PreparedStatement preps = conn.prepareStatement( "INSERT INTO `wittcarl_recurrence_plot_clustering`.`blob` (`id`, `signature`) VALUES (null, ?);"); preps.setObject(1, matrix2D); preps.execute(); PreparedStatement get = conn.prepareStatement("SELECT id, signature FROM `blob` WHERE id=3"); ResultSet resultSet = get.executeQuery(); resultSet.next(); int id = resultSet.getInt(1); InputStream is = resultSet.getBlob(2).getBinaryStream(); ObjectInputStream oip = new ObjectInputStream(is); Object object = oip.readObject(); String className = object.getClass().getName(); oip.close(); is.close(); resultSet.close(); // de-serialize list a java object from a given objectID SparseDoubleMatrix2D restored = (SparseDoubleMatrix2D) object; System.out.println(String.format("id: %s", id)); System.out.println(String.format("restored: %s", restored)); conn.close(); } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } }