数据库操作（登录，注册，全查）

17y.o.

已于 2022-07-27 08:51:47 修改

阅读量378

点赞数 2

文章标签：数据库 java servlet

于 2022-07-27 08:48:25 首次发布

本文链接：https://blog.csdn.net/weixin_51009910/article/details/126006952

版权

根据课堂案例，分层设计，连接数据库

项目结构

在这里插入图片描述

DBUtil 连接工具类

package com.hp.Utils;
import java.sql.*;

public class DBUtils {
    private String url = "jdbc:mysql://127.0.0.1:3306/db0726?characterEncoding=utf8";
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String user = "root";
    private String password = "123456";

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public DBUtils() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 查询
     * @param sql 要执行的sql语句
     * @param args 要注入到sql语句的参数
     * @return 查询的结果集
     */
    public ResultSet query(String sql ,Object[]args){
        try {
            ps = conn.prepareStatement(sql); //select * from 表名 where user=?
            if (args != null){
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i+1,args[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return rs;
    }

    /**
     * 增删改
     * @param sql 要执行的sql语句
     * @param args 要注入到sql语句的参数
     * @return 执行成功的条目数
     */
    public int update(String sql ,Object[]args){
        int f = 0;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            f = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return f;
    }

    /**
     * 关闭连接
     */
    public void close(){
        try {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

1、登录

成功：查询所有用户信息，然后转到主页home.jsp

失败：回到登录页面

 @Override
    public UserInfo login(String username, String password) {
        DBUtils db = new DBUtils();
        String sql = "select * from userinfo where username = ? and password = ?";
        Object[] args = {username,password};
        ResultSet rs = db.query(sql, args);
        UserInfo userInfo = null;
        try {
            while (rs.next()){
                Integer id = rs.getInt("id");
                String phone = rs.getString("phone");
                String name = rs.getString("name");
                userInfo = new UserInfo(id, username, password, phone, name);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (db != null){
                db.close();
            }
        }
        return userInfo;
    }

package com.hp.servlet;

import com.hp.dao.UserInfoDao;
import com.hp.dao.imp.UserInfoDaoImp;
import com.hp.entity.UserInfo;

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

@WebServlet("/login")
public class LoginServlet extends HttpServlet {

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

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

        String username = req.getParameter("username");
        String password = req.getParameter("password");

        UserInfo userInfo = new UserInfoDaoImp().login(username,password);
        if (userInfo != null){
            System.out.println("-->>successful");
            req.getSession().setAttribute("userinfo",userInfo);
            req.getRequestDispatcher("home.jsp").forward(req,resp);
        }else {
            System.out.println("-->>failure");
//            PrintWriter out = resp.getWriter();
//            out.write("failure");
//            resp.sendRedirect("http://localhost:8080/work0726_war/failure");
            resp.sendRedirect("http://localhost:8080/work0726_war/login.jsp");
            PrintWriter out = resp.getWriter();
            out.write("failure");
            out.close();
//            out.close();
        }

    }
}

login.jsp

<%--
  Created by IntelliJ IDEA.
  User: 46237
  Date: 2022/7/26
  Time: 22:28
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>登录页面</title>
</head>
<body>
    <form action="login" method="post">
        Username <input type="text" name="username"> <br>
        password <input type="password" name="password"> <br>
        <input type="submit" value="登录">
    </form>
    <a href="register.jsp">注册</a>
</body>
</html>

2、注册

成功：转到登录页面

失败：继续注册

    @Override
    public int insert(UserInfo userInfo) {
        DBUtils db = new DBUtils();
        String sql = "insert into userinfo (username,password,phone,name) values(?,?,?,?)";
        Object[] args = {userInfo.getUsername(),userInfo.getPassword(),userInfo.getPhone(),userInfo.getName()};
        int f = db.update(sql, args);
        if (db != null){

            db.close();
        }
        return f;
    }

package com.hp.servlet;

import com.hp.dao.imp.UserInfoDaoImp;
import com.hp.entity.UserInfo;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/register")
public class RegisterServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        String phone = req.getParameter("phone");
        String name = req.getParameter("name");
        UserInfo userInfo = new UserInfo(0, username, password, phone, name);
        int f = new UserInfoDaoImp().insert(userInfo);
        if (f > 0){
            System.out.println("-->>successful");
            resp.sendRedirect("http://localhost:8080/work0726_war/login.jsp");
        }else {
            System.out.println("-->>>failure");
            resp.sendRedirect("http://localhost:8080/work0726_war/register.jsp");
        }
    }
}

register.jsp

<%--
  Created by IntelliJ IDEA.
  User: 46237
  Date: 2022/7/27
  Time: 0:51
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>注册页面</title>
</head>
<body>
<form action="register" method="post">
    用户名<input type="text" name="username"> <br>

    密码<input type="password" name="password"><br>

    手机<input type="text" name="phone"><br>

    真实姓名<input type="text" name="name"><br>

    <input type="submit" value="注册">
</form>
</body>
</html>

3、查询所有用户信息

通过JSTL标准标签库和EL表达式展示数据

    @Override
    public List<UserInfo> selectAll() {
        DBUtils db = new DBUtils();
        String sql = "select * from userinfo";
        Object[] args = null;
        ResultSet rs = db.query(sql, args);
        List<UserInfo> list = new ArrayList<>();
        UserInfo userInfo = null;
        try {
            while(rs.next()){
                Integer id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String phone = rs.getString("phone");
                String name = rs.getString("name");
                userInfo = new UserInfo(id, username, password, phone, name);
                list.add(userInfo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if (db != null){
                db.close();
            }
        }
        return list;
    }

package com.hp.servlet;

import com.hp.dao.imp.UserInfoDaoImp;
import com.hp.entity.UserInfo;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/list")
public class ListServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("-->>用户信息");
        List<UserInfo> list = (List<UserInfo>) new UserInfoDaoImp().selectAll();
        if (list != null){
            req.getSession().setAttribute("list", list);
            req.getRequestDispatcher("list.jsp").forward(req,resp);
        }
    }

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

    }
}

注意：
	1.页面元素<%@page..%>中添加属性isELIgnored="false"取消忽略EL表达式
	2.注意添加<%@taglib..%> 引入JSTL标准标签库

list.jsp

<%--
  Created by IntelliJ IDEA.
  User: 46237
  Date: 2022/7/26
  Time: 23:25
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>用户信息</title>
</head>
<body>
    <table align="center">
        <tr>
            <td>ID</td>
            <td>用户名</td>
            <td>密码</td>
            <td>电话</td>
            <td>真实姓名</td>
        </tr>
        <c:forEach items="${list}" var="user">
            <tr>
                <td>${user.getId()}</td>
                <td>${user.getUsername()}</td>
                <td>${user.getPassword()}</td>
                <td>${user.getPhone()}</td>
                <td>${user.getName()}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>