JDBC进阶—— 师承尚硅谷（DAO）

JDBC 数据事务

1. 事务处理

介绍事务：
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。（AA给BB转账100，更CCDD没关系）

一致性:事务处理的时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

事务回滚：回滚到上一次提交之后的状态。

以AA给BB 转账100为例：
上代码：
更新了update代码：

    /**
     * 考虑事务 --增删改
     * 事务处理的原则：要么流畅的全部执行；要么在中途报错，所有单位回到原来的状态
     * 数据一旦提交就不能回滚
     *
     * 哪些操作会导致数据的自动提交？？
     *  >DDL操作都会自动提交
     *      >set autocommit = false 对DDL操作失效
     *  >DML默认情况下，一旦执行，自动提交
     *      >可以通过set autocommit = false 取消他的自动提交
     *  >默认在关闭连接时，会自动提交数据
     */
    public int update(Connection connection, String sql, Object... args) throws SQLException {
        //预编译sql语句,填充占位符
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //执行
        int n = ps.executeUpdate();
        //3.关闭，（除了连接，其他资源都关闭）
        JDBCUtils.closeResource(null, ps);

        return n;
    }

测试：

    @Test   //事物测试
    public void test_updateAffairs() throws SQLException {
        Connection connection = null;
        try {
            //开始事务
            connection = JDBCUtils.getConnection();
            //关闭自动提交
            connection.setAutoCommit(false);

            //进行数据库操作  AA向BB转账100
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            int aa = update(connection, sql1, "AA");

//            //模拟网络一场
//            System.out.println(10/0);

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            int bb = update(connection, sql2, "BB");

            //若没有异常，提交事务
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //有异常，回滚
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                //回复自动提交
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }

            //关闭connection资源
            JDBCUtils.closeResource(connection,null);//ps已经在update运行结束的时候被关闭了
        }

    }

效果：

模拟网络错误的时候，表格不变。

2. 数据库的并发问题和隔离级别

并发问题：同时运行多个事务的时候出现的问题

• 脏读: 对于两个事务 T1, T2,；T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚,
  T1读取的内容就是临时且无效的。
• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如 果 T1再次读取同一个表, 就会多出几行。

神仙一集，讲的非常清晰

数据库提供的四个隔离级别

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READCOMMITED 。
Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为:REPEATABLE READ。

相关的隔离操作：

1.查看当前的隔离级别:SELECT @@tx_isolation;

2.设置当前mysql的隔离级别：set transaction isolation level read committed;

3.设置数据库系统的全局隔离级别：
set global transaction isolation level read committed;

补充操作：
1.创建mysql用户：create user tom identified by 'abc123'; (tom，abc123是密码)

2.授权：

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123 
grant all privileges on *.* to tom@'%' identified by 'abc123';
#给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限.
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';`

---

JDBC DAO相关

ok这里重点理解。

1. DAO介绍

简介：全部看完了，再来看看简介感觉很舒服（我又要说了，就像是三九北风熬了一碗鲫鱼汤那般）

• DAO（BaseDAO）：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、 Delete），而不包含任何业务相关的信息
• 作用：为了实现功能的模块化，更有利于代码的维护和升级。

层次结构分析：

2. 针对customer表，演示DAO

上代码：
1.BaseDao类：（顶层通用的类，包含了update增删改方法、查询单条多条数据方法、特殊查询方法）

package com.JDBC_Advanced.after;

import com.JDBC_Advanced.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 访问数据信息的类和接口,模块化升级
 */
public abstract class BaseDao {
    //增删改，考虑事务
    public int update(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(null, ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    //查一条数据
    public <T> T queryForOne(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            if (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    Object value = rs.getObject(rsmd.getColumnLabel(i + 1));
                    //反射
                    Field declaredField = clazz.getDeclaredField(rsmd.getColumnLabel(i + 1));
                    declaredField.setAccessible(true);
                    declaredField.set(t, value);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(null, ps, rs);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    //查询多条数据
    public <T> List<T> queryForMore(Connection connection, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            ArrayList<T> list = new ArrayList<>();

            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    Object value = rs.getObject(columnLabel);
                    //反射
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(null, ps, rs);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    //查询特殊值？
    public <E> E getValue(Connection connection, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(null, ps, rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

2.Customer类:（针对的是表customers）

package com.JDBC_Advanced.after;
/**
 * 针对customer表而创建的类
 */

import java.sql.Date;

public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

3.CustomerDao接口：（规范针对Customer表的操作）

package com.JDBC_Advanced.after;
/**
 * 此接口用于规范 针对customer表的常用操作
 */

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

public interface CustomerDao {

    //插入一条记录
    void insert(Connection connection, Customer customer) throws SQLException;

    //删除一条记录--byID
    void deleteById(Connection connection, int id);

    //修改指定数据 --针对内存中的cust对象
    void update(Connection connection, Customer customer);

    //查询一条记录 ---根据id
    Customer getCustomerById(Connection connection, int id);

    //查询所有记录
    List<Customer> getAllCustomers(Connection connection);

    //返回数据表中的数据的条目数
    Long getCount(Connection connection);

    //返回最大生日
    Date getMaxBirth(Connection connection);
}

4.CustomerDaoImpl：（接口实现类，对于接口里的方法的具体实现）

package com.JDBC_Advanced.after;
/**
 * 对于接口里的方法的具体实现
 */

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

public class CustomerDaoImpl extends BaseDao implements CustomerDao {//这里的泛型指明了是操作的Customer类，所以后续可以选择更改删除参数中的Customers.class
    @Override
    public void insert(Connection connection, Customer customer) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from customers where id = ?";
        update(connection, sql, id);
    }

    @Override
    public void update(Connection connection, Customer customer) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = queryForOne(connection, Customer.class, sql, id);
        return customer;
    }

    @Override
    public List<Customer> getAllCustomers(Connection connection) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> list = queryForMore(connection, Customer.class, sql);
        return list;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        return getValue(connection, sql);
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from customers";
        return getValue(connection, sql);
    }
}

5.CustomerDaoImplTest类：（测试类，用的是@Test）
中间有一个快捷键生成的测试类的，如下图

package com.JDBC_Advanced.after;

import com.JDBC_Advanced.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

public class CustomerDaoImplTest {

    private CustomerDaoImpl dao = new CustomerDaoImpl();
    
    @Test
    public void insert() {

        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customer customer = new Customer(1, "文二牛", "wenerniu@11.com", new Date(2012 - 1 - 1));
            dao.insert(connection, customer);
            System.out.println("添加成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void deleteById() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            dao.deleteById(connection, 8);//删除8号
            System.out.println("删除成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void update() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customer customer = new Customer(12, "文三牛", "wen三niu@11.com", new Date(2012 - 11 - 1));
            dao.update(connection, customer);
            System.out.println("修改成功！");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void getCustomerById() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Customer customer_query = dao.getCustomerById(connection, 16);
            System.out.println("查询结果为----");
            System.out.println(customer_query);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void getAllCustomers() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            List<Customer> allCustomersList = dao.getAllCustomers(connection);
            System.out.println("查询全部-----");
            allCustomersList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void getCount() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Long count = dao.getCount(connection);
            System.out.println("计数结果------" + count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void getMaxBirth() {
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection();
            Date maxBirth = dao.getMaxBirth(connection);
            System.out.println("年龄最小为（birth值最大）------" + maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtils.closeResource(connection, null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

效果：
添加成功！

删除成功

修改成功

查询16号朱茵

查询全部

总计12条数据

birth最大值

3. 优化Dao

不太好理解
优化思路：从原来的代码中有所重复（如下图），（既然是针对Customer了，没必要在此处写获取类）

优化方法：
上代码**（代码注释中详细解析）**
把如下代码加入到BaseDao类开头：

    /**
     *出现对象之前可以给子类赋值的位置有哪些？---？？？
     * 获取当前对象父类的泛型
     */
    {
        //作用：当前BaseDAO的子类继承了父类中的泛型、、、靠杯啊真离谱
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;

        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();//获取了父类的泛型参数
        clazz = (Class<T>) actualTypeArguments[0];//泛型的第一个参数
    }

2.更改掉用到.class() 和相关的clazz

package com.JDBC_Advanced;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;

public class CustomerDaoImpl extends BaseDao<Customer> implements CustomerDao {//这里的泛型指明了是操作的Customer类，所以后续可以选择更改删除参数中的Customers.class

/*      为什么不写在这里呢？？
    -------如果写在这里的话，每次针对不同的表格（比如说order表customers表book表），都需要在这里写，
    所以把他提炼出来，放到最顶级的父类里（也就是BaseDao中）：这就充分的体现了优化的效果
    ----原理：继承。每次子类实例化一个对象的时候，都先从父类里面怎么怎么样....他们说这是多态！

    ------因为你每次针对不同的表格 都要写不同的类（就是Customer类），然后要写这个类对应的接口（也就是CustomerDao接口，里面有具体的想要操作的方法都在里面添加）
        然后，每次都要写这个接口的实现类（也就是这里的CustomerDaoImpl，），每次到这里都要写一遍以下“获取父类泛型”的代码，
        所以，把他提炼出来，放到最顶层的BaseDao类中（这个类是包含了所有的通用的东西，比如update方法啊，查询啊，还有这里的父类泛型也是提取出的）
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;

        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();//获取了父类的泛型参数
        clazz = actualTypeArguments[0]
    }
    */

    @Override
    public void insert(Connection connection, Customer customer) {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {
        String sql = "delete from customers where id = ?";
        update(connection, sql, id);
    }

    @Override
    public void update(Connection connection, Customer customer) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = queryForOne(connection, sql, id);
        return customer;
    }

    @Override
    public List<Customer> getAllCustomers(Connection connection) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> list = queryForMore(connection, sql);
        return list;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        return getValue(connection, sql);
    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from customers";
        return getValue(connection, sql);
    }
}

---

数据库连接池相关技术

真正开发中用到的连接数据库的技术。

1. 引入

上述学习的传统连接方式的问题：

• 连接数据库没有得到很好的利用，频繁的连接占用资源，导致系统崩溃
• 对于每次数据库连接都要断开。否则数据泄露
• 不能控制被创建的对象数量，连接过多直接崩

数据库连接池技术：（地铁班车，定时发车，一次顶多运输那么多数据，统一断开连接，等车…）

2. Druid数据库连接池

主流技术，
上代码：

    @Test
    public void Druidconn() throws Exception {
        InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("Druid.properties");
        Properties properties = new Properties();
        properties.load(is);

        DataSource source = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = source.getConnection();
        System.out.println(connection);
    }

其中的配置文件：（相关的参数查阅网络吧）

url=jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true
username=root
password=123456
driverClass=com.mysql.cj.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

效果：

Apache-DBUtils实现CRUD操作（第三方）

在maven导入dbutils

依赖：

<dependency>
	<groupId>commons-dbutils</groupId>
	<artifactId>commons-dbutils</artifactId>
	<version>1.6</version>
</dependency>

效果：

测试DBUtils

1. 添加数据：

上代码：

    @Test
    public void testInsert() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection_Druid();

        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        int count = queryRunner.update(connection, sql, "文五牛", "555@com", "2015-5-5");

        System.out.println("添加了" + count + "条记录。");
    }

效果：

2. 查询一条数据

上代码：

    //查询一条
    @Test
    public void testQueryInstance() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection_Druid();

        String sql = "select id, name, email, birth from customers where id = ?";

        //
        BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);//这里就是rs
        Customer customer = queryRunner.query(connection, sql, handler, 1);
        System.out.println(customer);
        JDBCUtils.closeResource(connection, null);
    }

效果：

3. 查询多条数据

上代码：

    //查询多条记录
    @Test
    public void testQueryInstances() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection_Druid();

        String sql = "select id, name, email, birth from customers";

        //
        BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
        //BeanListHandler：是ResultSetHandler接口类的实现类，用于封装表中的多条记录

        List<Customer> list = queryRunner.query(connection, sql, listHandler);
        list.forEach(System.out::println);
    }

效果：

4. 特殊值查询

上代码：

    //特殊值查询
    /**
     * * 如何查询类似于最大的，最小的，平均的，总和，个数相关的数据，
     * * 使用ScalarHandle
     */
    @Test
    public void testQueryforSpecial() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = JDBCUtils.getConnection_Druid();

        String sql = "select count(*) from customers";
        ScalarHandler<Object> handler = new ScalarHandler<>();
        Object query = queryRunner.query(connection, sql, handler);
        System.out.println("数据总数：" + query);

        JDBCUtils.closeResource(connection, null);

    }

效果：

5. 自定义实现类

上代码：

    //自定义实现类
    @Test
    public void testByOwn() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from customers";

        ResultSetHandler<List<Customer>> handler = new ResultSetHandler<List<Customer>>() {
        //匿名内部类
            @Override
            public List<Customer> handle(ResultSet resultSet) throws SQLException {

                ArrayList<Customer> list = new ArrayList<>();
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String email = resultSet.getString("email");
                    Date birth = resultSet.getDate("birth");

                    Customer customer = new Customer(id, name, email, birth);
                    list.add(customer);
                }
                return list;
            }
        };

        List<Customer> queryList = queryRunner.query(connection, sql, handler);
        queryList.forEach(System.out::println);
    }

效果：

6. 关闭资源

    //DBUtils关闭资源
    public void closeResource_DBUtils(Connection connection, Statement ps, ResultSet rs) {
        DbUtils.closeQuietly(connection);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }