JDBC中事务、批量操作、大数据类型、获取自动生成的主键、等用法

1 事务的用法

事务的ACID属性：

通俗的说事务：指一组操作，要么都成功执行，要么都不执行-->原子性

在所有的操作没有执行完毕之前，其他会话不能够看到中间改变的过程-->隔离性

事务发生前，和发生后，数据的总额依然匹配-->一致性

当事务完成后，其影响应该保留下来，不能够撤销-->持久性

事务的操作：先定义开始一个事务，然后对数据做修改操作，这时如果提交（commit）,这些修改就永远保存下来，如果回退（rollback）,数据库管理系统将放弃您所做的所有修改而回到开始事务的状态。

事务：指构成单个逻辑工作单元的操作集合

事务处理：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交（commit），要么整个事务回滚（rollback）到最初状态。

处理事务的两个动作：

    提交：commit：当整个事务中，所有的逻辑单元都正常执行成功，--->提交事务，--数据已经提交不能更改。

    回滚：rollback:当整个事务中，有一个逻辑单元执行失败，--->回滚事务。
                      撤销该事务中的所有操作--->恢复到最初状态。

JDBC事务的细节：

1.在JDBC中事务是默认自动提交的，在执行DML语句的时候就已经提交事务了。

2.事务只对DML语句有效，对DQL(查询)没效果，查询不会涉及到修改数据，但是以后再Spring设置的时候，往往把查询也放到事务中，
  注意：以后做开发，方式代码没有错，但是数据插入/修改/删除不成功，首先判断事务是否提交问题，

3.回滚事务在释放资源，释放锁机制（InnoDB：行锁）。

4.在MySQL中，MyISAM不支持外键，不支持事务，InnoDB都支持。

5.事务还有很多很多的细节操作，留给Hibernate再讲讲。

在Spring中有专门的事务管理器（TransactionManager）:

1 银行转账案例-不使用事务：
@Test
    public void testTX1() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        //1.查找张无忌的账户余额是否大于等于1000块
        String sql = "SELECT * FROM account WHERE name = ? AND balance >= ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "张无忌");
        ps.setInt(2, 1000);
        ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            throw new RuntimeException("亲，你的余额不足！！");//有个特点结束代码
        }
        //2.从张无忌的账户上减少1000块钱。
        sql = "UPDATE account SET balance = balance - ? WHERE name = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, 1000);
        ps.setString(2, "张无忌");
        ps.executeUpdate();
        //模拟停电
        //System.out.print(3/0);  
        //3.从赵敏的账户上增加1000块钱。
        sql = "UPDATE account SET balance = balance + ? WHERE name = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, 1000);
        ps.setString(2, "赵敏");
        ps.executeUpdate();
        System.out.println("转账成功");
        JdbcUtil.INSTANCE.close(conn, ps, rs);
    }

2 银行转账案例-使用事务：
@Test
    public void testTX() {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.查找张无忌的账户余额是否大于等于1000块
            String sql = "SELECT * FROM account WHERE name = ? AND balance >= ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "张无忌");
            ps.setInt(2, 1000);
            rs = ps.executeQuery();
            if (!rs.next()) {
                throw new RuntimeException("亲，你的余额不足！！");//有个特点结束代码
            }
            //取消事务的自动提交机制
            conn.setAutoCommit(false);
            //===================================
            //2.从张无忌的账户上减少1000块钱。
            sql = "UPDATE account SET balance = balance - ? WHERE name = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1000);
            ps.setString(2, "张无忌");
            ps.executeUpdate();
            //模拟停电
            //System.out.print(3/0);  
            //3.从赵敏的账户上增加1000块钱。
            sql = "UPDATE account SET balance = balance + ? WHERE name = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1000);
            ps.setString(2, "赵敏");
            ps.executeUpdate();
            //提交事务
            conn.commit();
            //====================================
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            JdbcUtil.INSTANCE.close(conn, ps, rs);
        }
    }

2 批量处理操作

批量操作：当需要成批插入或者更新记录时。可以采用Java的批量跟新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交更有效率。

JDBC批量处理语句包括下面俩个方法：

addBatch(String),添加需要批量处理的SQL语句或是参数。

executeBatch():执行批量处理语句。

通常情况下我们会遇到2种批量执行SQL语句的情况：

多条SQL语句的批量处理：Statment

一个SQL语句的批量传参：PreparedStatement

需求：往account表中插入1000条数据。

结论：MySQL不支持PreparedStatment的性能优化，也不支持批量操作。

Statement：

    Statement 批处理：一次性可以执行多条SQL语句，需要编译多次。

    应用场景：系统初始化（创建表，创建数据库等）

    添加sql语句：st.addBatch(sql)  添加sql语句

    批量处理sql语句，int[] st.esecuteBatch()

    清除缓存： st.clearBatch();

PreparedStatement：

    PreparedStatement 批处理：一次性可以执行多条SQL语句，需要编译多次。

    应用场景：表数据初始化

    添加批量参数：psmt.addBatch(sql)  添加实际参数，执行之前，需要执行psmt.setXxx()设置实际参数

    执行批处理，int[] psmt.esecuteBatch()

    清除缓存： psmt.clearBatch();

    清除参数：psmt.clearParameter();

    //不使用批量操作
    //MyISAM:441ms
    //InnoDB:4125ms
    @Test
    public void testStatement() throws Exception {
        long begin = System.currentTimeMillis();
        Connection conn = JdbcUtil.INSTANCE.getConn();
        Statement st = conn.createStatement();
        for (int i = 0; i < 1001; i++) {
            String sql = "insert into account (name,balance) values ('韦小宝',28)";
            st.executeUpdate(sql);
        }
        JdbcUtil.INSTANCE.close(conn, st, null);
        System.out.println(System.currentTimeMillis() - begin);
    }

    //使用批量操作
    //MyISAM:492ms
    //Innodb:4151ms
    @Test
    public void testStatementByBatch() throws Exception {
        Long begin = System.currentTimeMillis();
        Connection conn = JdbcUtil.INSTANCE.getConn();
        Statement st = conn.createStatement();
        for (int i = 0; i < 1001; i++) {
            String sql = "insert into account (name,balance) values ('韦小宝',28)";
            st.addBatch(sql);//放入批量中
            if (i % 200 == 0) {
                st.executeBatch();//执行批量操作
                st.clearBatch();//清除缓存
            }
        }
        JdbcUtil.INSTANCE.close(conn, st, null);
        System.out.println(System.currentTimeMillis() - begin);
    }

    //不使用批量操作
    //MyISAM:473ms
    //InnoDB:4087ms
    @Test
    public void testPreparedStatement() throws Exception {
        Long begin = System.currentTimeMillis();
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "insert into account (name,balance) values (?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 1001; i++) {
            ps.setString(1, "乔峰");
            ps.setInt(2, 36);
            ps.executeUpdate();
        }
        JdbcUtil.INSTANCE.close(conn, ps, null);
        System.out.println(System.currentTimeMillis() - begin);
    }

    //使用批量操作
    //MyISAM:470sm
    //InnoDB:4140sm
    @Test
    public void testPreparedStatementByBatch() throws Exception {
        Long begin = System.currentTimeMillis();
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "insert into account (name,balance) values (?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 1001; i++) {
            ps.setString(1, "乔峰");
            ps.setInt(2, 36);
            ps.addBatch();//添加批量参数
            if (i % 200 == 0) {
                ps.executeBatch();//执行批量操作
                ps.clearBatch();//清除缓存
                ps.clearParameters();//清除参数
            }
        }
        JdbcUtil.INSTANCE.close(conn, ps, null);
        System.out.println(System.currentTimeMillis() - begin);
    }

3 大数据类型

TinyBlob, Blob, MediumBlob, LongBlob 都是二进制类型。

唯一不同的就是容量不同，可以把二进制的数据保存到数据库，比如把一个音频，视频，图片存到数据库中。

注意：开发中我们往往把二进制文件的保存路径存储到数据库中，而不是把数据存储到数据库。

比如：上传一张图片：保存到当前项目下的：/upload/12345.png

---

id name email headImg

1 乔峰 qiao /upload/qiao.png

2 阿朱 ah /upload/ah.png

TEXT系列：保存文字比较多（博客/小说）

TinyTEXT, TEXT, MediumTEXT, LongTEXT

对应着java中的String 在Java代码中没有变化。

    //把图片写入到数据中
    @Test
    public void testWrite() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "INSERT INTO image (img) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        InputStream in = new FileInputStream("C:/three.jpg");
        ps.setBlob(1, in);
        ps.executeUpdate();
        JdbcUtil.INSTANCE.close(conn, ps, null);
    }

    //把图片从数据库中读取出来
    @Test
    public void testRead() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "SELECT * FROM image WHERE id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 1);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Blob blob = rs.getBlob("img");
            //从数据库中读出来
            InputStream in = blob.getBinaryStream();
            //保存到磁盘的文件中：文件拷贝操作
            Files.copy(in, Paths.get("D:/123.png"));
        }
        JdbcUtil.INSTANCE.close(conn, ps, rs);
    }

    //插入文档
    @Test
    public void testWrite1() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "INSERT INTO image (img) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        InputStream in = new FileInputStream("C:/story.txt");
        ps.setBlob(1, in);
        ps.executeUpdate();
        JdbcUtil.INSTANCE.close(conn, ps, null);
    }


    //把文档从数据库中拿出来
    @Test
    public void testRead1() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "SELECT * FROM image WHERE id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 4);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Blob blob = rs.getBlob("img");
            //从数据库中读出来
            InputStream in = blob.getBinaryStream();
            //保存到磁盘的文件中：文件拷贝操作
            Files.copy(in, Paths.get("D:/123.txt"));
        }
        JdbcUtil.INSTANCE.close(conn, ps, rs);
    }

4 获取自动生成的主键

Statement:

int executeUpdate(String sql):执行DML/DDL语句。

int executeUpdate(String sql, int autoGeneratedKeys):

参数：autoGeneratedKeys:是否需要返回自动生成的主键

    Statement.RETURN_GENERATED_KEYS:要返回

    Statement.NO_GENERATED_KTY:不返回

ResultSet getGeneratedKeys():获取自动生成的主键

PreparedStatement:

PreparedStatement prepareStatement(String sql) ：执行DML/DDL语句。

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) ：

参数：autoGeneratedKeys:是否需要返回自动生成的主键

    Statement.RETURN_GENERATED_KEYS:要返回

    Statement.NO_GENERATED_KTY:不返回

    @Test
    public void testStatement() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        Statement st = conn.createStatement();
        String sql = "insert into t_student (name) values ('A')";
        st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = st.getGeneratedKeys();
        if(rs.next()){
            long id = rs.getLong(1);
            System.out.println(id);
        }
        JdbcUtil.INSTANCE.close(conn, st, rs);
    }

    @Test
    public void testPreparedStatement() throws Exception {
        Connection conn = JdbcUtil.INSTANCE.getConn();
        String sql = "insert into t_student (name) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, "B");
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        if(rs.next()){
            Long id = rs.getLong(1);
            System.out.println(id);
        }
        JdbcUtil.INSTANCE.close(conn, ps, rs);
    }