JavaWeb学习Day05（狂神说Java）

SMBMS（超市销售管理系统）

数据库：

项目如何搭建：

考虑使用不使用maven？依赖，jar

项目搭建准备工作

1. 搭建一个maven项目
2. 配置Tomcat
3. 测试项目是否能够跑起来
4. 导入项目中会遇到的jar包：jsp，Servlet，mysql驱动，jstl，
5. 创建项目包结构

6. 编写实体类

   ORM映射，表-类映射

7. 编写基础公共类

   1.数据库配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf-8
username=root
password=newpassword

​ 2.编写数据库的公共类

package com.kuang.dao;

import java.io.IOException;
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 {
        Properties properties = new Properties();

        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");

        try {
            properties.load(is);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            userName = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static ResultSet execute(Connection con, PreparedStatement ps, ResultSet rs, String sql, Object[] params) throws SQLException {
        ps = con.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject 占位符下标从1开始，我们数组是从0开始
            ps.setObject(i + 1, params[i]);
        }
        rs = ps.executeQuery();
        return rs;
    }

    public static int execute(Connection con, PreparedStatement ps, String sql, Object[] params) throws SQLException {
        ps = con.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            //setObject 占位符下标从1开始，我们数组是从0开始
            ps.setObject(i + 1, params[i]);
        }
        return ps.executeUpdate();
    }

    public static boolean closeResource(Connection con, PreparedStatement ps, ResultSet rs) {
        boolean closeFlag = true;
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        if (ps != null) {
            try {
                ps.close();
                ps = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        if (con != null) {
            try {
                con.close();
                con = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        return closeFlag;
    }

    public static boolean closeResource(Connection con) {
        boolean closeFlag = true;
        if (con != null) {
            try {
                con.close();
                con = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        return closeFlag;
    }

    public static boolean closeResource(PreparedStatement ps, ResultSet rs) {
        boolean closeFlag = true;
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        if (ps != null) {
            try {
                ps.close();
                ps = null;
            } catch (SQLException e) {
                e.printStackTrace();
                closeFlag = false;
            }
        }
        return closeFlag;
    }

}

​ 3.编写字符编码过滤器

8. 导入静态资源

登陆功能实现

1. 编写前端页面
2. 设置首页

<!--    设置欢迎界面-->
    <welcome-file-list>
        <welcome-file>/login.jsp</welcome-file>
    </welcome-file-list>

3. 编写dao层登陆用户登录的接口

public User getLoginUser(Connection connection, String userCode) throws SQLException ;

4. 编写Dao接口的实现类

package com.kuang.dao.user;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.User;

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

public class UserDaoImpel implements UserDao{
    public User getLoginUser(Connection connection, String userCode)throws SQLException{
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;

        if(connection!=null){
            String sql="select * from smbms_user where usercode=?";
            Object[] params={userCode};
                rs=BaseDao.execute(connection,pstm,rs,sql,params);
                if(rs.next()){
                    user =new User();
                    user.setId(rs.getInt("id"));;
                    user.setUserCode(rs.getString("userCode"));;
                    user.setUserName(rs.getString("username"));;
                    user.setUserPassword(rs.getString("userPassword"));;
                    user.setGender(rs.getInt("gender"));;
                    user.setBirthday(rs.getDate("birthday"));;
                    user.setPhone(rs.getString("phone"));;
                    user.setAddress(rs.getString("address"));;
                    user.setUserRole(rs.getInt("userRole"));;
                    user.setCreatedBy(rs.getInt("createdBy"));;
                    user.setCreationDate(rs.getTimestamp("creationDate"));;
                    user.setModifyBy(rs.getInt("modifyBy"));;
                    user.setModifyDate(rs.getTimestamp("modifyDate"));;
                }
                BaseDao.closeResource(null, rs, pstm);

        }
        return user;
    }
}

5. 编写业务层接口以及业务层实现类

package com.kuang.service.user;

import com.kuang.pojo.User;

public interface UserService {
    //用户登录
    public User login(String userCode,String password);
}

6. 业务层实现类

package com.kuang.service.user;

import com.kuang.dao.BaseDao;
import com.kuang.dao.user.UserDao;
import com.kuang.dao.user.UserDaoImpel;

import com.kuang.pojo.User;
import org.junit.Test;

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

public class UserServiceImpl implements UserService{
    //业务层都会调用dao层，所以我们要引入dao层
    private UserDao userDao;
    public UserServiceImpl(){
        userDao=new UserDaoImpel();
    }
    public User login(String userCode, String password){
        Connection connection=null;
        User user=null;

        try {
            connection= BaseDao.getConnection();
            //用过业务层调用对应的具体的数据库操作
            user=userDao.getLoginUser(connection,userCode);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return user;
    }
    
}

7. 编写Servlet

package com.kuang.servlet.user;

import com.kuang.pojo.User;
import com.kuang.service.user.UserService;
import com.kuang.service.user.UserServiceImpl;
import com.kuang.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 {
    //Servlet：控制层，调用业务层代码

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("LoginServlet start");
        //获取用户名和密码
        String userCode=req.getParameter("userCode");
        String userPassword=req.getParameter("userpassword");
        //和数据库中的密码进行对比，调用业务层
        UserService userService=new UserServiceImpl();
        User user=userService.login(userCode,userPassword);//这里已经把登陆的人给查出来了
        if (user!=null){//查有此人，可以登录
            //将用户的信息放到Session中
            req.getSession().setAttribute(Constants.USER_SESSION,user);
            //跳转到主页
            resp.sendRedirect("jsp/frame.jsp");
        }else{//查无此人，无法登录
            //转发回登陆页面，顺带提示他，用户名或者密码错误；
            req.setAttribute("error","用户名或者密码错误");
            req.getRequestDispatcher("login.jsp").forward(req,resp);

        }


    }

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

8. 注册Servlet

<!--    Servlet-->
    <servlet>
        <servlet-name>LoginServlet</servlet-name>
        <servlet-class>com.kuang.servlet.user.LoginServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LoginServlet</servlet-name>
        <url-pattern>/login.do</url-pattern>
    </servlet-mapping>

9.测试访问，要确保以上功能成功

登录功能优化

注销功能：

思路：移除Session，返回登陆页面

package com.kuang.servlet.user;

import com.kuang.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 LogoutServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //移除用户的Constants。USER_SESSION
        req.getSession().removeAttribute(Constants.USER_SESSION);
        resp.sendRedirect(req.getContextPath()+"/login.jsp");//返回登陆页面
    }

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

注册xml

<!--    注销-->
    <servlet>
        <servlet-name>LogoutServlet</servlet-name>
        <servlet-class>com.kuang.servlet.user.LogoutServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LogoutServlet</servlet-name>
        <url-pattern>/jsp/logout.do</url-pattern>
    </servlet-mapping>

登录拦截优化

编写过滤器,并注册

package com.kuang.filter;

import com.kuang.pojo.User;
import com.kuang.util.Constants;

import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

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

    }

    @Override
    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws IOException, ServletException {
        HttpServletRequest request=(HttpServletRequest) req;
        HttpServletResponse response=(HttpServletResponse) resp;
        //过滤器，从session中获取用户
        User user=(User) request.getSession().getAttribute(Constants.USER_SESSION);
        if(user==null){//已经被移除或者注销了，或者未登录
            response.sendRedirect("/smbms/error.jsp");

        }else{
            chain.doFilter(req,resp);
        }
    }

    @Override
    public void destroy() {

    }
}

<!--    用户登录过滤器-->
    <filter>
        <filter-name>SysFilter</filter-name>
        <filter-class>com.kuang.filter.SysFilter</filter-class>
        
    </filter>
    <filter-mapping>
        <filter-name>SysFilter</filter-name>
        <url-pattern>/jsp/*</url-pattern>
    </filter-mapping>

测试，登录，注销，权限

密码修改

1. 导入前端素材

<li><a href="${pageContext.request.contextPath}/jsp/pwdmodify.jsp">密码修改</a></li>

2. 写项目，建议从底层开始向上写

3. 写UserDao接口
4. UserDao接口实现类

package com.kuang.dao.user;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.User;

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

import static com.kuang.dao.BaseDao.execute;

public class UserDaoImpel implements UserDao{
    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException{
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;

        if(connection!=null){
            String sql="select * from smbms_user where userCode=?;";
            Object[] params={userCode};
//            execute(Connection con, PreparedStatement ps, ResultSet rs, String sql, Object[] params)
            rs= execute(connection,pstm,rs,sql,params);
                if(rs.next()){
                    user =new User();
                    user.setId(rs.getInt("id"));;
                    user.setUserCode(rs.getString("userCode"));;
                    user.setUserName(rs.getString("username"));;
                    user.setUserPassword(rs.getString("userPassword"));;
                    user.setGender(rs.getInt("gender"));;
                    user.setBirthday(rs.getDate("birthday"));;
                    user.setPhone(rs.getString("phone"));;
                    user.setAddress(rs.getString("address"));;
                    user.setUserRole(rs.getInt("userRole"));;
                    user.setCreatedBy(rs.getInt("createdBy"));;
                    user.setCreationDate(rs.getTimestamp("creationDate"));;
                    user.setModifyBy(rs.getInt("modifyBy"));;
                    user.setModifyDate(rs.getTimestamp("modifyDate"));;
                }
                BaseDao.closeResource(null, pstm, rs);

        }
        return user;
    }
    //修改当前用户密码
    public int updatePwd(Connection connection, int id, int password) throws SQLException {
        PreparedStatement pstm=null;
        int execute=0;
        if (connection!=null){
            String sql="update smbms_user set userPassword= ? where id=  ?";
            Object params[]={password,id};
            execute=BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return execute;

    }
}

5. UserService层

package com.kuang.service.user;

import com.kuang.pojo.User;

public interface UserService {
    //用户登录
    public User login(String userCode,String password);
    //根据用户id修改密码
    public  boolean updatePwd(int id,int pwd);
}

6. UserService实现类

package com.kuang.service.user;

import com.kuang.dao.BaseDao;
import com.kuang.dao.user.UserDao;
import com.kuang.dao.user.UserDaoImpel;

import com.kuang.pojo.User;
import org.junit.Test;

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

public class UserServiceImpl implements UserService{
    //业务层都会调用dao层，所以我们要引入dao层
    private UserDao userDao;
    public UserServiceImpl(){
        userDao=new UserDaoImpel();
    }

    public User login(String userCode, String password){
        Connection connection=null;
        User user=null;

        try {
            connection= BaseDao.getConnection();
            //用过业务层调用对应的具体的数据库操作
            user=userDao.getLoginUser(connection,userCode);
            if(!password.equals(user.getUserPassword())){
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection);
        }
        return user;
    }

    public boolean updatePwd(int id, int pwd) {
        Connection connection=null;
        boolean flag=false;

        //修改密码
        try {
            connection=BaseDao.getConnection();
            if (userDao.updatePwd(connection, id, pwd) > 0) {
                flag=true;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }
}

7. 记得实现复用，需要提出取方法

package com.kuang.servlet.user;

import com.kuang.pojo.User;
import com.kuang.service.user.UserService;
import com.kuang.service.user.UserServiceImpl;
import com.kuang.util.Constants;
import com.mysql.jdbc.StringUtils;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
//实现Servlet复用
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method=req.getParameter("method");
        if(method.equals("savepwd")&&method!=null){
            this.updatePwd(req,resp);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //从session里面拿ID
        Object o=req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword=req.getParameter("newpassword");
        System.out.println("UserServlet"+newpassword);
        boolean flag=false;
        if(o!=null&& !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService=new UserServiceImpl();
            flag=userService.updatePwd(((User)o).getId(),newpassword);
            if(flag){
                req.setAttribute("message","修改密码成功，请退出，使用新密码登录");
                //密码修改成功，移除当前session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else{
                req.setAttribute("message","修改密码失败");
                //密码修改失败
            }
        }else{
            req.setAttribute("message","新密码有问题");
        }
        req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);;
    }
}

8. 测试

优化密码修改使用Ajax：

1. 阿里巴巴的fastjson

<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>2.0.0</version>
</dependency>

2. 后台代码修改

package com.kuang.servlet.user;

import com.alibaba.fastjson.JSONArray;
import com.kuang.pojo.User;
import com.kuang.service.user.UserService;
import com.kuang.service.user.UserServiceImpl;
import com.kuang.util.Constants;
import com.mysql.jdbc.StringUtils;

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

//实现Servlet复用
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method=req.getParameter("method");
        if(method.equals("savepwd")&&method!=null){
            this.updatePwd(req,resp);
        }else if(method.equals("pwdmodify")&&method!=null)
            this.pwdModify(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
    //修改密码
    public void updatePwd(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //从session里面拿ID
        Object o=req.getSession().getAttribute(Constants.USER_SESSION);
        String newpassword=req.getParameter("newpassword");
        System.out.println("UserServlet"+newpassword);
        boolean flag=false;
        if(o!=null&& !StringUtils.isNullOrEmpty(newpassword)){
            UserService userService=new UserServiceImpl();
            flag=userService.updatePwd(((User)o).getId(),newpassword);
            if(flag){
                req.setAttribute("message","修改密码成功，请退出，使用新密码登录");
                //密码修改成功，移除当前session
                req.getSession().removeAttribute(Constants.USER_SESSION);
            }else{
                req.setAttribute("message","修改密码失败");
                //密码修改失败
            }
        }else{
            req.setAttribute("message","新密码有问题");
        }
        req.getRequestDispatcher("pwdmodify.jsp").forward(req,resp);;
    }
    //验证旧密码,session中有用户的密码
    public void pwdModify(HttpServletRequest req,HttpServletResponse resp){
        Object o=req.getSession().getAttribute(Constants.USER_SESSION);
        String oldpassword=req.getParameter("oldpassword");
        //万能的map
        HashMap<String,String> resultMap=new HashMap<String,String>();

        if(o==null){//session失效，session过期了
            resultMap.put("result","sessionerror");
        }else if(StringUtils.isNullOrEmpty(oldpassword)){//输入的密码为空
            resultMap.put("result","error");

        }else {
            String usePassword=((User)o).getUserPassword();//session中用户密码
            if (oldpassword.equals(usePassword)){
                resultMap.put("result","true");
            }else{
                resultMap.put("result","false");
            }
        }
        try{
            resp.setContentType("application/json");
            PrintWriter writer=resp.getWriter();
            //JSONArray阿里巴巴的JSON工具类，转化格式
//
            writer.write(JSONArray.toJSONString(resultMap));
            writer.flush();
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

3. 测试

用户管理实现

思路：

1. 导入分页的工具类
2. 用户列表页面导入

1.获取用户数量

1. UserDao

package com.kuang.dao.user;

import com.kuang.pojo.User;

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

public interface UserDao {
    //得到登录用户
    public User getLoginUser(Connection connection, String userCode) throws SQLException;
    //修改当前用户
    public int updatePwd(Connection connection,int id,String password) throws  SQLException;
    //查询用户总数
    public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
}

2. UserDaoImpl

package com.kuang.dao.user;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.User;
import com.mysql.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import static com.kuang.dao.BaseDao.closeResource;
import static com.kuang.dao.BaseDao.execute;

public class UserDaoImpel implements UserDao{

    public User getLoginUser(Connection connection, String userCode) throws SQLException{
        PreparedStatement pstm=null;
        ResultSet rs=null;
        User user=null;

        if(connection!=null){
            String sql="select * from smbms_user where userCode=?;";
            Object[] params={userCode};
//            execute(Connection con, PreparedStatement ps, ResultSet rs, String sql, Object[] params)
            rs= execute(connection,pstm,rs,sql,params);
                if(rs.next()){
                    user =new User();
                    user.setId(rs.getInt("id"));;
                    user.setUserCode(rs.getString("userCode"));;
                    user.setUserName(rs.getString("username"));;
                    user.setUserPassword(rs.getString("userPassword"));;
                    user.setGender(rs.getInt("gender"));;
                    user.setBirthday(rs.getDate("birthday"));;
                    user.setPhone(rs.getString("phone"));;
                    user.setAddress(rs.getString("address"));;
                    user.setUserRole(rs.getInt("userRole"));;
                    user.setCreatedBy(rs.getInt("createdBy"));;
                    user.setCreationDate(rs.getTimestamp("creationDate"));;
                    user.setModifyBy(rs.getInt("modifyBy"));;
                    user.setModifyDate(rs.getTimestamp("modifyDate"));;
                }
                BaseDao.closeResource(null, pstm, rs);

        }
        return user;
    }
    //修改当前用户密码
    public int updatePwd(Connection connection, int id, String password) throws SQLException {
        System.out.println("UserServlet"+password);
        PreparedStatement pstm=null;
        int execute=0;
        if (connection!=null){
            String sql="update smbms_user set userPassword= ? where id=  ?";
            Object params[]={password,id};
            execute=BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,null);
        }
        return execute;

    }
//根据用户名或者角色查询用户总数
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet rs=null;
        int count=0;
        if(connection!=null){
            StringBuffer sql=new StringBuffer();
            sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole=r.id");
            ArrayList<Object> list=new ArrayList<Object>();//存放参数
            if(!StringUtils.isNullOrEmpty(username)){
                sql.append(" and u.userName like ?");
                list.add("%"+username+"%");//默认下标为0
            }
            if(userRole>0){
                sql.append(" and u.userRole=?");
                list.add(userRole);
            }
            //怎么将list转换为数组
            Object[] params=list.toArray();
            System.out.println("UserDapImpl->getUserCount:"+sql.toString());//输出最后完整的SQL语句
            rs=BaseDao.execute(connection,pstm,rs,sql.toString(),params);
            if(rs.next()){
                count=rs.getInt("count");//从结果集中获取最终的数量
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }


}

3. UserService

package com.kuang.service.user;

import com.kuang.pojo.User;

public interface UserService {
    //用户登录
    public User login(String userCode,String password);
    //根据用户id修改密码
    public  boolean updatePwd(int id,String pwd);
    //查询记录数
    public int getUserCount(String username,int userRole);
}

4. UserServiceImpl

package com.kuang.service.user;

import com.kuang.dao.BaseDao;
import com.kuang.dao.user.UserDao;
import com.kuang.dao.user.UserDaoImpel;

import com.kuang.pojo.User;
import org.junit.Test;

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

public class UserServiceImpl implements UserService{
    //业务层都会调用dao层，所以我们要引入dao层
    private UserDao userDao;
    public UserServiceImpl(){
        userDao=new UserDaoImpel();
    }

    public User login(String userCode, String password){
        Connection connection=null;
        User user=null;

        try {
            connection= BaseDao.getConnection();
            //用过业务层调用对应的具体的数据库操作
            user=userDao.getLoginUser(connection,userCode);
            if(!password.equals(user.getUserPassword())){
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return user;
    }

    public boolean updatePwd(int id, String pwd) {
        System.out.println("UserServlet"+pwd);
        Connection connection=null;
        boolean flag=false;

        //修改密码
        try {
            connection=BaseDao.getConnection();
            if (userDao.updatePwd(connection, id, pwd) > 0) {
                flag=true;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return flag;
    }
//查询记录数
    public int getUserCount(String username, int userRole) {
        Connection connection=null;
        int count=0;
        try{
            connection=BaseDao.getConnection();
            count=userDao.getUserCount(connection,username,userRole);
        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            BaseDao.closeResource(connection,null,null);
        }
        return count;
    }


}

2.获取用户列表

1. userDao

package com.kuang.dao.user;

import com.kuang.pojo.User;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public interface UserDao {
    //得到登录用户
    public User getLoginUser(Connection connection, String userCode) throws SQLException;
    //修改当前用户
    public int updatePwd(Connection connection,int id,String password) throws  SQLException;
    //查询用户总数
    public int getUserCount(Connection connection,String username,int userRole) throws SQLException;
    //通过条件查询-userlist
    public List<User> getUserList(Connection connection,String userName,int userRole,int currentPageNo,int pageSize) throws Exception;
}

2. userdaoImpl

public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws Exception {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        if (connection != null) {
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*,r.roleName as `userRoleName` from smbms_user u,smbms_role r where u.userRole=r.id");
            List<Object> list = new ArrayList<Object>();
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }
            if (userRole > 0) {
                sql.append(" and r.id = ?");
                list.add(userRole);
            }
            //mysql 分页使用limit startIndex, pageSize
            //比如现在一共13条数据，每页最大容量是5
            //0,5 01234 第一页
            //5,5 56789 第二页
            //10,3 10,11,12 第三页
            sql.append(" order by u.creationDate desc limit ?,?");
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            System.out.println("sql--->"+sql.toString());
            rs = BaseDao.execute(connection, ps, rs, sql.toString(), params);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setGender(rs.getInt("gender"));
                user.setBirthday(rs.getDate("birthday"));
                user.setPhone(rs.getString("phone"));
                user.setUserRole(rs.getInt("userRole"));
                user.setUserRoleName(rs.getString("userRoleName"));
                userList.add(user);
            }
            BaseDao.closeResource(ps, rs);

        }
        return userList;

    }

3. UserService

package com.kuang.service.user;

import com.kuang.pojo.User;

import java.sql.Connection;
import java.util.List;

public interface UserService {
    //用户登录
    public User login(String userCode,String password);
    //根据用户id修改密码
    public  boolean updatePwd(int id,String pwd);
    //查询记录数
    public int getUserCount(String username,int userRole);
    //根据
    public List<User> getUserList(String queryUserName,int queryUserRole,int currentPageNo,int pageSize);
}

4. UserServiceImpl

public List<User> getUserList(String queryUserName, int queryUserRole, int currentPageNo, int pageSize) {
        Connection connection = null;
        List<User> userList = null;
        System.out.println("queryUserName--->"+queryUserName);
        System.out.println("queryUserRole--->"+queryUserRole);
        System.out.println("currentPageNo--->"+currentPageNo);
        try {
            connection= BaseDao.getConnection();
            userList = userDao.getUserList(connection, queryUserName, queryUserRole, currentPageNo, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeResource(connection,null,null);
        }
        return userList;

    }

3.获取角色操作

为了我们职责统一，可以把角色的操作单独放在一个包中，和pojo类对应

RoleDao

package com.kuang.dao.role;

import com.kuang.pojo.Role;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public interface RoleDao {
    //获取角色列表
    public List<Role> getRoleList(Connection connection) throws SQLException;
}

RoleDaoImpl

package com.kuang.dao.role;

import com.kuang.dao.BaseDao;
import com.kuang.pojo.Role;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoleDaoImpl implements RoleDao{
    //获取角色列表
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm=null;
        ResultSet resultSet=null;
        ArrayList<Role> roleList=new ArrayList<Role>();
        if(connection!=null){
            String sql="select * from smbms_role";
            Object[] params={};
            resultSet=BaseDao.execute(connection,pstm,resultSet,sql,params);
            while(resultSet.next()){
                Role _role=new Role();
                _role.setId(resultSet.getInt("id"));
                _role.setRoleCode(resultSet.getString("roleCode"));
                _role.setRoleName(resultSet.getString("roleName"));
                roleList.add(_role);
            }
            BaseDao.closeResource(null,pstm,resultSet);
        }
        return roleList;
    }
}

RoleService

package com.kuang.service.role;

import com.kuang.pojo.Role;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public interface RoleService {
    //获取角色列表
    public List<Role> getRoleList();
}

RoleServiceImpl

package com.kuang.service.role;


import com.kuang.dao.BaseDao;
import com.kuang.dao.role.RoleDao;
import com.kuang.dao.role.RoleDaoImpl;
import com.kuang.pojo.Role;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class RoleServiceImpl implements RoleService{
    //引入Dao
    private RoleDao roleDao;
    public RoleServiceImpl() {
        roleDao = new RoleDaoImpl();
    }

    public List<Role> getRoleList(){
        Connection connection=null;
        List<Role> roleList=null;
        try{
            connection= BaseDao.getConnection();
            roleList=roleDao.getRoleList(connection);

        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            BaseDao.closeResource(connection,null,null);
        }
        return roleList;
    }
}

4.用户显示的Servlet

1. 获取用户前端的数据（查询）
2. 判断请求是否执行需要执行，看参数的值判断
3. 为了实现分页，需要计算出当前页面和总页面，页面大小
4. 用户列表展示
5. 返回前端