JDBC其他操作

事务控制

begin; // 开始事务 start transaction;
多条sql语句

commit; // 提交事务, 最终确认，让所有修改生效
rollback; // 回滚事务，撤销事务内所有的修改

jdbc 默认是让每条sql的执行作为一个独立的事务
让事务变为手动提交：

try {
    Connection.setAutoCommit(false);
    // 执行多条sql 接下来，事务会在执行第一条 insert, update, delete 时 开始

    // 提交事务
    Connection.commit();
} catch(Exception e ) {
    // 回滚事务
    Connection.rollback();
}

try(Connection conn = JdbcUtils.getConnection()) {
    try {
        // 让事务变成手动提交
        conn.setAutoCommit(false);
        PreparedStatement stmt = conn.prepareStatement("delete from student where sid = ?");
        stmt.setInt(1, 1005);
        // begin
        stmt.executeUpdate();
        int i = 1 / 0; // 出现异常
        stmt.setInt(1, 1007);
        stmt.executeUpdate();
        conn.commit(); // 手动提交事务
    } catch (SQLException e) {
        conn.rollback(); // 手动回滚事务
    }
} catch (SQLException e) {
    e.printStackTrace();
}

性能提升

1. 批量增删改

10 万

create table big (
    id int primary key auto_increment,
    name varchar(100)
);

批处理
PreparedStatement.addBatch() 加入批处理包
PreparedStatement.executeBatch() 把批处理包中所有sql，一次性发送给数据库服务器

对于mysql需要通过连接参数，开启批处理功能：
在连接字符串之后添加：

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useCursorFetch

示例代码：

try(Connection conn = JdbcUtils.getConnection()) {
    PreparedStatement psmt = conn.prepareStatement("insert into big(name) values(?)");
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 100001; i++) {
        psmt.setString(1, "张" +i);
        psmt.addBatch(); // 10001 10002 .... ... 19999 20000
        if(i % 10000 == 0) {
            psmt.executeBatch(); // 发送给服务器并清空批处理包
        }
    }
    psmt.executeBatch(); // 收尾的
    long end = System.currentTimeMillis();
    System.out.println("共花费了:" +(end-start)+"毫秒");
} catch (SQLException e) {
    e.printStackTrace();
}

2. 查询性能提升

返回记录行数 10000
PreparedStatement.setFetchSize(50);
mysql 中需要在连接参数中加入： useCursorFetch=true，来开启抓取大小的限制功能
还可以给fetchSize一个默认值： 连接参数中加入： defaultFetchSize=50
while(rs.next()) { 2001~3000

}

select * … limit 20;

提升查询性能最有效的方法，建立数据库索引
索引是对现有的数据进行排序，在排序的结果中搜索，效率会很高
索引的数据结构是:B+树
create index 索引名 on 表(列);
// 向 big 的name 建立一个索引
create index idx_name on big(name);

mysql的主键会自动创建索引，用主键作为查询条件，搜索速度最快

索引使用的注意事项：

• 使用了空间换了时间，会占用额外存储
• 索引提升查询性能的同时，影响数据的增删改( 在这张表的查询远高于增删改时，建立索引有意义 )
• null 值不适合索引，要加索引的列上一般添加一个not null约束, 并给一个默认值
• 区分度低的列不适合建索引
  男，女
  50 50
  索引值和总记录数的比例越小，区分度就高
  3 / 30万
• mysql 主键索引(聚簇索引， 把这一行的值都存储到叶子节点上) 和 普通索引（叶子节点只存储了主键的值和索引列的值）
  所以查询普通索引时，会搜索两遍，首先走普通索引，再走主键索引，（称为回表）

避免回表的办法，1. 不用滥用select * ， 2. 建立复合（组合）索引

create index idx_name on big(name, sex, age);
先按name排序，name相同的再按sex排序

where name='张1' and sex = '男'
 最左匹配规则

• 从大量的数据中找到少量数据时，才能充分利用索引

删除索引

语法：

alter table 表名 drop key 索引名
alter table big drop key idx_name;

两个列 都是姓名 值
张1 张1
…
其中一个列建索引，另一个不建索引
比较两者的效率

3. sql语句的执行流程

1. sql 发送给数据库服务器
   连接层

2. 管理连接，验证连接是否有效（连接池）
   服务层

3. 查询缓存处理，缓存中已有的数据直接返回, 从mysql 8.0开始，这个功能没有了
   select * from student 10 条学生记录放入了查询缓存

    insert ... 
    update
    delete
   

4. sql 需要词法分析、语法分析，生成语法解析树 – 由分析器完成
   select * from student where id = 1;
   select * from student where id = 2;
   select * from student where id = 3;

   select * from student where id = ?
   ? 1
   ? 2
   mysql 中默认没有打开这个功能，需要打开开关：
   useServerPrepStmts=true
   cachePrepStmts=true
   prepStmtCacheSize=25

5. 进行sql的优化处理，选择索引 – 由优化器

6. 调用存储引擎 – 执行器
   存储引擎层

7. 去索引搜索，读取磁盘数据
   mysql 默认的存储引擎是InnoDB( 支持事务，支持外键，聚簇索引按（主键顺序排列，在叶子节点存储所有记录） )
   MyISAM( 不支持事务，不支持外键, 不支持灾难恢复 )
   Memory( 内存 , hash索引, 只能支持等值比较)

4. 连接池 (connection pool)

apache dbcp 最老牌
c3p0 连接池
alibaba druid (德鲁伊) sql监控

javax.sql.DataSource 连接池接口
Conneciton conn = datasource.getConnection(); 从池中返回连接对象

conn.close(); // 一般都覆盖了关闭方法，将连接归还给连接池，而不是真正关闭
文档地址：
https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表

• 用DriverManager 获取连接，称为直连
• 用DataSource 获取连接，称为池连

DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");

//        dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 可选步骤，注册驱动
dataSource.setInitialSize(5); // 初始连接数
dataSource.setMaxActive(10); // 最大连接数
dataSource.setMinIdle(5);    // 最小连接数
dataSource.setValidationQuery("select 1"); // 一条简单的sql语句，用来保活
dataSource.setTestWhileIdle(true); // 当空闲时时不时检查一下连接的有效性, 利用ValidationQuery中的sql语句
dataSource.setTimeBetweenEvictionRunsMillis(60*1000); // 默认一分钟

try(Connection conn = dataSource.getConnection()){

    // conn.close();// 归还连接池了
}

装饰模式 decorate pattern: 在原有对象的基础上，对方法进行功能上的增强，或者是功能上的改变
原有对象和装饰对象它们实现了共同的接口，或是继承共同的父类

Reader 是父类
InputStreamReader reader =…
BufferedReader reader2 = new BufferedReader(reader1);
增强了一个方法，可以以行为单位读取数据
把两个对象中相对独立的功能进行组合，比继承的方式更为灵活

单元测试

junit （java 单元 测试工具）
测试方法的格式

1. 必须是public的
2. 没有返回值
3. 没有参数
4. 必须在方法上加一个 @Test注解

每个测试方法都可以作为一个独立的入口, 可以运行类上测试来执行类中的所有测试方法

Assert.assertEquals(期望值，实际值); 如果测试通过会显示绿色对勾, 如果测试不通过会显示黄色的叉号

junit内部使用了反射知识完成单元测试

import jdbc.JdbcUtils;
import org.junit.Assert;
import org.junit.Test;

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

public class TestJunit {

    /* 测试方法的格式
     * 1) 必须是public的
     * 2) 没有返回值
     * 3) 没有参数
     */
    @Test
    public void test1() throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        PreparedStatement psmt = conn.prepareStatement("select * from big where name=?");
        psmt.setString(1, "张434");
        ResultSet rs = psmt.executeQuery();
        rs.next();
        int id = rs.getInt("id");
        Assert.assertEquals(434, id);
    }

    @Test
    public void test2() {
        System.out.println("测试方法2");
    }
}