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) {
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);
}
}
/**
* 按属性条件列表创建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) {
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 {
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;
}
}
