jsp、servlet、jdbc实现留言板（可记住密码）

使用工具：mysql、IDEA

一、目标

• 对留言板进行优化：
  登陆页面输入用户名、密码，提交给某个servlet，该servlet可以检索数据库，验证用户名和密码是否合法，给出验证结果

二、JDBC使用说明

1. JDBC基本功能

    建立连接、发送SQL语句，处理数据库操作结果
   

2. mysql使用

   （1）workbench–新建–schemas—create a new schema
   （2）使用:数据库右键set default schema
   （3）新建表格
   （4）编辑表格 列：id字段（专门）（长整数，自增编号），关系模型数据库进行连接查询根据id字段完成，
   注：PK=primary key NN=not null AI=auto increment

3. IDEA下载驱动

   将MYSQL-mysql-connector-java-8.0.18.jar复制到项目目录下web的WEB_INF-lib下，并在File-Project Structure-Modules下添加该jar

4. JDBC语句

//1.注册数据库的驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.printf("加载驱动有错误"+e.getMessage());
        }
        //2.获取数据库连接（里面内容依次是："jdbc:mysql://主机名:端口号/数据库名？时区参数","用户名","登录密码"）
        String dbURL = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
        Connection connection = null;//放外面
        try {
            connection = DriverManager.getConnection(dbURL,"root","root");//（数据库连接，用户名，密码）
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.printf("生成连接对象有错误+"+throwables.getMessage());
        }
        //3.创建语句对象
        try {
            Statement statement = connection.createStatement();
            //4.需要执行的语句
            String sql = "select * from student";
            ResultSet resultSet = statement.executeQuery(sql);//返回二维结果表
            while(resultSet.next()){    //遍历resultSet
                System.out.println("姓名："+resultSet.getString("name"));
                System.out.println("年龄："+resultSet.getInt("age"));
            }
            //5.关闭
            statement.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

三、代码

BoardLoginServlet.java(新)

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.IOException;
import java.sql.*;

@javax.servlet.annotation.WebServlet(name = "BoardLoginServlet",urlPatterns = "/board")
public class BoardLoginServlet extends HttpServlet {

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset = utf-8");
        request.setCharacterEncoding("UTF-8");
        ServletContext context = request.getServletContext();
        HttpSession session=request.getSession();
        String username = request.getParameter("username").trim();
        String password = request.getParameter("password").trim();
        session.setAttribute("username",username);

        //1.注册数据库的驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.printf("加载驱动有错误"+e.getMessage());
        }
        //2.获取数据库连接（里面内容依次是："jdbc:mysql://主机名:端口号/数据库名","用户名","登录密码"）
        String dbURL = "jdbc:mysql://localhost:3306/users?serverTimezone=UTC";
        Connection connection = null;//放外面
        try {
            connection = DriverManager.getConnection(dbURL,"root","root");//（数据库连接，用户名，密码）
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.printf("生成连接对象有错误+"+throwables.getMessage());
        }
        //3.需要执行的sql语句
        String sql = "select * from pswd where name=?";
        String sql2 = "select * from pswd";
        //4.获取预处理对象，并依次给参数赋值
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, username);
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()){
                if(!(resultSet.getString("password").equals(password))) {
                    response.sendRedirect("board-error.jsp");
                    return;
                }else{
                    if ((request.getParameter("check") != null) && (request.getParameter("check").equals("check"))){
                        Cookie nameCookie = new Cookie("username", username);
                        Cookie pswdCookie = new Cookie("password", password);
                        nameCookie.setMaxAge(60 * 60);
                        pswdCookie.setMaxAge(60 * 60);
                        nameCookie.setPath("/");
                        pswdCookie.setPath("/");
                        response.addCookie(nameCookie);
                        response.addCookie(pswdCookie);

                        String value1 = "", value2 = "";
                        Cookie cookie = null;
                        Cookie[] cookies = request.getCookies();
                        if (cookies != null) {
                            for (int i = 0; i < cookies.length; i++) {
                                cookie = cookies[i];
                                if (cookie.getName().equals("username"))
                                    value1 = cookie.getValue();
                                if (cookie.getName().equals("password"))
                                    value2 = cookie.getValue();
                            }
                            ResultSet rs = statement.executeQuery(sql2);//返回二维结果表
                            while(rs.next()){    //遍历resultSet
                                if (value1.equals(rs.getString("name")) && value2.equals(rs.getString("password"))) {
                                    response.sendRedirect("board-title.jsp");

                                    return;
                                } else {
                                    response.sendRedirect("board-title.jsp");
                                    return;
                                }
                            }
                        }
                    }
                    else {
                        response.sendRedirect("board-title.jsp");
                        return;
                    }
                }


            }
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }



    }
}

总结

在使用jdbc过程中出现过很多错误
例如使用try-catch后，登录后出现500

解决：在sendRedirect后加上return；

另外，此次增加了一个简单的登陆失败页面。

编程小白，有错误请大佬指出…

继续努力!!!








本人原创，欢迎转载~