数据库的事件（超详细） - ToB企服应用市场:ToB评测及商务社交产业平台

package com.qf.jdbc01;
import com.utils.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test02 {
public static void main(String[] args) {
//传参
try {
saveMoney(1,200);
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
withdrawMoney(2,300);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//场景一：取钱
public static void withdrawMoney(int id,float money) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
System.out.println(connection);
String sql = "update bank set money = money-? where id = ?";
statement = connection.prepareStatement(sql);
statement.setFloat(1,money);
statement.setInt(2,id);
statement.executeUpdate();
} finally {
DBUtil.close(connection,statement,null);
}
}
//场景二、存钱
public static void saveMoney(int id,float money) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBUtil.getConnection();
System.out.println(connection);
String sql = "update bank set money=money+? where id=?";
statement = connection.prepareStatement(sql);
statement.setFloat(1,money);
statement.setInt(2,id);
statement.executeUpdate();
} finally {
DBUtil.close(connection,statement,null);
}
}
//场景三：转账
// try {
//
// DBUtil.startTransaction();
//
// withdrawMoney(1,200);
//
// //System.out.println(10/0);
//
// saveMoney(2,200);
//
// DBUtil.commit();
//
// } catch (Exception e) {
// try {
// DBUtil.rollback();
// } catch (SQLException ex) {
// throw new RuntimeException(ex);
// }
// }
// }
}

复制代码

package com.utils;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* 数据库工具类
*/
public class DBUtil {
private static String url;
private static String username;
private static String password;
private static final ThreadLocal<Object> local ;//使用ThreadLocal处理线程安全的问题
static{
Properties properties = new Properties();
try {
properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
String driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
local = new ThreadLocal<>();
}
/**
* 获取连接对象
*/
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(url,username,password);
if (connection == null){
connection = DriverManager.getConnection(url,username,password);
local.set(connection);//将Connection对象添加到local中
}
return connection;
}
/**
* 关闭资源
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
if (connection.getAutoCommit()){
connection.close();
if (connection == null) {
connection.close();
local.set(null);
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
/**
* 开启事务
*/
public static void startTransaction() throws SQLException {
Connection connection = getConnection();
connection.setAutoCommit(false);
}
/**
* 提交事务
*/
public static void commit() throws SQLException {
Connection connection = (Connection) local.get();
if(connection != null){
connection.commit();
local.set(null);
}
}
/**
* 事务回滚
*/
public static void rollback() throws SQLException {
Connection connection = (Connection) local.get();
if (connection != null){
connection.rollback();
local.set(null);
}
}
/**
* 更新数据（添加、删除、修改）
*/
public static int commonUpdate(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
int num = statement.executeUpdate();
return num;
}finally {
close(connection,statement,null);
}
}
/**
* 添加数据 - 主键回填(主键是int类型可以返回)
*/
public static int commonInsert(String sql,Object... params) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
paramHandler(statement,params);
statement.executeUpdate();
resultSet = statement.getGeneratedKeys();
int primaryKey = 0;
if(resultSet.next()){
primaryKey = resultSet.getInt(1);
}
return primaryKey;
}finally {
close(connection,statement,resultSet);
}
}
/**
* 查询多个数据
*/
public static <T> List<T> commonQueryList(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
List<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
list.add(t);
}
return list;
} finally {
DBUtil.close(connection,statement,resultSet);
}
}
/**
* 查询单个数据
*/
public static <T> T commonQueryObj(Class<T> clazz,String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.prepareStatement(sql);
paramHandler(statement,params);
resultSet = statement.executeQuery();
//获取表数据对象
ResultSetMetaData metaData = resultSet.getMetaData();
//获取字段个数
int count = metaData.getColumnCount();
if(resultSet.next()){
T t = clazz.newInstance();
//获取字段名及数据
for (int i = 1; i <= count; i++) {
String fieldName = metaData.getColumnName(i);
Object fieldVal = resultSet.getObject(fieldName);
setField(t,fieldName,fieldVal);
}
return t;
}
} finally {
DBUtil.close(connection,statement,resultSet);
}
return null;
}
/**
* 处理statement对象参数数据的处理器
*/
private static void paramHandler(PreparedStatement statement,Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
statement.setObject(i+1,params[i]);
}
}
/**
* 获取当前类及其父类的属性对象
* @param clazz class对象
* @param name 属性名
* @return 属性对象
*/
private static Field getField(Class<?> clazz,String name){
for(Class<?> c = clazz;c != null;c = c.getSuperclass()){
try {
Field field = c.getDeclaredField(name);
return field;
} catch (NoSuchFieldException e) {
} catch (SecurityException e) {
}
}
return null;
}
/**
* 设置对象中的属性
* @param obj 对象
* @param name 属性名
* @param value 属性值
*/
private static void setField(Object obj,String name,Object value){
Field field = getField(obj.getClass(), name);
if(field != null){
field.setAccessible(true);
try {
field.set(obj, value);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}

复制代码