Java学习--深入JDBC

一、事务控制

JDBC连接也提供了事务支持,JDBC连接 支持的事务由Connection提供,Connection默认打开自动提交,即关闭事务,在这种情况下,每条SQL语句一旦执行,便会立即提交到数据库,永久生效,无法对其进行回滚操作。

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)

4.1连接池的种类:

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

4.2

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

conn.close(); // 一般都覆盖了关闭方法,将连接归还给连接池,而不是真正关闭

  • 用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();// 归还连接池了
}
展开阅读全文

没有更多推荐了,返回首页