Example usage for org.hibernate ScrollableResults get

List of usage examples for org.hibernate ScrollableResults get

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. ScrollableResults
  6. get

Introduction

In this page you can find the example usage for org.hibernate ScrollableResults get.

Prototype

Object[] get();

Source Link

Document

Get the current row of results.

Usage

From source file:edu.harvard.med.screensaver.db.ScreenResultsDAOImpl.java

License:Open Source License

public int createScreenedReagentCounts(final ScreenType screenType, Screen study,
        AnnotationType positiveAnnotationType, AnnotationType overallAnnotationType) {
    // Break this into two separate queries because of Hibernate bug (http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615):
    // when using the "group by" clause with a full object (as opposed to an attribute of the object/table),
    // Hibernate is requiring that every attribute of the object be specified in a "group by" and not 
    // just the object itself.  so the workaround is to query once to get the id's then once again to 
    // get the objects.

    log.info("1. get the reagent id's for the positive counts");
    ScrollableResults sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() {
        public ScrollableResults execute(Session session) {
            HqlBuilder builder = new HqlBuilder();
            builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw")
                    .from("aw", AssayWell.libraryWell, "w", JoinType.INNER)
                    .from("w", Well.latestReleasedReagent, "r", JoinType.INNER)
                    .from("w", Well.library, "l", JoinType.INNER)
                    .where("l", "screenType", Operator.EQUAL, screenType)
                    .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL);
            builder.where("aw", "positive", Operator.EQUAL, Boolean.TRUE);
            builder.groupBy("r", "id");
            log.debug("hql: " + builder.toHql());
            return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE)
                    .scroll(ScrollMode.FORWARD_ONLY);
        }/*from  w w  w .  j a v  a  2s  .c  o  m*/
    });

    Map<Integer, Long> positivesMap = Maps.newHashMap();
    while (sr.next()) {
        Object[] row = sr.get();
        positivesMap.put((Integer) row[0], (Long) row[1]);
    }

    log.info("2. get the reagent id's for the overall counts");
    sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() {
        public ScrollableResults execute(Session session) {
            HqlBuilder builder = new HqlBuilder();
            builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw")
                    .from("aw", AssayWell.libraryWell, "w", JoinType.INNER)
                    .from("w", Well.library, "l", JoinType.INNER)
                    .from("w", Well.latestReleasedReagent, "r", JoinType.INNER)
                    .where("l", "screenType", Operator.EQUAL, screenType)
                    .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL)
                    .groupBy("r", "id");
            log.debug("hql: " + builder.toHql());
            return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE)
                    .scroll(ScrollMode.FORWARD_ONLY);
        }
    });

    Map<Integer, Long> overallMap = Maps.newHashMap();
    while (sr.next()) {
        Object[] row = sr.get();
        overallMap.put((Integer) row[0], (Long) row[1]);
    }

    log.info("3. get the Reagents");
    sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() {
        public ScrollableResults execute(Session session) {
            HqlBuilder builder = new HqlBuilder();
            builder.select("r").distinctProjectionValues().from(AssayWell.class, "aw")
                    .from("aw", AssayWell.libraryWell, "w", JoinType.INNER)
                    .from("w", Well.library, "l", JoinType.INNER)
                    .from("w", Well.latestReleasedReagent, "r", JoinType.INNER)
                    .where("l", "screenType", Operator.EQUAL, screenType)
                    .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL);
            log.debug("hql: " + builder.toHql());
            return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE)
                    .scroll(ScrollMode.FORWARD_ONLY);
        }
    });

    log.info("4. build the Study: positives: " + positivesMap.size() + ", reagents: " + overallMap.size());
    int count = 0;
    while (sr.next()) {
        Reagent r = (Reagent) sr.get()[0];

        AnnotationValue av = new AnnotationValue(overallAnnotationType, r, null,
                (double) overallMap.get(r.getReagentId()).intValue());
        _dao.saveOrUpdateEntity(av);
        Long positiveCount = positivesMap.get(r.getReagentId());
        if (positiveCount != null) {
            av = new AnnotationValue(positiveAnnotationType, r, null, (double) positiveCount.intValue());
            _dao.saveOrUpdateEntity(av);
        }
        // Note: due to memory performance, we will build the study_reagent_link later
        if (count++ % ROWS_TO_CACHE == 0) {
            log.debug("flushing");
            _dao.flush();
            _dao.clear();
        }
        if (count % 10000 == 0) {
            log.info("" + count + " reagents processed");
        }
    }

    log.info("save the study");
    _dao.saveOrUpdateEntity(study);
    _dao.flush();
    log.info("populateStudyReagentLinkTable");
    int reagentCount = populateStudyReagentLinkTable(study.getScreenId());
    log.info("done: positives: " + positivesMap.size() + ", reagents: " + overallMap.size());
    return reagentCount;
}

From source file:edu.harvard.med.screensaver.io.screenresults.ScreenResultReporter.java

License:Open Source License

/**
 * for [#2268] new column to display # overlapping screens
 *//*w ww .j  a v  a 2s .com*/
@Transactional
public int createScreenedReagentCounts(final ScreenType screenType, Screen study,
        AnnotationType positiveAnnotationType, AnnotationType overallAnnotationType) {
    // Break this into two separate queries because of an apparent Hibernate bug:
    // when using the "group by" clause with a full object (as opposed to an attribute of the object/table),
    // Hibernate is requiring that every attribute of the object be specified in a "group by" and not 
    // just the object itself.  so the workaround is to query once to get the id's then once again to 
    // get the objects.
    //    study = _dao.mergeEntity(study);
    //    positiveAnnotationType = _dao.mergeEntity(positiveAnnotationType);
    //    overallAnnotationType = _dao.mergeEntity(overallAnnotationType);
    //    _dao.flush();

    log.info("1. get the reagent id's for the positive counts");
    ScrollableResults sr = _dao.runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() {
        public ScrollableResults execute(Session session) {
            HqlBuilder builder = new HqlBuilder();
            builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw")
                    .from("aw", AssayWell.libraryWell, "w", JoinType.INNER)
                    .from("w", Well.latestReleasedReagent, "r", JoinType.INNER)
                    .from("w", Well.library, "l", JoinType.INNER)
                    .where("l", "screenType", Operator.EQUAL, screenType)
                    .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL);
            builder.where("aw", "positive", Operator.EQUAL, Boolean.TRUE);
            builder.groupBy("r", "id");
            log.debug("hql: " + builder.toHql());
            return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE)
                    .scroll(ScrollMode.FORWARD_ONLY);
        }
    });

    Map<Integer, Long> positivesMap = Maps.newHashMap();
    while (sr.next()) {
        Object[] row = sr.get();
        positivesMap.put((Integer) row[0], (Long) row[1]);
    }

    log.info("2. get the reagent id's for the overall counts");
    sr = _dao.runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() {
        public ScrollableResults execute(Session session) {
            HqlBuilder builder = new HqlBuilder();
            builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw")
                    .from("aw", AssayWell.libraryWell, "w", JoinType.INNER)
                    .from("w", Well.library, "l", JoinType.INNER)
                    .from("w", Well.latestReleasedReagent, "r", JoinType.INNER)
                    .where("l", "screenType", Operator.EQUAL, screenType)
                    .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL)
                    .groupBy("r", "id");
            log.debug("hql: " + builder.toHql());
            return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE)
                    .scroll(ScrollMode.FORWARD_ONLY);
        }
    });

    log.info("begin assigning values to the study");
    int overallCount = 0;
    Map<Integer, Long> overallMap = Maps.newHashMap();
    while (sr.next()) {
        Object[] row = sr.get();
        Integer r_id = (Integer) row[0];
        Long count = (Long) row[1];
        Reagent r = _dao.findEntityById(Reagent.class, r_id, true);
        // note: for memory performance, we're side-stepping the AnnotationType.createAnnotationValue() method
        AnnotationValue av = new AnnotationValue(overallAnnotationType, r, null, (double) count);
        _dao.persistEntity(av);
        Long positiveCount = positivesMap.get(r_id);
        if (positiveCount != null) {
            // note: for memory performance, we're side-stepping the AnnotationType.createAnnotationValue() method
            av = new AnnotationValue(positiveAnnotationType, r, null, (double) positiveCount.intValue());
            _dao.persistEntity(av);
        }
        // Note: due to memory performance, we will build the study_reagent_link later
        if (count++ % AbstractDAO.ROWS_TO_CACHE == 0) {
            log.debug("flushing");
            _dao.flush();
            _dao.clear();
        }
        if (++overallCount % 10000 == 0) {
            log.info("" + overallCount + " reagents processed");
        }
    }

    log.info("save the study");
    // unnecessary since study is already persisted, and the reagents will be linked by the populateStudyReagentLinkTable - sde4
    // _dao.mergeEntity(study);
    _dao.flush();
    log.info("populateStudyReagentLinkTable");
    int reagentCount = _screenDao.populateStudyReagentLinkTable(study.getScreenId());
    log.info("done: positives: " + positivesMap.size() + ", reagents: " + overallCount);
    return reagentCount;
}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getVFDBList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getVFDBSQL(key, "function");

    sql += " GROUP BY vfg_id, gene_name, gene_product, vf_id, vf_name, vf_fullname, function ";

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {//from  w w w .j a  v a2s.c  o  m

        sql += " ORDER BY b.vfg_id, b.gene_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindVFDBSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("vfg_id", obj[0]);
        row.put("gene_name", obj[1]);
        row.put("gene_product", obj[2]);
        row.put("vf_id", obj[3]);
        row.put("vf_name", obj[4]);
        row.put("vf_fullname", obj[5]);
        row.put("function", obj[6]);
        row.put("feature_count", obj[7]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getVFDBFeatureList(Map<String, String> key, Map<String, String> sort, int start,
        int end) {

    String sql = "";

    sql += getVFDBFeatureSQL(key, "function");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {//from  w w w  . j av a  2 s.  c o  m

        sql += " ORDER BY na_feature_id";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindVFDBFeatureSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {
        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("vf_id", obj[0]);
        row.put("vf_name", obj[1]);
        row.put("vfg_id", obj[2]);
        row.put("gene_name", obj[3]);
        row.put("na_feature_id", obj[4]);
        row.put("genome_info_id", obj[5]);
        row.put("genome_name", obj[6]);
        row.put("accession", obj[7]);
        row.put("locus_tag", obj[8]);
        row.put("product", obj[9]);
        row.put("genome_id", obj[10]);
        row.put("feature_id", obj[11]);
        row.put("patric_id", obj[12]);
        row.put("refseq_locus_tag", obj[13]);
        row.put("alt_locus_tag", obj[14]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getCTDList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getCTDSQL(key, "function");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {//  w w  w  . j a  va 2 s.c  o m

        sql += " ORDER BY cs.gene_sym, cs.disease_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("gene_disease_rel", obj[2]);
        row.put("disease_name", obj[3]);
        row.put("pubmed_id", obj[4]);
        row.put("disease_id", obj[5]);
        row.put("gd_app_name", obj[6]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getCTDGraphList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getCTDSQL(key, "graphlist");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {//from w  ww . j a  v  a2s .  c  om

        sql += " ORDER BY cs.gene_sym, cs.disease_name";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindGraphCTDSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("gene_disease_rel", obj[2]);
        row.put("disease_name", obj[3]);
        row.put("pubmed_id", obj[4]);
        row.put("disease_id", obj[5]);
        row.put("gd_app_name", obj[6]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;

}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getGADGraphList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getGADSQL(key, "graphlist");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {/*from w  w  w . j a  v  a  2  s. c  o  m*/

        sql += " ORDER BY gs.gene_sym";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindGraphGADSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("association", obj[2]);
        row.put("mesh_disease_terms", obj[3]);
        row.put("broad_phenotype", obj[4]);
        row.put("pubmed_id", obj[5]);
        row.put("conclusion", obj[6]);
        row.put("gd_app_name", obj[7]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;
}

From source file:edu.vt.vbi.patric.dao.DBDisease.java

License:Apache License

public List<ResultType> getGADList(Map<String, String> key, Map<String, String> sort, int start, int end) {

    String sql = "";

    sql += getGADSQL(key, "function");

    if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction")
            && sort.get("direction") != null) {

        sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction");

    } else {//  w  ww .j av a 2  s  . c  o m

        sql += " ORDER BY gs.gene_sym";
    }

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q = bindSQLValues(q, key);

    if (end > 0) {
        q.setMaxResults(end);
    }

    ScrollableResults scr = q.scroll();
    List<ResultType> results = new ArrayList<>();

    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) {

        Object[] obj = scr.get();
        ResultType row = new ResultType();
        row.put("gene_sym", obj[0]);
        row.put("gene_id", obj[1]);
        row.put("association", obj[2]);
        row.put("mesh_disease_terms", obj[3]);
        row.put("broad_phenotype", obj[4]);
        row.put("pubmed_id", obj[5]);
        row.put("conclusion", obj[6]);
        row.put("gd_app_name", obj[7]);

        results.add(row);
    }

    session.getTransaction().commit();

    return results;
}

From source file:edu.vt.vbi.patric.dao.DBPRC.java

License:Apache License

public ArrayList<ResultType> getPRCData(String taxonId, String filter, int start, int end, String sort,
        String dir) {//from   w  ww.ja v a2 s. co  m

    String sql = "select distinct experiment_id, description, speciesname, processing_type, summary, pubmed_id, count(distinct sample_id) from app.post_genomic";
    if (filter.equals("MS"))
        sql += " where processing_type = 'Mass spectrometry'";
    else if (filter.equals("MA"))
        sql += " where processing_type = 'Microarray'";
    else
        sql += " where processing_type = 'Protein interaction'";
    sql += " and taxon_id in (" + DBSummary.getTaxonIdsInTaxonSQL(":taxonId") + ")";
    sql += " group by experiment_id, description, speciesname, processing_type, summary, pubmed_id";
    sql += " order by " + sort + " " + dir;

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);

    q.setTimeout(SQL_TIMEOUT);
    q.setString("taxonId", taxonId);

    ScrollableResults scr = null;

    try {
        scr = q.scroll();
    } catch (Exception ex) {
        System.out.println("[SQL error]" + taxonId);
        ex.printStackTrace();
        return null;
    }

    ArrayList<ResultType> results = new ArrayList<ResultType>();
    Object[] obj = null;
    if (start > 1) {
        scr.setRowNumber(start - 1);
    } else {
        scr.beforeFirst();
    }

    for (int i = start; (end > 0 && i < end && scr.next() == true) || (end == -1 && scr.next() == true); i++) {
        obj = scr.get();
        ResultType row = new ResultType();

        row.put("experiment_id", obj[0]);
        row.put("description", obj[1]);
        row.put("speciesname", obj[2]);
        row.put("experimenttype", obj[3]);
        row.put("summary", obj[4]);
        row.put("pubmed_id", obj[5]);
        row.put("samples", obj[6]);
        results.add(row);
    }
    session.getTransaction().commit();
    return results;
}

From source file:gov.nih.nci.caarray.plugins.nimblegen.NdfHandler.java

License:BSD License

private int loadProbes(ArrayDesignDetails details, Map<String, LogicalProbe> logicalProbes,
        ScrollableResults results) throws IOException {
    int count = 0;
    results.beforeFirst();//w  w  w.jav  a2  s  .com
    String lastSeqId = null;
    while (results.next()) {
        final Object[] values = results.get();
        final Map<String, Object> vals = new HashMap<String, Object>();
        vals.put(PROBE_ID, values[0]);
        vals.put(SEQ_ID, values[1]);
        vals.put(CONTAINER2, values[2]);
        vals.put(X, values[3]);
        vals.put(Y, values[4]);

        if (lastSeqId != null && !vals.get(SEQ_ID).equals(lastSeqId)) {
            logicalProbes.clear();
            flushAndClearSession();
        }
        lastSeqId = (String) vals.get(SEQ_ID);

        final PhysicalProbe p = createPhysicalProbe(details, vals, logicalProbes);
        getArrayDao().save(p);
        ++count;
    }
    return count;
}