List of usage examples for org.hibernate SQLQuery setMaxResults
@Override
Query<R> setMaxResults(int maxResult);
From source file:edu.vt.vbi.patric.dao.DBDisease.java
License:Apache License
public List<ResultType> getGADList(Map<String, String> key, Map<String, String> sort, int start, int end) { String sql = ""; sql += getGADSQL(key, "function"); if (sort != null && sort.containsKey("field") && sort.get("field") != null && sort.containsKey("direction") && sort.get("direction") != null) { sql += " ORDER BY " + sort.get("field") + " " + sort.get("direction"); } else {//from w w w. j a v a2 s .co m sql += " ORDER BY gs.gene_sym"; } Session session = factory.getCurrentSession(); session.beginTransaction(); SQLQuery q = session.createSQLQuery(sql); q = bindSQLValues(q, key); if (end > 0) { q.setMaxResults(end); } ScrollableResults scr = q.scroll(); List<ResultType> results = new ArrayList<>(); if (start > 1) { scr.setRowNumber(start - 1); } else { scr.beforeFirst(); } for (int i = start; (end > 0 && i < end && scr.next()) || (end == -1 && scr.next()); i++) { Object[] obj = scr.get(); ResultType row = new ResultType(); row.put("gene_sym", obj[0]); row.put("gene_id", obj[1]); row.put("association", obj[2]); row.put("mesh_disease_terms", obj[3]); row.put("broad_phenotype", obj[4]); row.put("pubmed_id", obj[5]); row.put("conclusion", obj[6]); row.put("gd_app_name", obj[7]); results.add(row); } session.getTransaction().commit(); return results; }
From source file:gov.nih.nci.caarray.dao.AuditLogDaoImpl.java
License:BSD License
/** * {@inheritDoc}// w w w. ja v a 2 s . com */ public List<AuditLogRecord> getRecords(AuditLogSearchCriteria criteria, PageSortParams<AuditLogRecord> sort) { StringBuffer sb = new StringBuffer(); buildSql(criteria, sb, "distinct r.*"); sb.append(" order by "); if (sort.getSortCriteria().isEmpty()) { sb.append("created_date asc, r.id desc"); } else { String direction = sort.isDesc() ? " desc" : " asc"; String comma = ""; for (SortCriterion<AuditLogRecord> s : sort.getSortCriteria()) { sb.append(comma); sb.append("r.").append(s.getOrderField()).append(direction); comma = ", "; } sb.append(comma).append("r.id").append(direction); } SQLQuery q = buildQuery(criteria, sb); q.addEntity("r", AuditLogRecord.class); q.setMaxResults(sort.getPageSize()); q.setFirstResult(sort.getIndex()); @SuppressWarnings("unchecked") List<AuditLogRecord> records = q.list(); return records; }
From source file:net.lc4ever.framework.activiti.engine.impl.AbstractNativeQueryHibernate.java
License:Open Source License
/** * @see org.activiti.engine.query.NativeQuery#listPage(int, int) */// w w w .jav a 2s . co m @Override public List<U> listPage(final int firstResult, final int maxResults) { return crudService.callback(new HibernateCallback<List<U>>() { @SuppressWarnings("unchecked") @Override public List<U> doInHibernate(final Session session) throws HibernateException, SQLException { SQLQuery query = session.createSQLQuery(selectClause); if (parameters != null) { for (Map.Entry<String, Object> entry : parameters.entrySet()) { query.setParameter(entry.getKey(), entry.getValue()); } } if (resultTransformer != null) query.setResultTransformer(resultTransformer); query.setFirstResult(firstResult); query.setMaxResults(maxResults); return query.list(); } }); }
From source file:org.ambraproject.annotation.service.AnnotationServiceImpl.java
License:Apache License
/** * Get a list of all annotations satisfying the given criteria. * * @param startDate search for annotation after start date. * @param endDate is the date to search until. If null, search until present date * @param annotTypes List of annotation types * @param maxResults the maximum number of results to return, or 0 for no limit * @param journal journalName/*from ww w . jav a2 s .com*/ * @return the (possibly empty) list of article annotations. * @throws ParseException if any of the dates or query could not be parsed * @throws URISyntaxException if an element of annotType cannot be parsed as a URI */ @Override @Transactional(readOnly = true) @SuppressWarnings("unchecked") public List<AnnotationView> getAnnotations(final Date startDate, final Date endDate, final Set<String> annotTypes, final int maxResults, final String journal) throws ParseException, URISyntaxException { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns annotationIDs, article DOIs and titles, which are needed to construction the annotionView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select ann.annotationID, art.doi, art.title "); sqlQuery.append("from annotation ann "); sqlQuery.append("join article art on art.articleID = ann.articleID "); sqlQuery.append("join Journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and ann.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and ann.created < :endDate"); params.put("endDate", endDate); } if (annotTypes != null) { sqlQuery.append(" and ann.type in (:annotTypes)"); params.put("annotTypes", annotTypes); } sqlQuery.append(" order by ann.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(Annotation.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Annotation> annotations = hibernateTemplate.findByCriteria(criteria); List<AnnotationView> views = new ArrayList<AnnotationView>(annotations.size()); for (Annotation ann : annotations) { String articleDoi = results.get(ann.getID())[0]; String articleTitle = results.get(ann.getID())[1]; views.add(buildAnnotationView(ann, articleDoi, articleTitle, false)); } return views; } else { return new ArrayList<AnnotationView>(); } }
From source file:org.ambraproject.service.annotation.AnnotationServiceImpl.java
License:Apache License
/** * Get a list of all annotations satisfying the given criteria. * * @param startDate search for annotation after start date. * @param endDate is the date to search until. If null, search until present date * @param annotTypes List of annotation types * @param maxResults the maximum number of results to return, or 0 for no limit * @param journal journalName/*from w w w . j a v a 2 s. c o m*/ * @return the (possibly empty) list of article annotations. * @throws ParseException if any of the dates or query could not be parsed * @throws URISyntaxException if an element of annotType cannot be parsed as a URI */ @Override @Transactional(readOnly = true) @SuppressWarnings("unchecked") public List<AnnotationView> getAnnotations(final Date startDate, final Date endDate, final Set<String> annotTypes, final int maxResults, final String journal) throws ParseException, URISyntaxException { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns annotationIDs, article DOIs and titles, which are needed to construction the annotionView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** URIGen * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select ann.annotationID, art.doi, art.title "); sqlQuery.append("from annotation ann "); sqlQuery.append("join article art on art.articleID = ann.articleID "); sqlQuery.append("join journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and ann.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and ann.created < :endDate"); params.put("endDate", endDate); } if (annotTypes != null) { sqlQuery.append(" and ann.type in (:annotTypes)"); params.put("annotTypes", annotTypes); } sqlQuery.append(" order by ann.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(Annotation.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Annotation> annotations = (List<Annotation>) hibernateTemplate.findByCriteria(criteria); List<AnnotationView> views = new ArrayList<AnnotationView>(annotations.size()); for (Annotation ann : annotations) { String articleDoi = results.get(ann.getID())[0]; String articleTitle = results.get(ann.getID())[1]; views.add(buildAnnotationView(ann, articleDoi, articleTitle, false)); } return views; } else { return new ArrayList<AnnotationView>(); } }
From source file:org.ambraproject.service.trackback.TrackbackServiceImpl.java
License:Apache License
@Override @Transactional(readOnly = true)// ww w.j av a 2s. c o m @SuppressWarnings("unchecked") public List<LinkbackView> getTrackbacks(final Date startDate, final Date endDate, final int maxResults, final String journal) { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns trackbackIDs, article DOIs and titles, which are needed to construction the trackbackView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select track.trackbackID, art.doi, art.title "); sqlQuery.append("from trackback track "); sqlQuery.append("join article art on art.articleID = track.articleID "); sqlQuery.append("join journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and track.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and track.created < :endDate"); params.put("endDate", endDate); } sqlQuery.append(" order by track.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(org.ambraproject.models.Trackback.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")); List<org.ambraproject.models.Trackback> trackbacks = (List<Trackback>) hibernateTemplate .findByCriteria(criteria); List<LinkbackView> views = new ArrayList<LinkbackView>(trackbacks.size()); for (org.ambraproject.models.Trackback track : trackbacks) { String articleDoi = results.get(track.getID())[0]; String articleTitle = results.get(track.getID())[1]; views.add(new LinkbackView(track, articleDoi, articleTitle)); } return views; } else { return new ArrayList<LinkbackView>(); } }
From source file:org.ambraproject.trackback.TrackbackServiceImpl.java
License:Apache License
@Override @Transactional(readOnly = true)// w ww . j a v a 2 s.c o m @SuppressWarnings("unchecked") public List<TrackbackView> getTrackbacks(final Date startDate, final Date endDate, final int maxResults, final String journal) { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns trackbackIDs, article DOIs and titles, which are needed to construction the trackbackView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select track.trackbackID, art.doi, art.title "); sqlQuery.append("from trackback track "); sqlQuery.append("join article art on art.articleID = track.articleID "); sqlQuery.append("join Journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and track.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and track.created < :endDate"); params.put("endDate", endDate); } sqlQuery.append(" order by track.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(org.ambraproject.models.Trackback.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")); List<org.ambraproject.models.Trackback> trackbacks = hibernateTemplate.findByCriteria(criteria); List<TrackbackView> views = new ArrayList<TrackbackView>(trackbacks.size()); for (org.ambraproject.models.Trackback track : trackbacks) { String articleDoi = results.get(track.getID())[0]; String articleTitle = results.get(track.getID())[1]; views.add(new TrackbackView(track, articleDoi, articleTitle)); } return views; } else { return new ArrayList<TrackbackView>(); } }
From source file:org.anyframe.iam.admin.securedresources.dao.impl.SecuredResourcesDaoHibernateImpl.java
License:Apache License
public Page getListwithLevel(ResourceSearchVO resourceSearchVO) throws Exception { int pageIndex = resourceSearchVO.getPage(); int pageSize = resourceSearchVO.getRows(); if (pageSize <= 0) pageSize = this.getPropertiesService().getInt("PAGE_SIZE"); int pageUnit = this.getPropertiesService().getInt("PAGE_UNIT"); String sidx = StringUtil.null2str(resourceSearchVO.getSidx()); String sord = StringUtil.null2str(resourceSearchVO.getSord()); String searchCondition = StringUtil.null2str(resourceSearchVO.getSearchCondition()); String searchKeyword = StringUtil.null2str(resourceSearchVO.getSearchKeyword()); String searchType = StringUtil.null2str(resourceSearchVO.getSearchtype()); String roleId = StringUtil.null2str(resourceSearchVO.getRoleId()); String systemName = StringUtil.null2str(resourceSearchVO.getSystemName()); int startIndex = (pageIndex - 1) * pageSize; Query query = (Query) this.getSessionFactory().getCurrentSession() .getNamedQuery("findsecuredResourceswithlevel"); StringBuffer replaceSQL = new StringBuffer(); if (!("".equals(searchKeyword))) { if ("resourceName".equals(searchCondition)) { replaceSQL.append("AND a.resource_name like '%" + searchKeyword + "%'"); } else if ("resourceId".equals(searchCondition)) { replaceSQL.append("AND a.resource_id like '%" + searchKeyword + "%'"); } else {/*from www .jav a 2 s.c o m*/ replaceSQL.append("AND a.resource_pattern like '%" + searchKeyword + "%'"); } } if ("URL".equals(searchType) || "Method".equals(searchType) || "PointCut".equals(searchType)) { replaceSQL.append("AND a.resource_type like '" + searchType + "'"); } if ("".equals(systemName) || "All".equals(systemName)) { replaceSQL.append(" AND a.resource_name = " + systemName); } String queryString = query.getQueryString(); queryString = queryString.replace("--replaceSQL1", replaceSQL.toString()); StringBuffer replaceSQLOrderby = new StringBuffer(); if ("resourceId".equals(sidx) || "".equals(sidx)) { replaceSQLOrderby.append("ORDER BY a.resource_id "); } else { replaceSQLOrderby.append("ORDER BY a." + sidx); } if ("desc".equals(sord) || "".equals(sord)) { replaceSQLOrderby.append("DESC"); } else { replaceSQLOrderby.append("ASC"); } queryString = queryString.replace("--replaceSQL2", replaceSQLOrderby.toString()); SQLQuery replacedQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(queryString); replacedQuery.addEntity(IamResourceResult.class); replacedQuery.setParameter("roleId", roleId); replacedQuery.setFirstResult(startIndex); replacedQuery.setMaxResults(pageSize); @SuppressWarnings("unchecked") List<IamResourceResult> resultList = replacedQuery.list(); query = (Query) this.getSessionFactory().getCurrentSession() .getNamedQuery("countsecuredResourceswithlevel"); queryString = query.getQueryString(); queryString = queryString.replace("--replaceSQL", replaceSQL.toString()); replacedQuery = this.getSessionFactory().getCurrentSession().createSQLQuery(queryString); replacedQuery.setParameter("roleId", roleId); int totalSize = Integer.parseInt(replacedQuery.list().get(0).toString()); Page resultPage = new Page(resultList, pageIndex, totalSize, pageUnit, pageSize); return resultPage; }
From source file:org.egov.wtms.application.service.DefaultersWTReportService.java
License:Open Source License
@ReadOnly public List<DefaultersReport> getDefaultersReportDetails(final String fromAmount, final String toAmount, final String ward, final String topDefaulters, final int startsFrom, final int maxResults) throws ParseException { StringBuilder queryStr = new StringBuilder(); queryStr = queryStr.append(/*from ww w . ja va 2 s.c o m*/ "select dcbinfo.hscno as \"hscNo\", dcbinfo.demand as \"demandId\", dcbinfo.username as \"ownerName\",wardboundary.name as \"wardName\", ") .append("dcbinfo.houseno as \"houseNo\" , localboundary.localname as \"locality\", dcbinfo.mobileno as \"mobileNumber\", ") .append("dcbinfo.arr_balance as \"arrearsDue\" , dcbinfo.curr_balance as \"currentDue\" , dcbinfo.arr_balance+dcbinfo.curr_balance as \"totalDue\" ") .append("from egwtr_mv_dcb_view dcbinfo INNER JOIN eg_boundary wardboundary on dcbinfo.wardid = wardboundary.id INNER JOIN eg_boundary localboundary on dcbinfo.locality = localboundary.id"); if (Double.parseDouble(toAmount) == 0) queryStr.append(" where dcbinfo.arr_balance+dcbinfo.curr_balance >=" + fromAmount); else queryStr.append(" where dcbinfo.arr_balance+dcbinfo.curr_balance >=" + fromAmount + " and dcbinfo.arr_balance+dcbinfo.curr_balance <=" + toAmount); queryStr.append(" and dcbinfo.connectionstatus = '" + ConnectionStatus.ACTIVE.toString() + "'"); if (ward != null && !ward.isEmpty()) queryStr.append(" and wardboundary.id = '" + ward + "'"); queryStr.append(" and dcbinfo.demand IS NOT NULL"); if (!topDefaulters.isEmpty()) queryStr.append(" order by dcbinfo.arr_balance+dcbinfo.curr_balance desc "); final SQLQuery finalQuery = getCurrentSession().createSQLQuery(queryStr.toString()); finalQuery.setFirstResult(startsFrom); finalQuery.setMaxResults(maxResults); finalQuery.setResultTransformer(new AliasToBeanResultTransformer(DefaultersReport.class)); return finalQuery.list(); }
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); }/*w ww . j ava2 s . c o m*/ 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()); }