使用JdbcTemplat,druid连接池,事务...综合使用
编写工具类:
import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; //数据库连接池工具类 public class JdbcUtil { private static Properties pro =null; static { //加载配置文件 pro = new Properties(); InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("druid.properties"); try { pro.load(in); } catch (IOException e) { e.printStackTrace(); } } //获取数据源 public static DataSource getDataSource() throws Exception { DataSource ds = DruidDataSourceFactory.createDataSource(pro); return ds; } //获取连接池对象 public static Connection getConnection() throws Exception { DataSource ds = DruidDataSourceFactory.createDataSource(pro); Connection connection = ds.getConnection(); return connection; } //释放资源 public static void closeZY(ResultSet resultSet, Statement statement,Connection connection){ if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } }
dao层类代码:
import cn.itcast.utils.JdbcUtil; import cn.itcast.utils.TestUtil; import org.springframework.jdbc.core.JdbcTemplate; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class MoneyDao { //根据姓名查询账户余额,并返回 public double blanceChecked(String name) throws Exception { JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtil.getDataSource()); String sql = "select money from account where name = ?"; Double count = jdbcTemplate.queryForObject(sql, double.class, name); return count; } //转出 public int outMoney(String name, double money,Connection connection) throws Exception { String sql = "update account set money = money-? where name =?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setDouble(1,money); ps.setString(2,name); int i = ps.executeUpdate(); return i; } //转入 public int inMoney(String name, double money,Connection connection) throws Exception { JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtil.getDataSource()); String sql = "update account set money = money +? where name =?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setDouble(1,money); ps.setString(2,name); int i = ps.executeUpdate(); return i; } }
service层类代码:
import cn.itcast.dao.MoneyDao; import cn.itcast.utils.TestUtil; import java.sql.Connection; import java.sql.SQLException; public class MoneyService { //查询传入名字的账户的余额是否够转账 public boolean outMoney(String name,double money) throws Exception { MoneyDao md = new MoneyDao(); double blance = md.blanceChecked(name); if (blance < money) { System.out.println("余额不足,无法转账"); return false; } return true; } //转账业务 public void rotateMoney(double money, String nameOut, String nameIn) { MoneyDao md = new MoneyDao(); Connection connection = TestUtil.getConnection(); try { connection.setAutoCommit(false);//开启事务 md.outMoney(nameOut, money,connection); //int j =1/0;//模拟事务中间故障 md.inMoney(nameIn, money,connection); connection.commit(); System.out.println("转账成功"); } catch (Exception e) { e.printStackTrace(); System.out.println("转账失败"); try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } }
}
web层类代码:
import cn.itcast.service.MoneyService; /* 三层架构模拟转账 */ public class MoneyTest { public static void main(String[] args) throws Exception { double money =100;//转账的钱数 String name = "jack";//转出人 String name2 = "rose";//收款人 MoneyService ms = new MoneyService(); boolean b = ms.outMoney(name, money); if (b) {//余额足够转账 ms.rotateMoney(money,name,name2); } } }
数据库图片:
运行测试结果:
修改转账金额为:1000运行结果为:
经转账金额改成100,把业务层 模拟事务故障的注释去掉后运行结果为:
加上注释后再次运行: