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

List ls = this.getHibernateTemplate().executeFind(new HibernateCallback<Object>() {@Overridepublic 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 20080415List 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;  } }

 

 

。。。