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

---

目录

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

0.创建数据库

1.

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

2.

/**

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

*/

3.

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

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

 

 

1.

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

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: CaptainDongDate: 2023/4/9Time: 11:59To 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: CaptainDongDate: 2023/4/9Time: 11:59To 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: CaptainDongDate: 2023/4/9Time: 11:59To 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.项目结构：