实现类：
public class InfoDAOImpl extends BaseDao<Info>
1、List<Object[]> midlist=super.createSqlQuery(sqlStr, null);//根据SQL返回记录，每条记录对应对象数组
2、Map map = new HashMap<>();//map和InfoDTO中属性相同
String querySQL="SELECT b.contract_no from ……";
map.put("contract_no", StringType.INSTANCE);
List<Object> param = new ArrayList<Object>();
List<InfoDTO> list = super.createSqlQuery(querySQL, param, map, InfoForDTO.class);
均给予BaseDao
package com.common.dao;
import java.lang.reflect.ParameterizedType; import java.math.BigDecimal; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set;
import org.apache.log4j.Logger; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.criterion.CriteriaSpecification; import org.hibernate.transform.Transformers; import org.hibernate.type.Type; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.orm.hibernate4.HibernateCallback; import org.springframework.orm.hibernate4.support.HibernateDaoSupport; import org.springframework.stereotype.Component; import org.springframework.util.Assert;
import com.common.generic.page.PageResult;
@Component @SuppressWarnings("all") public class BaseDao<T> extends HibernateDaoSupport { private static Integer BATCH_SIZE = 500; private static Logger logger = Logger.getLogger(BaseDao.class); private Class<T> clazz;
public BaseDao() { ParameterizedType pt = (ParameterizedType) this.getClass() .getGenericSuperclass(); clazz = (Class<T>) pt.getActualTypeArguments()[0]; }
@Autowired public void setSuperSessionFactory(SessionFactory sessionFactory){ super.setSessionFactory(sessionFactory); }; public SessionFactory getCurrentSessionFactory(){ return super.getSessionFactory(); }; public void clear() throws Exception{ getCurrentSessionFactory().getCurrentSession().clear(); } public void merge(T entity) throws Exception{ getHibernateTemplate().merge(entity); } // public Session getSession(){ // return getCurrentSessionFactory().openSession(); // }
public void save(T entity) throws Exception{ getHibernateTemplate().save(entity); }
public void update(T entity) throws Exception{ getHibernateTemplate().update(entity); }
public void delete(T entity) throws Exception { getHibernateTemplate().delete(entity); } public void delete(Long id) throws Exception{ T t = (T)this.getHibernateTemplate().get(clazz, id); getHibernateTemplate().delete(t); } public void delete(String id) throws Exception{ T t = (T)this.getHibernateTemplate().get(clazz, id); getHibernateTemplate().delete(t); } @SuppressWarnings("rawtypes") public List<T> findAll() throws Exception{ List list = getHibernateTemplate().find( "from " + clazz.getName()); return list; } @SuppressWarnings("rawtypes") public List findAll(final String hql,final List<Object> value) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() {
public List doInHibernate(Session session) throws HibernateException { Query query = session.createQuery(hql); if(value != null){ for (int i = 0; i < value.size(); i++) { query.setParameter(i, value.get(i)); } } return query.list(); } }); return list; } public T findById(Long id) throws Exception{ return (T) getHibernateTemplate().get(clazz, id); }
public T findById(String id) throws Exception{ return (T) getHibernateTemplate().get(clazz, id); } public Integer deleteByIdList(final Long[] idList) throws Exception{ final String hql = "delete from " + clazz.getName() + " where id in (:idList)"; int result = getHibernateTemplate().execute(new HibernateCallback<Integer>() { public Integer doInHibernate(Session session) { Query query = session.createQuery(hql) .setParameterList("idList", idList); return query.executeUpdate(); } }); return result; } @SuppressWarnings("rawtypes") public List<T> findByPage(final Integer beginIndex,final Integer pageSize) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() { public List doInHibernate(Session session){ //此方法查询后返回list结果 List result =session.createQuery("from " + clazz.getName()).setFirstResult(beginIndex) .setMaxResults(pageSize).list(); return result; } }); return list; } @SuppressWarnings("rawtypes") public List findByPage(final String hql,final List<Object> param,final int beginIndex,final int pageSize) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() { public List doInHibernate(Session session){ Query query = session.createQuery(hql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } List result = query.setFirstResult(beginIndex).setMaxResults(pageSize).list(); return result; } }); return list; } /** * 获取唯一结果 * @param hql HQL语句 * @param param HQL参数 * @return Object对象 * @throws Exception */ public Object findUniqueResult(final String hql,final List<Object> param) throws Exception{ Object obj =getHibernateTemplate().execute(new HibernateCallback<Object>() { public Object doInHibernate(Session session){ Query query = session.createQuery(hql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } return query.uniqueResult(); } }); return obj; } /** * 获取唯一结果 * @param sql SQL语句 * @param param SQL参数 * @return Object对象 * @throws Exception */ public Object findUniqueResultForSql(final String sql,final List<Object> param) throws Exception{ Object obj =getHibernateTemplate().execute(new HibernateCallback<Object>() { public Object doInHibernate(Session session){ Query query = session.createSQLQuery(sql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } return query.uniqueResult(); } }); return obj; } /** * 获取唯一的结果 结果类型为:Integer * @param hql SQL语句 * @param param SQL参数 * @return Integer * @throws Exception */ public Integer findUniqueNumberResultForSql(final String sql,final List<Object> param) throws Exception{ Object object = findUniqueResultForSql(sql,param); return Integer.parseInt(object.toString()); } /** * 获取唯一的结果 结果类型为:Integer * @param hql HQL语句 * @param param HQL参数 * @return Integer * @throws Exception */ public Integer findUniqueNumberResult(final String hql,final List<Object> param) throws Exception{ Object object = findUniqueResult(hql,param); return Integer.parseInt(object.toString()); } /** * SQL查询 * @param sql sql语句 * @param param sql参数 * @return List集合 * @throws Exception */ public List createSqlQuery(final String sql,final List<Object> param) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() { public List doInHibernate(Session session){ Query query = session.createSQLQuery(sql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } List result = query.list(); return result; } }); return list; } /** * SQL查询 会自动将结果封装成对象 * @param sql sql * @param param 参数 * @param scalar 类型 * @param classes 对象 * @return * @throws Exception */ public List createSqlQuery(final String sql,final List<Object> param,final Map<String,Type> scalar,final Class<?> classes) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() { public List doInHibernate(Session session){ SQLQuery query = session.createSQLQuery(sql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } if(scalar != null && scalar.size() > 0){ Set<Map.Entry<String, Type>> scalarSet = scalar.entrySet(); Iterator<Map.Entry<String, Type>> iterator = scalarSet.iterator(); while(iterator.hasNext()){ Entry<String,Type> entry = iterator.next(); String columnName = entry.getKey(); Type type = entry.getValue(); query.addScalar(columnName, type); } query.setResultTransformer(Transformers.aliasToBean(classes)); } List result = query.list(); return result; } }); return list; } /** * * <p> * Description:SQL查询转化成对象<br /> * </p> * @author haijun_liu * @version 0.1 2015年10月23日 * @param sql * @param param * @param clas * @return * @throws Exception * List */ public List createSqlQuery(final String sql,final List<Object> param,final Class clas) throws Exception{ List list = getHibernateTemplate().execute(new HibernateCallback<List>() { public List doInHibernate(Session session){ Query query = session.createSQLQuery(sql).addEntity(clas); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } List result = query.list(); return result; } }); return list; } /** * 保存或更新对象 * @param entity 实体对象 */ public void saveOrUpdate(T entity){ getHibernateTemplate().saveOrUpdate(entity); } /** * 批量保存 * @param list list集合 * @throws Exception */ public void batchSaveOrUpdate(final List<T> list) throws Exception{ getHibernateTemplate().execute(new HibernateCallback<Object>() { public Object doInHibernate(Session session) throws HibernateException { logger.info(clazz.getCanonicalName() +"类批量添加开始...."); long startTime = System.currentTimeMillis(); if(list!= null && list.size() > 0){ for (int i = 0; i < list.size(); i++) { T entity =list.get(i); session.saveOrUpdate(entity); if(i % BATCH_SIZE==0){ session.flush(); session.clear(); } } session.flush(); session.clear(); } long endTime = System.currentTimeMillis(); logger.info(clazz.getCanonicalName() +"总耗时:"+(startTime-endTime)+"毫秒"); logger.info(clazz.getCanonicalName() +"类批量添加结束...."); return null; } }); } public int executeUpdate(final String sql,final List<Object> param) throws Exception{ Integer count = getHibernateTemplate().execute(new HibernateCallback<Integer>() { public Integer doInHibernate(Session session) throws HibernateException { Query query = session.createSQLQuery(sql); if(param!=null){ for(int i =0;i<param.size();i++){ query.setParameter(i, param.get(i)); } } return query.executeUpdate(); } }); return count; } public int batchExecuteUpdate(final List<String> sqlList,final List<Object[]> param) throws Exception{ Integer count = getHibernateTemplate().execute(new HibernateCallback<Integer>() { public Integer doInHibernate(Session session) throws HibernateException { int updateCount =0; for (int i = 0; i < sqlList.size(); i++) { String sql =sqlList.get(i); Query query = session.createSQLQuery(sql); if(param!=null){ for(int j =0;j<param.size();j++){ Object[] params = param.get(i); for (int k = 0; k < params.length; k++) { query.setParameter(k, params[k]); } } } updateCount += query.executeUpdate(); } return updateCount; } }); return count; } /* * ------------------------ kun - start ----------------------- */ /** * hql分页 * @param hql * @param params 查询参数 * @param startIndex 数据偏移量，如第一页从0条记录形开始 * @param pageSize 每页条数 * @return * @author Kun * @date 2016年5月24日 下午4:00:45 */ public PageResult<T> pagedQuery(String hql, List<Object> params, int startIndex, int pageSize) { // Count查询 String countQueryString = " select count(*) " + hql; Long totalCount = (Long) createQuery(countQueryString, params).uniqueResult();
if (totalCount < 1) return new PageResult<T>(); // 实际查询返回分页对象 // int startIndex = PageResult.getStartOfPage(pageNo, pageSize); // 改为直接从datatabels插件上取值 Query query = createQuery(hql, params); List<T> list = query.setFirstResult(startIndex).setMaxResults(pageSize) .list();
return new PageResult<T>(totalCount.intValue(), list, 1, pageSize); // pageCurrent值暂时无用，当前页码由插件记录 } /** * sql分页 * @param sal * @param startIndex 数据偏移量，如第一页从0条记录形开始 * @param pageSize 每页条数 * @param values 不定长查询参数 * @return * @author Kun * @date 2016年5月24日 下午4:00:45 */ public PageResult<Map> pageSqlQueryMap(String sql, int startIndex, int pageSize, Object... values) { // Count查询 String countQueryString = " select count(*) from ("+sql+") t"; BigDecimal totalCount1 = (BigDecimal) createSqlQuerys(countQueryString, values).uniqueResult(); Integer totalCount=Integer.parseInt(totalCount1.toString()); if (totalCount < 1) return new PageResult<Map>(); // 实际查询返回分页对象 SQLQuery query = createSqlQuerys(sql, values); query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP); List<Map> list = query.setFirstResult(startIndex).setMaxResults(pageSize).list(); return new PageResult<Map>(totalCount, list, 1, pageSize); } /** * 获取session * @return * @author Kun * @date 2016年6月6日 下午2:04:30 */ protected Session getCurrentSession() { return this.getCurrentSessionFactory().getCurrentSession(); } /** * 常规sql查询 * @param sql * @param values 不定长查询参数 * @return * @author Kun * @date 2016年6月6日 下午2:04:53 */ protected SQLQuery createSqlQuerys(String sql, Object... values) { Assert.hasText(sql); SQLQuery query = getCurrentSession().createSQLQuery(sql); //query.setCacheable(true); for (int i = 0; i < values.length; i++) { query.setParameter(i, values[i]); } return query; } /** * 常规hql查询 * @param hql * @param params * @return * @author Kun * @date 2016年6月6日 下午2:05:10 */ protected Query createQuery(String hql, List<Object> params) { Assert.hasText(hql); Query query = getCurrentSession().createQuery(hql); query.setCacheable(true); if(params != null) { for (int i = 0; i < params.size(); i++) { query.setParameter(i, params.get(i)); } } return query; } /* * ------------------------ kun - end ----------------------- */ }