medical/tianti-common/src/main/java/com/jeff/tianti/common/dao/CustomBaseSqlDaoImpl.java

package com.jeff.tianti.common.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.springframework.beans.factory.annotation.Autowired;

import com.jeff.tianti.common.entity.PageModel;

public class CustomBaseSqlDaoImpl {
	
	@Autowired
	private EntityManager em;
	
	public List<Map<String, Object>> querySqlObjects(String sql, Integer currentPage,Integer rowsInPage){
		return this.querySqlObjects(sql, null, currentPage, rowsInPage);
	}
	
	public List<Map<String, Object>> querySqlObjects(String sql){
		return this.querySqlObjects(sql, null, null, null);
	}
	
	public List<Map<String, Object>> querySqlObjects(String sql, List<Object> params){
		return this.querySqlObjects(sql, params, null, null);
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> querySqlObjects(String sql, Object params, Integer currentPage,Integer rowsInPage){
		Query qry = em.createNativeQuery(sql);
		SQLQuery s = qry.unwrap(SQLQuery.class);
		
		//设置参数
		if(params != null){
			if(params instanceof List){
				List<Object> paramList = (List<Object>) params;
				for(int i = 0, size = paramList.size(); i < size; i++){
					qry.setParameter(i+1, paramList.get(i));
				}
			}else if(params instanceof Map){
				Map<String, Object> paramMap = (Map<String, Object>) params;
				for(String key : paramMap.keySet()){
					qry.setParameter(key, paramMap.get(key));
				}
			}
		}
		
		if (currentPage != null && rowsInPage != null) {//判断是否有分页
			// 起始对象位置
			qry.setFirstResult(rowsInPage * (currentPage - 1));
			// 查询对象个数
			qry.setMaxResults(rowsInPage);
		}
		s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> resultList=new ArrayList<Map<String, Object>>();
		try {
			resultList=s.list();
		} catch (Exception e) {
		}finally{
			em.close();
		}
		return resultList;
	}
	
	public PageModel<Map<String, Object>> querySqlObjects(String sql,
			String sbCount, Map<String, Object> params, Integer currentPage,Integer rowsInPage){
		PageModel<Map<String, Object>> pageModel = new PageModel<Map<String, Object>>();
		
		List<Map<String, Object>> list = this.querySqlObjects(sql, params, currentPage, rowsInPage);

		pageModel.setList(list);
		
		if(currentPage == null || rowsInPage == null){
			pageModel.setTotalCount(list == null ? 0 : list.size());
		}else{
			Integer count = this.queryCountBySql(sbCount, params);
			
			pageModel.setCurrentPage(currentPage);
			pageModel.setTotalCount(count);
			pageModel.setPageSize(rowsInPage);
			
			int totalPage = 0;
			if(count%rowsInPage == 0){
				totalPage = count / rowsInPage;
			}else{
				totalPage = count / rowsInPage + 1;
			}
			
			pageModel.setTotalPage(totalPage);
		}
		
		
		return pageModel;
	}
	
	
	public int getCount(String sql){
		String sqlCount="select count(0) count_num from ("+sql+") as total";
		List<Map<String, Object>> list = this.querySqlObjects(sqlCount);
		if(list.size() > 0){
			int countNum=((BigInteger) list.get(0).get("count_num")).intValue();
			return countNum;
		}else{
			return 0;
		}
	}
	
	/**
	 * 处理sql语句
	 * 
	 * @param _strSql
	 * @return
	 */
	public String toSql(String _strSql) {
		String strNewSql = _strSql;

		if (strNewSql != null) {
			strNewSql = regReplace("'", "''", strNewSql);
		} else {
			strNewSql = "";
		}

		return strNewSql;
	}

	private String regReplace(String strFind, String strReplacement, String strOld) {
		String strNew = strOld;
		Pattern p = null;
		Matcher m = null;
		try {
			p = Pattern.compile(strFind);
			m = p.matcher(strOld);
			strNew = m.replaceAll(strReplacement);
		} catch (Exception e) {
		}

		return strNew;
	}
	
	/**
	 * 根据hql语句查询数据
	 * @param hql
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public List queryForList(String hql, List<Object> params){
		Query query = em.createQuery(hql);
		List list = null;
		try {
			if(params != null && !params.isEmpty()){
				for(int i=0,size=params.size();i<size;i++){
					query.setParameter(i+1, params.get(i));
				}
			}
			list = query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}
		return list;
	}
	
	@SuppressWarnings("rawtypes")
	public List queryByMapParams(String hql, Map<String, Object> params, Integer currentPage,Integer pageSize){
		//EntityManager em = this.emf.createEntityManager();
		Query query = em.createQuery(hql);
		List list = null;
		try {
			if(params != null && !params.isEmpty()){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
			
			if(currentPage != null && pageSize != null){
				query.setFirstResult((currentPage-1)*pageSize);
				query.setMaxResults(pageSize);
			}
			list = query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}
		
		return list;
	}
	
	@SuppressWarnings("rawtypes")
	public List queryByMapParams(String hql, Map<String, Object> params){
		return queryByMapParams(hql, params, null, null);
	}
	
	@SuppressWarnings("rawtypes")
	public List queryForList(String hql){
		return queryForList(hql, null);
	}
	
	/**
	 * 根据hql语句和分页条件查找分页数据
	 * @param hql
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel queryForPage(String hql,int currentPage,int pageSize){
		PageModel page = new PageModel();
		List list = null;
		Integer totalCount = 0;
		Integer totalPage = 0; //总页数
		try {
			int firstResult = (currentPage-1)*pageSize;
			Query query = em.createQuery(hql);
			query.setMaxResults(pageSize);
			query.setFirstResult(firstResult);
			list = query.getResultList();
			
			Query query2 = em.createQuery(hql);
			List list2 = query2.getResultList();
			totalCount = (list2 == null) ? 0 : list2.size();
			if(totalCount % pageSize == 0){
				totalPage = totalCount/pageSize;
			}else{
				totalPage = totalCount/pageSize + 1;
			}
			
			page.setCurrentPage(currentPage);
			page.setList(list);
			page.setPageSize(pageSize);
			page.setTotalCount(totalCount);
			page.setTotalPage(totalPage);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}		
		return page;
	}
	
	/**
	 * 
	 * @param hql
	 * @param hqlCount  查询所有数据的hql
	 * @param params
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageModel queryForPageWithParams(String hql, String hqlCount, Map<String,Object> params, int currentPage,int pageSize){
		PageModel page = new PageModel();
		List<Object> list = null;
		Integer totalCount = 0;
		Integer totalPage = 0;
		
		//EntityManager em = this.emf.createEntityManager();
		Query query = em.createQuery(hql);
		
		try {
			
			if(params != null){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
			
			query.setMaxResults(pageSize);
			query.setFirstResult((currentPage-1)*pageSize);
			list = query.getResultList();
			
			
			totalCount = this.queryCount(hqlCount, params).intValue();
			if(totalCount % pageSize == 0){
				totalPage = totalCount/pageSize;
			}else{
				totalPage = totalCount/pageSize + 1;
			}
			
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			page.setList(list);
			page.setTotalCount(totalCount);
			page.setTotalPage(totalPage);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}			
		return page;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageModel queryForPageWithParams(String hql,Map<String,Object> params, int currentPage,int pageSize){
		PageModel page = new PageModel();
		List<Object> list = null;
		Integer totalCount = 0;
		Integer totalPage = 0;
		
		//EntityManager em = this.emf.createEntityManager();
		Query query = em.createQuery(hql);
		
		try {
			
			if(params != null){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
			
			query.setMaxResults(pageSize);
			query.setFirstResult((currentPage-1)*pageSize);
			list = query.getResultList();
			
			Query queryTotal = em.createQuery(hql);
			
			if(params != null){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					queryTotal.setParameter(entry.getKey(), entry.getValue());
				}
			}
			
			List<Object> totlaList = queryTotal.getResultList(); 
			totalCount = (totlaList == null) ? 0 : totlaList.size();
			if(totalCount % pageSize == 0){
				totalPage = totalCount/pageSize;
			}else{
				totalPage = totalCount/pageSize + 1;
			}
			
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			page.setList(list);
			page.setTotalCount(totalCount);
			page.setTotalPage(totalPage);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}			
		return page;
	}

        /**
	 * 根据SQL语句查询分页
	 * @param sql
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel queryForPageBySql(String sql,Integer currentPage,Integer pageSize){
		PageModel page = new PageModel();
		Integer totalCount = 0;
		Integer totalPage = 0;
		
		//EntityManager em = this.emf.createEntityManager();
		Query qry = em.createNativeQuery(sql);
		Query qry2 = em.createNativeQuery(sql);
		SQLQuery s = qry.unwrap(SQLQuery.class);
		if (currentPage != null && pageSize != null) {//判断是否有分页
			// 起始对象位置
			qry.setFirstResult(pageSize * (currentPage - 1));
			// 查询对象个数
			qry.setMaxResults(pageSize);
		}
		s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<Map> resultList=new ArrayList<Map>();
		List list = qry2.getResultList();
		totalCount = (list == null ? 0 : list.size());
		if(totalCount % pageSize == 0){
			totalPage = totalCount/pageSize;
		}else{
			totalPage = totalCount/pageSize + 1;
		}
		try {
			resultList=s.list();
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			page.setList(resultList);
			page.setTotalCount(totalCount);
			page.setTotalPage(totalPage);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}		
		return page;
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageModel queryForPageBySql(String sql,Map<String,Object> params,Integer currentPage,Integer pageSize){
		PageModel page = new PageModel();
		Integer totalCount = 0;
		Integer totalPage = 0;
		
		Query qry = em.createNativeQuery(sql);
		Query qry2 = em.createNativeQuery(sql);
		
		for(Map.Entry<String,Object> entry: params.entrySet()){
			qry.setParameter(entry.getKey(), entry.getValue());
			qry2.setParameter(entry.getKey(), entry.getValue());
		}
		
		SQLQuery s = qry.unwrap(SQLQuery.class);
		if (currentPage != null && pageSize != null) {//判断是否有分页
			// 起始对象位置
			qry.setFirstResult(pageSize * (currentPage - 1));
			// 查询对象个数
			qry.setMaxResults(pageSize);
		}
		s.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		List<Map> resultList=new ArrayList<Map>();
		List list = qry2.getResultList();
		totalCount = (list == null ? 0 : list.size());
		if(totalCount % pageSize == 0){
			totalPage = totalCount/pageSize;
		}else{
			totalPage = totalCount/pageSize + 1;
		}
		try {
			resultList=s.list();
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			page.setList(resultList);
			page.setTotalCount(totalCount);
			page.setTotalPage(totalPage);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}		
		return page;
	}
	
	/**
	 * 查询总数
	 * @param hql
	 * @param params
	 * @return
	 */
	public Long queryCount(String hql, Map<String, Object> params){
		//EntityManager em = this.emf.createEntityManager();
		Query query = em.createQuery(hql);
		Long count = null;
		try{
			if(params != null && !params.isEmpty()){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
			count = (Long) query.getSingleResult();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			em.close();
		}
		
		return count;
	}
	
	/**
	 * 查询总数
	 * @param hql
	 * @param params
	 * @return
	 */
	public Integer queryCountBySql(String sql, Map<String, Object> params){
		Integer count = null;
		try {
			Query query = em.createNativeQuery(sql);
			if(params != null && !params.isEmpty()){
				for(Map.Entry<String,Object> entry: params.entrySet()){
					query.setParameter(entry.getKey(), entry.getValue());
				}
			}
			
			Object obj = query.getSingleResult();
			if(obj instanceof BigInteger){
				count = ((BigInteger) obj).intValue();
			}else{
				count = (Integer) obj;
			}
			 
		} finally {
			if(em != null){
				em.close();
			}
		}
		return count;
	}
	
	/**
	 * select count(*) from table 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeSql(String sql, List<Object> params){
		try {
			Query query = em.createNativeQuery(sql);
			if(params != null && !params.isEmpty()){
				for(int i = 0, size = params.size(); i < size; i++){
					query.setParameter(i+1, params.get(i));
				}
			}
			return query.executeUpdate();
		} finally {
			if(em != null){
				em.close();
			}
		}
	}
	

}