Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery 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:gov.nih.nci.cabig.caaers.accesscontrol.query.impl.AbstractIdFetcher.java

License:BSD License

@SuppressWarnings("unchecked")
public List<?> search(final AbstractQuery query) {
    String queryString = query.getQueryString();
    if (log.isDebugEnabled())
        log.debug("::: " + queryString);
    return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(final Session session) throws HibernateException, SQLException {
            if (query instanceof NativeSQLQuery) {
                org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
                Map<String, org.hibernate.type.Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
                for (String key : scalarMap.keySet()) {
                    nativeQuery.addScalar(key, scalarMap.get(key));
                }//from   w w  w .ja  v a  2  s . c o  m
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    nativeQuery.setParameter(key, value);
                }
                return nativeQuery.list();
            } else {
                org.hibernate.Query hibernateQuery = session.createQuery(query.getQueryString());
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        hibernateQuery.setParameterList(key, (Collection) value);
                    } else {
                        hibernateQuery.setParameter(key, value);
                    }

                }
                return hibernateQuery.list();
            }
        }

    });
}

From source file:gov.nih.nci.cabig.caaers.dao.CaaersDao.java

License:BSD License

/**
 * A paginated query. /*from w  w  w .j  av a  2s. c om*/
 * @param query - The query to be executed. 
 * @param firstResult - The starting index
 * @param maxResults - The number of objects to be returned. 
 * @return - A list of objects returned by the query, matching the pagination criteria. 
 */
@SuppressWarnings("unchecked")
public List<?> search(final AbstractQuery query, final Integer firstResult, final Integer maxResults) {
    String queryString = query.getQueryString();
    if (log.isDebugEnabled())
        log.debug("::: " + queryString);
    return (List<?>) getHibernateTemplate().execute(new HibernateCallback() {

        public Object doInHibernate(final Session session) throws HibernateException, SQLException {
            if (query instanceof NativeSQLQuery) {
                org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
                setResultSetBoundaries(nativeQuery, firstResult, maxResults);
                Map<String, Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
                for (String key : scalarMap.keySet()) {
                    nativeQuery.addScalar(key, scalarMap.get(key));
                }
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        nativeQuery.setParameterList(key, (Collection) value);
                    } else {
                        nativeQuery.setParameter(key, value);
                    }
                }
                return nativeQuery.list();
            } else {
                org.hibernate.Query hibernateQuery = session.createQuery(query.getQueryString());
                setResultSetBoundaries(hibernateQuery, firstResult, maxResults);
                Map<String, Object> queryParameterMap = query.getParameterMap();
                for (String key : queryParameterMap.keySet()) {
                    Object value = queryParameterMap.get(key);
                    if (value instanceof Collection) {
                        hibernateQuery.setParameterList(key, (Collection) value);
                    } else {
                        hibernateQuery.setParameter(key, value);
                    }

                }
                return hibernateQuery.list();
            }
        }

    });
}

From source file:gov.nih.nci.cabig.caaers.service.workflow.WorkflowServiceImpl.java

License:BSD License

/**
 * Find DCC reviewer from the workflow// w w  w  .  j  a v a 2  s. com
 * @param wfId
 * @return User instance
 */
public User findCoordinatingCenterReviewer(Integer wfId) {
    String strQuery = "SELECT jbpm_pooledactor.actorid_ "
            + "FROM public.jbpm_pooledactor, public.jbpm_taskactorpool, public.jbpm_taskinstance "
            + "WHERE jbpm_pooledactor.id_ = jbpm_taskactorpool.pooledactor_ "
            + "AND jbpm_taskactorpool.taskinstance_= jbpm_taskinstance.id_ "
            + "AND jbpm_taskinstance.name_ IN ('Coordinating Center Review', 'Data Coordinator Review') "
            + "AND jbpm_taskinstance.procinst_= :wfId";
    final NativeSQLQuery query = new NativeSQLQuery(strQuery);
    query.setParameter("wfId", wfId.intValue());
    query.setScalar("actorid_", StandardBasicTypes.STRING);
    List userList = jbpmTemplate.getHibernateTemplate().executeFind(new HibernateCallback() {

        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            org.hibernate.SQLQuery nativeQuery = session.createSQLQuery(query.getQueryString());
            Map<String, org.hibernate.type.Type> scalarMap = ((NativeSQLQuery) query).getScalarMap();
            for (String key : scalarMap.keySet()) {
                nativeQuery.addScalar(key, scalarMap.get(key));
            }
            Map<String, Object> queryParameterMap = query.getParameterMap();
            for (String key : queryParameterMap.keySet()) {
                Object value = queryParameterMap.get(key);
                nativeQuery.setParameter(key, value);
            }
            return nativeQuery.list();
        }
    });

    User reviewer = null;
    if (CollectionUtils.isNotEmpty(userList)) {
        reviewer = userRepository.getUserByLoginName((String) userList.get(0));
    }

    return reviewer;

}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public Collection<Integer> getAgeLowerLimitValues(StudyCriteria studyCrit) {
    if (studyCrit == null || studyCrit.getId() == null)
        return new ArrayList<Integer>();
    Long studyId = studyCrit.getId();
    ageLowerLimits = new HashSet<Integer>();
    Session session = getSessionFactory().getCurrentSession();
    HashMap params = new HashMap();
    String sql = " SELECT AGE_AT_ENROLL_MIN FROM ENROLL_AGE_LU WHERE STUDY_ID = :studyId ";
    params.put("studyId", studyId);
    SQLQuery q = session.createSQLQuery(sql);
    q.addScalar("AGE_AT_ENROLL_MIN", Hibernate.INTEGER);
    HQLHelper.setParamsOnQuery(params, q);
    Collection<BigDecimal> minValues = q.list();
    Collection<Integer> intValues = CollectionUtils.collect(minValues, new IntegerTransformer());
    ageLowerLimits.addAll(intValues);//from  w  ww.j av a2  s.  c  o  m
    return ageLowerLimits;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public Collection<Integer> getAgeUpperLimitValues(StudyCriteria studyCrit) {
    if (studyCrit == null || studyCrit.getId() == null)
        return new ArrayList<Integer>();
    Long studyId = studyCrit.getId();
    ageUpperLimits = new HashSet<Integer>();
    Session session = getSessionFactory().getCurrentSession();
    HashMap params = new HashMap();
    String sql = " SELECT AGE_AT_ENROLL_MAX FROM ENROLL_AGE_LU WHERE STUDY_ID = :studyId ";
    params.put("studyId", studyId);
    SQLQuery q = session.createSQLQuery(sql);
    q.addScalar("AGE_AT_ENROLL_MAX", Hibernate.INTEGER);
    HQLHelper.setParamsOnQuery(params, q);
    Collection<BigDecimal> minValues = q.list();
    Collection<Integer> intValues = CollectionUtils.collect(minValues, new IntegerTransformer());
    ageUpperLimits.addAll(intValues);/*from  w ww.  jav a2s  . co m*/
    return ageUpperLimits;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public Collection<String> getCaseControlStatus(StudyCriteria studyCrit) {
    if (studyCrit == null || studyCrit.getId() == null)
        return new ArrayList<String>();
    Long studyId = studyCrit.getId();
    caseControlStatus = new HashSet<String>();
    Session session = getSessionFactory().getCurrentSession();
    HashMap params = new HashMap();
    String sql = " SELECT DISTINCT CASE_CONTROL_STATUS FROM STUDY_PARTICIPANT WHERE STUDY_ID= :studyId ";
    params.put("studyId", studyId);
    SQLQuery q = session.createSQLQuery(sql);
    q.addScalar("CASE_CONTROL_STATUS", Hibernate.STRING);
    HQLHelper.setParamsOnQuery(params, q);
    List<String> statusValues = q.list();
    caseControlStatus.addAll(statusValues);
    return caseControlStatus;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public Long getStudyParticipantCount(StudyCriteria studyCrit) {
    Long count = new Long(0);
    if (studyCrit == null || studyCrit.getId() == null)
        return count;
    Long studyId = studyCrit.getId();
    Set studyParticipantSet = new HashSet<String>();
    Session session = getSessionFactory().getCurrentSession();
    HashMap params = new HashMap();
    String sql = " SELECT PARTICIPANT_ID FROM STUDY_PARTICIPANT WHERE STUDY_ID= :studyId ";
    params.put("studyId", studyId);
    SQLQuery q = session.createSQLQuery(sql);
    q.addScalar("PARTICIPANT_ID", Hibernate.STRING);
    HQLHelper.setParamsOnQuery(params, q);
    List<String> statusValues = q.list();
    studyParticipantSet.addAll(statusValues);
    count = new Long(studyParticipantSet.size());
    return count;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public Collection<String> getAllQCStatus() {
    if (qcStatusValues == null) {
        qcStatusValues = new HashSet<String>();
        try {/*from w w  w.j  a  va2  s .  c  o m*/
            Session session = getSessionFactory().getCurrentSession();
            String sql = "SELECT GENETYPE_STATUS FROM GENOTYPE_STATUS_LU ";
            SQLQuery q = session.createSQLQuery(sql);
            q.addScalar("GENETYPE_STATUS", Hibernate.STRING);
            Collection<String> values = q.list();
            qcStatusValues.addAll(values);
        } catch (HibernateException e) {
            logger.error(e);
            throw new RuntimeException(e);
        } catch (Exception e) {
            logger.error(e);
            throw new RuntimeException(e);
        }
    }
    return qcStatusValues;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

public List<String> getChromosomes() {
    if (CHROMOSOME_LIST == null) {
        Session session = null;//from  w w  w.j av a 2s .  c om
        try {
            session = getSessionFactory().getCurrentSession();
        } catch (HibernateException e) {
            logger.error(e);
        } catch (Exception e) {
            logger.error(e);
        }

        /* The below code is extremely slow.  Hence commented out.
          At the same time, we do not have a class that is mapped to
          CHR_START_END table.  So as a temp solution use direct SQL query
        */
        /*  Criteria crit = session.createCriteria(SNPAnnotation.class );
            crit.setProjection(Projections.property("chromosomeName"));
            List<String> values = crit.list();
         */

        SQLQuery q = session.createSQLQuery("SELECT CHROMOSOME FROM CHR_START_END");
        q.addScalar("CHROMOSOME", Hibernate.STRING);
        q.list();
        List<String> values = q.list();
        /* now sort them and place 'em in CHROMOSOME_LIST */
        CHROMOSOME_LIST = new ArrayList<String>();

        String[] sortedChrs = new String[22];
        SortedSet<String> sexChromosomes = new TreeSet<String>();
        for (int i = 0; i < values.size(); i++) {
            String chr = values.get(i);
            try {
                int index = Integer.parseInt(chr) - 1;
                sortedChrs[index] = chr;
            } catch (NumberFormatException e) {
                sexChromosomes.add(chr); // either X, Y, or MT
            }
        }

        for (int i = 0, j = 0; i < sortedChrs.length; i++) {
            String sortedChr = sortedChrs[i];
            CHROMOSOME_LIST.add(j++, sortedChr);
        }

        CHROMOSOME_LIST.addAll(sexChromosomes);
    }
    return CHROMOSOME_LIST;
}

From source file:gov.nih.nci.caintegrator.studyQueryService.germline.ObjectQueryHandler.java

License:BSD License

@SuppressWarnings("unchecked")
public List<String> getAnalysisMethodTypes(StudyCriteria studyCrit) {
    if (studyCrit == null || studyCrit.getId() == null)
        return new ArrayList<String>();
    Long studyId = studyCrit.getId();
    analysisMethodTypes = new HashSet<String>();
    Session session = getSessionFactory().getCurrentSession();
    HashMap params = new HashMap();
    String sql = "SELECT ANALYSIS_METHOD_TYPE FROM SNP_ANALYSIS_LU WHERE STUDY_ID = :studyId ORDER BY DISPLAY_ORDER";
    params.put("studyId", studyId);
    SQLQuery q = session.createSQLQuery(sql);
    q.addScalar("ANALYSIS_METHOD_TYPE", Hibernate.STRING);
    HQLHelper.setParamsOnQuery(params, q);
    List<String> values = q.list();
    if (values != null) {
        analysisMethodTypes.addAll(values);
        return new ArrayList<String>(analysisMethodTypes);
    } else/*from w  w w  .  j a v a2  s. c o  m*/
        return Collections.EMPTY_LIST;
}