数据库结构：
用户跟组织：多对对
用户跟角色：多对多
组织：有上级组织
资源跟组织：多对多
资源跟角色：多对多
资源：有上级资源
资源：有资源类型
BaseDaoImpl中用了Hibernate原生的session：
@Repository
public class BaseDaoImpl<T> implements BaseDaoI<T> {
@Autowired
private SessionFactory sessionFactory;
/**
* 获得当前事物的session
*
* @return org.hibernate.Session
*/
public Session getCurrentSession() {
return sessionFactory.getCurrentSession();
}
@Override
public T getByHql(String hql) {
Query q = getCurrentSession().createQuery(hql);
List<T> l = q.list();
if (l != null && l.size() > 0) {
return l.get(0);
}
return null;
}
@Override
public T getByHql(String hql, Map<String, Object> params) {
Query q = getCurrentSession().createQuery(hql);
if (params != null && !params.isEmpty()) {
for (String key : params.keySet()) {
q.setParameter(key, params.get(key));
}
}
List<T> l = q.list();
if (l != null && l.size() > 0) {
return l.get(0);
}
return null;
}
@Override
public Long count(String hql) {
Query q = getCurrentSession().createQuery(hql);
return (Long) q.uniqueResult();
}
@Override
public List<Map> findBySql(String sql) {
SQLQuery q = getCurrentSession().createSQLQuery(sql);
return q.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
}
BaseServiceImpl中有对泛型的处理：
@Service
public class BaseServiceImpl<T> implements BaseServiceI<T> {
@Autowired
private BaseDaoI<T> baseDao;
@Override
public T getById(Serializable id) {
Class<T> c = (Class<T>) ((ParameterizedType) getClass()
.getGenericSuperclass()).getActualTypeArguments()[0];
return baseDao.getById(c, id);
}
@Override
public T getByFilter(HqlFilter hqlFilter) {
String className = ((Class<T>) ((ParameterizedType) getClass()
.getGenericSuperclass()).getActualTypeArguments()[0]).getName();
String hql = "select distinct t from " + className + " t";
return getByHql(hql + hqlFilter.getWhereAndOrderHql(),
hqlFilter.getParams());
}
@Override
public Long countByFilter(HqlFilter hqlFilter) {
String className = ((Class<T>) ((ParameterizedType) getClass()
.getGenericSuperclass()).getActualTypeArguments()[0]).getName();
String hql = "select count(distinct t) from " + className + " t";
return count(hql + hqlFilter.getWhereHql(), hqlFilter.getParams());
}
这里有个自定义的HqlFilter，用于添加where条件和排序，过滤结果集
/**
* 带参构造
*
* @param request
*/
public HqlFilter(HttpServletRequest request) {
this.request = request;
addFilter(request);
}
/**
* 添加过滤
*
* @param request
*/
public void addFilter(HttpServletRequest request) {
Enumeration<String> names = request.getParameterNames();
while (names.hasMoreElements()) {
String name = names.nextElement();
String value = request.getParameter(name);
addFilter(name, value);
}
}
/**
* 添加过滤
*
* 举例，name传递：QUERY_t#id_S_EQ
*
* 举例，value传递：0
*
* @param params
*/
public void addFilter(String name, String value) {
if (name != null && value != null) {
if (name.startsWith("QUERY_")) {// 如果有需要过滤的字段
String[] filterParams = StringUtils.split(name, "_");
if (filterParams.length == 4) {
String columnName = filterParams[1].replaceAll("#", ".");// 要过滤的字段名称=t.id
String columnType = filterParams[2];// 字段类型=S
String operator = filterParams[3];// SQL操作符=EQ
String placeholder = UUID.randomUUID().toString().replace("-", "");// 生成一个随机的参数名称
if (hql.toString().indexOf(" where 1=1") < 0) {
hql.append(" where 1=1 ");
}
hql.append(" and " + columnName + " " + getSqlOperator(operator) + " :param"
+ placeholder + " ");// 拼HQL
params.put("param" + placeholder, getObjValue(columnType, operator, value));// 添加参数
}
}
}
}
上面这个方法把 request里的参数全部映射，并且把 QUERY_t#id_S_EQ 拼成HQL
“Where 1=1 and t.id =:paramXXX “
然后准备好paramXXX的参数替换
其中操作符operator 是自定义的，包括EQ,NE,LK等，表示=,!=,like
参数替换时的columnType也是自定义的，包括S，I，D等，表示String,Integer,Date
String的时候如果是like，还要动态组合两边的 ’%’
/**
* 获得添加过滤字段后加上排序字段的HQL
*
* @return
*/
public String getWhereAndOrderHql() {
if (!StringUtils.isBlank(sort) && !StringUtils.isBlank(order)) {
if (sort.indexOf(".") < 1) {
sort = "t." + sort;
}
hql.append(" order by " + sort + " " + order + " ");// 添加排序信息
} else {
if (request != null) {
String s = request.getParameter("sort");
String o = request.getParameter("order");
if (!StringUtils.isBlank(s)) {
sort = s;
}
if (!StringUtils.isBlank(o)) {
order = o;
}
if (!StringUtils.isBlank(sort) && !StringUtils.isBlank(order)) {
if (sort.indexOf(".") < 1) {
sort = "t." + sort;
}
hql.append(" order by " + sort + " " + order + " ");// 添加排序信息
}
}
}
return hql.toString();
}
这样就把Action里的参数完全转换成了HQL
SyuserServiceImpl中：
@Override
public void grantRole(String id, String roleIds) {
Syuser user = getById(id);
if (user != null) {
user.setSyroles(new HashSet<Syrole>());
for (String roleId : roleIds.split(",")) {
if (!StringUtils.isBlank(roleId)) {
Syrole role = roleDao.getById(Syrole.class, roleId);
if (role != null) {
user.getSyroles().add(role);
}
}
}
}
}
@Override
public void grantOrganization(String id, String organizationIds) {
Syuser user = getById(id);
if (user != null) {
user.setSyorganizations(new HashSet<Syorganization>());
for (String organizationId : organizationIds.split(",")) {
if (!StringUtils.isBlank(organizationId)) {
Syorganization organization = organizationDao.getById(
Syorganization.class, organizationId);
if (organization != null) {
user.getSyorganizations().add(organization);
}
}
}
}
}
SyroleServiceImpl中：
@Override
public void saveRole(Syrole syrole, String userId) {
save(syrole);
Syuser user = userDao.getById(Syuser.class, userId);
user.getSyroles().add(syrole);// 把新添加的角色与当前用户关联
}
SyorganizationServiceImpl中：
@Override
public void saveOrganization(Syorganization syorganization, String userId) {
save(syorganization);
Syuser user = userDao.getById(Syuser.class, userId);
user.getSyorganizations().add(syorganization);
}
Model中：
Syuser：
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "SYUSER_SYORGANIZATION", schema = "", joinColumns = { @JoinColumn(name = "SYUSER_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "SYORGANIZATION_ID", nullable = false, updatable = false) })
public Set<Syorganization> getSyorganizations() {
return this.syorganizations;
}
public void setSyorganizations(Set<Syorganization> syorganizations) {
this.syorganizations = syorganizations;
}
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "SYUSER_SYROLE", schema = "", joinColumns = { @JoinColumn(name = "SYUSER_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "SYROLE_ID", nullable = false, updatable = false) })
public Set<Syrole> getSyroles() {
return this.syroles;
}
public void setSyroles(Set<Syrole> syroles) {
this.syroles = syroles;
}
Syrole：
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "SYUSER_SYROLE", schema = "", joinColumns = { @JoinColumn(name = "SYROLE_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "SYUSER_ID", nullable = false, updatable = false) })
public Set<Syuser> getSyusers() {
return this.syusers;
}
public void setSyusers(Set<Syuser> syusers) {
this.syusers = syusers;
}
Syorganization：
@OneToMany(fetch = FetchType.LAZY, mappedBy = "syorganization", cascade = CascadeType.ALL)
public Set<Syorganization> getSyorganizations() {
return this.syorganizations;
}
public void setSyorganizations(Set<Syorganization> syorganizations) {
this.syorganizations = syorganizations;
}
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "SYUSER_SYORGANIZATION", schema = "", joinColumns = { @JoinColumn(name = "SYORGANIZATION_ID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "SYUSER_ID", nullable = false, updatable = false) })
public Set<Syuser> getSyusers() {
return this.syusers;
}
public void setSyusers(Set<Syuser> syusers) {
this.syusers = syusers;
}
这样，权限相关的后台部分就结束了，效果如下：
1.点击登陆，会首先获取菜单((SyresourceServiceI) service).getMainMenu(
先查角色的：
select distinct t from Syresource t join t.syroles role join role.syusers user where 1=1 and user.id = :param49bd757c16594cbe8657b7d88364d81a and t.syresourcetype.id = :paramf778a1257fa64e9ebca2a52bcc9750fc
再查组织的：
select distinct t from Syresource t join t.syorganizations organization join organization.syusers user where 1=1 and user.id = :param49bd757c16594cbe8657b7d88364d81a and t.syresourcetype.id = :paramf778a1257fa64e9ebca2a52bcc9750fc
2.点击用户管理菜单，会通过SecurityUtil查询权限，并动态显示某些功能图标
因为登陆时已经对该用户所有资源都强制加载了，这时候不再查数据库
也就是每次更改了权限后需要重新登陆
3.点击某个用户的用户角色：
首先调用user.getSyroles()，得到当前用户所有角色：当然这里是错的，应该跟用户无关，查询所有角色
Initializing collection [sy.model.base.Syuser.syroles#0]
SELECT
syroles0_.SYUSER_ID AS SYUSER1_7_1_,
syroles0_.SYROLE_ID AS SYROLE2_9_1_,
syrole1_.ID AS ID1_5_0_,
syrole1_.CREATEDATETIME AS CREATEDA2_5_0_,
syrole1_.DESCRIPTION AS DESCRIPT3_5_0_,
syrole1_.ICONCLS AS ICONCLS4_5_0_,
syrole1_. NAME AS NAME5_5_0_,
syrole1_.SEQ AS SEQ6_5_0_,
syrole1_.UPDATEDATETIME AS UPDATEDA7_5_0_
FROM
SYUSER_SYROLE syroles0_
INNER JOIN SYROLE syrole1_ ON syroles0_.SYROLE_ID = syrole1_.ID
WHERE
syroles0_.SYUSER_ID = '0'
Collection initialized
这里为什么又查一遍数据库？？
登陆时不是全都初始化过了？？
然后得到所选用户的所有角色，并在页面上勾选：
select distinct t from Syrole t join t.syusers user where 1=1 and user.id = :paramdac50de702204cc6a258b8af78905a65
另外虽然数据库中的多对多并没有方向性，但页面上并不提供双向查询
这里的顺序是：用户->角色/组织->资源
通过后台可以发现，Hibernate总是有不必要的Sql执行，实际项目中肯定要最后优化一遍
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。