JDBC(下)

最新推荐文章于 2022-03-14 21:47:53 发布

Will1997

最新推荐文章于 2022-03-14 21:47:53 发布

阅读量160

点赞数

分类专栏： JavaEE学习总结

本文链接：https://blog.csdn.net/qq_42371269/article/details/82025462

版权

JavaEE学习总结专栏收录该内容

23 篇文章 9 订阅

订阅专栏

JDBC中的事务：把多条sql语句当作一个整体执行（ACID）

conn.setAutoCommit( true | false )：自动提交, 默认值是true，JDBC默认每执行一条sql语句就自动提交一次

conn.commit(): 手动提交，当调用 conn.setAutoCommit( false ) 含义就是让事务手动提交, 后续的多条sql就视为一个事务

conn.rollback(): 当sql语句发生执行失败时，可以让该事务回滚到执行之前的状态

例：

Connection conn = null;
PreparedStatement stmt = null;
try {
    conn = Utils.getConnection();
    // 让事务手动提交（自己来控制事务）
    conn.setAutoCommit(false);
    stmt = conn.prepareStatement("delete from emp where empno=?");
    stmt.setInt(1, 7900);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7900");
    int i = 1/0;

    stmt.setInt(1, 7902);
    stmt.executeUpdate();
    System.out.println("delete from emp where empno=7902");

    // 让所有操作都成功了，再commit
    conn.commit();
} catch(Exception e) {
    e.printStackTrace();
    if(conn != null) {
        try {
            // 一旦有异常，回滚之前的操作
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
} finally {
    Utils.close(stmt, conn);
}

提升JDBC执行效率的几种方法

1) 充分利用预编译 PreparedStatement 对象预编译SQL语句(针对于 mysql 驱动是 8.0的）

默认情况下， JDBC执行一条SQL语句需要的步骤：
1. 将sql语句从客户端程序发送给数据库服务器 executeUpdate, exeucteQuery
2. 由命令解析器进行`词法分析`、`语法分析`、生成`解析树`
3. 如果是查询，还要生成`查询计划`，对sql执行进行优化
4. 由访问控制模块`检查权限`、生成新的`解析树`
5. 进入表管理模块，打开对应的表文件
6. 调用存储引擎，执行
7. 将结果返回给客户端程序

其中每执行一条SQL语句时，会重复 2,3,4步骤，所以非常耗时，这时就可以利用预编译重复使用 2,3,4步骤

// SQL 语句的 预编译 过程   
//这样只有在执行`prepare`的时候进行了2. 3. 4 而后面的`execute`执行时就不必执行2. 3. 4了

prepare sql1 from 'select * from student where sid = ?';

set @param:=1001;
execute sql1 using @param;

set @param:=1002;
execute sql1 using @param;

对于mysql数据库时，需要在连接字符串的后面加入以下参数： &useServerPrepStmts=true&cachePrepStmts=true

useServerPrepStmts=true 是开启MySQL的预编译功能，即`PreparedStatement对象`会利用`prepare`语句
cachePrepStmts=true 是同一个连接的多个`PreparedStatement对象`能够被缓存，否则一旦`PreparedStatement对象`关闭，则下一个`PreparedStatement对象`执行相同sql时，还是会重新执行`prepare`

static final String URL = "jdbc:mysql://localhost:3306/test3? 
                               serverTimezone=GMT%2B8&useSSL=false    
                                      &useServerPrepStmts=true&cachePrepStmts=true;

2) 批处理：

当执行的SQL语句过多时，每执行一次SQL语句就会使JDBC与服务器交互一次，这非常耗时

所以就需要采用批处理方式，先将SQL语句进行分批处理，然后再一起发送给服务器，这样就减少了JDBC与数据库的交互次数，节约了时间，提高效率

主要方法： addBatch(): SQL语句加入进批将结果集中的数据分批的返回处理包中

executeBatch(): 将批处理包一次性发送给数据库服务器

注意：
对于mysql 要启用批处理功能需要添加参数： &rewriteBatchedStatements=true

public static void main(String[] args) {
	Connection conn = null;
	PreparedStatement stmt = null;
	try {
		conn = Utils.getConnection();
		long start = System.currentTimeMillis();
		stmt = conn.prepareStatement("insert into bigtable(id,name) values(?,?)");
		int max = 500;
		for (int i = 0; i < 1501; i++) {
			stmt.setInt(1,i);
			stmt.setString(2,"aaa");

            stmt.executeUpdate(); // 调用一次exeucteUpdate方法，就会跟数据库服务器交互一次

			stmt.addBatch();         // 将insert语句加入至批处理包

			if( (i+1) % 500 == 0 ) { // 批处理包满500发一次
				stmt.executeBatch();
			}
		}
		stmt.executeBatch(); // 将批处理包中所有sql一次性发送给服务器

		long end = System.currentTimeMillis();
		System.out.println(end-start);
	} catch(Exception e) {
		e.printStackTrace();
	} finally {
		Utils.close(stmt, conn);
	}
}

3） fetchSize 指定抓包大小（跟查询有关）

mysql 默认查询，会把查询到的所有记录都包装在ResultSet中返回, 当结果记录太多时，效率，内存都会受到影响
需要添加： &useCursorFetch=true&defaultFetchSize=100 来启用基于游标的ResultSet查询

useCursorFetch=true: 启动基于游标的ResultSet查询

defaultFetchSize=100: 默认每次返回的结果集中最多只有 100 条数据

从数据库的角度提升执行SQL的效率：

建立索引：针对于查询语句

格式： create index 索引名 on 表名(列名) ；

例： create index idx_sid on test(sid) ；一般针对于经常用于查询的列

注意：

1) 对经常做查询的列建索引
2) 索引会影响增删改效率，如果查询操作远远高于增删改，适合建立索引
3) create table 表名( id int primary key )
主键列上会自动建立索引
4) 外键列上建立了索引，会有助于表连接的效率
5) 查询列如果应用了函数，则不会利用索引
select * from bigtable where abs(-2) = 263161;
数据列 -2
-1
1
2
3 会产生歧义
6) 模糊查询
select * from 表 where 列 like '%...' ; /*不会走索引*/
select * from 表 where 列 like 'aaaa%...' ; /*会走索引*/

数据库连接池：预先创建一些数据库连接，用的时候从连接池借，每次使用完连接，要还回连接池，而不是真正关闭.

连接池的接口实现： javax.sql.DataSource;

DataSource.getConnection() : 从连接池中获取一个连接（池连）

DirverManager.getConnection() : 直接与数据库建立连接（直连）

实现：各个应用程序服务器
Tomcat 内置了连接池
weblogic 内置了连接池
websphere 内置了连接池

第三方的独立连接池实现
apache dbcp
c3p0
alibaba druid 德鲁伊

连接池的作用：

1）实现连接的重用

2）限制了连接的上限，不至于把整个数据库拖垮

涉及到的设计模式：

1) 连接的重用体现了享元模式

2) close方法行为的改变体现的是装饰器模式

直连的 close() : 关闭与数据库建立的连接

池连的 close(): 将从连接池中借来的连接归还给连接池并不是 “ 真正的关闭连接”

    public static void main(String[] args) throws SQLException {

        // 使用连接池获取数据库连接
        // 1. 创建连接池对象
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false" +
                "&useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true" +
                "&useCursorFetch=true&defaultFetchSize=100");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        // 重要属性
        dataSource.setMaxActive(5); // 连接池中最大连接数
        dataSource.setMinIdle(1);   // 连接池中的最小连接数
        dataSource.setInitialSize(1); // 连接池中的初始连接数
        // 跟连接是否可用有关
        dataSource.setTestOnBorrow(true); // 从连接池取的时候检查是否有效
        dataSource.setTestOnReturn(false); // 向连接池还的时候检查是否有效
        dataSource.setTestWhileIdle(true); // 隔一段时间检查这些空闲连接是否有效
        dataSource.setTimeBetweenEvictionRunsMillis(60000); // 一分钟
        dataSource.setValidationQuery("select 1");

        // 2. 从连接池获取连接对象
        DruidPooledConnection conn = dataSource.getConnection(); // 装饰器模式

        // 3. 把连接对象还回连接池，以便其它人重用
        conn.close();
    }
}

模板模式：（ Template ）

把能够重用的代码留在方法内部，变化的部分作为参数传递进来，以实现代码的重用

参数可以是普通类型，还可以是接口类型（可以包含多条变化的语句）


抽取前：

public void insert(Student stu){
	Connection conn = null;
	PreparedStatement stmt = null;
	try {
		conn = Utils.getConnection();
		String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
		System.out.println(sql);
		stmt = conn.prepareStatement(sql);
		// 用 set方法给sql语句中的？占位符赋值
		stmt.setString(1, stu.getSname());
		Date birthday = stu.getBirthday();
		stmt.setDate(2, new java.sql.Date(birthday.getTime()));
		stmt.setString(3, stu.getSex());
		stmt.executeUpdate();
	} catch (Exception e) {
		e.printStackTrace();
	} finally{
		Utils.close(stmt,conn);
	}
}

  
抽取后：

public static void update(String sql, Object... args){ // 其中 Object... 等价于 Object[]
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = Utils.getConnection();
        System.out.println(sql);
        stmt = conn.prepareStatement(sql);
        // 用 set方法给sql语句中的？占位符赋值
        int idx = 1;
        // 遍历所有的 ? 值
        for (Object arg : args) {
            if(arg instanceof java.util.Date) {
                java.util.Date date = (java.util.Date) arg;
                java.sql.Date sdate = new java.sql.Date(date.getTime());
                stmt.setObject(idx, sdate);
            } else {
                stmt.setObject(idx, arg);
            }
            idx ++;
        }
        stmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally{
        Utils.close(stmt,conn);
    }
}

抽取后调用的例子，变得简单了：

String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex());

String sql = "update student set sname=?, birthday=?, sex=? where sid=?";
update(sql, stu.getSname(), stu.getBirthday(), stu.getSex(), stu.getSid());

String sql = "delete from student where sid=?";
update(sql, sid);