Example usage for org.hibernate SQLQuery setMaxResults

List of usage examples for org.hibernate SQLQuery setMaxResults

Introduction

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

Prototype

@Override
    Query<R> setMaxResults(int maxResult);

Source Link

Usage

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());
}