Javaweb小练习---在JSP中使用Javabean访问数据库完成用户信息的简单添加

谁践踏了优雅 2024-03-23 11:17 30阅读 0赞

## Javaweb小练习---在JSP中使用Javabean访问数据库完成用户信息的简单添加 ##

--------------------

0.创建数据库

在resources目录下创建db.properties文件

/\*\*

\* 获取链接与释放资源的工具类--JdbcUtil类

\*/

/\*\*\* 实体类---建立该类实现记录信息对象化\*/

4.在User类基础上，建立UserDao类，封装基本的数据库操作

5.创建数据提交页面----a.jsp

6.计算加工页面-----b.jsp

7.显示信息页面-----c.jsp

8.项目结构：

--------------------

### 0.创建数据库 ###

\-- 创建数据库test

-- 创建数据库test
    CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
    
    -- 切换到test数据库
    USE test;
    
    -- 创建数据表user
    CREATE TABLE IF NOT EXISTS user (
        userid VARCHAR(10) PRIMARY KEY,
        username VARCHAR(20) NOT NULL,
        sex VARCHAR(10) NOT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
     SELECT *
     FROM user

![e4ff4e56a9374ea0b1ee93a8895ea86f.png][]

### 1. ###

### 在resources目录下创建db.properties文件 ###

![5c1cabdd6afc43afa3f33856d0ea16ab.png][]

driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/test
    username=root
    password=123456

### 2. ###

### /\*\* ###

### \* 获取链接与释放资源的工具类--JdbcUtil类 ###

### \*/ ###

package jdbc;
    
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    /**
     * 获取链接与释放资源的工具类--JdbcUtil类
     */
    public class JdbcUtil{
        private static String driver;
        private static String url;
        private static String username;
        private static String password;
        private static Properties properties = new Properties();
    
        public JdbcUtil() {
        }
    
        static  {
            try {
                //设计该工具类的静态初始化器中的代码，该代码在装入类时执行，且执行一次
                //加载属性文件，并在JdbcUtil雷中可读取其中的属性值
                properties.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
                driver = properties.getProperty("driver");
                url = properties.getProperty("url");
                username = properties.getProperty("username");
                password = properties.getProperty("password");
                Class.forName(driver);
            }catch (Exception e){
                //throw new ExceptionInInitializerError(e);
                System.out.println(e);
            }
        }
    
        //设计获得连接对象的方法getConnection（）
        public static Connection getConnection() throws Exception{
            Connection connection=null;
            try{
                //1-读取db.properties文件
                Properties properties = new Properties();
                InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties");
                properties.load(in);
    
    
                //2-读取属性
                String driver=properties.getProperty("driver");
                String url=properties.getProperty("url");
                String username=properties.getProperty("username");
                String password=properties.getProperty("password");
    
                //3-注册驱动
                Class.forName(driver);
    
                //4-获取连接
                connection= DriverManager.getConnection(url,username,password);
    
                //5-日志打印连接信息
                System.out.println("连接信息: " + url + " " + username + " " + password);
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("数据库连接失败，请检查连接参数是否正确！");
            }
            return connection;
        }
    
        //设计释放结果集、语句和连接方法free()
        public static void free(ResultSet resultSet, Statement statement,Connection connection)throws Exception{
            if (resultSet!=null){resultSet.close();}
            if (statement!=null){statement.close();}
            if (connection!=null){connection.close();}
        }
    }

### 3. ###

### /\*\*   \* 实体类---建立该类实现记录信息对象化   \*/ ###

package vo;
    
    /**
     * 实体类---建立该类实现记录信息对象化
     */
    public class User {
        private String userid;
        private String username;
        private String sex;
    
        public User() {
        }
    
        public User(String userid, String username, String sex) {
            this.userid = userid;
            this.username = username;
            this.sex = sex;
        }
    
        public String getUserid() {
            return userid;
        }
    
        public void setUserid(String userid) {
            this.userid = userid;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    }

### 4.在User类基础上，建立UserDao类，封装基本的数据库操作 ###

package dao;
    
    import jdbc.JdbcUtil;
    import vo.User;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * 在User类基础上，建立UserDao类，封装基本的数据库操作
     */
    public class UserDao {
        //1-向数据库中添加用户的方法add（User user），将对象user插入数据表中
        public void add(User user)throws Exception{
            Connection connection= JdbcUtil.getConnection();
            String sql="insert into user(userid,username,sex) values(?,?,?)";
            PreparedStatement preparedStatement=connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setString(1,user.getUserid());
            preparedStatement.setString(2,user.getUsername());
            preparedStatement.setString(3,user.getSex());
            preparedStatement.executeUpdate();
            JdbcUtil.free(null,preparedStatement,connection);
        }
        //2-修改数据库用户记录方法update(User user),将对象user进行修改
        public void update(User user)throws Exception{
            Connection connection= JdbcUtil.getConnection();
            String sql="update user set username=?,sex=? where userid=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getSex());
            preparedStatement.setString(3,user.getUserid());
            preparedStatement.executeUpdate();
            JdbcUtil.free(null,preparedStatement,connection);
        }
        //3-删除数据库用户记录方法delete(String userId),根据userId值进行删除记录
        public void delete(String userId)throws Exception{
            Connection connection=JdbcUtil.getConnection();
            String sql="delete from user where userid=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,userId);
            preparedStatement.executeUpdate();
            JdbcUtil.free(null,preparedStatement,connection);
        }
        //4-根据id查询用户方法findUserById（）
        public User findUserById(String userId)throws Exception{
            Connection connection=JdbcUtil.getConnection();
            User user=null;
            String sql="select * from user where userid=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,userId);
            ResultSet resultSet=preparedStatement.executeQuery();
            if (resultSet.next()){
                user=new User();
                user.setUserid(resultSet.getString("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setSex(resultSet.getString("sex"));
            }
            JdbcUtil.free(resultSet,preparedStatement,connection);
            return user;
        }
        //5-查询全部用户的方法QueryAll()
        public List<User>QueryAll()throws Exception{
            Connection connection=JdbcUtil.getConnection();
            List<User> userList=new ArrayList<>();
            String sql="select * from user";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            ResultSet resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                User user=new User();
                user.setUserid(resultSet.getString("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setSex(resultSet.getString("sex"));
                userList.add(user);
            }
            JdbcUtil.free(resultSet,preparedStatement,connection);
            return userList;
        }
    }

### 5.创建数据提交页面----a.jsp ###

<%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/9
      Time: 11:59
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@page pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
    <head>
      <title>数据提交页面</title>
    </head>
    <body>
    <form action="b.jsp" method="post">
      <table>
        <tr>
          <td>
            编号：
          </td>
          <td>
            <input type="text" name="userid" required>
          </td>
        </tr>
        <tr>
          <td>
            姓名：
          </td>
          <td>
            <input type="text" name="username" required>
          </td>
        </tr>
        <tr>
          <td>
            性别：
          </td>
          <td>
            <input type="text" name="sex" required>
          </td>
        </tr>
        <tr>
          <td colspan="2">
            <button type="submit">提交</button>
            <button type="reset">重置</button>
          </td>
        </tr>
      </table>
    </form>
    </body>
    </html>

### 6.计算加工页面-----b.jsp ###

<%@ page import="vo.User" %>
    <%@ page import="dao.UserDao" %>
    <%@ page import="java.util.List" %>
    <%@ page import="java.sql.SQLException" %><%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/9
      Time: 11:59
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>计算加工页面</title>
    </head>
    <body>
    <%
        request.setCharacterEncoding("UTF-8");
        String user_id=request.getParameter("userid");
        String user_name=request.getParameter("username");
        String user_sex=request.getParameter("sex");
    
        User user=new User(user_id,user_name,user_sex);
        UserDao userDao=new UserDao();
        //userDao.add(user);
        // 实现插入
        try {
            userDao.add(user);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    
        List<User>users=userDao.QueryAll();
        request.setAttribute("users_list",users);
    %>
    //转到c.jsp网页
    <jsp:forward page="c.jsp"></jsp:forward>
    </body>
    </html>

### 7.显示信息页面-----c.jsp ###

<%@ page import="java.util.List" %>
    <%@ page import="vo.User" %><%--
      Created by IntelliJ IDEA.
      User: CaptainDong
      Date: 2023/4/9
      Time: 11:59
      To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>显示信息页面</title>
    </head>
    <body>
    <%
        List<User>users=(List<User>) (request.getAttribute("users_list"));
        for (int i=0;i<users.size();i++){
            User user=users.get(i);
            String abc="编号："+user.getUserid()+"_姓名："+user.getUsername()+"_性别："+user.getSex();
            out.println(abc);
        }
    %>
    </body>
    </html>

### 8.项目结构： ###

![caf642528e1d42bd899a3fac7b2ff9ee.png][]

[e4ff4e56a9374ea0b1ee93a8895ea86f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/9aec582d1ba6486696bb5ee48b473843.png
[5c1cabdd6afc43afa3f33856d0ea16ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/f00bbad0513542fca5b9d4541a90cfad.png
[caf642528e1d42bd899a3fac7b2ff9ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/659a7ee9ad0a4cda938a1b1f23f4d00a.png