List of usage examples for org.hibernate SQLQuery setMaxResults
@Override
Query<R> setMaxResults(int maxResult);
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 {/* ww w . j a v a 2 s . c o 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 w ww . j a v a2 s. c o 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 {/*ww w. j av a2s .c om*/ 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 . c o 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>(); }/* ww w. jav a 2s. co 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 {// w w w .ja v a2s . 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 www.j a v a 2s.c om*/ 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 {/* w w w . j a v a 2 s . c o 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);/* w w w. ja v a 2 s .c om*/ query.setMaxResults(numOfRows); 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 w w . j a v a 2 s .c om 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; }