千锋逆战班，jdbc事务

学习java第44天
努力努力在努力，坚持坚持在坚持！
今天学习了连接池
1.我们可以手动写连接池需要写的类实现DataSource，我们创建个静态集合用来充当池，然后在静态代码块中写个循环往集合中放入
2.德鲁伊，我额们需要导入炸包，然后我们在创建配置文件database.properties里面的内容，在原先的基础上加上链接池的初始连接数，最大连接，最小连接，以及超时等待时间（毫秒数），连接池就改为了一个DruidDataSource的对象ds存储 //使用德鲁伊工厂创建连接池 ds = (DruidDataSource) ruidDataSourceFactory.createDataSource(properties);
获取连接就为ds.getConnection();
3.事务的处理，需要把线程和连接数据库对象绑定在一起，确定在调用所有DAO中的方法保持连接的数据库是一至的这样才能确定事务的原子性，conn.setAutoCommit(false);//关闭自动提交，事务的开始,conn.commit()执行成功无异常提交，conn.rollback()回滚放在catch中异常就启动，要在事务都执行完之后，才关闭事务开始之前开启的connection对象

jdbc配置文件，存储数据库的驱动，链接地址，用户名，密码

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8
username=root
password=740056981

数据库注册驱动，连接数据库，释放资源的工具类

package com.qf.day4.account;

import com.qf.day4.t3.DBUtils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DButil {
    private static final Properties properties  = new Properties();
    //为当前线程绑定一个Connection连接。从头到尾
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<Connection>();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            properties.load(is);//将配置文件内的信息通过is输入流读到properties集合中
            Class.forName(properties.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    public static Connection getConnection(){
        //在ThreadLocal里取
        Connection conn = THREAD_LOCAL.get();//一个事务他只会去一次，第二次再取的话线程里面就存在了，直接就返回线程里的连接了

        try {
            //没有 ，新建
            if(conn == null){
                conn = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
                THREAD_LOCAL.set(conn);//存到线程中
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(Connection conn , Statement st, ResultSet re){
        try {
            if (re != null) {
                re.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
                THREAD_LOCAL.remove();//关闭连接后，移除线程中绑定的连接对象。在执行下一个操作前解除
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

数据表t_account的实体类

package com.qf.day4.account;

public class t_account {
    private String cid;
    private String password;
    private String username;
    private double balance;
    private String phone;

    @Override
    public String toString() {
        return "t_account{" +
                "cid='" + cid + '\'' +
                ", password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                ", phone='" + phone + '\'' +
                '}';
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public t_account(String cid, String password, String username, double balance, String phone) {
        this.cid = cid;
        this.password = password;
        this.username = username;
        this.balance = balance;
        this.phone = phone;
    }

    public t_account(String cid) {
        this.cid = cid;
    }
}

t_account的数据对象访问的工具类（只实现了查单个和修改方法）

package com.qf.day4.account;




import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class t_accountDaoImpl {
    private Connection conn =null;
    private PreparedStatement pre = null;
    private ResultSet re = null;

    //查单个
    public t_account select(String cid){
        conn = DButil.getConnection();
        String sql = "select cardid,password,username,balance,phone from t_account where cardid = ?";
        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1,cid);
            re = pre.executeQuery();
            if(re.next()){
                t_account t1 = new t_account(re.getString(1),re.getString(2),re.getString(3),re.getDouble(4),re.getString(5));

                return t1;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(null,pre,re);
        }
        return null;

    }
    //修改
    public int upDate(t_account t){
        conn = DButil.getConnection();
        String sql = "update t_account set password=?,username=?,balance=?,phone=? where cardid = ? ";

        try {
            pre = conn.prepareStatement(sql);
            pre.setString(1,t.getPassword());
            pre.setString(2,t.getUsername());
            pre.setDouble(3,t.getBalance());
            pre.setString(4,t.getPhone());
            pre.setString(5,t.getCid());

            return pre.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.closeAll(null,pre,re);
        }
        return 0;
    }
}

t_account业务层（实现了转账）业务层的主要作用是在用户和数据层中间进行接收用户传入的数据调用数据层的方法实现用户想要的功能

package com.qf.day4.account;

import java.sql.Connection;
import java.sql.SQLException;

public class t_accountServiceImpl {

    public String tranSfer(String cid,String password,String toid,double money){
        //2.组织业务功能
        t_accountDaoImpl td = new t_accountDaoImpl();

        Connection conn = null;//拿一个连接


        try {
            //建立了一个数据库连接
            conn = DButil.getConnection();

            conn.setAutoCommit(false);//关闭自动提交，事务的开始

            //1.验证自己的账户存不存在
            t_account t1 = td.select(cid);
            if(t1 == null){

                return "用户不存在";
            }
            //账户存在，密码是否正确
            if(!t1.getPassword().equals(password)){
                return "密码错误";
            }
            //账户和密码都对，查看转账金额是否大于余额
            if(t1.getBalance()<=money){
                return "余额不足";
            }
            //查询转账的账户是否存在
            t_account t2 = td.select(toid);

            if(t2 == null){
                return "对方账户不存在";
            }
            //自己的余额进行扣除转账金额

            t1.setBalance(t1.getBalance()- money);//把自己的对象属性做了修改
            int i = td.upDate(t1);
//            int i1 = 1/0;

            //对方账户加钱
            t2.setBalance(t2.getBalance()+money);
            int i2 = td.upDate(t2);

            conn.commit();//提交
            return "转账成功";
        } catch (Exception e) {
            try {
                conn.rollback();//回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DButil.closeAll(conn,null,null);
        }
        return "转账失败";
    }

}

测试类

package com.qf.day4.account;

public class Test {
    public static void main(String[] args) {
        t_accountServiceImpl ts = new t_accountServiceImpl();

        String a = ts.tranSfer("b107","123","b104",2000);
        System.out.println(a);
    }
}