Hibernate4读取Clob数据

package cn.framework.dao.impl;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.internal.CriteriaImpl;
import org.hibernate.persister.entity.AbstractEntityPersister;
import org.hibernate.persister.entity.SingleTableEntityPersister;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import cn.framework.dao.BaseHibernateDao;
import cn.framework.dao.model.OrderBy;
import cn.framework.dao.util.DaoUtil;
import cn.framework.util.Page;
import cn.framework.util.PropertyFilter;
import cn.framework.util.ReflectUtil;
import cn.framework.util.ReflectionUtils;
import cn.framework.util.SimpleUtils;
import cn.framework.util.StringUtil;
import cn.framework.util.PropertyFilter.MatchType;

@Repository
@SuppressWarnings("unchecked")
public abstract class BaseHibernateDaoImpl<T> implements BaseHibernateDao{

private LinkedList<OrderBy> orderList = new LinkedList<OrderBy>();

private final Logger logger=Logger.getLogger(getClass());

protected SessionFactory sessionFactory;

protected Class<T> entityClass;

String orderPropertyName = "";

public void setSessionFactory(SessionFactory sessionFactory) {
this.sessionFactory = sessionFactory;
}

protected Session getSession() {
return sessionFactory.getCurrentSession();
}

/**
* 用于Dao层子类使用的构造函数.
* 通过子类的泛型定义取得对象类型Class.
* eg.
* public class UserDao extends SimpleHibernateDao<User, Long>
*/
public BaseHibernateDaoImpl() {
this.entityClass = ReflectionUtils.getSuperClassGenricType(getClass());
}

/**
* 用于用于省略Dao层, 在Service层直接使用通用SimpleHibernateDao的构造函数.
* 在构造函数中定义对象类型Class.
* eg.
* SimpleHibernateDao<User, Long> userDao = new SimpleHibernateDao<User, Long>(sessionFactory, User.class);
*/
public BaseHibernateDaoImpl(final SessionFactory sessionFactory, final Class<T> entityClass) {
this.sessionFactory = sessionFactory;
this.entityClass = entityClass;
}

private boolean desc = false;

@Autowired
DaoUtil daoUtil;

public boolean delete(Object domain) {
  try {
   getSession().delete(domain);
   getSession().flush();
   return true;
  } catch (Exception e) {
   logger.error(e);
   return false;
  }
}

/**
* 根据Criterion条件创建Criteria.
*
* 本类封装的find()函数全部默认返回对象类型为T,当不为T时使用本函数.
*
* @param criterions 数量可变的Criterion.
*/
public Criteria createCriteria(final Criterion... criterions) {
  Criteria criteria = getSession().createCriteria(entityClass);
  for (Criterion c : criterions) {
   criteria.add(c);
  }
  return criteria;
}

public boolean deleteByKey(Serializable id) {
  try {
   getSession().delete(findById(id));
   getSession().flush();
   return true;
  } catch (Exception e) {
   logger.error(e);
   return false;
  }
}

public boolean deleteListByKey(String tableName,String keyColumn,String keyValues){
  try {
   StringBuffer sql=new StringBuffer("delete from ");
   if(StringUtils.isBlank(tableName)){
    return false;
   }

   if(StringUtils.isBlank(keyColumn) && StringUtils.isBlank(keyValues)){

   }
   sql.append(tableName).append(" where 1=1").append(" and ").append(keyColumn).append(" in (").append(keyValues).append(")");
   System.out.println("deleteListByKey:"+sql.toString());
   return executeSql(sql.toString());
  } catch (Exception e) {
   logger.error(e);
   return false;
  }
}

public boolean deleteListByKey(Class obj,String keyValues){
  String keyColumn=getPrimaryKeyColumn(obj);
  String tableName=getTableName();
  return deleteListByKey(tableName,keyColumn,keyValues);
}

public boolean deleteListByObj(List domainList) {
  try {
   deleteAll(domainList);
   return true;
  } catch (Exception e) {
   logger.error(e);
   return false;
  }
}

public T save(Object domain)throws Exception {
   getSession().save(domain);
   getSession().flush();
   return (T) domain;
}

public T saveOrUpdate(Object domain)throws Exception {
   getSession().saveOrUpdate(domain);
   getSession().flush();
   return (T) domain;
}

public T update(Object domain)throws Exception {
   getSession().update(domain);
   getSession().flush();
   return (T) domain;
}

public T merge(Object domain) throws Exception{
   getSession().merge(domain);
   getSession().flush();
   return (T) domain;
}

public List<T> findListByObj(Object value) {
return findListByObj(value);
}

public List<T> findListByObjR(Object object) {
return findListByObjR(object, null, null);
}

public List<T> findListByObjR(Object object, Integer firstResult,
   Integer size) {
  final Object f_object = object;
  final Integer f_firstResult = firstResult;
  final Integer f_size = size;

Criteria criteria = getSession().createCriteria(f_object.getClass());
  Example exampleAtt = Example.create(f_object);
  criteria.add(exampleAtt);
  Map<String, Class> map = ReflectUtil
    .getUserClassTypePropertyMap(f_object.getClass());
  if (map != null && !map.isEmpty()) {
   for (String key : map.keySet()) {
    String getterMethodName = daoUtil.propertyToGetterMethod(key);
    Object beanValue = ReflectUtil.invokeMethod(f_object,
      getterMethodName);
    if (beanValue != null) {
     Class cla = map.get(key);
     if (cla != null) {
      String keyProperty = getPrimaryKeyParam(cla);
      if (StringUtils.isNotBlank(keyProperty)) {
       // criteria.createCriteria(key);
       String _getterMethodName = daoUtil
         .propertyToGetterMethod(keyProperty);
       Object _beanValue = ReflectUtil.invokeMethod(
         beanValue, _getterMethodName);
       if (_beanValue != null) {
        criteria.add(Restrictions.eq(key + "."
          + keyProperty, _beanValue));
       } else {
        criteria.add(Example.create(beanValue));
       }
      }
     }
    }
   }
  }
  if (f_firstResult != null && f_size != null) {
   criteria.setFirstResult(f_firstResult);
   criteria.setMaxResults(f_size);
  }
  List<T> list = criteria.list();
  return list;
}

public List<T> findListByObj(Object value, int firstResult, int maxResults) {
return findListByObj(value, firstResult, maxResults);
}

public List<T> findAll() {
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  date = addOrderBy(date);
  return findByCriteria(date);
}

/**
* 获取全部对象,支持排序.
*/
public List<T> findAll(String orderBy, boolean isAsc) {
  Criteria c = createCriteria();
  if (isAsc) {
   c.addOrder(Order.asc(orderBy));
  } else {
   c.addOrder(Order.desc(orderBy));
  }
  return c.list();
}

public List<T> findAll(String[] orderBy, boolean[] isAsc) {
  Criteria c = createCriteria();
  for (int i = 0; i < orderBy.length; i ++)
   if (isAsc[i])
    c.addOrder(Order.asc(orderBy[i]));
   else
    c.addOrder(Order.desc(orderBy[i]));

return c.list();
}

public List<T> findAll(int firstResult, int size) {
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  date = addOrderBy(date);
  return findByCriteria(date, firstResult, size);
}

public T findById(Serializable id) {
  T obj = (T) getSession().get(typeClass(), id);
  evict(obj);
  return obj;
}

public List<T> findListByParam(String propertyName, Object value) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  List<T> list = findListByParam(propertyNames, values);
  evict(list);
  return list;
}

public List<T> findListByParam(String propertyName, Object value,
   Integer firstResult, Integer size) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  List<T> list = findListByParam(propertyNames, values, firstResult, size);
  evict(list);
  return list;
}

public T findByParam(String propertyName, Object value) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  T object = findByParam(propertyNames, values);
  evict(object);
  return object;
}

public T findByParam(String[] propertyNames, Object[] values) {
  List<T> resultList = findListByParam(propertyNames, values, null, null);
  if (resultList != null && resultList.size() > 0) {
   T object = resultList.get(0);
   evict(object);
  }
  return null;
}

public List<T> findListByParam(String[] propertyNames, Object[] values) {
  List<T> list = findListByParam(propertyNames, values, null, null);
//  evict(list);
  return list;
}

public List<T> findListByParam(String[] propertyNames, Object[] values,
   Integer firstResult, Integer size) {
  List<T> list = null;
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  for (int i = 0; i < propertyNames.length; i++) {
   String propertyName = propertyNames[i];
   Object value = values[i];
   date.add(Restrictions.eq(propertyName, value));
  }
  date = addOrderBy(date);
  if (firstResult != null && size != null) {
   list = findByCriteria(date, firstResult, size);
  } else {
   list = findByCriteria(date);
  }
//  evict(list);
  return list;
}

public List<T> findByParamOr(String[] propertyNames, Object[] values,
   Integer firstResult, Integer size) {
  List<T> list = null;
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  for (int i = 0; i < propertyNames.length; i++) {
   String propertyName = propertyNames[i];
   Object value = values[i];
   String neextPropertyName = propertyNames[i + 1];
   Object nextValue = values[i + 1];
   date.add(Restrictions.or(Restrictions.eq(propertyName, value),
     Restrictions.eq(neextPropertyName, nextValue)));
   if (i + 1 == propertyNames.length - 1) {
    break;
   }
  }
  date = addOrderBy(date);
  if (firstResult != null && size != null) {
   list = findByCriteria(date, firstResult, size);
  } else {
   list = findByCriteria(date);
  }
  evict(list);
  return list;
}

public List<T> findListByParamForOr(String[] propertyNames, Object[] values,
   Integer arg2, Integer arg3){
  List<T> list = findListByParamForOr(propertyNames, values, null, null);
  evict(list);
  return list;
}

public List<T> findListByParamForOrLike(String[] propertyNames, Object[] values) {
  List<T> list = findListByParamForOrLike(propertyNames, values, null, null);
  evict(list);
  return list;
}

public List<T> findListByParamForOrLike(String[] propertyNames,
   Object[] values, Integer firstResult, Integer size) {
  List<T> list = null;
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  if (propertyNames.length < 2) {
   date.add(Restrictions.like(propertyNames[0], values[0].toString(),
     MatchMode.ANYWHERE));
  } else {
   for (int i = 0; i < propertyNames.length; i++) {
    String propertyName = propertyNames[i];
    Object value = values[i];
    String neextPropertyName = propertyNames[i + 1];
    Object nextValue = values[i + 1];
    date.add(Restrictions.or(Restrictions.like(propertyName,
      value.toString(), MatchMode.ANYWHERE), Restrictions.eq(
      neextPropertyName, nextValue)));
    if (i + 1 == propertyNames.length - 1) {
     break;
    }
   }
  }
  date = addOrderBy(date);
  if (firstResult != null && size != null) {
   list = findByCriteria(date, firstResult, size);
  } else {
   list = findByCriteria(date);
  }
  evict(list);
  return list;
}

public List<T> findListByParamForIn(String propertyName, Object[] value) {
  List<T> list = null;
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  date.add(Restrictions.in(propertyName, value));
  list = findByCriteria(date);
  evict(list);
  return list;
}

public Integer getCount() {
  try {
   String queryString = "select count(*) from " + getTableName();
   List result = find(queryString);
   return ((Long) result.get(0)).intValue();
  } catch (Exception e) {
   return 0;
  }

}

public Integer getCount(String propertyName, Object value) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  return getCount(propertyNames, values);
}

public Integer getCount(String[] propertyNames, Object[] values) {
  String queryString = "select count(*) from " + getTableName()
    + " as model where ";
  for (String propertyName : propertyNames) {
   queryString += " model." + propertyName + "=? and";
  }
  queryString = StringUtils.removeEnd(queryString, "and");
  List result = find(queryString, values);
  if (result != null && !result.isEmpty()) {
   Object obj = result.get(0);
   if (obj != null) {
    return Integer.parseInt(obj.toString());
   }
  }
  return null;
}

public Integer getCountByObjR(Object object) {
  DetachedCriteria criteria = DetachedCriteria
    .forClass(object.getClass());
  Example exampleAtt = Example.create(object);
  criteria.add(exampleAtt);
  String countProperty = getPrimaryKeyParam(object.getClass());
  criteria.setProjection(Projections.count(countProperty));
  Map<String, Class> map = ReflectUtil.getUserClassTypePropertyMap(object.getClass());
  if (map != null && !map.isEmpty()) {
   for (String key : map.keySet()) {
    String getterMethodName = daoUtil.propertyToGetterMethod(key);
    Object beanValue = ReflectUtil.invokeMethod(object,
      getterMethodName);
    if (beanValue != null) {
     Class cla = map.get(key);
     if (cla != null) {
      String keyProperty = getPrimaryKeyParam(cla);
      if (StringUtils.isNotBlank(keyProperty)) {
       // criteria.createCriteria(key);
       String _getterMethodName = daoUtil
         .propertyToGetterMethod(keyProperty);
       Object _beanValue = ReflectUtil.invokeMethod(
         beanValue, _getterMethodName);
       if (_beanValue != null) {
        criteria.add(Restrictions.eq(key + "."
          + keyProperty, _beanValue));
       } else {
        criteria.add(Example.create(beanValue));
       }
      }
     }
    }
   }
  }
  List list = findByCriteria(criteria);
  if (list != null && !list.isEmpty()) {
   return Integer.valueOf(list.get(0).toString());
  }
  return null;
}

public List<T> findListByParamForLike(String propertyName, Object value) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  return findListByParamForLike(propertyNames, values);
}

public List<T> findListByParamForLike(String propertyName, Object value,
   Integer firstResult, Integer size) {
  String[] propertyNames = { propertyName };
  Object[] values = { value };
  return findListByParamForLike(propertyNames, values, firstResult, size);
}

public List<T> findListByParamForLike(String[] propertyNames, Object[] values) {
return findListByParamForLike(propertyNames, values, null, null);
}

public List<T> findListByParamForLike(String[] propertyNames, Object[] values,
   Integer firstResult, Integer size) {
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  for (int i = 0; i < propertyNames.length; i++) {
   String propertyName = propertyNames[i];
   Object value = values[i];
   if (value instanceof String) {
    date.add(Restrictions.like(propertyName, String.valueOf(value),
      MatchMode.ANYWHERE));
   } else {
    date.add(Restrictions.like(propertyName, value));
   }
  }
  date = addOrderBy(date);
  if (firstResult != null && size != null) {
   return findByCriteria(date, firstResult, size);
  } else {
   return findByCriteria(date);
  }
}

public String getDbTableName() {
  SessionFactory factory = getSession().getSessionFactory();
  Class cls = typeClass();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) factory
    .getClassMetadata(cls);
  return classMetadata.getTableName();
}

public List<String> getDbCelName() {
  List<String> resultList = new ArrayList<String>();
  SessionFactory factory = getSession().getSessionFactory();
  Class cls = typeClass();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) factory
    .getClassMetadata(cls);
  // 添加主键
  resultList.addAll(Arrays.asList(classMetadata
    .getIdentifierColumnNames()));
  String[] propertyNames = classMetadata.getPropertyNames();
  for (String propertyName : propertyNames) {
   // 判断是否一对多的对像,移除
   boolean isCollection = classMetadata.getClassMetadata()
     .getPropertyType(propertyName).isCollectionType();
   if (!isCollection) {
    String[] propertyColumnNames = classMetadata
      .getPropertyColumnNames(propertyName);
    for (String columnName : propertyColumnNames) {
     resultList.add(columnName);
    }
   }
  }
  return resultList;
}

// 取得泛型类型
@SuppressWarnings("unchecked")
protected Class<T> typeClass() {
return (Class<T>) ((ParameterizedType) getClass()
.getGenericSuperclass()).getActualTypeArguments()[0];
}

// 取得泛型tableName
private String getTableName() {
return typeClass().getSimpleName();
}

public DetachedCriteria addOrderBy(DetachedCriteria date) {
  if (orderList.isEmpty()) {
   initOrderList(date);
  }
  for (OrderBy orderBy : orderList) {
   if (orderBy.isDesc()) {
    date.addOrder(Order.desc(orderBy.getPropertyName()));
   } else {
    date.addOrder(Order.asc(orderBy.getPropertyName()));
   }
  }
  clearOrderList();
  return date;
}

public void clearOrderList() {
orderList = new LinkedList<OrderBy>();
}

public void initOrderList(DetachedCriteria date) {
  try {
   String orderPropertyName = daoUtil
     .getPrimaryKeyParam(typeClass());
   if (desc) {
    date.addOrder(Order.desc(orderPropertyName));
   } else {
    date.addOrder(Order.asc(orderPropertyName));
   }
  } catch (Exception e) {

}
}

public void addOrderBy(OrderBy orderBy) {
this.orderList.addFirst(orderBy);
}

public boolean isDesc() {
return desc;
}

public void orderDesc(boolean desc) {
this.desc = desc;
}

// add method for com.trendcom.dao.impl.BaseHibernateDaoImpl
public List findListBySql(String sql) {
  final String _sql = sql;
  Query query = getSession().createSQLQuery(_sql);
  return query.list();
}

public Long getLongBySql(String sql){
  List sqlList=this.getListBySql(sql);
  if(sqlList!=null && sqlList.size()>0){
   Long obj=StringUtil.toLong(sqlList.get(0));
   return obj;
  }else{
   return null;
  }
}

public String getStringBySql(String sql){
  List sqlList=this.getListBySql(sql);
  if(sqlList != null && !sqlList.isEmpty()){
   String obj=StringUtil.toString(sqlList.get(0));
   if(obj != null){
    return obj;
   }
  }
  return null;
}

public Integer getIntegerBySql(String sql){
  List sqlList=this.getListBySql(sql);
  if(sqlList != null && !sqlList.isEmpty()){
   Integer obj=StringUtil.toInteger(sqlList.get(0));
   if(obj != null){
    return obj;
   }
  }
  return null;
}

public List<T> findListBySql(String sql, Integer page, Integer size) {
  List<T> result = new ArrayList<T>();
  Transaction tx = null;
  try {
   Session session = this.getSession();
   tx = session.beginTransaction();
   Query query = session.createQuery(sql);
   query.setFirstResult(page);
   query.setMaxResults(size);
   logger.debug("sql:" + sql);
   result = query.list();
   tx.commit();
  } catch (Exception e) {
   e.printStackTrace();
  }
  return result;
}

public boolean saveSql(String sql) {
  final String _sql = sql;
  try {
   executeUpdateSql(sql);
   return true;
  } catch (Exception e) {
   logger.error(e);
   return false;
  }
}

public List getListBySql(String sql) {
return getListBySql(sql, null, null);
}

public List getListBySql(String sql, Integer firstResult, Integer size) {
  final String _sql = sql;
  final Integer FfirstResult = firstResult;
  final Integer Fsize = size;
  SQLQuery query = getSession().createSQLQuery(_sql);
//  query.setResultTransformer(Transformers.TO_LIST);
  if (FfirstResult != null && Fsize != null) {
   query.setFirstResult(FfirstResult);
   query.setMaxResults(Fsize);
  }
  List list = query.list();

return list;
}

public List<Map<String, String>> getListMapBySql(String sql) {
return getListMapBySql(sql, null, null);
}

public List<Map<String, String>> getListMapBySql(String sql,
   Integer firstResult, Integer size) {
  final String _sql = sql;
  final Integer FfirstResult = firstResult;
  final Integer Fsize = size;

List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
  Map<String, String> map0 = null;
  SQLQuery query = getSession().createSQLQuery(_sql);
  query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
  if (FfirstResult != null && Fsize != null) {
   query.setFirstResult(FfirstResult);
   query.setMaxResults(Fsize);
  }
  List<Map<Object, Object>> list = query.list();
  if (list != null && !list.isEmpty()) {
   int length = list.size();
   for (int i = 0; i < length; i++) {
    Map<Object, Object> map = list.get(i);
    map0 = new LinkedHashMap<String, String>();
//    for (Map.Entry<Object, Object> entry : map.entrySet()) {
//     Object key = entry.getKey();
//     Object value = entry.getValue();
//     if (value == null) {
//      value = "";
//     }
//     map0.put(String.valueOf(key), String.valueOf(value));
//    }
    for(Map.Entry<Object,Object> entry:map.entrySet()){
     Object key=entry.getKey();
     Object value=entry.getValue();
     if(value == null){
      value="";
     }else if(value instanceof CLOB){
      value=ClobToString((CLOB)value);
     }
     map0.put(String.valueOf(key),String.valueOf(value));
    }
    mapList.add(map0);
   }
  }
  return mapList;
}
public String getClobToStringBySql(String sql,String columKey){
  Connection conn=null;
  ResultSet rs = null;
  Statement stmt =null;
  Clob data=null;
  try {
   conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   if (rs.next()) {
    if (StringUtils.isNotBlank(columKey)) {
     data = rs.getClob(columKey);
    } else {
     data = rs.getClob(rs.getString(1));
    }
   }
  } catch (Exception e) {
   throw new RuntimeException(e.getLocalizedMessage());
  }finally{
   closeConnection(conn, rs, stmt);
  }
  return ClobToString(data);
}
public HashMap getMapClobBySqlForPage(String sql,String dateFormat,Page page){
  Integer limit=page.getPageSize();
  Integer start = page.getPageIndex() * limit, end = start+ page.getPageSize();
  Integer intRowCount=0;
  List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
  Connection conn=null;
  ResultSet rs = null;
  Statement stmt =null;
  try {
   SimpleDateFormat df = null;
   if (dateFormat != null) {
    df = new SimpleDateFormat(dateFormat);
   }else {
    df = new SimpleDateFormat("yyyy-MM-dd");
   }
   //创建一个可以滚动的只读的SQL语句对象
   conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
   rs = stmt.executeQuery(sql);
   ResultSetMetaData rsmd = rs.getMetaData();
   rs.last();
   //记录总数
   intRowCount = rs.getRow();
   //设置一页显示的记录数
   //limit=limit;
   //记算总页数
   int intPageCount=(intRowCount+limit-1) / limit;
   //取得待显示页码
   int intPage=1;
   if (start>0) {
    intPage=(start+limit)/limit;
   }
   //调整待显示的页码
   if(intPage>intPageCount) {intPage = intPageCount;}

   if(intPageCount>0){
    //将记录指针定位到待显示页的第一条记录上
    rs.absolute((intPage-1)* limit + 1);
    //显示数据
    Integer i = 0;
    int colNum = rsmd.getColumnCount();
    while(i<limit && !rs.isAfterLast()){
     Map<String, Object> map = new HashMap<String, Object>();
     for (int cols = 1; cols <= colNum; cols++) {
      String colName = rsmd.getColumnLabel(cols).toUpperCase();
      int colType = rsmd.getColumnType(cols);
      if (colType == Types.DATE) {//日期
       java.sql.Timestamp timestamp = rs.getTimestamp(cols);
       if (timestamp == null) {
        map.put(colName, "");
       } else {
        map.put(colName, df.format(timestamp));
       }
      } if(colType==Types.CLOB){//CLOB
       Clob colValue = rs.getClob(cols);
       map.put(colName, ClobToString(colValue));
      }else {
       String colValue = rs.getString(cols);
       map.put(colName, colValue == null ? "" : colValue);
      }
     }
     list.add(map);
     rs.next();
     i++;
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new RuntimeException(e.getLocalizedMessage());
  }finally{
   closeConnection(conn, rs, stmt);
  }

  HashMap result = new HashMap();
  result.put("total", intRowCount);
  result.put("data", list);
        return result;
}
public List getListClobMapBySql(String sql,String dateFormat){
  List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
  Connection conn=null;
  ResultSet rs = null;
  Statement stmt =null;
  try {
   SimpleDateFormat df = null;
   if (dateFormat != null) {
    df = new SimpleDateFormat(dateFormat);
   }else {
    df = new SimpleDateFormat("yyyy-MM-dd");
   }
   //创建一个可以滚动的只读的SQL语句对象
   conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   ResultSetMetaData rsmd = rs.getMetaData();
   int colNum = rsmd.getColumnCount();
   //显示数据
   while(rs.next()){
    Map<String, Object> map = new HashMap<String, Object>();
    for (int cols = 1; cols <= colNum; cols++) {
     String colName = rsmd.getColumnLabel(cols).toUpperCase();
     int colType = rsmd.getColumnType(cols);
     if (colType == Types.DATE) {//日期
      java.sql.Timestamp timestamp = rs.getTimestamp(cols);
      if (timestamp == null) {
       map.put(colName, "");
      } else {
       map.put(colName, df.format(timestamp));
      }
     } if(colType==Types.CLOB){//CLOB
      Clob colValue = rs.getClob(cols);
      map.put(colName, ClobToString(colValue));
     }else {
      String colValue = rs.getString(cols);
      map.put(colName, colValue == null ? "" : colValue);
     }
    }
    list.add(map);
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new RuntimeException(e.getLocalizedMessage());
  }finally{
   closeConnection(conn, rs, stmt);
  }
  return list;
}
public List getListClobBySql(String sql,String columKey){
  List<String> list = new ArrayList<String>();
  Connection conn=null;
  ResultSet rs = null;
  Statement stmt =null;
  try {
   //创建一个可以滚动的只读的SQL语句对象
   conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   //显示数据
   while(rs.next()){
    if (StringUtils.isNotBlank(columKey)) {
     list.add(ClobToString(rs.getClob(columKey)));
    }else {
     list.add(ClobToString(rs.getClob(1)));
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new RuntimeException(e.getLocalizedMessage());
  }finally{
   closeConnection(conn, rs, stmt);
  }
  return list;
}
private void closeConnection(Connection conn,ResultSet rs,Statement stmt) {
  try {
   if (rs!=null) {
    rs.close();
   }
   if (stmt!=null) {
    stmt.close();
   }
   if (conn!=null) {
    conn.close();
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new RuntimeException(e.getLocalizedMessage());
  }
}
public List getListClobPageMapBySql(String sql,String dateFormat,Integer start,Integer limit){
  List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
  Connection conn=null;
  ResultSet rs = null;
  Statement stmt =null;
  try {
   SimpleDateFormat df = null;
   if (dateFormat != null) {
    df = new SimpleDateFormat(dateFormat);
   }else {
    df = new SimpleDateFormat("yyyy-MM-dd");
   }
   //创建一个可以滚动的只读的SQL语句对象
   conn=SessionFactoryUtils.getDataSource(sessionFactory).getConnection();
   stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
   rs = stmt.executeQuery(sql);
   ResultSetMetaData rsmd = rs.getMetaData();
   rs.last();
   //记录总数
   Integer intRowCount=0;
   intRowCount = rs.getRow();
   //设置一页显示的记录数
   //limit=limit;
   //记算总页数
   int intPageCount = (intRowCount+limit-1) / limit;
   //取得待显示页码
   int intPage=1;
   if (start>0) {
    intPage=(start+limit)/limit;
   }
   //调整待显示的页码
   if(intPage>intPageCount) {intPage = intPageCount;}
   if(intPageCount>0){
    //将记录指针定位到待显示页的第一条记录上
    rs.absolute((intPage-1)* limit + 1);
    //显示数据
    Integer i = 0;
    while(i<limit && !rs.isAfterLast()){
     Map<String, Object> map = new HashMap<String, Object>();
     int colNum = rsmd.getColumnCount();
     for (int cols = 1; cols <= colNum; cols++) {
      String colName = rsmd.getColumnLabel(cols).toUpperCase();
      int colType = rsmd.getColumnType(cols);
      if (colType == Types.DATE) {//日期
       java.sql.Timestamp timestamp = rs.getTimestamp(cols);
       if (timestamp == null) {
        map.put(colName, "");
       } else {
        map.put(colName, df.format(timestamp));
       }
      } if(colType==Types.CLOB){//CLOB
       Clob colValue = rs.getClob(cols);
       map.put(colName, ClobToString(colValue));
      }else {
       String colValue = rs.getString(cols);
       map.put(colName, colValue == null ? "" : colValue);
      }
     }
     list.add(map);
     rs.next();
     i++;
    }
   }
  } catch (Exception e) {
   e.printStackTrace();
   throw new RuntimeException(e.getLocalizedMessage());
  }finally{
   closeConnection(conn, rs, stmt);
  }
  return list;
}
public static String ClobToString(Clob clob) {
  String reString = "";
  if (clob!=null) {
   try {
    Reader is = clob.getCharacterStream();// 获得流
    BufferedReader br = new BufferedReader(is);
    String s = br.readLine();
    StringBuffer sb = new StringBuffer();
    while(s != null) {// 履行轮回将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
     sb.append(s);
     s = br.readLine();
    }
    reString = sb.toString();
   } catch (SQLException e) {
    e.printStackTrace();
   } catch (IOException e) {
    e.printStackTrace();
   }
  }
  return reString;
}
public static String ClobToString(CLOB clob) {
  String reString = "";
  try {
   Reader is = clob.getCharacterStream();// 获得流
   BufferedReader br = new BufferedReader(is);
   String s = br.readLine();
   StringBuffer sb = new StringBuffer();
   while(s != null) {// 履行轮回将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
    sb.append(s);
    s = br.readLine();
   }
   reString = sb.toString();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return reString;
}

public List<Map<Object, Object>> getListMapObjBySql(String sql,
   Integer firstResult, Integer size) {
  final String _sql = sql;
  final Integer FfirstResult = firstResult;
  final Integer Fsize = size;

List<Map<Object, Object>> mapList = new ArrayList<Map<Object, Object>>();
  Map<Object, Object> map0 = null;
  SQLQuery query = getSession().createSQLQuery(_sql);
  query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
  if (FfirstResult != null && Fsize != null) {
   query.setFirstResult(FfirstResult);
   query.setMaxResults(Fsize);
  }
  List<Map<Object, Object>> list = query.list();
  if (list != null && !list.isEmpty()) {
   int length = list.size();
   for (int i = 0; i < length; i++) {
    Map<Object, Object> map = list.get(i);
    map0 = new LinkedHashMap<Object, Object>();
    for (Map.Entry<Object, Object> entry : map.entrySet()) {
     Object key = entry.getKey();
     Object value = entry.getValue();
     if (value == null) {
      value = "";
     }
     map0.put(key, value);
    }
    mapList.add(map0);
   }
  }
  return mapList;
}

public List getSqlToBean(String sql, Class obj) {
return getSqlToBean(sql, obj, null, null);
}

public List getSqlToBean(String sql, Class obj, Integer firstResult,
   Integer size) {
  final String _sql = sql;
  final Integer FfirstResult = firstResult;
  final Integer Fsize = size;
  final Class Fobj = obj;
  List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
  Map<String, String> map0 = null;
  SQLQuery query = getSession().createSQLQuery(_sql);
  query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
  if (FfirstResult != null && Fsize != null) {
   query.setFirstResult(FfirstResult);
   query.setMaxResults(Fsize);
  }
  List<Map<Object, Object>> list = query.list();
  if (list != null && !list.isEmpty()) {
   int length = list.size();
   for (int i = 0; i < length; i++) {
    Map<Object, Object> map = list.get(i);
    map0 = new LinkedHashMap<String, String>();
    for (Map.Entry<Object, Object> entry : map.entrySet()) {
     Object key = entry.getKey();
     Object value = entry.getValue();
     if (value == null) {
      value = "";
     }
     map0.put(String.valueOf(key), String.valueOf(value));
    }
    mapList.add(map0);
   }
  }
  return mapList;
}

public Integer getSqlCountByTableName(String tableName) {
  Integer count = 0;
  String queryString = "select count(*) from " + tableName;
  System.out.println("queryString=" + queryString);
  Session session = getSession().getSessionFactory().getCurrentSession();
  Object obj = session.createSQLQuery(queryString).uniqueResult();
  if (obj != null && StringUtils.isNumeric(obj.toString())) {
   count = Integer.parseInt(obj.toString()) + 1;
  }
  return count;
}

public Integer getSqlMax(String tableName, String column) {
return getSqlMax(tableName, column, null, null);
}

public Integer getSqlMax(String tableName, String MaxColumn,
   String[] propertyNames, Object[] values) {
  Integer count = 0;
  String remove = "and";
  StringBuffer strBuffer = new StringBuffer("select max(" + MaxColumn
    + ") from " + tableName); // oracle
  if (propertyNames != null && propertyNames.length > 0) {
   if (propertyNames.length == values.length) {
    strBuffer.append(" where ");
    for (int i = 0; i < propertyNames.length; i++) {
     strBuffer.append(" " + propertyNames[i] + " = '"
       + values[i] + "'");
     strBuffer.append(" and");
    }
   }
  }
  String queryString = StringUtils
    .removeEnd(strBuffer.toString(), remove);
  count = getCountBySql(queryString);
  return count;
}

public boolean creatTable(String sql) {
  boolean flag = true;
  try {
   executeUpdateSql(sql);
  } catch (Exception e) {
   flag = false;
   e.printStackTrace();
  }
  return flag;
}

public boolean dropTable(String tableName) {
  String sql = " drop table " + tableName;
  int count = executeUpdateSql(sql);
  System.out.println("dropTable count:" + count);
  return true;
}

/**
* 表是否存在注意:如果是sql server则改为 String sql=select count(*) from sysobjects
* where name='"+table.toUpperCase()+"'";
*/

public Integer isExitsTable(String table) {
  // if (SystemManager.instance().)
  // String
  // sql="select count(*) from user_tables where table_name = '"+table.toUpperCase()+"'";
  // //CHEN:change later
  String sql = "select count(*) from sysobjects where name='"
    + table.toUpperCase() + "'";// SQL Server
  Integer count = getCountBySql(sql);
  return count;
}

public boolean isExistColumn(String tableName, String columnName) {
  boolean flag = false;
  // sql
  // String
  // sql="select count(*) from syscolumns where id=object_id('"+tableName+"') and name='"+columnName+"'";
  // oracle
  String sql = "select count(*) from cols where table_name=upper('"
    + tableName + "') and column_name='" + columnName.toUpperCase()
    + "'";
  Integer count = getCountBySql(sql);
  if (count != null && count.intValue() > 0) {
   flag = true;
  }
  return flag;
}

public boolean executeSql(String sql) {
  Integer count = executeUpdateSql(sql);
  System.out.println("count=" + count);
  if (count != null && count.intValue() > 0) {
   return true;
  }
  return false;
}

public Integer getCountBySql(String sql) {
  final String _sql = sql;
  int count = 0;
  String result = getSession().createSQLQuery(_sql).uniqueResult()
    .toString();
  if (StringUtils.isNumeric(result)) {
   count = Integer.parseInt(result);
  }
  return count;
}

private int executeUpdateSql(String sql) {
final String _sql = sql;
return (Integer) getSession().createSQLQuery(_sql).executeUpdate();
}

public List findListBySql(String sql, String alias, Class obj,
   Integer firstResult, Integer size) {
  final String _sql = sql;
  final String _alias = alias;
  final Class _obj = obj;
  final Integer _firstResult = firstResult;
  final Integer _size = size;
  Query query = getSession().createSQLQuery(_sql).addEntity(_alias, _obj);
  if (_firstResult != null && _size != null) {
   query.setFirstResult(_firstResult);
   query.setMaxResults(_size);
  }
  List list = query.list();
  if (list != null && !list.isEmpty()) {
   for (Object objs : list) {
    getSession().evict(objs);
   }
  }
  return list;
}

public List findListByHql(String hql, Integer firstResult, Integer size) {
  final String _hql = hql;
  final Integer _firstResult = firstResult;
  final Integer _size = size;
  Query query = getSession().createQuery(_hql);
  if (_firstResult != null && _size != null) {
   query.setFirstResult(_firstResult);
   query.setMaxResults(_size);
  }
  List list = query.list();
  if (list != null && !list.isEmpty()) {
   for (Object obj : list) {
    getSession().evict(obj);
   }
  }
  return list;
}

public List findListBySql(String sql, String alias, Class obj) {
  return findListBySql(sql, alias, obj, null, null);
}

public Object findBySql(String sql, String alias, Class obj) {
  List<Object> list=findListBySql(sql,alias,obj);
  if(list.size()>0){
   return list.get(0);
  }else{
   return null;
  }
}

public List<Map<String, String>> getListMapBySql(String sql,
String[] aliasPropertys) {
return getListMapBySql(sql, aliasPropertys, null, null);
}

public List<Map<String, String>> getListMapBySql(String sql,
   String[] aliasPropertys, Integer firstResult, Integer size) {
  final String _sql = sql;
  final String[] _aliasPropertys = aliasPropertys;
  final Integer _firstResult = firstResult;
  final Integer _size = size;
  if (aliasPropertys != null && aliasPropertys.length > 0) {
   SQLQuery query = getSession().createSQLQuery(_sql);
   for (String aliasColumn : _aliasPropertys) {
    query.addScalar(aliasColumn, new StringType());
   }
   if (_firstResult != null && _size != null) {
    query.setFirstResult(_firstResult);
    query.setMaxResults(_size);
   }
   query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
   List<Map<String, String>> list = query.list();
   return list;
  } else {
   return null;
  }
}

public List<Map<String, String>> getListMapByCallSql(String calSql,
   String[] propertyNames, Object[] values) {
  try{
   for(String propertyName:propertyNames){
    calSql+=" model."+propertyName+"=? and";
         }
   calSql=StringUtils.removeEnd(calSql, "and");
   List<Map<String,String>> result = find(calSql,values);
   return result;
  }catch(Exception e){
   e.printStackTrace();
   return null;
  }
}

public String getOrderParamName() {
return orderPropertyName;
}

public void setOrderParamName(String orderPropertyName) {
this.orderPropertyName = orderPropertyName;
}

public Integer findMax(String propertyName) {
  DetachedCriteria date = DetachedCriteria.forClass(typeClass());
  date = addOrderBy(date);
  date.setProjection(Projections.max(propertyName));
  return (Integer) findByCriteria(date).get(0);
}

public T findById(String id) {
return (T) getSession().get(typeClass(), id);
}

public T findById(Integer id) {
return (T) getSession().get(typeClass(), id);
}

// DaoUtil data

public String getPrimaryKeyParam(Class cla) {
  if (cla == null) {
   cla = typeClass();
  }
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  return classMetadata.getIdentifierPropertyName();
}

/**
* 取得主键propertyColumn
*
* @param objClass
* @param columnName
* @return
*/
public String getPrimaryKeyColumn(Class cla) {
  if (cla == null) {
   cla = typeClass();
  }
  List<String> resultList = new ArrayList<String>();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  // 添加主键
  resultList.addAll(Arrays.asList(classMetadata
    .getIdentifierColumnNames()));
  if (!resultList.isEmpty()) {
   String propertyName = resultList.get(0);
   return propertyName;
  }
  return null;
}

/**
* 按列名与对像取出映射对象的字段
*
* @param objClass
* @param columnName
* @return
*/
public String getDBColunmByParam(Class cla, String propertyName) {
  if (cla == null) {
   cla = typeClass();
  }
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  // 判断是否一对多的对像,移除
  boolean isCollection = classMetadata.getClassMetadata()
    .getPropertyType(propertyName).isCollectionType();
  if (!isCollection) {
   String[] propertyColumnNames = classMetadata
     .getPropertyColumnNames(propertyName);
   if (propertyColumnNames != null && propertyColumnNames.length > 0) {
    return propertyColumnNames[0];
   }
  }

return null;
}

/**
* 取得数据表与model 的对应关系
*
* @author zsb
* @date Oct 26, 2010 3:02:15 PM
* @版本 V 1.0
* @return
*/
public Map<String, String> getModelColunmInfo(Class cla) {
  if (cla == null) {
   cla = typeClass();
  }
  Map<String, String> map = new HashMap<String, String>();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  String primaryKeyProperty = classMetadata.getIdentifierPropertyName();
  String primaryKeyColumnName[] = classMetadata
    .getIdentifierColumnNames();
  if (primaryKeyColumnName != null && primaryKeyColumnName.length == 1) {
   map.put(primaryKeyColumnName[0], primaryKeyProperty); // 主键
   String[] propertyNames = classMetadata.getPropertyNames();
   for (String propertyName : propertyNames) {
    // 判断是否一对多的对像,移除
    boolean isCollection = classMetadata.getClassMetadata()
      .getPropertyType(propertyName).isCollectionType();
    if (!isCollection) {
     String[] propertyColumnNames = classMetadata
       .getPropertyColumnNames(propertyName);
     if (propertyColumnNames != null
       && propertyColumnNames.length == 1) {
      map.put(propertyColumnNames[0], propertyName);
     }
    }
   }
  }
  return map;
}

/**
* 取得主键propertyColumn
*
* @param objClass
* @param columnName
* @return
*/
public List getPrimaryKeyColumns(Class cla) {
  if (cla == null) {
   cla = typeClass();
  }
  List<String> resultList = new ArrayList<String>();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  // 添加主键
  resultList.addAll(Arrays.asList(classMetadata
    .getIdentifierColumnNames()));
  if (!resultList.isEmpty()) {
   return resultList;
  }
  return resultList;
}

public Map<String, String> getComplexPrimaryKeyByParam(Class cla,
   String propertyName) {
  if (cla == null) {
   cla = typeClass();
  }
  Map<String, String> map = new Hashtable<String, String>();
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) sessionFactory
    .getClassMetadata(cla);
  // 判断是否一对多的对像,移除
  boolean isCollection = classMetadata.getClassMetadata()
    .getPropertyType(propertyName).isCollectionType();
  if (!isCollection) {
   String[] propertyColumnNames = classMetadata
     .getPropertyColumnNames(propertyName);
   String[] propertys3 = classMetadata.getIdentifierColumnNames();
   if (propertyColumnNames != null && propertyColumnNames.length > 0) {
    int length = propertyColumnNames.length;
    for (int i = 0; i < length; i++) {
     map.put(propertys3[i], propertyColumnNames[i]);
    }
   }
  }
  return map;
}

private void evict(List<T> entryList) {
  if (entryList != null) {
   for (T object : entryList) {
    evict(object);
   }
  }
}

private void evict(T object) {
getSession().evict(object);
}

/**
* @author jofo 换成hibernate4需加接口
*/
public List findByCriteria(DetachedCriteria criteria, int firstResult,
   int maxResults) throws DataAccessException {
  Criteria executableCriteria = criteria
    .getExecutableCriteria(getSession());
  // HibernateTemplate.prepareCriteria(executableCriteria);
  if (firstResult >= 0) {
   executableCriteria.setFirstResult(firstResult);
  }
  if (firstResult > 0) {
   executableCriteria.setMaxResults(firstResult);
  }
  return executableCriteria.list();
}

/**
* @author jofo 换成hibernate4需加接口
*/
public List findByCriteria(DetachedCriteria criteria)
throws DataAccessException {
return findByCriteria(criteria, -1, -1);
}

/**
* @author jofo 换成hibernate4
*/
public void deleteAll(Collection entities) {
  for (Iterator it = entities.iterator(); it.hasNext();) {
   getSession().delete(it.next());
  }
  getSession().flush();
}

public List findByExample(String entityName, Object exampleEntity,
   int firstResult, int maxResults) throws DataAccessException {
  // Assert.notNull(exampleEntity, "Example entity must not be null");
  Criteria executableCriteria = (entityName != null) ? getSession()
    .createCriteria(entityName) : getSession().createCriteria(
    exampleEntity.getClass());

executableCriteria.add(Example.create(exampleEntity));
  // HibernateTemplate.this.prepareCriteria(executableCriteria);
  if (firstResult >= 0) {
   executableCriteria.setFirstResult(firstResult);
  }
  if (maxResults > 0) {
   executableCriteria.setMaxResults(maxResults);
  }
  return executableCriteria.list();
}

public List find(String queryString) throws DataAccessException {
return find(queryString, (Object[]) null);
}

public List find(String queryString, Object value)
throws DataAccessException {
return find(queryString, new Object[] { value });
}

public List find(String queryString, Object[] values)
   throws DataAccessException {
  Query queryObject = getSession().createQuery(queryString);
  // HibernateTemplate.this.prepareQuery(queryObject);
  if (values != null) {
   for (int i = 0; i < values.length; ++i) {
    queryObject.setParameter(i, values[i]);
   }
  }
  return queryObject.list();
}

public int bulkUpdate(String queryString) throws DataAccessException {
return bulkUpdate(queryString, (Object[]) null);
}

public int bulkUpdate(String queryString, Object value)
throws DataAccessException {
return bulkUpdate(queryString, new Object[] { value });
}

public Integer bulkUpdate(String queryString, Object[] values)
   throws DataAccessException {
  Query queryObject = getSession().createQuery(queryString);
  // HibernateTemplate.this.prepareQuery(queryObject);
  if (values != null) {
   for (int i = 0; i < values.length; ++i) {
    queryObject.setParameter(i, values[i]);
   }
   return new Integer(queryObject.executeUpdate()).intValue();
  }
  return 0;
}

public int getIntBySql(String sql){
  List list=this.getListBySql(sql);
  if(list != null && !list.isEmpty()){
   Integer obj=StringUtil.toInteger(list.get(0));
   if(obj != null){
    return obj;
   }
  }
    return 0;
}

public Double getDoubleBySql(String sql){
  List sqlList=this.getListBySql(sql);
  if(sqlList!=null && sqlList.size()>0){
   Double obj=StringUtil.toDouble(sqlList.get(0));
   return obj;
  }else{
   return null;
  }
}

public List<Map<Object,Object>> getListMapObjBySql(String sql, String[] aliasPropertys){
  return getListMapObjBySql(sql,aliasPropertys);
}

/**
* 根据查询HQL与参数列表创建Query对象.
*
* @param values 命名参数,按名称绑定.
*/
public SQLQuery createSQLQuery(final String queryString, final Map<String, Object> values) {
  Assert.hasText(queryString, "queryString不能为空");
  SQLQuery query = getSession().createSQLQuery(queryString);
  if (values != null) {
   query.setProperties(values);
  }
  return query;
}
/**
* 按sql查询唯一对象.
*
* @param values 命名参数,按名称绑定.
*/
public Object findSqlUnique(final String sql, final Map<String, Object> values) {
  //return (X) createSqlQuery(hql, values).uniqueResult();
  return createSQLQuery(sql, values).uniqueResult();
}

public Long getCountByHql(String sql, Map values,
   Boolean[] isNeedDoWith) {

String countHql = sql;
  if(SimpleUtils.isEmptyArray(isNeedDoWith) || isNeedDoWith[0]){
   String fromHql = sql;
   // select子句与order by子句会影响count查询,进行简单的排除,考虑不同的SQL编写习惯
   if (fromHql.indexOf("from") == -1)
    fromHql = "from " + StringUtils.substringAfter(fromHql, "FROM");
   else
    fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
   if (fromHql.indexOf("order by") == -1)
    fromHql = StringUtils.substringBefore(fromHql, "ORDER BY");
   else
    fromHql = StringUtils.substringBefore(fromHql, "order by");

    countHql = "select count(*) " + fromHql;
  }

try {
   long count = 0;
   Object obj = findSqlUnique(countHql, values);
   if (obj != null)
    count = (obj instanceof BigDecimal) ? ((BigDecimal) obj)
      .intValue() : ((BigInteger) obj).intValue();
   return count;
  } catch (Exception e) {
   throw new RuntimeException("sql can't be auto count, sql is:"
     + countHql, e);
  }

}

public List<T> findListByObj(String hql, Object... values) {
  try{
   String hqlString = hql;
   if (!hql.contains(" as ")) {
    hqlString = createMapHql(hqlString);
   }
   // 查询
   List result = find(hqlString, values);
   // 映射成实体
   return (ReflectionUtils.createEntityList(entityClass, result));
  }catch(Exception e){
   e.printStackTrace();
   return null;
  }
}

/** 补充MAP查询的HQL语句 */
public String createMapHql(String hql) {
  int begin = hql.indexOf(" Map(");
  if (begin > 0) {
   int end = hql.indexOf(")", (begin += 5));
   String[] fields = hql.substring(begin, end).split(",");

StringBuilder sb = new StringBuilder();
   sb.append(hql.substring(0, begin));
   for (int i = 0; i < fields.length; i++) {
    if (i > 0)
     sb.append(",");
    sb.append(fields[i]).append(" as ").append(
      fields[i].replaceAll("\\.", "_"));
   }
   sb.append(hql.substring(end));
   hql = sb.toString();
  }
  return hql;
}

/**
* 根据查询HQL与参数列表创建Query对象.
*
* 本类封装的find()函数全部默认返回对象类型为T,当不为T时使用本函数.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public Query createQuery(final String queryString, final Object... values) {
  Assert.hasText(queryString, "queryString不能为空");
  Query query = getSession().createQuery(queryString);
  if (values != null) {
   for (int i = 0; i < values.length; i++) {
    query.setParameter(i, values[i]);
   }
  }
  return query;
}

/**
* 根据查询HQL与参数列表创建Query对象.
*
* @param values 命名参数,按名称绑定.
*/
public Query createQuery(final String queryString, final Map<String, Object> values) {
  Assert.hasText(queryString, "queryString不能为空");
  Query query = getSession().createQuery(queryString);
  if (values != null) {
   query.setProperties(values);
  }
  return query;
}

/**
* 执行count查询获得本次Hql查询所能获得的对象总数.
*
* 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
*/
public long countHqlResult(final String hql, final Object... values) {
  String fromHql = hql;
  // select子句与order by子句会影响count查询,进行简单的排除,考虑不同的SQL编写习惯
  if (fromHql.indexOf("from") == -1)
   fromHql = "from " + StringUtils.substringAfter(fromHql, "FROM");
  else
   fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
  if (fromHql.indexOf("order by") == -1)
   fromHql = StringUtils.substringBefore(fromHql, "ORDER BY");
  else
   fromHql = StringUtils.substringBefore(fromHql, "order by");

String countHql = "select count(*) " + fromHql;

try {
   Long count = findUnique(countHql, values);
   return count;
  } catch (Exception e) {
   throw new RuntimeException("hql can't be auto count, hql is:"
     + countHql, e);
  }
}

public Long getCountByHql(String hql, Object[] values) {
  String fromHql = hql;
  // select子句与order by子句会影响count查询,进行简单的排除,考虑不同的SQL编写习惯
  if (fromHql.indexOf("from") == -1)
   fromHql = "from " + StringUtils.substringAfter(fromHql, "FROM");
  else
   fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
  if (fromHql.indexOf("order by") == -1)
   fromHql = StringUtils.substringBefore(fromHql, "ORDER BY");
  else
   fromHql = StringUtils.substringBefore(fromHql, "order by");

String countHql = "select count(*) " + fromHql;

try {
   Long count = findUnique(countHql, values);
   return count;
  } catch (Exception e) {
   throw new RuntimeException("hql can't be auto count, hql is:"
     + countHql, e);
  }
}

/**
* 按Criteria查询唯一对象.
*
* @param criterions 数量可变的Criterion.
*/
public T findUnique(final Criterion... criterions) {
  return (T) createCriteria(criterions).uniqueResult();
}

/**
* 按HQL查询唯一对象.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public <X> X findUnique(final String hql, final Object... values) {
  return (X) createQuery(hql, values).uniqueResult();
}

/**
* 判断对象的属性值在数据库内是否唯一.
*
* 在修改对象的情景下,如果属性新修改的值(value)等于属性原来的值(orgValue)则不作比较.
*/
public boolean isPropertyUnique(final String propertyName,
   final Object newValue, final Object oldValue) {
  if (newValue == null || newValue.equals(oldValue))
   return true;
  Object object = findUniqueBy(propertyName, newValue);
  return (object == null);
}

/**
* 按属性查找唯一对象,匹配方式为相等.
*/
public T findUniqueBy(final String propertyName, final Object value) {
  Assert.hasText(propertyName, "propertyName不能为空");
  Criterion criterion = Restrictions.eq(propertyName, value);
  return (T) createCriteria(criterion).uniqueResult();
}

/**
* 执行count查询获得本次Criteria查询所能获得的对象总数.
*/
@SuppressWarnings("unchecked")
protected int countCriteriaResult(final Criteria c) {
  CriteriaImpl impl = (CriteriaImpl) c;

// 先把Projection、ResultTransformer、OrderBy取出来,清空三者后再执行Count操作
Projection projection = impl.getProjection();
ResultTransformer transformer = impl.getResultTransformer();

List<CriteriaImpl.OrderEntry> orderEntries = null;
  try {
   orderEntries = (List) ReflectionUtils.getFieldValue(impl,
     "orderEntries");
   ReflectionUtils
     .setFieldValue(impl, "orderEntries", new ArrayList());
  } catch (Exception e) {
   e.printStackTrace();
  }

// 执行Count查询
int totalCount = (Integer) c.setProjection(Projections.rowCount())
.uniqueResult();

// 将之前的Projection,ResultTransformer和OrderBy条件重新设回去
c.setProjection(projection);

if (projection == null) {
   c.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
  }
  if (transformer != null) {
   c.setResultTransformer(transformer);
  }
  try {
   ReflectionUtils.setFieldValue(impl, "orderEntries", orderEntries);
  } catch (Exception e) {
   e.printStackTrace();
  }

return totalCount;
}

/**
* 执行count查询获得本次Hql查询所能获得的对象总数.
*
* 本函数只能自动处理简单的hql语句,复杂的hql查询请另行编写count语句查询.
*/
public Long getCountByCache(final String hql,
   final Map values,String cacheRegion) {
  String fromHql = hql;
  // select子句与order by子句会影响count查询,进行简单的排除,考虑不同的SQL编写习惯
  if (fromHql.indexOf("from") == -1)
   fromHql = "from " + StringUtils.substringAfter(fromHql, "FROM");
  else
   fromHql = "from " + StringUtils.substringAfter(fromHql, "from");
  if (fromHql.indexOf("order by") == -1)
   fromHql = StringUtils.substringBefore(fromHql, "ORDER BY");
  else
   fromHql = StringUtils.substringBefore(fromHql, "order by");

String countHql = "select count(*) " + fromHql;

try {
   Long count = findUniqueByCache(cacheRegion, countHql, values);
   return count;
  } catch (Exception e) {
   throw new RuntimeException("hql can't be auto count, hql is:"
     + countHql, e);
  }
}
/**
* 按HQL查询唯一对象.
* @param <X>
* @param cacheRegion 缓存区域
* @param hql
* @param values 数量可变的参数,按顺序绑定
* @return 唯一对象
*/
public <X> X findUniqueByCache(final String cacheRegion,final String hql, final Map<String, Object> values) {
  Query query = createQuery(hql, values);
  query.setCacheable(true);
  query.setCacheRegion(cacheRegion);
  return (X) query.uniqueResult();
}

public List<Map<String, Object>> getListMapByParam(String sql,
   Map values) {
  SQLQuery q = (SQLQuery) createSQLQuery(sql, values)
    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);// setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List<Map<String, Object>> list = q.list();
return list;
}

public List<Map<String, Object>> getListMapByParam(String sql,
   Object[] values) {
  SQLQuery q = (SQLQuery) createSQLQuery(sql, values)
    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);// setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
  List<Map<String, Object>> list = q.list();
  return list;
}
/**
* 根据查询SQL与参数列表创建Query对象.
*
* 本类封装的find()函数全部默认返回对象类型为T,当不为T时使用本函数.
*
* @param values 数量可变的参数,按顺序绑定.
*/
public Query createSQLQuery(final String queryString, final Object... values) {
  Assert.hasText(queryString, "queryString不能为空");
  SQLQuery query = getSession().createSQLQuery(queryString);
  if (values != null) {
   for (int i = 0; i < values.length; i++) {
    query.setParameter(i, values[i]);
   }
  }
  return query;
}

public List<T> findListByParam(String propertyName, Object value,
   MatchType matchType) {
  Criterion criterion = buildPropertyFilterCriterion(propertyName, value,
    matchType);
  return find(criterion);
}

/**
* 按属性条件参数创建Criterion,辅助函数.
*/
protected Criterion buildPropertyFilterCriterion(final String propertyName,
   final Object propertyValue, final MatchType matchType) {
  Assert.hasText(propertyName, "propertyName不能为空");
  Criterion criterion = null;
  try {

// 根据MatchType构造criterion
   if (MatchType.EQ.equals(matchType)) {
    criterion = Restrictions.eq(propertyName, propertyValue);
   } else if (MatchType.NE.equals(matchType)) {
    criterion = Restrictions.ne(propertyName, propertyValue);
   } else if (MatchType.LIKE.equals(matchType)) {
    criterion = Restrictions.like(propertyName,
      (String) propertyValue, MatchMode.ANYWHERE);
   } else if (MatchType.LE.equals(matchType)) {
    criterion = Restrictions.le(propertyName, propertyValue);
   } else if (MatchType.LT.equals(matchType)) {
    criterion = Restrictions.lt(propertyName, propertyValue);
   } else if (MatchType.GE.equals(matchType)) {
    criterion = Restrictions.ge(propertyName, propertyValue);
   } else if (MatchType.GT.equals(matchType)) {
    criterion = Restrictions.gt(propertyName, propertyValue);
   } else if (MatchType.IN.equals(matchType)) {
    if (propertyValue instanceof String) {
     String value[] = ((String) propertyValue).replaceAll(" ",
       "").split(",");
     criterion = Restrictions.in(propertyName, value);
    } else {
     criterion = Restrictions.in(propertyName,
       (Object[]) propertyValue);
    }
   }
  } catch (Exception e) {
   throw ReflectionUtils.convertReflectionExceptionToUnchecked(e);
  }
  return criterion;
}

/**
* 按Criteria查询对象列表.
*
* @param criterions 数量可变的Criterion.
*/
public List<T> find(final Criterion... criterions) {
  return createCriteria(criterions).list();
}

public Long getCountBySql(String sql, Map values, Boolean[] isNeedDoWith) {

/**
* 按属性条件列表创建Criterion数组,辅助函数.
*/
protected Criterion[] buildPropertyFilterCriterions(
   final List<PropertyFilter> filters) {
  List<Criterion> criterionList = new ArrayList<Criterion>();
  for (PropertyFilter filter : filters) {
   if (!filter.isMultiProperty()) { // 只有一个属性需要比较的情况.
    Criterion criterion = buildPropertyFilterCriterion(filter
      .getPropertyName(), filter.getPropertyValue(), filter
      .getMatchType());
    criterionList.add(criterion);
   } else {// 包含多个属性需要比较的情况,进行or处理.
    Disjunction disjunction = Restrictions.disjunction();
    for (String param : filter.getPropertyNames()) {
     Criterion criterion = buildPropertyFilterCriterion(param,
       filter.getPropertyValue(), filter.getMatchType());
     disjunction.add(criterion);
    }
    criterionList.add(disjunction);
   }
  }
  return criterionList.toArray(new Criterion[criterionList.size()]);
}

/**
* 有缓存
*/
public List findListByHql(String sql, boolean removeCashe, Integer firstResult,
   Integer size) {
  final String _sql = sql;
  final Integer _firstResult = firstResult;
  final Integer _size = size;
  Query query = getSession().createSQLQuery(_sql);
  if (_firstResult != null && _size != null) {
   query.setFirstResult(_firstResult);
   query.setMaxResults(_size);
  }
  List list = query.list();
  if (list != null && !list.isEmpty()) {
   for (Object objs : list) {
    getSession().evict(objs);
   }
  }
  if (removeCashe && list != null && !list.isEmpty()) {
   Object obj;
   for (Iterator iterator1 = list.iterator(); iterator1.hasNext(); getSession()
     .evict(obj))
    obj = iterator1.next();

}
return list;
}

/**
* 有缓存
*/
public List findListBySql(String sql, String alias, Class objClass,
   boolean removeCashe, Integer firstResult, Integer size) {
  final String _sql = sql;
  final String _alias = alias;
  final Class _obj = objClass;
  final Integer _firstResult = firstResult;
  final Integer _size = size;
  Query query = getSession().createSQLQuery(_sql).addEntity(_alias, _obj);
  if (_firstResult != null && _size != null) {
   query.setFirstResult(_firstResult);
   query.setMaxResults(_size);
  }
  List list = query.list();
  if (list != null && !list.isEmpty()) {
   for (Object objs : list) {
    getSession().evict(objs);
   }
  }
  if (removeCashe && list != null && !list.isEmpty()) {
   Object obj;
   for (Iterator iterator1 = list.iterator(); iterator1.hasNext(); getSession()
     .evict(obj))
    obj = iterator1.next();

}
return list;
}

public List getListBySqlAndArray(String arg0, Map arg1, String[] arg2) {
return getListBySqlAndArray(arg0,arg1,arg2,null,null);
}

public List getListBySqlAndArray(String arg0, Map arg1, String[] arg2,
Integer arg3, Integer arg4) {
return getListBySqlAndArray(arg0,arg1,arg2,arg3,arg4);
}

public List getListBySqlAndArray(String arg0, Map arg1, String[] arg2,
   boolean arg3, Integer arg4, Integer arg5) {
  return getListBySqlAndArray(arg0,arg1,arg2,arg3,arg4,arg5);
}

public String getTableName(Class clazz) {
  SessionFactory factory = getSession().getSessionFactory();
  Class cls = clazz;
  AbstractEntityPersister classMetadata = (SingleTableEntityPersister) factory
    .getClassMetadata(cls);
  return classMetadata.getTableName();
}

public List getListMapByParam(String[] property,String[] propertyNames, Object[] values){
  try{
   DetachedCriteria date = DetachedCriteria.forClass(typeClass());
   for (int i = 0; i < propertyNames.length; i++) {
    String propertyName = propertyNames[i];
    Object value = values[i];
    date.add(Restrictions.eq(propertyName, value));
   }
   date = addOrderBy(date);
   List list = findByCriteria(date);
   List<Map<String,Object>> mapList=new ArrayList<Map<String,Object>>();
   for(Object obj:list){
    Field[] fields = obj.getClass().getDeclaredFields();
    Map<String,Object> map=new HashMap<String, Object>();
          for (Field field : fields) {
          field.setAccessible(true);
          if (property != null && property.length > 0) {
      for (int i = 0; i < property.length; i++) {
       if (field.getName().equals(property[i])) {
        map.put(StringUtils.upperCase(field.getName()), field.get(obj));
       }
      }
      continue;
     }
          map.put(StringUtils.upperCase(field.getName()), field.get(obj));
          }
          mapList.add(map);
   }
   return mapList;
  }catch(Exception e){
   e.printStackTrace();
   return null;
  }
}

public Integer countSqlResult(String sql, Map values,
   Boolean... isNeedDoWith) {

try {
   Integer count = 0;
   Object obj = findSqlUnique(countHql, values);
   if (obj != null)
    count = (obj instanceof BigDecimal) ? ((BigDecimal) obj)
      .intValue() : ((BigInteger) obj).intValue();
   return count;
  } catch (Exception e) {
   throw new RuntimeException("sql can't be auto count, sql is:"
     + countHql, e);
  }
}

public HashMap getMapBySqlForPage(String sql, Page page){
   StringBuffer sqlb=new StringBuffer(sql);
   HashMap result = new HashMap();
         if (page!=null) {
    if (!StringUtils.isBlank(page.getSortField())) {
     if ("desc".equals(page.getSortField())) {
      page.setSortOrder("asc");
     }
     sqlb.append(" order by ").append(page.getSortField())
       .append(" ").append(page.getSortOrder());
    }
    List<Map<String, String>> data = new ArrayList();
    int start = page.getPageIndex() * page.getPageSize(), end = start
      + page.getPageSize();
    List<Map<String, String>> dataAll = getListMapBySql(sqlb
      .toString());
    for (int i = 0, l = dataAll.size(); i < l; i++) {
     Map<String, String> record = dataAll.get(i);
     if (record == null)
      continue;
     if (start <= i && i < end) {
      data.add(record);
     }
    }
    result.put("data", data);
    result.put("total", dataAll.size());
   }else{
    List<Map<String, String>> dataAll = getListMapBySql(sqlb
      .toString());
    result.put("data", dataAll);
   }
         return result;
}

public HashMap getMapBySqlForPage(String sql,String sqlCount, Page page){
  StringBuffer sqlb=new StringBuffer(sql);
   HashMap result = new HashMap();
         if (page!=null) {
    if (!StringUtils.isBlank(page.getSortField())) {
     if ("desc".equals(page.getSortField())) {
      page.setSortOrder("asc");
     }
     sqlb.append(" order by ").append(page.getSortField())
       .append(" ").append(page.getSortOrder());
    }
    int start = page.getPageIndex() * page.getPageSize(), end = start
      + page.getPageSize();
    List<Map<String, String>> dataAll = getListMapBySql(sqlb.toString(),start,page.getPageSize());
    result.put("data", dataAll);
    Integer count=getCountBySql(sqlCount);
    result.put("total", count);
   }else{
    List<Map<String, String>> dataAll = getListMapBySql(sqlb
      .toString());
    result.put("data", dataAll);
   }
         return result;
}

public boolean saveList(List domainList) throws Exception {
  if(domainList != null){
   for(Object obj:domainList){
    this.save(obj);
   }
  }
  return true;

//  Session session = getSession();// 获取Session
//  Transaction transaction = session.getTransaction();// 取到事务
//  try {
//   transaction.begin(); // 开启事务
//   for (int i = 0; i < domainList.size(); i++) {
//    session.save(domainList.get(i));
//    if (i % 50 == 0) {//每五十条数据提交SQL
//     session.flush();// 批量提交到数据库
//     session.clear();// 清理一级缓存
//    }
//   }
//   transaction.commit(); // 提交事物
//  } catch (Exception e) {
//   e.printStackTrace();
//   transaction.rollback(); // 出错将回滚事物
//   return false;
//  }
//  return true;
}

public boolean updateList(List domainList) throws Exception {
  if(domainList != null){
   for(Object obj:domainList){
    this.update(obj);
   }
  }
  return true;
//  Session session = getSession();// 获取Session
//  Transaction transaction = session.getTransaction();// 取到事务
//  try {
//   transaction.begin(); // 开启事务
//   for (int i = 0; i < domainList.size(); i++) {
//    session.update(domainList.get(i));
//    if (i % 50 == 0) {//每五十条数据提交SQL
//     session.flush();// 批量提交到数据库
//     session.clear();// 清理一级缓存
//    }
//   }
//   transaction.commit(); // 提交事物
//  } catch (Exception e) {
//   e.printStackTrace();
//   transaction.rollback(); // 出错将回滚事物
//   return false;
//  }
//  return true;
}

}

秒客网

Hibernate4读取Clob数据

相关文章