JDBC优化之Batch、Fetch

最新推荐文章于 2023-09-20 07:50:14 发布

coolmsn8786

最新推荐文章于 2023-09-20 07:50:14 发布

阅读量311

点赞数 1

文章标签： java 数据库

Batch和Fetch两个特性非常重要。
Batch相当于JDBC的写缓冲，Fetch相当于读缓冲。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Test {
private static int _1W = 10000;
private static List<String> list = new ArrayList<String>(100 * _1W);
static {
for (int i = 0; i < 10 * _1W; i++) {
list.add(String.valueOf(i));
}
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
long start = System.currentTimeMillis();
fetchRead();
long end = System.currentTimeMillis();
System.out.println((end - start) + "ms");
}
public static void batchWrite() throws SQLException, ClassNotFoundException {
// 1108ms
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
for (int i = 0; i < list.size(); i++) {
cmd.setString(1, list.get(i));
cmd.addBatch();
if (i % _1W == 0) {
cmd.executeBatch();
}
}
cmd.executeBatch();
connection.commit();
}
public static void jdbcWrite() throws ClassNotFoundException, SQLException {
// 28189ms
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
for (String s : list) {
cmd.setString(1, s);
cmd.execute();
}
connection.commit();
}
public static void jdbcRead() throws ClassNotFoundException, SQLException {
// 3120ms
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection.prepareStatement("select * from t");
ResultSet rs = cmd.executeQuery();
int i = 0;
while (rs.next()) {
rs.getString(1);
i = i + 1;
}
System.out.println("count:" + i);
}
public static void fetchRead() throws ClassNotFoundException, SQLException {
//764ms
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
connection.setAutoCommit(false);
PreparedStatement cmd = connection.prepareStatement("select * from t");
cmd.setFetchSize(_1W);
ResultSet rs = cmd.executeQuery();
int i = 0;
while (rs.next()) {
rs.getString(1);
i = i + 1;
}
System.out.println("count:" + i);
}
}

JDBC插入比较，相差25倍
不带Batch   28189ms
Batch           1108ms

JDBC读取比较，相差4倍
不带Fetch    3120ms
Fetch           764ms

如果把JDBC类比为JAVA IO的话，
不使用Fetch和Batch相当于直接使用FileInputStream和FileOutputStream
而设置了Fetch和Batch相当于使用BufferedInputStream和BufferedOutputStream

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1063033/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1063033/