Example usage for org.hibernate SQLQuery setFirstResult

List of usage examples for org.hibernate SQLQuery setFirstResult

Introduction

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

Prototype

@Override
    Query<R> setFirstResult(int startPosition);

Source Link

Usage

From source file:org.generationcp.middleware.dao.GermplasmDAO.java

License:Open Source License

public List<GermplasmDTO> getGermplasmDTOList(final GermplasmSearchRequestDto germplasmSearchRequestDTO,
        final Integer page, final Integer pageSize) {

    try {//from   ww  w .  j  a v  a  2 s .co  m

        String queryString = "SELECT convert(g.gid, char) AS germplasmDbId, reference.btable AS germplasmPUI, " //
                + "  (SELECT n.nval FROM names n " //
                + "   INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'ACCNO' AND u.fldno = n.ntype)" //
                + "   WHERE (n.gid = g.gid) LIMIT 1) AS accessionNumber, " //
                + "   STR_TO_DATE (convert(g.gdate,char), '%Y%m%d') AS acquisitionDate," //
                + "  (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'ORI_COUN' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS countryOfOriginCode, " //
                + "   (SELECT n.nval FROM names n WHERE n.nstat = 1 AND n.gid = g.gid LIMIT 1) AS germplasmName," //
                + "  (SELECT n.nval FROM names n " //
                + "   INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'GENUS' AND u.fldno = n.ntype)" //
                + "   WHERE (n.gid = g.gid) LIMIT 1) AS genus," //
                + "   (SELECT ld.source FROM listdata ld" //
                + "   WHERE ld.gid = g.gid LIMIT 1) AS germplasmSeedSource, " //
                + "   (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPNAM' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS species, " //
                + "   (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPAUTH' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS speciesAuthority, " //
                + "   (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SUBTAX' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS subtaxa, " //
                + "   (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'STAUTH' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS subtaxaAuthority, " //
                + "   (SELECT a.aval FROM atributs a " //
                + "   INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'PROGM' AND u.fldno = a.atype)" //
                + "   WHERE (a.gid = g.gid) LIMIT 1) AS instituteCode, " //
                + "   m.mname as breedingMethodDbId " //
                + "  FROM germplsm g " //
                + "     LEFT JOIN reflinks reference ON reference.brefid = g.gref " //
                + "     LEFT join methods m ON g.methn = m.mid " //
                + "    WHERE g.deleted = 0" //
                + "      AND g.grplce = 0"; //

        if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) {
            queryString = queryString + "      AND (SELECT n.nval" //
                    + "                    FROM names n" //
                    + "                    WHERE n.nstat = 1 AND n.gid = g.gid" //
                    + "                    LIMIT 1) like :likeCondition "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) {
            queryString = queryString + " AND EXISTS (SELECT 1" //
                    + "         FROM names n" //
                    + "                  INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'ACCNO' AND u.fldno = n.ntype)" //
                    + "         WHERE n.gid = g.gid AND n.nval IN (:accessionNumbers)) "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) {
            queryString = queryString + " AND EXISTS (SELECT 1" //
                    + "         FROM atributs a" //
                    + "                  INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'CROPNM' AND u.fldno = a.atype)" //
                    + "         WHERE a.gid = g.gid AND a.aval IN (:commonCropNames)) "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) {
            queryString = queryString + " AND g.gid IN (:germplasmDbIds) ";
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) {
            queryString = queryString + " AND EXISTS (SELECT 1" //
                    + "         FROM names n" //
                    + "                  INNER JOIN udflds u ON (u.ftable = 'NAMES' AND u.fcode = 'GENUS' AND u.fldno = n.ntype)" //
                    + "         WHERE n.gid = g.gid AND n.nval IN (:germplasmGenus)) "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) {
            queryString = queryString + " AND EXISTS (SELECT 1" //
                    + "         FROM names n" //
                    + "         WHERE n.gid = g.gid AND n.nval IN (:germplasmNames)) "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) {
            queryString = queryString + " AND reference.btable IN (:germplasmPUIs) "; //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) {
            queryString = queryString + " AND EXISTS (SELECT 1" //
                    + "         FROM atributs a" //
                    + "                  INNER JOIN udflds u ON (u.ftable = 'ATRIBUTS' AND u.fcode = 'SPNAM' AND u.fldno = a.atype)" //
                    + "         WHERE a.gid = g.gid AND a.aval IN (:germplasmSpecies)) "; //
        }

        final SQLQuery sqlQuery = this.getSession().createSQLQuery(queryString);

        sqlQuery.addScalar("germplasmDbId").addScalar("germplasmPUI").addScalar("accessionNumber")
                .addScalar("acquisitionDate").addScalar("countryOfOriginCode").addScalar("germplasmName")
                .addScalar("genus").addScalar("germplasmSeedSource").addScalar("species")
                .addScalar("speciesAuthority").addScalar("subtaxa").addScalar("subtaxaAuthority")
                .addScalar("instituteCode").addScalar("breedingMethodDbId") //
                .setResultTransformer(new AliasToBeanResultTransformer(GermplasmDTO.class));

        if (StringUtils.isNoneBlank(germplasmSearchRequestDTO.getPreferredName())) {
            sqlQuery.setParameter("likeCondition", "%" + germplasmSearchRequestDTO.getPreferredName() + "%"); //
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getAccessionNumbers())) {
            sqlQuery.setParameterList("accessionNumbers", germplasmSearchRequestDTO.getAccessionNumbers());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getCommonCropNames())) {
            sqlQuery.setParameterList("commonCropNames", germplasmSearchRequestDTO.getCommonCropNames());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmDbIds())) {
            sqlQuery.setParameterList("germplasmDbIds", germplasmSearchRequestDTO.getGermplasmDbIds());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmGenus())) {
            sqlQuery.setParameterList("germplasmGenus", germplasmSearchRequestDTO.getGermplasmGenus());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmNames())) {
            sqlQuery.setParameterList("germplasmNames", germplasmSearchRequestDTO.getGermplasmNames());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmPUIs())) {
            sqlQuery.setParameterList("germplasmPUIs", germplasmSearchRequestDTO.getGermplasmPUIs());
        }

        if (!CollectionUtils.isEmpty(germplasmSearchRequestDTO.getGermplasmSpecies())) {
            sqlQuery.setParameterList("germplasmSpecies", germplasmSearchRequestDTO.getGermplasmSpecies());
        }

        if (page != null && pageSize != null) {
            sqlQuery.setFirstResult(pageSize * page);
            sqlQuery.setMaxResults(pageSize);
        }

        final List<GermplasmDTO> germplasmDTOList = sqlQuery.list();

        return germplasmDTOList;

    } catch (final HibernateException e) {
        final String message = "Error with getGermplasmDTOList" + e.getMessage();
        GermplasmDAO.LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<LocationDetails> getLocationDetails(final Integer locationId, final Integer start,
        final Integer numOfRows) {
    try {//from  ww  w.  java2 s .  co m

        final StringBuilder query = new StringBuilder()
                .append("select l.lname as location_name,l.locid,l.ltype as ltype,")
                .append(" g.lat as latitude, g.lon as longitude, g.alt as altitude,")
                .append(" c.cntryid as cntryid, c.isofull as country_full_name, l.labbr as location_abbreviation,")
                .append(" ud.fname as location_type,")
                .append(" ud.fdesc as location_description, l.program_uuid,")
                .append(" c.isoabbr as cntry_name, province.lname AS province_name, province.locid as province_id")
                .append(" from location l").append(" left join georef g on l.locid = g.locid")
                .append(" left join cntry c on l.cntryid = c.cntryid")
                .append(" left join udflds ud on ud.fldno = l.ltype").append(" ,location province");

        if (locationId != null) {
            query.append(" where l.locid = :id");
            query.append(" AND province.locid = l.snl1id");

            final SQLQuery sqlQuery = this.getSession().createSQLQuery(query.toString());
            sqlQuery.setParameter("id", locationId);
            sqlQuery.setFirstResult(start);
            sqlQuery.setMaxResults(numOfRows);
            sqlQuery.addEntity(LocationDetails.class);

            return sqlQuery.list();
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationDetails", "id",
                String.valueOf(locationId), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
    return new ArrayList<>();
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<LocationDetailsDto> getLocationsByFilter(final int pageNumber, final int pageSize,
        final Map<LocationFilters, Object> filters) {
    final List<LocationDetailsDto> locationList = new ArrayList<>();
    final StringBuilder sqlString = new StringBuilder();
    try {/*  w  ww .j a va  2 s .c  o  m*/

        sqlString.append(
                "SELECT l.locid ,ud.fname ,l.lname ,l.labbr ,c.isothree ,c.isoabbr ,g.lat ,g.lon ,g.alt ,province.lname as province")
                .append(" FROM location l ").append(" LEFT JOIN georef g on l.locid = g.locid ")
                .append(" LEFT JOIN cntry c on l.cntryid = c.cntryid ")
                .append(" LEFT JOIN udflds ud on ud.fldno = l.ltype, ").append(" location province")
                .append(createConditionWhereByFilter(filters));

        sqlString.append(" and province.locid = l.snl1id ");
        sqlString.append(" ORDER BY l.locid ");

        final SQLQuery query = this.getSession().createSQLQuery(sqlString.toString()).addScalar("l.locid")
                .addScalar("ud.fname").addScalar("l.lname").addScalar("l.labbr").addScalar("c.isothree")
                .addScalar("c.isoabbr").addScalar("g.lat").addScalar("g.lon").addScalar("g.alt")
                .addScalar("province");
        final int start = pageSize * (pageNumber - 1);
        final int numOfRows = pageSize;
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);
        this.setQueryParameters(query, filters);

        final List<Object[]> results = query.list();

        if (!results.isEmpty()) {
            for (final Object[] row : results) {
                final Integer locationDbId = (Integer) row[0];
                final String locationType = (String) row[1];
                final String name = (String) row[2];
                final String abbreviation = (String) row[3];
                final String countryCode = (String) row[4];
                final String countryName = (String) row[5];
                final Double latitude = (Double) row[6];
                final Double longitude = (Double) row[7];
                final Double altitude = (Double) row[8];

                final LocationDetailsDto locationDetailsDto = new LocationDetailsDto(locationDbId, locationType,
                        name, abbreviation, countryCode, countryName, latitude, longitude, altitude);
                if (!locationType.equalsIgnoreCase(LocationDAO.COUNTRY)) {
                    final AdditionalInfoDto additionalInfoDto = new AdditionalInfoDto(
                            locationDetailsDto.getLocationDbId());
                    additionalInfoDto.addInfo("province", (String) row[9]);
                    locationDetailsDto.setMapAdditionalInfo(additionalInfoDto);
                }
                locationList.add(locationDetailsDto);
            }
        }

        return locationList;

    } catch (final HibernateException e) {
        LocationDAO.LOG.error(e.getMessage(), e);
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocalLocationsByFilter", "", null,
                e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
}

From source file:org.generationcp.middleware.dao.ProjectMethodDAO.java

License:Open Source License

/**
 * Returns a list of {@link Method} records by project id.
 *
 * @param projectId the project id/* w  w  w.  j  a v a 2  s.co m*/
 * @param start the first row to retrieve
 * @param numOfRows the number of rows to retrieve
 * @return the list of {@link Method}s
 * @throws MiddlewareQueryException the MiddlewareQueryException
 */
@SuppressWarnings("unchecked")
public List<Integer> getByProjectId(Long projectId, int start, int numOfRows) throws MiddlewareQueryException {

    if (projectId == null) {
        return new ArrayList<Integer>();
    }

    try {
        SQLQuery query = getSession().createSQLQuery(ProjectMethod.GET_METHODS_BY_PROJECT_ID);
        query.setParameter("projectId", projectId.intValue());
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);
        return (List<Integer>) query.list();
    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getByProjectId(projectId=" + projectId
                + ") query from ProjectMethod: " + e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.dao.ProjectMethodDAO.java

License:Open Source License

@SuppressWarnings("rawtypes")
public List<ProjectMethod> getProjectMethodByProject(Project project, int start, int numOfRows)
        throws MiddlewareQueryException {

    if (project == null || project.getProjectId() == null) {
        return new ArrayList<ProjectMethod>();
    }/*from   ww  w.  j  av  a 2 s  .c o m*/

    try {
        SQLQuery query = getSession().createSQLQuery(ProjectMethod.GET_PROJECT_METHODS_BY_PROJECT_ID);
        query.setParameter("projectId", project.getProjectId().intValue());
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);
        List results = query.list();
        List<ProjectMethod> toReturn = new ArrayList<ProjectMethod>();
        for (Object o : results) {
            Object[] result = (Object[]) o;
            if (result != null) {
                Integer projectMethodId = (Integer) result[0];
                Integer methodId = (Integer) result[2];
                ProjectMethod projectMethod = new ProjectMethod(projectMethodId, project, methodId);
                toReturn.add(projectMethod);
            }
        }
        return toReturn;

    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getProjectMethodByProjectId(project=" + project
                + ") query from ProjectMethod: " + e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.dao.StudyDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Study> getByCountryUsingEqual(String country, int start, int numOfRows)
        throws MiddlewareQueryException {
    try {//from   ww  w .  j  ava 2 s .c  o  m
        SQLQuery query = getSession().createSQLQuery(Study.GET_BY_COUNTRY_USING_EQUAL);
        query.setParameter("country", country);
        query.addEntity("s", Study.class);
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);

        return query.list();

    } catch (HibernateException e) {
        throw new MiddlewareQueryException("Error with getByCountryUsingEqual(country=" + country
                + ") query from Study: " + e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.dao.StudyDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Study> getByCountryUsingLike(String country, int start, int numOfRows)
        throws MiddlewareQueryException {
    try {/*from   w  w  w  . j a v a2 s  .c o  m*/
        SQLQuery query = getSession().createSQLQuery(Study.GET_BY_COUNTRY_USING_LIKE);
        query.setParameter("country", country);
        query.addEntity("s", Study.class);
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);

        return query.list();
    } catch (HibernateException e) {
        throw new MiddlewareQueryException(
                "Error with getByCountryUsingLike(country=" + country + ") query from Study: " + e.getMessage(),
                e);
    }
}

From source file:org.generationcp.middleware.dao.StudyDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<Study> getBySeason(Season season, int start, int numOfRows) throws MiddlewareQueryException {
    try {/*from   w  w w .  j av a 2  s. co m*/
        SQLQuery query = getSession().createSQLQuery(Study.GET_BY_SEASON);

        if (season == Season.DRY) {
            query = getSession().createSQLQuery(Study.GET_BY_SEASON + Study.DRY_SEASON_CONDITION);
        } else if (season == Season.WET) {
            query = getSession().createSQLQuery(Study.GET_BY_SEASON + Study.WET_SEASON_CONDITION);
        }
        query.addEntity("s", Study.class);
        query.setFirstResult(start);
        query.setMaxResults(numOfRows);

        return query.list();

    } catch (HibernateException e) {
        throw new MiddlewareQueryException(
                "Error with getBySeason(season=" + season + ") query from Study: " + e.getMessage(), e);
    }
}

From source file:org.generationcp.middleware.manager.InventoryDataManagerImpl.java

License:Open Source License

@Override
public List<LotReportRow> generateReportOnDormantLots(final int year, final int start, final int numOfRows) {

    final SQLQuery query = this.getActiveSession().createSQLQuery(Lot.GENERATE_REPORT_ON_DORMANT);
    query.setParameter("year", year);
    query.setFirstResult(start);
    query.setMaxResults(numOfRows);/*w ww  .j a  v  a2 s.  co  m*/

    final LocationDataManagerImpl locationManager = new LocationDataManagerImpl(this.getSessionProvider());
    final OntologyDataManagerImpl ontologyManager = new OntologyDataManagerImpl(this.getSessionProvider());
    final List<LotReportRow> report = new ArrayList<>();

    final List<?> results = query.list();
    for (final Object o : results) {
        final Object[] result = (Object[]) o;
        if (result != null) {
            final LotReportRow row = new LotReportRow();

            row.setLotId((Integer) result[0]);

            row.setEntityIdOfLot((Integer) result[1]);

            row.setActualLotBalance(((Double) result[2]).doubleValue());

            final Location location = locationManager.getLocationByID((Integer) result[3]);
            row.setLocationOfLot(location);

            final Term termScale = ontologyManager.getTermById((Integer) result[4]);
            row.setScaleOfLot(termScale);

            report.add(row);
        }
    }
    return report;
}

From source file:org.jboss.seam.wiki.plugin.blog.BlogDAO.java

License:LGPL

public List<BlogEntry> findBlogEntriesInDirectory(WikiDirectory startDir, WikiDocument ignoreDoc, Pager pager,
        Integer year, Integer month, Integer day, String tag, boolean countComments) {

    final Map<Long, BlogEntry> blogEntryMap = new HashMap<Long, BlogEntry>();

    StringBuilder queryString = new StringBuilder();
    queryString.append("select").append(" ");
    for (int i = 0; i < getWikiDocumentSQLColumnNames().length; i++) {
        queryString.append(getWikiDocumentSQLColumnNames()[i]);
        if (i != getWikiDocumentSQLColumnNames().length - 1)
            queryString.append(", ");
    }/*from   w  ww.ja va  2  s.c o  m*/
    queryString.append(", '0' as COMMENT_COUNT").append(" ");
    queryString.append(getblogEntryFromClause(tag));
    queryString.append(getBlogEntryWhereClause(ignoreDoc, year, month, day, tag));

    queryString.append(" ");
    queryString.append("order by doc2.CREATED_ON desc");

    SQLQuery query = getSession().createSQLQuery(queryString.toString());

    bindBlogEntryWhereClause(query, startDir, ignoreDoc, year, month, day, tag);

    query.setComment("Finding all blogEntry documents recursively in dir: " + startDir.getName());
    query.addEntity(WikiDocument.class);
    query.addScalar("COMMENT_COUNT", Hibernate.LONG);
    query.setFirstResult(pager.getQueryFirstResult());
    query.setMaxResults(pager.getQueryMaxResults());

    query.setResultTransformer(new ResultTransformer() {
        public Object transformTuple(Object[] result, String[] aliases) {
            BlogEntry be = new BlogEntry();
            be.setEntryDocument((WikiDocument) result[0]);
            blogEntryMap.put(be.getEntryDocument().getId(), be); // Put in map so we can attach comment count later
            return be;
        }

        public List transformList(List list) {
            return list;
        }
    });

    List<BlogEntry> result = (List<BlogEntry>) query.list();

    if (countComments && result.size() > 0) {
        // The risk here is that pager.getQueryMaxResults() is too large for the IN() operator of some DBs...
        StringBuilder commentQueryString = new StringBuilder();
        commentQueryString.append("select doc.NODE_ID as DOC_ID, count(c3.NODE_ID) as COMMENT_COUNT")
                .append(" ");
        commentQueryString.append("from WIKI_DOCUMENT doc").append(" ");
        commentQueryString.append("left outer join WIKI_NODE c1 on doc.NODE_ID = c1.PARENT_NODE_ID")
                .append(" ");
        commentQueryString.append("left outer join WIKI_COMMENT c2 on c1.NODE_ID = c2.NODE_ID").append(" ");
        commentQueryString.append("left outer join WIKI_COMMENT c3 on c2.NS_THREAD = c3.NS_THREAD").append(" ");
        commentQueryString.append("where doc.NODE_ID in (:blogEntriesIds)").append(" ");
        commentQueryString.append("group by doc.NODE_ID");

        SQLQuery commentQuery = getSession().createSQLQuery(commentQueryString.toString());
        commentQuery.setComment("Finding comment count for blog entries");
        commentQuery.addScalar("DOC_ID");
        commentQuery.addScalar("COMMENT_COUNT");
        commentQuery.setParameterList("blogEntriesIds", blogEntryMap.keySet());

        commentQuery.setResultTransformer(new ResultTransformer() {
            public Object transformTuple(Object[] result, String[] aliases) {
                BlogEntry be = blogEntryMap.get(((BigInteger) result[0]).longValue());
                be.setCommentCount(((BigInteger) result[1]).longValue());
                return null;
            }

            public List transformList(List list) {
                return list;
            }
        });
        commentQuery.list();
    }

    return result;
}