接口
package com.cslc.pcbp.bms.basic.dao;
import java.io.Serializable;
import java.util.List;
import org.hibernate.criterion.DetachedCriteria;
/**
* <P>Title: pcbp_bms</P>
* <P>Description: 基本DAO接口</P>
* <P>Copyright: Copyright(c) 2013</P>
* <P>Company: cslc.com.cn</P>
* @author zwh
* @version 1.0
* @date Apr 12, 2013
*
*/
public interface IBaseDao<T> {
/**
* 持久化实体
* @param t 实体对象
*
**/
public void persist(T t) throws Exception;
/**
* 持久化实体
* @param t 实体对象
* @return Long 持久对象主键值
**/
public Long save(T t) throws Exception;
/**
* 查找
* @param id 实体对象id
* @return T 持久对象
**/
public T findById(Serializable id) throws Exception;
/**
* 更新
* @param t 实体对象
* @return T 持久对象
**/
public T update(T t) throws Exception;
/**
* 批量更新 使用 名称参数占位 即 xxx = :ttt 类型 不使用 xxx = ?
* @param hql 更新语句
* @param paraNameArr 占位参数名
* @param paraValueArr 参数值
* @param paraTypeArr 参数类型
* @return Integer 批量更新影响行数
**/
public Integer batchUpdate(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList) throws Exception;
/**
* 更新
* @param t 实体对象
* @return T 持久对象
**/
public void updateNoReturn(T t) throws Exception;
/**
* 删除
* @param t 实体对象
*
**/
public void delete(T t) throws Exception;
/**
* 根据查询对象得实体列表
* @param criteria 实体查询对象
* @return List<T> 实体对象列表
**/
public List<T> findByCriteria(DetachedCriteria criteria) throws Exception;
/**
* 根据查询对象得对象数量
* @param criteria 实体查询对象
* @return Integer 实体对象数量
**/
public Integer findCountByCriteria(final DetachedCriteria criteria) throws Exception;
/**
* 根据查询对象得实体分页列表
* @param criteria 实体查询对象
* @param pageIndex 页号
* @param pageSize 列表长度
* @return List<T> 实体对象列表
**/
public List<T> findPageByCriteria(final DetachedCriteria criteria,final Integer pageIndex,final Integer pageSize) throws Exception;
/**
* 根据hql语句得实体数量 要求以 select count(x) 开头 且 使用 名称参数占位 即 xxx = :ttt 类型 不使用 xxx = ?
* @param hql hql语句
* @param paraNameArr 占位参数名
* @param paraValueArr 参数值
* @param paraTypeArr 参数类型
* @return List<T> 实体对象列表
**/
public Integer queryCount(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList) throws Exception;
/**
* 根据hql语句得实体分页列表 使用 名称参数占位 即 xxx = :ttt 类型 不使用 xxx = ?
* @param hql hql语句
* @param paraNameArr 占位参数名
* @param paraValueArr 参数值
* @param paraTypeArr 参数类型
* @param pageIndex 页号
* @param pageSize 列表长度
* @return List<T> 实体对象列表
**/
public List<T> queryForPage(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList,final Integer pageIndex,final Integer pageSize) throws Exception;
/**
* 根据hql语句得实体分页列表
* @param hql hql语句
* @param pageIndex 页号
* @param pageSize 列表长度
* @return List<T> 实体对象列表
**/
public List<T> queryForPage(final String hql,final Integer pageIndex,final Integer pageSize) throws Exception;
/**
* 查询所有记录数(HQL)
*
* @param hql 查询的语句
* @return 总记录数
*/
public Integer getAllRowCount(final String hql);
/**
* 根据hql语句得实体分页列表
* @param hql hql语句
* @param pageIndex 页号
* @param pageSize 列表长度
* @return List<T> 实体对象列表
**/
public List<T> queryForPageSQL(final String sql,final Integer pageIndex,final Integer pageSize) throws Exception;
/**
* 查询所有记录数(HQL)
*
* @param hql 查询的语句
* @return 总记录数
*/
public Integer getAllRowCountSQL(final String sql);
}
实现
package com.cslc.pcbp.bms.basic.dao.impl;
import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import com.cslc.pcbp.bms.basic.dao.IBaseDao;
/**
* <P>Title: pcbp_bms</P>
* <P>Description: 基本DAO接口实现</P>
* <P>Copyright: Copyright(c) 2013</P>
* <P>Company: cslc.com.cn</P>
* @author zwh
* @version 1.0
* @date Apr 12, 2013
*
*/
public class BaseDaoImpl<T> extends HibernateDaoSupport implements IBaseDao<T>{
@SuppressWarnings("unchecked")
public BaseDaoImpl() {
Type type = this.getClass().getGenericSuperclass();
if (type instanceof ParameterizedType) {
Type trueType = ((ParameterizedType)type).getActualTypeArguments()[0];
this.type = (Class<T>) trueType;
}
}
private Class<T> type;
public BaseDaoImpl(Class<T> type){
this.type = type;
}
@Override
public void delete(T t) throws Exception {
// TODO Auto-generated method stub
this.getHibernateTemplate().delete(t);
}
@SuppressWarnings("unchecked")
@Override
public List<T> findByCriteria(DetachedCriteria criteria) throws Exception {
// TODO Auto-generated method stub
return (List<T>)this.getHibernateTemplate().findByCriteria(criteria);
}
@SuppressWarnings("unchecked")
@Override
public T findById(Serializable id) throws Exception {
// TODO Auto-generated method stub
return (T)this.getHibernateTemplate().get(type, id);
}
@SuppressWarnings("unchecked")
@Override
public List<T> findPageByCriteria(final DetachedCriteria detachedCriteria,
final Integer pageIndex, final Integer pageSize) throws Exception {
// TODO Auto-generated method stub
return (List<T>)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException,SQLException {
Criteria criteria = detachedCriteria.getExecutableCriteria(session);
if(pageIndex != null && pageSize != null){
criteria.setFirstResult((pageIndex-1)*pageSize).setMaxResults(pageSize);
}
return criteria.list();
}
});
}
@Override
public void persist(T t) throws Exception {
// TODO Auto-generated method stub
this.getHibernateTemplate().persist(t);
}
@Override
public Long save(T t) throws Exception {
// TODO Auto-generated method stub
return (Long)getHibernateTemplate().save(t);
}
@SuppressWarnings("unchecked")
@Override
public T update(T t) throws Exception {
// TODO Auto-generated method stub
return (T)this.getHibernateTemplate().merge(t);
}
@Override
public void updateNoReturn(T t) throws Exception{
this.getHibernateTemplate().update(t);
}
@SuppressWarnings("unchecked")
@Override
public Integer findCountByCriteria(final DetachedCriteria detachedCriteria)
throws Exception {
// TODO Auto-generated method stub
return (Integer)getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException,SQLException {
Criteria criteria = detachedCriteria.getExecutableCriteria(session);
return ((Integer)criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<T> queryForPage(final String hql,final Integer pageIndex,final Integer pageSize)
throws Exception {
// TODO Auto-generated method stub
return (List<T>)getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException,SQLException{
Query query = session.createQuery(hql);
if(pageIndex != null && pageSize != null){
query.setFirstResult((pageIndex-1)*pageSize);
query.setMaxResults(pageSize);
}
return query.list();
}
});
}
@SuppressWarnings("unchecked")
@Override
public Integer queryCount(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList) throws Exception {
// TODO Auto-generated method stub
return (Integer)getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException,SQLException{
Query query = session.createQuery(hql);
if(paraNameList != null && paraNameList.size() > 0){
for(int i = 0;i<paraNameList.size();i++){
if(paraValueList.get(i) instanceof Collection){
query.setParameterList(paraNameList.get(i), (Collection)paraValueList.get(i), paraTypeList.get(i));
}else{
query.setParameter(paraNameList.get(i), paraValueList.get(i), paraTypeList.get(i));
}
}
}
return ((Long)query.uniqueResult()).intValue();
}
});
}
@SuppressWarnings("unchecked")
@Override
public List<T> queryForPage(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList
,final Integer pageIndex,final Integer pageSize) throws Exception {
// TODO Auto-generated method stub
return (List<T>)getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException,SQLException{
Query query = session.createQuery(hql);
if(paraNameList != null && paraNameList.size() > 0){
for(int i = 0;i<paraNameList.size();i++){
if(paraValueList.get(i) instanceof Collection){
query.setParameterList(paraNameList.get(i), (Collection)paraValueList.get(i), paraTypeList.get(i));
}else{
query.setParameter(paraNameList.get(i), paraValueList.get(i), paraTypeList.get(i));
}
}
}
if(pageIndex != null && pageSize != null){
query.setFirstResult((pageIndex-1)*pageSize);
query.setMaxResults(pageSize);
}
return query.list();
}
});
}
@SuppressWarnings("unchecked")
@Override
public Integer getAllRowCount(final String hql) {
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createQuery(hql);
List result = query.list();
return result;
}
});
if(list != null && list.size() > 0) {
return (Integer)(list.size());
}
return 0;
}
@SuppressWarnings("unchecked")
@Override
public Integer getAllRowCountSQL(final String sql) {
List list = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery(sql);
List result = query.list();
return result;
}
});
if(list != null && list.size() > 0) {
return (Integer)(list.size());
}
return 0;
}
@SuppressWarnings("unchecked")
@Override
public List<T> queryForPageSQL(final String sql, final Integer pageIndex,
final Integer pageSize) throws Exception {
// TODO Auto-generated method stub
return (List<T>)getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException,SQLException{
Query query = session.createSQLQuery(sql);
if(pageIndex != null && pageSize != null){
query.setFirstResult((pageIndex-1)*pageSize);
query.setMaxResults(pageSize);
}
return query.list();
}
});
}
@SuppressWarnings("unchecked")
@Override
public Integer batchUpdate(final String hql,final List<String> paraNameList,final List<Object> paraValueList,final List<org.hibernate.type.Type> paraTypeList) throws Exception {
// TODO Auto-generated method stub
return (Integer)getHibernateTemplate().execute(new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException,SQLException{
Query query = session.createQuery(hql);
if(paraNameList != null && paraNameList.size() > 0){
for(int i = 0;i<paraNameList.size();i++){
if(paraValueList.get(i) instanceof Collection){
query.setParameterList(paraNameList.get(i), (Collection)paraValueList.get(i), paraTypeList.get(i));
}else{
query.setParameter(paraNameList.get(i), paraValueList.get(i), paraTypeList.get(i));
}
}
}
return query.executeUpdate();
}
});
}
}
例子:
子接口:
package com.cslc.pcbp.bms.system.dao;
import java.util.List;
import com.cslc.pcbp.bet.entity.bst.TBusiRole;
import com.cslc.pcbp.bms.basic.dao.IBaseDao;
/**
* <P>Title: pcbp_bms</P>
* <P>Description: 业务角色DAO接口</P>
* <P>Copyright: Copyright(c) 2013</P>
* <P>Company: cslc.com.cn</P>
* @author zwh
* @version 1.0
* @date May 2, 2013
*
*/
public interface IRoleDao extends IBaseDao<TBusiRole>{
/**
* 查询角色个数 模糊查询 %*%
* @param name 角色名称
* @return Integer 角色个数
**/
public Integer getRoleCount(String name) throws Exception;
/**
* 查询角色列表 模糊查询 %*%
* @param name 角色名称
* @return List<TBusiRole> 角色列表
**/
public List<TBusiRole> getRoleList(String name,Integer pageIndex,Integer pageSize) throws Exception;
}
子接口实现:
package com.cslc.pcbp.bms.system.dao.impl;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import com.cslc.pcbp.bet.entity.bst.TBusiRole;
import com.cslc.pcbp.bms.basic.dao.impl.BaseDaoImpl;
import com.cslc.pcbp.bms.system.dao.IRoleDao;
import com.cslc.pcbp.bms.tools.CharConversion;
public class RoleDaoImpl extends BaseDaoImpl<TBusiRole> implements IRoleDao{
@Override
public Integer getRoleCount(String name) throws Exception {
// TODO Auto-generated method stub
// DetachedCriteria criteria = DetachedCriteria.forClass(TBusiRole.class);
// if(StringUtils.isNotBlank(name)){
// criteria.add(Restrictions.like("name", name,MatchMode.ANYWHERE));
// }
// return this.findCountByCriteria(criteria);
StringBuilder hql = new StringBuilder("select count(t) from TBusiRole t ");
List<String> paraNameList = new ArrayList<String>();
List<Object> paraValueList = new ArrayList<Object>();
List<org.hibernate.type.Type> paraTypeList = new ArrayList<org.hibernate.type.Type>();
if(StringUtils.isNotBlank(name)){
hql.append(" where t.name like :name escape '\\'");
paraNameList.add("name");
paraValueList.add("%"+CharConversion.sqlSpecialCharConversion(name)+"%");
paraTypeList.add(Hibernate.STRING);
}
return this.queryCount(hql.toString(), paraNameList, paraValueList, paraTypeList);
}
@Override
public List<TBusiRole> getRoleList(String name, Integer pageIndex,
Integer pageSize) throws Exception {
// TODO Auto-generated method stub
// DetachedCriteria criteria = DetachedCriteria.forClass(TBusiRole.class);
// if(StringUtils.isNotBlank(name)){
// criteria.add(Restrictions.like("name", name,MatchMode.ANYWHERE));
// }
// criteria.addOrder(Order.desc("createdTime")).addOrder(Order.desc("roleId"));
// return this.findPageByCriteria(criteria, pageIndex, pageSize);
StringBuilder hql = new StringBuilder(" from TBusiRole t");
List<String> paraNameList = new ArrayList<String>();
List<Object> paraValueList = new ArrayList<Object>();
List<org.hibernate.type.Type> paraTypeList = new ArrayList<org.hibernate.type.Type>();
if(StringUtils.isNotBlank(name)){
hql.append(" where t.name like :name escape '\\'");
paraNameList.add("name");
paraValueList.add("%"+CharConversion.sqlSpecialCharConversion(name)+"%");
paraTypeList.add(Hibernate.STRING);
}
return this.queryForPage(hql.toString(), paraNameList, paraValueList, paraTypeList,pageIndex,pageSize);
}
}
模糊查询时特殊字符转换:
package com.cslc.pcbp.bms.tools;
import org.apache.commons.lang.StringUtils;
public class CharConversion {
public static String sqlSpecialCharConversion(String parameter){
if(StringUtils.isBlank(parameter))return "";
return parameter.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\'").replaceAll("%", "\\\\%").replaceAll("_", "\\\\_").replaceAll("]", "]]");
}
}
注意几点:
1. 处理模糊查询时需要对特殊字符进行转换;
2. 模糊查询对特殊字符进行处理后sql语句需加入escape '\\',且放在紧跟where语句后其他语句前,如果有多个模糊查询 可用 if(hql.indexOf("like") != -1)hql.append(" escape '\\' "); 来加入
3. DetachedCriteria与Criteria在进行非模糊查询时已对特殊字符进行转换.
4. struts 显示标签 如下可以正常显示特殊字符
即:值外双引号 内单引号
<input type="text" name="roleName" value="<s:property value='roleName' />"/>