mysql - Spring Security - 查询security-context.xml中的变化

Question

Spring Security 在假设要使用数据库时，发现security-context.xml 中描述了如下查询

users-by-username-query=
select username,password,enabled from authentication where username=? 

authorities-by-username-query=
select u.username, ur.authority from authentication u, authenticationrole ur 
where u.authenticationId = ur.authenticationId and u.username =? 

它有两个表authentication和authenticationrole

authentication -> authenticationid, username, password, ENABLED(0 / 1)

authenticationrole -> authenticationroleid, authenticationid, authority

想根据我的数据库结构更改这些查询

user -> userid, username, password, userstatus(0 / 1)

role -> roleid, rolecode, rolename

userrole -> roleid, userid

Answer 1

试试这个

用户按用户名查询

select username,password, userstatus from user where username=?

authorities-by-username-query

   select user.username, role.rolename from 
   user join userrole on user.userid=userrole.userid 
   join role on userrole.roleid=role.roleid,
   where user.username=?