jdbc基础3提高知识查漏

1. 事务

把多条sql语句看做一个整体执行，ACID（原子性，一致性，隔离性，持久性）

Connection conn = ...
conn.setAutoCommit( true | false );

// 自动提交, 默认值是true
所谓的自动提交，就是在每条执行的增删改sql语句之后，由jdbc自动加一条commit语句，让事务提交（更改生效）

stmt.executeUpdate("insert into..."); // commit;
stmt.executeUpdate("update ..."); // commit;
stmt.executeUpdate("delete ..."); // commit;

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

try {
	stmt.executeUpdate("insert into...");  // ok	
	stmt.executeUpdate("update ..."); // 异常
	conn.commit(); // 手动提交事务（更改生效）
} catch(Exception e) {
	conn.rollback(); // 手动回滚事务
}

例子：

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);
}

2. 效率提升

2.1 充分利用预编译sql

1. 在jdbc中使用PreparedStatement接口
2. 对于mysql 需要在连接字符串上加两个参数： &useServerPrepStmts=true&cachePrepStmts=true

2.2 批处理(对增删改提升,对查询无效)

注意：
对于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);
	}
}

2.3 fetchSize 抓包大小(跟查询有关)

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

2.4 从数据库角度考虑效率提升

对于查询，建立索引：
create index 索引名字 on 表名(列名);
create index idx_bigtable_id on bigtable(id);

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%…’ ; /会走索引/

ctrl

2.5 数据库连接池

预先创建一些数据库连接，用的时候从连接池借，每次使用完连接，要还回连接池，而不是真正关闭.
连接池的接口和实现：
javax.sql.DataSource; // 接口
DataSource.getConnection() // 从连接池获取一个空闲的连接， 池连
DriverManager.getConnection() // 直接与数据库建立新的连接， 直连

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

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

小结：
连接池的作用：1. 实现连接的重用 2. 限制了连接的上限，不至于把整个数据库拖垮
涉及到的设计模式：1) 连接的重用体现了享元模式 2) 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);