Example usage for org.hibernate Query setParameter

List of usage examples for org.hibernate Query setParameter

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

Introduction

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

Prototype

@SuppressWarnings("unchecked")
Query<R> setParameter(int position, Object val);

Source Link

Document

Bind a positional query parameter using its inferred Type.

Usage

From source file:au.org.theark.lims.model.dao.InventoryDao.java

License:Open Source License

public BiospecimenLocationVO getInvCellLocation(InvCell invCell) throws ArkSystemException {
    BiospecimenLocationVO biospecimenLocationVo = new BiospecimenLocationVO();

    StringBuilder hqlString = new StringBuilder();
    hqlString.append(/* w ww  .j  a v a  2 s .com*/
            "SELECT site.name AS siteName, freezer.name as freezerName, rack.name AS rackName, box.name AS boxName, cell.colno AS column, cell.rowno AS row, box.colnotype.name AS colNoType, box.rownotype.name AS rowNoType \n");
    hqlString.append("FROM InvCell AS cell \n");
    hqlString.append("LEFT JOIN cell.invBox AS box \n");
    hqlString.append("LEFT JOIN box.invRack AS rack \n");
    hqlString.append("LEFT JOIN rack.invFreezer AS freezer \n");
    hqlString.append("LEFT JOIN freezer.invSite AS site \n");
    hqlString.append("WHERE cell.id = :id");

    Query q = getSession().createQuery(hqlString.toString());
    q.setParameter("id", invCell.getId());
    Object[] result = (Object[]) q.uniqueResult();

    if (result != null) {
        biospecimenLocationVo.setSiteName(result[0].toString());
        biospecimenLocationVo.setFreezerName(result[1].toString());
        biospecimenLocationVo.setRackName(result[2].toString());
        biospecimenLocationVo.setBoxName(result[3].toString());

        Long colno = new Long((Long) result[4]);
        Long rowno = new Long((Long) result[5]);
        biospecimenLocationVo.setColumn(colno);
        biospecimenLocationVo.setRow(rowno);

        String colNoType = result[6].toString();
        String rowNoType = result[7].toString();

        String colLabel = new String();
        if (colNoType.equalsIgnoreCase("ALPHABET")) {
            char character = (char) (colno + 64);
            colLabel = new Character(character).toString();
        } else {
            colLabel = new Integer(colno.intValue()).toString();
        }
        biospecimenLocationVo.setColLabel(colLabel);

        String rowLabel = new String();
        if (rowNoType.equalsIgnoreCase("ALPHABET")) {
            char character = (char) (rowno + 64);
            rowLabel = new Character(character).toString();
        } else {
            rowLabel = new Integer(rowno.intValue()).toString();
        }
        biospecimenLocationVo.setRowLabel(rowLabel);
    }
    return biospecimenLocationVo;
}

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;

    /*      //from   www. j a  va2 s . 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);/*  w  ww .j  a va2s  . c o  m*/
    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

/**
 * /*from  www .  ja va 2  s .com*/
 */
public List<List<String>> getPhenoDataAsMatrix(Study study, List<String> subjectUids,
        List<PhenoDataSetField> phenoDataSetFields, List<PhenoDataSetGroup> phenoDataSetGroups,
        PhenoDataSetCategory phenoDataSetCategory) {
    List<List<String>> dataSet = new ArrayList<List<String>>();
    StringBuffer dataHQLquery = new StringBuffer();
    StringBuffer noDataHQLquery = new StringBuffer();
    StringBuffer phenoFieldColumnSQL = new StringBuffer();
    List<String> header = new ArrayList<String>(0);

    //stringBuffer.append("SELECT data.* FROM (\n");
    //ARK-799
    //      dataHQLquery.append("SELECT lss.subjectUID, pc.recordDate, pc.description, \n"); 
    dataHQLquery.append("SELECT lss.subjectUID, pdsc.recordDate, \n");
    noDataHQLquery
            .append("SELECT lss.subjectUID, cast(null as char) AS recordDate, cast(null as char) AS name, \n");
    header.add("SUBJECTUID");
    header.add("RECORD_DATE");
    //ARK-799
    //      header.add("COLLECTION");

    // Loop for all custom goups
    for (PhenoDataSetGroup pdsg : phenoDataSetGroups) {
        // Get all custom fields for the group and create pivot SQL to create column
        //for(PhenoDataSetFieldDisplay pdsfd : getPhenoDataSetFieldDisplayForPhenoDataSetFieldGroup(pdsg)) {
        for (PhenoDataSetField pdsfd : getPhenoDataSetFieldsLinkedToPhenoDataSetFieldGroupAndPhenoDataSetCategory(
                pdsg, phenoDataSetCategory)) {

            //MAX(IF(custom_field_display_id = 14, pd.number_data_value, NULL)) AS cfd14,
            phenoFieldColumnSQL.append("(MAX(CASE WHEN pdsd.phenoDataSetFieldDisplay.id = ");
            phenoFieldColumnSQL
                    .append(getPhenoDataSetFieldDisplayByPhenoDataSetFieldAndGroup(pdsfd, pdsg).getId());

            // Determine field type and append SQL accordingly
            if (pdsfd.getFieldType().getName().equalsIgnoreCase(Constants.FIELD_TYPE_DATE)) {
                phenoFieldColumnSQL.append(" THEN pdsd.dateDataValue ELSE NULL END) ");
            }
            if (pdsfd.getFieldType().getName().equalsIgnoreCase(Constants.FIELD_TYPE_NUMBER)) {
                phenoFieldColumnSQL.append(" THEN pdsd.numberDataValue ELSE NULL END) ");
            }
            if (pdsfd.getFieldType().getName().equalsIgnoreCase(Constants.FIELD_TYPE_CHARACTER)) {
                phenoFieldColumnSQL.append(" THEN pdsd.textDataValue ELSE NULL END) ");
            }

            phenoFieldColumnSQL.append(") ");
            phenoFieldColumnSQL.append(",");

            noDataHQLquery.append("cast(null as char) ");
            noDataHQLquery.append(",");

            header.add(pdsfd.getName().toUpperCase());
        }
    }
    // Remove erroneous ',' char from end of strings
    if (phenoFieldColumnSQL.length() > 0) {
        phenoFieldColumnSQL.setLength(phenoFieldColumnSQL.length() - 1);
        noDataHQLquery.setLength(noDataHQLquery.length() - 1);
        dataHQLquery.append(phenoFieldColumnSQL);

        dataHQLquery.append("\nFROM \n");
        dataHQLquery.append(" PhenoDataSetData pdsd, ");
        dataHQLquery.append(" PhenoDataSetCollection pdsc, ");
        dataHQLquery.append(" LinkSubjectStudy lss, ");
        dataHQLquery.append(" PhenoDataSetFieldDisplay pdsfd \n");
        dataHQLquery.append(" WHERE pdsd.phenoDataSetCollection.id = pdsc.id \n");
        dataHQLquery.append(" AND pdsc.linkSubjectStudy.id = lss.id \n");
        dataHQLquery.append(" AND lss.study = :study \n");
        dataHQLquery.append(" AND lss.subjectUID IN (:subjectUids) \n");
        dataHQLquery.append(" AND pdsfd.phenoDataSetGroup in (:phenoDataSetGroups) \n");
        dataHQLquery.append(" AND pdsd.phenoDataSetFieldDisplay.id = pdsfd.id \n");
        dataHQLquery.append("GROUP BY lss.subjectUID, pdsd.phenoDataSetCollection");

        noDataHQLquery.append("\nFROM LinkSubjectStudy lss\n");
        noDataHQLquery.append("WHERE lss.study = :study \n");
        noDataHQLquery.append(
                "AND lss.id NOT IN (SELECT pdsc.linkSubjectStudy.id FROM PhenoDataSetCollection pdsc WHERE pdsc.questionnaire IN (:phenoDataSetGroups))\n");

        String hqlQuery = dataHQLquery.toString();

        Session session = getSession();

        Query dataQuery = session.createQuery(hqlQuery);
        dataQuery.setParameter("study", study);
        dataQuery.setParameterList("subjectUids", subjectUids);
        dataQuery.setParameterList("phenoDataSetGroups", phenoDataSetGroups);

        // Add header as first list item
        dataSet.add(header);
        // Add data
        //ArrayList<List<String>> dataList = new ArrayList<List<String>>();
        //dataList = (ArrayList<List<String>>) dataQuery.list();

        //This result set contains a List of Object arrayseach array represents one set of properties
        Iterator it = dataQuery.iterate();
        while (it.hasNext()) {
            Object[] val = (Object[]) it.next();
            List<String> stringList = new ArrayList<String>();
            for (Object o : val) {
                stringList.add(o != null ? o.toString() : new String());
            }
            dataSet.add(stringList);
        }

        hqlQuery = noDataHQLquery.toString();

        Query noDataQuery = session.createQuery(hqlQuery);
        noDataQuery.setParameter("study", study);
        noDataQuery.setParameterList("phenoDataSetGroups", phenoDataSetGroups);
        //noDataQuery.list();
        //dataSet.addAll(noDataQuery.list());
    }
    return dataSet;
}

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   ww w.  j av a  2s  .  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);/*w  w w .j ava 2s.com*/
    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 Long getWithoutStudyCompCount(Study study) {

    /*//from   w w w  .j  a  v  a2s. c  om
     * The following HQL implements this MySQL query: SELECT COUNT(*) FROM study.link_subject_study AS lss LEFT JOIN study.consent AS c ON lss.id =
     * c.subject_id -- this line is implicit from annotations on the entity classes WHERE lss.study_id = 2 AND c.id IS NULL;
     */
    String hqlString = "SELECT COUNT(*) FROM LinkSubjectStudy AS lss \n" + "LEFT JOIN lss.consents AS c \n"
            + "WHERE lss.study = :study \n" + "AND c.id IS NULL";
    Query q = getSession().createQuery(hqlString);
    // if (hqlString.contains(":study_id")) {
    // q.setParameter("study_id", study.getId());
    // }
    // if (hqlString.contains(":study")) {
    q.setParameter("study", study);
    // }
    Long undefCount = (Long) q.uniqueResult();

    return undefCount;
}

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 ";

    }/*from w  w w  . j  a v a2s .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();
}