Java Query類代碼示例

import org.hibernate.Query; //導入依賴的package包/類
@Override
public List<Category> getInterestCategory(List<Integer> interestIds) {
	
	 List<Category> categories = new ArrayList<Category>();
	 Session session  =  HibernateUtils.getSession();//生成Session實例
	 Transaction tx = session.beginTransaction();//生成事務實例
	 try {
		 ListToString listToString = new ListToString();
		 String idString = listToString.getListToString(interestIds);
		  String hql =  "select new Category(category_id,category_name) from Category";
		  String hql2 = " where category_id in " +idString;
		   if (!idString.equals("")) {
			   Query query = session.createQuery(hql+hql2);
			   categories = query.list();
			   tx.commit();
		   }
	     //調用session的get()方法，找到此用戶到內存中 
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();//事務回滾
	}finally{
		HibernateUtils.closeSession();//關閉session實例
	}
	 return categories;  
}
 

import org.hibernate.Query; //導入依賴的package包/類
/**
   * Checks that combination of Instructor/Dept 
   * does not already exist
   * @param frm
   * @return
   */
  protected boolean isDeptInstructorUnique(
          InstructorEditForm frm, 
       HttpServletRequest request ) {
      
      String query = "from DepartmentalInstructor " +
      				"where externalUniqueId=:puid and department.uniqueId=:deptId";
      if (frm.getInstructorId()!=null && frm.getInstructorId().trim().length()>0) {
          query += " and uniqueId!=:uniqueId";
      }
      
      DepartmentalInstructorDAO ddao = new DepartmentalInstructorDAO();
      org.hibernate.Session hibSession = ddao.getSession();
      
HttpSession httpSession = request.getSession();
String deptId = (String) httpSession.getAttribute(Constants.DEPT_ID_ATTR_NAME);
     
      Query q = hibSession.createQuery(query);
      q.setString("puid", frm.getPuId().trim());
      q.setLong("deptId", Long.parseLong(deptId));
      if (frm.getInstructorId()!=null && frm.getInstructorId().trim().length()>0) {
          q.setString("uniqueId", frm.getInstructorId().trim());
      }
      
      return (q.list().size()==0);
  }
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
@Transactional(propagation = Propagation.MANDATORY)
public void deleteForTaxonomy(final Taxonomy taxonomy)
{
	getHibernateTemplate().execute(new HibernateCallback()
	{
		@Override
		public Object doInHibernate(Session session)
		{
			Query query = session
				.createQuery("delete from TermAttributes ta where ta.term.id in (from Term where taxonomy = :taxonomy)");
			query.setParameter("taxonomy", taxonomy);
			query.executeUpdate();
			query = session.createQuery("delete from Term where taxonomy = :taxonomy");
			query.setParameter("taxonomy", taxonomy);
			query.executeUpdate();
			return null;
		}
	});
}
 

import org.hibernate.Query; //導入依賴的package包/類
/**
 * 
 * @param hql傳入的hql語句
 * @param pageCode當前頁
 * @param pageSize每頁顯示大小
 * @return
 */
public List doSplitPage(final String hql,final int pageCode,final int pageSize){
    //調用模板的execute方法，參數是實現了HibernateCallback接口的匿名類，
    return (List) this.getHibernateTemplate().execute(new HibernateCallback(){
        //重寫其doInHibernate方法返回一個object對象，
        public Object doInHibernate(Session session)
                throws HibernateException, SQLException {
            //創建query對象
            Query query=session.createQuery(hql);
            //返回其執行了分布方法的list
            return query.setFirstResult((pageCode-1)*pageSize).setMaxResults(pageSize).list();
             
        }
         
    });
     
}
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
@SuppressWarnings("unchecked")
protected void executeDataMigration(HibernateMigrationHelper helper, MigrationResult result, Session session)
	throws Exception
{
	// System SMTP settings
	Query query = session.createQuery("FROM SystemConfig WHERE key = 'smtppassword'");
	List<FakeSystemConfig> sysconf = query.list();
	if( !Check.isEmpty(sysconf) )
	{
		FakeSystemConfig sc = sysconf.get(0); // Should only be one
		String encpwd = encryptionService.encrypt(sc.value);
		sc.value = encpwd;
		session.save(sc);
		result.incrementStatus();
	}

	// The end
	session.flush();
	session.clear();
}
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
@Transactional(propagation = Propagation.MANDATORY)
public int deleteAllForUserKeyReasons(final ItemKey itemKey, final String userId, final Collection<String> reasons)
{
	return ((Number) getHibernateTemplate().execute(new HibernateCallback()
	{
		@Override
		public Object doInHibernate(Session session)
		{
			if( Check.isEmpty(reasons) )
			{
				return Integer.valueOf(0);
			}
			Query query = session.createQuery(
				"delete from Notification where itemid = :itemkey and userTo = :user and reason in (:reasons) and institution = :inst");
			query.setParameter(ITEMKEY, itemKey.toString());
			query.setParameter(USER, userId);
			query.setParameterList(REASONS, reasons);
			query.setParameter(INST, CurrentInstitution.get());
			return query.executeUpdate();
		}
	})).intValue();
}
 

import org.hibernate.Query; //導入依賴的package包/類
@SuppressWarnings("unchecked")
private void deleteSystemUrl(String parentId,Session session){
	List<Url> urls=session.createQuery("from "+Url.class.getName()+" where parentId=:parentId").setString("parentId", parentId).list();
	session.createQuery("delete "+Url.class.getName()+" where parentId=:parentId").setString("parentId", parentId).executeUpdate();
	for(Url url:urls){
		int count=0;
		Query countQuery=session.createQuery("select count(*) from "+Url.class.getName()+" where parentId=:parentId").setString("parentId", url.getId());
		Object countObj=countQuery.uniqueResult();
		if(countObj instanceof Long){
			count=((Long)countObj).intValue();
		}else if(countObj instanceof Integer){
			count=((Integer)countObj).intValue();
		}
		if(count>0){
			deleteSystemUrl(url.getId(),session);	
		}
	}
}
 

import org.hibernate.Query; //導入依賴的package包/類
/**
 * 查詢target裏麵指定的字段
 * 
 * @param <T> 要查詢的實體類
 * 
 * @param t 要查詢的類
 * @param pageSize
 * @param currentPage
 * @param constraint 查詢條件
 * @param desc
 * @param asc
 * @param queryRecordCount 是否查詢總記錄數(記錄很多時查詢較費時間),若傳false,則返回的page實體類的記錄數為Long.MAX_VALUE,<br>
 * 			前端可做無限分頁
 * @param selectField 要查詢的字段,若不傳,則查詢該類所有字段,page裏麵放的是實體類,否則放的是map,不過map裏麵的key的'.'會被替換成'__'<br>
 * 			(這個是可變參數,沒有請不傳,切忌傳null!)
 * @return
 */
public final static <T> Page<T> listCustomField(Class<?> t,int pageSize,int currentPage,Map<String, Object> constraint,
		List<String> desc,List<String> asc,boolean queryRecordCount,String[] groupBy,String... selectField){
	try {
		StringBuffer hql = getSelectHql(t, constraint, desc, asc, groupBy, selectField);
		Long recordCount = Long.MAX_VALUE;
		if (queryRecordCount) {
			recordCount = getRecordCount(constraint, hql);
		}
		
		Query query = createQuery(constraint,hql);
		
		faging(pageSize,currentPage, query);
		@SuppressWarnings("unchecked")
		List<T> list = query.list();
		
		return setPage(currentPage, recordCount, pageSize, list);
	} catch (Exception e) {
		setException(e);
		return null;
	}finally{
		closeSession();
	}
}
 

import org.hibernate.Query; //導入依賴的package包/類
/**
 * 獲取user_id對應的已提交的Task
 */
@Override
public List<Task> geTasksOfUnconfirmed(int user_id,int start,int num) {
	List<Task> tasks = new ArrayList<Task>();  
	Session session = HibernateUtils.getSession();//生成Session實例
	 Transaction tx = session.beginTransaction();//生成事務實例
	 try {
		 
		 Query query =  session.createQuery("SELECT  new Task( task_id,task_img_amount,task_start_time,user_id) FROM Task WHERE task_iscommit = 1 AND user_id = "+user_id);
		 query.setFirstResult(start-1);
		 query.setMaxResults(num);
		 tasks = query.list(); 
	     tx.commit();//提交事務
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();//事務回滾
	}finally{
		HibernateUtils.closeSession();//關閉session實例
	}
	 return tasks; 
}
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
   public Integer getCountRoleForOrg(Integer roleId, Integer orgId, String searchPhrase) {
StringBuilder queryTextBuilder = new StringBuilder(RoleDAO.COUNT_ROLE_FOR_ORG);
if (!StringUtils.isBlank(searchPhrase)) {
    String[] tokens = searchPhrase.trim().split("\\s+");
    for (String token : tokens) {
	queryTextBuilder.append(" AND (uor.userOrganisation.user.firstName LIKE '%").append(token)
		.append("%' OR uor.userOrganisation.user.lastName LIKE '%").append(token)
		.append("%' OR uor.userOrganisation.user.login LIKE '%").append(token).append("%')");
    }
}

Query query = getSession().createQuery(queryTextBuilder.toString());
query.setInteger("roleId", roleId.intValue());
query.setInteger("orgId", orgId.intValue());
Object value = query.uniqueResult();
return new Integer(((Number) value).intValue());
   }
 

import org.hibernate.Query; //導入依賴的package包/類
@SuppressWarnings({ "rawtypes", "unchecked" })
public final static Page getQueryResult(String hql,String domainSimpleName,
		Map<String,Object> map,int pageSize,int currentPage){
	try {
		StringBuffer sb = new StringBuffer(hql);
		if (map != null && domainSimpleName != null) {
			appendHqlWhere(domainSimpleName, sb, map);
		}
		
		Long recordCount = getRecordCount(map, sb);
		
		Query createQuery = getSession().createQuery(sb.toString());
		if (map != null) {
			setMapParam(map, createQuery);
		}
		faging(pageSize, currentPage, createQuery);
		
		return setPage(currentPage, recordCount, pageSize, createQuery.list());
		//return createQuery.list();
	} catch (Exception e) {
		setException(e);
		return null;
	}finally{
		closeSession();
	}
}
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
@Transactional
public ActivateRequest getLastActive(final String type, final Item item, final String attachment)
{
	return (ActivateRequest) getHibernateTemplate().execute(new HibernateCallback()
	{
		@Override
		@Transactional
		public Object doInHibernate(Session session)
		{
			Query query = session.createQuery("from ActivateRequest req"
				+ " where req.type = :type and req.item = :item and req.attachment = :att and req.status = :status"
				+ " order by req.until desc");
			query.setFetchSize(1);
			query.setFirstResult(0);
			query.setString("type", type);
			query.setString("att", attachment);
			query.setEntity("item", item);
			query.setInteger("status", ActivateRequest.TYPE_ACTIVE);

			List<?> l = query.list();
			return l.size() == 0 ? null : l.get(0);
		}
	});
}
 

import org.hibernate.Query; //導入依賴的package包/類
public static List<TimePattern> findAll(Long sessionId, Boolean visible) {
	String query = "from TimePattern tp " +
				 	"where tp.session.uniqueId=:sessionId";
	if (visible!=null) 
	    query += " and visible=:visible";
	
	org.hibernate.Session hibSession = new TimePatternDAO().getSession();
	Query q = hibSession.createQuery(query);
	q.setCacheable(true);
	q.setLong("sessionId", sessionId.longValue());
	if (visible!=null) 
	    q.setBoolean("visible", visible.booleanValue());
	
    List<TimePattern> v = q.list();
    Collections.sort(v);
    return v;
}
 

import org.hibernate.Query; //導入依賴的package包/類
/**
 * Prepare the given Query object, applying cache settings and/or
 * a transaction timeout.
 * @param queryObject the Query object to prepare
 * @see #setCacheQueries
 * @see #setQueryCacheRegion
 */
protected void prepareQuery(Query queryObject) {
	if (isCacheQueries()) {
		queryObject.setCacheable(true);
		if (getQueryCacheRegion() != null) {
			queryObject.setCacheRegion(getQueryCacheRegion());
		}
	}
	if (getFetchSize() > 0) {
		queryObject.setFetchSize(getFetchSize());
	}
	if (getMaxResults() > 0) {
		queryObject.setMaxResults(getMaxResults());
	}

	SessionHolder sessionHolder =
			(SessionHolder) TransactionSynchronizationManager.getResource(getSessionFactory());
	if (sessionHolder != null && sessionHolder.hasTimeout()) {
		queryObject.setTimeout(sessionHolder.getTimeToLiveInSeconds());
	}
}
 

import org.hibernate.Query; //導入依賴的package包/類
@Override
public List<User> getAllUsers(int start,int num) { 
	 List<User> users = new ArrayList<User>();
        Session session = HibernateUtils.getSession();//生成Session實例
	 Transaction tx = session.beginTransaction();//生成事務實例 
	 
	 try {
		  Query query = session.createQuery("select new User(user_id,username,tel_num,integral,accuracy,is_frozen) from User order by user_id");
		  query.setFirstResult(start-1);
		  System.out.println(start-1);
		  System.out.println(num);
		  query.setMaxResults(num);
		  users = query.list();
		 tx.commit();//提交事務
	} catch (Exception e) {
		e.printStackTrace();
		tx.rollback();
	}finally {
		HibernateUtils.closeSession();//關閉session實例
	}
	 return users;  
}