[JDBC封装与设计模式]JSP链接数据库实现用户登录，注册功能通用模板

原创已于 2023-10-22 20:32:24 修改 · 1.4k 阅读

36 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#java #数据库 #设计模式

于 2023-10-22 20:24:15 首次发布

项目练习专栏收录该内容

4 篇文章

订阅专栏

本文详细描述了一个使用Java、MySQL8.0.31、JDBC和SpringBoot的简单Web应用，包括数据库表结构创建、JDBC配置、DAO设计以及用户注册和登录功能的实现。

总览

环境

mysql Ver 8.0.31

mysql-connector-j-8.0.31.jar

Tomcat 10.1.13

Navicat Premium 16

IntelliJ IDEA 2023.2.1

数据库

建表代码

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 22/10/2023 19:41:26
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`account`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

代码

jdbc.properties 数据库配置信息

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
uname=root
upwd=123456

BaseDao.java 数据库工具类

package com.dao;

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

public class BaseDao {
    public Connection conn=null;
    public PreparedStatement ps=null;
    //单例模式  整个项目只会连接一次数据库
    private static  String driver;
    private static String url;
    private static String uname;
    private static String upwd;
    //static修饰的属性，方法，代码块只会执行一次
    static{
        // 当前类加载属性文件转换成 输入流对象
        InputStream is=BaseDao.class.getClassLoader().getResourceAsStream("com/dao/jdbc.properties");
        Properties properties=new Properties();
        try {
            //属性文件对象加载输入流对象
            properties.load(is);
            //通过键获取属性文件的值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            uname = properties.getProperty("uname");
            upwd = properties.getProperty("upwd");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建数据库的连接
    public void getConnection() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,uname,upwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //关闭数据库链接
    public void closeConnection(){
        try {
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //查询模板方法
    public ResultSet selectSql(String sql,Object[] objs){
        ResultSet rs = null;
        if (conn!=null){
            try {
                ps = conn.prepareStatement(sql);
                if (objs!=null){
                    for (int i=0;i<objs.length;i++){
                        ps.setObject(i+1,objs[i]);
                    }
                }
                rs=ps.executeQuery();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return rs;
    }

    //增删改模板方法
    public int editSql(String sql,Object[] objs){
        int rs=0;
        if (conn!=null){
            try{
                ps = conn.prepareStatement(sql);
                if (objs!=null){
                    for (int i=0;i<objs.length;i++){
                        ps.setObject(i+1,objs[i]);
                    }
                }
                rs=ps.executeUpdate();
            }catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return rs;
    }
}

UserDao.java 需求接口

package com.dao;

import com.entity.User;

import java.util.List;

public interface UserDao {
    public List<User> findUserList(String account);
    public int addUser(User user);
    public int delUser(int id);
}

UserDaoImpl.java 实现类

package com.dao.impl;

import com.dao.BaseDao;
import com.dao.UserDao;
import com.entity.User;

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

public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public List<User> findUserList(String account) {
        getConnection();
        List<User> userList=new ArrayList<>();
        try {
            String sql = "select * from user where account = ?";
            ResultSet rs = selectSql(sql,new Object[]{account});
            while (rs.next()){
                String password = rs.getString("password");
                userList.add(new User(account,password));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        closeConnection();
        return userList;
    }

    @Override
    public int addUser(User user) {
        getConnection();
        String sql="insert into user(account,password) values(?,?)";
        int rs = editSql(sql,new Object[]{user.getAccount(),user.getPassword()});
        closeConnection();
        return rs;
    }

    @Override
    public int delUser(int account) {
        getConnection();
        String sql="delete from user where account = ?";
        int rs = editSql(sql,new Object[]{account});
        closeConnection();
        return rs;
    }
}

User.java 实体

package com.entity;

public class User {
    private String account,password;
    public User() {
    }
    public User(String account, String password) {
        this.account = account;
        this.password = password;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "account='" + account + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

index.jsp 登录页面

<%--
  Created by IntelliJ IDEA.
  User: 53172
  Date: 2023/10/22
  Time: 16:37
  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="dologin.jsp" method="post">
    <table>
    <tbody>
    <tr>
      <td>用户名:</td>
      <td><input type="text" name="account"/></td>
    </tr>
    <tr>
      <td>密码:</td>
      <td><input type="password" name="password"/></td>
    </tr>
    </tbody>
    </table>
    <input type="submit" value="登录">
    <input type="button" value="注册" onclick="window.open('register.jsp')">
  </form>
  </body>
</html>

dologin.jsp 登录逻辑处理

<%@ page import="com.dao.UserDao" %>
<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="java.util.List" %>
<%@ page import="com.entity.User" %><%--
  Created by IntelliJ IDEA.
  User: 53172
  Date: 2023/10/22
  Time: 17:56
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<%
    String uact=request.getParameter("account");
    String upwd=request.getParameter("password");
    UserDao ud = new UserDaoImpl();
    List<User> userList = ud.findUserList(uact);
    if (!userList.isEmpty() && userList.get(0).getPassword().equals(upwd)){
        out.println("用户 "+uact+" 登录成功");
    }else {
        out.println("账号或密码错误");
    }
%>
<br/>
<input type="button" value="返回登录" onclick="window.open('index.jsp')">
<input type="button" value="返回注册" onclick="window.open('register.jsp')">
</body>
</html>

register.jsp 注册页面

<%--
  Created by IntelliJ IDEA.
  User: 53172
  Date: 2023/10/22
  Time: 18:19
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form name="reg" action="doregister.jsp" method="post">
    <table>
        <tbody>
        <tr>
            <td>用户名:</td>
            <td><input type="text" name="account"/></td>
        </tr>
        <tr>
            <td>密码:</td>
            <td><input type="password" name="password"/></td>
        </tr>
        <tr>
            <td>重复密码:</td>
            <td><input type="password" name="rpassword"/></td>
        </tr>
        </tbody>
    </table>
    <input type="button" value="注册" onclick="doregister()">
</form>
<script>
    function doregister(){
        if (document.reg.account.value==null){
            alert("用户名不能为空！");
            return;
        }
        if (document.reg.password.value===document.reg.rpassword.value){
            reg.submit();
        }else{
            alert("两次密码输入不一致！");
        }
    }
</script>
</body>
</html>

doregister.jsp 注册逻辑处理

<%@ page import="com.dao.UserDao" %>
<%@ page import="com.dao.impl.UserDaoImpl" %>
<%@ page import="com.entity.User" %>
<%@ page import="java.util.List" %><%--
  Created by IntelliJ IDEA.
  User: 53172
  Date: 2023/10/22
  Time: 18:17
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<%
    String uact=request.getParameter("account");
    String upwd=request.getParameter("password");
    UserDao ud = new UserDaoImpl();
    int rs=0;
    try{
        rs=ud.addUser(new User(uact,upwd));
    }catch (Exception e){
        e.printStackTrace();
    }
    if (rs==1){
        out.println("恭喜用户"+uact+"注册成功!");
    }else {
        out.println("注册失败,可能用户名:"+uact+"已存在!");
    }
%>
<br/>
<input type="button" value="返回登录" onclick="window.open('index.jsp')">
<input type="button" value="返回注册" onclick="window.open('register.jsp')">
</body>
</html>