List of usage examples for java.sql ResultSet getBytes
byte[] getBytes(String columnLabel) throws SQLException;
ResultSet object as a byte array in the Java programming language. From source file:org.apache.drill.test.framework.Utils.java
public static String getSqlResult(ResultSet resultSet) throws SQLException { StringBuffer stringBuffer = new StringBuffer(); List columnLabels = new ArrayList<String>(); try {// w w w . j a v a 2 s .c om int columnCount = resultSet.getMetaData().getColumnCount(); for (int i = 1; i <= columnCount; i++) { columnLabels.add(resultSet.getMetaData().getColumnLabel(i)); } List<Integer> types = Lists.newArrayList(); for (int i = 1; i <= columnCount; i++) { types.add(resultSet.getMetaData().getColumnType(i)); } LOG.debug("Result set data types:"); LOG.debug(Utils.getTypesInStrings(types)); stringBuffer.append(new ColumnList(types, columnLabels).toString() + "\n"); while (resultSet.next()) { List<Object> values = Lists.newArrayList(); for (int i = 1; i <= columnCount; i++) { try { if (resultSet.getObject(i) == null) { values.add(null); continue; } if (resultSet.getMetaData().getColumnType(i) == Types.NVARCHAR) { values.add(new String(resultSet.getBytes(i), "UTF-16")); } else { values.add(new String(resultSet.getBytes(i), "UTF-8")); } } catch (Exception e) { if (resultSet.getMetaData().getColumnType(i) == Types.DATE) { values.add(resultSet.getDate(i)); } else { values.add(resultSet.getObject(i)); } } } stringBuffer.append(new ColumnList(types, values).toString() + "\n"); } } catch (IllegalArgumentException | IllegalAccessException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } finally { if (resultSet != null) { resultSet.close(); } } return stringBuffer.toString(); }
From source file:org.waarp.common.database.data.AbstractDbData.java
/** * Get one value into DbValue from ResultSet * //from w ww . j a v a 2s . co m * @param rs * @param value * @throws WaarpDatabaseSqlException */ static public void getTrueValue(ResultSet rs, DbValue value) throws WaarpDatabaseSqlException { try { switch (value.type) { case Types.VARCHAR: value.value = rs.getString(value.column); break; case Types.LONGVARCHAR: value.value = rs.getString(value.column); break; case Types.BIT: value.value = rs.getBoolean(value.column); break; case Types.TINYINT: value.value = rs.getByte(value.column); break; case Types.SMALLINT: value.value = rs.getShort(value.column); break; case Types.INTEGER: value.value = rs.getInt(value.column); break; case Types.BIGINT: value.value = rs.getLong(value.column); break; case Types.REAL: value.value = rs.getFloat(value.column); break; case Types.DOUBLE: value.value = rs.getDouble(value.column); break; case Types.VARBINARY: value.value = rs.getBytes(value.column); break; case Types.DATE: value.value = rs.getDate(value.column); break; case Types.TIMESTAMP: value.value = rs.getTimestamp(value.column); break; case Types.CLOB: value.value = rs.getClob(value.column).getCharacterStream(); break; case Types.BLOB: value.value = rs.getBlob(value.column).getBinaryStream(); break; default: throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " for " + value.column); } } catch (SQLException e) { DbSession.error(e); throw new WaarpDatabaseSqlException("Getting values in error: " + value.type + " for " + value.column, e); } }
From source file:com.threecrickets.prudence.cache.SqlCache.java
public CacheEntry fetch(String key) { Lock lock = lockSource.getReadLock(key); lock.lock();// ww w. j ava 2 s . co m try { Connection connection = connect(); if (connection == null) return null; try { String sql = "SELECT data, media_type, language, character_set, encoding, modification_date, tag, headers, expiration_date, document_modification_date FROM " + cacheTableName + " WHERE key=?"; PreparedStatement statement = connection.prepareStatement(sql); try { statement.setString(1, key); ResultSet rs = statement.executeQuery(); try { if (rs.next()) { byte[] data = rs.getBytes(1); MediaType mediaType = MediaType.valueOf(rs.getString(2)); Language language = Language.valueOf(rs.getString(3)); CharacterSet characterSet = CharacterSet.valueOf(rs.getString(4)); Encoding encoding = Encoding.valueOf(rs.getString(5)); Timestamp modificationDate = rs.getTimestamp(6); String tagValue = rs.getString(7); Tag tag = tagValue != null ? Tag.parse(tagValue) : null; String rawHeaders = rs.getString(8); Series<Header> headers = (rawHeaders != null) && (rawHeaders.length() > 0) ? deserializeHeaders(rawHeaders) : null; Timestamp expirationDate = rs.getTimestamp(9); Timestamp documentModificationDate = rs.getTimestamp(10); logger.fine("Fetched: " + key); CacheEntry entry; if (encoding != null) entry = new CacheEntry(data, mediaType, language, characterSet, encoding, headers, modificationDate, tag, expirationDate, documentModificationDate); else { try { entry = new CacheEntry(new String(data), mediaType, language, characterSet, null, headers, modificationDate, tag, expirationDate, documentModificationDate); } catch (IOException x) { throw new RuntimeException("Should never happen if data is not encoded!"); } } if (new java.util.Date().after(entry.getExpirationDate())) { lock.unlock(); try { logger.fine("Stale entry: " + key); delete(connection, key); // (Note that this also discarded our lock, // but we kept it as a local variable) } finally { lock.lock(); } return null; } return entry; } } finally { rs.close(); } } finally { statement.close(); } } finally { connection.close(); } } catch (SQLException x) { logger.log(Level.WARNING, "Could not fetch cache entry", x); } finally { lock.unlock(); } logger.fine("Did not fetch: " + key); return null; }
From source file:uk.ac.ebi.orchem.search.SimilaritySearch.java
/** * Performs a similarity search between a query molecule and the orchem fingerprint table. * * @param queryFp fingerprint of the query molecule * @param _cutOff tanimoto score below which to stop searching * @param _topN top N results after which to stop searching * @param debugYN Y or N to debug output back * @param idsOnlyYN Y or N to indicate to just return IDs of results (faster) * @param extraWhereClause option to include an extra SQL where clause refering to the base compound table * @return array of {@link uk.ac.ebi.orchem.bean.OrChemCompound compounds} * @throws Exception//w ww . j a v a 2s.c om */ private static oracle.sql.ARRAY search(BitSet queryFp, Float _cutOff, Integer _topN, String debugYN, String idsOnlyYN, String extraWhereClause) throws Exception { /* * The comment block below describes the search algorithm. From: "Bounds and Algorithms for Fast Exact Searches of Chemical Fingerprints in Linear and Sub-Linear Time" S.Joshua Swamidass and Pierre Baldi http://dx.doi.org/10.1021/ci600358f Top K Hits ---------- We can search for the top K hits by starting from the maximum (where A=B), and exploring discrete possible values of B right and left of the maximum. More precisely, for binary fingerprints, we first index the molecules in the database by their fingerprint "bit count" to enable efficient referencing of a particular bit count bin. Next, with respect to a particular query, we calculate the bound on the similarity for every bit count in the database. Then we sort these bit counts by their associated bound and iterate over the molecules in the database, in order of decreasing bound. As we iterate, we calculate the similarity between the query and the database molecule and use a heap to efficiently track the top hits. The algorithm terminates when "the lowest similarity value in the heap is greater than the bound associated with the current database bin" Algorithm 1 Top K Search Require: database of fingerprints binned by bit count Bs Ensure: hits contains top K hits which satisfy SIMILARITY( ) > T 1: hits <- MINHEAP() 2: bounds <- LIST() 3: for all B in database do //iterate over bins 4: tuple <- TUPLE(BOUND(A,B),B) 5: LISTAPPEND(bounds, tuple) 6: end for 7: QUICKSORT(bounds) //NOTE: the length of bounds is constant 8: for all bound, B in bounds do //iterate in order of decreasing bound 9: if bound < T then 10: break //threshold stopping condition 11: end if 12: if K HEAPSIZE(hits) and bound < MINSIMILARITY(hits) then 13: break //top-K stopping condition 14: end if 15: for all in database[B] do 16: S=SIMILARITY( ) 17: tuple <- TUPLE(S, ) 18: if S T then 19: continue //ignore this and continue to next 20: else if LENGTH(hits)< K then 21: HEAPPUSH(hits, tuple) 22: else if S > MINSIMILARITY(hits) then 23: HEAPPOPMIN(hits) 24: HEAPPUSH(hits,tuple) 25: end if 26: end for 27: end for 28: return hits */ boolean debugging = false; if (debugYN.toLowerCase().equals("y")) debugging = true; debug("started", debugging); /********************************************************************** * Similarity search algorithm section * * * **********************************************************************/ Comparator heapComparator = new SimHeapElementTanimComparator(); PriorityBuffer heap = null; OracleConnection conn = null; PreparedStatement pstmtFp = null; PreparedStatement pstmLookup = null; String query = " select bit_count, id, fp from orchem_fingprint_simsearch s where bit_count = ? "; float cutOff = _cutOff.floatValue(); int topN = -1; if (_topN == null) { debug("No topN breakout specified.. searching until lower bound reached", debugging); } else { topN = _topN.intValue(); debug("topN is " + topN + ", result set size limited.", debugging); } try { conn = (OracleConnection) new OracleDriver().defaultConnection(); String compoundTableName = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_TABLE, conn); String compoundTablePkColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_PK, conn); String compoundTableMolfileColumn = OrChemParameters.getParameterValue(OrChemParameters.COMPOUND_MOL, conn); if (extraWhereClause != null) { query = " select s.bit_count, s.id, s.fp from " + " orchem_fingprint_simsearch s , " + compoundTableName + " c " + " where s.bit_count = ? " + " and s.id = c." + compoundTablePkColumn + " " + " and " + extraWhereClause; debug("QUERY is " + query, debugging); } float queryBitCount = queryFp.cardinality(); byte[] queryBytes = Utils.toByteArray(queryFp, extFpSize); int queryByteArrLen = queryBytes.length; float lowBucketNum = queryBitCount - 1; float highBucketNum = queryBitCount + 1; float currBucketNum = queryBitCount; pstmtFp = conn.prepareStatement(query); pstmtFp.setFetchSize(250); ResultSet resFp = null; boolean done = false; byte[] dbByteArray = null; float tanimotoCoeff = 0f; heap = new PriorityBuffer(true, heapComparator); int bucksSearched = 0; int loopCount = 0; while (!done) { debug("bucket is " + currBucketNum, debugging); loopCount++; pstmtFp.setFloat(1, currBucketNum); bucksSearched++; resFp = pstmtFp.executeQuery(); float bound = 0f; if (currBucketNum < queryBitCount) bound = currBucketNum / queryBitCount; else bound = queryBitCount / currBucketNum; /* Algorithm step 9..11 Here we can break out because the tanimoto score is becoming to low */ if (bound < cutOff) { debug("bound < cutOff, done", debugging); done = true; } if (!done) { //Algorithm 15-26 while (resFp.next()) { dbByteArray = resFp.getBytes("fp"); tanimotoCoeff = calcTanimoto(queryBytes, queryByteArrLen, dbByteArray, queryBitCount, currBucketNum); if (tanimotoCoeff >= cutOff) { SimHeapElement elm = new SimHeapElement(); elm.setID(resFp.getString("id")); elm.setTanimotoCoeff(new Float(tanimotoCoeff)); if (heap.size() < topN || topN == -1) { heap.add(elm); debug("add elem " + elm.getID(), debugging); } else if (tanimotoCoeff > ((SimHeapElement) (heap.get())).getTanimotoCoeff() .floatValue()) { heap.remove(); heap.add(elm); debug("remove + add elem " + elm.getID(), debugging); } } } resFp.close(); /* Algorithm 12-14: * When top N hits is reached, and the lowest score of the * hits is greater than the current bucket bound, stop. * If not, the next bucket may contain a better score, so go on. */ if (topN != -1 && heap.size() >= topN && ((SimHeapElement) (heap.get())).getTanimotoCoeff().floatValue() > bound) { done = true; debug("topN reached, done", debugging); } else { // calculate new currBucket float up = queryBitCount / highBucketNum; float down = lowBucketNum / queryBitCount; if (up > down) { currBucketNum = highBucketNum; highBucketNum++; } else { currBucketNum = lowBucketNum; lowBucketNum--; } if (lowBucketNum < 1 && highBucketNum > extFpSize) done = true; } } } debug("searched bit_count buckets: " + loopCount, debugging); /******************************************************************** * Search completed. * * * * Next section is just looking up the compounds by ID and * * returning the results, sorted by Tanimoto coefficient * * * *******************************************************************/ String lookupCompoundQuery = " select " + compoundTableMolfileColumn + " from " + " " + compoundTableName + " where " + " " + compoundTablePkColumn + " =?"; pstmLookup = conn.prepareStatement(lookupCompoundQuery); List compounds = new ArrayList(); while (heap.size() != 0) { SimHeapElement bElm = (SimHeapElement) heap.remove(); if (idsOnlyYN.equals("N")) { // return structure to user pstmLookup.setString(1, bElm.getID()); ResultSet resLookup = pstmLookup.executeQuery(); if (resLookup.next()) { OrChemCompound c = new OrChemCompound(); c.setId(bElm.getID()); c.setScore(bElm.getTanimotoCoeff().floatValue()); c.setMolFileClob(resLookup.getClob(compoundTableMolfileColumn)); compounds.add(c); } resLookup.close(); } else { // only return ID and score to user OrChemCompound c = new OrChemCompound(); c.setId(bElm.getID()); c.setScore(bElm.getTanimotoCoeff().floatValue()); compounds.add(c); } } pstmLookup.close(); long befSort = System.currentTimeMillis(); Collections.sort(compounds, new OrChemCompoundTanimComparator()); debug("sorting time (ms) " + (System.currentTimeMillis() - befSort), debugging); OrChemCompound[] output = new OrChemCompound[compounds.size()]; for (int i = 0; i < compounds.size(); i++) { output[i] = (OrChemCompound) (compounds.get(i)); } ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("ORCHEM_COMPOUND_LIST", conn); debug("#compounds in result list : " + compounds.size(), debugging); debug("ended", debugging); return new ARRAY(arrayDescriptor, conn, output); } catch (Exception ex) { ex.printStackTrace(); throw (ex); } finally { if (pstmLookup != null) pstmLookup.close(); if (pstmtFp != null) pstmtFp.close(); if (conn != null) conn.close(); } }
From source file:biblivre3.administration.ReportsDAO.java
public BibliographyReportDto getBibliographyReportData(String authorName, Integer[] recordIdArray) { BibliographyReportDto dto = new BibliographyReportDto(); dto.setAuthorName(authorName);//from w ww . jav a 2 s . c o m Connection con = null; try { con = getDataSource().getConnection(); String sql = " select record from cataloging_biblio where record_serial in ("; for (int i = 0; i < recordIdArray.length; i++) { sql += "?, "; } sql = sql.substring(0, sql.length() - 2); sql += ") order by record_serial asc "; PreparedStatement st = con.prepareStatement(sql); for (int i = 0; i < recordIdArray.length; i++) { st.setInt(i + 1, recordIdArray[i]); } final ResultSet rs = st.executeQuery(); List<String[]> data = new ArrayList<String[]>(); while (rs.next()) { String iso2709 = new String(rs.getBytes("record"), "UTF-8"); Record record = MarcUtils.iso2709ToRecord(iso2709); String[] lending = new String[5]; lending[0] = Indexer.listOneTitle(record); lending[1] = Indexer.listEdition(record); String[] publication = Indexer.listPublicationFull(record); lending[2] = publication[1]; lending[3] = publication[2]; String[] location = Indexer.listLocation(record); lending[4] = StringUtils.join(location, "\n").replaceAll("\\n+", "\n"); data.add(lending); } dto.setData(data); } catch (Exception e) { log.error(e.getMessage(), e); throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION"); } finally { closeConnection(con); } return dto; }
From source file:fr.dyade.aaa.util.MySqlDBRepository.java
/** * Loads the byte array.//w ww . j a va2 s . co m * * @return The loaded bytes array. */ public byte[] load(String dirName, String name) throws IOException { if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "load called"); String fname = null; if (dirName == null) { fname = name; } else { fname = new StringBuffer(dirName).append('/').append(name).toString(); } try { // Creating a statement lets us issue commands against the connection. Statement s = conn.createStatement(); // ResultSet rs = s.executeQuery("SELECT content FROM JoramDB WHERE name='" + fname + "'"); if (!rs.next()) { throw new FileNotFoundException("Cannot find object in JoramDB " + ("serverCounter".equals(fname) ? "[KNOWN PROBLEM] " : "") + fname); } byte[] content = rs.getBytes(1); rs.close(); s.close(); if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "load, after database call"); nbloaded += 1; return content; } catch (SQLException sqle) { if (sqle instanceof com.mysql.jdbc.CommunicationsException && !reconnectLoop) { logger.log(BasicLevel.WARN, "Database reconnection problem at load, Reconnecting"); reconnection(); reconnectLoop = true; byte[] content = load(dirName, name); reconnectLoop = false; return content; } if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at load"); logger.log(BasicLevel.WARN, "load, problem load " + name); sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { String exceptionString = e.toString(); if (exceptionString.indexOf("KNOWN PROBLEM") == -1) { logger.log(BasicLevel.WARN, "load, problem load " + name + " in e with " + e.getMessage()); e.printStackTrace(); } throw new FileNotFoundException(e.getMessage()); } }
From source file:org.sakaiproject.mailarchive.impl.conversion.ExtractXMLToColumns.java
public Object getSource(String id, ResultSet rs) throws SQLException { ResultSetMetaData metadata = rs.getMetaData(); String rv = null;/* ww w. j a v a 2 s. c o m*/ switch (metadata.getColumnType(1)) { case Types.BLOB: Blob blob = rs.getBlob(1); if (blob != null) { rv = new String(blob.getBytes(1L, (int) blob.length())); } break; case Types.CLOB: Clob clob = rs.getClob(1); if (clob != null) { rv = clob.getSubString(1L, (int) clob.length()); } break; case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: byte[] bytes = rs.getBytes(1); if (bytes != null) { rv = new String(bytes); } break; } // System.out.println("getSource(" + id + ") \n" + rv + "\n"); return rv; }
From source file:biblivre3.administration.ReportsDAO.java
public AssetHoldingDto getAssetHoldingReportData() { AssetHoldingDto dto = new AssetHoldingDto(); Connection con = null;/*from ww w. j a va2s .c o m*/ try { con = getDataSource().getConnection(); String sql = " SELECT H.asset_holding, R.record FROM cataloging_holdings H INNER JOIN cataloging_biblio R " + " ON R.record_serial = H.record_serial WHERE H.database = 0 " + " ORDER BY H.asset_holding "; final PreparedStatement pst = con.prepareStatement(sql); pst.setFetchSize(100); final ResultSet rs = pst.executeQuery(); List<String[]> dataList = new ArrayList<String[]>(); while (rs.next()) { Record record = MarcUtils.iso2709ToRecord(rs.getBytes("record")); String assetHolding = rs.getString("asset_holding"); String[] data = new String[5]; data[0] = assetHolding; data[1] = Indexer.listPrimaryAuthor(record); data[2] = Indexer.listOneTitle(record); data[3] = Indexer.listEdition(record); data[4] = Indexer.listYearOfPublication(record); dataList.add(data); } dto.setData(dataList); } catch (Exception e) { log.error(e.getMessage(), e); throw new ExceptionUser("ERROR_REPORT_DAO_EXCEPTION"); } finally { closeConnection(con); } return dto; }
From source file:org.sakaiproject.content.impl.serialize.impl.conversion.Type1BlobCollectionConversionHandler.java
public Object getSource(String id, ResultSet rs) throws SQLException { ResultSetMetaData metadata = rs.getMetaData(); String rv = null;//from ww w. jav a 2s . c o m switch (metadata.getColumnType(1)) { case Types.BLOB: Blob blob = rs.getBlob(1); if (blob != null) { rv = new String(blob.getBytes(1L, (int) blob.length())); } break; case Types.CLOB: Clob clob = rs.getClob(1); if (clob != null) { rv = clob.getSubString(1L, (int) clob.length()); } break; case Types.CHAR: case Types.LONGVARCHAR: case Types.VARCHAR: case Types.BINARY: case Types.VARBINARY: case Types.LONGVARBINARY: byte[] bytes = rs.getBytes(1); if (bytes != null) { rv = new String(bytes); } break; } //System.out.println("getSource(" + id + ") \n" + rv + "\n"); return rv; }
From source file:org.wso2.carbon.certificate.mgt.core.dao.impl.AbstractCertificateDAOImpl.java
@Override public CertificateResponse retrieveCertificate(String serialNumber) throws CertificateManagementDAOException { Connection conn;//from w w w . jav a 2 s .c o m PreparedStatement stmt = null; ResultSet resultSet = null; CertificateResponse certificateResponse = null; int tenantId = PrivilegedCarbonContext.getThreadLocalCarbonContext().getTenantId(); try { conn = this.getConnection(); String query = "SELECT CERTIFICATE, SERIAL_NUMBER, TENANT_ID, USERNAME FROM" + " DM_DEVICE_CERTIFICATE WHERE SERIAL_NUMBER = ? AND TENANT_ID = ? "; stmt = conn.prepareStatement(query); stmt.setString(1, serialNumber); stmt.setInt(2, tenantId); resultSet = stmt.executeQuery(); if (resultSet.next()) { certificateResponse = new CertificateResponse(); byte[] certificateBytes = resultSet.getBytes("CERTIFICATE"); certificateResponse.setCertificate(certificateBytes); certificateResponse.setSerialNumber(resultSet.getString("SERIAL_NUMBER")); certificateResponse.setTenantId(resultSet.getInt("TENANT_ID")); certificateResponse.setUsername(resultSet.getString("USERNAME")); CertificateGenerator.extractCertificateDetails(certificateBytes, certificateResponse); } } catch (SQLException e) { String errorMsg = "Unable to get the read the certificate with serial" + serialNumber; log.error(errorMsg, e); throw new CertificateManagementDAOException(errorMsg, e); } finally { CertificateManagementDAOUtil.cleanupResources(stmt, resultSet); } return certificateResponse; }