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(); // 手动回滚事务
}
例子:
java 32行
java 32行
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
- 在jdbc中使用PreparedStatement接口
- 对于mysql 需要在连接字符串上加两个参数: &useServerPrepStmts=true&cachePrepStmts=true
2.2 批处理(对增删改提升,对查询无效)
注意:
对于mysql 要启用批处理功能需要添加参数: &rewriteBatchedStatements=true
java 27行
java 27行
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);
- 对经常做查询的列建索引
- 索引会影响增删改效率,如果查询操作远远高于增删改,适合建立索引
- create table 表名( id int primary key )
主键列上会自动建立索引 - 外键列上建立了索引,会有助于表连接的效率
- 查询列如果应用了函数,则不会利用索引
select * from bigtable where abs(-2) = 263161;
-2
-1
1
2
3 - 模糊查询
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 把能够重用的代码留在方法内部,变化的部分作为参数传递进来,以实现代码的重用
参数可以是普通类型,还可以是接口类型(可以包含多条变化的语句)
例如:下面代码可以抽取为:
抽取前:
java 21行
java 21行
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);
}
}
抽取后:
java 28行
java 28行
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);
}
}
抽取后调用的例子,变得简单了:
java 9行
java 9行
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);