Java Code Examples for org.hibernate.Query#setFirstResult()

/**
 * 获取所有的题目
 * @param s_question
 * @param pageBean
 * @return
 */
@Override
public List<Question> getQuestions(Question s_question, PageBean pageBean) throws Exception{

    Session session=getSessionFactory().getCurrentSession();
    StringBuffer hql=new StringBuffer("from Question");
    if(StringUtil.isNotEmpty(s_question.getSubject())){
        hql.append(" and subject like '%"+s_question.getSubject()+"%'");
    }
    Query query=session.createQuery(hql.toString().replaceFirst("and", "where"));
    if(pageBean!=null){
        query.setFirstResult(pageBean.getStart());
        query.setMaxResults(pageBean.getPageSize());
    }
    @SuppressWarnings("unchecked")
    List<Question> questionList=(List<Question>)query.list();
    return questionList;
}
 

@SuppressWarnings("unchecked")
private TaskResult loadTaskResult(Session session, TaskId taskId, int resultIndex) {
    DBTaskId dbTaskId = taskId(taskId);

    TaskData task = session.load(TaskData.class, dbTaskId);
    Query query = session.getNamedQuery("loadTasksResultByTask").setParameter("task", task);

    query.setMaxResults(1);
    query.setFirstResult(resultIndex);
    List<TaskResultData> results = (List<TaskResultData>) query.list();
    if (results.isEmpty()) {
        return null;
    } else {
        return results.get(0).toTaskResult(taskId);
    }
}
 

/**
 * 获取分页记录HqlQuery
 *
 * @param cq
 * @param isOffset
 * @return
 */
@SuppressWarnings("unchecked")
public PageList getPageList(final HqlQuery hqlQuery,
		final boolean needParameter) {

	Query query = getSession().createQuery(hqlQuery.getQueryString());
	if (needParameter) {
		query.setParameters(hqlQuery.getParam(),
				(Type[]) hqlQuery.getTypes());
	}
	int allCounts = query.list().size();
	int curPageNO = hqlQuery.getCurPage();
	int offset = PagerUtil.getOffset(allCounts, curPageNO,
			hqlQuery.getPageSize());
	String toolBar = PagerUtil.getBar(hqlQuery.getMyaction(), allCounts,
			curPageNO, hqlQuery.getPageSize(), hqlQuery.getMap());
	query.setFirstResult(offset);
	query.setMaxResults(hqlQuery.getPageSize());
	return new PageList(query.list(), toolBar, offset, curPageNO, allCounts);
}
 

@SuppressWarnings("unchecked")
public List<Organization> listOrganizations(int startIndex, int noOfRecords) 
        throws DbException { 

    try {
        Query query = session.createQuery("from Organization order by Name asc");        
        query.setMaxResults(noOfRecords);
        query.setFirstResult(startIndex);                

        return query.list();
    } 
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in listOrganizations", e);
    }

}
 

public List<DataFeedInfo> getAllDataFeedsByCodeAndConfidence(String code, double confidence, Integer offset, Integer limit){
	Query createQuery =null;
	try{
		createQuery = getCurrentSession().createSQLQuery(SELECT_DATA_FEED_BY_CODE_AND_CONFIDENCE);
		createQuery.setParameter(0, code);
		createQuery.setParameter(1, confidence);
		createQuery.setFirstResult(offset);
		createQuery.setMaxResults(limit);
		List results = createQuery.list();
		return adaptToDataFeedInfo(results);
	}
	catch(Exception e){
		logger.error("Exception while fetching data from db for collectionCode: "+code , e);
		return null;
	}
}
 

@SuppressWarnings("unchecked")
public List<T> getListByPage(String hql,int start) {
	Session session = this.getHibernateTemplate().getSessionFactory()
	.openSession();
	Query query = session.createQuery(hql);
	List<T> list = (List<T>) query.list();
	int totalSize = list.size();
	Page page = new Page(start, totalSize);
	int number = page.getPageSize();
	int startRow = (start) * number - number;
    query.setFirstResult(startRow); 
    query.setMaxResults(number); 
    List<T> listPage = (List<T>) query.list();
	session.close();
	return listPage;
}
 

@SuppressWarnings("unchecked")
public List<Organization> searchOrganizations(String name, int startIndex,
        int noOfRecords) 
                throws DbException { 
    try {
        Query query = session.createQuery("from Organization where name like ?");
        query.setString(0, "%" + name +"%");

        query.setMaxResults(noOfRecords);
        query.setFirstResult(startIndex);

        return query.list();
    } 
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in searchOrganizations", e);
    }
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<User> getLearnersByMostProgress(Long lessonId, String searchPhrase, Integer limit, Integer offset) {
StringBuilder queryText = new StringBuilder(LearnerProgressDAO.LOAD_LEARNERS_BY_MOST_PROGRESS);
// find the search phrase parts in any of name parts of the user
if (!StringUtils.isBlank(searchPhrase)) {
    String[] tokens = searchPhrase.trim().split("\\s+");
    for (String token : tokens) {
	queryText.append(" AND (u.first_name LIKE '%").append(token).append("%' OR u.last_name LIKE '%")
		.append(token).append("%' OR u.login LIKE '%").append(token).append("%')");
    }
}
queryText.append(LearnerProgressDAO.LOAD_LEARNERS_BY_MOST_PROGRESS_ORDER_CLAUSE);

Query query = getSession().createSQLQuery(queryText.toString()).addEntity(User.class).setLong("lessonId",
	lessonId);
if (limit != null) {
    query.setMaxResults(limit);
}
if (offset != null) {
    query.setFirstResult(offset);
}
return query.list();
   }
 

@Override
public List<EventVo> getFollowEvent(int uid, int page, int pagenum) {
	List<EventVo> listVo = null;
	Session session=HibernateSessionFactory.getSession();
	session.clear();
	String hql="select vo.eid from EventFollowerVo vo where vo.uid=:uid";
	Query query=session.createQuery(hql);
	query.setParameter("uid",uid);
	query.setFirstResult((page-1)*pagenum);
	query.setMaxResults(pagenum);
	List list = query.list();
	for(int i=0;i<list.size();i++){
		int eid = (int) list.get(i);
		String hql2="from EventVo vo where vo.eid=:eid";
		Query query2=session.createQuery(hql2);
		query2.setParameter("eid",eid);
		if(listVo == null){
			listVo = new ArrayList<EventVo>();
		}
		listVo.addAll(query2.list());
	}
	if(listVo ==null||listVo.size() == 0){
		list = null;
	}
	return listVo;
}
 

@SuppressWarnings("unchecked")
@Test
public final void whenRetrievingLastPage_thenCorrectSize() {
    final int pageSize = 10;

    final String countQ = "Select count (f.id) from Foo f";
    final Query countQuery = session.createQuery(countQ);
    final Long countResults = (Long) countQuery.uniqueResult();
    final int lastPageNumber = (int) (Math.ceil(countResults / pageSize));

    final Query selectQuery = session.createQuery("From Foo");
    selectQuery.setFirstResult((lastPageNumber - 1) * pageSize);
    selectQuery.setMaxResults(pageSize);
    final List<Foo> lastPage = selectQuery.list();

    assertThat(lastPage, hasSize(lessThan(pageSize + 1)));
}
 

@SuppressWarnings("unchecked")
public List<MailJob> searchMailJobs(Date startTime,
        Date endTime, int startIndex, int noOfRecords) 
                throws DbException { 
    try {
        Query query = session.createQuery("from MailJob where started >= ? and started <= ? order by Id asc");
        query.setLong(0, SqlUtil.convertTimestamp(startTime));
        query.setLong(1, SqlUtil.convertTimestamp(endTime));
        query.setMaxResults(noOfRecords);
        query.setFirstResult(startIndex);

        return query.list();
    } 
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in searchMailJobs", e);
    }
}
 

@SuppressWarnings("rawtypes")
@Override
public PageBean getPageBean(int pageNum, int pageSize,QueryHelper queryHelper) {
	System.out.println("-------> DaoSupportImpl.getPageBean( int pageNum, int pageSize, QueryHelper queryHelper )");
	// 参数列表
	List<Object> parameters = queryHelper.getParameters();
	// 查询本页的数据列表
	Query listQuery = getSession().createQuery(queryHelper.getListQueryHql()); // 创建查询对象
	if (parameters != null) { // 设置参数
		for (int i = 0; i < parameters.size(); i++) {
			listQuery.setParameter(i, parameters.get(i));
		}
	}
	listQuery.setFirstResult((pageNum - 1) * pageSize);
	listQuery.setMaxResults(pageSize);
	List list = listQuery.list(); // 执行查询
	// 查询总记录数量
	Query countQuery = getSession().createQuery(queryHelper.getCountQueryHql());
	if (parameters != null) { // 设置参数
		for (int i = 0; i < parameters.size(); i++) {
			countQuery.setParameter(i, parameters.get(i));
		}
	}
	Long count = (Long) countQuery.uniqueResult(); // 执行查询
	return new PageBean(pageNum, pageSize, count.intValue(), list);
}
 

/**
 * 获取分页记录HqlQuery
 * 
 * @param cq
 * @param isOffset
 * @return
 */
@SuppressWarnings("unchecked")
public PageList getPageList(final HqlQuery hqlQuery,
		final boolean needParameter) {

	Query query = getSession().createQuery(hqlQuery.getQueryString());
	if (needParameter) {
		query.setParameters(hqlQuery.getParam(),
				(Type[]) hqlQuery.getTypes());
	}
	int allCounts = query.list().size();
	int curPageNO = hqlQuery.getCurPage();
	int offset = PagerUtil.getOffset(allCounts, curPageNO,
			hqlQuery.getPageSize());
	String toolBar = PagerUtil.getBar(hqlQuery.getMyaction(), allCounts,
			curPageNO, hqlQuery.getPageSize(), hqlQuery.getMap());
	query.setFirstResult(offset);
	query.setMaxResults(hqlQuery.getPageSize());
	return new PageList(query.list(), toolBar, offset, curPageNO, allCounts);
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<User> getLearnersLatestCompletedForLesson(Long lessonId, Integer limit, Integer offset) {
Query query = getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_LATEST_COMPLETED_BY_LESSON)
	.setLong("lessonId", lessonId);
if (limit != null) {
    query.setMaxResults(limit);
}
if (offset != null) {
    query.setFirstResult(offset);
}
return query.list();
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<User> getLearnersLatestByActivity(Long activityId, Integer limit, Integer offset) {
Query query = getSession().createSQLQuery(LearnerProgressDAO.LOAD_LEARNERS_LATEST_BY_ACTIVITY)
	.addEntity(User.class).setLong("activityId", activityId);
if (limit != null) {
    query.setMaxResults(limit);
}
if (offset != null) {
    query.setFirstResult(offset);
}
return query.list();
   }
 

@Deprecated
@Override
public PageBean getPageBean(int pageNum, int pageSize, String hql,
		List<Object> parameters) {
	//查询数据列表
	Query listQuery=getSession().createQuery(hql);//创建查询对象
	if(parameters!=null)//设置参数
	{
		for(int i=0;i<parameters.size();i++)
		{
			listQuery.setParameter(i, parameters.get(i));
		}
	}
	listQuery.setFirstResult((pageNum-1)*pageSize);
	listQuery.setMaxResults(pageSize);
	List list=listQuery.list();//执行查询
	//查询记录总数
	Query countQuery=getSession().createQuery("SELECT COUNT(*) "+hql);
	if(parameters!=null)//设置参数
	{
		for(int i=0;i<parameters.size();i++)
		{
			countQuery.setParameter(i, parameters.get(i));
		}
	}
	Long count=(Long) countQuery.uniqueResult();//执行查询
	
	return new PageBean(pageNum, pageSize, count.intValue(), list);
}
 

@SuppressWarnings("unchecked")
public List<T> getListByPageByDesc(int currentPage, int pageSize,String orderId) {
	Session session = this.getHibernateTemplate().getSessionFactory()
			.openSession();
	Query query = session.createQuery("from  "
	+ getPersistentClass().getName() + " order by " + orderId
	+ " desc");
	int startRow = (currentPage) * pageSize - pageSize;
	query.setFirstResult(startRow);
	query.setMaxResults(pageSize);
	List<T> listPage = (List<T>) query.list();
	session.close();
	return listPage;
}
 

@Override
public List executeSqlQuery(String sql, int firstResult, int maxResults) {
    Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
    if (firstResult >= 0 && maxResults >= 0) {
        query.setFirstResult(firstResult);
        query.setMaxResults(maxResults);
    }
    return query.list();
}
 

@SuppressWarnings("unchecked")
   private List<UserDTO> getAllUsersPage(String queryText, String entityName, Integer page, Integer size,
    String sortBy, String sortOrder, String searchPhrase) {
switch (sortBy) {
    case "userId":
	sortBy = entityName + ".userId + 0 ";
	break;
    case "login":
	sortBy = entityName + ".login ";
	break;
    case "firstName":
	sortBy = entityName + ".firstName ";
	break;
    case "lastName":
	sortBy = entityName + ".lastName ";
	break;
    case "email":
	sortBy = entityName + ".email ";
	break;
}

StringBuilder queryBuilder = new StringBuilder(queryText);
// support for custom search from a toolbar
UserDAO.addNameSearch(queryBuilder, entityName, searchPhrase);
//order by
queryBuilder.append(" ORDER BY ").append(sortBy).append(sortOrder);

Query query = getSession().createQuery(queryBuilder.toString());
if (size != null) {
    query.setMaxResults(size);
    if (page != null) {
	query.setFirstResult(page * size);
    }
}
List<Object[]> list = query.list();

//group by userId as long as it returns all completed visitLogs for each user
List<UserDTO> userDtos = new ArrayList<UserDTO>();
for (Object[] element : list) {
    Integer userId = ((Number) element[0]).intValue();
    String login = (String) element[1];
    String firstName = (String) element[2];
    String lastName = (String) element[3];
    String email = (String) element[4];
    Long portraitUuid = element[5] != null ? ((Number) element[5]).longValue() : null;

    UserDTO userDto = new UserDTO(userId, firstName, lastName, login, null, null, null, email, null, null, null,
	    null, false, null, portraitUuid);

    userDtos.add(userDto);
}

return userDtos;
   }
 

/**
 * <p>Returns a List of <b>T</b> entities, where HQL clauses can be
 * specified.</p>
 * 
 * Note: This method is useful in read only. It can be use to delete or 
 * create <b>T</b> entities, but caution with <code>top</code> and 
 * <code>skip</code> arguments.
 * 
 * @param clauses query clauses (WHERE, ORDER BY, GROUP BY), if null no
 * clauses are apply.
 * @param skip number of entities to skip.
 * @param n  number of entities max returned.
 * @return a list of <b>T</b> entities.
 */
@SuppressWarnings ("unchecked")
public List<T> scroll (final String clauses, final int skip, final int n)
{
   StringBuilder hql = new StringBuilder ();
   hql.append ("FROM ").append (entityClass.getName ());
   if (clauses != null)
      hql.append (" ").append (clauses);

   Session session;
   boolean newSession = false;
   try
   {
      session = getSessionFactory ().getCurrentSession ();
   }
   catch (HibernateException e)
   {
      session = getSessionFactory ().openSession ();
      newSession = true;
   }

   Query query = session.createQuery (hql.toString ());
   if (skip > 0) query.setFirstResult (skip);
   if (n > 0) 
   {
      query.setMaxResults (n);
      query.setFetchSize (n);
   }
   
   logger.info("Execution of HQL: " + hql.toString ());
   long start = System.currentTimeMillis ();

   List<T> result = (List<T>) query.list ();
   logger.info("HQL executed in " + 
      (System.currentTimeMillis() -start) + "ms.");

   if (newSession)
   {
      session.disconnect ();
   }

   return result;
}