HibernateDaoImpl17(1341)

goodddd · 发表于 2010-12-15 15:46:48

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:18

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:52

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:51

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:44

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:52

/**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:30

/**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:58

/**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:34

/**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:28

/**
*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:19

/**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:03

/**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:05

/**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:38

/**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:02

/**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:40

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:40

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:07

//排序对象
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

[原创] HibernateDaoImpl17(1341)

相关帖子

站长推荐 /1