51Testing软件测试论坛
标题:
HibernateDaoImpl17(1341)
[打印本页]
作者:
goodddd
时间:
2010-12-15 15:46
标题:
HibernateDaoImpl17(1341)
private SQLQuery makeSubQueryCountQueryForDynamicFilter(Query sourceQuery,
List<Filter> filters, Map<String, Object> parameters,
Session session)
{
StringBuffer sql = new StringBuffer();
StringBuffer sb = new StringBuffer(sourceQuery.getQueryString());
sql.append("select count(*) FROM (");
Map<String, Object> params = new HashMap<String, Object>();
if (filters != null && !filters.isEmpty())
{
sb.append(" where 1=1");
for (Filter filter : filters)
{
getWhereSQL(sb, params, filter);
}
}
sql.append(sb.toString()).append(" ) A ");
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
if (null != parameters)
{
setQueryParameter(makedQuery, parameters);
}
return makedQuery;
}
private SQLQuery makeQueryForDynamicFilter(Query sourceQuery,
List<Filter> filters, Map<String, Object> parameters,
Session session)
{
StringBuffer sql = new StringBuffer();
sql.append(sourceQuery.getQueryString());
Map<String, Object> params = new HashMap<String, Object>();
setFilters(filters, sql, params);
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
if (null != parameters)
{
setQueryParameter(makedQuery, parameters);
}
return makedQuery;
}
private String makeQueryStringFromFilter(Filter filter,
String injectParamNameSuffix)
{
StringBuilder sb = new StringBuilder();
String suffix = injectParamNameSuffix;
if (null == suffix)
{
suffix = "";
}
setFilters(filter, sb, suffix);
return sb.toString();
}
作者:
goodddd
时间:
2010-12-15 15:52
private void setFilters(Filter filter, StringBuilder sb, String suffix)
{
if (filter instanceof EqFilter)
{
sb.append("(").append(filter.getProperty()).append(" = :").append(
filter.getProperty() + suffix).append(")");
}
else if (filter instanceof LikeFilter)
{
sb.append("(").append(filter.getProperty()).append(" like :")
.append(filter.getProperty() + suffix).append(")");
}
else if (filter instanceof NotEqFilter)
{
sb.append(" and (").append(filter.getProperty()).append(" <> :")
.append(filter.getProperty() + suffix).append(")");
}
else if (filter instanceof NotLikeFilter)
{
sb.append(" and (").append(filter.getProperty()).append(
" not like :").append(filter.getProperty() + suffix)
.append(")");
}
else if (filter instanceof NullFilter)
{
sb.append(" and (").append(filter.getProperty())
.append(" is null)");
}
else if (filter instanceof NotNullFilter)
{
sb.append(" and (").append(filter.getProperty()).append(
" is not null)");
}
else if (filter instanceof InFilter)
{
sb.append(" and (").append(filter.getProperty()).append(" in (:")
.append(filter.getProperty() + suffix).append("))");
}
else
{
throw new HibernateException("Not supported!");
}
}
private SQLQuery makeQueryForDynamicFilter(Query sourceQuery,
List<Filter> filters, List<Sort> sorts,
Map<String, Object> parameters, Session session)
{
StringBuffer sql = new StringBuffer();
sql.append(sourceQuery.getQueryString());
Map<String, Object> params = new HashMap<String, Object>();
setFilters(filters, sql, params);
setSortColumn(sorts, sql);
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
if (null != parameters)
{
setQueryParameter(makedQuery, parameters);
}
return makedQuery;
}
private void setSortColumn(List<Sort> sorts, StringBuffer sql)
{
if (sorts != null && !sorts.isEmpty())
{
sql.append(" ORDER BY ");
for (Sort sort : sorts)
{
sql.append(sort.getColumn());
sql.append(" ");
sql.append(sort.getOrder());
sql.append(",");
}
sql.deleteCharAt(sql.lastIndexOf(","));
}
}
private void setFilters(List<Filter> filters, StringBuffer sql,
Map<String, Object> params)
{
if (filters != null && !filters.isEmpty())
{
if (sql.indexOf("where") < 0)
{
sql.append(" where 1=1");
}
for (Filter filter : filters)
{
getWhereSQL(sql, params, filter);
}
}
}
作者:
goodddd
时间:
2010-12-15 15:54
1539
private void getWhereSQL(StringBuffer sql, Map<String, Object> params,
Filter filter)
{
if (filter instanceof EqFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" = :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LikeFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" like :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NotEqFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" <> :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NotLikeFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" not like :").append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NullFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" is null)");
}
else if (filter instanceof NotNullFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" is not null)");
}
else if (filter instanceof InFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" in (:")
.append(filter.getProperty()).append("))");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LeFilter)
{
setLeFilter(sql, params, filter);
}
else if (filter instanceof GeFilter)
{
setGeFilter(sql, params, filter);
}
else if (filter instanceof ORFilter)
{
Filter[] arg = (Filter[]) filter.getValue();
sql.append(" and (").append(makeQueryStringFromFilter(arg[0], ""))
.append(" or ").append(
makeQueryStringFromFilter(arg[1], "1")).append(")");
params.put(arg[0].getProperty(), arg[0].getValue());
params.put(arg[1].getProperty() + "1", arg[1].getValue());
}
else
{
throw new HibernateException("Not supported!");
}
}
private void setGeFilter(StringBuffer sql, Map<String, Object> params,
Filter filter)
{
if (params.containsKey(filter.getProperty()))
{
sql.append(" and (").append(filter.getProperty()).append(" >= (:")
.append(filter.getProperty() + "1").append("))");
params.put(filter.getProperty() + "1", filter.getValue());
}
else
{
sql.append(" and (").append(filter.getProperty()).append(" >= (:")
.append(filter.getProperty()).append("))");
params.put(filter.getProperty(), filter.getValue());
}
}
private void setLeFilter(StringBuffer sql, Map<String, Object> params,
Filter filter)
{
if (params.containsKey(filter.getProperty()))
{
sql.append(" and (").append(filter.getProperty()).append(" <= (:")
.append(filter.getProperty() + "1").append("))");
params.put(filter.getProperty() + "1", filter.getValue());
}
else
{
sql.append(" and (").append(filter.getProperty()).append(" <= (:")
.append(filter.getProperty()).append("))");
params.put(filter.getProperty(), filter.getValue());
}
}
作者:
goodddd
时间:
2010-12-15 15:55
1647
private SQLQuery makeCountQueryForDynamicFilter(Query sourceQuery,
List<Filter> filters, Map<String, Object> parameters,
Session session)
{
StringBuffer sql = new StringBuffer();
sql.append("select count(*) ");
sql.append(removeSelect(sourceQuery.getQueryString()));
Map<String, Object> params = new HashMap<String, Object>();
setFilters(filters, sql, params);
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
if (null != parameters)
{
setQueryParameter(makedQuery, parameters);
}
return makedQuery;
}
private SQLQuery makeQueryForDynamicTable(Query sourceQuery,
List<Filter> filters, String table, Map<String, Object> parameters,
Session session)
{
StringBuffer sql = new StringBuffer();
sql.append(sourceQuery.getQueryString().replaceAll("tableName", table));
Map<String, Object> params = new HashMap<String, Object>();
if (filters != null && !filters.isEmpty())
{
sql.append(" where 1=1");
for (Filter filter : filters)
{
generatorSQL(sql, params, filter);
}
}
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
if (parameters != null && !parameters.isEmpty())
{
setQueryParameter(makedQuery, parameters);
}
return makedQuery;
}
private void generatorSQL(StringBuffer sql, Map<String, Object> params,
Filter filter)
{
if (filter instanceof EqFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" = :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LikeFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" like :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NotEqFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" <> :")
.append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NotLikeFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" not like :").append(filter.getProperty()).append(")");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NullFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" is null)");
}
else if (filter instanceof NotNullFilter)
{
sql.append(" and (").append(filter.getProperty()).append(
" is not null)");
}
else if (filter instanceof InFilter)
{
sql.append(" and (").append(filter.getProperty()).append(" in (:")
.append(filter.getProperty()).append("))");
params.put(filter.getProperty(), filter.getValue());
}
else if (filter instanceof ORFilter)
{
Filter[] arg = (Filter[]) filter.getValue();
sql.append(" and (").append(makeQueryStringFromFilter(arg[0], ""))
.append(" or ").append(
makeQueryStringFromFilter(arg[1], "1")).append(")");
params.put(arg[0].getProperty(), arg[0].getValue());
params.put(arg[1].getProperty() + "1", arg[1].getValue());
}
else
{
throw new HibernateException("Not supported!");
}
}
作者:
goodddd
时间:
2010-12-15 15:57
1774
/**
* 功能描述：根据条件删除动态表中的数据
*
* @param queryName 命名查询名称
* @param filters 查询条件 where部分
* @param table 动态表名
* @param parameters 除where以外的参数设置(用于更新操作)
* @throws DataAccessException 数据库访问异常
*/
public void updateOrDeleteByDynamicTable(final String queryName,
final List<Filter> filters, final String table,
final Map<String, Object> parameters) throws DataAccessException
{
try
{
getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
return makeQueryForDynamicTable(query, filters, table,
parameters, session).executeUpdate();
}
});
}
catch (Exception he)
{
log.error("",he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/**
* 功能描述：根据原生SQL来执行修改和删除对象
*
* @author g67943
* @param queryName SQL名称
* @param parameters 更新或者删除条件
* @throws DataAccessException 数据库访问异常
*/
public void updateOrDeleteBySQLQuery(final String queryName,
final Map<String, Object> parameters) throws DataAccessException
{
try
{
Validator.checkStringLen(queryName);
getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
setQueryParameter(query, parameters);
return query.executeUpdate();
};
});
}
catch (ValidatorException ve)
{
log.error("", ve);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, ve);
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/**
* 功能描述：根据HSQLQuery完成更新或者删除对象
* @author g67943
* @param queryName 对应配置文件中HSQL Query的名称
* @param parameters HQuery的对象
* @throws DataAccessException 数据库访问异常 数据访问异常
*/
public void updateOrDeleteByHSQLQuery(final String queryName,
final Map<String, Object> parameters) throws DataAccessException
{
try
{
Validator.checkParmIsNull(queryName);
getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
setQueryParameter(query, parameters);
return query.executeUpdate();
};
});
}
catch (ValidatorException ve)
{
log.error("", ve);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, ve);
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/* 私有方法 */
/**
* 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
*
* @param hql 要去除的hql
* @return 去除select后的hql
*/
private String removeSelect(String hql)
{
int beginPos = hql.toLowerCase().indexOf("from ");
if (beginPos == 0)
{
return hql;
}
beginPos = hql.toLowerCase().indexOf("from ");
return hql.substring(beginPos);
}
作者:
goodddd
时间:
2010-12-15 15:59
/**1891
* 去除hql的orderby 子句，用于pagedQuery.
*
* @param hql 要去除的hql
* @return 去除orderby后的hql子句
* @see #pagedQuery(String,int,int,Object[])
*/
private String removeOrders(String hql)
{
int endPos = hql.toLowerCase().indexOf(" order by");
if (endPos == -1)
{
return hql;
}
return hql.substring(0, endPos);
}
/**
* 根据Filter过滤条件创建Criteria对象.
*
* @param filters 过滤条件
* @param session hibernateTemplate回调的HibernateCallback中的session对象
* @return 创建的Criteria对象
* @throws DataAccessException 数据库访问异常
*/
private Criteria createCriteria(List<Filter> filters, Session session)
{
Criteria criteria = session.createCriteria(entityClass);
if (filters != null && filters.size() > 0)
{
for (Filter filter : filters)
{
criteria.add(getCriterion(filter));
}
}
return criteria;
}
/**
* 功能描述：根据过滤器的条件返回构造Hibernate的查询条件。
*
* @param filter 查询条件键值对
* @return Criterion
*/
@SuppressWarnings("unchecked")
private Criterion getCriterion(Filter filter)
{
if (filter instanceof EqFilter)
{
return Restrictions.eq(filter.getProperty(), filter.getValue());
}
else if (filter instanceof GeFilter)
{
return Restrictions.ge(filter.getProperty(), filter.getValue());
}
else if (filter instanceof GtFilter)
{
return Restrictions.gt(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LtFilter)
{
return Restrictions.lt(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LeFilter)
{
return Restrictions.le(filter.getProperty(), filter.getValue());
}
else if (filter instanceof LikeFilter)
{
return Restrictions.like(filter.getProperty(), filter.getValue());
}
else if (filter instanceof NotEqFilter)
{
return Restrictions.not(Restrictions.eq(filter.getProperty(),
filter.getValue()));
}
else if (filter instanceof NotLikeFilter)
{
return Restrictions.not(Restrictions.like(filter.getProperty(),
filter.getValue()));
}
else if (filter instanceof NullFilter)
{
return Restrictions.isNull(filter.getProperty());
}
else if (filter instanceof NotNullFilter)
{
return Restrictions.isNotNull(filter.getProperty());
}
else if (filter instanceof ORFilter)
{
Filter[] arg = (Filter[]) filter.getValue();
return Restrictions.or(getCriterion(arg[0]), getCriterion(arg[1]));
}
else if (filter instanceof InFilter)
{
return getInFilter(filter);
}
return Restrictions.eq(filter.getProperty(), filter.getValue());
}
@SuppressWarnings("unchecked")
private Criterion getInFilter(Filter filter)
{
if (filter.getValue() instanceof Collection)
{
return Restrictions.in(filter.getProperty(), (Collection) filter
.getValue());
}
else if (filter.getValue() instanceof Object[])
{
return Restrictions.in(filter.getProperty(), (Object[]) filter
.getValue());
}
return null;
}
作者:
goodddd
时间:
2010-12-15 16:02
/**1969
* 功能描述：命名SQL的参数设置
*
* @param query 查询对象
* @param params 参数映射
*/
@SuppressWarnings("unchecked")
protected void setQueryParameter(Query query, Map<String, Object> params)
{
if (params != null)
{
Map.Entry<String, Object> param = null;
for (Iterator<Map.Entry<String, Object>> iter = params.entrySet()
.iterator(); iter.hasNext();)
{
param = iter.next();
if (param.getValue() instanceof Collection)
{
query.setParameterList(param.getKey(), (Collection) param
.getValue());
}
else if (param.getValue() instanceof Object[])
{
query.setParameterList(param.getKey(), (Object[]) param
.getValue());
}
else
{
query.setParameter(param.getKey(), param.getValue());
}
}
}
}
/**
* 功能描述：根据条件构造原生sql查询
*
* @param queryName 命名查询名称
* @param filters 查询条件
* @param clazz 返回对象实体类
* @param curPage 当前页码
* @param pageSize 每页显示多少条记录
* @return List<?>
* @throws DataAccessException 数据库访问异常
*/
public List<?> findPageByDynamicFilter(final String queryName,
final List<Filter> filters, final Class<?> clazz,
final int curPage, final int pageSize) throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeQueryForDynamicFilter(
query, filters, null, session);
if (null != clazz)
{
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
}
return sqlQuery.setFirstResult(
(curPage - 1) * pageSize).setMaxResults(
pageSize).list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
作者:
goodddd
时间:
2010-12-15 16:03
/**2050
* 功能描述：根据条件构造原生sql分页查询
*
* @param queryName 命名查询名称
* @param filters 查询条件
* @param sorts 排序条件
* @param clazz 返回对象实体类
* @param curPage 当前页码
* @param pageSize 每页显示多少条记录
* @return List<?>
* @throws DataAccessException 数据库访问异常
*/
public List<?> findPageByDynamicFilter(final String queryName,
final List<Filter> filters, final List<Sort> sorts,
final Class<?> clazz, final int curPage, final int pageSize)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeQueryForDynamicFilter(
query, filters, sorts, null, session);
if (null != clazz)
{
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
}
return sqlQuery.setFirstResult(
(curPage - 1) * pageSize).setMaxResults(
pageSize).list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/**
* 功能描述：根据条件构造原生sql查询
*
* @param queryName 命名查询名称
* @param filters 查询条件
* @param parameters 最后的where以外的参数(多见于子查询中的参数)
* @return List<?>
* @throws DataAccessException 数据库访问异常
*/
public int getCountByDynamicFilter(final String queryName,
final List<Filter> filters, final Map<String, Object> parameters)
throws DataAccessException
{
try
{
return (Integer) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeCountQueryForDynamicFilter(
query, filters, parameters, session);
return sqlQuery.list().get(0);
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
作者:
goodddd
时间:
2010-12-15 16:06
/**2138
* 功能描述：根据条件构造原生sql分页查询
*
* @param queryName 命名查询名称
* @param filters 查询条件
* @param parameters 最后的where以外的参数(多见于子查询中的参数)
* @param clazz 返回对象实体类
* @param curPage 当前页码
* @param pageSize 每页显示多少条记录
* @return List<?>
* @throws DataAccessException 数据库访问异常
*/
public List<?> findPageByDynamicFilter(final String queryName,
final List<Filter> filters, final Map<String, Object> parameters,
final Class<?> clazz, final int curPage, final int pageSize)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeQueryForDynamicFilter(
query, filters, parameters, session);
if (null != clazz)
{
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
}
return sqlQuery.setFirstResult(
(curPage - 1) * pageSize).setMaxResults(
pageSize).list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/**
* 功能描述：根据条件构造原生sql分页查询
*
* @param queryName 命名查询名称
* @param filters 查询条件
* @param parameters 最后的where以外的参数(多见于子查询中的参数)
* @param clazz 返回对象实体类
* @throws DataAccessException 数据库访问异常
*/
public List<?> findPageByDynamicFilter(final String queryName,
final List<Filter> filters, final List<Sort> sorts, final Map<String, Object> parameters,
final Class<?> clazz)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeQueryForDynamicFilter(
query, filters, sorts, parameters, session);
if (null != clazz)
{
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
}
return sqlQuery.list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
作者:
goodddd
时间:
2010-12-15 16:08
/**
*2226
*/
public List<?> findPageByDynamicFilter(final String queryName,
final List<Filter> filters, final List<Sort> sorts,
final Map<String, Object> parameters, final Class<?> clazz,
final int curPage, final int pageSize) throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.getNamedQuery(queryName);
SQLQuery sqlQuery = makeQueryForDynamicFilter(
query, filters, sorts, parameters, session);
if (null != clazz)
{
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
}
return sqlQuery.setFirstResult(
(curPage - 1) * pageSize).setMaxResults(
pageSize).list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
public List<?> findBySQL(final String sql, final Class<?> clazz)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.createSQLQuery(sql);
query.setResultTransformer(Transformers
.aliasToBean(clazz));
return query.list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
public List<?> findBySQL(final String sql, final Class<?> clazz,
final int curPage, final int pageSize) throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Query query = session.createSQLQuery(sql);
query.setResultTransformer(Transformers
.aliasToBean(clazz));
return query.setFirstResult(
(curPage - 1) * pageSize).setMaxResults(
pageSize).list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
作者:
goodddd
时间:
2010-12-15 16:09
/**2340
* 功能描述：根据过滤和排序条件查询出分页列表记录
*
* @param filters 过滤条件
* @param sorts 排序条件
* @param currentPage 页码
* @param pageSize 页面大小
* @return 满足条件的一页列表记录
* @throws DataAccessException 数据库访问异常 数据访问异常
*/
@SuppressWarnings("unchecked")
public List<T> getPageListWithOrder(final List<Filter> filters,
final List<Sort> sorts) throws DataAccessException
{
try
{
return (List<T>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
Criteria criteria = createCriteria(filters, session);
if (null != sorts && sorts.size() > 0)
{
for (Sort sort : sorts)
{
if (sort.getOrder().equals(Sort.ASC))
{
criteria.addOrder(Order.asc(sort
.getColumn()));
}
else
{
criteria.addOrder(Order.desc(sort
.getColumn()));
}
}
}
return criteria.list();
};
});
}
catch (Exception he)
{
log.error("", he);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, he);
}
}
/**
* 功能描述：从配置表读出SQL语句，拼装新的SQL语句并执行
* @param queryName
* @param parameters
* @param filters
* @param appendSql
* @return
* @throws DataAccessException
*/
public List<?> findByDynamicSql(final String queryName,
final Map<String, Object> parameters, final List<Filter> filters,
final List<Sort> sorts, final String appendSql, final Class<?> clazz)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
// 1.从配置表读出SQL语句
Query readQuery = session.getNamedQuery(queryName);
StringBuffer sqlBuff = new StringBuffer(readQuery
.getQueryString());
// 2.拼装SQL语句
if (null != appendSql
&& !"".equals(appendSql.trim()))
{
if (sqlBuff.indexOf("where") < 0)
{
sqlBuff.append(" where 1=1");
}
sqlBuff.append(appendSql);
}
// 3.创建新的QUERY
Query newQuery = session.createSQLQuery(sqlBuff
.toString());
// 4.填充占位符
SQLQuery sqlQuery = makeQueryForDynamicFilter(
newQuery, filters, sorts, parameters,
session);
// 5.执行SQL
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
return sqlQuery.list();
}
});
}
catch (Exception e)
{
log.error("", e);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, e);
}
}
作者:
goodddd
时间:
2010-12-15 16:10
/**2433
* 获取某个表的记录数 通过查询sqlserver系统表的记录，与表的实际记录数可能有细微的出入
*/
@SuppressWarnings("unchecked")
public long getTableRowCount(final String tableName)
{
return ((Integer) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
List ls = session
.createSQLQuery(
"select rows from sysindexes where indid < 2 and id = object_id(?)")
.setString(0, tableName).list();
if (ls.isEmpty())
{
return 0;
}
return ls.get(0);
};
})).longValue();
}
/**
* 功能描述：从配置表读出SQL语句，拼装新的SQL语句并执行(分页)
* @param queryName
* @param parameters
* @param filters
* @param appendSql
* @return
* @throws DataAccessException
*/
public List<?> findByDynamicSql(final String queryName,
final Map<String, Object> parameters, final List<Filter> filters,
final List<Sort> sorts, final String appendSql,
final Class<?> clazz, final int pageNo, final int pageSize)
throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
// 1.从配置表读出SQL语句
Query readQuery = session.getNamedQuery(queryName);
StringBuffer sqlBuff = new StringBuffer(readQuery
.getQueryString());
// 2.拼装SQL语句
if (null != appendSql
&& !"".equals(appendSql.trim()))
{
if (sqlBuff.indexOf("where") < 0)
{
sqlBuff.append(" where 1=1");
}
sqlBuff.append(appendSql);
}
// 3.创建新的QUERY
Query newQuery = session.createSQLQuery(sqlBuff
.toString());
// 4.填充占位符
SQLQuery sqlQuery = makeQueryForDynamicFilter(
newQuery, filters, sorts, parameters,
session);
// 5.执行SQL
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
return sqlQuery.setFirstResult(
(pageNo - 1) * pageSize).setMaxResults(
pageSize).list();
}
});
}
catch (Exception e)
{
log.error("", e);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, e);
}
}
作者:
goodddd
时间:
2010-12-15 16:11
/**2494
* 功能描述：从配置表读出SQL语句，拼装新的SQL语句并执行
* @param queryName
* @param parameters
* @param filters
* @param appendSql
* @return
* @throws DataAccessException
*/
public int findCountByDynamicSql(final String queryName,
final Map<String, Object> parameters, final List<Filter> filters,
final String appendSql) throws DataAccessException
{
try
{
return (Integer) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
// 1.从配置表读出SQL语句
Query readQuery = session.getNamedQuery(queryName);
StringBuffer sqlBuff = new StringBuffer(readQuery
.getQueryString());
// 2.拼装SQL语句
if (null != appendSql
&& !"".equals(appendSql.trim()))
{
if (sqlBuff.indexOf("where") < 0)
{
sqlBuff.append(" where 1=1");
}
sqlBuff.append(appendSql);
}
// 3.创建新的QUERY
Query newQuery = session.createSQLQuery(sqlBuff
.toString());
// 4.填充占位符
SQLQuery sqlQuery = makeQueryForDynamicFilter(
newQuery, filters, parameters, session);
// 5.执行SQL
return sqlQuery.list().get(0);
}
});
}
catch (Exception e)
{
log.error("", e);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, e);
}
}
作者:
goodddd
时间:
2010-12-15 16:11
/**2616
* 功能描述：从配置表读出SQL语句，替换里面的占位符，并拼装新的SQL语句执行
* @param queryName
* @param replaceParams 替换SQL中占位符的参数
* @param filters
* @param appendSql
* @return
* @throws DataAccessException
*/
public List<?> findByDynReplaceSql(final String queryName,
final Map<String, Object> replaceParams,
final List<Filter> filters, final List<Sort> sorts,
final String appendSql, final Class<?> clazz, final int pageNo,
final int pageSize) throws DataAccessException
{
try
{
return (List<?>) getHibernateTemplate().execute(
new HibernateCallback()
{
public Object doInHibernate(Session session)
{
// 1.从配置表读出SQL语句
Query readQuery = session.getNamedQuery(queryName);
StringBuffer sqlBuff = new StringBuffer(readQuery
.getQueryString());
// 2.拼装SQL语句
if (null != appendSql
&& !"".equals(appendSql.trim()))
{
if (sqlBuff.indexOf("where") < 0)
{
sqlBuff.append(" where 1=1");
}
sqlBuff.append(appendSql);
}
// 3.创建新的QUERY
Query newQuery = session.createSQLQuery(sqlBuff
.toString());
// 4.填充占位符
SQLQuery sqlQuery = makeQueryForReplaceParamAndDynFilter(
newQuery, filters, sorts, replaceParams,
session);
// 5.执行SQL
sqlQuery.setResultTransformer(Transformers
.aliasToBean(clazz));
return sqlQuery.setFirstResult(
(pageNo - 1) * pageSize).setMaxResults(
pageSize).list();
}
});
}
catch (Exception e)
{
log.error("", e);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, e);
}
}
/**
* 功能描述：通过替换占位符和拼装过滤器,排序器，生成新的SQLQuery
* @param sourceQuery
* @param filters
* @param sorts
* @param replaceParams
* @param session
* @return
*/
private SQLQuery makeQueryForReplaceParamAndDynFilter(Query sourceQuery,
List<Filter> filters, List<Sort> sorts,
Map<String, Object> replaceParams, Session session)
{
StringBuffer sql = new StringBuffer();
sql.append(sourceQuery.getQueryString());
Map<String, Object> params = new HashMap<String, Object>();
setFilters(filters, sql, params);
setSortColumn(sorts, sql);
if (null != replaceParams)
{
sql = new StringBuffer(replaceQueryParameter(sql.toString(),
replaceParams));
}
SQLQuery makedQuery = session.createSQLQuery(sql.toString());
if (!params.isEmpty())
{
setQueryParameter(makedQuery, params);
}
return makedQuery;
}
/**
* 功能描述：替换命名SQL中的占位符
*
* @param sql 替换占位符前的SQL语句
* @param replaceParams 参数映射
*/
@SuppressWarnings("unchecked")
protected String replaceQueryParameter(String sql,
Map<String, Object> replaceParams)
{
if (null != replaceParams)
{
for (Entry<String, Object> e : replaceParams.entrySet())
{
String key = e.getKey();
String replaceValue = (String) e.getValue();
sql = sql.replaceAll(key, replaceValue);
}
}
return sql;
}
作者:
goodddd
时间:
2010-12-15 16:12
/**2672
* 功能描述：动态更新或删除数据
* @param queryName
* @param parameters
* @param filters
* @param appendSql
* @throws DataAccessException
*/
public void updateOrDelByDynamicSql(final String queryName,
final Map<String, Object> parameters, final List<Filter> filters,
final String appendSql) throws DataAccessException
{
try
{
getHibernateTemplate().execute(new HibernateCallback()
{
public Object doInHibernate(Session session)
{
// 1.从配置表读出SQL语句
Query readQuery = session.getNamedQuery(queryName);
StringBuffer sqlBuff = new StringBuffer(readQuery
.getQueryString());
// 2.拼装SQL语句
if (null != appendSql && !"".equals(appendSql.trim()))
{
if (sqlBuff.indexOf("where") < 0)
{
sqlBuff.append(" where 1=1");
}
sqlBuff.append(appendSql);
}
// 3.创建新的QUERY
Query newQuery = session.createSQLQuery(sqlBuff.toString());
// 4.填充占位符
SQLQuery sqlQuery = makeQueryForDynamicFilter(newQuery,
filters, parameters, session);
// 5.执行SQL
return sqlQuery.executeUpdate();
}
});
}
catch (Exception e)
{
log.error("", e);
throw new DataAccessException(
IDataAccessExceptionConstants.E_HIBERNATE_ERROR, e);
}
}
}
作者:
goodddd
时间:
2010-12-15 16:22
package com.huaweisymantec.core.util;
import java.util.Collection;
import org.apache.log4j.Logger;
import com.huaweisymantec.core.common.exception.IValidatorExceptionConstants;
import com.huaweisymantec.core.common.exception.ValidatorException;
/*****************************************校验**************************************
* @ Copyright(c) 2004~2008.
*
* <PRE>
* Project Name :
*
* Package Name :
*
* File Name : Validator.java
*
* Creation Date : Nov 12, 2008
*
* Author :
*
* Purpose : 验证类
*
*
* History : 1.0
*
*
* </PRE>
******************************************************************************/
public class Validator implements java.io.Serializable
{
private static final long serialVersionUID = 4059074741442821214L;
private static Logger log = Logger.getLogger(Validator.class);
private Validator()
{
}
/**
* 功能描述：验证参数对象是否为空
* @param obj 对象
* @throws ValidatorException 参数合法性验证异常 数据验证异常
*/
public static final void checkParmIsNull(Object obj)
throws ValidatorException
{
if (null == obj)
{
log.error("obj is null...");
throw new ValidatorException(
IValidatorExceptionConstants.E_ARGUMENT_IS_NULL);
}
}
/**
* 功能描述：验证字符串对象是否为空或者是长度为0
* @param source 源
* @throws ValidatorException 参数合法性验证异常 数据验证异常
*/
public static final void checkStringLen(String source)
throws ValidatorException
{
if (null == source || source.trim().length() == 0)
{
log.error("source is null...");
throw new ValidatorException(
IValidatorExceptionConstants.E_STRING_IS_EMPTY);
}
}
/**
* 功能描述：验证集合对象是否为空或者集合的数据是否为空
* @param c 集合
* @throws ValidatorException 参数合法性验证异常
*/
public static final void checkCollectionIsNull(Collection<?> c)
throws ValidatorException
{
checkParmIsNull(c);
if (c.size() == 0)
{
log.error("collection is null...");
throw new ValidatorException(
IValidatorExceptionConstants.E_COLLECTION_IS_EMPTY);
}
}
}
作者:
goodddd
时间:
2010-12-15 16:38
package com.huaweisymantec.core.framework.hibernate.filter;
//等于号的过滤器的实现
public class EqFilter extends AbstractFilter implements Filter
{
/**
*
*/
private static final long serialVersionUID = 7379227648710833118L;
public EqFilter(String property, Object value)
{
super(property, value);
}
}
//Hibernate查询过滤接口
package com.huaweisymantec.core.framework.hibernate.filter;
import java.io.Serializable;
public interface Filter extends Serializable
{
public String getProperty();
public Object getValue();
}
//大于等于过滤器
public class GeFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 6393739604512321848L;
public GeFilter(String property, Object value)
{
super(property, value);
}
}
//大于号过滤器
public class GtFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 3514980852961212166L;
public GtFilter(String property, Object value)
{
super(property, value);
}
}
//IN操作过滤器
public class InFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 8497739726131661216L;
public InFilter(String property, Object value)
{
super(property, value);
}
}
//小于等于号过滤器
public class LeFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = -1098421522768665466L;
public LeFilter(String property, Object value)
{
super(property, value);
}
}
//Like查询过滤器
public class LikeFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 7512252843956983646L;
public LikeFilter(String property, Object value)
{
super(property, value);
}
}
//小于号过滤
public class LtFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = -4374774124302205894L;
public LtFilter(String property, Object value)
{
super(property, value);
}
}
//不等于号过滤器
public class NotEqFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = -6047954438581905650L;
public NotEqFilter(String property, Object value)
{
super(property, value);
}
}
//Not Like查询过滤器
public class NotLikeFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = -3640753543478852674L;
public NotLikeFilter(String property, Object value)
{
super(property, value);
}
}
//非空的过滤器
public class NotNullFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 5514895090495410072L;
public NotNullFilter(String property, Object value)
{
super(property, value);
}
}
//空值过滤器
public class NullFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 1673924189101424908L;
public NullFilter(String property, Object value)
{
super(property, value);
}
}
//OR操作过滤器
public class ORFilter extends AbstractFilter implements Filter
{
private static final long serialVersionUID = 181102804001682953L;
public ORFilter(String property, Object value)
{
super(property, value);
}
}
作者:
goodddd
时间:
2010-12-15 16:39
//排序对象
public class Sort implements java.io.Serializable
{
private static final long serialVersionUID = 2511268176857697675L;
public static final String ASC = "ASC";
public static final String DESC = "DESC";
// 排序字段名称
private String column;
// 排序字段的排序方式
private Order order;
/**
* 构造函数
*/
public Sort()
{
}
public Sort(String column)
{
this.column = column;
this.order = Order.ASC;
}
public Sort(String column, Order order)
{
this.column = column;
this.order = order;
}
public String getColumn()
{
return column;
}
public String getOrder()
{
return order.getOrder();
}
}
//查询过滤器
package com.huaweisymantec.core.framework.hibernate.filter;
import java.io.Serializable;
public abstract class AbstractFilter implements Serializable
{
private static final long serialVersionUID = 3552843930263383239L;
private final String property;
private final Object value;
protected AbstractFilter(String property, Object value)
{
this.property = property;
this.value = value;
}
public String getProperty()
{
return property;
}
public Object getValue()
{
return value;
}
}
欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)
Powered by Discuz! X3.2