Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:ubic.gemma.persistence.service.expression.designElement.CompositeSequenceDaoImpl.java

License:Apache License

@Override
public Map<CompositeSequence, Collection<Gene>> getGenes(Collection<CompositeSequence> compositeSequences) {
    Map<CompositeSequence, Collection<Gene>> returnVal = new HashMap<>();

    int BATCH_SIZE = 2000;

    if (compositeSequences.size() == 0)
        return returnVal;

    /*//from   ww w.  j  av a  2 s  .c om
     * Get the cs->gene mapping
     */
    final String nativeQuery = "SELECT CS, GENE FROM GENE2CS WHERE CS IN (:csids) ";

    for (CompositeSequence cs : compositeSequences) {
        returnVal.put(cs, new HashSet<Gene>());
    }

    List<Object> csGene = new ArrayList<>();
    Session session = this.getSessionFactory().getCurrentSession();
    org.hibernate.SQLQuery queryObject = session.createSQLQuery(nativeQuery);
    queryObject.addScalar("cs", new LongType());
    queryObject.addScalar("gene", new LongType());

    Collection<Long> csIdBatch = new HashSet<>();
    for (CompositeSequence cs : compositeSequences) {
        csIdBatch.add(cs.getId());

        if (csIdBatch.size() == BATCH_SIZE) {
            queryObject.setParameterList("csids", csIdBatch);
            csGene.addAll(queryObject.list());
            session.clear();
            csIdBatch.clear();
        }
    }

    if (csIdBatch.size() > 0) {
        queryObject.setParameterList("csids", csIdBatch);
        csGene.addAll(queryObject.list());
        session.clear();
    }

    StopWatch watch = new StopWatch();
    watch.start();

    int count = 0;
    Collection<Long> genesToFetch = new HashSet<>();
    Map<Long, Collection<Long>> cs2geneIds = new HashMap<>();

    for (Object object : csGene) {
        Object[] ar = (Object[]) object;
        Long cs = (Long) ar[0];
        Long gene = (Long) ar[1];
        if (!cs2geneIds.containsKey(cs)) {
            cs2geneIds.put(cs, new HashSet<Long>());
        }
        cs2geneIds.get(cs).add(gene);
        genesToFetch.add(gene);
    }

    // nothing found?
    if (genesToFetch.size() == 0) {
        returnVal.clear();
        return returnVal;
    }

    if (AbstractDao.log.isDebugEnabled())
        AbstractDao.log.debug("Built cs -> gene map in " + watch.getTime() + " ms; fetching "
                + genesToFetch.size() + " genes.");

    // fetch the genes
    Collection<Long> batch = new HashSet<>();
    Collection<Gene> genes = new HashSet<>();
    String geneQuery = "from Gene g where g.id in ( :gs )";

    org.hibernate.Query geneQueryObject = this.getSessionFactory().getCurrentSession().createQuery(geneQuery)
            .setFetchSize(1000);

    for (Long gene : genesToFetch) {
        batch.add(gene);
        if (batch.size() == BATCH_SIZE) {
            AbstractDao.log.debug("Processing batch ... ");
            geneQueryObject.setParameterList("gs", batch);
            //noinspection unchecked
            genes.addAll(geneQueryObject.list());
            batch.clear();
        }
    }

    if (batch.size() > 0) {
        geneQueryObject.setParameterList("gs", batch);
        //noinspection unchecked
        genes.addAll(geneQueryObject.list());
    }

    if (AbstractDao.log.isDebugEnabled())
        AbstractDao.log.debug("Got information on " + genes.size() + " genes in " + watch.getTime() + " ms");

    Map<Long, Gene> geneIdMap = new HashMap<>();
    for (Gene g : genes) {
        Hibernate.initialize(g);
        Long id = g.getId();
        geneIdMap.put(id, g);
    }

    // fill in the return value.
    for (CompositeSequence cs : compositeSequences) {
        Long csId = cs.getId();
        assert csId != null;
        Collection<Long> genesToAttach = cs2geneIds.get(csId);
        if (genesToAttach == null) {
            // this means there was no gene for that cs; we should remove it from the result
            returnVal.remove(cs);
            continue;
        }
        for (Long geneId : genesToAttach) {
            returnVal.get(cs).add(geneIdMap.get(geneId));
        }
        ++count;
    }

    if (AbstractDao.log.isDebugEnabled())
        AbstractDao.log.debug("Done, " + count + " result rows processed, " + returnVal.size() + "/"
                + compositeSequences.size() + " probes are associated with genes");
    return returnVal;
}

From source file:ubic.gemma.persistence.service.expression.experiment.ExpressionExperimentDaoImpl.java

License:Apache License

@Override
public Collection<ExpressionExperiment> findByExpressedGene(Gene gene, Double rank) {

    //language=MySQL
    final String queryString = "SELECT DISTINCT ee.ID AS eeID FROM "
            + "GENE2CS g2s, COMPOSITE_SEQUENCE cs, PROCESSED_EXPRESSION_DATA_VECTOR dedv, INVESTIGATION ee "
            + "WHERE g2s.CS = cs.ID AND cs.ID = dedv.DESIGN_ELEMENT_FK AND dedv.EXPRESSION_EXPERIMENT_FK = ee.ID"
            + " AND g2s.gene = :geneID AND dedv.RANK_BY_MEAN >= :rank";

    Collection<Long> eeIds;

    try {/*  ww w.  j  av  a2s. c  o m*/
        Session session = this.getSessionFactory().getCurrentSession();
        org.hibernate.SQLQuery queryObject = session.createSQLQuery(queryString);
        queryObject.setLong("geneID", gene.getId());
        queryObject.setDouble("rank", rank);
        queryObject.addScalar("eeID", new LongType());
        ScrollableResults results = queryObject.scroll();

        eeIds = new HashSet<>();

        // Post Processing
        while (results.next())
            eeIds.add(results.getLong(0));

        session.clear();
    } catch (org.hibernate.HibernateException ex) {
        throw super.convertHibernateAccessException(ex);
    }

    return this.load(eeIds);
}

From source file:ubic.gemma.persistence.service.expression.experiment.ExpressionExperimentDaoImpl.java

License:Apache License

@Override
public Collection<ExpressionExperiment> findByGene(Gene gene) {

    /*/*from   w w w . ja  va  2  s .  c om*/
     * uses GENE2CS table.
     */
    //language=MySQL
    final String queryString = "SELECT DISTINCT ee.ID AS eeID FROM "
            + "GENE2CS g2s, COMPOSITE_SEQUENCE cs, ARRAY_DESIGN ad, BIO_ASSAY ba, INVESTIGATION ee "
            + "WHERE g2s.CS = cs.ID AND ad.ID = cs.ARRAY_DESIGN_FK AND ba.ARRAY_DESIGN_USED_FK = ad.ID AND"
            + " ba.EXPRESSION_EXPERIMENT_FK = ee.ID AND g2s.GENE = :geneID";

    Collection<Long> eeIds;

    Session session = this.getSessionFactory().getCurrentSession();
    org.hibernate.SQLQuery queryObject = session.createSQLQuery(queryString);
    queryObject.setLong("geneID", gene.getId());
    queryObject.addScalar("eeID", new LongType());
    ScrollableResults results = queryObject.scroll();

    eeIds = new HashSet<>();

    while (results.next()) {
        eeIds.add(results.getLong(0));
    }

    return this.load(eeIds);
}

From source file:ubic.gemma.persistence.util.CommonQueries.java

License:Apache License

/**
 * @param session      session//from w w  w  . java 2 s. c o m
 * @param genes        genes
 * @param arrayDesigns array design
 * @return map of probe IDs to collections of gene IDs.
 */
public static Map<Long, Collection<Long>> getCs2GeneIdMap(Collection<Long> genes, Collection<Long> arrayDesigns,
        Session session) {

    Map<Long, Collection<Long>> cs2genes = new HashMap<>();

    String queryString = "SELECT CS AS csid, GENE AS geneId FROM GENE2CS g WHERE g.GENE IN (:geneIds) AND g.AD IN (:ads)";
    SQLQuery queryObject = session.createSQLQuery(queryString);
    queryObject.addScalar("csid", LongType.INSTANCE);
    queryObject.addScalar("geneId", LongType.INSTANCE);
    queryObject.setParameterList("ads", arrayDesigns);
    queryObject.setParameterList("geneIds", genes);
    queryObject.setReadOnly(true);
    queryObject.setFlushMode(FlushMode.MANUAL);

    ScrollableResults results = queryObject.scroll(ScrollMode.FORWARD_ONLY);
    CommonQueries.addGeneIds(cs2genes, results);
    results.close();

    return cs2genes;

}

From source file:ubic.gemma.persistence.util.CommonQueries.java

License:Apache License

/**
 * @param session session//from   www  .  j a  v a  2s  .  c  o m
 * @param probes  probes
 * @return map of probes to all the genes 'detected' by those probes. Probes that don't map to genes will have an
 * empty gene collection.
 */
public static Map<Long, Collection<Long>> getCs2GeneMapForProbes(Collection<Long> probes, Session session) {
    if (probes.isEmpty())
        return new HashMap<>();

    Map<Long, Collection<Long>> cs2genes = new HashMap<>();

    String queryString = "SELECT CS AS csid, GENE AS geneId FROM GENE2CS g WHERE g.CS IN (:probes) ";
    org.hibernate.SQLQuery queryObject = session.createSQLQuery(queryString);
    queryObject.addScalar("csid", LongType.INSTANCE);
    queryObject.addScalar("geneId", LongType.INSTANCE);
    queryObject.setParameterList("probes", probes, LongType.INSTANCE);
    queryObject.setReadOnly(true);
    queryObject.setFlushMode(FlushMode.MANUAL);

    ScrollableResults results = queryObject.scroll(ScrollMode.FORWARD_ONLY);
    CommonQueries.addGeneIds(cs2genes, results);
    results.close();

    return cs2genes;
}

From source file:ubic.gemma.persistence.util.CommonQueries.java

License:Apache License

public static Collection<Long> filterProbesByPlatform(Collection<Long> probes, Collection<Long> arrayDesignIds,
        Session session) {//from  ww w  . j  a  v a 2s  .  c o m
    assert probes != null && !probes.isEmpty();
    assert arrayDesignIds != null && !arrayDesignIds.isEmpty();
    String queryString = "SELECT CS AS csid FROM GENE2CS WHERE AD IN (:adids) AND CS IN (:probes)";
    org.hibernate.SQLQuery queryObject = session.createSQLQuery(queryString);
    queryObject.addScalar("csid", LongType.INSTANCE);
    queryObject.setParameterList("probes", probes, LongType.INSTANCE);
    queryObject.setParameterList("adids", arrayDesignIds, LongType.INSTANCE);

    ScrollableResults results = queryObject.scroll(ScrollMode.FORWARD_ONLY);
    List<Long> r = new ArrayList<>();
    while (results.next()) {
        r.add(results.getLong(0));

    }
    results.close();
    return r;
}

From source file:ud.ing.modi.mapper.PendienteAltaRegistroMapper.java

public int obtenerNumSolicitudesAltaTiendaOnline() {
    int respuesta = 0;
    iniciaOperacion();/*from  w  w w .java  2  s.  c  o m*/
    SQLQuery query = getSesion().createSQLQuery(
            "SELECT COUNT(*) AS NUM FROM PENDIENTE_ALTA_REGISTRO A, TIENDA_ONLINE B WHERE A.COD_CLIENTE = B.COD_CLIENTE");
    query.addScalar("NUM", Hibernate.INTEGER);
    List resustado = query.list();

    if (!resustado.isEmpty()) {
        respuesta = (Integer) resustado.get(0);
    }
    System.out.println("EL PRIMER QQUERYRESULTO : " + respuesta);
    return respuesta;
}

From source file:ud.ing.modi.mapper.PendienteAltaRegistroMapper.java

public int obtenerNumSolicitudesAltaPuntoRecarga() {
    int respuesta = 0;
    iniciaOperacion();/*from w w  w  . j  av a2 s  . c  o m*/
    SQLQuery query = getSesion().createSQLQuery(
            "SELECT COUNT(*) AS NUM FROM PENDIENTE_ALTA_REGISTRO A, PUNTO_RECARGA B WHERE A.COD_CLIENTE = B.COD_CLIENTE");
    query.addScalar("NUM", Hibernate.INTEGER);
    List resustado = query.list();

    if (!resustado.isEmpty()) {
        respuesta = (Integer) resustado.get(0);
    }
    System.out.println("EL PRIMER QQUERYRESULTO : " + respuesta);
    return respuesta;
}