Example usage for org.hibernate Query setParameterList

Introduction

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

Prototype

Query<R> setParameterList(int position, Object[] values);

Source Link

Usage

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

License:Open Source License

public BioCollectionCustomFieldData getBioCollectionCustomFieldData(BioCollection bioCollectionCriteria,
        ArkFunction arkFunction, String customFieldName) {
    StringBuffer sb = new StringBuffer();
    sb.append(" FROM  CustomFieldDisplay AS cfd ");
    sb.append("LEFT JOIN cfd.bioCollectionCustomFieldData as fieldList ");
    sb.append(" WITH fieldList.bioCollection.id = :bioCollectionId ");
    sb.append("  WHERE cfd.customField.study.id IN (:studyId)");
    sb.append(" AND cfd.customField.arkFunction.id = :functionId");
    sb.append(" AND cfd.customField.name = :customFieldName");
    sb.append(" ORDER BY cfd.sequence");

    Query query = getSession().createQuery(sb.toString());
    query.setParameter("bioCollectionId", bioCollectionCriteria.getId());

    // Allow child studies to inherit parent defined custom fields
    List studyList = new ArrayList();
    studyList.add(bioCollectionCriteria.getStudy().getId());
    if (bioCollectionCriteria.getStudy().getParentStudy() != null
            && bioCollectionCriteria.getStudy().getParentStudy() != bioCollectionCriteria.getStudy()) {
        studyList.add(bioCollectionCriteria.getStudy().getParentStudy().getId());
    }//w  w w.jav  a 2  s.  c  om
    query.setParameterList("studyId", studyList);

    query.setParameter("functionId", arkFunction.getId());
    query.setParameter("customFieldName", customFieldName);

    BioCollectionCustomFieldData bccfd = new BioCollectionCustomFieldData();
    List<Object[]> listOfObjects = query.list();
    for (Object[] objects : listOfObjects) {
        CustomFieldDisplay cfd = new CustomFieldDisplay();

        if (objects.length > 0 && objects.length >= 1) {

            cfd = (CustomFieldDisplay) objects[0];
            if (objects[1] != null) {
                bccfd = (BioCollectionCustomFieldData) objects[1];
            } else {
                bccfd.setCustomFieldDisplay(cfd);
            }
        }
    }
    return bccfd;
}

---

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

License:Open Source License

public Long isCustomFieldUsed(BioCollectionCustomFieldData bioCollectionCFData) {
    Long count = new Long("0");
    CustomField customField = bioCollectionCFData.getCustomFieldDisplay().getCustomField();

    // The Study// w  w w .  ja va 2s  .c  o m
    try {
        Long id = bioCollectionCFData.getBioCollection().getId();
        BioCollection bioCollection = getBioCollection(id);
        //Study subjectStudy = bioCollection.getStudy();
        ArkFunction arkFunction = customField.getArkFunction();

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append(" SELECT COUNT(*) FROM BioCollectionCustomFieldData AS bccfd WHERE EXISTS ");
        stringBuffer.append(" ( ");
        stringBuffer.append("  SELECT cfd.id ");
        stringBuffer.append("  FROM CustomFieldDisplay AS cfd ");
        stringBuffer.append("  WHERE cfd.customField.study IN (:studyId)");
        stringBuffer.append(
                "  AND cfd.customField.arkFunction.id = :functionId AND bccfd.customFieldDisplay.id = :customFieldDisplayId");
        stringBuffer.append(" )");

        String theHQLQuery = stringBuffer.toString();

        Query query = getSession().createQuery(theHQLQuery);
        //query.setParameter("studyId", subjectStudy.getId());
        List studyList = new ArrayList();
        studyList.add(bioCollection.getStudy());
        if (bioCollection.getStudy().getParentStudy() != null
                && bioCollection.getStudy().getParentStudy() != bioCollection.getStudy()) {
            studyList.add(bioCollection.getStudy().getParentStudy());
        }
        query.setParameterList("studyId", studyList);
        query.setParameter("functionId", arkFunction.getId());
        query.setParameter("customFieldDisplayId", bioCollectionCFData.getCustomFieldDisplay().getId());
        count = (Long) query.uniqueResult();

    } catch (EntityNotFoundException e) {
        //The given BioCollection is not available, this should not happen since the person is editing custom fields for the LIMS collection
        e.printStackTrace();
    }

    return count;
}

---

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

License:Open Source License

public List<BiospecimenCustomFieldData> getBiospecimenCustomFieldDataList(Biospecimen biospecimenCriteria,
        ArkFunction arkFunction, int first, int count) {
    List<BiospecimenCustomFieldData> biospecimenCustomFieldDataList = new ArrayList<BiospecimenCustomFieldData>();

    StringBuffer sb = new StringBuffer();
    sb.append(" FROM  CustomFieldDisplay AS cfd ");
    sb.append("LEFT JOIN cfd.biospecimenCustomFieldData as fieldList ");
    sb.append(" with fieldList.biospecimen.id = :biospecimenId ");
    sb.append("  where cfd.customField.study.id IN (:studyId)");
    sb.append(" and cfd.customField.arkFunction.id = :functionId");
    sb.append(" order by cfd.sequence");

    Query query = getSession().createQuery(sb.toString());
    query.setParameter("biospecimenId", biospecimenCriteria.getId());

    // Allow child studies to inherit parent defined custom fields
    List studyList = new ArrayList();
    studyList.add(biospecimenCriteria.getStudy().getId());
    if (biospecimenCriteria.getStudy().getParentStudy() != null
            && biospecimenCriteria.getStudy().getParentStudy() != biospecimenCriteria.getStudy()) {
        studyList.add(biospecimenCriteria.getStudy().getParentStudy().getId());
    }/*w  ww.ja  v a  2s .  c o m*/
    query.setParameterList("studyId", studyList);
    query.setParameter("functionId", arkFunction.getId());
    query.setFirstResult(first);
    query.setMaxResults(count);

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

            cfd = (CustomFieldDisplay) objects[0];
            if (objects[1] != null) {
                bscfd = (BiospecimenCustomFieldData) objects[1];
            } else {
                bscfd.setCustomFieldDisplay(cfd);
            }
            biospecimenCustomFieldDataList.add(bscfd);
        }
    }
    return biospecimenCustomFieldDataList;
}

---

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

License:Open Source License

public List<InvRack> searchInvRack(InvRack invRack, List<Study> studyListForUser) throws ArkSystemException {
    StringBuilder hqlString = new StringBuilder();
    hqlString.append("FROM InvRack AS rack \n");
    //Added new condition to not showing the fully occupied racks to change the box.
    //hqlString.append("WHERE invFreezer.id IN (SELECT id FROM InvFreezer AS freezer \n");
    hqlString.append("WHERE rack.available <> 0 AND invFreezer.id IN (SELECT id FROM InvFreezer AS freezer \n");
    hqlString.append(//  ww w.  ja  v  a 2 s  .  co  m
            "                        WHERE freezer.invSite.id IN (SELECT invSite.id FROM StudyInvSite \n");
    hqlString.append("                                                      WHERE study IN (:studies)))");

    Query q = getSession().createQuery(hqlString.toString());
    q.setParameterList("studies", studyListForUser);

    List<InvRack> list = q.list();
    return list;
}

---

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

License:Open Source License

public List<InvFreezer> searchInvFreezer(InvFreezer invFreezer, List<Study> studyListForUser)
        throws ArkSystemException {
    StringBuilder hqlString = new StringBuilder();
    hqlString.append("FROM InvFreezer AS freezer \n");
    hqlString.append("WHERE freezer.invSite.id IN (SELECT invSite.id FROM StudyInvSite \n");
    hqlString.append("                     WHERE study IN (:studies))");

    Query q = getSession().createQuery(hqlString.toString());
    q.setParameterList("studies", studyListForUser);

    List<InvFreezer> list = q.list();
    return list;//from  ww w.  j av a  2 s .c om
}

---

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

License:Open Source License

/**
 * /*from  w w w .  j  a  v  a  2s  . c o  m*/
 */
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 {//  w w  w. j av a2s . 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:baking.dao.BaseDao.java

License:Open Source License

/**
 * ?count(*)where in?hql??//w  ww  .  j a  v a 2s .c  o m
 * 
 * @param hql the hql
 * 
 * @return the toatal by hql
 */
public int countHql(String hql, String name, List vals) {
    //
    Query query = getSession().createQuery(hql);
    //??
    query.setParameterList(name, vals);
    Long result = (Long) query.uniqueResult();
    if (result != null) {
        return result.intValue();
    }
    return 0;
    //return total;
}

---

From source file:br.com.fatecmogidascruzes.saph.dao.AlternativeDAO.java

@Override
public List<EvaluatedItem> getEvaluatedItemsByAbility(Alternative alternative, Ability ability) {

    session = HSession.getSession();/*from ww  w . jav a  2 s.  c  om*/
    String hql = "from EvaluatedItem ev WHERE ev IN (:evList) AND ev.ability.id = :abId";
    Query q = session.createQuery(hql);
    q.setParameterList("evList", alternative.getEvaluatedItems());
    q.setParameter("abId", ability.getId());

    List entities = q.list();
    session.close();
    return entities;

}

---

From source file:br.com.fatecmogidascruzes.saph.dao.AlternativeDAO.java

@Override
public List<EvaluatedItem> getEvaluatedItemsByPerformance(Alternative alternative, Performance performance) {

    session = HSession.getSession();/*  w  w w.  jav a 2 s.c  o m*/
    String hql = "from EvaluatedItem ev WHERE ev IN (:evList) AND ev.performance = :performance";
    Query q = session.createQuery(hql);
    q.setParameterList("evList", alternative.getEvaluatedItems());
    q.setParameter("performance", performance);

    List entities = q.list();
    session.close();
    return entities;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »