Learn JDBC-03

前言

上面的一节中主要是说了Java和SQL中对应的类型，还有如何使用PreparedStatement对Blob类型的数据进行存取，最后我们还提到了如果进行高效的批量插入的操作。首先我需要使用PreparedStatement进行操作，因为这是经过了预编译的。然后我们使用Batch批处理的方式，极大的提高了批量插入的效率。最后一步，关闭MySQL的自动提交setAutoCommit(false)。(Oracle默认就是关闭自动提交的。如果不主动提交的话，数据就会消失。这点就很操蛋。)不过AutoCommit这个东西的用途不仅仅是用在这个批量插入上面来，其实这个东西还有更大的用途。这就是下面要说的事务。

事务

事务的简单介绍

上面说到了SQL语句是自动提交的，但是有时候我们需要几个SQL语句作为一个整体，要么这个整体执行成功，要么是全都不执行。比如说，甲向乙转账一百元。可以分为以上的三个步骤，首先查询甲的账号余额是否还有一百，然后甲的余额减去一百，最后乙的余额减去一百。重点就在后面的两步。如果是转换成为SQL语句的话，就是

update person set money = money - 100 where name = 'jia';

update person set money = money + 100 where name = 'yi';

上面的两条语句应该就是上面我们说的事务的范畴。这两个语句要么就是都成功了，要么就是都失败。如果第一行执行了，到了第二行出现了异常，程序崩溃了。那么甲的一百块没了，但是乙并没有收到这一百块，这个问题就很大了，以后就没人去这个银行存钱或者进行转账了，毕竟这次是一百，下次可能就是一百万。不过该如何解决这个问题呢？我们可以在遇到异常的时候，把钱给甲转回去。这个方式肯定是不可行的。当我们系统出现异常的时候，如果我们之前做过了系统备份，我们就可以将系统还原到备份的那个时候。根据这个思路，我们可以在执行语句之前做一个备份，如果中途出现了异常，我们就还原到备份之前的状态。这样就不会出问题了。这其实就是数据库事务的处理的方式。

不过我们首先需要做的是让数据不可以自动提交，因为数据一旦提交了，就没办法回到之前的状态。数据在一下的两种状态是会提交的。

默认情况下，执行一个SQL语句就会自动提交一次。
当数据库连接关闭的时候会提交。

其中第一点，我们可以通过之前说的那个setAutoCommited(false)关闭默认的自动提交，使用commit方式手动提交数据。第二点，我们需要保持数据库的连接不要中断。这就要保证了执行上面两条SQL语句的必须是同一个连接，我们要将同一个Connection传入两个方式，最后关闭。

如果遇到了异常，我们需要进行数据的回滚。调用的是rollback方法。既然是出现了异常才会调用，那么这个方法必然是在catch语句块中进行调用的。

其中，上面的操作写成代码就是如下这个样子哒。

@Test
public void test() {
    Connection conn = null;
    try {
        conn = JDBCUtils.getConnection();
        conn.setAutoCommit(false);

        String sql1 = "update person set money = money - ? where name = ?";
        JDBCUtils.update(conn, sql1, 100, "jia");

//            System.out.println(10/0);

        String sql2 = "update person set money = money + ? where name = ?";
        JDBCUtils.update(conn, sql2, 100, "yi");

        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();

        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } finally {
        try {
            if (conn != null) {
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        JDBCUtils.closeResource(conn);
    }
}

完成事务的时候尤其需要注意需要取消自动提交，还有就是必须保证是同一个连接，连接不能断开，如果断开了就会自动提交了。不过上面的代码还有一点是需要注意的。那就是finally语句中的setAutoCommit(true)，再次开启自动提交。既然下面都开关闭连接了，为什么还要做一个多余的动作呢？这个就要说到下面要说的数据库连接池这个东西了。和线程池是十分类似的，建立数据库的连接也十分的耗时。所以我们最好是维护一个连接池，当我们关闭连接之后，连接可能只是回到连接池中等待下一次的调用的。但是下一次的调用不一定是知道取消了自动提交的，所以我们需要将连接恢复到使用之前的状态。

事务与隔离

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

上面是事务的四个基本的属性。其中第一二四是非常容易理解的。难以理解的就是第三点——多个事务之间的关系。如果有多个事务同时操作同一个数据库，那么就会出现一定的问题。其中主要就是以下的三个问题。

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

其中，我们主要需要解决的问题是脏读和不可重复读。如果要解决幻读的话，数据库的效率就会变得特别的差，而且一般情况下我们只需要解决前面的两种就行了。MySQL默认替我们解决了不可重复读的问题，Oracle默认解决的是脏读的问题。

如果解决这些问题就需要对数据库的隔离属性进行一些设置了。

其中总共是分为四个级别。

READ UNCOMMITED 什么问题都没有解决。
READ COMMITED 解决了脏读的问题。(Oracle默认)
REPEATABLE READ 解决了不可重复读的问题。(MySQL默认)
SERIALIZABLE 解决了幻读的问题，但是直接变成了串行了，效率不高。

其中我们可以通过如下的方法来获取和设置隔离的级别。

System.out.println(conn.getTransactionIsolation() == Connection.TRANSACTION_REPEATABLE_READ);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
System.out.println(conn.getTransactionIsolation() == Connection.TRANSACTION_SERIALIZABLE);

DAO

所谓的DAO指的就是Date Access Object。是Java中用来访问数据库中的信息的类与接口。包括了对数据的CRUD（Create、Retrival、Update、 Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO 。

其实我们上面已经做了DAO的一部分的操作了，就是我们之前写的那个JDBCUtils那个类。基本上就是涵盖了我们需要的所有的方法。不过我们还是需要进行一些改进。

其中获取连接关闭的连接的方式需要取掉，然后方法也不需要是静态的方法。我们可以指定泛型，而不是查询的时候给定参数Class对象。

package com.sher.dao;

import com.sher.java1.Demo1;
import com.sher.java1.JDBCUtils;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.*;

import static com.sher.java1.JDBCUtils.closeResource;

/**
 * @author SHeR
 * @time 10/28/2019 6:16 PM
 * @describe
 */
@SuppressWarnings("all")
public class BaseDao1<T> {

    private Class<T> type;

    public BaseDao1() {
        Class<? extends BaseDao1> clazz = this.getClass();
        ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
        this.type = (Class<T>) actualTypeArguments[0];
    }


    public int update(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }
        int res = ps.executeUpdate();
        closeResource(null, ps, null);
        return res;
    }

    public T getInstance(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }

        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        T t = null;
        if (rs.next()) {
            t = type.getConstructor().newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object o = rs.getObject(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);
                Field field = type.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, o);
            }
        }
        closeResource(null, ps, rs);
        return t;
    }

    public List<T> getInstances(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }

        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<T> list = new ArrayList<>();
        while (rs.next()) {
            T t = type.getConstructor().newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object o = rs.getObject(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);
                Field field = type.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, o);
            }
            list.add(t);
        }
        closeResource(null, ps, rs);
        return list;
    }

    public Map<String, Object> getMapInstance(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }

        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        if (rs.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < columnCount; i++) {
                Object o = rs.getObject(i + 1);
                String label = metaData.getColumnLabel(i + 1);
                map.put(label, o);
            }
            return map;
        }
        closeResource(null, ps, rs);
        return null;
    }

    public List<Map<String, Object>> getMapInstances(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }

        ResultSet rs = ps.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<Map<String, Object>> list = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < columnCount; i++) {
                Object o = rs.getObject(i + 1);
                String label = metaData.getColumnLabel(i + 1);
                map.put(label, o);
            }
            list.add(map);
        }
        closeResource(null, ps, rs);
        return list;
    }

    public Object getValue(Connection conn, String sql, Object... objs) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < objs.length; i++) {
            ps.setObject(i + 1, objs[i]);
        }

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Object object = rs.getObject(1);
            return object;
        }
        closeResource(null, ps, rs);
        return null;
    }
}

上面的BaseDAO其实是共有的，任何类型都是可以使用的。针对我们上面说到的一个表，我们需要另外的提供一个接口，要求实现里面的一些操作。

public interface StudentDao1 {
    String getName(Connection conn, int id);

    int getId(Connection conn, int name);

    Student getStudentByid(Connection conn, int id);

    Student getStudentByid(Connection conn, int min, int max);

    Student getStudentByName(Connection conn, String name);

    boolean addStudent(Connection conn, Student student);

    boolean removeStudentById(Connection conn, int id);

    boolean removeStudentByName(Connection conn, String name);

    boolean changeName(Connection conn, int id, String name);

    List<Student> getAllStudentsList(Connection conn);

    List<Map<String, String>> getAllStudentsMap(Connection conn);
}

上面就是简单的写一写，对于这个表我们需要什么样的操作。最后我们需要继承上面的BaseDAO然后实现上面的这个接口。

package com.sher.dao;

import com.sher.java1.Student;

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

/**
 * @author SHeR
 * @time 10/28/2019 6:40 PM
 * @describe
 */
public class StudentDAOImpl extends BaseDao1<Student> implements StudentDao1 {

    @Override
    public String getName(Connection conn, int id) {
        String sql = "select username from test where id = ?";
        try {
            Object value = getValue(conn, sql, id);
            return (String) value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int getId(Connection conn, int name) {
        String sql = "select id from test where username = ?";
        try {
            Object value = getValue(conn, sql, name);
            return (int) value;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Student getStudentByid(Connection conn, int id) {
        String sql = "select * from test where id = ?";
        try {
            return getInstance(conn, sql, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> getStudentByid(Connection conn, int min, int max) {
        String sql = "select * from test where id between ? and ?";
        try {
            return getInstances(conn, sql, min, max);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Student getStudentByName(Connection conn, String name) {
        String sql = "select * from test where username = ?";
        try {
            return getInstance(conn, sql, name);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void addStudent(Connection conn, Student student) {
        String sql = "insert into test values(?, ?)";
        try {
            update(conn, sql, student.getId(), student.getUsername());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void removeStudentById(Connection conn, int id) {
        String sql = "delete from test where id = ?";
        try {
            update(conn, sql, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void removeStudentByName(Connection conn, String name) {
        String sql = "delete from test where username - ?";
        try {
            update(conn, sql, name);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void changeName(Connection conn, int id, String name) {
        String sql = "update test set username = ? where id = ?";
        try {
            update(conn, sql, name, id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Student> getAllStudentsList(Connection conn) {
        String sql = "select * from test";
        try {
            return getInstances(conn, sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getAllStudentsMap(Connection conn) {
        String sql = "select * from test";
        try {
            return getMapInstances(conn, sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

通过这种方式我们就可以使用经过封装之后的StudentDAOImpl来处理数据了。完全不用使用任何的SQL语句，因为SQL语句已经被封装到这个类中了。我们可以像使用Java中其他的类一样的使用这个类。不过需要注意的是每次调用方法的时候，我们还是需要将一个连接传入到方法当中去。

使用DAO基本就是一个模版的格式。首先是BaseDAO，这个东西只需要一个，因为这个是所有的类都可以使用的，而且这个类带上了泛型。然后我们需要针对特别的表，创建对应的JavaBean对象，分析我们需要使用到什么样的方法，编写XXXDAO的接口。最后继承BaseDAO<XXX>并且实现XXXDAO接口，这个类一般叫做XXXDAOImpl。通过上面的方式我们就完成了一个比较完整的一个DAO了。JDBC的学习基本上就要在此告一段落了。下面我们还需要学习的就是几种数据库连接池，还有就是apache的一个包DBUtils，就像之前说的那个Commons-IO一样，很多功能别人已经替我们实现了。不过我们还是需要自己写一遍才能明白其中的原理。

总结

上面简单的介绍了数据库中的事务以及隔离级别，还有就是进一步对jdbc进行封装，写了一个DAO对象。不过这个DAO对象在之后还是需要进行改进的。

Learn JDBC-03

Learn JDBC-03

前言

事务

事务的简单介绍

事务与隔离

DAO

总结

Learn JDBC-04

Learn JDBC-02

Hony Sher