List of usage examples for org.hibernate SQLQuery setParameterList
@Override
NativeQuery<T> setParameterList(String name, Object[] values);
From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java
License:Open Source License
private List<Map<String, Object>> getObservationUnitsQueryResult(final int datasetId, final List<MeasurementVariableDto> selectionMethodsAndTraits, final List<String> observationUnitIds) { try {// www . ja v a 2s .c om final String observationUnitTableQuery = this.getObservationUnitsQuery(selectionMethodsAndTraits); final SQLQuery query = this.createQueryAndAddScalar(selectionMethodsAndTraits, observationUnitTableQuery); query.setParameter("datasetId", datasetId); query.setParameterList("observationUnitIds", observationUnitIds); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = query.list(); return results; } catch (final Exception e) { final String error = "An internal error has ocurred when trying to execute the operation"; ExperimentDao.LOG.error(error); throw new MiddlewareException(error); } }
From source file:org.generationcp.middleware.dao.dms.ExperimentDao.java
License:Open Source License
public Map<Integer, Map<String, List<Object>>> getValuesFromObservations(final int studyId, final List<Integer> datasetTypeIds, final Map<Integer, Integer> inputVariableDatasetMap) { final StringBuilder queryString = new StringBuilder("SELECT \n" + "CASE WHEN e.parent_id IS NULL THEN e.nd_experiment_id ELSE e.parent_id END as `experimentId`,\n" + "p.observable_id as `variableId`, \n" + "p.value \n" + "FROM nd_experiment e \n" + "INNER JOIN project proj ON proj.project_id = e.project_id AND proj.study_id = :studyId \n" + "INNER JOIN phenotype p ON p.nd_experiment_id = e.nd_experiment_id \n" + "WHERE proj.dataset_type_id IN (:datasetTypeIds) "); if (!inputVariableDatasetMap.isEmpty()) { queryString.append("AND ("); final Iterator<Map.Entry<Integer, Integer>> iterator = inputVariableDatasetMap.entrySet().iterator(); while (iterator.hasNext()) { final Map.Entry<Integer, Integer> entry = iterator.next(); queryString.append(String.format("(p.observable_id = %s AND e.project_id = %s %n)", entry.getKey(), entry.getValue()));/*ww w .ja v a 2 s .c o m*/ if (iterator.hasNext()) { queryString.append(" OR "); } else { queryString.append(") \n"); } } } queryString.append("ORDER BY `experimentId`, `variableId` ;"); final SQLQuery q = this.getSession().createSQLQuery(queryString.toString()); q.addScalar("experimentId", new IntegerType()); q.addScalar("variableId", new StringType()); q.addScalar("value", new StringType()); q.setParameter("studyId", studyId); q.setParameterList("datasetTypeIds", datasetTypeIds); q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = q.list(); final Map<Integer, Map<String, List<Object>>> map = new HashMap<>(); for (final Map<String, Object> row : results) { final Integer experimentId = (Integer) row.get("experimentId"); final String variableId = (String) row.get("variableId"); final Object value = row.get("value"); if (!map.containsKey(experimentId)) { map.put(experimentId, new HashMap<String, List<Object>>()); } if (!map.get(experimentId).containsKey(variableId)) { map.get(experimentId).put(variableId, new ArrayList<Object>()); } // Group values per variable and experimentId. map.get(experimentId).get(variableId).add(value); } return map; }
From source file:org.generationcp.middleware.dao.dms.GeolocationDao.java
License:Open Source License
@SuppressWarnings("unchecked") public List<TrialEnvironment> getTrialEnvironmentDetails(final Set<Integer> environmentIds) { final List<TrialEnvironment> environmentDetails = new ArrayList<>(); if (environmentIds.isEmpty()) { return environmentDetails; }// w w w . ja va 2 s .co m try { // Get location name, study id and study name final String sql = "SELECT DISTINCT e.nd_geolocation_id, l.lname, gp.value, p.project_id, p.name, p.description, prov.lname as provinceName, c.isoabbr " + "FROM nd_experiment e " + " LEFT JOIN nd_geolocationprop gp ON e.nd_geolocation_id = gp.nd_geolocation_id " + " AND gp.type_id = " + TermId.LOCATION_ID.getId() + " LEFT JOIN location l ON l.locid = gp.value " + " LEFT JOIN location prov ON prov.locid = l.snl1id " + " LEFT JOIN cntry c ON l.cntryid = c.cntryid " + " INNER JOIN project ds ON ds.project_id = e.project_id " + " INNER JOIN project p ON p.project_id = ds.study_id " + " WHERE e.nd_geolocation_id IN (:locationIds) "; final SQLQuery query = this.getSession().createSQLQuery(sql); query.setParameterList("locationIds", environmentIds); query.addScalar("nd_geolocation_id", Hibernate.INTEGER); query.addScalar("lname", Hibernate.STRING); query.addScalar("value", Hibernate.INTEGER); query.addScalar(GeolocationDao.PROJECT_ID, Hibernate.INTEGER); query.addScalar("name", Hibernate.STRING); query.addScalar(GeolocationDao.DESCRIPTION, Hibernate.STRING); query.addScalar(GeolocationDao.PROVINCE_NAME, Hibernate.STRING); query.addScalar(GeolocationDao.ISOABBR, Hibernate.STRING); final List<Integer> locIds = new ArrayList<>(); final List<Object[]> result = query.list(); for (final Object[] row : result) { final Integer environmentId = (Integer) row[0]; final String locationName = (String) row[1]; final Integer locId = (Integer) row[2]; final Integer studyId = (Integer) row[3]; final String studyName = (String) row[4]; final String studyDescription = (String) row[5]; final String provinceName = (String) row[6]; final String countryName = (String) row[7]; environmentDetails.add(new TrialEnvironment(environmentId, new LocationDto(locId, locationName, provinceName, countryName), new StudyReference(studyId, studyName, studyDescription))); locIds.add(locId); } } catch (final HibernateException e) { final String errorMessage = "Error at getTrialEnvironmentDetails=" + environmentIds + GeolocationDao.AT_GEOLOCATION_DAO + e.getMessage(); GeolocationDao.LOG.error(errorMessage, e); throw new MiddlewareQueryException(errorMessage, e); } return environmentDetails; }
From source file:org.generationcp.middleware.dao.dms.GeolocationDao.java
License:Open Source License
@SuppressWarnings("unchecked") public TrialEnvironments getEnvironmentsForTraits(final List<Integer> traitIds, final String programUUID) { final TrialEnvironments environments = new TrialEnvironments(); try {/*from w w w . jav a 2 s . c o m*/ final String sql = "SELECT DISTINCT gp.nd_geolocation_id as envtId, l.lname as locationName, prov.lname as provinceName, c.isoabbr, p.project_id, p.name, gp.value as locationId" + " FROM nd_experiment e " + " INNER JOIN project ds ON ds.project_id = e.project_id " + " INNER JOIN project p ON p.project_id = ds.study_id " + " INNER JOIN phenotype ph ON ph.nd_experiment_id = e.nd_experiment_id" + " INNER JOIN nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id AND gp.type_id = " + TermId.LOCATION_ID.getId() + " LEFT JOIN location l ON l.locid = gp.value" + " LEFT JOIN location prov ON prov.locid = l.snl1id" + " LEFT JOIN cntry c ON c.cntryid = l.cntryid" + " WHERE ph.observable_id IN (:traitIds) AND p.program_uuid = :programUUID ;"; final SQLQuery query = this.getSession().createSQLQuery(sql); query.addScalar(GeolocationDao.ENVT_ID); query.addScalar(GeolocationDao.LOCATION_NAME); query.addScalar(GeolocationDao.PROVINCE_NAME); query.addScalar(GeolocationDao.ISOABBR); query.addScalar(GeolocationDao.PROJECT_ID); query.addScalar("name"); query.addScalar(GeolocationDao.LOCATION_ID); query.setParameterList("traitIds", traitIds); query.setParameter("programUUID", programUUID); final List<Object[]> list = query.list(); for (final Object[] row : list) { // otherwise it's invalid data and should not be included if (NumberUtils.isNumber((String) row[6])) { environments.add(new TrialEnvironment( (Integer) row[0], new LocationDto(Integer.valueOf(row[6].toString()), (String) row[1], (String) row[2], (String) row[3]), new StudyReference((Integer) row[4], (String) row[5]))); } } } catch (final HibernateException e) { final String errorMessage = "Error at getEnvironmentForTraits at GeolocationDao: " + e.getMessage(); GeolocationDao.LOG.error(errorMessage, e); throw new MiddlewareQueryException(errorMessage, e); } return environments; }
From source file:org.generationcp.middleware.dao.dms.GeolocationDao.java
License:Open Source License
public List<InstanceMetadata> getInstanceMetadata(final int studyId, final List<Integer> locationIds) { final String queryString = "select \n" + " geoloc.nd_geolocation_id as instanceDBId, \n" + " geoloc.description as instanceNumber, \n" + " pmain.project_id trialDbId, \n" + " pmain.name as trialName, \n" + " proj.name as instanceDatasetName, \n" + " pmain.program_uuid as programDbId, \n" + " max(if(geoprop.type_id = 8190, loc.lname, null)) as LOCATION_NAME, \n" + " max(if(geoprop.type_id = 8190, geoprop.value, null)) as LOCATION_ID, \n" + " max(if(geoprop.type_id = 8189, geoprop.value, null)) as LOCATION_ABBR, \n" + " max(if(geoprop.type_id = 8370, geoprop.value, null)) as CROP_SEASON \n" + " from \n" + " nd_geolocation geoloc \n" + " inner join nd_experiment nde on nde.nd_geolocation_id = geoloc.nd_geolocation_id \n" + " inner join project proj on proj.project_id = nde.project_id \n" + " inner join project pmain on pmain.project_id = proj.study_id \n" + " left outer join nd_geolocationprop geoprop on geoprop.nd_geolocation_id = geoloc.nd_geolocation_id \n" + " left outer join location loc on geoprop.value = loc.locid and geoprop.type_id = 8190 \n" + " where nde.type_id = 1020 and pmain.project_id = :studyId \n"; final StringBuilder strBuilder = new StringBuilder(queryString); final boolean locationFilterSpecified = !CollectionUtils.isEmpty(locationIds); if (locationFilterSpecified) { strBuilder.append(" and geoprop.value in (:locationIds) "); }//w w w. j a v a2 s.co m strBuilder.append(" group by geoloc.nd_geolocation_id "); strBuilder.append(" order by geoloc.nd_geolocation_id asc \n"); final SQLQuery query = this.getSession().createSQLQuery(strBuilder.toString()); query.setParameter("studyId", studyId); if (locationFilterSpecified) { query.setParameterList("locationIds", locationIds); } query.addScalar("instanceDBId", new IntegerType()); query.addScalar("instanceNumber"); query.addScalar("trialDbId", new IntegerType()); query.addScalar("trialName"); query.addScalar("instanceDatasetName"); query.addScalar("programDbId"); query.addScalar("LOCATION_NAME"); query.addScalar("LOCATION_ID", new IntegerType()); query.addScalar("LOCATION_ABBR"); query.addScalar("CROP_SEASON"); @SuppressWarnings("rawtypes") final List results = query.list(); final List<InstanceMetadata> tiMetadata = new ArrayList<>(); for (final Object result : results) { final Object[] row = (Object[]) result; final InstanceMetadata metadata = new InstanceMetadata(); metadata.setInstanceDbId((Integer) row[0]); metadata.setInstanceNumber(String.valueOf(row[1])); metadata.setTrialDbId((Integer) row[2]); metadata.setTrialName(String.valueOf(row[3])); metadata.setInstanceDatasetName(String.valueOf(row[4])); metadata.setProgramDbId(String.valueOf(row[5])); metadata.setLocationName(String.valueOf(row[6])); metadata.setLocationDbId((Integer) row[7]); metadata.setLocationAbbreviation(String.valueOf(row[8])); metadata.setSeason(String.valueOf(row[9])); tiMetadata.add(metadata); } return tiMetadata; }
From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java
License:Open Source License
public List<NumericTraitInfo> getNumericTraitInfoList(final List<Integer> environmentIds, final List<Integer> numericVariableIds) { final List<NumericTraitInfo> numericTraitInfoList = new ArrayList<>(); try {/* w w w .j av a 2 s . c o m*/ final SQLQuery query = this.getSession().createSQLQuery("SELECT p.observable_id, " + "COUNT(DISTINCT e.nd_geolocation_id) AS location_count, " + "COUNT(DISTINCT s.dbxref_id) AS germplasm_count, " + "COUNT(DISTINCT e.nd_experiment_id) AS observation_count , " + "IF (MIN(p.value * 1) IS NULL, 0, MIN(p.value * 1)) AS min_value, " + "IF (MAX(p.value * 1) IS NULL, 0, MAX(p.value * 1)) AS max_value " + "FROM phenotype p " + " INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id " + " INNER JOIN stock s ON e.stock_id = s.stock_id " + "WHERE e.nd_geolocation_id IN (:environmentIds) " + " AND p.observable_id IN (:numericVariableIds) " + "GROUP by p.observable_id "); query.setParameterList("environmentIds", environmentIds); query.setParameterList("numericVariableIds", numericVariableIds); final List<Object[]> list; if (!environmentIds.isEmpty() && !numericVariableIds.isEmpty()) { list = query.list(); for (final Object[] row : list) { final Integer id = (Integer) row[0]; final Long locationCount = ((BigInteger) row[1]).longValue(); final Long germplasmCount = ((BigInteger) row[2]).longValue(); final Long observationCount = ((BigInteger) row[3]).longValue(); final Double minValue = (Double) row[4]; final Double maxValue = (Double) row[5]; final NumericTraitInfo numericTraitInfo = new NumericTraitInfo(null, id, null, locationCount, germplasmCount, observationCount, minValue, maxValue, 0); numericTraitInfoList.add(numericTraitInfo); } } } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error at getNumericTraitInfoList() query on PhenotypeDao: " + e.getMessage(), e); } return numericTraitInfoList; }
From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java
License:Open Source License
public List<TraitInfo> getTraitInfoCounts(final List<Integer> environmentIds, final List<Integer> variableIds) { final List<TraitInfo> traitInfoList = new ArrayList<>(); try {// w w w .j a va2 s .c o m final SQLQuery query = this.getSession() .createSQLQuery("SELECT p.observable_id, " + "COUNT(DISTINCT e.nd_geolocation_id) AS location_count, " + "COUNT(DISTINCT s.dbxref_id) AS germplasm_count, " + "COUNT(DISTINCT e.nd_experiment_id) AS observation_count " + "FROM phenotype p " + " INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id " + " INNER JOIN stock s ON e.stock_id = s.stock_id " + "WHERE e.nd_geolocation_id IN (:environmentIds) " + " AND p.observable_id IN (:variableIds) " + "GROUP by p.observable_id "); query.setParameterList("environmentIds", environmentIds); query.setParameterList("variableIds", variableIds); List<Object[]> list = new ArrayList<>(); if (!environmentIds.isEmpty() && !variableIds.isEmpty()) { list = query.list(); } for (final Object[] row : list) { final Integer id = (Integer) row[0]; final long locationCount = ((BigInteger) row[1]).longValue(); final long germplasmCount = ((BigInteger) row[2]).longValue(); final long observationCount = ((BigInteger) row[3]).longValue(); traitInfoList.add(new TraitInfo(id, null, null, locationCount, germplasmCount, observationCount)); } } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error at getTraitInfoCounts() query on PhenotypeDao: " + e.getMessage(), e); } return traitInfoList; }
From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java
License:Open Source License
public List<TraitInfo> getTraitInfoCounts(final List<Integer> environmentIds) { final List<TraitInfo> traitInfoList = new ArrayList<>(); try {//from www . java2s. c o m final SQLQuery query = this.getSession() .createSQLQuery("SELECT p.observable_id, " + "COUNT(DISTINCT e.nd_geolocation_id) AS location_count, " + "COUNT(DISTINCT s.dbxref_id) AS germplasm_count, " + "COUNT(DISTINCT e.nd_experiment_id) AS observation_count " + "FROM phenotype p " + " INNER JOIN nd_experiment e ON e.nd_experiment_id = p.nd_experiment_id " + " INNER JOIN stock s ON e.stock_id = s.stock_id " + "WHERE e.nd_geolocation_id IN (:environmentIds) " + "GROUP by p.observable_id "); query.setParameterList("environmentIds", environmentIds); final List<Object[]> list = query.list(); for (final Object[] row : list) { final Integer id = (Integer) row[0]; final long locationCount = ((BigInteger) row[1]).longValue(); final long germplasmCount = ((BigInteger) row[2]).longValue(); final long observationCount = ((BigInteger) row[3]).longValue(); traitInfoList.add(new TraitInfo(id, null, null, locationCount, germplasmCount, observationCount)); } } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error at getTraitInfoCounts() query on PhenotypeDao: " + e.getMessage(), e); } return traitInfoList; }
From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java
License:Open Source License
public Map<Integer, List<Double>> getNumericTraitInfoValues(final List<Integer> environmentIds, final List<NumericTraitInfo> traitInfoList) { final Map<Integer, List<Double>> traitValues = new HashMap<>(); // Get trait IDs final List<Integer> traitIds = new ArrayList<>(); for (final NumericTraitInfo trait : traitInfoList) { traitIds.add(trait.getId());/* w ww .j ava 2 s. c o m*/ } try { final SQLQuery query = this.getSession() .createSQLQuery("SELECT p.observable_id, p.value * 1 " + "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) "); query.setParameterList("environmentIds", environmentIds); query.setParameterList(TRAIT_IDS, traitIds); List<Object[]> list = new ArrayList<>(); if (!environmentIds.isEmpty()) { list = query.list(); } for (final Object[] row : list) { final Integer traitId = (Integer) row[0]; final Double value = (Double) row[1]; List<Double> 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 getNumericTraitInfoValues() query on PhenotypeDao: " + e.getMessage(), e); } return traitValues; }
From source file:org.generationcp.middleware.dao.dms.PhenotypeDao.java
License:Open Source License
public Map<Integer, List<Double>> getNumericTraitInfoValues(final List<Integer> environmentIds, final Integer trait) { final Map<Integer, List<Double>> traitValues = new HashMap<>(); try {/*from w w w .ja v a 2 s.co m*/ final SQLQuery query = this.getSession() .createSQLQuery("SELECT p.observable_id, p.value * 1 " + "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 = :traitId "); query.setParameterList("environmentIds", environmentIds); query.setParameter("traitId", trait); List<Object[]> list = new ArrayList<>(); if (!environmentIds.isEmpty()) { list = query.list(); } for (final Object[] row : list) { final Integer traitId = (Integer) row[0]; final Double value = (Double) row[1]; List<Double> 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 getNumericTraitInfoValues() query on PhenotypeDao: " + e.getMessage(), e); } return traitValues; }