SMBMS系统

一、步骤

1.搭建一个web项目

2.配置Tomcat

3.导入依赖和更新web.xml

4.编写实体类JavaBean；

5.创建项目的目录

编写基础公共类，工具类；
  1. 数据库配置文件db.properties,

    关于读取配置文件的问题；其中一种方法，使用类加载器。getClassLoader ().getResource会选择当前class文件的根目录(src/)，在web程序中，我们的Java文件夹中Java文件编译过后会在classes文件夹下，我们的resource下面的文件也会到这个包里，所以可以这么使用；关于类加载器的内容，慢慢的开始补充；

1
InputStream stream = BaseDao.class.getClassLoader ().getResourceAsStream ("db.properties");
2.编写数据库的查询类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
package com.saxon.Dao;


import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class BaseDao {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        try {
            InputStream stream = BaseDao.class.getClassLoader ().getResourceAsStream ("db.properties");
            Properties properties = new Properties ();
            properties.load (stream);
            driver = properties.getProperty ("driver");
            url = properties.getProperty ("url");
            username = properties.getProperty ("username");
            password = properties.getProperty ("password");

        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

    public static Connection getConnection () {
        Connection connection = null;
        try {
            Class.forName (driver);
            connection = DriverManager.getConnection (url, username, password);
        } catch (Exception e) {
            e.printStackTrace ();
        }
        return connection;
    }

    /**
     * @param connection 数据库连接;
     * @param sql        查询语句;
     * @param params     参数;
     * @return 返回查询;
     */
    public static ResultSet executeQuery (Connection connection, String sql, Object[] params) {
        ResultSet resultSet = null;
        if (connection != null) {
            try {
                PreparedStatement preparedStatement = connection.prepareStatement (sql);
                for (int i = 1; i < params.length; i++) {
                    preparedStatement.setObject (i, params[i - 1]);
                }
                resultSet = preparedStatement.executeQuery (sql);

            } catch (Exception e) {
                e.printStackTrace ();
            }
        }
        return resultSet;
    }

    /**
     * @param connection 数据库连接
     * @param sql        数据库查询语句
     * @param params     数据库的属性
     * @return 返回改变的行数
     */

    public static int execute (Connection connection, String sql, Object[] params) {
        int changeRows = 0;
        if (connection != null) {
            try {
                PreparedStatement preparedStatement = connection.prepareStatement (sql);
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject (i + 1, params[i]);
                }
                changeRows = preparedStatement.executeUpdate ();
            } catch (SQLException throwables) {
                throwables.printStackTrace ();
            }
        }
        return changeRows;
    }

    /**
     * 关闭连接释放资源
     *
     * @param connection 连接
     * @param statement  数据库本身
     * @param resultSet  结果集合
     * @return 返回释放结果
     */
    public static boolean release (Connection connection, Statement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close ();
                connection = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace ();
                System.out.println ("connection" + "关闭失败");
                return false;
            }
        }
        if (statement != null) {
            try {
                statement.close ();
                statement = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace ();
                System.out.println ("statement" + "关闭失败");
                return false;
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close ();
                resultSet = null;
            } catch (SQLException throwables) {
                throwables.printStackTrace ();
                System.out.println ("resultSet" + "关闭失败");
                return false;
            }
        }
        return true;

    }
}

3.编写字符编码过滤器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.saxon.filter;

import javax.servlet.*;
import java.io.IOException;

public class CharacterEncodingFilter implements Filter {
    @Override
    public void init (FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter (ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
        request.setCharacterEncoding ("utf-8");
        response.setCharacterEncoding ("utf-8");
        chain.doFilter (request, response);

    }

    @Override
    public void destroy () {

    }
}

7.导入静态资源
8.编写Dao层代码
1
2
3
4
5
6
7
8
9
10
package com.saxon.Dao.User;

import com.saxon.pojo.User;

import java.sql.Connection;

public interface UserDao {
     User getLoginUser(Connection connection, String userCode,String password);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package com.saxon.Dao.User;

import com.saxon.Dao.BaseDao;
import com.saxon.pojo.User;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {
    private User user;

    public UserDaoImpl () {
        user=new User ();
    }

    @Override
    public User getLoginUser (Connection connection, String userCode,String password) {
        connection = BaseDao.getConnection ();
        String sql = "select * from smbms_user where smbms_user.userCode=? and smbms_user.userPassword=?; ";
        Object[] params = {userCode,password};
        if (connection != null) {
            ResultSet resultSet = BaseDao.executeQuery (connection, sql, params);
            try {
                if(resultSet.next ()){
                    user.setID (resultSet.getInt ("id"));
                    user.setUserCode (resultSet.getString ("userCode"));
                    user.setUserName (resultSet.getString ("userName"));
                    user.setUserPassword (resultSet.getString ("userPassword"));
                    user.setGender (resultSet.getInt ("gender"));
                    user.setBirthday (resultSet.getDate ("birthday"));
                    user.setPhone (resultSet.getString ("phone"));
                    user.setAddress (resultSet.getString ("address"));
                    user.setUserRole (resultSet.getInt ("userRole"));
                    user.setCreateBy (resultSet.getInt ("createdBy"));
                    user.setCreationDate (resultSet.getDate ("creationDate"));
                    user.setModifyBy (resultSet.getInt ("modifyBy"));
                    user.setModifyDate (resultSet.getDate ("modifyDate"));
                }else {
                    user=null;
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace ();
            }

        }
        return user;
    }
}

9.编写service层代码
1
2
3
4
5
6
7
8
package com.saxon.service;

import com.saxon.pojo.User;

public interface UserService {
     User  login(String userCode,String password);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.saxon.service;

import com.saxon.Dao.BaseDao;
import com.saxon.Dao.User.UserDao;
import com.saxon.Dao.User.UserDaoImpl;
import com.saxon.pojo.User;

import java.sql.Connection;

public class UserServiceImpl implements UserService{
    private UserDao userDao;

    public UserServiceImpl () {
      userDao = new UserDaoImpl ();
    }

    @Override
    public User login (String userCode,String password) {
        Connection connection=null;
        User user=null;
        connection=BaseDao.getConnection ();
        if (userDao!=null){
            user = userDao.getLoginUser (connection, userCode,password);
        }
        return user;
    }
}

10.编写注册登录servlet
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.saxon.servlet.login;

import com.saxon.pojo.User;
import com.saxon.service.UserServiceImpl;
import com.saxon.util.constants;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println ("start");
        UserServiceImpl user=new UserServiceImpl ();
        String userCode = req.getParameter ("userCode");
        String password = req.getParameter ("userPassword");
        User login = user.login (userCode, password);
        if (login!=null){
            req.getSession ().setAttribute (constants.USER_SESSION,user);
            resp.sendRedirect ("JSP/frame.jsp");
            resp.setCharacterEncoding ("utf-8");
        }else {
            this.getServletContext ().setAttribute ("error","密码错误");
            resp.sendRedirect ("login.jsp");
        }
    }

    @Override
    protected void doPost (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet (req, resp);
    }
}

二、密码修改

1.旧密码验证

由于旧密码可以被我们储存在session中，所以我们可以不用在验证旧密码的时候去查询数据库；

前端代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<!--
jsp/user.do" 数据提交给的serlvet action里面的数据
这里的name属性就是我们接收值的string 例如req.getParameter ("oldpassword") 表单传递的是name
${}:el表达式，
-->


<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@include file="/JSP/common/head.jsp"%>
<div class="right">
            <div class="location">
                <strong>你现在所在的位置是:</strong>
                <span>密码修改页面</span>
            </div>
            <div class="providerAdd">
                <form id="userForm" name="userForm" method="post" action="${pageContext.request.contextPath }/jsp/user.do">
                    <input type="hidden" name="method" value="savepwd">
                    <!--div的class 为error是验证错误，ok是验证成功-->
                    <div class="info">${message}</div>
                    <div class="">
                        <label for="oldPassword">旧密码：</label>
                        <input type="password" name="oldpassword" id="oldpassword" value="">
						<font color="red"></font>
                    </div>
                    <div>
                        <label for="newPassword">新密码：</label>
                        <input type="password" name="newpassword" id="newpassword" value=""> 
						<font color="red"></font>
                    </div>
                    <div>
                        <label for="rnewpassword">确认新密码：</label>
                        <input type="password" name="rnewpassword" id="rnewpassword" value=""> 
						<font color="red"></font>
                    </div>
                    <div class="providerAddBtn">
                        <!--<a href="#">保存</a>-->
                        <input type="button" name="save" id="save" value="保存" class="input-button">
                    </div>
                </form>
            </div>
        </div>
    </section>
<%@include file="/JSP/common/foot.jsp" %>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/pwdmodify.js" charset="UTF-8"></script>

对旧数据的查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
//验证旧密码，从session中获得
    public void oldPwd (HttpServletRequest req, HttpServletResponse resp) {
        Map<String, String> map = new HashMap<> ();//设置一个map集合来把信息传递出去
        String oldpassword = req.getParameter ("oldpassword");//获得传递过来的信息
        User user = (User) req.getSession ().getAttribute (constants.USER_SESSION);
        if (user != null) {
            if (oldpassword != null) {
                if (oldpassword.equals (user.getUserPassword ())) {
                    map.put ("result", "true");
                } else {
                    map.put ("result", "false");
                }
            } else {
                map.put ("result", "error");
            }
        } else {
            map.put ("result", "sessionerror");
        }
        //设置编译的类型
        resp.setContentType ("application/json");
        PrintWriter writer = null;
        try {
            writer = resp.getWriter ();
            writer.write (JSONArray.toJSONString (map));//将一个对象变为json
            writer.flush ();
        } catch (IOException e) {
            e.printStackTrace ();
        } finally {
            if (writer != null) {
                writer.close ();
            }
        }

    }

把servlet处理的结果放回到一个集合中，再把集合变成一个json对象，这样js可以接收数据；

1
2
3
4
5
6
7
8
9
10
11
12
if (oldpassword.equals (user.getUserPassword ())) {
                    map.put ("result", "true");
                } else {
                    map.put ("result", "false");
                }
            } else {
                map.put ("result", "error");
            }
        } else {
            map.put ("result", "sessionerror");
        }
writer.write (JSONArray.toJSONString (map));//将一个对象变为json

AJAX：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
oldpassword.on("blur", function () {
        $.ajax({
            type: "GET",
            url: path + "/jsp/user.do",//传向的位置
            data: {method: "pwdmodify", oldpassword: oldpassword.val()},//传输的数据
            dataType: "json",//接收的数据类型
            success: function (data) {//成功的页面，回调函数
                if (data.result == "true") {//旧密码正确
                    validateTip(oldpassword.next(), {"color": "green"}, imgYes, true);
                } else if (data.result == "false") {//旧密码输入不正确
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 原密码输入不正确", false);
                } else if (data.result == "sessionerror") {//当前用户session过期，请重新登录
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 当前用户session过期，请重新登录", false);
                } else if (data.result == "error") {//旧密码输入为空
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 请输入旧密码", false);
                }
            },
            error: function (data) {
                //请求出错 回调函数

                validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 请求错误", false);
            }
        });
1
2
//在ajax的成功返回的值中，data,由于接受的数据是json，我们后台返回的是一个map集合编译成的一个json数据，而json的格式就是key-value,所以其实date是一个对象，我们传的数值就是一个属性，所以用data.result;
data.result == "true“
1
2
3
4
5
6
7
$.ajax({
            type: "GET",//接受方式
            url: path + "/jsp/user.do",//传向的位置
            data: {method: "pwdmodify", oldpassword: oldpassword.val()},//传输的数据
            dataType: "json",//接收的数据类型
            success: function (data) {//成功的页面，回调函数,
            error:function(data)

2.密码更新

1.userDao
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Override
    public int changeUserPassword (Connection connection, String userCode, String password) {
        int execute = 0;
        try {
            String sql = "update smbms_user set userPassword =? where userCode=?;";
            Object[] params = {password, userCode};
            if (connection != null) {
                execute = BaseDao.execute (connection, sql, params);
            }
        } catch (Exception e) {
            e.printStackTrace ();
            System.out.println ("UserDaoImpl 关闭失败");
        } finally {
            if (connection != null) {
                BaseDao.release (null, null, null);//感觉应该没啥用
            }
        }
        return execute;
    }

2.userService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public boolean PasswordService (String userCode, String password) {
        boolean flag = true;
        Connection connection = null;
        try {
            connection = BaseDao.getConnection ();
            if (connection != null) {
                int i = userDao.changeUserPassword (connection, userCode, password);
                flag = i > 0;
            }
        } catch (Exception throwables) {
            throwables.printStackTrace ();
        } finally {
            if (connection != null) {
                BaseDao.release (connection, null, null);
            }
        }
        return flag;
    }

3.servlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
//更新密码
    public void updatePassword (HttpServletRequest req, HttpServletResponse resp) throws Exception {
        String newpwd = req.getParameter ("newpassword");
        Object attribute = req.getSession ().getAttribute (constants.USER_SESSION);
        if (attribute != null) {
            User user = (User) attribute;
            UserServiceImpl service = new UserServiceImpl ();
            boolean b = service.PasswordService (user.getUserCode (), newpwd);
            if (b) {
                req.setAttribute ("message", "change password successful");
                req.getSession ().removeAttribute (constants.USER_SESSION);
                req.getRequestDispatcher ("/login.jsp").forward (req, resp);
            } else {
                req.setAttribute ("message", "change password error");
                req.getRequestDispatcher ("#").forward (req, resp);
            }
        } else {
            req.setAttribute ("message", "error");
            req.getRequestDispatcher ("#").forward (req, resp);
        }
    }
1
<div class="info">${message}</div>//接收返回值

最后的提交与否是要三个都是真的时候才会提交

1
2
3
4
5
6
7
8
9
10
11
12
13
14
saveBtn.on("click", function () {
        oldpassword.blur();
        newpassword.blur();
        rnewpassword.blur();
        if (
            oldpassword.attr("validateStatus") == "true"&&
            newpassword.attr("validateStatus") == "true"
            && rnewpassword.attr("validateStatus") == "true") {
            if (confirm("确定要修改密码？")) {
                $("#userForm").submit();
            }
        }

    });

密码验证的js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
var oldpassword = null;
var newpassword = null;
var rnewpassword = null;
var saveBtn = null;

$(function () {
    oldpassword = $("#oldpassword");
    newpassword = $("#newpassword");
    rnewpassword = $("#rnewpassword");
    saveBtn = $("#save");

    oldpassword.next().html("*");
    newpassword.next().html("*");
    rnewpassword.next().html("*");

    oldpassword.on("blur", function () {
        $.ajax({
            type: "GET",
            url: path + "/jsp/user.do",//传向的位置
            data: {method: "pwdmodify", oldpassword: oldpassword.val()},//传输的数据
            dataType: "json",//接收的数据类型
            success: function (data) {//成功的页面，回调函数
                if (data.result == "true") {//旧密码正确
                    validateTip(oldpassword.next(), {"color": "green"}, imgYes, true);
                } else if (data.result == "false") {//旧密码输入不正确
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 原密码输入不正确", false);
                } else if (data.result == "sessionerror") {//当前用户session过期，请重新登录
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 当前用户session过期，请重新登录", false);
                } else if (data.result == "error") {//旧密码输入为空
                    validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 请输入旧密码", false);
                }
            },
            error: function (data) {
                //请求出错 回调函数

                validateTip(oldpassword.next(), {"color": "red"}, imgNo + " 请求错误", false);
            }
        });


    }).on("focus", function () {
        validateTip(oldpassword.next(), {"color": "#666666"}, "* Please enter the original password", false);
    });
    
    
    
    
    
    newpassword.on("focus", function () {
        validateTip(newpassword.next(), {"color": "#666666"}, "* Password length must be greater than 6 and less than 20", false);
    }).on("blur", function () {
        if (newpassword.val() != null && newpassword.val().length > 6
            && newpassword.val().length < 20) {
            validateTip(newpassword.next(), {"color": "green"}, imgYes, true);
        } else {
            validateTip(newpassword.next(), {"color": "red"}, imgNo + " Password input does not meet the specification, please re-enter", false);
        }
    });


    rnewpassword.on("focus", function () {
        validateTip(rnewpassword.next(), {"color": "#666666"}, "* Please enter the same password as above", false);
    }).on("blur", function () {
        if (rnewpassword.val() != null && rnewpassword.val().length > 6
            && rnewpassword.val().length < 20 && newpassword.val() == rnewpassword.val()) {
            validateTip(rnewpassword.next(), {"color": "green"}, imgYes, true);
        } else {
            validateTip(rnewpassword.next(), {"color": "red"}, imgNo + " The two passwords are inconsistent, please re-enter", false);
        }
    });


    saveBtn.on("click", function () {
        oldpassword.blur();
        newpassword.blur();
        rnewpassword.blur();
        if (
            oldpassword.attr("validateStatus") == "true"&&
            newpassword.attr("validateStatus") == "true"
            && rnewpassword.attr("validateStatus") == "true") {
            if (confirm("确定要修改密码？")) {
                $("#userForm").submit();
            }
        }

    });
});

三、用户管理

用户管理是由三条线组成：查询用户，查询角色，查询用户数量（分页）

查询用户：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
@Override
    public List<User> getUserList (Connection connection, String userName, int roleID, int currentPage, int pageSize) {
        StringBuffer sql = new StringBuffer ("select * from smbms_user u ,smbms_role r  where u.userRole=r.id ");
        ResultSet resultSet = null;
        ArrayList<Object> list = new ArrayList<> ();
        List<User> userList = new ArrayList<> ();
        int execute = 0;
        if (connection != null) {
            if (userName != null) {
                sql.append ("and u.userName like ? ");
                list.add ("%" + userName + "%");
            }
            if (roleID > 0) {
                sql.append ("and r.id= ?");
                list.add (roleID);
            }
            sql.append (" order by u.id  limit ?,?;");//第一个参数指定第一个返回记录行的偏移量,就是从第几个开始，第二个参数指定返回记录行的最大数目
            currentPage = (currentPage - 1) *pageSize;
            list.add (currentPage);
            list.add (pageSize);

            try {
                resultSet = BaseDao.executeQuery (connection, sql.toString (), list.toArray ());
                while (resultSet.next ()) {
                    User _user = new User ();
                    _user.setID (resultSet.getInt ("id"));
                    _user.setUserCode (resultSet.getString ("userCode"));
                    _user.setUserName (resultSet.getString ("userName"));
                    _user.setUserPassword (resultSet.getString ("userPassword"));
                    _user.setGender (resultSet.getInt ("gender"));
                    _user.setBirthday (resultSet.getDate ("birthday"));
                    _user.setPhone (resultSet.getString ("phone"));
                    _user.setAddress (resultSet.getString ("address"));
                    _user.setUserRole (resultSet.getInt ("userRole"));
                    _user.setCreateBy (resultSet.getInt ("createdBy"));
                    _user.setCreationDate (resultSet.getDate ("creationDate"));
                    _user.setModifyBy (resultSet.getInt ("modifyBy"));
                    _user.setModifyDate (resultSet.getDate ("modifyDate"));
                    userList.add (_user);
                }
            } catch (Exception throwables) {
                throwables.printStackTrace ();
            } finally {
                BaseDao.release (null, null, resultSet);
            }
        }
        return userList;
    }

这里的重点是利用字符串的拼接和集合的使用，来把查询语句拼成一句查询语句。用于实现我们的用户管理界面的用户查询和角色查询

1
2
3
4
5
6
7
8
if (userName != null) {
               sql.append ("and u.userName like ? ");
               list.add ("%" + userName + "%");
           }
           if (roleID > 0) {
               sql.append ("and r.id= ?");
               list.add (roleID);
           }

userservice

1
2
3
4
5
6
7
8
9
10
11
@Override
    public List<User> getUserList (String userName, int userRole, int currentPage, int pageSize) {
        List<User> userList = new ArrayList<> ();
        Connection connection = BaseDao.getConnection ();
        if (connection != null) {
            userList = userDao.getUserList (connection, userName, userRole, currentPage, pageSize);

            BaseDao.release (connection, null, null);
        }
        return userList;
    }
查询数量：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
@Override
    public int UserNum (Connection connection, String userName, int roleID) {
        StringBuffer sql = new StringBuffer ("select count(1) as count from smbms_user u ,smbms_role r  where u.userRole=r.id ");
        ResultSet resultSet = null;
        ArrayList<Object> list = new ArrayList<> ();
        int execute = 0;
        if (connection != null) {
            if (userName != null) {
                sql.append ("and u.userCode like ? ");
                list.add ("%" + userName + "%");
            }
            if (roleID > 0) {
                sql.append ("and u.userRole= ?");
                list.add (roleID);
            }

            try {
                resultSet = BaseDao.executeQuery (connection, sql.toString (), list.toArray ());
                if (resultSet != null) {
                    while (resultSet.next ()) {
                        execute = resultSet.getInt ("count");
                    }
                }
            } catch (Exception throwables) {
                throwables.printStackTrace ();
            } finally {
                BaseDao.release (null, null, resultSet);
            }
        }
        return execute;
    }

联表查询；

1
2
3
4
5
6
7
8
9
10
@Override
public int getCount (String userName, int userRole) {
    Connection connection = BaseDao.getConnection ();
    int i = 0;
    if (connection != null) {
        i = userDao.UserNum (connection, userName, userRole);
        BaseDao.release (connection, null, null);
    }
    return i;
}
查询用户的角色的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@Override
   public List<Role> getRoleList (Connection connection){
       List<Role> list = new ArrayList<> ();
       String sql = "select * from smbms_role r;";
       if (connection != null) {
           ResultSet resultSet = BaseDao.executeQuery (connection, sql, null);
           if (resultSet != null) {
               try {
                   while (resultSet.next ()) {
                       Role _role = new Role ();
                       _role.setID (resultSet.getInt ("id"));
                       _role.setRoleCode (resultSet.getString ("roleCode"));
                       _role.setRoleName (resultSet.getString ("roleName"));
                       list.add (_role);
                   }
               } catch (SQLException throwables) {
                   throwables.printStackTrace ();
               }
           }
       }
       return list;
   }

roleService

1
2
3
4
5
6
7
8
9
10
@Override
   public List<Role> getRoleList () {
       List<Role> roleList = new ArrayList<> ();
       Connection connection = BaseDao.getConnection ();
       if (connection != null) {
           roleList = role.getRoleList (connection);
       }
       BaseDao.release (connection,null,null);
       return roleList;
   }

这查询的是为了使用用户角色来查询我们的用户

接下来就是把这三条线合在一起组成一条线；

servlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
//分页查询界面
    public void query (HttpServletRequest req, HttpServletResponse resp) {
        String queryname = req.getParameter ("queryname");
        String checkRole = req.getParameter ("queryUserRole");
        String index = req.getParameter ("pageIndex");
        int queryUserRole = 0;
        //确定查询的框是不是为空
        if (queryname == null) {
            queryname = "";
        }
        //判断角色框是否是空，在这个框里，传递出来的是value=0;
        /**
         * <c:if test="${roleList != null }">
         * 	<option value="0">--请选择--</option>
         */
        if (checkRole != null && ! checkRole.equals ("")) {
            queryUserRole = Integer.parseInt (checkRole);
        }
        //分页
        int currentPage = 1;
        int pageSize = 5;
        int totalCount = 0;

        UserServiceImpl userService = new UserServiceImpl ();
        //获得用户总数
        totalCount = userService.getCount (queryname, queryUserRole);
        if (index != null) {
            currentPage = Integer.parseInt (index);
        }

        //设置页面
        PageSupport pageSupport = new PageSupport ();
        pageSupport.setPageSize (pageSize);
        pageSupport.setTotalPageCount (totalCount);
        pageSupport.setCurrentPageNo (currentPage);
        pageSupport.setTotalCount (totalCount);
        int currentPageNo = pageSupport.getCurrentPageNo ();
        //控制第一页和最后一页
        if (currentPageNo <= 0) {
            currentPageNo = 1;
        } else if (currentPageNo > totalCount) {
            currentPageNo = totalCount;
        }
        //获得用户列表显示
        List<User> userList = userService.getUserList (queryname, queryUserRole, currentPage, pageSize);

        req.setAttribute ("userList", userList);
        //角色列表  用户查询的显示rolelist
        roleServiceImpl role = new roleServiceImpl ();
        List<Role> roleList = role.getRoleList ();
        req.setAttribute ("roleList", roleList);
        //显示页数
        req.setAttribute ("totalCount", totalCount);
        req.setAttribute ("currentPageNo", currentPageNo);//当前页数
        req.setAttribute ("totalPageCount",pageSupport.getTotalPageCount ());//查询的总页数
        req.setAttribute ("queryUserName",queryname);//名字查询
        req.setAttribute ("userList",userList);//用户列表
        req.setAttribute ("queryUserRole",roleList);//用户角色的列表

        try {
            req.getRequestDispatcher ("/JSP/userlist.jsp").forward (req, resp);
        } catch (Exception e) {
            e.printStackTrace ();
        }
    }

jsp:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
<%@ page import="java.util.List" %>
<%@ page import="com.saxon.pojo.Role" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
		 pageEncoding="UTF-8"%>
<%@include file="/JSP/common/head.jsp"%>
        <div class="right">
            <div class="location">
                <strong>你现在所在的位置是:</strong>
                <span>用户管理页面</span>
            </div>
            <div class="search">
           		<form method="get" action="${pageContext.request.contextPath }/jsp/user.do">
					<input name="method" value="query" class="input-text" type="hidden">
					 <span>用户名：</span>
					 <input name="queryname" class="input-text"	type="text" value="${queryUserName}">

					<span>用户角色：</span>
					<select name="queryUserRole">
						<c:if test="${roleList!= null }">
							<option value="0">--请选择--</option>
						</c:if>
					</select>
					<input type="hidden" name="pageIndex" value="1"/>
					 <input	value="查 询" type="submit" id="searchbutton">
<%--					 <a href="${pageContext.request.contextPath}/jsp/useradd.jsp" >添加用户</a>--%>
				</form>
            </div>
            <!--用户-->
            <table class="providerTable" cellpadding="0" cellspacing="0">
                <tr class="firstTr">
                    <th width="10%">用户编码</th>
                    <th width="20%">用户名称</th>
                    <th width="10%">性别</th>
                    <th width="10%">年龄</th>
                    <th width="10%">电话</th>
                    <th width="10%">用户角色</th>
                    <th width="30%">操作</th>
                </tr>
                   <c:forEach var="user" items="${userList }" varStatus="status">
					<tr>
						<td>
						<span>${user.userCode }</span>
						</td>
						<td>
						<span>${user.userName }</span>
						</td>
						<td>
							<span>
								<c:if test="${user.gender==1}">男</c:if>
								<c:if test="${user.gender==2}">女</c:if>
							</span>
						</td>
						<td>
						<span>${user.birthday}</span>
						</td>
						<td>
						<span>${user.phone}</span>
						</td>
						<td>
							<span>${user.userRole}</span>
						</td>
						<td>
						<span><a class="viewUser" href="javascript:;" userid=${user.ID } username=${user.userName }><img src="${pageContext.request.contextPath }/images/read.png" alt="查看" title="查看"/></a></span>
						<span><a class="modifyUser" href="javascript:;" userid=${user.ID } username=${user.userName }><img src="${pageContext.request.contextPath }/images/xiugai.png" alt="修改" title="修改"/></a></span>
						<span><a class="deleteUser" href="javascript:;" userid=${user.ID } username=${user.userName }><img src="${pageContext.request.contextPath }/images/schu.png" alt="删除" title="删除"/></a></span>
						</td>
					</tr>
				</c:forEach>
			</table>
			<input type="hidden" id="totalPageCount" value="${totalPageCount}"/>
		  	<c:import url="/JSP/rollpage.jsp">
	          	<c:param name="totalCount" value="${totalCount}"/>
	          	<c:param name="currentPageNo" value="${currentPageNo}"/>
	          	<c:param name="totalPageCount" value="${totalPageCount}"/>
          	</c:import>
        </div>
    </section>

<!--点击删除按钮后弹出的页面-->
<div class="zhezhao"></div>
<div class="remove" id="removeUse">
    <div class="removerChid">
        <h2>提示</h2>
        <div class="removeMain">
            <p>你确定要删除该用户吗？</p>
            <a href="#" id="yes">确定</a>
            <a href="#" id="no">取消</a>
        </div>
    </div>
</div>

<%@include file="/JSP/common/foot.jsp" %>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/userlist.js"></script>

##