List of usage examples for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE
AliasToEntityMapResultTransformer INSTANCE
To view the source code for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE.
Click Source Link
From source file:org.cgiar.ccafs.marlo.data.dao.mysql.AbstractMarloDAO.java
License:Open Source License
/** * This method make a query that returns a not mapped object result from the model. * /*w w w. j av a 2s . co m*/ * @param sqlQuery is a string representing an SQL query. */ public List<Map<String, Object>> excuteStoreProcedure(String storeProcedure, String sqlQuery) { this.sessionFactory.getCurrentSession().createSQLQuery(storeProcedure).executeUpdate(); Query query = this.sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); query.setFlushMode(FlushMode.COMMIT); List<Map<String, Object>> result = query.list(); return result; }
From source file:org.cgiar.ccafs.marlo.data.dao.mysql.AbstractMarloDAO.java
License:Open Source License
/** * This method make a query that returns a not mapped object result from the model. * //from w w w . ja va2 s . c o m * @param sqlQuery is a string representing an HQL query. */ public List<Map<String, Object>> findCustomQuery(String sqlQuery) { Query query = sessionFactory.getCurrentSession().createSQLQuery(sqlQuery); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); query.setFlushMode(FlushMode.COMMIT); List<Map<String, Object>> result = query.list(); return result; }
From source file:org.code_factory.jpa.nestedset.JpaNestedSetManager.java
License:Open Source License
@Override public <T extends NodeInfo> List<Map<String, Object>> fetchTreeAsAdjacencyList(Class<T> clazz, Long rootId, int maxLevel) { Configuration config = getConfig(clazz); StringBuilder sb = new StringBuilder(); sb.append("SELECT node.*,").append(" parent.id parent_id ").append(" FROM ").append(config.getEntityName()) .append(" node ").append(" JOIN ").append(config.getEntityName()).append(" parent ") .append(" ON node.").append(config.getLeftFieldName()).append(" BETWEEN parent.") .append(config.getLeftFieldName()).append(" AND parent.").append(config.getRightFieldName()) .append(" WHERE node.").append(config.getLevelFieldName()).append(" = parent.") .append(config.getLevelFieldName()).append(" + 1").append(" AND node.") .append(config.getRootIdFieldName()).append(" = ").append(rootId); if (maxLevel > 0) { sb.append(" AND node.").append(config.getLevelFieldName()).append(" < ").append(maxLevel); }//from w w w . j a v a 2 s.co m Query query = em.createNativeQuery(sb.toString()); org.hibernate.Query hibernateQuery = ((org.hibernate.jpa.HibernateQuery) query).getHibernateQuery(); hibernateQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); return hibernateQuery.list(); }
From source file:org.generationcp.middleware.dao.dms.DmsProjectDao.java
License:Open Source License
public List<MeasurementVariable> getObservationSetVariables(final List<Integer> observationSetIds, final List<Integer> variableTypes) { try {/*from w w w . j av a 2s .c o m*/ final String query = " SELECT distinct " // + " pp.variable_id AS " + OBS_SET_VARIABLE_ID + ", " // + " variable.name AS " + OBS_SET_VARIABLE_NAME + ", " // + " variable.definition AS " + OBS_SET_DESCRIPTION + ", " // + " pp.alias AS " + OBS_SET_ALIAS + ", " // + " pp.value as " + OBS_SET_VALUE + ", " + " variableType.cvterm_id AS " + OBS_SET_VARIABLE_TYPE_ID + ", " // + " scale.name AS " + OBS_SET_SCALE + ", " // + " method.name AS " + OBS_SET_METHOD + ", " // + " property.name AS " + OBS_SET_PROPERTY + ", " // + " dataType.cvterm_id AS " + OBS_SET_DATA_TYPE_ID + ", " // + " category.cvterm_id AS " + OBS_SET_CATEGORY_ID + ", " // + " category.name AS " + OBS_SET_CATEGORY_NAME + ", " // + " category.definition AS " + OBS_SET_CATEGORY_DESCRIPTION + ", " // + " (SELECT formula_id FROM formula WHERE target_variable_id = pp.variable_id and active = 1 LIMIT 1) AS " + OBS_SET_FORMULA_ID + ", " + " scaleMinRange.value AS " + OBS_SET_SCALE_MIN_RANGE + ", " // + " scaleMaxRange.value AS " + OBS_SET_SCALE_MAX_RANGE + ", " // + " vo.expected_min AS " + OBS_SET_EXPECTED_MIN + ", " // + " vo.expected_max AS " + OBS_SET_EXPECTED_MAX + ", " // + " cropOntology.value AS " + OBS_SET_CROP_ONTOLOGY_ID + "," + " pp.value as " + OBS_SET_VARIABLE_VALUE + " FROM project dataset " // + " INNER JOIN projectprop pp ON dataset.project_id = pp.project_id " // + " INNER JOIN cvterm variable ON pp.variable_id = variable.cvterm_id " // + " INNER JOIN cvterm variableType ON pp.type_id = variableType.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrscale ON variable.cvterm_id = cvtrscale.subject_id " // + " AND cvtrscale.type_id = " + TermId.HAS_SCALE.getId() // + " INNER JOIN cvterm scale ON cvtrscale.object_id = scale.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrmethod ON variable.cvterm_id = cvtrmethod.subject_id " // + " AND cvtrmethod.type_id = " + TermId.HAS_METHOD.getId() // + " INNER JOIN cvterm method ON cvtrmethod.object_id = method.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrproperty ON variable.cvterm_id = cvtrproperty.subject_id " // + " AND cvtrproperty.type_id = " + TermId.HAS_PROPERTY.getId() // + " INNER JOIN cvterm property ON cvtrproperty.object_id = property.cvterm_id " // + " INNER JOIN cvterm_relationship cvtrdataType ON scale.cvterm_id = cvtrdataType.subject_id " // + " AND cvtrdataType.type_id = " + TermId.HAS_TYPE.getId() // + " INNER JOIN cvterm dataType ON cvtrdataType.object_id = dataType.cvterm_id " // + " LEFT JOIN cvterm_relationship cvtrcategory ON scale.cvterm_id = cvtrcategory.subject_id " + " AND cvtrcategory.type_id = " + TermId.HAS_VALUE.getId() // + " LEFT JOIN cvterm category ON cvtrcategory.object_id = category.cvterm_id " // + " LEFT JOIN cvtermprop scaleMaxRange on scale.cvterm_id = scaleMaxRange.cvterm_id " // + " AND scaleMaxRange.type_id = " + TermId.MAX_VALUE.getId() // + " LEFT JOIN cvtermprop scaleMinRange on scale.cvterm_id = scaleMinRange.cvterm_id " // + " AND scaleMinRange.type_id = " + TermId.MIN_VALUE.getId() // + " LEFT JOIN variable_overrides vo ON variable.cvterm_id = vo.cvterm_id " // + " AND dataset.program_uuid = vo.program_uuid " // + " LEFT JOIN cvtermprop cropOntology ON cropOntology.cvterm_id = variable.cvterm_id" // + " AND cropOntology.type_id = " + TermId.CROP_ONTOLOGY_ID.getId() + " WHERE " // + " dataset.project_id in (:observationSetIds) " // + " AND pp.type_id in (:variableTypes) " + " ORDER BY pp.rank "; final SQLQuery sqlQuery = this.getSession().createSQLQuery(query); sqlQuery.setParameterList("observationSetIds", observationSetIds); sqlQuery.setParameterList("variableTypes", variableTypes); sqlQuery.addScalar(OBS_SET_VARIABLE_ID).addScalar(OBS_SET_VARIABLE_NAME).addScalar(OBS_SET_DESCRIPTION) .addScalar(OBS_SET_ALIAS).addScalar(OBS_SET_VALUE).addScalar(OBS_SET_VARIABLE_TYPE_ID) .addScalar(OBS_SET_SCALE).addScalar(OBS_SET_METHOD).addScalar(OBS_SET_PROPERTY) .addScalar(OBS_SET_DATA_TYPE_ID).addScalar(OBS_SET_CATEGORY_ID).addScalar(OBS_SET_CATEGORY_NAME) .addScalar(OBS_SET_CATEGORY_DESCRIPTION).addScalar(OBS_SET_SCALE_MIN_RANGE, new DoubleType()) .addScalar(OBS_SET_SCALE_MAX_RANGE, new DoubleType()) .addScalar(OBS_SET_EXPECTED_MIN, new DoubleType()) .addScalar(OBS_SET_EXPECTED_MAX, new DoubleType()) .addScalar(OBS_SET_FORMULA_ID, new IntegerType()).addScalar(OBS_SET_CROP_ONTOLOGY_ID) .addScalar(OBS_SET_VARIABLE_VALUE); sqlQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = sqlQuery.list(); final Map<Integer, MeasurementVariable> variables = new LinkedHashMap<>(); for (final Map<String, Object> result : results) { final Integer variableId = (Integer) result.get("variableId"); if (!variables.containsKey(variableId)) { variables.put(variableId, new MeasurementVariable()); final MeasurementVariable measurementVariable = variables.get(variableId); measurementVariable.setTermId(variableId); measurementVariable.setName((String) result.get(OBS_SET_VARIABLE_NAME)); measurementVariable.setAlias((String) result.get(OBS_SET_ALIAS)); measurementVariable.setValue((String) result.get(OBS_SET_VALUE)); measurementVariable.setDescription((String) result.get(OBS_SET_DESCRIPTION)); measurementVariable.setScale((String) result.get(OBS_SET_SCALE)); measurementVariable.setMethod((String) result.get(OBS_SET_METHOD)); measurementVariable.setProperty((String) result.get(OBS_SET_PROPERTY)); final VariableType variableType = VariableType .getById((Integer) result.get(OBS_SET_VARIABLE_TYPE_ID)); measurementVariable.setVariableType(variableType); //TODO: fix the saving of Treatment Factor Variables in the projectprop table. // Right now, the saved typeid is 1100. It should be 1809(VariableType.TREATMENT_FACTOR.getid()) if (variableType != null) { measurementVariable.setFactor(!variableType.getRole().equals(PhenotypicType.VARIATE)); } final DataType dataType = DataType.getById((Integer) result.get(OBS_SET_DATA_TYPE_ID)); measurementVariable.setDataType(dataType.getName()); measurementVariable.setDataTypeId(dataType.getId()); final Integer formulaId = (Integer) result.get(OBS_SET_FORMULA_ID); if (formulaId != null) { final Formula formula = (Formula) this.getSession().createCriteria(Formula.class) .add(Restrictions.eq("formulaId", formulaId)).add(Restrictions.eq("active", true)) .uniqueResult(); if (formula != null) { measurementVariable.setFormula(FormulaUtils.convertToFormulaDto(formula)); } } final Double scaleMinRange = (Double) result.get(OBS_SET_SCALE_MIN_RANGE); final Double scaleMaxRange = (Double) result.get(OBS_SET_SCALE_MAX_RANGE); final Double expectedMin = (Double) result.get(OBS_SET_EXPECTED_MIN); final Double expectedMax = (Double) result.get(OBS_SET_EXPECTED_MAX); measurementVariable.setMinRange(expectedMin != null ? expectedMin : scaleMinRange); measurementVariable.setMaxRange(expectedMax != null ? expectedMax : scaleMaxRange); measurementVariable.setScaleMinRange(scaleMinRange); measurementVariable.setScaleMaxRange(scaleMaxRange); measurementVariable.setVariableMinRange(expectedMin); measurementVariable.setVariableMaxRange(expectedMax); measurementVariable.setCropOntology((String) result.get(OBS_SET_CROP_ONTOLOGY_ID)); } final MeasurementVariable measurementVariable = variables.get(variableId); if (measurementVariable.getValue() == null || measurementVariable.getValue().isEmpty()) { measurementVariable.setValue((String) result.get(OBS_SET_VARIABLE_VALUE)); } final Object categoryId = result.get(OBS_SET_CATEGORY_ID); if (categoryId != null) { if (measurementVariable.getPossibleValues() == null || measurementVariable.getPossibleValues().isEmpty()) { measurementVariable.setPossibleValues(new ArrayList<ValueReference>()); } final ValueReference valueReference = // new ValueReference((Integer) categoryId, // Objects.toString(result.get(OBS_SET_CATEGORY_NAME)), // Objects.toString(result.get(OBS_SET_CATEGORY_DESCRIPTION))); if (!measurementVariable.getPossibleValues().contains(valueReference)) { measurementVariable.getPossibleValues().add(valueReference); } } } return new ArrayList<>(variables.values()); } catch (final HibernateException e) { throw new MiddlewareQueryException( "Error getting datasets variables for dataset=" + observationSetIds + ": " + e.getMessage(), e); } }
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 {//from www .j av a2s . co m 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()));// w ww. j av a 2 s .c om 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.GeolocationPropertyDao.java
License:Open Source License
public Map<Integer, String> getGeoLocationPropertyByVariableId(final Integer datasetId, final Integer instanceDbId) { Preconditions.checkNotNull(datasetId); final String sql = "SELECT " + " gp.type_id as variableId, " + " gp.value as value " + "FROM " + " nd_experiment e " + " INNER JOIN " + " nd_geolocationprop gp ON gp.nd_geolocation_id = e.nd_geolocation_id " + "WHERE " + " e.project_id = :datasetId " + " and e.nd_geolocation_id = :instanceDbId"; final SQLQuery query = this.getSession().createSQLQuery(sql); query.addScalar("variableId").addScalar("value").setParameter("datasetId", datasetId) .setParameter("instanceDbId", instanceDbId); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = query.list(); final Map<Integer, String> geoProperties = new HashMap<>(); for (final Map<String, Object> result : results) { final Integer variableId = (Integer) result.get("variableId"); final String value = (String) result.get("value"); geoProperties.put(variableId, value); }/*from w w w. ja v a2 s .c o m*/ return geoProperties; }
From source file:org.generationcp.middleware.dao.ProjectDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Project> getProjectsByUser(final WorkbenchUser user, final String cropName) { final List<Project> projects = new ArrayList<>(); try {/*from w w w . j ava 2 s .co m*/ if (user != null) { final SQLQuery query = this.getSession().createSQLQuery(GET_PROJECTS_BY_USER_ID); query.setParameter("userId", user.getUserid()); query.setParameter("cropName", cropName); query.addScalar("project_id").addScalar("project_uuid").addScalar("project_name") .addScalar("start_date").addScalar("user_id").addScalar("crop_type") .addScalar("last_open_date"); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); final List<Map<String, Object>> results = query.list(); for (final Map<String, Object> result : results) { final Long project_id = Long.valueOf((Integer) result.get("project_id")); final String project_uuid = (String) result.get("project_uuid"); final String project_name = (String) result.get("project_name"); final Date start_date = (Date) result.get("start_date"); final Integer user_id = (Integer) result.get("user_id"); final CropType crop_type = new CropType((String) result.get("crop_type")); final Date last_open_date = (Date) result.get("last_open_date"); final Project u = new Project(project_id, project_uuid, project_name, start_date, user_id, crop_type, last_open_date); projects.add(u); } return projects; } } catch (final HibernateException e) { throw new MiddlewareQueryException("Error in getProjectsByUser(user=" + user + ") query from ProjectUserInfoDao: " + e.getMessage(), e); } return new ArrayList<>(); }
From source file:org.inbio.neoportal.image_crawler.DAO.DefaultM3sDAO.java
License:Open Source License
public List<Map<String, Object>> getImages(int offset, int quantity) { Session session = sessionFactoryM3s.getCurrentSession(); Query query = session.createSQLQuery("select m.media_id, tm.taxon_id, " + "p.first_name || ' ' || p.last_name as author, " + "t.name as rights " + "from core.media m " + "left join core.taxon_media tm ON m.media_id = tm.media_id " + "left join core.person p ON m.author_person_id = p.person_id " + "left join core.use_policy up ON m.use_policy_id = up.use_policy_id " + "left join core.text_translation t ON up.name_text_id = t.text_id " + "where tm.taxon_id is not null"); query.setFirstResult(offset);//from ww w . jav a 2 s . com query.setMaxResults(quantity); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String, Object>> result = query.list(); return result; }