Example usage for org.hibernate Query list

List of usage examples for org.hibernate Query list

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. list

Introduction

In this page you can find the example usage for org.hibernate Query list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java

License:Open Source License

public List<PhenoDataSetData> getPhenoDataList(PhenoDataSetCollection phenoCollection,
        PhenoDataSetCategory phenoDataSetCategory, int first, int count) {

    List<PhenoDataSetData> phenoDataList = new ArrayList<PhenoDataSetData>();

    // The following HQL is based on this SQL, except that we don't need the CustomField
    //      SELECT *
    //        FROM study.custom_field cf
    //       INNER JOIN study.custom_field_display cfd
    //          ON cfd.custom_field_id = cf.id
    //       INNER JOIN study.custom_field_group cfg
    //          ON cfd.custom_field_group_id = cfg.id
    //       INNER JOIN pheno.pheno_collection pc
    //          ON pc.custom_field_group_id = cfg.id
    //        LEFT JOIN pheno.pheno_data pd
    //          ON pd.custom_field_display_id = cfd.id
    //         AND pd.pheno_collection_id = pc.id
    //       WHERE pc.id = 1;

    /*      /*  ww  w  .j  a  v a  2s. c  o  m*/
     * WARNING: Do no try to do a HQL WITH clause between "pd.phenoCollection.id" and another table's column!
     * Even though it looks like it should work in SQL (as above), for an unknown reason HQL parsing will fail - like this:
     * ----
     * Caused by: org.hibernate.hql.ast.QuerySyntaxException: with-clause referenced two different from-clause elements 
     * [  FROM au.org.theark.core.model.study.entity.CustomFieldDisplay AS cfd  
     * INNER JOIN cfd.customFieldGroup cfg  
     * INNER JOIN cfg.phenoCollection pc   
     * LEFT JOIN cfd.phenoData AS pd   
     * WITH pd.phenoCollection.id = pc.id 
     * WHERE pc.id = :pcId ORDER BY cfd.sequence ]
     * ----
     * Thus the present work-around is to use an argument "pcId" for the WITH criteria.
     */
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT pdsfd, pdsd ");
    sb.append("  FROM PhenoDataSetFieldDisplay AS pdsfd ");
    sb.append(" INNER JOIN pdsfd.phenoDataSetGroup AS pdsg ");
    sb.append(" INNER JOIN pdsg.phenoDataSetCollections pdsc ");
    sb.append("  LEFT JOIN pdsfd.phenoDataSetData AS pdsd ");
    sb.append("  WITH pdsd.phenoDataSetCollection.id = :pcId ");
    sb.append(" WHERE pdsc.id = :pcId ");
    sb.append(" and pdsfd.phenoDataSetCategory = :phenoDataSetCategory ");
    sb.append(" and pdsfd.phenoDataSetField is not null ");
    sb.append(" ORDER BY pdsfd.phenoDataSetFiledOrderNumber ");

    Query query = getSession().createQuery(sb.toString());
    query.setParameter("pcId", phenoCollection.getId());
    query.setParameter("phenoDataSetCategory", phenoDataSetCategory);
    query.setFirstResult(first);
    query.setMaxResults(count);

    List<Object[]> listOfObjects = query.list();
    for (Object[] objects : listOfObjects) {
        PhenoDataSetFieldDisplay pfd = new PhenoDataSetFieldDisplay();
        PhenoDataSetData phenoData = new PhenoDataSetData();
        if (objects.length > 0 && objects.length >= 1) {
            pfd = (PhenoDataSetFieldDisplay) objects[0];
            if (objects[1] != null) {
                phenoData = (PhenoDataSetData) objects[1];
            } else {
                phenoData.setPhenoDataSetFieldDisplay(pfd);
            }
            phenoDataList.add(phenoData);
        }
    }
    return phenoDataList;
}

From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java

License:Open Source License

public List<PhenoDataSetCollection> searchPageablePhenoCollection(PhenoDataCollectionVO collectionCriteria,
        int first, int count) {

    List<PhenoDataSetCollection> resultList = new ArrayList<PhenoDataSetCollection>();
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT qnaire, pc ");
    sb.append("  FROM " + PhenoDataSetGroup.class.getName() + " AS qnaire ");
    sb.append("  LEFT JOIN qnaire.phenoDataSetCollections as pc ");
    sb.append("  WITH pc.linkSubjectStudy.id = :subjectId ");
    sb.append(" WHERE qnaire.study.id = :studyId ");
    //sb.append("   AND qnaire.arkFunction.id = :functionId ");
    sb.append("   AND qnaire.published = true ");

    Query query = getSession().createQuery(sb.toString());
    query.setParameter("subjectId",
            collectionCriteria.getPhenoDataSetCollection().getLinkSubjectStudy().getId());
    query.setParameter("studyId", collectionCriteria.getPhenoDataSetGroup().getStudy().getId());
    //log.info("colcrit ark=" + collectionCriteria.getArkFunction());
    //long id = collectionCriteria.getArkFunction().getId();
    //log.info("id=" + id);
    //query.setParameter("functionId",id);
    query.setFirstResult(first);//ww w  . j a v a 2 s. c om
    query.setMaxResults(count);

    List<Object[]> listOfObjects = query.list();
    for (Object[] objects : listOfObjects) {
        //CustomFieldGroup questionnaire = new CustomFieldGroup();
        PhenoDataSetGroup questionnaire = new PhenoDataSetGroup();
        PhenoDataSetCollection pc = new PhenoDataSetCollection();
        if (objects.length > 0 && objects.length >= 1) {
            questionnaire = (PhenoDataSetGroup) objects[0];
            if (objects[1] != null) {
                pc = (PhenoDataSetCollection) objects[1];
            } else {
                pc.setQuestionnaire(questionnaire);
            }
            resultList.add(pc);
        }
    }
    Criteria criteria = getSession().createCriteria(PhenoDataSetCollection.class);
    criteria.createAlias("questionnaire", "qnaire");
    criteria.add(Restrictions.eq("linkSubjectStudy",
            collectionCriteria.getPhenoDataSetCollection().getLinkSubjectStudy()));
    // Just a precaution (PhenoCollection to should always map to a CustomFieldGroup where the ArkFunction will correspond to Pheno) 
    criteria.add(Restrictions.eq("qnaire.arkFunction", collectionCriteria.getArkFunction()));
    criteria.setFirstResult(first);
    criteria.setMaxResults(count);
    resultList = criteria.list();
    return resultList;
}

From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java

License:Open Source License

@Override
public List<PhenoDataSetFieldDisplay> getPhenoFieldDisplaysIn(List<String> fieldNameCollection, Study study,
        ArkFunction arkFunction, PhenoDataSetGroup phenoDataSetGroup) {
    if (fieldNameCollection == null || fieldNameCollection.isEmpty()) {
        return new ArrayList<PhenoDataSetFieldDisplay>();
    } else {//from   w  w  w  . j  a  v  a  2 s  .  co m
        List<String> lowerCaseNames = new ArrayList<String>();
        for (String name : fieldNameCollection) {
            lowerCaseNames.add(name.toLowerCase());
        }
        /*String queryString = "select cfd from PhenoDataSetFieldDisplay cfd " + 
          " where cfd.customFieldGroup =:customFieldGroup and customField.id in ( " + 
          " SELECT id from CustomField cf " + 
          " where cf.study =:study " + " and lower(cf.name) in (:names) " + " and cf.arkFunction =:arkFunction )";
        */String queryString = "select pdsfd from PhenoDataSetFieldDisplay pdsfd "
                + " where pdsfd.phenoDataSetGroup =:phenoDataSetGroup and phenoDataSetField.id in ( "
                + " SELECT id from PhenoDataSetField pdsf " + " where pdsf.study =:study "
                + " and lower(pdsf.name) in (:names) " + " and pdsf.arkFunction =:arkFunction )";
        Query query = getSession().createQuery(queryString);
        query.setParameter("study", study);
        // query.setParameterList("names", fieldNameCollection);
        query.setParameterList("names", lowerCaseNames);
        query.setParameter("arkFunction", arkFunction);
        query.setParameter("phenoDataSetGroup", phenoDataSetGroup);
        return query.list();
    }
}

From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java

License:Open Source License

@Override
public List<PhenoDataSetFieldDisplay> getPhenoFieldDisplaysIn(Study study, ArkFunction arkFunction) {
    String queryString = "select pdsfd from PhenoDataSetFieldDisplay pdsfd "
            + " where phenoDataSetField.id in ( " + " SELECT id from PhenoDataSetField pdsf "
            + " where pdsf.study =:study " + " and pdsf.arkFunction =:arkFunction )";
    Query query = getSession().createQuery(queryString);
    query.setParameter("study", study);
    query.setParameter("arkFunction", arkFunction);
    return query.list();
}

From source file:au.org.theark.phenotypic.model.dao.PhenotypicDao.java

License:Open Source License

public PhenoDataSetField getPhenoDataSetFieldByNameStudyPFG(String phenoFieldName, Study study,
        ArkFunction arkFunction, PhenoDataSetGroup phenoDataSetGroup)
        throws ArkRunTimeException, ArkSystemException {

    /*Query q = getSession().createQuery("Select customField from CustomField customField " +
                         " where customField.name =:customFieldName " +
                         " and lower(customField.study) =lower(:study) " +
                         " and customField.arkFunction =:arkFunction " +
                         " and exists (" +
                         "            from CustomFieldDisplay as customFieldDisplay " +
                         "            where customFieldDisplay.customField = customField " +
                         "            and customFieldDisplay.customFieldGroup =:customFieldGroup ) ");
    q.setParameter("customFieldName", phenoFieldName);
    q.setParameter("study", study);/*from w w  w .j  a v a 2s . co  m*/
    q.setParameter("arkFunction", arkFunction);
    q.setParameter("customFieldGroup", phenoDataSetGroup);*/

    Query q = getSession().createQuery("Select phenoDataSetField from PhenoDataSetField phenoDataSetField "
            + " where phenoDataSetField.name =:phenoDataSetField "
            + " and lower(phenoDataSetField.study) =lower(:study) "
            + " and phenoDataSetField.arkFunction =:arkFunction " + " and exists ("
            + "            from PhenoDataSetFieldDisplay as phenoDataSetFieldDisplay "
            + "            where phenoDataSetFieldDisplay.phenoDataSetField = phenoDataSetField "
            + "            and phenoDataSetFieldDisplay.phenoDataSetGroup =:phenoDataSetGroup ) ");
    q.setParameter("phenoDataSetField", phenoFieldName);
    q.setParameter("study", study);
    q.setParameter("arkFunction", arkFunction);
    q.setParameter("phenoDataSetGroup", phenoDataSetGroup);
    List<PhenoDataSetField> results = null;
    try {
        results = q.list();
    } catch (HibernateException hiberEx) {
        throw new ArkRunTimeException("Problem finding the phono data set fields.");
    }
    if (results.size() > 0) {
        return (PhenoDataSetField) results.get(0);
    }
    return null;
}

From source file:au.org.theark.report.model.dao.ReportDao.java

License:Open Source License

public List<LinkSubjectStudy> getSubjectsMatchingComponentConsent(ConsentDetailsReportVO cdrVO) {

    String qs = " select lss from LinkSubjectStudy lss " + "  left join fetch lss.person p "
            + "  left join fetch p.addresses a " + "  left join fetch p.phones ps "
            + "  left join fetch lss.consents c " + " where " + " lss.study =:study ";

    if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
        qs = qs + " and lss.id =:id ";

    }//ww w. jav a  2 s. c  o m
    if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
        qs = qs + " and lss.subjectStatus=:subjectStatus ";
    }

    Query query = getSession().createQuery((qs + "order by lss.id "));
    query.setParameter("study", cdrVO.getLinkSubjectStudy().getStudy());

    if (cdrVO.getLinkSubjectStudy().getSubjectUID() != null) {
        query.setParameter("id", cdrVO.getLinkSubjectStudy().getId());
    }
    if (cdrVO.getLinkSubjectStudy().getSubjectStatus() != null) {
        query.setParameter("subjectStatus", cdrVO.getLinkSubjectStudy().getSubjectStatus());
    }
    /*      next stage...just prefetch for now
            if (cdrVO.getConsentStatus() != null){
             q.setParameter("studyId", cdrVO.getLinkSubjectStudy().getStudy());   
          }
          if (cdrVO.getStudyComp() != null){
             q.setParameter("studyId", cdrVO.getLinkSubjectStudy().getStudy());
          }*/

    List<LinkSubjectStudy> results = query.list();
    return results;
}

From source file:au.org.theark.report.model.dao.ReportDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<StudyUserRolePermissionsDataRow> getStudyUserRolePermissions(Study study) {
    String sqlString = "SELECT * FROM `study`.`study_user_role_permission_view` WHERE studyName = :studyName";
    Query q = getSession().createSQLQuery(sqlString);
    q.setParameter("studyName", study.getName());
    q.setResultTransformer(Transformers.aliasToBean(StudyUserRolePermissionsDataRow.class));
    return q.list();
}

From source file:au.org.theark.study.model.dao.StudyDao.java

License:Open Source License

/**
 * Ensures that the lastname in the db is returned
 *///ww  w.j a  v a  2  s. com
public String getCurrentLastnameFromDB(Person person) {
    /*
     * Criteria criteria = getSession().createCriteria(PersonLastnameHistory.class);
     * 
     * if (person.getId() != null) { criteria.add(Restrictions.eq(au.org.theark.core.Constants.PERSON_SURNAME_HISTORY_PERSON, person)); }
     * criteria.addOrder(Order.desc("id")); PersonLastnameHistory personLastnameHistory = new PersonLastnameHistory(); if
     * (!criteria.list().isEmpty()) { personLastnameHistory = (PersonLastnameHistory) criteria.list().get(0); }
     * 
     * return personLastnameHistory.getLastName();
     */

    if (person.getId() != null) {
        Query q = getSession()
                .createQuery("Select person.lastName from Person person where person.id = :personId ");
        q.setParameter("personId", person.getId());

        List<String> results = q.list();
        if (!results.isEmpty()) {
            return (String) results.get(0);
        }
    }

    return null;
}

From source file:au.org.theark.study.model.dao.StudyDao.java

License:Open Source License

public Collection<ArkUser> lookupArkUser(Study study) {
    StringBuffer hqlQuery = new StringBuffer();
    hqlQuery.append("  select distinct arkUserObj from ArkUserRole as arkuserRole,ArkUser as arkUserObj ");
    hqlQuery.append("  where arkuserRole.study = ");
    hqlQuery.append(study.getId());/*from  ww  w .  j  a v  a 2s .  c o m*/
    hqlQuery.append(" and arkuserRole.arkUser.id = arkUserObj.id");
    org.hibernate.Query queryObject = getSession().createQuery(hqlQuery.toString());
    return queryObject.list();

}

From source file:au.org.theark.study.model.dao.StudyDao.java

License:Open Source License

/**
 * <p>/*from   w ww  .jav  a  2 s. co m*/
 * Builds a HQL to Left Join wtih SubjectCustomFieldData and applies a condition using the WITH clause to get a sub-set for the given Subject and
 * then applies the restrictions on study and module.
 * </p>
 */
public List<SubjectCustomFieldData> getSubjectCustomFieldDataList(LinkSubjectStudy linkSubjectStudyCriteria,
        ArkFunction arkFunction, CustomFieldCategory customFieldCategory, CustomFieldType customFieldType,
        int first, int count) {

    List<SubjectCustomFieldData> subjectCustomFieldDataList = new ArrayList<SubjectCustomFieldData>();

    StringBuffer sb = new StringBuffer();
    sb.append("SELECT cfd, fieldList");
    sb.append(" FROM  CustomFieldDisplay AS cfd ");
    sb.append("LEFT JOIN cfd.customField AS cf ");
    sb.append("LEFT JOIN cf.customFieldType AS cft ");
    sb.append("LEFT JOIN cfd.subjectCustomFieldData as fieldList ");
    sb.append(" with fieldList.linkSubjectStudy.id = :subjectId ");
    sb.append("  where cfd.customField.study.id = :studyId");
    sb.append(" and cfd.customField.arkFunction.id = :functionId");
    //Add new requirement for the category
    if (customFieldCategory != null) {
        sb.append(" and cfd.customField.customFieldCategory.id = :customFieldCategotyId");
    }
    //      if(type == null || "SUBJECT".equalsIgnoreCase(type)){
    sb.append(" and (cft is null or cft.name = :type)");
    //      }else{
    //         sb.append(" and cft.name = :type");
    //      }
    sb.append(" order by cfd.sequence");

    Query query = getSession().createQuery(sb.toString());
    query.setParameter("subjectId", linkSubjectStudyCriteria.getId());
    query.setParameter("studyId", linkSubjectStudyCriteria.getStudy().getId());
    query.setParameter("functionId", arkFunction.getId());
    //Add type and category
    if (customFieldCategory != null) {
        query.setParameter("customFieldCategotyId", customFieldCategory.getId());
    }
    query.setParameter("type", customFieldType.getName());
    query.setFirstResult(first);
    query.setMaxResults(count);

    List<Object[]> listOfObjects = query.list();
    for (Object[] objects : listOfObjects) {
        CustomFieldDisplay cfd = new CustomFieldDisplay();
        SubjectCustomFieldData scfd = new SubjectCustomFieldData();
        if (objects.length > 0 && objects.length >= 1) {

            cfd = (CustomFieldDisplay) objects[0];
            if (objects[1] != null) {
                scfd = (SubjectCustomFieldData) objects[1];
            } else {
                scfd.setCustomFieldDisplay(cfd);
            }

            subjectCustomFieldDataList.add(scfd);
        }
    }
    return subjectCustomFieldDataList;
}