package com.imut.utils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
/**
* @author djs
* @create 2022-09-25 14:30
*/
public class JDBCUtils {
/**
* ResourceBundle 是资源绑定器 只能获取.properties的文件(配置文件)
* 注意事项:
* 1. .properties的文件必须放在类路径下(Src目录下)
* 2. 文件名称一定不能写.properties后缀名
*/
private static ResourceBundle bundle = ResourceBundle.getBundle("resources/jdbc");
private static String driver = bundle.getString("driver");
private static String url = bundle.getString("url");
private static String user = bundle.getString("user");
private static String password = bundle.getString("password");
// 构造器私有化:避免工具类可以创建对象
private JDBCUtils(){}
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取一个连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement) {
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//查询表中的数据,以集合的形式返回
public static <T> List <T> query(String sql, Class<T> clazz, Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> list = new ArrayList<>();
try {
// 1.获取一个连接
connection = getConnection();
// 2.预编译一个sql语句,返回一个PrepareStatement对象
preparedStatement = connection.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
// 4。执行sql,得到结果集
resultSet = preparedStatement.executeQuery();
// 5.获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
// 6.获取一个类的反射实例
T t = null;
// 7.遍历得到每一行数据
while (resultSet.next()){
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 7.1获取列值
Object object = resultSet.getObject(i + 1);
// 7.2获取列别名
String columnLabel = metaData.getColumnLabel(i + 1);
// 7.3获取属性并设置属性的值
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, object);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(connection, preparedStatement, resultSet);
}
return list;
}
//新增,修改,删除
public static int update(String sql, Object ...obj) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 1.获取连接
connection = getConnection();
// 2.预编译sql,返回一个PrepareStatement实例
preparedStatement = connection.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < obj.length; i++) {
preparedStatement.setObject(i+1, obj[i]);
}
// 4.执行
int result = preparedStatement.executeUpdate();
connection.commit();
return result;
} catch (Exception e) {
if(connection != null){
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
} finally {
// 5.关闭资源
closeConnection(connection, preparedStatement);
}
return 0;
}
}
Web工程目录结构
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2ea5af6bd9e51790898430ffdecc8016.png)
介绍ThreadLocal
每次使用完threadLocal完后,都要删除里面存的值
根本原因:
添加了事务版本:
package com.imut.utils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
/**
* @author djs
* @create 2022-09-25 14:30
*/
public class JDBCUtils {
private static ResourceBundle bundle = ResourceBundle.getBundle("resources.jdbc");
private static String driver = bundle.getString("driver");
private static String url = bundle.getString("url");
private static String user = bundle.getString("user");
private static String password = bundle.getString("password");
//避免创建对象
private JDBCUtils(){}
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
/**
* 获取一个连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Connection connection = threadLocal.get();
System.out.println("connection = " + connection);
if(connection == null){
connection = DriverManager.getConnection(url, user, password);
threadLocal.set(connection);
}
return connection;
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement) {
if (connection != null){
try {
connection.close();
//从threadlocal中删除,因为tomcat是支持线程池的
// 如果不删除,可能下次获得的还是这个线程,会获得这个的connection
threadLocal.remove();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null){
try {
resultSet.close();
//从threadlocal中删除,因为tomcat是支持线程池的
// 如果不删除,可能下次获得的还是这个线程,会获得这个的connection
threadLocal.remove();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//查询表中的数据,以集合的形式返回
public static <T> List <T> query(String sql, Class<T> clazz, Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> list = new ArrayList<>();
try {
// 1.获取一个连接
connection = getConnection();
// 2.预编译一个sql语句,返回一个PrepareStatement对象
preparedStatement = connection.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
// 4。执行sql,得到结果集
resultSet = preparedStatement.executeQuery();
// 5.获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
// 6.获取一个类的反射实例
T t = null;
// 7.遍历得到每一行数据
while (resultSet.next()){
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 7.1获取列值
Object object = resultSet.getObject(i + 1);
// 7.2获取列别名
String columnLabel = metaData.getColumnLabel(i + 1);
// 7.3获取属性并设置属性的值
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, object);
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(null, preparedStatement, resultSet);
}
return list;
}
//新增,修改,删除
public static int update(String sql, Object ...obj) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// 1.获取连接
connection = getConnection();
// 2.预编译sql,返回一个PrepareStatement实例
preparedStatement = connection.prepareStatement(sql);
// 3.填充占位符
for (int i = 0; i < obj.length; i++) {
preparedStatement.setObject(i+1, obj[i]);
}
// 4.执行
int result = preparedStatement.executeUpdate();
return result;
} catch (Exception e) {
if(connection != null){
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
} finally {
// 5.关闭资源
closeConnection(null, preparedStatement);
}
return 0;
}
}
测试
@Test
public void test5(){
BankDao bankDao = new BankDaoImpl();
Bank bank1 = bankDao.seleteById("b001");
Bank bank2 = bankDao.seleteById("b002");
try {
Connection connection = JDBCUtils.getConnection();
connection.setAutoCommit(false);
double money = 1000;
if(bank1.getBalance() < money){
System.out.println("钱不够!");
return;
}
bank1.setBalance(bank1.getBalance()-money);
bank2.setBalance(bank2.getBalance()+money);
bankDao.update(bank1);
int a = 1 /0 ;
bankDao.update(bank2);
System.out.println("转账成功!");
connection.commit();
//关闭JDBC的各个资源
JDBCUtils.closeConnection(connection,null);
} catch (Exception e) {
e.printStackTrace();
}
}