文档章节

JDBC批处理Select语句

sms95
 sms95
发布于 2015/04/26 09:22
字数 2102
阅读 4
收藏 0

在网络上开销最昂贵的资源就是客户端与服务器往返的请求与响应,JDBC中类似的一种情况就是对数据库的调用,如果你在做数据插入、更新、删除操作,可以使用executeBatch()方法减少数据库调用次数,如:

1
2
3
4
5
Statement pstmt = conn.createStatement();
pstmt.addBatch( "insert into settings values(3,'liu')" );
pstmt.addBatch( "insert into settings values(4,'zhi')" );
pstmt.addBatch( "insert into settings values(5,'jun')" );
pstmt.executeBatch();

但不幸的是对于批量查询,JDBC并没有内建(built-in)的方法,而且JDBC执行批处理的时候也不能有SELECT语句,如:

1
2
3
Statement pstmt = conn.createStatement();
pstmt.addBatch( "select * from settings" );
pstmt.executeBatch();

会抛出异常:

1
2
3
4
Exception in thread "main" java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate().
    at com.mysql.jdbc.Statement.executeBatch(Statement.java: 961 )
    at test.SelectBatchTest.test2(SelectBatchTest.java: 49 )
    at test.SelectBatchTest.main(SelectBatchTest.java: 12 )

假设你想从一系列指定的id列表中获取名字,逻辑上,我们要做的事情看起来应该是:

1
2
3
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (?)" );
stmt.setString( "1,2,3" );

但是这样做并不能得到预期的结果,JDBC只允许你用单个的字面值来替换“?” JDBC之所以这么做是有必要的,因为如果SQL自身可以改变的话,JDBC驱动就没法预编译SQL语句了,另一方面它还能防止SQL注入攻击。

但有四种可替代的实现方法可供选择:

  1. 分别对每个id做查询

  2. 一个查询做完所有事

  3. 使用存储过程

  4. 分批处理

方法一: 分别对每个id做查询

假设有100个id,那么就有100次数据库调用:

1
2
3
4
5
6
7
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id = ?" );
for ( int i= 0 ; i < 100 ; i++ ) {
  stmt.setInt(i);   // or whatever values you are trying to query by
  // execute statement and get result
}

这种方法写起来非常简单,但是性能非常慢,数据库往返要处理100次。

方法二:一个查询完成所有事

在运行时,你可以使用一个循环来构建如下SQL语句:

1
2
3
4
5
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (?, ?, ?)" );
stmt.setInt( 1 );
stmt.setInt( 2 );
stmt.setInt( 3 );

这种方案从代码相比第一种方法算是第二简单的,它解决了来回多次请求数据库的问题,但是如果每次请求参数的个数不一样时预处理语句就必须重新编译,由于每次SQL字面值不匹配,因此如果分别用10个id、3个id、100个,这样会在缓存中产生三个预处理语句。除了重新编译预处理语句之外,先前缓存池中的预处理语句将被移除(受限于缓存池大小),进而导致重新编译已编译过的语句。最后,这种查询方式在内存溢出或磁盘分页操作时查询会占用很长时间。

该方案的另一种变体就是在SQL语句中硬编码:

1
2
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (1, 2, 3)" );

这样方式甚至更差,而且没有任何机会对SQL语句重用,至少用“?”还可以对使用相同数量参数的SQL语句进行重用。

1
2
3
4
5
6
7
PreparedStatement stmt = conn.prepareStatement(
  "select id, name from users where id in (?) ; "  
  + "select id, name from users where id in (?); "
  + "select id, name from users where id in (?)" );
stmt.setInt( 1 );
stmt.setInt( 2 );
stmt.setInt( 3 );

这种方法的优点就是每次查询模版语句都一样,数据库不需要每次计算执行路径。然而,从数据库驱动的角度来说SQL每次都不一样,预处理语句每次必须预处理保存在缓存中。而且不是所有数据库系统都支持分号间隔的多个SQL语句的

方法三:使用存储过程

存储过程执行在数据库系统中,因此它可以做很多查询而不需要太多网络负载,存储过程可以收集所有结果一次性返回。这是一种速度很快的解决方案。但是它对数据库的依赖比较强,不能随意的切换数据库系统,否则需要重写存储过程而且需要你分离应用服务器与数据库服务器之间的逻辑。如果应用架构已经使用了存储过程,无疑这是只最佳方案。

方法四:分批处理

批量查询是方案一和方案二的折衷选择,它预先确定一批查询参数的常量,然后用这些参数构建一批查询。因为这只会涉及到有限个查询,所以它有预处理语句的优势(预编译不会与缓存中的预处理发生碰撞)。批处理多个值在相同的查询保留了服务器来回请求最小化的优势。最后你可以通过控制批处理的上限来避免大查询的内存问题。如果你有很关键的查询对性能方面有要求又不想用存储过程,那么这是一种很好的解决办法,现在我们通过一个例子说明:

1
2
3
4
public static final int SINGLE_BATCH = 1 ;
public static final int SMALL_BATCH = 4 ;
public static final int MEDIUM_BATCH = 11 ;
public static final int LARGE_BATCH = 51 ;

第一件要做的事是你要衡量有多少批处理以及每个批处理的大小。(注意:在真实的代码中,这些值应该写在一个配置文件中而不是采取硬编码的形式,也就是说,你可以在运行时试验并改变批处理的大小)不管真正的批处理大小是多大,你总需要一个单个的批处理—大小为1的批处理(SINGLE_BATTCH)。这样如果有人请求的就是一个值或者在一个很大的查询中最后有遗留下来的单个值都能派上用场。对于批处理的大小,使用素数会更好些。换句话说,大小不应该可以相互的整除或者被相同的数整除。请求数的最大值将有最少的服务器往返。批处理的大小的数量和真正的大小是基于配置变化的。需要注意的是:大的批处理大小不应该太大否则你将遇到内存麻烦。同时最小批处理的大小应该很小,你可能会使用这个来做很多次的查询。

1
while ( totalNumberOfValuesLeftToBatch > 0 ) {

按如下方式重复操作直到推出循环。

1
2
3
4
5
6
7
8
9
int batchSize = SINGLE_BATCH;
if ( totalNumberOfValuesLeftToBatch >= LARGE_BATCH ) {
  batchSize = LARGE_BATCH;
} else if ( totalNumberOfValuesLeftToBatch >= MEDIUM_BATCH ) {
  batchSize = MEDIUM_BATCH;
} else if ( totalNumberOfValuesLeftToBatch >= SMALL_BATCH ) {
  batchSize = SMALL_BATCH;
}
totalNumberOfValuesLeftToBatch -= batchSize;

这种方案在这里是查找到最大的批处理大小,可能这个最大值比我们实际要查询的值稍大。举例说明:假设查询有75个参数,那么首先选择51个元素(LARGE_BATCH),现在还剩24个待查询,然后接着用11个元素的查询(MEDIUM_BATCH)。现在还有13个值,因为仍然大于11,再做一次11个元素的查询,现在只剩下2个值,它少于那个最小的批处理4(SMALL_BATCH),所以做两次单查询。总共5次往返用了3次预处理在缓存中。这是一个很重要的改进比单独地坐75次单查询。

1
2
3
4
5
6
7
8
9
10
11
12
StringBuilder inClause = new StringBuilder();
boolean firstValue = true ;
for ( int i= 0 ; i < batchSize; i++) {
  inClause.append( '?' );
  if ( firstValue ) {
    firstValue = false ;
  } else {
    inClause.append( ',' );
  }
}
PreparedStatement stmt = conn.prepareStatement(
    "select id, name from users where id in (" + inClause.toString() + ')' );

现在已经构建了一个真实的预处理语句,由于一直用相同的方式构建的查询,驱动注意到SQL是相同的。(注意:如果你还没有用Java5,使用StringBuffer替换StringBuilder才能正常编译),返回id很重要这样有利于查找哪个名字对应哪个id。

1
2
3
for ( int i= 0 ; i < batchSize; i++) {
  stmt.setInt(i);   // or whatever values you are trying to query by
}

设置合适的值数量去查询,包括其他搜索条件查询。仅仅只要把这些参数在之举参数之后。在这种情况你可以最终当前的索引。

从这点来看,你仅仅只是执行查询返回了结果,在第一次尝试的时候,你应该关注一下性能的提升,根据具体情况调整优化批处理的大小(batch size)。

© 著作权归作者所有

共有 人打赏支持
sms95
粉丝 1
博文 31
码字总数 22644
作品 0
长沙
私信 提问
Spring JdbcTemplate方法详解

JdbcTemplate主要提供以下五类方法: execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句; update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate...

zqcju
2014/06/13
0
6
Ujorm 1.31 发布,Java 版 ORM 框架

Ujorm 1.31 支持 UPDATE 和 DELETE 语句,包括批处理模式;可为 SQL 实体和列名在真正的 SQL 语句上增加引用,方法是 MetaParams.setQuotedSqlNames()。修复了关于延迟加载的关键 bug,为 Ne...

oschina
2012/12/13
618
1
java Spring调整Quartz定时备份数据库

首先实现单独能够备份db的java程序,思路是执行动态把要执行的批处理命令写入1个bat文件中,然后调用java程序执行这个批处理 执行语句:mysqldump -u root -p123456 sshweb >E:\mysql_backu...

长平狐
2013/01/06
586
0
day14_DBUtils学习笔记

一、DBUtils介绍 Apache公司开发的框架。   DBUtils是java编程中的数据库操作实用工具,小巧简单实用。   DBUtils封装了对JDBC的操作,简化了JDBC操作。可以少写代码。 commons-dbutils 是...

黑泽明军
2018/05/20
0
0
Andriod studio 打包aar

因为项目不同,有些公用库而且还是c++的,还有一些带资源的,简单的复制遇到库升级又是一轮配置,编译成aar则解决这些麻烦。 但是默认andriod studio的make moudle只生成debug包,在3.0以上及...

breeze
2018/12/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

《傲慢与偏见》的读书笔记与读后感作文2400字

《傲慢与偏见》的读书笔记与读后感作文2400字: 作者:孙苑馨;笔者按:读书这个习惯是我爸妈养出来的。小时候父母赚的钱除了吃饭穿衣剩下的就是买书了,他们除了买各种新鲜出版的文艺派图书...

原创小博客
12分钟前
0
0
oozie job 的挂了监控报警或重启

oozie Coordinator 的job 和actioni状态很多,但好像不支持设置某状态如failed后30分钟后自动重新拉启,因他的条件只有几种:触发条件可以是一个时间频率、一个dataset实例是否可用,或者可能...

hblt-j
30分钟前
1
0
python精简笔记-[6]-[if,for,while]语句

1.if 条件判断 语法: if 判断条件: 执行语句……elif 判断条件:    执行语句……elif 判断条件:    执行语句……else: 执行语句…… 实例1: n = input("请输...

平头哥-Enjoystudy
34分钟前
0
0
RxJava 是如何实现线程切换的(上)

前言 通过前一篇的从观察者模式出发,聊聊RxJava,我们大致理解了RxJava的实现原理,在RxJava中可以非常方便的实现不同线程间的切换。subscribeOn 用于指定上游线程,observeOn 用于指定下游线...

亭子happy
今天
2
0
想晋级高级工程师只知道表面是不够的!Git内部原理介绍

本文由云+社区发表 作者:腾讯工蜂用户:王二卫 从不一样的视角了解git,以便更好的使用git 一、git & git 版本库认识 git 是一个内容寻址的文件系统,其核心部分是一个简单的键值对数据库(...

腾讯云加社区
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部