MySQL数据库入门学习（三）——使用工具类封装JDBC实现对数据库的连接
1.前言回顾
在对MySQL数据库进行操作之前，我们首先通过JDBC获取Connection对象，在这个过程中，我们首先要加载MySQL数据库驱动，然后才能通过 DriverManager.getConnection（url,user,password）创建一个连接Connection对象。我们首先观察JDBC中对数据库进行增删查改操作的代码(这里只列出了更新update和查询select）的代码：
//拼接需要执行的更新sql语句
String sql = "update book set name=?,price = ?,status = ?,discount = ?,isBorrowed = ?,createTime = ? where id = ?";
int result = 0;
try {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
//获取创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysqldemo?serverTimezone=UTC", "root", "root");
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置name占位符对应的值
preparedStatement.setString(1, newBook.getName());
//设置price占位符对应的值
preparedStatement.setDouble(2, newBook.getPrice());
//设置status占位符对应的值
preparedStatement.setByte(3, newBook.getStatus());
//设置discount占位符对应的值
preparedStatement.setFloat(4, newBook.getDiscount());
//设置isBorrowed对应占位符的值
preparedStatement.setBoolean(5, newBook.isBorrowed());
//设置createTime占位符对应的值,需要将 java.Util.Date 转化为 java.sql.Date才可以
preparedStatement.setDate(6,new Date(newBook.getCreateTime().getTime()));
//设置id占位符对应的值
preparedStatement.setInt(7, newBook.getId());
//开始执行更新,并返回影响的行数
result = preparedStatement.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;public Book findBookById(int id) {
//拼接需要执行的查找的sql语句
String sql = "select * from book where id = ?";
//结果集
ResultSet resultSet = null;
Book book = null;
try {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
//获取创建连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysqldemo?serverTimezone=UTC", "root", "root");
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置id占位符对应的值
preparedStatement.setInt(1, id);
//开始执行查询语句,并返回查询的结果集
resultSet = preparedStatement.executeQuery();
//如果查询到结果不为空而是有记录
if(resultSet.next()) {
//获取book的name
String name = resultSet.getString("name");
//获取book的status
byte status = resultSet.getByte("status");
//获取book的price
double price = resultSet.getDouble("price");
//获取book的discount
float discount = resultSet.getFloat("discount");
//获取book的isBorrowed
boolean isBorrowed = resultSet.getBoolean("isBorrowed");
//获取book的createTime
Date date = resultSet.getDate("createTime");
book = new Book();
book.setBorrowed(isBorrowed);
book.setDiscount(discount);
book.setId(id);
book.setPrice(price);
book.setName(name);
book.setStatus(status);
book.setCreateTime(date);
}
} catch (SQLException e) {
e.printStackTrace();
book = null;
}
return book;
}我们发现，无论是更新操作还是查找操作，都存在着重复代码：
①加载MySQL数据库驱动。
②创建连接对象Connection。
因此我们可以通过一个工具类来封装一个获取Connection对象的方法，从而达到代码复用，减少重复的代码。同时我们连接MySQL数据的配置信息比如 url，user,password等信息可以配置在一个文件中，以后我们修改配置信息时只需要在文件中修改就好了，不用去修改源代码，从而降低代码之间的耦合度。在此次中我们将链接数据库所需要的信息存储到一个 mysql.properties,采用 key = value的形式存储键值对：
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=root
url=jdbc:mysql://localhost:3306/mysqldemo?serverTimezone=UTC
2.在util包下创建一个工具类JDBCUtil.java进行封装
2.1首先在项目的资源文件夹resources中读取配置文件mysql.properties到程序的 java.util.Properties中去。
//用于加载配置信息的properties
private static Properties pro = new Properties();
static {
//加载配置文件作为输入流
InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("mysql.properties");
try {
//加载配置文件流到Properties中
pro.load(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}2.2获取Connection连接对象
/**
*获取Connection连接对象
* @return 返回 Connection对象
*/
public static Connection getConnection() {
Connection connection = null;
try {
//加载数据库驱动
String driverClassName = pro.getProperty("driverClassName");
//加载驱动
Class.forName(driverClassName);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
try {
//获取数据库的URL
String url = pro.getProperty("url");
//获取用户名
String user =pro.getProperty("username");
//获取密码
String password = pro.getProperty("password");
//获取连接对象
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}2.3关闭资源
/**
* 关闭资源,首先关闭resultSet，然后关闭statement，最后关闭connection
* @param connection 数据库连接对象
* @param statement 预处理对象
* @param resultSet 结果集
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}2.4完整的工具类代码
package util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author 陌意随影
TODO :JDBC连接数据库工具类
*2020年10月31日 下午4:17:50
*/
public class JDBCUtil {
//用于加载配置信息的properties
private static Properties pro = new Properties();
static {
//加载配置文件作为输入流
InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("mysql.properties");
try {
//加载配置文件流到Properties中
pro.load(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
*获取Connection连接对象
* @return 返回 Connection对象
*/
public static Connection getConnection() {
Connection connection = null;
try {
//加载数据库驱动
String driverClassName = pro.getProperty("driverClassName");
//加载驱动
Class.forName(driverClassName);
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
try {
//获取数据库的URL
String url = pro.getProperty("url");
//获取用户名
String user =pro.getProperty("username");
//获取密码
String password = pro.getProperty("password");
//获取连接对象
connection = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 关闭资源,首先关闭resultSet，然后关闭statement，最后关闭connection
* @param connection 数据库连接对象
* @param statement 预处理对象
* @param resultSet 结果集
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}3.修改BookDao.java中的的代码使用工具类JDBCUtil.java获取连接对象Connection和关闭资源
package dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import entity.Book;
import util.JDBCUtil;
/**
* @author 陌意随影
TODO : BookDao
*2020年10月30日 下午9:04:08
*/
public class BookDao {
/**
* @param book 需要保存的对象
* @return 保存成功返回1否则返回 0
*/
public int saveByStatement(Book book) {
//拼接需要执行的sql语句
String sql = "insert into book(name,price,status,discount,isBorrowed,createTime) values(\""+ book.getName()+"\",\""
+ book.getPrice()+"\",\""+book.getStatus()+"\",\""+book.getDiscount()+"\"," + book.isBorrowed()+",\"" + book.getCreateTime().toLocaleString()+"\");";
System.out.println(sql);
int result = 0;
try {
//2.获取创建连接
Connection connection = JDBCUtil.getConnection();
//3.创建预处理对象
Statement statement = connection.createStatement();
//4.执行sql语句并返回执行结果影响的行数
result = statement.executeUpdate(sql);
//5.释放资源,先开启的后释放
JDBCUtil.close(connection, statement, null);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
/**
* @param book 需要保存的对象
* @return 保存成功返回1否则返回 0
*/
public int saveByPrepareStatement(Book book) {
//拼接需要执行的sql语句
String sql = "insert into book(name,price,status,discount,isBorrowed,createTime) values(?,?,?,?,?,?)";
int result = 0;
try {
//获取创建连接
Connection connection = JDBCUtil.getConnection();
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置name占位符对应的值
preparedStatement.setString(1, book.getName());
//设置price占位符对应的值
preparedStatement.setDouble(2, book.getPrice());
//设置status占位符对应的值
preparedStatement.setByte(3, book.getStatus());
//设置discount占位符对应的值
preparedStatement.setFloat(4, book.getDiscount());
//设置isBorrowed对应占位符的值
preparedStatement.setBoolean(5, book.isBorrowed());
//设置createTime占位符对应的值,需要将 java.Util.Date 转化为 java.sql.Date才可以
preparedStatement.setDate(6,new Date(book.getCreateTime().getTime()));
//开始执行,并返回影响的行数
result = preparedStatement.executeUpdate();
//释放资源,先开启的后释放
JDBCUtil.close(connection, preparedStatement, null);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
/**
* 更新book
* @param newBook
* @return 返回受影响的行数
*/
public int updateBookByPreparedStatement(Book newBook) {
//拼接需要执行的更新sql语句
String sql = "update book set name=?,price = ?,status = ?,discount = ?,isBorrowed = ?,createTime = ? where id = ?";
int result = 0;
try {
//获取创建连接
Connection connection = JDBCUtil.getConnection();
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置name占位符对应的值
preparedStatement.setString(1, newBook.getName());
//设置price占位符对应的值
preparedStatement.setDouble(2, newBook.getPrice());
//设置status占位符对应的值
preparedStatement.setByte(3, newBook.getStatus());
//设置discount占位符对应的值
preparedStatement.setFloat(4, newBook.getDiscount());
//设置isBorrowed对应占位符的值
preparedStatement.setBoolean(5, newBook.isBorrowed());
//设置createTime占位符对应的值,需要将 java.Util.Date 转化为 java.sql.Date才可以
preparedStatement.setDate(6,new Date(newBook.getCreateTime().getTime()));
//设置id占位符对应的值
preparedStatement.setInt(7, newBook.getId());
//开始执行更新,并返回影响的行数
result = preparedStatement.executeUpdate();
//释放资源,先开启的后释放
JDBCUtil.close(connection, preparedStatement, null);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
/**
* 删除book
* @param id 需要删除书籍的ID
* @return 返回受影响的行数
*/
public int deleteBookById(int id) {
//拼接需要执行的删除的sql语句
String sql = "delete from book where id = ?";
int result = 0;
try {
//获取创建连接
Connection connection = JDBCUtil.getConnection();
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置id占位符对应的值
preparedStatement.setInt(1, id);
//开始执行更新,并返回影响的行数
result = preparedStatement.executeUpdate();
//释放资源,先开启的后释放
JDBCUtil.close(connection, preparedStatement, null);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
/**
* 通过ID查询book
* @param id 要查询的book的ID
* @return 返回ID对应的book
*/
public Book findBookById(int id) {
//拼接需要执行的查找的sql语句
String sql = "select * from book where id = ?";
//结果集
ResultSet resultSet = null;
Book book = null;
try {
//获取创建连接
Connection connection = JDBCUtil.getConnection();
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置id占位符对应的值
preparedStatement.setInt(1, id);
//开始执行查询语句,并返回查询的结果集
resultSet = preparedStatement.executeQuery();
//如果查询到结果不为空而是有记录
if(resultSet.next()) {
//获取book的name
String name = resultSet.getString("name");
//获取book的status
byte status = resultSet.getByte("status");
//获取book的price
double price = resultSet.getDouble("price");
//获取book的discount
float discount = resultSet.getFloat("discount");
//获取book的isBorrowed
boolean isBorrowed = resultSet.getBoolean("isBorrowed");
//获取book的createTime
Date date = resultSet.getDate("createTime");
book = new Book();
book.setBorrowed(isBorrowed);
book.setDiscount(discount);
book.setId(id);
book.setPrice(price);
book.setName(name);
book.setStatus(status);
book.setCreateTime(date);
}
//释放资源,先开启的后释放
JDBCUtil.close(connection, preparedStatement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
book = null;
}
return book;
}
/**
* 查询所有图书
* @return 返回List<book>
*/
public List<Book> findAllBook() {
//拼接需要执行的查找的sql语句
String sql = "select * from book ";
//结果集
ResultSet resultSet = null;
List<Book> bookList = null;
try {
//获取创建连接
Connection connection = JDBCUtil.getConnection();
//创建预处理对象
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//开始执行查询语句,并返回查询的结果集
resultSet = preparedStatement.executeQuery();
bookList = new ArrayList<>();
//如果查询到结果不为空而是有记录
while(resultSet.next()) {
//获取book的ID
int id = resultSet.getInt("id");
//获取book的name
String name = resultSet.getString("name");
//获取book的status
byte status = resultSet.getByte("status");
//获取book的price
double price = resultSet.getDouble("price");
//获取book的discount
float discount = resultSet.getFloat("discount");
//获取book的isBorrowed
boolean isBorrowed = resultSet.getBoolean("isBorrowed");
//获取book的createTime
Date date = resultSet.getDate("createTime");
Book book = new Book();
book.setBorrowed(isBorrowed);
book.setDiscount(discount);
book.setId(id);
book.setPrice(price);
book.setName(name);
book.setStatus(status);
book.setCreateTime(date);
bookList.add(book);
}
//释放资源,先开启的后释放
JDBCUtil.close(connection, preparedStatement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
bookList = null;
}
return bookList;
}
}4.使用JUnit测试单元进行测试
经过测试BookDao.java中的每个方法都能正常运行
5.本次实验环境
eclipse2019, jdk1.8,mysql8.0
