package com.bcxin.survey.dao;

import com.bcxin.survey.utils.CollectionUtil;
import com.bcxin.survey.utils.SpringContextUtil;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * 
 * <b>查询帮助类，重点关注sql查询 </b>
 * 
 * @author LuoPeng
 * @date 2016年12月14日 下午9:14:00
 * @注意事项 </b>
 * <b>
 */
public class QueryHelper {

	private static SessionFactory SESSION_FACTORY;

	public static SessionFactory getSessionFactory() {
		if (SESSION_FACTORY == null) {
			SESSION_FACTORY = (SessionFactory) SpringContextUtil.getBean("sessionFactory");
		}
		return SESSION_FACTORY;
	}

	public static List<? extends Object> findList(String hql) {
		try {
			return QueryHelper.findList(hql, null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<? extends Object> findList(String hql,int start,int end) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(hql);
			return query.setFirstResult(start).setMaxResults(end).list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public static List<? extends Object> findList(String hql, Map<String, Object> paramMap) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(hql);
			if (paramMap != null && paramMap.size() != 0) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public static List<? extends Object> findList(String hql,
			Map<String, Object> paramMap, Integer pageIndex, Integer pageSize) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(
					hql);
			query.setFirstResult(pageIndex);
			query.setMaxResults(pageSize);
			if (paramMap != null && paramMap.size() != 0) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	@SuppressWarnings("unchecked")
	public static List<? extends Object> findList1(String sql,
			Map<String, Object> paramMap, Integer pageIndex, Integer pageSize) {
		try {
			Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			query.setFirstResult(pageIndex);
			query.setMaxResults(pageSize);
			if (paramMap != null && paramMap.size() != 0) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static Object uniqueResult(String hql, Map<String, Object> paramMap) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(hql);
			if (paramMap != null && paramMap.size() != 0) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			return ((Number)query.iterate().next()).intValue();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	
	@SuppressWarnings("unchecked")
	public static List<Object> findBySql(String sql, Map<String, Object> paramMap) {
		Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		if (paramMap != null && paramMap.size() != 0) {
			Iterator<String> iter = paramMap.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next();
				Object param = paramMap.get(key);
				query.setParameter(key, param);
			}
		}
		
		return query.list();
	}
	/**
	 * 
	 * @param sql sql语句
	 * @param paramMap 参数
	 * @param numberOfPage 每页条数
	 * @param pageNum 第几页
	 * @return Object集合
	 */
	@SuppressWarnings("unchecked")
	public static List<Object> findBySql(String sql, Map<String, Object> paramMap, int numberOfPage, int pageNum) {
		Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		if (paramMap != null && paramMap.size() != 0) {
			Iterator<String> iter = paramMap.keySet().iterator();
			while (iter.hasNext()) {
				String key = iter.next();
				Object param = paramMap.get(key);
				query.setParameter(key, param);
			}
		}
		
		query.setFirstResult((pageNum-1)*numberOfPage);
		query.setMaxResults(numberOfPage);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public static List<Object> findBySql(String sql, int start, int limit) {
		Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql)
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public static Map<String, Object> findBySql(String sql, String countSql, int start, int limit) {
		Query query = getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setFirstResult(start);
		query.setMaxResults(limit);
		Query countQuery = getSessionFactory().getCurrentSession().createSQLQuery(countSql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Object totalProperty = countQuery.uniqueResult();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("totalProperty", Long.valueOf(((Map<String, Object>)totalProperty).get("totalProperty").toString()));
		map.put("root", query.list());
		return map;
	}

	@SuppressWarnings("unchecked")
	public static List<? extends Object> findSingle(String hql) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(hql);
			query.setMaxResults(1);
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Object executeSql(String sql, Map<String, Object> paramMap) {
		try {
			SQLQuery query = getSessionFactory().getCurrentSession().createSQLQuery(sql);
			if (paramMap != null && paramMap.size() != 0) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			Object rs = query.executeUpdate();
			return rs;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return paramMap;
	}

	public static void executeHql(String hql) {
		try {
			getSessionFactory().getCurrentSession().createQuery(hql)
					.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void executeHql(String hql, Map<String, Object> paramMap) {
		try {
			Query query = getSessionFactory().getCurrentSession().createQuery(hql);
			if (!CollectionUtil.isEmpty(paramMap)) {
				Iterator<String> iter = paramMap.keySet().iterator();
				while (iter.hasNext()) {
					String key = iter.next();
					Object param = paramMap.get(key);
					query.setParameter(key, param);
				}
			}
			query.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}