DBUtil.java // 数据库操作文件
package com.bjpowernode.jdbc.util; import java.io.File;
import java.io.FileInputStream;
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; public class DBUtil { private static String driver ;
private static String url ;
private static String uname ;
private static String pwd ; static{ try {
//创建集合类对象
Properties properties = new Properties();
//把文件封装成字节输入流
InputStream inStream = new FileInputStream(new File("./src/DBConfig.properties")); /*
等同于 ResourceBundle isStream = ResourceBundle.getBundle("com.bjpowernode.jdbc.util.DBConfig"); //注意：DBConfig 不要添加后缀 */
//把字节流加载到集合类中，在内存中以key和value的格式形成
properties.load(inStream);
//通过key获得value
driver = properties.getProperty("driver");
url = properties.getProperty("url");
uname = properties.getProperty("uname");
pwd = properties.getProperty("pwd");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("读取配置文件失败！",e);
} } //获得连接
public static Connection getConnection (){
Connection conn = null;
try {
//1:注册驱动
Class.forName(driver); conn = DriverManager.getConnection(url,uname,pwd);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("连接数据库失败！",e);
}
return conn;
} //释放资源
public static void close(ResultSet rs ,Statement pstm ,Connection conn){
try{
if (rs != null){
rs.close();
} }catch(SQLException e){
e.printStackTrace();
throw new RuntimeException("rs关闭失败！",e);
}
try{
if (pstm != null){
pstm.close();
} }catch(SQLException e){
e.printStackTrace();
throw new RuntimeException("pstm关闭失败！",e);
}
try{
if (conn != null){
conn.close();
} }catch(SQLException e){
e.printStackTrace();
throw new RuntimeException("conn关闭失败！",e);
} }
//开启事务
public static void beginTransaction(Connection conn){
try {
if(conn != null){
conn.setAutoCommit(false);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("开启事务失败！",e);
}
}
//提交事务
public static void commit(Connection conn){
try {
if(conn != null){
conn.commit();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException("提交事务失败！",e);
}
} //回滚事务
public static void rollback(Connection conn){
try {
if(conn != null){
conn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("回滚事务失败！",e);
}
}
}
Transfer_transaction.java // 调用数据库文件
package com.bjpowernode.jdbc.transfer; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import com.bjpowernode.jdbc.util.DBUtil; public class Transfer_transaction {
public static void main(String[] args) {
transfer("zs","ls",100);
}
/**
*
* @param from_act : 转出账户
* @param to_act ：转入账户
* @param money ： 转账金额
*/
private static void transfer(String from_act, String to_act, double money) { /*if(转出账户的金额 >= 转账金额)｛
转出账户 - 转账金额
转入账户 + 转账金额
｝else{
提示余额不足
}*/
Connection conn = null;
//转出账户的金额
try {
//事务是针对连接开启的
conn = DBUtil.getConnection();
//开启事务
DBUtil.beginTransaction(conn); double from_money = getMoneyByAct(conn,from_act); if(from_money >= money){//转出账户的金额 >= 转账金额
//转账
//转出账户 - 转账金额
updateMoneyByAct(conn,from_money-money,from_act);
//转入账户的金额
double to_money = getMoneyByAct(conn,to_act);
//模拟异常
// Integer.parseInt("abc");
//转入账户+ 转账金额
updateMoneyByAct(conn,to_money+money,to_act);
//提交事务
DBUtil.commit(conn);
System.out.println("转账成功！");
}else{//提示余额不足
System.out.println("余额不足！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("转账失败！");
//回滚事务
DBUtil.rollback(conn);
}finally{
DBUtil.close(null, null, conn);
} }
/**
* 通过账户修改账户金额
*
* @param money : 需要修改的金额
* @param act ： 账户名称
*/
private static void updateMoneyByAct(Connection conn ,double money, String act) {
// TODO Auto-generated method stub
// Connection conn = null;
PreparedStatement pstm = null; try {
// conn = DBUtil.getConnection(); String sql = "update t_account set money = ? where act_no = ?";
pstm = conn.prepareStatement(sql); pstm.setDouble(1, money);
pstm.setString(2, act); pstm.executeUpdate(); } catch (Exception e) {
// TODO: handle exception throw new RuntimeException("修改金额失败",e);
}finally{
DBUtil.close(null, pstm, null);
}
}
/**
* 通过账户查询账户金额
* @param act
* @return
*/
private static double getMoneyByAct(Connection conn ,String act) {
// Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
double from_money = 0;
try {
//获得连接
// conn = DBUtil.getConnection();
String sql = "select money from t_account where act_no = ?";
//创建数据库操作对象
pstm = conn.prepareStatement(sql);
//为占位符号赋值
pstm.setString(1, act);
//执行sql
rs = pstm.executeQuery();
/*while(rs.next()){
from_money = rs.getDouble("money");
}
if(rs.next()){
from_money = rs.getDouble("money");
}
*/
from_money = rs.next()?rs.getDouble("money"):0; } catch (Exception e) {
throw new RuntimeException("查询账户余额失败！",e);
}finally{
DBUtil.close(rs, pstm, null);
}
return from_money;
}
}
DBConfig.properties //数据库配置文件
driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://127.0.0.1\:3366/bjpowernode
uname=root
pwd=root