Hibernate底层数据库操作函数BaseDao+具体实例

List ls = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				return session.createSQLQuery(sql).list();
			}
		});

  

String hql = "from EsbServiceV v where v.providerApp = "+ user.getApplicationId() +"   and v.enabledFlag = 'Y'";		
		
		int rows = pageQueryParameter.getRows(); // 获取每页显示的条数
		final int firstResult = (pageQueryParameter.getPage() - 1) * rows;
		final int maxResults = rows;
		final String hq = hql;
		List<EsbServiceV> list = getHibernateTemplate().executeFind(new HibernateCallback<Object>() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery(hq);
				query.setFirstResult(firstResult);
				query.setMaxResults(maxResults);// 定义返回的记录数
				List list = query.list();
				return list;
			}
		});

 

 

1.

package com.techson.boct.dao;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.impl.CriteriaImpl;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.techson.boct.page.PagerModel;

public class BaseDao<T> extends HibernateDaoSupport{
	public void delete(T o) {
		this.getHibernateTemplate().delete(o);
	}
	public void insert(T o) {
		this.getHibernateTemplate().save(o);
	}

	public T select(Class cls, Serializable id){
		return (T) this.getHibernateTemplate().get(cls, id);
	}
	public void update(T o) {
		this.getHibernateTemplate().update(o);
	}

	public List<T> getAll(String className) {
		String queryString = "from "+className;
		return this.getHibernateTemplate().find(queryString);
	}
	
	public List<T> getAllByQuery(String queryString) {
		return this.getHibernateTemplate().find(queryString);
	}
	
	public Query getQuery(final String queryStr)
	    throws DataAccessException {
		Query query = (Query) getHibernateTemplate().execute(
		        new HibernateCallback() {
		            public Object doInHibernate(Session session) {
		                return session.createQuery(queryStr);
		            }
		        });
		return query;
	}
	
	public List<T> getListByQuery(final String queryString,final int pageSize,final int pageIndex) {
//		Query query = this.getHibernateTemplate().getSessionFactory().getCurrentSession().createQuery(queryString);
		Query query = getQuery(queryString);
		query.setFirstResult(pageIndex * pageSize);
		query.setMaxResults(pageSize);
		return query.list();
	}

	public List<T> findByProperty(String className, String propertyName,
			Object value) throws Exception {
		String queryString = "from "+className+" where "+propertyName+" =?";
		return this.getHibernateTemplate().find(queryString,value);
	}
	
	public int findByCriteriaCount(final DetachedCriteria detachedCriteria){
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				return ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
			}
		}, true);
	}
	
	public Map<String,Object> findPageByCriteria(final DetachedCriteria detachedCriteria,final int pageSize,final int pageIndex)throws Exception {
		return (Map<String,Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				List items = criteria.setFirstResult(pageIndex).setMaxResults(pageSize).list();
				int totalCount = items.size();
				if(totalCount >= pageSize){
					//移除order   chanHo 20080415
					List orderEntrys=null;
					Field field=null;

					CriteriaImpl impl = (CriteriaImpl) criteria;
					try {
						field = CriteriaImpl.class.getDeclaredField("orderEntries");
						field.setAccessible(true);//这是关键：）
						orderEntrys = (List)field.get(impl);
						field.set(criteria,new ArrayList()); 
					} catch (Exception e) {
						e.printStackTrace();
					} 
					
					criteria.setProjection(null);
					criteria.setFirstResult(0).setMaxResults(1);
					totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
					criteria.setProjection(null);
					criteria.setProjection(null);
					criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); 
					//恢復order , 不過不建議重用detachedCriteria ,目前Criteria有很多問題!後面的代碼可以注釋掉
					/* List innerOrderEntries = null;
					try {
						innerOrderEntries = (List)field.get(criteria);
					} catch (Exception e) {
						e.printStackTrace();
					}
					for(int i=0;i<orderEntrys.size();i++){
						innerOrderEntries.add(orderEntrys.get(i));
					} */
				}
				Map reyurnMap = new HashMap<String,Object>();
				reyurnMap.put("count", totalCount);
				reyurnMap.put("page", items);
				return reyurnMap;
			}
		}, true);
	} 
	
	public List<T> findPageByCriteriaOrderBy(final DetachedCriteria detachedCriteria,final int pageSize,final int pageIndex)throws Exception {
		return (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)throws HibernateException {
				Criteria criteria = detachedCriteria.getExecutableCriteria(session);
				List items = criteria.setFirstResult(pageIndex * pageSize).setMaxResults(pageSize).list();
				return items;
			}
		}, true);
	} 

	public List<T> findByCriteria(DetachedCriteria detachedCriteria)
			throws Exception {
		return this.getHibernateTemplate().findByCriteria(detachedCriteria);
	}
	
	public Map<String,Object> findPageByQuery(final String queryStr,final int pageSize,final int pageIndex)throws Exception {
		return (Map<String,Object>) getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session)throws HibernateException {
					Query query = session.createQuery(queryStr);
					List items = query.setFirstResult(pageIndex * pageSize).setMaxResults(pageSize).list();
					long totalCount = items.size();
					if(totalCount == pageSize || totalCount==0){
						int fromIndex = queryStr.toLowerCase().indexOf("from");
						int orderIndex = queryStr.toLowerCase().indexOf("order");
						String countQueryStr = "";
						if(orderIndex!=-1){
							countQueryStr = "select count(*) " + queryStr.substring(fromIndex,orderIndex);
						}else{
							countQueryStr = "select count(*) " + queryStr.substring(fromIndex);
						}
						Query CountQuery = session.createQuery(countQueryStr);
						totalCount = (Long)CountQuery.uniqueResult();
						System.out.println("totalCount=" + totalCount);
					}else if(totalCount>0 && totalCount<pageSize){
						totalCount =  pageSize * pageIndex +  totalCount;
					}
					
					Map reyurnMap = new HashMap<String,Object>();
					reyurnMap.put("count", totalCount);
					reyurnMap.put("page", items);
					return reyurnMap;
				}
			}, true);
	}
	
	public PagerModel<T> findPageByCriteria(final int offset, final int pageSize,final DetachedCriteria detachedCriteria) throws Exception {
		  return (PagerModel<T>)getHibernateTemplate().execute(new HibernateCallback() {
		   public Object doInHibernate(Session session)throws HibernateException {
		    Criteria criteria = detachedCriteria.getExecutableCriteria(session);
		    List items = criteria.setFirstResult(offset).setMaxResults(pageSize).list();
		    
		    int totalCount = items.size();
		    if(totalCount>=pageSize) {
		     List orderEntrys=null;
		     Field field=null;
		     
		     CriteriaImpl impl = (CriteriaImpl) criteria;
		     try {
		      field = CriteriaImpl.class.getDeclaredField("orderEntries");
		      field.setAccessible(true);//这是关键：）
		      orderEntrys = (List)field.get(impl);
		      field.set(criteria,new ArrayList()); 
		     } catch (Exception e) {
		      e.printStackTrace();
		     } 
		     
		     criteria.setProjection(null);
		     criteria.setFirstResult(0).setMaxResults(1);
		     totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
		     criteria.setProjection(null);
		     criteria.setProjection(null);
		     criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY); 
		    } else {
		     List orderEntrys=null;
		     Field field=null;
		        
		     CriteriaImpl impl = (CriteriaImpl) criteria;
		     try {
		      field = CriteriaImpl.class.getDeclaredField("orderEntries");
		      field.setAccessible(true);//���ǹؼ�
		         orderEntrys = (List)field.get(impl);
		         field.set(criteria,new ArrayList()); 
		        } catch (Exception e) {
		         e.printStackTrace();
		        }
		     criteria.setProjection(null);
		     criteria.setFirstResult(0).setMaxResults(1);
		     totalCount = ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
		    }
		    
		    PagerModel<T> pm = new PagerModel<T>();
		    pm.setTotal(totalCount);
		    pm.setDatas(items);
		    pm.setPageSize(pageSize);
		    return pm;
		    }
		  },true);
		 }
}

 

具体实例：

删除：

public void deleteTypeEQ2(Long id ,String type){
		String hql = "delete from CompareHotel h where h.type = ? and h.hotelid = ?" ;
		Query query = getBaseDao().getQuery(hql);
		query.setLong(1, id);
		query.setString(0, type);
		query.executeUpdate();
		log.info("deleteTypeEQ2  delete ok.");
	}

public List<CompareHotel> findByIdAndType(String countryCode,String type) throws SystemException{
		String hql = "from CompareHotel h where h.type = "+type+" and h.countrycode = '" + countryCode+"'";
		Query query = this.getBaseDao().getQuery(hql);
		return query.list();
	}

public void deleteAll(String countrycode) throws SystemException {		
		String hql = "delete from CompareHotel gh where gh.countrycode = ?";
		Query query = getBaseDao().getQuery(hql);
		query.setString(0, countrycode);
		query.executeUpdate();
	}
public List<Agentmarket> findById(long id)throws SystemException{
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Agentmarket.class);
		detachedCriteria.add(Expression.eq("agent.internalid", id));
		List<Agentmarket> agentmarket = this.getBaseDao().findByCriteria(detachedCriteria);
		return agentmarket;
	}
public List<CompareHotel> findByhotelIdAndType(Long id,String type) throws SystemException{
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(CompareHotel.class);		
		detachedCriteria.add(Expression.eq("hotelid",id));
		detachedCriteria.add(Expression.eq("type",type));
		List<CompareHotel> l = (List<CompareHotel>)this.getBaseDao().findByCriteria(detachedCriteria);
		return l;
	}

 

public int deleteAllByPackageId(List<Long> packageId) {
		String hql=" delete from PackageHotel ph where ph.packageId in (:packageId)  ";
		Query query = this.baseDao.getQuery(hql);
		query.setParameterList("packageId",  packageId);
		int num = query.executeUpdate();
		return num;
	}


public Query getQuery(final String queryStr)
	    throws DataAccessException {
		Query query = (Query) getHibernateTemplate().execute(
		        new HibernateCallback() {
		            public Object doInHibernate(Session session) {
		                return session.createQuery(queryStr);
		            }
		        });
		return query;
	}

public List<Hotelbookheader> findBysfno(String sfno) throws SystemException {
  String hql = " FROM Hotelbookheader hbh where hbh.sfno =:sfno";
  Query query = this.getBaseDao().getQuery(hql);
  query.setParameter("sfno", sfno);
  List<Hotelbookheader> list = query.list();
  if (list.size() > 0) {
   return list;
  } else {
   return null;
  }
 }

 

 

。。。