1,准备工作
1) 我们需要使用的jar包如下
durid.jar包 mysql驱动jar包,dbutils.jar包,C3P0.jar包,junit.jar包
2) 准备的数据库 account 字段 id(int),name(varchar),money(double)
3) C3P0配置文件 c3p0-config.xml
<c3p0-config>
<!-- 使用默认的配置读取连接池对象 -->
<default-config>
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名</property>
<property name="user">数据库用户名</property>
<property name="password">数据库密码</property>
</default-config>
</c3p0-config>
4) 工具类(C3P0utils)
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class C3P0Utils {
private static DataSource ds = new ComboPooledDataSource();
/*
* 通过static 代码块 让我们这个代码在整个程序运行过程中只加载一次
* */
private static String driverclass;
private static String url;
private static String username;
private static String password;
// 获取我们的数据源
public static DataSource getDs() {
return ds;
}
// 添加一个方法 , 获取我们的连接对象 Connection
public static Connection getConn() {
Connection conn;
try {
return conn = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关流操作
public static void close(Connection conn, PreparedStatement stat, ResultSet res) {
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 当我们再去做更新操作的时候,我们不需要 ResultSet
public static void close(Connection conn, PreparedStatement stat) {
close(conn, stat, null);
}
}
2.准备实体类Bean
public class Account implements Serializable {
private Integer id;
private String name;
private double money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
3. 实现Dao层代码
1) AccountDao
public interface AccountDao {
public Account findByName(String name);
public void update(Account account);
}
2) AccountDaoImpl
public class AccountDaoImpl implements AccountDao {
//
QueryRunner qr = new QueryRunner();
// 声明一个连接对象
private Connection conn;
// 通过构造赋值
public AccountDaoImpl(Connection conn) {
this.conn = conn;
}
/**
* 通过name查指定用户
* @param name 用户名
* */
@Override
public Account findByName(String name) {
String sql = "select * from account where name = ?";
try {
return qr.query(conn,sql, new BeanHandler<Account>(Account.class), name);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 通过查询到的用户名,进行数据修改
* */
@Override
public void update(Account account) {
String sql = "update account set money = ? where name = ?";
try {
qr.update(conn,sql, account.getMoney(), account.getName());
} catch (Exception e) {
e.printStackTrace();
}
}
}
4. 业务Service层代码
1) AccountService
public interface AccountService {
/**
* 根据账户名查询账户信息
*
* @param outName 账户名
* @param inName 账户名
* @param money 转账金额
*/
public void transfer(String outName, String inName, double money);
}
2) AccountServiceImpl
public class AccountServiceImpl implements AccountService {
// 从连接池获取Connection对象
Connection conn = C3P0Utils.getConn();
private AccountDao accountDao = new AccountDaoImpl(conn);
@Override
public void transfer(String outName, String inName, double money) {
try {
conn.setAutoCommit(false);//手动开启事物
// 查询用户
Account byName = accountDao.findByName(outName); // 来源
Account byName2 = accountDao.findByName(inName); // 目标
// 更改用户余额
byName.setMoney(byName.getMoney() - money); // 来源
byName2.setMoney(byName2.getMoney() + money);//目标
//更新数据库
accountDao.update(byName);
int i = 1 / 0;
accountDao.update(byName2);
conn.commit(); // 到这代码执行完毕,提交事物
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();// 失败回滚
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5. 测试
public class MyTest01 {
@Test
public void test01() {
AccountService accountService = new AccountServiceImpl();
accountService.transfer("aaa", "bbb", 100D);
}
}