用Jdbc、Servlet和jsp做一个登录

用Jdbc、Servlet和jsp做一个登录

简单介绍：在mysql里新建一个user表：表结构如下

先新建好各个包：实体模型包（类文件）：po  →  数据库访问对象包（接口文件）：dao、 →  业务逻辑包（接口文件）：service、 →  控制层包（servlet文件）：servlet、→  过滤器（servlet文件）：filter →  前端页面显示层：jsp（jsp建立在WebRoot下面）

以此延伸出来的包有：util包（封装的工具类）、dao.impl包（类文件）、service.impl包（类文件）、test类包（用于测试逻辑业务层的方法）

最好先建立好几个基础包，写入内容后再建立实现类包，不然实现类包会将原来的包覆盖掉。

 

在实现数据库访问对象之前必须先进行数据库的链接：往WebRoot的Web-INF的lib文件中放入需要用到的jar驱动文件，没有的可以到网上下载；我用的是mysql驱动，

https://jingyan.baidu.com/article/22fe7ced29711e3002617f2c.html

这个路径里有教你如何到mySQL官网里下载驱动的方法，很详细；

如果是用的SqlServer的话，我的另外一篇博文里有sqlserver的下载路径；

为了减少耦合性，数据库采用属性文件的方式来连接数据库,数据库属性文件jdbc.properties

jdbc.properties内容如下：

url中的3306是mysql的端口号，SqlServer的端口号：1433 Oracle的端口号：1521

连接数据库可以看我的另外一篇博文，里面有详细步骤，链接如下：

https://blog.****.net/weixin_42271651/article/details/90181918

 

实际操作步骤简介：

1、在po包中新建实体类User；

2、在dao包中新建数据库访问接口UserDao；

3、在dao.impl包新建UserDaoImpl实现类，实现UserDao接口；

4、在service包中新建业务逻辑接口UserService；

5、在service.impl包中新建UserServiceImpl实现类，实现UserService接口；

6、在servlet包中新建UserServlet；

7、在filter包中新建Fiter过滤器，继承Javax.servlet.Fiter接口；

8、在util包中新建DBUtil工具类；

9、在jsp文件中新建login.jsp的JSP文件

10、在jsp文件中新建main.jsp就JSP文件

文件展示图：

操作步骤详解：

1. 在po包中新建实体类User；

package com.xmjlit.po;

public class User {

    private Integer id;

    private String username;

    private String password;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public String getPassword() {

        return password;

    }

    public void setPassword(String password) {

        this.password = password;

    }

}

1. 在dao包中新建数据库访问接口UserDao；

package com.xmjlit.dao;

import java.util.List;

import com.xmjlit.po.User;

public interface UserDao {

    public void add(User u);

    public void delete(int id);

    public void update(User u);

    public User selectAll(int id);

    public List<User> getAllUser();

}

1. 在dao.impl包新建UserDaoImpl实现类，实现UserDao接口；

package com.xmjlit.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.xmjlit.dao.UserDao;

import com.xmjlit.po.User;

import com.xmjlit.util.DBUtil;

public class UserDaoImpl implements UserDao {

    private Connection con = null;// 引用的是java.sql类型，不要引错了

    private PreparedStatement ps = null;

    private ResultSet rs = null;

    @Override

    public void add(User u) {

        String sql = "insert into user(username,password) values(?,?)";// sql语句

        con = DBUtil.getConnection();// 调用加载驱动的方法

        try {

            ps = con.prepareStatement(sql);// 调用Connection中的prepareStatement方法来执行sql语句

            ps.setString(1, u.getUsername());

            ps.setString(2, u.getPassword());

            ps.executeUpdate();

            System.out.println(u.getUsername() + "新增成功");

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {// 释放外部资源的代码写在finally块中

            DBUtil.close(con, ps, rs);

        }

    }

    @Override

    public void delete(int id) {

        String sql = "delete from user where id=?";

        con = DBUtil.getConnection();

        try {

            ps = con.prepareStatement(sql);

            ps.setInt(1, id);

            ps.executeUpdate();

            System.out.println("删除成功");

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {

            DBUtil.close(con, ps, rs);

        }

    }

    @Override

    public void update(User u) {

        String sql = "update user set username=? where id=? ";

        con = DBUtil.getConnection();

        try {

            ps = con.prepareStatement(sql);

            ps.setString(1, u.getUsername());

            ps.setInt(2, u.getId());

            ps.executeUpdate();

            System.out.println("修改成功");

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } finally {

            DBUtil.close(con, ps, rs);

        }

    }

    @Override

    public User selectAll(int id) {

        String sql = "select * from user where id=?";

        User u = new User();

        con = DBUtil.getConnection();

        try {

            ps = con.prepareStatement(sql);

            ps.setInt(1, id);

            rs = ps.executeQuery();

            while (rs.next()) {

                u.setId(rs.getInt("id"));

                u.setUsername(rs.getString("username"));

                u.setPassword(rs.getString("password"));

            }

            System.out.println("查询成功" + "id=" + u.getId() + " 用户名："

                    + u.getUsername() + " 密码：" + u.getPassword());

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return u;

    }

    @Override

    public List<User> getAllUser() {

        List<User> list = new ArrayList<User>();

        String sql = "select * from user";

        con = DBUtil.getConnection();

        try {

            ps = con.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next()) {

                User u = new User();

                u.setId(rs.getInt("id"));

                u.setUsername(rs.getString("username"));

                u.setPassword(rs.getString("password"));

                list.add(u);

                System.out.println("查询成功" + "id=" + u.getId() + " 用户名："

                        + u.getUsername() + " 密码：" + u.getPassword());

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return list;

    }

}

1. 在service包中新建业务逻辑接口UserService；

package com.xmjlit.service;

import java.util.List;

import com.xmjlit.po.User;

public interface UserService {

    public void add(User u);

    public void delete(int id);

    public void update(User u);

    public User selectAll(int id);

    public List<User> getAllUser();

}

5、在service.impl包中新建UserServiceImpl实现类，实现UserService接口；

package com.xmjlit.service.impl;

import java.util.List;

import com.xmjlit.dao.impl.UserDaoImpl;

import com.xmjlit.po.User;

import com.xmjlit.service.UserService;

public class UserServiceImpl implements UserService {

    private UserDaoImpl userDao=new UserDaoImpl();

    @Override

    public void add(User u) {

        userDao.add(u);

    }

    @Override

    public void delete(int id) {

        userDao.delete(id);

    }

    @Override

    public void update(User u) {

        userDao.update(u);

    }

    @Override

    public User selectAll(int id) {

        User u=userDao.selectAll(id);

        return u;

    }

    @Override

    public List<User> getAllUser() {

        List<User> list=userDao.getAllUser();

        return list;

    }

}

6、在servlet包中新建UserServlet；

package com.xmjlit.servlet;

import java.io.IOException;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.xmjlit.dao.impl.UserDaoImpl;

import com.xmjlit.po.User;

public class UserServlet extends HttpServlet {

    public UserDaoImpl ud = new UserDaoImpl();

    public void doGet(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        doGet(request, response);

    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)

            throws ServletException, IOException {

        List<User> list = ud.getAllUser();

        request.setAttribute("users", list);

        request.getRequestDispatcher("/jsp/main.jsp")

                .forward(request, response);

    }

}

7、在filter包中新建Fiter过滤器，继承Javax.servlet.Fiter接口；

package com.xmjlit.filter;

import java.io.IOException;

import javax.servlet.FilterChain;

import javax.servlet.FilterConfig;

import javax.servlet.ServletException;

import javax.servlet.ServletRequest;

import javax.servlet.ServletResponse;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class Filter extends HttpServlet implements javax.servlet.Filter {

    @Override

    public void doFilter(ServletRequest request, ServletResponse response,

            FilterChain chain) throws IOException, ServletException {

        HttpServletRequest req=(HttpServletRequest) request;

        HttpServletResponse resp=(HttpServletResponse) response;

        StringBuffer path=req.getRequestURL();//利用StingBuffer来分割字符串

        System.out.println(path);

        String s=path.substring(path.lastIndexOf("/")+1);

        //lastIndexOf()：返回指定字符在此字符串中最后一次出现处的索引。

        //substring(start index):从指定位置开始截取字符串,默认到末尾。

        System.out.println(s);

        if ("/jsp/login.jsp".equals(s)||"/servlet/UserServlet".equals(s)) {

            chain.doFilter(req, resp);

        }

        Object u=req.getSession().getAttribute("username");

        System.out.println(u);

        if (u==null) {

            resp.sendRedirect("/jsp/login.jsp");

            return;

        }

        chain.doFilter(req, resp);

    }

    @Override

    public void init(FilterConfig arg0) throws ServletException {

        // TODO Auto-generated method stub    

    }

}

8、在util包中新建DBUtil工具类；

DBUtil思路备注：加载驱动类、获取数据库连接、获取PreparedStatement、执行query/update 、关闭con/st/rs

package com.xmjlit.util;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

public class DBUtil {

    // 为加载驱动准备的四个参数

    private static String user = null;

    private static String password = null;

    private static String url = null;

    private static String driver = null;

    static {

        Properties pro = new Properties();

        // 读取文件数据

        InputStream in = DBUtil.class.getClassLoader().getResourceAsStream(

                "jdbc.properties");

        try {

            pro.load(in);// 加载流

            user = pro.getProperty("user");// 获取流里的数据，引号里的user是jdbc.properties文件中的user

            password = pro.getProperty("password");

            url = pro.getProperty("url");

            driver = pro.getProperty("driver");

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public static Connection getConnection() {

        Connection con = null;

        try {

            Class.forName(driver);

            con = DriverManager.getConnection(url, user, password);

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return con;

    }

    public static void close(Connection con, PreparedStatement ps, ResultSet rs) {// 关闭con// ps // rs

        try {

            if (con != null) {

                con.close();

            }

            if (ps != null) {

                ps.close();

            }

            if (rs != null) {

                rs.close();

            }

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

}

 

9、在jsp文件中新建login.jsp的JSP文件

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%

    String path = request.getContextPath();

    String basePath = request.getScheme() + "://"

            + request.getServerName() + ":" + request.getServerPort()

            + path + "/";

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%><!-- 声明c标签 -->

<c:set value="${pageContext.request.contextPath}" scope="page" var="ctx"></c:set>

<html>

<head>

<base href="<%=basePath%>">

<title>My JSP 'login.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">

<meta http-equiv="cache-control" content="no-cache">

<meta http-equiv="expires" content="0">

<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

<meta http-equiv="description" content="This is my page">

<!--

    <link rel="stylesheet" type="text/css" href="styles.css">

    -->

</head>

<body>

    <form action="${ctx}/servlet/UserServlet" method="post">

        <input style="display: none;" name="type" value="login"> 用户名<input

            id="username" name="username" value=""> 密码<input

            id="password" name="password" value=""> <input type="submit"

            value="登录"><input type="reset" value="取消">

        <!-- <button type="submit">登录</button> -->

    </form>

</body>

</html>

10、在jsp文件中新建main.jsp就JSP文件

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<c:set value="${pageContext.request.contextPath}" scope="page" var="ctx"></c:set>

 

<%

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

  <head>

    <base href="<%=basePath%>">   

    <title>My JSP 'main.jsp' starting page</title>   

    <meta http-equiv="pragma" content="no-cache">

    <meta http-equiv="cache-control" content="no-cache">

    <meta http-equiv="expires" content="0">   

    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">

    <meta http-equiv="description" content="This is my page">

    <!--

    <link rel="stylesheet" type="text/css" href="styles.css">

    -->

  </head> 

  <body>

   <table width="90%" border="1" cellpadding="0" cellspacing="0">

        <tr>

            <td width="5%" class="td_title">用户id</td>

            <td width="5%" class="td_title">用户名</td>

            <td width="5%" class="td_title">密码</td>

            <td width="5%" class="td_title">新增</td>

            <td width="5%" class="td_title">修改</td>

            <td width="5%" class="td_title">删除</td>

        </tr>

        <c:forEach items="${users}" varStatus="status" var="user">

            <tr>

                <td width="5%" class="td_title">${status.index+1}</td>

                <td width="5%" class="td_title">${user.username}</td>

                <td width="5%" class="td_title">${user.password}</td>

                <td width="5%" class="td_title"><a

                    href="${ctx}/servlet/ListServlet?type=addJump">新增</a></td>

                <td width="5%" class="td_title"><a

                    href="${ctx}/servlet/ListServlet?type=updateJump&id=${user.id}">修改 </a>

                </td>

                <td width="5%" class="td_title"><a

                    href="${ctx}/servlet/ListServlet?type=delete&id=${user.id}"> 删除 </a></td>

            </tr>

        </c:forEach>
        </table>

  </body>

</html>

 

登录页面效果图展示：

登录成功后效果图：