List of usage examples for java.sql ResultSet getBlob
Blob getBlob(String columnLabel) throws SQLException;
ResultSet object as a Blob object in the Java programming language. From source file:org.fao.geonet.arcgis.ArcSDEJdbcConnection.java
@Override public Map<String, String> retrieveMetadata(AtomicBoolean cancelMonitor, String arcSDEVersion) throws Exception { Map<String, String> results = new HashMap<>(); ArcSDEVersionFactory arcSDEVersionFactory = new ArcSDEVersionFactory(); String metadataTable = arcSDEVersionFactory.getTableName(arcSDEVersion); String columnName = arcSDEVersionFactory.getMetadataColumnName(arcSDEVersion); String sqlQuery = "SELECT " + columnName + ", UUID FROM " + metadataTable; getJdbcTemplate().query(sqlQuery, new RowCallbackHandler() { @Override/*from www . ja va2s .c om*/ public void processRow(ResultSet rs) throws SQLException { // Cancel processing if (cancelMonitor.get()) { Log.warning(ARCSDE_LOG_MODULE_NAME, "Cancelling metadata retrieve using " + "ArcSDE connection (via JDBC)"); rs.getStatement().cancel(); results.clear(); } String document = ""; int colId = rs.findColumn(columnName); int colIdUuid = rs.findColumn("UUID"); // very simple type check: if (rs.getObject(colId) != null) { if (rs.getMetaData().getColumnType(colId) == Types.BLOB) { Blob blob = rs.getBlob(columnName); byte[] bdata = blob.getBytes(1, (int) blob.length()); document = new String(bdata); } else if (rs.getMetaData().getColumnType(colId) == Types.LONGVARBINARY) { byte[] byteData = rs.getBytes(colId); document = new String(byteData); } else if (rs.getMetaData().getColumnType(colId) == Types.LONGNVARCHAR || rs.getMetaData().getColumnType(colId) == Types.LONGVARCHAR || rs.getMetaData().getColumnType(colId) == Types.VARCHAR || rs.getMetaData().getColumnType(colId) == Types.SQLXML) { document = rs.getString(colId); } else { throw new SQLException("Trying to harvest from a column with an invalid datatype: " + rs.getMetaData().getColumnTypeName(colId)); } String uuid = rs.getString(colIdUuid); ; results.put(uuid, document); } } }); Log.info(ARCSDE_LOG_MODULE_NAME, "Finished retrieving metadata, found: #" + results.size() + " metadata records"); return results; }
From source file:org.apache.syncope.core.util.ContentExporter.java
private String getValues(final ResultSet rs, final String columnName, final Integer columnType) throws SQLException { String res = null;/*w w w . j a va 2 s . c o m*/ try { switch (columnType) { case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: final InputStream is = rs.getBinaryStream(columnName); if (is != null) { res = new String(Hex.encode(IOUtils.toByteArray(is))); } break; case Types.BLOB: final Blob blob = rs.getBlob(columnName); if (blob != null) { res = new String(Hex.encode(IOUtils.toByteArray(blob.getBinaryStream()))); } break; case Types.BIT: case Types.BOOLEAN: if (rs.getBoolean(columnName)) { res = "1"; } else { res = "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnName); if (timestamp != null) { res = DataFormat.format(new Date(timestamp.getTime())); } break; default: res = rs.getString(columnName); } } catch (IOException e) { LOG.error("Error retrieving hexadecimal string", e); } return res; }
From source file:it.greenvulcano.gvesb.utils.ResultSetUtils.java
/** * Returns all values from the ResultSet as an XML. * For instance, if the ResultSet has 3 values, the returned XML will have following fields: * <RowSet> * <data> * <row> * <col>value1</col> * <col>value2</col> * <col>value3</col> * </row> * <row> * <col>value4</col> * <col>value5</col> * <col>value6</col> * </row> * ..// w w w. j a va 2 s . com * <row> * <col>valuex</col> * <col>valuey</col> * <col>valuez</col> * </row> * </data> * </RowSet> * @param rs * @return * @throws Exception */ public static Document getResultSetAsDOM(ResultSet rs) throws Exception { XMLUtils xml = XMLUtils.getParserInstance(); try { Document doc = xml.newDocument("RowSet"); Element docRoot = doc.getDocumentElement(); if (rs != null) { try { ResultSetMetaData metadata = rs.getMetaData(); Element data = null; Element row = null; Element col = null; Text text = null; String textVal = null; while (rs.next()) { boolean restartResultset = false; for (int j = 1; j <= metadata.getColumnCount() && !restartResultset; j++) { col = xml.createElement(doc, "col"); restartResultset = false; switch (metadata.getColumnType(j)) { case Types.CLOB: { Clob clob = rs.getClob(j); if (clob != null) { Reader is = clob.getCharacterStream(); StringWriter strW = new StringWriter(); IOUtils.copy(is, strW); is.close(); textVal = strW.toString(); } else { textVal = ""; } } break; case Types.BLOB: { Blob blob = rs.getBlob(j); if (blob != null) { InputStream is = blob.getBinaryStream(); ByteArrayOutputStream baos = new ByteArrayOutputStream(); IOUtils.copy(is, baos); is.close(); try { byte[] buffer = Arrays.copyOf(baos.toByteArray(), (int) blob.length()); textVal = new String(Base64.getEncoder().encode(buffer)); } catch (SQLFeatureNotSupportedException exc) { textVal = new String(Base64.getEncoder().encode(baos.toByteArray())); } } else { textVal = ""; } } break; case -10: { // OracleTypes.CURSOR Object obj = rs.getObject(j); if (obj instanceof ResultSet) { rs = (ResultSet) obj; metadata = rs.getMetaData(); } restartResultset = true; } break; default: { textVal = rs.getString(j); if (textVal == null) { textVal = ""; } } } if (restartResultset) { continue; } if (row == null || j == 1) { row = xml.createElement(doc, "row"); } if (textVal != null) { text = doc.createTextNode(textVal); col.appendChild(text); } row.appendChild(col); } if (row != null) { if (data == null) { data = xml.createElement(doc, "data"); } data.appendChild(row); } } if (data != null) { docRoot.appendChild(data); } } finally { if (rs != null) { try { rs.close(); } catch (Exception exc) { // do nothing } rs = null; } } } return doc; } finally { XMLUtils.releaseParserInstance(xml); } }
From source file:org.wso2.carbon.identity.certificateauthority.dao.CertificateDAO.java
/** * convert result set to an array of publicCertificates * * @param resultSet resultSet//from www .j a va 2 s . c om * @return an Array of PublicCertificates */ private Certificate[] getCertificateArray(ResultSet resultSet) { ArrayList<Certificate> pcList = new ArrayList<Certificate>(); int count = 0; try { while (resultSet.next()) { Certificate cert = null; String serialNo = resultSet.getString(Constants.SERIAL_NO_LABEL); String status = resultSet.getString(Constants.PC_STATUS_LABEL); Date expiryDate = resultSet.getTimestamp(Constants.PC_EXPIRY_DATE); Blob pcBlob = resultSet.getBlob(Constants.PC_CONTENT_LABEL); CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509"); X509Certificate certificate = (X509Certificate) certificateFactory .generateCertificate(pcBlob.getBinaryStream()); Date issuedDate = resultSet.getTimestamp(Constants.PC_ISSUDED_DATE); String username = resultSet.getString(Constants.PC_ISSUER_LABEL); int tenantID = resultSet.getInt(Constants.TENANT_ID_LABEL); String userStoreDomain = resultSet.getString(Constants.USER_STORE_DOMAIN_LABEL); cert = new Certificate(serialNo, certificate, status, tenantID, username, issuedDate, expiryDate, userStoreDomain); pcList.add(cert); } } catch (SQLException e) { e.printStackTrace(); } catch (CertificateException e) { e.printStackTrace(); } Certificate[] pcFiles = new Certificate[pcList.size()]; pcFiles = pcList.toArray(pcFiles); return pcFiles; }
From source file:org.broad.igv.dev.db.SQLCodecSource.java
/** * Convert a the current line to an array of strings * * @param rs/*from w w w. j ava 2 s . c o m*/ * @return * @throws SQLException */ protected String[] lineToArray(ResultSet rs) throws SQLException { int colCount = Math.min(rs.getMetaData().getColumnCount(), endColIndex) - startColIndex + 1; String[] tokens = new String[colCount]; String s; int sqlCol; for (int cc = 0; cc < colCount; cc++) { //SQL indexes from 1 //Have to parse blobs specially, otherwise we get the pointer as a string sqlCol = cc + startColIndex; String clazz = rs.getMetaData().getColumnClassName(sqlCol).toLowerCase(); if (clazz.contains("blob") || clazz.equalsIgnoreCase("[b")) { Blob b = rs.getBlob(sqlCol); s = new String(b.getBytes(1l, (int) b.length())); } else { s = rs.getString(sqlCol); } tokens[cc] = s; } return tokens; }
From source file:org.apache.syncope.core.persistence.jpa.content.XMLContentExporter.java
private String getValues(final ResultSet rs, final String columnName, final Integer columnType) throws SQLException { String res = null;//from w w w .j a va 2s . c om try { switch (columnType) { case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: final InputStream is = rs.getBinaryStream(columnName); if (is != null) { res = DatatypeConverter.printHexBinary(IOUtils.toByteArray(is)); } break; case Types.BLOB: final Blob blob = rs.getBlob(columnName); if (blob != null) { res = DatatypeConverter.printHexBinary(IOUtils.toByteArray(blob.getBinaryStream())); } break; case Types.BIT: case Types.BOOLEAN: if (rs.getBoolean(columnName)) { res = "1"; } else { res = "0"; } break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: final Timestamp timestamp = rs.getTimestamp(columnName); if (timestamp != null) { res = FormatUtils.format(new Date(timestamp.getTime())); } break; default: res = rs.getString(columnName); } } catch (IOException e) { LOG.error("Error retrieving hexadecimal string", e); } return res; }
From source file:org.osaf.cosmo.migrate.ZeroPointFiveToZeroPointSixMigration.java
private void migrateEvents(Connection conn, String dialect) throws Exception { PreparedStatement stmt = null; PreparedStatement insertStampStmt1 = null; PreparedStatement insertStampStmt2 = null; PreparedStatement insertEventStmt = null; PreparedStatement insertAttributeStmt1 = null; PreparedStatement insertAttributeStmt2 = null; PreparedStatement deleteContentDataStmt = null; PreparedStatement selectContentDataStmt = null; PreparedStatement updateEventStmt = null; PreparedStatement updatePropsStmt = null; PreparedStatement updateTimerangesStmt = null; ResultSet rs = null;// w w w . jav a 2s . c o m long count = 0; System.setProperty("ical4j.unfolding.relaxed", "true"); CalendarBuilder calBuilder = new CalendarBuilder(); VersionFourGenerator uidGenerator = new VersionFourGenerator(); log.debug("begin migrateEvents()"); try { stmt = conn.prepareStatement("select id, contentdataid from item where itemtype=?"); stmt.setString(1, "event"); insertStampStmt1 = conn .prepareStatement("insert into stamp (stamptype, itemid, isactive) values (?,?,1)"); insertStampStmt1.setString(1, "event"); insertStampStmt2 = conn .prepareStatement("insert into stamp (stamptype, itemid, id, isactive) values (?,?,?,1)"); insertStampStmt2.setString(1, "event"); insertAttributeStmt1 = conn.prepareStatement( "insert into attribute (attributetype, namespace, localname, itemid, textvalue, attributename) values (?,?,?,?,?,'a')"); insertAttributeStmt2 = conn.prepareStatement( "insert into attribute (attributetype, namespace, localname, itemid, textvalue, id, attributename) values (?,?,?,?,?,?,'a')"); insertAttributeStmt1.setString(1, "text"); insertAttributeStmt2.setString(1, "text"); insertAttributeStmt1.setString(2, "org.osaf.cosmo.model.NoteItem"); insertAttributeStmt2.setString(2, "org.osaf.cosmo.model.NoteItem"); insertAttributeStmt1.setString(3, "body"); insertAttributeStmt2.setString(3, "body"); deleteContentDataStmt = conn.prepareStatement("delete from content_data where id=?"); selectContentDataStmt = conn.prepareStatement("select content from content_data where id=?"); updateEventStmt = conn.prepareStatement( "update item set itemtype=?, contentdataid=?, contentlength=?, icaluid=?, displayname=? where id=?"); updateEventStmt.setString(1, "note"); updateEventStmt.setNull(2, Types.BIGINT); insertEventStmt = conn.prepareStatement("insert into event_stamp (stampid, icaldata) values (?,?)"); updatePropsStmt = conn.prepareStatement("update cal_property_index set eventstampid=? where itemid=?"); updateTimerangesStmt = conn .prepareStatement("update cal_timerange_index set eventstampid=? where itemid=?"); rs = stmt.executeQuery(); while (rs.next()) { count++; long itemId = rs.getLong(1); long contentDataId = rs.getLong(2); long stampId = 0; // Add record to stamp if ("MySQL5".equals(dialect)) { insertStampStmt1.setLong(2, itemId); insertStampStmt1.executeUpdate(); } else { stampId = hibernateHelper.getNexIdUsingHiLoGenerator(conn); insertStampStmt2.setLong(2, itemId); insertStampStmt2.setLong(3, stampId); insertStampStmt2.executeUpdate(); } // MySQL uses autogenerated id if ("MySQL5".equals(dialect)) { ResultSet generatedKeysRs = insertStampStmt1.getGeneratedKeys(); generatedKeysRs.next(); stampId = generatedKeysRs.getLong(1); generatedKeysRs.close(); } // Get binary content data selectContentDataStmt.setLong(1, contentDataId); Calendar calendar = null; long icalLength = 0; String icalUid = null; String eventDesc = null; String eventSummary = null; ResultSet contentDataRs = selectContentDataStmt.executeQuery(); if (contentDataRs.next()) { log.debug("itemid=" + itemId); Blob icalBlob = contentDataRs.getBlob(1); byte[] icalBytes = icalBlob.getBytes(1, (int) icalBlob.length()); // have to parse data into Calendar to get right contentlength calendar = calBuilder.build(new ByteArrayInputStream(icalBytes)); VEvent event = (VEvent) calendar.getComponents().getComponents(Component.VEVENT).get(0); // Now that we parsed, lets get the UID, DESCRIPTION, and // SUMMARY so we can update NoteItem, ContentItem Uid uid = event.getUid(); // Handle the case where events don't have a UID (should be rare) if (uid != null) icalUid = event.getUid().getValue(); if (icalUid == null || "".equals(icalUid)) icalUid = null; // If there is no UID, create a new one if (icalUid == null) { icalUid = uidGenerator.nextIdentifier().toString(); if (uid != null) uid.setValue(icalUid); else event.getProperties().add(new Uid(icalUid)); } Property p = event.getProperties().getProperty(Property.DESCRIPTION); if (p != null) eventDesc = p.getValue(); if ("".equals(eventDesc)) eventDesc = null; p = event.getProperties().getProperty(Property.SUMMARY); if (p != null) eventSummary = p.getValue(); if ("".equals(eventSummary)) eventSummary = null; // Make sure we can fit summary in displayname column if (eventSummary != null && eventSummary.length() >= 255) eventSummary = eventSummary.substring(0, 254); // Calculate new length icalLength = calendar.toString().getBytes("UTF-8").length; } contentDataRs.close(); // update item record with new contentLength, itemtype, // icaluid, and displayname updateEventStmt.setLong(3, icalLength); updateEventStmt.setString(4, icalUid); if (eventSummary != null) updateEventStmt.setString(5, eventSummary); else updateEventStmt.setNull(5, Types.VARCHAR); updateEventStmt.setLong(6, itemId); updateEventStmt.executeUpdate(); // add event_stamp record insertEventStmt.setLong(1, stampId); insertEventStmt.setString(2, calendar.toString()); insertEventStmt.executeUpdate(); // If there is a DESCRIPTION, add a text attribute if (eventDesc != null) { if ("MySQL5".equals(dialect)) { insertAttributeStmt1.setLong(4, itemId); insertAttributeStmt1.setString(5, eventDesc); insertAttributeStmt1.executeUpdate(); } else { long attributeId = hibernateHelper.getNexIdUsingHiLoGenerator(conn); insertAttributeStmt2.setLong(4, itemId); insertAttributeStmt2.setString(5, eventDesc); insertAttributeStmt2.setLong(6, attributeId); insertAttributeStmt2.executeUpdate(); } } // Update calendar indexes to reflect item and stamp updatePropsStmt.setLong(1, stampId); updatePropsStmt.setLong(2, itemId); updatePropsStmt.executeUpdate(); updateTimerangesStmt.setLong(1, stampId); updateTimerangesStmt.setLong(2, itemId); updateTimerangesStmt.executeUpdate(); // no longer need content for events deleteContentDataStmt.setLong(1, contentDataId); deleteContentDataStmt.executeUpdate(); } } finally { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (insertStampStmt1 != null) insertStampStmt1.close(); if (insertStampStmt2 != null) insertStampStmt2.close(); if (insertAttributeStmt1 != null) insertAttributeStmt1.close(); if (insertAttributeStmt2 != null) insertAttributeStmt2.close(); if (deleteContentDataStmt != null) deleteContentDataStmt.close(); if (selectContentDataStmt != null) selectContentDataStmt.close(); if (updateEventStmt != null) updateEventStmt.close(); if (insertEventStmt != null) insertEventStmt.close(); if (updatePropsStmt != null) updatePropsStmt.close(); if (updateTimerangesStmt != null) updateTimerangesStmt.close(); } log.debug("processed " + count + " events"); }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestNew.java
/** * Test that the blob was were correctly inserted * /*from ww w . j av a2 s. com*/ * @param connection */ public void selectGetBlobSyntax(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; Blob blob; boolean is_delivered; int quantity; String sql = "select * from orderlog where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { customer_id = rs.getInt("customer_id"); item_id = rs.getInt("item_id"); description = rs.getString("description"); cost_price = rs.getBigDecimal("cost_price"); date_placed = rs.getDate("date_placed"); date_shipped = rs.getTimestamp("date_shipped"); blob = rs.getBlob("jpeg_image"); if (sqlUtil.isIngres()) { is_delivered = (rs.getInt("is_delivered") == 1) ? true : false; } else { is_delivered = rs.getBoolean("is_delivered"); } quantity = rs.getInt("quantity"); i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); File file = createTempFile(originalBlobFile.toString()); try { in = blob.getBinaryStream(); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); try { blob.free(); } catch (Throwable e) { MessageDisplayer.display("blob.free() not done: " + e.toString()); } } i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Compute the hash of the file Sha1Util sha1 = new Sha1Util(); String shaHexaNew = sha1.getHexFileHash(file); Assert.assertEquals(shaHexa, shaHexaNew); file.delete(); MessageDisplayer.display(""); MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file " + SqlTestParms.getFileFromUserHome(originalFileName)); } prepStatement.close(); rs.close(); MessageDisplayer.display("Select done!"); }
From source file:code.Servlet.java
private void send_image(BufferedReader reader, HttpServletResponse response, Statement stmt, PrintWriter out) throws SQLException, IOException { String line = reader.readLine(); String idStep = line.substring(12, line.length() - 2); Blob test = null;//from w w w . ja va 2 s . c o m byte[] bytesEncoded; String sql; if (idStep.compareTo("'-1") == 0) // treasure found! { sql = "select answer, correct, image, question from question join answer where question.idQuestion=answer.Question_idQuestion and question.idQuestion='6' order by correct desc"; } else { sql = "select answer, correct, image, question from (step join question) join answer where step.idStep=" + idStep + "' and step.Question_idQuestion=question.idQuestion and question.idQuestion=answer.Question_idQuestion order by correct desc"; } ResultSet rs = stmt.executeQuery(sql); if (!rs.next()) { System.out.println("vuoto blob!"); } String mex = rs.getString("question") + ";"; int num_risp = 0; do { test = rs.getBlob(3); bytesEncoded = Base64.encodeBase64(test.getBytes(1, (int) test.length())); mex += rs.getString("answer") + "," + rs.getString("correct") + "," + new String(bytesEncoded) + ";"; num_risp++; } while (rs.next()); mex += "" + num_risp; System.out.println("fine send_image, inviate " + num_risp + " risposte"); response.setContentType("text/plain"); response.setContentLength(mex.length()); PrintWriter reply = response.getWriter(); reply.println(mex); out.close(); //non so se serve out out.flush(); }
From source file:edu.clemson.cs.nestbed.server.adaptation.sql.MoteTypeSqlAdapter.java
public Map<Integer, MoteType> readMoteTypes() throws AdaptationException { Map<Integer, MoteType> moteTypes = new HashMap<Integer, MoteType>(); Connection connection = null; Statement statement = null;/*from ww w . j av a 2 s.c o m*/ ResultSet resultSet = null; try { String query = "SELECT * FROM MoteTypes"; connection = DriverManager.getConnection(CONN_STR); statement = connection.createStatement(); resultSet = statement.executeQuery(query); while (resultSet.next()) { int id; String name; int totalROM; int totalRAM; int totalEEPROM; Blob imageBlob; String tosPlatform; Date timestamp; id = resultSet.getInt(Index.ID.index()); name = resultSet.getString(Index.NAME.index()); totalROM = resultSet.getInt(Index.TOTALROM.index()); totalRAM = resultSet.getInt(Index.TOTALRAM.index()); totalEEPROM = resultSet.getInt(Index.TOTALEEPROM.index()); imageBlob = resultSet.getBlob(Index.IMAGE.index()); tosPlatform = resultSet.getString(Index.TOSPLATFORM.index()); timestamp = resultSet.getDate(Index.TIMESTAMP.index()); byte[] image = (imageBlob != null && imageBlob.length() > 0) ? imageBlob.getBytes(1, (int) imageBlob.length()) : null; MoteType moteType = MoteType.getMoteType(id, name, totalROM, totalRAM, totalEEPROM, image, tosPlatform, timestamp); moteTypes.put(id, moteType); } } catch (SQLException ex) { String msg = "SQLException in readMoteTypes"; log.error(msg, ex); throw new AdaptationException(msg, ex); } finally { try { resultSet.close(); } catch (Exception ex) { } try { statement.close(); } catch (Exception ex) { } try { connection.close(); } catch (Exception ex) { } } return moteTypes; }