List of usage examples for org.hibernate SQLQuery setFirstResult
@Override
Query<R> setFirstResult(int startPosition);
From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java
/** * danh sach hoa don doanh nghiep//from w w w . j av a 2 s . c o m * * @param fileId * @param start * @param count * @param sortField * @return */ public GridResult getLstPayment(Long fileId, int start, int count, String sortField) { // String sql = "from Fee f left join Fee_Payment_Info fpi on f.fee_Id = fpi.fee_Id where f.fee_Id " // + "in (select fp.fee_Id from Fee_Procedure fp where fp.procedure_Id = ((select fi.file_Type from Files fi where fi.file_Id = ?)) union " // + "select ff.fee_Id from Fee_File ff where ff.file_Id = ? ) and f.is_Active = 1"; String sql = "from fee f inner join fee_payment_info fpi on f.fee_id = fpi.fee_id where fpi.file_id = ? and f.is_Active=1 and fpi.is_Active=1"; SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery("select count(*) " + sql); SQLQuery query = (SQLQuery) getSession().createSQLQuery( "select f.fee_Id,f.fee_Name,f.description,fpi.cost,f.fee_Type,fpi.status,fpi.fee_Payment_Type_Id, f.price,fpi.payment_Person,fpi.payment_Date,fpi.payment_Info,fpi.bill_path " + sql); query.setLong(0, fileId); //query.setLong(1, fileId); countQuery.setParameter(0, fileId); //countQuery.setParameter(1, fileId); query.setFirstResult(start); query.setMaxResults(count); int total = Integer.parseInt(countQuery.uniqueResult().toString()); List lstResult = query.list(); FeePaymentFileForm item = new FeePaymentFileForm(); List result = new ArrayList<FeePaymentFileForm>(); for (int i = 0; i < lstResult.size(); i++) { Object[] row = (Object[]) lstResult.get(i); if (row.length > 0) { if (row[0] != null && !"".equals(row[0])) { item.setFeeId(Long.parseLong(row[0].toString())); } if (row[1] != null && !"".equals(row[1])) { item.setFeeName(row[1].toString()); } if (row[2] != null && !"".equals(row[2])) { item.setDescription(row[2].toString()); } if (row[7] != null && !"".equals(row[7])) { item.setPrice(Long.parseLong(row[7].toString())); } if (row[4] != null && !"".equals(row[4])) { item.setFeeType(Long.parseLong(row[4].toString())); } if (row[5] != null && !"".equals(row[5])) { item.setStatus(Long.parseLong(row[5].toString())); } if (row[6] != null && !"".equals(row[6])) { item.setFeePaymentType(Long.parseLong(row[6].toString())); } if (row[8] != null && !"".equals(row[8])) { item.setPaymentPerson(row[8].toString()); } if (row[9] != null && !"".equals(row[9])) { item.setPaymentDate(row[9].toString()); } if (row[10] != null && !"".equals(row[10])) { item.setPaymentInfo(row[10].toString()); } if (row[11] != null && !"".equals(row[11])) { item.setBillPath(row[11].toString()); } } result.add(item); item = new FeePaymentFileForm(); } GridResult gr = new GridResult(total, result); return gr; }
From source file:com.viettel.hqmc.DAOHE.FeePaymentInfoDAOHE.java
/** * quan ly nop phi/* w w w .ja v a 2s.co m*/ * * @param start * @param count * @param sortField * @return */ public GridResult getLstFeeManage(int start, int count, String sortField) { String sql = "from files f inner join fee_payment_info fpi on f.file_id = fpi.file_id where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 1 )"; SQLQuery countQuery = (SQLQuery) getSession() .createSQLQuery("select count (distinct fpi.payment_info_id) " + sql); SQLQuery query = (SQLQuery) getSession().createSQLQuery( "select distinct f.file_code,f.product_name,fpi.payment_date,fpi.cost,fpi.bill_path,fpi.fee_payment_type_id,fpi.status,fpi.fee_id,fpi.file_id,fpi.payment_info_id,fpi.payment_person " + sql); query.setFirstResult(start); query.setMaxResults(count); int total = Integer.parseInt(countQuery.uniqueResult().toString()); List lstResult = query.list(); FeePaymentFileForm item = new FeePaymentFileForm(); List result = new ArrayList<FeePaymentFileForm>(); for (int i = 0; i < lstResult.size(); i++) { Object[] row = (Object[]) lstResult.get(i); if (row.length > 0) { if (row[0] != null && !"".equals(row[0])) { item.setFileCode(row[0].toString()); } if (row[1] != null && !"".equals(row[1])) { item.setProductName(row[1].toString()); } if (row[2] != null && !"".equals(row[2])) { item.setPaymentDate(row[2].toString()); } if (row[3] != null && !"".equals(row[3])) { item.setCost(Long.parseLong(row[3].toString())); } if (row[7] != null && !"".equals(row[7])) { item.setFeeId(Long.parseLong(row[7].toString())); } if (row[4] != null && !"".equals(row[4])) { item.setBillPath(row[4].toString()); } if (row[5] != null && !"".equals(row[5])) { item.setFeePaymentType(Long.parseLong(row[5].toString())); } if (row[6] != null && !"".equals(row[6])) { item.setStatus(Long.parseLong(row[6].toString())); } if (row[8] != null && !"".equals(row[8])) { item.setFileId(Long.parseLong(row[8].toString())); } if (row[9] != null && !"".equals(row[9])) { item.setPaymentInfoId(Long.parseLong(row[9].toString())); } if (row[10] != null && !"".equals(row[10])) { item.setPaymentPerson((row[10].toString())); } } result.add(item); item = new FeePaymentFileForm(); } GridResult gr = new GridResult(total, result); return gr; }
From source file:com.wm.framework.sh.dao.impl.BaseDaoImpl.java
License:Open Source License
/**??executeSQLFindByPagination <br> * ??SQL ? <br>/*ww w . j a va2 s . c om*/ * ? <br> * 20141223?11:26:15 * @see com.wm.framework.sh.dao.BaseDao#executeSQLFindByPagination(java.lang.String, com.fklvtu.framework.sh.util.Pagination, java.lang.Object[]) * @param sql * @param pagination * @param params * @return */ public void executeSQLFindByPagination(String sql, String sqlTotalCount, Pagination<JSONObject> pagination, Object... params) { log.debug("executeSQLFind start"); SQLQuery sqlQuery = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql); this.queryAttachParams(sqlQuery, params); int pageNo = pagination.getPageNo(); int pageSize = pagination.getPageSize(); sqlQuery.setFirstResult((pageNo - 1) * pageSize); sqlQuery.setMaxResults(pageSize); List<JSONObject> affected = sqlQuery.list(); // String[] fields = this.buildFields(sqlTotalCount); //String[] fields = this.buildFields(sql); JSONArray ja = this.buildJSONArray(fields, affected); pagination.setFields(fields); pagination.setData(ja); this.sqlGetTotalCount(sql, pagination, params); log.debug("executeSQLFind end " + affected); }
From source file:com.wm.framework.sh.dao.impl.BaseDaoImpl.java
License:Open Source License
@Override public void executeSQLFindByPagination2(String sql, String sqlTotalCount, Pagination<JSONObject> pagination, Object... params) {/*from w ww .j a va 2 s .co m*/ log.debug("executeSQLFind start"); SQLQuery sqlQuery = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createSQLQuery(sql); System.out.println("sql---->" + sql); this.queryAttachParams(sqlQuery, params); int pageNo = pagination.getPageNo(); int pageSize = pagination.getPageSize(); sqlQuery.setFirstResult((pageNo - 1) * pageSize); sqlQuery.setMaxResults(pageSize); List<JSONObject> affected = sqlQuery.list(); // String[] fields = this.buildFields(sqlTotalCount); JSONArray ja = this.buildJSONArray(fields, affected); pagination.setFields(fields); pagination.setData(ja); log.debug("executeSQLFind end " + affected); }
From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java
@Override public PageFinder<ApiKey> queryApiKey(ApiKeyMetadata apiKeyMetadata, Query query) throws Exception { StringBuilder sqlPrefixBuilder = new StringBuilder(); sqlPrefixBuilder.append(" select "); sqlPrefixBuilder.append(/*from www . j av a2s . c o m*/ " t1.id, t1.app_key as appKey, t1.app_secret as appSecret, t1.status, t1.update_user as updateUser, t1.update_time as updateTime "); StringBuilder sqlSuffixBuilder = new StringBuilder(); sqlSuffixBuilder.append(" from "); sqlSuffixBuilder.append(" tbl_merchant_api_key t1 "); sqlSuffixBuilder.append(" left join "); sqlSuffixBuilder.append(" tbl_merchant_api_key_metadata t2 "); sqlSuffixBuilder.append(" on(t1.id = t2.key_id) "); sqlSuffixBuilder.append(" left join "); sqlSuffixBuilder.append(" ( "); sqlSuffixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement()); sqlSuffixBuilder.append(" ) t3 "); sqlSuffixBuilder.append(" on (t2.metadata_val = t3.metadata_val) "); sqlSuffixBuilder.append(" where 1 = 1 "); // ? Map<String, Object> properties = new HashMap<String, Object>(); if (apiKeyMetadata != null) { if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataTag())) { sqlSuffixBuilder.append(" and t3.metadata_tag like :metadata_tag"); properties.put("metadata_tag", '%' + apiKeyMetadata.getMetadataTag() + '%'); } if (apiKeyMetadata.getMetadataKey() != null) { sqlSuffixBuilder.append(" and t2.metadata_key = :metadata_key "); properties.put("metadata_key", apiKeyMetadata.getMetadataKey().name()); } if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataVal())) { sqlSuffixBuilder.append(" and t2.metadata_val = :metadata_val "); properties.put("metadata_val", apiKeyMetadata.getMetadataVal()); } if (apiKeyMetadata.getApiKey() != null) { if (apiKeyMetadata.getApiKey().getStatus() != null) { sqlSuffixBuilder.append(" and t1.status = :status "); properties.put("status", apiKeyMetadata.getApiKey().getStatus().ordinal()); } } } Session session = null; PageFinder<ApiKey> pageFinder = null; try { session = apiKeyDao.getHibernateSession(); SQLQuery sqlQuery = session.createSQLQuery("select count(1) " + sqlSuffixBuilder.toString()); sqlQuery.setProperties(properties); int rowCount = ((Number) sqlQuery.uniqueResult()).intValue(); pageFinder = new PageFinder<ApiKey>(query.getPage(), query.getPageSize(), rowCount); if (rowCount > 0) { // ? sqlSuffixBuilder.append(" group by "); sqlSuffixBuilder .append(" t1.id, t1.app_key, t1.app_secret, t1.status, t1.update_user, t1.update_time "); sqlSuffixBuilder.append(" order by "); sqlSuffixBuilder.append(" t1.update_time desc "); // ?APP sqlQuery = session.createSQLQuery(sqlPrefixBuilder.append(sqlSuffixBuilder).toString()); sqlQuery.setFirstResult(pageFinder.getStartOfPage()); sqlQuery.setMaxResults(pageFinder.getPageSize()); sqlQuery.setProperties(properties); sqlQuery.addScalar("id", Hibernate.STRING); sqlQuery.addScalar("appKey", Hibernate.STRING); sqlQuery.addScalar("appSecret", Hibernate.STRING); sqlQuery.addScalar("status", customApiKeyStatus); sqlQuery.addScalar("updateTime", Hibernate.STRING); sqlQuery.addScalar("updateUser", Hibernate.STRING); sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKey.class)); List<ApiKey> merchantApiKeys = sqlQuery.list(); // ?APP? sqlPrefixBuilder.setLength(0); sqlPrefixBuilder.append(" select "); sqlPrefixBuilder.append( " t1.metadata_key as metadataKey, t1.metadata_val as metadataVal, t2.metadata_tag as metadataTag "); sqlPrefixBuilder.append(" from "); sqlPrefixBuilder.append(" tbl_merchant_api_key_metadata t1 "); sqlPrefixBuilder.append(" left join "); sqlPrefixBuilder.append(" ( "); sqlPrefixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement()); sqlPrefixBuilder.append(" ) t2 "); sqlPrefixBuilder.append(" on(t1.metadata_val = t2.metadata_val) "); sqlPrefixBuilder.append(" where "); sqlPrefixBuilder.append(" t1.key_id = ? "); sqlQuery = session.createSQLQuery(sqlPrefixBuilder.toString()); sqlQuery.addScalar("metadataKey", customAppType); sqlQuery.addScalar("metadataVal", Hibernate.STRING); sqlQuery.addScalar("metadataTag", Hibernate.STRING); sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class)); for (ApiKey merchantApiKey : merchantApiKeys) { sqlQuery.setParameter(0, merchantApiKey.getId()); merchantApiKey.setApiKeyMetadatas(new HashSet<ApiKeyMetadata>(sqlQuery.list())); } pageFinder.setData(merchantApiKeys); } } finally { apiKeyDao.releaseHibernateSession(session); } return pageFinder; }
From source file:gov.nih.nci.caarray.dao.AuditLogDaoImpl.java
License:BSD License
/** * {@inheritDoc}/*from w ww . j a v a 2s. c om*/ */ 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) *//*from w w w. j a v a 2 s .c o 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.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 {// w w w . j a va2 s. c om 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 w w w .j a v a 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); }//from www.j av a2 s . co 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()); }