JAVA第二阶段第十天 JDBC事务&连接池

连接池:DBCP

DBCP连接池的使用步骤

1.导入其jar包，添加依赖，数据库jar也需要 (如图1)
2.获取DataSource对象：BasicDataSourceFactory.createDataSource(pro);
3.添加数据库的配置文件，把数据配置文件的信息加载到Properties对象

package com.offcn.test;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Properties;

public class Test {
    public static void main(String[] args) {
        //通过反射获取文件
        try {
            InputStream is = Test.class.getResourceAsStream("dbcpconfig.properties");
            Properties pro = new Properties();
            pro.load(is);
            DataSource ds = BasicDataSourceFactory.createDataSource(pro);
            System.out.println(ds.getConnection());
        } catch (Exception e) {
            e.printStackTrace();
        }
       //通过流读取获取文件
        try {
            FileInputStream fis = new FileInputStream(new File("com/offcn/test/dbcpconfig.properties"));
            Properties pro = new Properties();
            pro.load(fis);
            DataSource ds = BasicDataSourceFactory.createDataSource(pro);
            System.out.println(ds.getConnection());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

DBCP连接池的作用

1.连接池的效率较高
2.是tomcat的内置连接池
3.一秒钟最多可以创建于管理10000个连接
4.安全性比较差，丢失数据 ,类似socket 的udp协议
5.避免反复来创建和反复销毁连接

dbcp工具类的封装

1.只实例化一个对象
2.配置文件只加载一次：单例设置模式 :1.私有的属性 2.私有的构造 3.提供公有的对外方法
3.配置文件的地址:http://commons.apache.org/proper/commons-dbcp/configuration.html

package com.offcn.test;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.net.DatagramSocketImplFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DbcpUtil {
    //私有属性
    private static DbcpUtil dbcpUtil;
    private DataSource dataSource;
    //私有构造方法
    private DbcpUtil(){
        try {
            Properties pro = new Properties();
            InputStream is = DbcpUtil.class.getResourceAsStream("dbcpconfig.properties");
            pro.load(is);
            dataSource = BasicDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //提供对外方法
    public synchronized static DbcpUtil getInstance(){
        if(dbcpUtil == null){
            synchronized (DbcpUtil.class){
                if (dbcpUtil == null){
                    dbcpUtil = new DbcpUtil();
                }
            }
        }
        return dbcpUtil;
    }
    //提供连接方法
    public Connection getcon(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    //在main（测试类）方法里测试
    //System.out.println(DbcpUtil.getInstance().getcon());
}

连接池：C3P0

C3P0连接池的特点

1.数据不会丢失,安全性比较高,但是效率慢一点
2.所有spring框架的连接池都使用的c3p0
3.spring全家桶 :1.spring 2.springMVC 3.springData 4.springBoot 5.springcould

C3P0的基本步骤和使用

1.导入相关的jar包
2.使用其核心的类：QueryRunner ==> QueryRunner qr = new QueryRunner();
3.常规的方法:update(“sql语句”,“具体的值”)
4.查询的接口类: ResultSetHandler :
==>>1. new BeanHandler(User.class) 查询的是单条对象
==>>2. new BeanListHandler(User.class) 查询出所有对象
==>>3. new MapHandler() 查询出第一行数据,然后把数据封装成一个map对象
==>>4. new MapListHandler() 查询出表中的数据，都封装成一个map对象,也是一个集合，只是泛型是Map
==>>5. new ScalarHandler() 查询总记录数,必须用Long 类型来接收

package com.offcn.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.SQLException;

public class Test1 {
    public static void main(String[] args) {
        //c3p0默认获取
        /*DataSource ds = new ComboPooledDataSource();
        try {
            System.out.println(ds.getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }*/

        //根据名称name获取
        DataSource ds = new ComboPooledDataSource("offcn");
        try {
            System.out.println(ds.getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

package com.offcn.test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Test2 {
    //增删改
    @Test
    public void show1() throws SQLException {
        DataSource ds = new ComboPooledDataSource();
        QueryRunner qr = new QueryRunner(ds);
        String sql = "insert into user(uname,upwd)values(?,?)";
        Object[] objects = {"刘莉","181818"};
        int num = qr.update(sql,objects);
        System.out.println(num);
    }
    //查询  BeanHandler 只能查询一个对象
    @Test
    public void show2() throws SQLException {
        DataSource ds = new ComboPooledDataSource();
        QueryRunner qr = new QueryRunner(ds);
        String sql = "select * from user where uid = ?";
        User user = qr.query(sql,new BeanHandler<User>(User.class),5);
        System.out.println(user.getUid()+"\t"+user.getUname()+"\t"+user.getUpwd());
    }
    //查询 BeanListHandler 集合查询所有对象信息
    @Test
    public void show5(){
        try {
            DataSource ds = new ComboPooledDataSource();
            QueryRunner qr = new QueryRunner(ds);
            String sql = "select * from user";
            List<User> list = qr.query(sql,new BeanListHandler<User>(User.class));
            for (User u:list) {
                System.out.println(u.getUid()+"\t"+u.getUname()+"\t"+u.getUpwd());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //MapHandler 集合只能查询第一个对象
    @Test
    public void show3(){
        try {
            DataSource ds = new ComboPooledDataSource();
            QueryRunner qr = new QueryRunner(ds);
            String sql = "select * from user";
            Map<String,Object> map = qr.query(sql,new MapHandler());
            for (String str:map.keySet()) {
                System.out.println(str);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //查询  MapListHandler 查询出所有对象信息
    @Test
    public void show4(){
        try {
            DataSource ds = new ComboPooledDataSource();
            QueryRunner qr = new QueryRunner(ds);
            String sql = "select * from user";
            List<Map<String,Object>> list = qr.query(sql,new MapListHandler());
            for (Map<String,Object> map:list) {
                //System.out.println(map);
                for (String str:map.keySet()) {
                    System.out.println("键："+str+"\t"+"值："+map.get(str));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

实体类的要求

1.必须序列化：implements Serializable
2必须是私有的属性
3.必须给一个无参构造，调用set、get 方法

package com.offcn.test;

import java.io.Serializable;

public class User implements Serializable {
    private int uid;
    private String uname;
    private String upwd;

    public User() {
    }

    public User(int uid, String uname, String upwd) {
        this.uid = uid;
        this.uname = uname;
        this.upwd = upwd;
    }

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpwd() {
        return upwd;
    }

    public void setUpwd(String upwd) {
        this.upwd = upwd;
    }
}

JDBC 事务

事务的使用场景：

1.保证一组sql语句同时执行成功，才能确定这次操作合理，类似转账的过程
2.事务： mysql 数据库中的
==>>1.自动提交 set autocommit = 1
==>>2.手动提交 set autocommit = 0
==>>3.开启事务 start transaction
==>>4.设置回滚原点 savepoint ss
==>>5.mysql 两条执行语句
==>>6.回滚事务 rollback
==>>7.回滚到事务原点 rollback to ss
==>>8.提交事务 commit
3.事务的原理：如果开启事务，给用户来创建一个临时的文件来保存数据，需要commit的时候才会进行提交(同步到数据库里)，rollback清空临时文件的所有数据
4.手动设置事务回滚需要先修改mysql为手动提交：set autocommit = 0

事务的四大原则: ACID

1.原子性：每一个事务都是最小的原子单位，不能进行再分割，要么两条sql语句同时执行成功要么同时执行失败
2.一致性：要么是执行前的数据，要么是执行后的数据
3.隔离性：每一个事务都是相互隔离的,互不影响
4.持久性：只要commit以后，数据就保存到数据库了