• 封装JDBCUtil工具

    封装JDBC

    一.准备数据

    1.创建表及数据库

    -- 若存在数据库mydb则删除
DROP DATABASE IF EXISTS mydb;
-- 创建数据库mydb
CREATE DATABASE mydb;
-- 选择数据库mydb
USE mydb;

-- 创建user表
CREATE TABLE user(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(40),
		password VARCHAR(40),
		email VARCHAR(40),
		birthday DATE
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    2.插入数据

    -- 插入数据
INSERT INTO user(username,password,email,birthday)  VALUES ('lili','123456','lili@sina.com','1999-12-04');
INSERT INTO user(username,password,email,birthday)  VALUES ('dodo','565678','dodo@sina.com','1997-10-07');
INSERT INTO user(username,password,email,birthday)  VALUES ('nxnx','246866','nxnx@sohu.com','1992-11-11');
INSERT INTO user(username,password,email,birthday)  VALUES ('popo','246851','popo@sohu.com','1999-11-22');

    • 1
    • 2
    • 3
    • 4
    • 5

    二.编写实体类User(JavaBean)

    • java.util.Date与java.sql.Date的关系
      • java.util.Date是父类
      • java.sql.Date是子类
    package cn.com.demo04;

import java.util.Date;
public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	private Date birthday;

	public User() {

	}

	public User(int id, String username, String password, String email, Date birthday) {
		this.id = id;
		this.username = username;
		this.password = password;
		this.email = email;
		this.birthday = birthday;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

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

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", email=" + email+ ", birthday=" + birthday + "]";
	}

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69

    三.JDBCUtils工具类

    package cn.com.demo04;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
	// 加载驱动并建立数据库连接
	public static Connection getConnection() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		String databaseUrl = "jdbc:mysql://localhost:3306/mydb";
		String username = "root";// 数据库用户名
		String password = "test";// 数据库密码
		Connection connection = DriverManager.getConnection(databaseUrl, username, password);
		return connection;
	}

	// 关闭数据库连接释放资源
	public static void release(Connection connection,Statement statement, ResultSet resultSet) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			connection = null;
		}
		
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			statement = null;
		}
		
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			resultSet = null;
		}
	}
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50

    四.UserDao

    package cn.com.demo04;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class UserDao {
	
	// 添加用户
	public boolean insert(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtils.getConnection();
			String sql = "INSERT INTO user(id,username,password,email,birthday) VALUES (?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user.getId());
			preparedStatement.setString(2, user.getUsername());
			preparedStatement.setString(3, user.getPassword());
			preparedStatement.setString(4, user.getEmail());
			//请注意: setDate()方法第二参数类型为java.sql.Date
			Date date = new Date(user.getBirthday().getTime());
			preparedStatement.setDate(5, date);
			int num = preparedStatement.executeUpdate();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(connection, preparedStatement,null);
		}
		return false;
	}

	// 删除用户
	public boolean delete(int userid) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtils.getConnection();
			String sql = "DELETE FROM user WHERE id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			int num = preparedStatement.executeUpdate();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(connection, preparedStatement,null);
		}
		return false;
	}

	// 修改用户
	public boolean update(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtils.getConnection();
			String sql = "UPDATE user SET username=? , password=? , email=? , birthday=? WHERE id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getEmail());
			//请注意: setDate()方法第二参数类型为java.sql.Date
			Date date = new Date(user.getBirthday().getTime());
			preparedStatement.setDate(4, date);
			preparedStatement.setInt(5, user.getId());
			int num = preparedStatement.executeUpdate();
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(connection, preparedStatement,null);
		}
		return false;
	}
	
	// 查找用户
	public User find(int userid) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtils.getConnection();
			String sql = "SELECT * FROM user WHERE id = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				User user = new User();
				user.setId(resultSet.getInt("id"));
				user.setUsername(resultSet.getString("username"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				//请注意：getDate()方法返回的类型为java.sql.Date
				user.setBirthday(resultSet.getDate("birthday"));
				return user;
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(connection, preparedStatement,resultSet);
		}
		return null;
	}
	
	// 查询所有用户
	public ArrayList findAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList list = new ArrayList();
		try {
			connection = JDBCUtils.getConnection();
			String sql = "SELECT * FROM user";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				User user = new User();
				user.setId(resultSet.getInt("id"));
				user.setUsername(resultSet.getString("username"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				//请注意：getDate()方法返回的类型为java.sql.Date
				user.setBirthday(resultSet.getDate("birthday"));
				list.add(user);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(connection, preparedStatement,resultSet);
		}
		return null;
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148

    五.测试类

    package cn.com.demo4;

import java.sql.Date;
import java.util.ArrayList;

public class TestJDBC {
	public static void main(String[] args) {
		UserDao userDao=new UserDao();
		//增加用户
		User user=new User();
		user.setId(5);
		user.setUsername("莉莉");
		user.setPassword("666666");
		user.setEmail("lili@sohu.com");
		Date birthday=Date.valueOf("1997-07-01");
		user.setBirthday(birthday);
		userDao.insert(user);
		//修改用户
		user.setEmail("koko@qq.com");
		userDao.update(user);
		//查找用户
		User foundUser = userDao.find(user.getId());
		System.out.println(foundUser);
		//查找所有用户
		ArrayList userList = userDao.findAll();
		System.out.println(userList.size());
		//删除用户
		userDao.delete(user.getId());
	}

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
  • 相关阅读:
    Oracle-操作【Python-cx_oracle】
    Cisco Secure Firewall Threat Defense Virtual Release 7.2.0
    彻底读懂用户线程和守护线程（代码详解）
    三国志14信息查询小程序（历史武将信息一览）制作更新过程02-基本架构
    山西电力市场日前价格预测【2023-11-11】
    Vue-video-player下载失败(npm i 报错)
    PostgreSQL basebackup备份和恢复
    上周热点回顾（6.10-6.16）
    css实现轮播图弧形
    如何通过财务体系建设，助推企业数智化转型？
  • 原文地址：https://blog.csdn.net/weixin_53903929/article/details/133875572