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:com.runwaysdk.dataaccess.database.general.Oracle.java
/** * This is a special method used to update the baseClass attribute of MdType * and it is used only within the TransactionManagement aspect, hence it takes * a JDBC connection object as a parameter. * @param mdTypeId//www . j a v a2s .com * @param table * @param classColumnName * @param classBytes * @param sourceColumnName * @param source * @param conn */ @Override public int updateClassAndSource(String mdTypeId, String updateTable, String classColumnName, byte[] classBytes, String sourceColumnName, String source, Connection conn) { PreparedStatement prepared = null; Statement statement = null; ResultSet resultSet = null; int written = 0; try { // clear the blob this.truncateBlob(updateTable, classColumnName, mdTypeId, 0, conn); // get the blob statement = conn.createStatement(); String select = "SELECT " + classColumnName + " FROM " + updateTable + " WHERE " + EntityDAOIF.ID_COLUMN + " = '" + mdTypeId + "' FOR UPDATE"; String update = "UPDATE " + updateTable + " SET " + classColumnName + " = " + "?, " + sourceColumnName + " = ? WHERE " + EntityDAOIF.ID_COLUMN + " = '" + mdTypeId + "'"; resultSet = statement.executeQuery(select); boolean resultSetFound = resultSet.next(); if (!resultSetFound) { return 0; } Blob classBlob = resultSet.getBlob(classColumnName); prepared = conn.prepareStatement(update); written = addBlobToStatement(prepared, 1, classBlob, classBytes); prepared.setString(2, source); prepared.executeUpdate(); } catch (SQLException e) { this.throwDatabaseException(e); } finally { try { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (prepared != null) prepared.close(); } catch (SQLException e) { this.throwDatabaseException(e); } } return written; }
From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java
/** * Method for getting the list of applications of a tenant from database with minimal information. * * @param dbConnection database connection * @param tenantId tenant id/*from ww w . j a v a 2 s. c om*/ * @param cloudType cloud type * @return list of applications * @throws AppCloudException */ public List<Application> getAllApplicationsList(Connection dbConnection, int tenantId, String cloudType) throws AppCloudException { PreparedStatement preparedStatement = null; List<Application> applications = new ArrayList<>(); ResultSet resultSet = null; try { preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.GET_ALL_APPLICATIONS_LIST); preparedStatement.setInt(1, tenantId); preparedStatement.setString(2, cloudType); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { Application application = new Application(); application.setApplicationName(resultSet.getString(SQLQueryConstants.APPLICATION_NAME)); application.setApplicationType(resultSet.getString(SQLQueryConstants.APPLICATION_TYPE_NAME)); application.setHashId(resultSet.getString(SQLQueryConstants.HASH_ID)); application.setIcon(resultSet.getBlob(SQLQueryConstants.ICON)); applications.add(application); } } catch (SQLException e) { String msg = "Error while retrieving application list from database in tenant : " + tenantId + " and cloud : " + cloudType; throw new AppCloudException(msg, e); } finally { DBUtil.closeResultSet(resultSet); DBUtil.closePreparedStatement(preparedStatement); } return applications; }
From source file:org.wso2.appcloud.core.dao.ApplicationDAO.java
/** * Method for getting application from database using application hash id. * * @param dbConnection database connection * @param applicationHashId application hash id * @return application object/*from ww w. j a v a2 s.co m*/ * @throws AppCloudException */ public Application getApplicationByHashId(Connection dbConnection, String applicationHashId, int tenantId) throws AppCloudException { PreparedStatement preparedStatement = null; Application application = new Application(); ResultSet resultSet = null; try { preparedStatement = dbConnection.prepareStatement(SQLQueryConstants.GET_APPLICATION_BY_HASH_ID); preparedStatement.setString(1, applicationHashId); preparedStatement.setInt(2, tenantId); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { application.setApplicationName(resultSet.getString(SQLQueryConstants.NAME)); application.setHashId(applicationHashId); application.setDescription(resultSet.getString(SQLQueryConstants.DESCRIPTION)); application.setDefaultVersion(resultSet.getString(SQLQueryConstants.DEFAULT_VERSION)); application.setApplicationType(resultSet.getString(SQLQueryConstants.APPLICATION_TYPE_NAME)); application.setIcon(resultSet.getBlob(SQLQueryConstants.ICON)); application.setCustomDomain(resultSet.getString(SQLQueryConstants.CUSTOM_DOMAIN)); application.setVersions(getAllVersionsOfApplication(dbConnection, applicationHashId, tenantId)); } } catch (SQLException e) { String msg = "Error while retrieving application detail for application with hash id : " + applicationHashId + " in tenant : " + tenantId; throw new AppCloudException(msg, e); } finally { DBUtil.closeResultSet(resultSet); DBUtil.closePreparedStatement(preparedStatement); } return application; }
From source file:com.runwaysdk.dataaccess.database.general.Oracle.java
/** * This is a special method used to update the generated server, common, and client classes for an MdType. * This method is used only within the TransactionManagement aspect, hence it takes a JDBC connection object as a parameter. * It is up to the client to close the connection object. * * @param table//w w w .j av a2 s . c o m * @param updateTable * @param serverClassesColumnName * @param serverClassesBytes * @param commonClassesColumnName * @param commonClassesBytes * @param clientClassesColumnName * @param clientClassesBytes * @param conn */ @Override public int updateMdFacadeGeneratedClasses(String mdFacadeId, String table, String serverClassesColumnName, byte[] serverClassesBytes, String commonClassesColumnName, byte[] commonClassesBytes, String clientClassesColumnName, byte[] clientClassesBytes, Connection conn) { PreparedStatement prepared = null; Statement statement = null; ResultSet resultSet = null; int written = 0; try { // clear the blob this.truncateBlob(table, serverClassesColumnName, mdFacadeId, 0, conn); this.truncateBlob(table, commonClassesColumnName, mdFacadeId, 0, conn); this.truncateBlob(table, clientClassesColumnName, mdFacadeId, 0, conn); // get the blob statement = conn.createStatement(); String select = "SELECT " + serverClassesColumnName + ", " + commonClassesColumnName + ", " + clientClassesColumnName + " FROM " + table + " WHERE " + EntityDAOIF.ID_COLUMN + " = '" + mdFacadeId + "' FOR UPDATE"; String update = "UPDATE " + table + " SET " + serverClassesColumnName + " = " + "?, " + commonClassesColumnName + " = ?, " + clientClassesColumnName + " = ? " + " WHERE " + EntityDAOIF.ID_COLUMN + " = '" + mdFacadeId + "'"; resultSet = statement.executeQuery(select); boolean resultSetFound = resultSet.next(); if (!resultSetFound) { return 0; } Blob serverClassesBlob = resultSet.getBlob(serverClassesColumnName); Blob commonClassesBlob = resultSet.getBlob(commonClassesColumnName); Blob clientClassesBlob = resultSet.getBlob(clientClassesColumnName); prepared = conn.prepareStatement(update); written += addBlobToStatement(prepared, 1, serverClassesBlob, serverClassesBytes); written += addBlobToStatement(prepared, 2, commonClassesBlob, commonClassesBytes); written += addBlobToStatement(prepared, 3, clientClassesBlob, clientClassesBytes); prepared.executeUpdate(); } catch (SQLException e) { this.throwDatabaseException(e); } finally { try { if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); if (prepared != null) prepared.close(); } catch (SQLException e) { this.throwDatabaseException(e); } } return written; }
From source file:org.bytesoft.openjtcc.supports.logger.DbTransactionLoggerImpl.java
private Map<XidImpl, CompensableArchive> loadNativeService(Connection connection) { Map<XidImpl, CompensableArchive> serviceMap = new HashMap<XidImpl, CompensableArchive>(); PreparedStatement stmt = null; ResultSet rs = null; try {/*from ww w .j a va2 s.c om*/ StringBuilder ber = new StringBuilder(); ber.append("select s.global_tx_id, s.branch_qualifier, s.coordinator, s.bean_name, s.variable"); ber.append(", s.try_committed, s.confirmed, s.cancelled, s.committed, s.rolledback "); ber.append("from tcc_compensable s "); ber.append("left join tcc_transaction t on ("); ber.append(" t.application = s.application "); ber.append(" and t.endpoint = s.endpoint "); ber.append(" and t.global_tx_id = s.global_tx_id "); ber.append(") where s.application = ? and s.endpoint = ? and t.deleted = ?"); stmt = connection.prepareStatement(ber.toString()); stmt.setString(1, this.instanceKey.getApplication()); stmt.setString(2, this.instanceKey.getEndpoint()); stmt.setBoolean(3, false); rs = stmt.executeQuery(); while (rs.next()) { String globalTransactionId = rs.getString("global_tx_id"); String branchQualifier = rs.getString("branch_qualifier"); boolean coordinator = rs.getBoolean("coordinator"); String beanName = rs.getString("bean_name"); CompensableArchive holder = new CompensableArchive(); holder.launchSvc = coordinator; byte[] globalBytes = ByteUtils.stringToByteArray(globalTransactionId); XidImpl globalXid = this.xidFactory.createGlobalXid(globalBytes); if (coordinator) { holder.branchXid = globalXid; } else { byte[] branchBytes = ByteUtils.stringToByteArray(branchQualifier); XidImpl branchXid = this.xidFactory.createBranchXid(globalXid, branchBytes); holder.branchXid = branchXid; } boolean tryCommitted = rs.getBoolean("try_committed"); boolean confirmed = rs.getBoolean("confirmed"); boolean cancelled = rs.getBoolean("cancelled"); boolean committed = rs.getBoolean("committed"); boolean rolledback = rs.getBoolean("rolledback"); holder.tryCommitted = tryCommitted; holder.confirmed = confirmed; holder.cancelled = cancelled; holder.committed = committed; holder.rolledback = rolledback; CompensableInfo info = new CompensableInfo(); info.setIdentifier(beanName); Compensable<Serializable> service = this.compensableMarshaller.unmarshallCompensable(info); holder.service = service; Blob blob = rs.getBlob("variable"); Serializable variable = null; if (blob != null) { InputStream input = blob.getBinaryStream(); if (input != null) { byte[] bytes = this.streamToByteArray(input); try { variable = (Serializable) this.serializer.deserialize(bytes); } catch (IOException e) { // ignore } } } holder.variable = variable; serviceMap.put(globalXid, holder); } } catch (Exception ex) { ex.printStackTrace(); } finally { closeResultSet(rs); closeStatement(stmt); } return serviceMap; }
From source file:org.quartz.impl.jdbcjobstore.StdJDBCDelegate.java
/** * <p>/*from w w w.j av a2 s . c o m*/ * This method should be overridden by any delegate subclasses that need * special handling for BLOBs for job details. The default implementation * uses standard JDBC <code>java.sql.Blob</code> operations. * </p> * * @param rs * the result set, already queued to the correct row * @param colName * the column name for the BLOB * @return the deserialized Object from the ResultSet BLOB * @throws ClassNotFoundException * if a class found during deserialization cannot be found * @throws IOException * if deserialization causes an error */ protected Object getJobDetailFromBlob(ResultSet rs, String colName) throws ClassNotFoundException, IOException, SQLException { if (canUseProperties()) { Blob blobLocator = rs.getBlob(colName); if (blobLocator != null) { InputStream binaryInput = blobLocator.getBinaryStream(); return binaryInput; } else { return null; } } return getObjectFromBlob(rs, colName); }
From source file:org.quartz.impl.jdbcjobstore.StdJDBCDelegate.java
/** * <p>//from w ww . ja v a 2 s. c o m * This method should be overridden by any delegate subclasses that need * special handling for BLOBs. The default implementation uses standard * JDBC <code>java.sql.Blob</code> operations. * </p> * * @param rs * the result set, already queued to the correct row * @param colName * the column name for the BLOB * @return the deserialized Object from the ResultSet BLOB * @throws ClassNotFoundException * if a class found during deserialization cannot be found * @throws IOException * if deserialization causes an error */ protected Object getObjectFromBlob(ResultSet rs, String colName) throws ClassNotFoundException, IOException, SQLException { Object obj = null; Blob blobLocator = rs.getBlob(colName); if (blobLocator != null && blobLocator.length() != 0) { InputStream binaryInput = blobLocator.getBinaryStream(); if (null != binaryInput) { if (binaryInput instanceof ByteArrayInputStream && ((ByteArrayInputStream) binaryInput).available() == 0) { //do nothing } else { ObjectInputStream in = new ObjectInputStream(binaryInput); try { obj = in.readObject(); } finally { in.close(); } } } } return obj; }
From source file:org.rhq.enterprise.server.content.ContentSourceManagerBean.java
@SuppressWarnings("unchecked") private long outputPackageVersionBitsRangeHelper(int resourceId, PackageDetailsKey packageDetailsKey, OutputStream outputStream, long startByte, long endByte, int packageVersionId) { // TODO: Should we make sure the resource is subscribed/allowed to receive the the package version? // Or should we not bother to perform this check? if the caller knows the PV ID, it // probably already got it through its repos Query query = entityManager/*ww w. j a v a2s. c o m*/ .createNamedQuery(PackageBits.QUERY_PACKAGE_BITS_LOADED_STATUS_PACKAGE_VERSION_ID); query.setParameter("id", packageVersionId); LoadedPackageBitsComposite composite = (LoadedPackageBitsComposite) query.getSingleResult(); boolean packageBitsAreAvailable = composite.isPackageBitsAvailable(); if (packageBitsAreAvailable) { // it says the package bits are available, but if its stored on the filesystem, we should // make sure no one deleted the file. If the file is deleted, let's simply download it again. if (!composite.isPackageBitsInDatabase()) { try { File bitsFile = getPackageBitsLocalFileAndCreateParentDir(composite.getPackageVersionId(), composite.getFileName()); if (!bitsFile.exists()) { log.warn("Package version [" + packageDetailsKey + "] has had its bits file [" + bitsFile + "] deleted. Will attempt to download it again."); packageBitsAreAvailable = false; } } catch (Exception e) { throw new RuntimeException("Package version [" + packageDetailsKey + "] has had its bits file deleted but cannot download it again.", e); } } } PackageVersionContentSource pvcs = null; // will be non-null only if package bits were not originally available if (!packageBitsAreAvailable) { if (resourceId == -1) { throw new IllegalStateException("Package bits must be inserted prior to the agent asking for them " + "during a cotent-based resource creation"); } // if we got here, the package bits have not been downloaded yet. This eliminates the // possibility that the package version were directly uploaded by a user // or auto-discovered by a resource and attached to a repo. So, that leaves // the only possibility - the package version comes from a content source and therefore has // a PackageVersionContentSource mapping. Let's find that mapping. Query q2 = entityManager .createNamedQuery(PackageVersionContentSource.QUERY_FIND_BY_PKG_VER_ID_AND_RES_ID); q2.setParameter("resourceId", resourceId); q2.setParameter("packageVersionId", packageVersionId); List<PackageVersionContentSource> pvcss = q2.getResultList(); // Note that its possible more than one content source can deliver a PV - if a resource is subscribed // to repo(s) that contain multiple content sources that can deliver a PV, we just take // the first one we find. if (pvcss.size() == 0) { throw new RuntimeException("Resource [" + resourceId + "] cannot access package version [" + packageDetailsKey + "] - no content source exists to deliver it"); } pvcs = pvcss.get(0); // Make it a true EJB call so we suspend our tx and get a new tx. // This way, we start with a fresh tx timeout when downloading and this // won't affect the time we are in in this method's tx (I hope that's how it works). // This is because this method itself may take a long time to send the data to the output stream // and we don't want out tx timing out due to the time it takes downloading. PackageBits bits = null; bits = contentSourceManager.downloadPackageBits(subjectManager.getOverlord(), pvcs); if (bits != null) { // rerun the query just to make sure we really downloaded it successfully query.setParameter("id", pvcs.getPackageVersionContentSourcePK().getPackageVersion().getId()); composite = (LoadedPackageBitsComposite) query.getSingleResult(); if (!composite.isPackageBitsAvailable()) { throw new RuntimeException("Failed to download package bits [" + packageDetailsKey + "] for resource [" + resourceId + "]"); } } else { // package bits are not loaded and never will be loaded due to content source's download mode == NEVER composite = null; } } Connection conn = null; PreparedStatement ps = null; ResultSet results = null; InputStream bitsStream = null; try { if (composite == null) { // this is DownloadMode.NEVER and we are really in pass-through mode, stream directly from adapter ContentServerPluginContainer pc = ContentManagerHelper.getPluginContainer(); ContentProviderManager adapterMgr = pc.getAdapterManager(); int contentSourceId = pvcs.getPackageVersionContentSourcePK().getContentSource().getId(); bitsStream = adapterMgr.loadPackageBits(contentSourceId, pvcs.getLocation()); } else { if (composite.isPackageBitsInDatabase()) { // this is DownloadMode.DATABASE - put the bits in the database conn = dataSource.getConnection(); ps = conn.prepareStatement("SELECT BITS FROM " + PackageBits.TABLE_NAME + " WHERE ID = ?"); ps.setInt(1, composite.getPackageBitsId()); results = ps.executeQuery(); results.next(); Blob blob = results.getBlob(1); long bytesRetrieved = 0L; if (endByte < 0L) { if (startByte == 0L) { bytesRetrieved = StreamUtil.copy(blob.getBinaryStream(), outputStream, false); } } else { long length = (endByte - startByte) + 1; //InputStream stream = blob.getBinaryStream(startByte, length); // JDK 6 api InputStream stream = blob.getBinaryStream(); bytesRetrieved = StreamUtil.copy(stream, outputStream, startByte, length); } log.debug("Retrieved and sent [" + bytesRetrieved + "] bytes for [" + packageDetailsKey + "]"); ps.close(); conn.close(); return bytesRetrieved; } else { // this is DownloadMode.FILESYSTEM - put the bits on the filesystem File bitsFile = getPackageBitsLocalFileAndCreateParentDir(composite.getPackageVersionId(), composite.getFileName()); if (!bitsFile.exists()) { throw new RuntimeException( "Package bits at [" + bitsFile + "] are missing for [" + packageDetailsKey + "]"); } bitsStream = new FileInputStream(bitsFile); } } // the magic happens here - outputStream is probably a remote stream down to the agent long bytesRetrieved; if (endByte < 0L) { if (startByte > 0L) { bitsStream.skip(startByte); } bytesRetrieved = StreamUtil.copy(bitsStream, outputStream, false); } else { BufferedInputStream bis = new BufferedInputStream(bitsStream); long length = (endByte - startByte) + 1; bytesRetrieved = StreamUtil.copy(bis, outputStream, startByte, length); } // close our stream but leave the output stream open try { bitsStream.close(); } catch (Exception closeError) { log.warn("Failed to close the bits stream", closeError); } bitsStream = null; log.debug("Retrieved and sent [" + bytesRetrieved + "] bytes for [" + packageDetailsKey + "]"); return bytesRetrieved; } catch (SQLException sql) { throw new RuntimeException( "Did not download the package bits to the DB for [" + packageDetailsKey + "]", sql); } catch (Exception e) { throw new RuntimeException("Could not stream package bits for [" + packageDetailsKey + "]", e); } finally { if (bitsStream != null) { try { bitsStream.close(); } catch (IOException e) { log.warn("Failed to close bits stream for: " + packageDetailsKey); } } if (results != null) { try { results.close(); } catch (SQLException e) { log.warn("Failed to close result set from jdbc blob query for: " + packageDetailsKey); } } if (ps != null) { try { ps.close(); } catch (SQLException e) { log.warn("Failed to close prepared statement from jdbc blob query for: " + packageDetailsKey); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { log.warn("Failed to close prepared statement from jdbc blob query for: " + packageDetailsKey); } } } }
From source file:com.nway.spring.jdbc.bean.AsmBeanProcessor.java
private Object processColumn(ResultSet rs, int index, Class<?> propType, String writer, String processorName, String beanName, MethodVisitor mv) throws SQLException { if (propType.equals(String.class)) { visitMethod(mv, index, beanName, "Ljava/lang/String;", "getString", writer); return rs.getString(index); } else if (propType.equals(Integer.TYPE)) { visitMethod(mv, index, beanName, "I", "getInt", writer); return rs.getInt(index); } else if (propType.equals(Integer.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_INTEGER, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Integer.class); } else if (propType.equals(Long.TYPE)) { visitMethod(mv, index, beanName, "J", "getLong", writer); return rs.getLong(index); } else if (propType.equals(Long.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_LONG, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Long.class); } else if (propType.equals(java.sql.Date.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Date;", "getDate", writer); return rs.getDate(index); } else if (propType.equals(java.util.Date.class)) { visitMethodCast(mv, index, beanName, PROPERTY_TYPE_DATE, "java/util/Date", writer); return rs.getTimestamp(index); } else if (propType.equals(Timestamp.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Timestamp;", "getTimestamp", writer); return rs.getTimestamp(index); } else if (propType.equals(Time.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Time;", "getTime", writer); return rs.getTime(index); } else if (propType.equals(Double.TYPE)) { visitMethod(mv, index, beanName, "D", "getDouble", writer); return rs.getDouble(index); } else if (propType.equals(Double.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_DOUBLE, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Double.class); } else if (propType.equals(Float.TYPE)) { visitMethod(mv, index, beanName, "F", "getFloat", writer); return rs.getFloat(index); } else if (propType.equals(Float.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_FLOAT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Float.class); } else if (propType.equals(Boolean.TYPE)) { visitMethod(mv, index, beanName, "Z", "getBoolean", writer); return rs.getBoolean(index); } else if (propType.equals(Boolean.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BOOLEAN, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Boolean.class); } else if (propType.equals(Clob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Clob;", "getClob", writer); return rs.getClob(index); } else if (propType.equals(Blob.class)) { visitMethod(mv, index, beanName, "Ljava/sql/Blob;", "getBlob", writer); return rs.getBlob(index); } else if (propType.equals(byte[].class)) { visitMethod(mv, index, beanName, "[B", "getBytes", writer); return rs.getBytes(index); } else if (propType.equals(Short.TYPE)) { visitMethod(mv, index, beanName, "S", "getShort", writer); return rs.getShort(index); } else if (propType.equals(Short.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_SHORT, writer, processorName); return JdbcUtils.getResultSetValue(rs, index, Short.class); } else if (propType.equals(Byte.TYPE)) { visitMethod(mv, index, beanName, "B", "getByte", writer); return rs.getByte(index); } else if (propType.equals(Byte.class)) { visitMethodWrap(mv, index, beanName, PROPERTY_TYPE_BYTE, writer, processorName); return rs.getByte(index); } else {/* w ww.j av a2 s. c o m*/ visitMethodCast(mv, index, beanName, PROPERTY_TYPE_OTHER, propType.getName().replace('.', '/'), writer); return rs.getObject(index); } }
From source file:ubic.gemma.core.externalDb.GoldenPathSequenceAnalysis.java
/** * Generic method to retrieve Genes from the GoldenPath database. The query given must have the appropriate form. * * @param starti start/*w w w . ja v a 2 s . c o m*/ * @param endi end * @param chromosome chromosome * @param query query * @return List of GeneProducts. This is a collection of transient instances, not from Gemma's database. */ private Collection<GeneProduct> findGenesByQuery(Long starti, Long endi, final String chromosome, String strand, String query) { // Cases: // 1. gene is contained within the region: txStart > start & txEnd < end; // 2. region is contained within the gene: txStart < start & txEnd > end; // 3. region overlaps start of gene: txStart > start & txStart < end. // 4. region overlaps end of gene: txEnd > start & txEnd < end // Object[] params; if (strand != null) { params = new Object[] { starti, endi, starti, endi, starti, endi, starti, endi, chromosome, strand }; } else { params = new Object[] { starti, endi, starti, endi, starti, endi, starti, endi, chromosome }; } return this.getJdbcTemplate().query(query, params, new ResultSetExtractor<Collection<GeneProduct>>() { @Override public Collection<GeneProduct> extractData(ResultSet rs) throws SQLException, DataAccessException { Collection<GeneProduct> r = new HashSet<>(); while (rs.next()) { GeneProduct product = GeneProduct.Factory.newInstance(); String name = rs.getString(1); /* * This happens for a very few cases in kgXref, where the gene is 'abParts'. We have to skip these. */ if (StringUtils.isBlank(name)) { continue; } /* * The name is our database identifier (either genbank or ensembl) */ DatabaseEntry accession = DatabaseEntry.Factory.newInstance(); accession.setAccession(name); if (name.startsWith("ENST")) { accession.setExternalDatabase(NcbiGeneConverter.getEnsembl()); } else { accession.setExternalDatabase(NcbiGeneConverter.getGenbank()); } product.getAccessions().add(accession); Gene gene = Gene.Factory.newInstance(); gene.setOfficialSymbol(rs.getString(2)); gene.setName(gene.getOfficialSymbol()); Taxon taxon = GoldenPathSequenceAnalysis.this.getTaxon(); assert taxon != null; gene.setTaxon(taxon); PhysicalLocation pl = PhysicalLocation.Factory.newInstance(); pl.setNucleotide(rs.getLong(3)); pl.setNucleotideLength(rs.getInt(4) - rs.getInt(3)); pl.setStrand(rs.getString(5)); pl.setBin(SequenceBinUtils.binFromRange((int) rs.getLong(3), rs.getInt(4))); PhysicalLocation genePl = PhysicalLocation.Factory.newInstance(); genePl.setStrand(pl.getStrand()); Chromosome c = new Chromosome(SequenceManipulation.deBlatFormatChromosomeName(chromosome), taxon); pl.setChromosome(c); genePl.setChromosome(c); /* * this only contains the chromosome and strand: the nucleotide positions are only valid for the * gene product */ gene.setPhysicalLocation(genePl); product.setName(name); String descriptionFromGP = rs.getString(8); if (StringUtils.isBlank(descriptionFromGP)) { product.setDescription("Imported from GoldenPath"); } else { product.setDescription("Imported from Golden Path: " + descriptionFromGP); } product.setPhysicalLocation(pl); product.setGene(gene); Blob exonStarts = rs.getBlob(6); Blob exonEnds = rs.getBlob(7); product.setExons(GoldenPathSequenceAnalysis.this.getExons(c, exonStarts, exonEnds)); /* * For microRNAs, we don't get exons, so we just use the whole length for now. */ if (product.getExons().size() == 0) { product.getExons().add(pl); } r.add(product); } return r; } }); }