前端显示的是一个用Jquery bootgrid实现的表
可以通过点击表格的标题来实现正反序列的排列,例如当点击’用户名’时, 会返回生序排列,再次点击会变成降序。点击事件发生时,bootgrid会发送如下:
http://localhost:9090/userlist?current=1&rowCount=10&sort%5Busername%5D=asc&searchPhrase=
说明:
- current : 当前页
- rowCount : 每页显示数, 选全部时, 值为-1
- sort[username] = asc : 表示按username生序排列
- searchPhrase: 模糊搜索字符串
后台用springMVC搭建, 使用MyBatis 连接数据库,同时用PageHelper类进行分页:
在UserController.java类里面:
@RequestMapping(value="/userlist",method=RequestMethod.GET)
@ResponseBody
DataGrid<Userinfo> userlist(@RequestParam("current") int current,@RequestParam("rowCount") int rowCount,
@RequestParam(value="sort[id]", required = false) String sortid,
@RequestParam(value="sort[username]", required = false) String sortusername,
@RequestParam(value="searchPhrase", required = false) String searchPhrase){
List<User> userlist;
int total=systemservice.getallusers().size();
String sortfield = "";
String sortype = "";
//在PageHelper类里面,0表示显示全部记录,所有要在这里改成0
if(rowCount == -1){
rowCount = 0;
}
//检查按哪个字段排序,是uid,还是username, sorttype 记录是'asc' 或 'desc'
if(!com.mysql.jdbc.StringUtils.isNullOrEmpty(sortid)){
sortfield = "uid";
sortype = sortid;
}
if(!com.mysql.jdbc.StringUtils.isNullOrEmpty(sortusername)){
sortfield = "username";
sortype = sortusername;
}
//之后调用SystemService类的getpageusersall来查询
userlist = systemservice.getpageusersall(current, rowCount, searchPhrase, sortfield, sortype);
.
.
.
省略了返回前端json数据部分,文章最后补充上
SystemService 的 getpageusersall 方法:
public List<User> getpageusersall(int pagenum, int pagesize, String searchPhrase, String sortField, String sortType) {
//Sort类是个辅助类,用来向MyBatis 配置中传参
Sort sort = new Sort( sortField, sortType, searchPhrase);
PageHelper.startPage(pagenum, pagesize);
//调用UserMapper类的getuserfull()方法
List<User> l = usermapper.getuserfull(sort);
return l;
}
Sort类很简单,就是把三个可选的参数, sortFiled, sortType 和 searchPhrase传递到Mybatis的配置文件中,Sort类的定义:
public class Sort {
private String field, type, searchPhrase;
public Sort(String field, String type, String searchPhrase) {
this.field = field;
this.type = type;
this.searchPhrase = searchPhrase;
}
public String getField() {
return field;
}
public void setField(String field) {
this.field = field;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getSearchPhrase() {
return searchPhrase;
}
public void setSearchPhrase(String searchPhrase) {
this.searchPhrase = searchPhrase;
}
}
UserMap类也很简单, 就是个接口,用来和MyBatis的配置文件对接:
public interface UserMapper {
List<User> getusers();
List<User> getuserssortasc(Sort sort);
List<User> getuserfull(Sort sort);
User getUserByid(int id);
void deleteuser(int uid);
void deleteuserrole(int uid);
void adduser(User user);
void updateuser(User user);
int getUidByusername(String username);
}
在src/main/resources/mapper目录下,定义了UserMapper.xml文件:
注意: 当在MyBatis里使用order by时, 参数取得要用${}而不是#{}, 这样可以跳过预编译,就不会在字段上加入’ ‘, 否则order by 命令会报错(变成 order by ‘username’, 肯定会报错), 但是为了防止SQL注入,需要对这些传入的变量进行检查,我还没做,主要不知道有什么好方法,希望大家指点。
截取相关上面的一段配置:
<select id="getuserfull" resultMap="userMap">
select * from user
<if test="searchPhrase != null and searchPhrase != '' ">
where username like '%${searchPhrase}%'
</if>
<if test="field != null and field !='' ">
order by ${field} ${type}
</if>
</select>
最后返回前端JSON串:
{
"current": 1,
"rowCount": 10,
"total": 11,
"rows": [
{
"id": 43,
"username": "春兰",
"password": "1234",
"tel": "33",
"age": 33,
"rolelist": "财务管理员"
},
{
"id": 42,
"username": "龙一",
"password": "1234",
"tel": "33",
"age": 44,
"rolelist": "出纳员"
},
{
"id": 41,
"username": "张晓岚",
"password": "1234",
"tel": "34",
"age": 3434,
"rolelist": "后勤经理"
},
{
"id": 40,
"username": "测试1",
"password": "1234",
"tel": "1",
"age": 1,
"rolelist": "人事,出纳员"
},
{
"id": 39,
"username": "aaaaaaa",
"password": "1234",
"tel": "33",
"age": 33,
"rolelist": "人事"
},
{
"id": 38,
"username": "豆腐豆腐",
"password": "1234",
"tel": "3",
"age": 3,
"rolelist": "后勤经理"
},
{
"id": 37,
"username": "zhangming",
"password": "1234",
"tel": "3",
"age": 3,
"rolelist": "人事"
},
{
"id": 36,
"username": "linbin",
"password": "1234",
"tel": "34",
"age": 343434,
"rolelist": "人事,出纳员,总经理,财务管理员,部门经理,采购经理"
},
{
"id": 35,
"username": "WANG",
"password": "1234",
"tel": "222",
"age": 33,
"rolelist": "总经理,部门经理"
},
{
"id": 34,
"username": "xiaocai",
"password": "1234",
"tel": "111",
"age": 32,
"rolelist": "财务管理员,部门经理"
}
]
}
这里要说明的是:在没有加入排序和模糊搜索的时候,为了取得用户在多对多关系中的用户角色,MyBatis的配置文件是这么定义sql查询的:
<select id="getusers" resultMap="userMap">
select * from user left join user_role on user.uid=user_role.userid left join role on user_role.roleid=role.rid
</select>
而在构造返回的json数据时,代码如下:
List<User> userlist=systemservice.getpageusers(current,rowCount);
//注意这里, MyBatis会自动合并查询记录,这里会打印出4, 而不是10,后面有详细说明
System.out.println("user number:" + userlist.size());
List<Userinfo> users=new ArrayList<Userinfo>();
for(User user:userlist){
Userinfo u=new Userinfo();
u.setId(user.getUid());
u.setAge(user.getAge());
u.setPassword(user.getPassword());
u.setTel(user.getTel());
u.setUsername(user.getUsername());
String rolename="";
List<User_role> ur=user.getUser_roles();
//这里就是拼接"rolelist": "总经理,部门经理" 字符串的
if(ur!=null){
for(User_role userole:ur){
rolename=rolename+","+userole.getRole().getRolename();
}
if(rolename.length()>0)
rolename=rolename.substring(1,rolename.length());
u.setRolelist(rolename);
}
users.add(u);
}
DataGrid<Userinfo> grid=new DataGrid<Userinfo>();
grid.setCurrent(current);
grid.setRows(users);
grid.setRowCount(rowCount);
grid.setTotal(total);
return grid;
但是这里有个需要注意的,使用
select * from user left join user_role on user.uid=user_role.userid left join role on user_role.roleid=role.rid limit 0, 10
最开始的搜索结果是:
但是MyBatis会自动执行合并,所以分页0,10的条件最开始是10条记录,等会到congroller的时候,就合并成4条记录了。然后在前端就显示这四条记录。所以我只能做更改:
<select id="getusers" resultMap="userMap">
select * from user
</select>
但是这样的话,就无法拿到多对多关系中的用户角色,所以只能自己重新遍历user结果,再查询role列表:
代码如下:
System.out.println("user number:" + userlist.size());
List<Userinfo> users=new ArrayList<Userinfo>();
for(User user:userlist){
Userinfo u=new Userinfo();
u.setId(user.getUid());
u.setAge(user.getAge());
u.setPassword(user.getPassword());
u.setTel(user.getTel());
u.setUsername(user.getUsername());
String rolename="";
List<User_role> ur=user.getUser_roles();
System.out.println(user.toString());
//为了规避Mybatis合并记录,只能自己取得role列表
user=systemservice.getUserByid(user.getUid());
ur = user.getUser_roles();
System.out.println(user.toString());
if(ur!=null){
for(User_role userole:ur){
rolename=rolename+","+userole.getRole().getRolename();
}
if(rolename.length()>0)
rolename=rolename.substring(1,rolename.length());
u.setRolelist(rolename);
}
users.add(u);
}
DataGrid<Userinfo> grid=new DataGrid<Userinfo>();
grid.setCurrent(current);
grid.setRows(users);
grid.setRowCount(rowCount);
grid.setTotal(total);
return grid;