JDBC性能优化

转载

IT祖师爷 2021-08-18 00:31:24

文章标签 数据数据库 sql oracle 字段名 文章分类 Java 后端开发

一、使用PreparedStatement的Batch功能

参见本人一下文章：http://blog.csdn.net/lmb55/article/details/50631062

二、选择合适的光标类型

选择合适的光标类型以最大限度的适用你的应用程序。（以下列出三种光标类型的性能问题）

对于从一个表中顺序读取所有记录的情况来说, Forward-Only型的光标提供了最好的性能。获取表中的数据时, 没有哪种方法比使用Forward-Only型的光标更快。但不管怎样, 当程序中必须按无次序的方式处理数据行时, 这种光标就无法使用了。

对于程序中要求与数据库的数据同步以及要能够在结果集中前后移动光标, 使用JDBC的Scroll-Insensitive型光标是较理想的选择。此类型的光标在第一次请求时就获取了所有的数据(当JDBC驱动采用’lazy’方式获取数据时或许是很多的而不是全部的数据)并且储存在客户端。因此，第一次请求会非常慢，特别是请求长数据时会理严重。而接下来的请求并不会造成任何网络往返(当使用’lazy’方法时或许只是有限的网络交通) 并且处理起来很快。因为第一次请求速度很慢，Scroll-Insensitive型光标不应该被使用在单行数据的获取上。当有要返回长数据时，开发者也应避免使用Scroll-Insensitive型光标，因为这样可能会造成内存耗尽。有些Scroll-Insensitive型光标的实现方式是在数据库的临时表中缓存数据来避免性能问题，但多数还是将数据缓存在应用程序中。

Scroll-Sensitive型光标，有时也称为Keyset-Driven光标，使用标识符，像数据库的ROWID之类。当每次在结果集移动光标时，会重新该标识符的数据。因为每次请求都会有网络往返，性能可能会很慢。无论怎样，用无序方式的返回结果行对性能的改善是没有帮助的。

现在来解释一下这个, 来看这种情况。一个程序要正常的返回1000行数据到程序中. 在执行时或者第一行被请求时, JDBC驱动不会执行程序提供的SELECT语句. 相反, 它会用键标识符来替换SELECT查询, 例如, ROWID. 然后修改过的查询都会被驱动程序执行，跟着会从数据库获取所有1000个键值. 每一次对一行结果的请求都会使JDBC驱动直接从本地缓存中找到相应的键值, 然后构造一个包含了’WHERE ROWID=？’子句的最佳化查询, 再接着执行这个修改过的查询, 最后从服务器取得该数据行。

当程序无法像Scroll-Insensitive型光标一样提供足够缓存时, Scroll-Sensitive型光标可以被替代用来作为动态的可滚动的光标。

三、使用有效的getter方法

JDBC提供多种方法从ResultSet中取得数据，像getInt()， getString()，和getObject()等等。而getObject()方法是最泛化了的，提供了最差的性能。这是因为JDBC驱动必须对要取得的值的类型作额外的处理以映射为特定的对象。所以就对特定的数据类型使用相应的方法.

要更进一步的改善性能，应在取得数据时提供字段的索引号，例如, getString(1)， getLong(2)，和getInt(3)等来替代字段名。如果没有指定字段索引号。网络交通不会受影响。但会使转换和查找的成本增加。例如，假设你使用getString(“foo”) … JDBC驱动可能会将字段名转为大写(如果需要)，并且在到字段名列表中逐个比较来找到”foo”字段。如果可以，直接使用字段索引，将为你节省大量的处理时间。

例如，假设你有一个100行15列的ResultSet，字段名不包含在其中。你感兴趣的是三个字段 EMPLOYEENAME (字串型), EMPLOYEENUMBER (长整型), 和SALARY (整型)。如果你指定getString(“EmployeeName”), getLong(“EmployeeNumber”), 和getInt(“Salary”)，查询旱每个字段名必须被转换为metadata中相对应的大小写，然后才进行查找。如果你使用getString(1), getLong(2), 和getInt(15)。性能就会有显著改善。

四、选择合适的数据类型

接收和发送某些数据可能代价昂贵。当你设计一个schema时，应选择能被最有效地处理的数据类型。例如，整型数就比浮点数或实数处理起来要快一些。浮点数的定义是按照数据库的内部规定的格式，通常是一种压缩格式。数据必须被解压和转换到另外种格式，这样它才能被数据的协议处理。

五、获取ResultSet

由于数据库系统对可滚动光标的支持有限，许多JDBC驱动程序并没有实现可滚动光标。除非你确信数据库支持可滚动光标的结果集，否则不要调用rs.last()和rs.getRow()方法去找出数据集的最大行数。因为JDBC驱动程序模拟了可滚动光标，调用rs.last()导致了驱动程序透过网络移到了数据集的最后一行。取而代之，你可以用ResultSet遍历一次计数或者用SELECT查询的COUNT函数来得到数据行数。

通常情况下，不要写那种依赖于结果集行数的代码, 因为驱动程序必须获取所有的数据集以便知道查询会返回多少行数据，当表中的数据量非常大时，这种做法是非常消耗性能的。

六、获取自动生成的键值

当插入一条记录时,我们很多时候都想马当前插入的主键返回出来.

如果主键是自动生成的(大多数时候是这样),那么取回主键是一种比较困难的事.
可能对于一些特殊的数据库必须把insert into和select max(key)或什么序列当前值作为一个事务的原子来操作,但很多数据库(以及数据库操作环境)并不能为你提供事务并发控制.所以如果多人同时操作,那么select max(key)很可能就不是你刚才insert的那条记录.

在SQL server 很方便，有现程的sql可以用

INSERT INTO tableName (fieldList ...) values (valueList ...) SELECT @@IDENTITY AS aliasName;

该SQL的示例如下：


--创建表
create table OrgInfo
(
OrgId int identity(1,1) primary key, 
OrgName nvarchar(20)
)
--插入测试数据
insert into OrgInfo
select '张三' union select '李四' union select '王五'

select * from OrgInfo
/*
OrgId       OrgName
----------- --------------------
1           李四
2           王五
3           张三
*/

--假设此时我们再插入一条，然后得到新插入的id
insert into OrgInfo select '刘六'
--得到新插入的id
select @@identity
/*
4
*/

--得到新插入的数据行
select * from OrgInfo where OrgId=@@identity
/*
OrgId       OrgName
----------- --------------------
4           刘六
*/

在Oracle中，有两种方案：

1、使用JDBC的getGeneratedKeys()方法，第一次返回oracle的rowid，第二次使用返回的rowid查询该条数据。

String sql = "insert into xx_cust(cust_type,cust_name,login_name)"  
+ " values('ipn','aaa','bb')";  
//省略创建数据库连接的过程
PreparedStatement stmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
stmt.execute();  
ResultSet rs = stmt.getGeneratedKeys();  
rs.next();  
String rowid=rs.getString(1);  
System.out.println("rowid="+rowid);//返回rowid

PreparedStatement preparedStatement =conn.prepareStatement("select cust_id from xx_cust where rowid=?");  
preparedStatement .setString(1, rowid);  
ResultSet rs2 = preparedStatement.executeQuery();  
rs2.next();  
String custId=rs2.getString(1);  
System.out.println("custid="+custId);//通过rowid查询插入的数据

2、使用oracle的returning语句


String sql = "insert into xx_cust(cust_type,cust_name,login_name)"  
+ " values('ipn','aaa','bb') returning cust_id into ?";  
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(sql);  
stmt.registerReturnParameter(1, OracleTypes.INTEGER);  
int count = stmt.executeUpdate();  
if (count > 0) {  
    ResultSet rs = stmt.getReturnResultSet();  
    while (rs.next()) {  
        String name = rs.getString(1);  
        System.out.println("name=" + name);  
    }  
}