/**
* 通过用户id得到用户信息
*
* @param id 用户的id
*/
public User getUserById(int id) {
Connection conn = DbUtil.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
List<Role> roleList = new ArrayList<Role>();
try {
ps =
conn.prepareStatement(
"SELECT ad.name,au.id,au.department_id,au.create_time,au.user_name,au.user_password,au.user_real_name,au.use_status from auth_user au left join auth_department ad on ad.id = au.department_id where au.id = ? ");
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setDepartmentId(rs.getInt("department_id"));
user.setDepartmentName(rs.getString("name"));
user.setCreateTime(rs.getTimestamp("create_time"));
user.setUserPassword(rs.getString("user_password"));
user.setUserRealName(rs.getString("user_real_name"));
user.setUseStatus(rs.getInt("use_status"));
}
ps =
conn.prepareStatement(
"SELECT ar.id,ar.role_name ,ar.role_ch_name from auth_user_role aur LEFT JOIN auth_role ar on aur.role_id = ar.id where aur.user_id = ? ");
ps.setInt(1, id);
rs = ps.executeQuery();
Role role = null;
while (rs.next()) {
role = new Role();
role.setId(rs.getInt("id"));
role.setRoleName(rs.getString("role_name"));
role.setRoleChName(rs.getString("role_ch_name"));
roleList.add(role);
}
user.setRoleList(roleList);
} catch (Exception e) {
log.error("查询用户信息出错", e);
} finally {
DbUtil.closeConnection(null, ps, conn);
}
return user;
}
/**
* 分页获取用户列表信息
*
* @param page
* @return
*/
public Page<User> getUserPage(Page<User> page, Map<String, String> param) {
DbOperation db = new DbOperation();
try {
// 查询总记录数
StringBuilder sb = new StringBuilder(50);
sb.append(
"select count(1) from auth_user au left join auth_department ad on au.department_id = ad.id ");
if (param != null) {
sb.append("where 1 = 1 ");
if (StringUtils.isNotBlank(param.get("username").toString())) {
sb.append(" and au.user_name like '%")
.append(param.get("username").toString())
.append("%' ");
}
if (StringUtils.isNotBlank(param.get("departmentname").toString())) {
sb.append(" and ad.name like '%")
.append(param.get("departmentname").toString())
.append("%' ");
}
}
ResultSet rs = db.executeQuery(sb.toString());
if (rs.next()) {
page.setTotalRecords(rs.getInt(1));
}
// 查询列表数据
if (page.getTotalRecords() > 0) {
List<User> list = new ArrayList<User>();
User user;
StringBuilder sql = new StringBuilder(50);
sql.append(
"SELECT au.id,au.department_id,au.create_time,au.user_name,au.user_password,au.user_real_name,au.use_status,ad.name from auth_user au left join auth_department ad on au.department_id = ad.id ");
if (param != null) {
sql.append("where 1 = 1 ");
if (StringUtils.isNotBlank(param.get("username").toString())) {
sql.append(" and au.user_name like '%")
.append(param.get("username").toString())
.append("%' ");
}
if (StringUtils.isNotBlank(param.get("departmentname").toString())) {
sql.append(" and ad.name like '%")
.append(param.get("departmentname").toString())
.append("%' ");
}
}
sql.append(" order by au.id desc limit ");
sql.append(page.getFirstResult()).append(",").append(page.getPageCount());
rs = db.executeQuery(sql.toString());
while (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("user_name"));
user.setDepartmentId(rs.getInt("department_id"));
user.setCreateTime(rs.getTimestamp("create_time"));
user.setUserPassword(rs.getString("user_password"));
user.setUserRealName(rs.getString("user_real_name"));
user.setUseStatus(rs.getInt("use_status"));
user.setDepartmentName(rs.getString("name"));
list.add(user);
}
page.setList(list);
}
} catch (Exception e) {
log.error("分页获取用户列表数据时出现异常：", e);
} finally {
this.release(db);
}
return page;
}
