Example usage for org.hibernate SQLQuery setParameterList

Introduction

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

Prototype

@Override
    NativeQuery<T> setParameterList(String name, Object[] values);

Source Link

Usage

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public Map<Integer, List<String>> getCharacterTraitInfoValues(final List<Integer> environmentIds,
        final List<CharacterTraitInfo> traitInfoList) {

    final Map<Integer, List<String>> traitValues = new HashMap<>();

    // Get trait IDs
    final List<Integer> traitIds = new ArrayList<>();
    for (final CharacterTraitInfo trait : traitInfoList) {
        traitIds.add(trait.getId());//  w w  w  . ja  v  a2  s  . co  m
    }

    try {
        final SQLQuery query = this.getSession()
                .createSQLQuery("SELECT DISTINCT p.observable_id, p.value " + "FROM phenotype p "
                        + "    INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id "
                        + "WHERE e.nd_geolocation_id IN (:environmentIds) "
                        + "    AND p.observable_id IN (:traitIds) " + "ORDER BY p.observable_id ");
        query.setParameterList("environmentIds", environmentIds);
        query.setParameterList(TRAIT_IDS, traitIds);

        List<Object[]> list = new ArrayList<>();

        if (!environmentIds.isEmpty() && !traitIds.isEmpty()) {
            list = query.list();
        }

        for (final Object[] row : list) {
            final Integer traitId = (Integer) row[0];
            final String value = (String) row[1];

            List<String> values = new ArrayList<>();
            values.add(value);
            // If the trait exists in the map, add the value found. Else, just add the <trait, values> pair.
            if (traitValues.containsKey(traitId)) {
                values = traitValues.get(traitId);
                values.add(value);
                traitValues.remove(traitId);
            }
            traitValues.put(traitId, values);
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at getCharacterTraitInfoValues() query on PhenotypeDao: " + e.getMessage(), e);
    }
    return traitValues;

}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public void setCategoricalTraitInfoValues(final List<CategoricalTraitInfo> traitInfoList,
        final List<Integer> environmentIds) {

    // Get trait IDs
    final List<Integer> traitIds = new ArrayList<>();
    for (final CategoricalTraitInfo trait : traitInfoList) {
        traitIds.add(trait.getId());//  ww w  .  j av  a 2 s .  co m
    }

    try {
        final SQLQuery query = this.getSession()
                .createSQLQuery("SELECT p.observable_id, p.cvalue_id, COUNT(p.phenotype_id) AS valuesCount "
                        + "FROM phenotype p "
                        + "INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id "
                        + "WHERE p.cvalue_id IS NOT NULL AND p.observable_id IN (:traitIds) "
                        + "  AND e.nd_geolocation_id IN (:environmentIds) "
                        + "GROUP BY p.observable_id, p.cvalue_id ");
        query.setParameterList(TRAIT_IDS, traitIds);
        query.setParameterList("environmentIds", environmentIds);

        List<Object[]> list = new ArrayList<>();

        if (!environmentIds.isEmpty() && !traitIds.isEmpty()) {
            list = query.list();
        }

        for (final Object[] row : list) {
            final Integer traitId = (Integer) row[0];
            final Integer cValueId = (Integer) row[1];
            final Long count = ((BigInteger) row[2]).longValue();

            for (final CategoricalTraitInfo traitInfo : traitInfoList) {
                if (traitInfo.getId() == traitId) {
                    traitInfo.addValueCount(new CategoricalValue(cValueId), count.longValue());
                    break;
                }
            }

        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at getCategoricalTraitInfoValues() query on PhenotypeDao: " + e.getMessage(), e);
    }

}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public List<Observation> getObservationForTraitOnGermplasms(final List<Integer> traitIds,
        final List<Integer> germplasmIds, final List<Integer> environmentIds) {
    final List<Observation> observationFinal = new ArrayList<>();

    try {/*from w w w  . j a  v  a  2s.  co  m*/
        final StringBuilder sb = new StringBuilder(PhenotypeDao.GET_OBSERVATIONS);
        sb.append(" AND s.dbxref_id IN (:germplasmIds) ");
        sb.append(PhenotypeDao.ORDER_BY_OBS);
        final SQLQuery query = this.getSession().createSQLQuery(sb.toString());
        query.setParameterList(TRAIT_IDS, traitIds);
        query.setParameterList("germplasmIds", germplasmIds);
        query.setParameterList("environmentIds", environmentIds);

        List<Object[]> list = new ArrayList<>();

        if (!environmentIds.isEmpty() && !traitIds.isEmpty()) {
            list = query.list();
        }

        for (final Object[] row : list) {
            final Integer traitId = (Integer) row[0];
            final Integer germplasmId = (Integer) row[1];
            final Integer environmentId = (Integer) row[2];
            final String value = (String) row[3];

            final ObservationKey rowKey = new ObservationKey(traitId, germplasmId, environmentId);
            final Observation observation = new Observation(rowKey, value);
            observationFinal.add(observation);
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at getObservationForTraitOnGermplasms() query on PhenotypeDao: " + e.getMessage(), e);
    }

    return observationFinal;
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public long countObservationForTraits(final List<Integer> traitIds, final List<Integer> environmentIds) {

    try {//from ww  w. ja v  a  2s .  c  o m
        final SQLQuery query = this.getSession().createSQLQuery(PhenotypeDao.COUNT_OBSERVATIONS);
        query.setParameterList(TRAIT_IDS, traitIds);
        query.setParameterList("environmentIds", environmentIds);
        return ((BigInteger) query.uniqueResult()).longValue();

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at countObservationForTraits() query on PhenotypeDao: " + e.getMessage(), e);
    }
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public List<Observation> getObservationForTraits(final List<Integer> traitIds,
        final List<Integer> environmentIds, final int start, final int numOfRows) {

    final List<Observation> toReturn = new ArrayList<>();

    try {// w  w  w . j  ava2 s  .  co m
        final StringBuilder sb = new StringBuilder(PhenotypeDao.GET_OBSERVATIONS);
        sb.append(PhenotypeDao.ORDER_BY_OBS);
        final SQLQuery query = this.getSession().createSQLQuery(sb.toString());

        query.setParameterList(TRAIT_IDS, traitIds);
        query.setParameterList("environmentIds", environmentIds);
        this.setStartAndNumOfRows(query, start, numOfRows);
        final List<Object[]> list = query.list();

        for (final Object[] row : list) {
            final Integer traitId = (Integer) row[0];
            final Integer germplasmId = (Integer) row[1];
            final Integer environmentId = (Integer) row[2];
            final String value = (String) row[3];

            toReturn.add(new Observation(new ObservationKey(traitId, germplasmId, environmentId), value));

        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(
                "Error at getObservationForTraits() query on PhenotypeDao: " + e.getMessage(), e);
    }
    return toReturn;
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public void deletePhenotypesByProjectIdAndVariableIds(final Integer projectId,
        final List<Integer> variableIds) {
    try {/*w  w  w. ja  v a2  s.  com*/
        // Delete phenotypes
        final String sql = "delete pheno " + " from nd_experiment e," + "  phenotype pheno"
                + " where e.project_id = :projectId " + " and pheno.observable_id IN (:variableIds) "
                + " and e.nd_experiment_id = pheno.nd_experiment_id";
        final SQLQuery statement = this.getSession().createSQLQuery(sql);
        statement.setParameter("projectId", projectId);
        statement.setParameterList("variableIds", variableIds);
        statement.executeUpdate();

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException("Error in deletePhenotypesByProjectIdAndVariableIds=" + projectId
                + ", " + variableIds + IN_PHENOTYPE_DAO + e.getMessage(), e);
    }
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public List<Object[]> getPhenotypeIdsByLocationAndPlotNo(final Integer projectId, final Integer locationId,
        final Integer plotNo, final List<Integer> cvTermIds) {
    try {/*from   ww w  .  j ava2  s.  c  om*/
        if (cvTermIds.isEmpty()) {
            return new ArrayList<>();
        }

        // get the phenotype_id
        final String sql = "SELECT  expprop.value, pheno.observable_id, pheno.phenotype_id FROM "
                + "nd_experiment exp "
                + "INNER JOIN nd_experimentprop expprop ON expprop.nd_experiment_id = exp.nd_experiment_id "
                + "INNER JOIN phenotype pheno ON exp.nd_experiment_id = pheno.nd_experiment_id "
                + "WHERE exp.project_id = :projectId " + "AND exp.nd_geolocation_id = :locationId "
                + "AND pheno.observable_id IN (:cvTermIds) " + "AND expprop.value = :plotNo "
                + "AND exp.type_id = 1155 " + "AND expprop.type_id in (8200, 8380)";

        final SQLQuery statement = this.getSession().createSQLQuery(sql);
        statement.setParameter(PROJECT_ID, projectId);
        statement.setParameter("locationId", locationId);
        statement.setParameterList(CV_TERM_IDS, cvTermIds);
        statement.setParameter("plotNo", plotNo);

        return statement.list();

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException("Error in getPhenotypeIdsByLocationAndPlotNo=" + projectId + ", "
                + locationId + IN_PHENOTYPE_DAO + e.getMessage(), e);
    }
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public List<PhenotypeSearchDTO> searchPhenotypes(final Integer pageSize, final Integer pageNumber,
        final PhenotypeSearchRequestDTO requestDTO) {
    final StringBuilder queryString = new StringBuilder(PhenotypeQuery.PHENOTYPE_SEARCH);

    addPhenotypeSearchFilter(requestDTO, queryString);

    final SQLQuery sqlQuery = this.getSession().createSQLQuery(queryString.toString());

    if (pageNumber != null && pageSize != null) {
        sqlQuery.setFirstResult(pageSize * (pageNumber - 1));
        sqlQuery.setMaxResults(pageSize);
    }//ww w . j ava2  s.  com

    addPhenotypeSearchQueryParams(requestDTO, sqlQuery);

    sqlQuery.addScalar("nd_experiment_id").addScalar("observationUnitDbId", new StringType())
            .addScalar("observationUnitName").addScalar("observationLevel")
            .addScalar("plantNumber", new IntegerType()).addScalar("germplasmDbId", new StringType())
            .addScalar("germplasmName").addScalar("studyDbId", new StringType()).addScalar("studyName")
            .addScalar("programName").addScalar("FieldMapRow").addScalar("FieldMapCol")
            .addScalar("plotNumber", new StringType()).addScalar("blockNumber", new StringType())
            .addScalar("replicate", new StringType()).addScalar("COL").addScalar("ROW")
            .addScalar("studyLocationDbId", new StringType()).addScalar("studyLocation", new StringType())
            .addScalar("entryType").addScalar("entryNumber", new StringType());

    // TODO get map with AliasToEntityMapResultTransformer.INSTANCE
    final List<Object[]> results = sqlQuery.list();

    final Map<Integer, PhenotypeSearchDTO> observationUnitsByNdExpId = new LinkedHashMap<>();

    if (results != null && !results.isEmpty()) {

        // Process ObservationUnits (Measurement row)
        for (final Object[] row : results) {
            final PhenotypeSearchDTO observationUnit = new PhenotypeSearchDTO();

            final Integer ndExperimentId = (Integer) row[0];
            observationUnit.setObservationUnitDbId((String) row[1]); // OBS_UNIT_ID
            observationUnit.setObservationUnitName((String) row[2]);
            observationUnit.setObservationLevel((String) row[3]);
            observationUnit.setObservationLevels("1");
            observationUnit.setPlantNumber((String) row[4]);
            observationUnit.setGermplasmDbId((String) row[5]);
            observationUnit.setGermplasmName((String) row[6]);
            observationUnit.setStudyDbId((String) row[7]);
            observationUnit.setStudyName((String) row[8]);
            observationUnit.setProgramName((String) row[9]);
            String x = (String) row[15]; // ROW
            String y = (String) row[16]; // COL
            if (StringUtils.isBlank(x) || StringUtils.isBlank(y)) {
                x = (String) row[10]; // fieldMapRow
                y = (String) row[11]; // fieldMapCol
            }
            observationUnit.setX(x);
            observationUnit.setY(y);
            observationUnit.setPlotNumber((String) row[12]);
            observationUnit.setBlockNumber((String) row[13]);
            observationUnit.setReplicate((String) row[14]);
            observationUnit.setStudyLocationDbId((String) row[17]);
            observationUnit.setStudyLocation((String) row[18]);
            observationUnit.setEntryType((String) row[19]);
            observationUnit.setEntryNumber((String) row[20]);

            observationUnitsByNdExpId.put(ndExperimentId, observationUnit);
        }

        // Get observations (Traits)
        final SQLQuery observationsQuery = this.getSession()
                .createSQLQuery(PhenotypeQuery.PHENOTYPE_SEARCH_OBSERVATIONS);
        observationsQuery.setParameterList("ndExperimentIds", observationUnitsByNdExpId.keySet());
        observationsQuery.addScalar("expid").addScalar("phen_id").addScalar("cvterm_id")
                .addScalar("cvterm_name", new StringType()).addScalar("value", new StringType())
                .addScalar("crop_ontology_id", new StringType()).addScalar("updated_date");
        final List<Object[]> observationResults = observationsQuery.list();

        for (final Object[] result : observationResults) {
            final Integer ndExperimentId = (Integer) result[0];

            final PhenotypeSearchObservationDTO observation = new PhenotypeSearchObservationDTO();
            final String variableId = (result[5] != null && !((String) result[5]).isEmpty())
                    ? (String) result[5]
                    : String.valueOf(result[2]);
            observation.setObservationVariableDbId(variableId);
            observation.setObservationVariableName((String) result[3]);
            observation.setObservationDbId((Integer) result[1]);
            observation.setValue((String) result[4]);
            observation.setObservationTimeStamp(
                    Util.formatDateAsStringValue((Date) result[6], Util.FRONTEND_TIMESTAMP_FORMAT));
            // TODO
            observation.setSeason(StringUtils.EMPTY);
            observation.setCollector(StringUtils.EMPTY);

            final PhenotypeSearchDTO observationUnit = observationUnitsByNdExpId.get(ndExperimentId);
            observationUnit.getObservations().add(observation);
        }
    }

    return new ArrayList<>(observationUnitsByNdExpId.values());
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

private static void addPhenotypeSearchQueryParams(final PhenotypeSearchRequestDTO requestDTO,
        final SQLQuery sqlQuery) {

    final List<String> cvTermIds = requestDTO.getObservationVariableDbIds();

    if (cvTermIds != null && !cvTermIds.isEmpty()) {
        sqlQuery.setParameterList(CV_TERM_IDS, cvTermIds);
    }/*from w w w .  ja v a2s .  c  o  m*/

    if (requestDTO.getStudyDbIds() != null && !requestDTO.getStudyDbIds().isEmpty()) {
        sqlQuery.setParameterList("studyDbIds", requestDTO.getStudyDbIds());
    }

    if (requestDTO.getObservationLevel() != null) {
        sqlQuery.setParameter("datasetType", requestDTO.getObservationLevel());
    }

    if (requestDTO.getObservationTimeStampRangeStart() != null) {
        sqlQuery.setParameter("observationTimeStampRangeStart", requestDTO.getObservationTimeStampRangeStart());
    }

    if (requestDTO.getObservationTimeStampRangeEnd() != null) {
        sqlQuery.setParameter("observationTimeStampRangeEnd", requestDTO.getObservationTimeStampRangeEnd());
    }

    if (requestDTO.getLocationDbIds() != null && !requestDTO.getLocationDbIds().isEmpty()) {
        sqlQuery.setParameterList("locationDbIds", requestDTO.getLocationDbIds());
    }

    if (requestDTO.getGermplasmDbIds() != null && !requestDTO.getGermplasmDbIds().isEmpty()) {
        sqlQuery.setParameterList("germplasmDbIds", requestDTO.getGermplasmDbIds());
    }
    if (requestDTO.getProgramDbIds() != null && !requestDTO.getProgramDbIds().isEmpty()) {
        sqlQuery.setParameterList("programDbIds", requestDTO.getProgramDbIds());
    }

    if (requestDTO.getTrialDbIds() != null && !requestDTO.getTrialDbIds().isEmpty()) {
        sqlQuery.setParameterList("trialDbIds", requestDTO.getTrialDbIds());
    }
}

---

From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java

License:Open Source License

public void updateOutOfSyncPhenotypes(final Set<Integer> experimentIds, final Set<Integer> targetVariableIds) {
    final String sql = "UPDATE nd_experiment experiment\n"
            + "LEFT JOIN nd_experiment experimentParent ON experimentParent.nd_experiment_id = experiment.parent_id\n"
            + "INNER JOIN phenotype pheno ON  pheno.nd_experiment_id = experimentParent.nd_experiment_id OR pheno.nd_experiment_id = experiment.nd_experiment_id\n"
            + "SET pheno.status = :status \n"
            + "WHERE experiment.nd_experiment_id in (:experimentIds)  AND pheno.observable_id in (:variableIds) ;";

    final SQLQuery statement = this.getSession().createSQLQuery(sql);
    statement.setParameter("status", Phenotype.ValueStatus.OUT_OF_SYNC.getName());
    statement.setParameterList("experimentIds", experimentIds);
    statement.setParameterList("variableIds", targetVariableIds);
    statement.executeUpdate();/*  www .  ja  va  2s  .  c o m*/
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »