jdbc批处理插入对象

@java方面

1.使用Statement对象

2.预编译PreparedStatement

3.使用PreparedStatement + 批处理

为了区分出这三者之间的效率，下面的事例执行过程都是在数据库表t1中插入1万条记录，并记录出所需的时间(此时间与电脑硬件有关)

1.使用Statement对象

使用范围：当执行相似SQL(结构相同，具体值不同)语句的次数比较少

优点：语法简单

缺点:采用硬编码效率低，安全性较差。

原理：硬编码，每次执行时相似SQL都会进行编译



事例执行过程:

public void exec(Connection conn){

try {

Long beginTime = System.currentTimeMillis();

conn.setAutoCommit(false);//设置手动提交

Statement st = conn.createStatement();

for(int i=0;i<10000;i++){

String sql="insert into t1(id) values ("+i+")";

st.executeUpdate(sql);

}

Long endTime = System.currentTimeMillis();

System.out.println("Statement用时："+(endTime-beginTime)/1000+"秒");//计算时间

st.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}



执行时间：Statement用时：31秒



2.预编译PreparedStatement

使用范围：当执行相似sql语句的次数比较多（例如用户登陆，对表频繁操作..）语句一样，只是具体的值不一样，被称为动态SQL

优点：语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）

缺点: 执行非相似SQL语句时，速度较慢。

原理：相似SQL只编译一次，减少编译次数

名词解释：

SQL注入:select * from user where username="张三" and password="123" or 1=1;

前面这条语句红色部分就是利用sql注入，使得这条词句使终都会返回一条记录，从而降低了安全性。

事例执行过程:

public void exec2(Connection conn){

try {

Long beginTime = System.currentTimeMillis();

conn.setAutoCommit(false);//手动提交

PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");

for(int i=0;i<10000;i++){

pst.setInt(1, i);

pst.execute();

}

conn.commit();

Long endTime = System.currentTimeMillis();

System.out.println("Pst用时:"+(endTime-beginTime)+"秒");//计算时间

pst.close();

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

执行时间：Pst用时:14秒



3.使用PreparedStatement + 批处理

使用范围：一次需要更新数据库表多条记录

优点：减少和SQL引擎交互的次数，再次提高效率,相似语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）

缺点:

原理：

批处理: 减少和SQL引擎交互的次数，一次传递给SQL引擎多条SQL。

名词解释：

PL/SQL引擎：在oracle中执行pl/sql代码的引擎，在执行中发现标准的sql会交给sql引擎进行处理。

SQL引擎：执行标准sql的引擎。

事例执行过程:

public void exec3(Connection conn){

try {

conn.setAutoCommit(false);

Long beginTime = System.currentTimeMillis();

PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");



for(int i=1;i<=10000;i++){

pst.setInt(1, i);

pst.addBatch();//加入批处理，进行打包

if(i%1000==0){//可以设置不同的大小；如50，100，500，1000等等

pst.executeBatch();

conn.commit();

pst.clearBatch();

}

}

pst.executeBatch();

Long endTime = System.currentTimeMillis();

System.out.println("pst+batch用时："+(endTime-beginTime)+"毫秒");

pst.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

执行时间：pst+batch用时：485毫秒



@数据库方面

1 静态SQL

使用范围：

优点：每次执行相似sql都会进行编译。

缺点:效率低,占用破费cpu资源，耗费SGA 资源。

原理：(检查SQL 语句在SGA 中的共享池中是否存在, 如果不存在，则编译、解析后执行：硬解析,

如果已存在，则取出编译结果执行：软解析)

事例执行过程:

DECLARE
time1 NUMBER;
time2 NUMBER;
BEGIN
time1 := dbms_utility.get_time;
EXECUTE IMMEDIATE 'truncate table t_demo';
FOR i IN 1..10000
LOOP
EXECUTE IMMEDIATE
'insert into t_demo(u_id) values('||i||')';
END LOOP;
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)/100||'秒');
END

执行时间：pst+batch用时：20.93秒

2 动态SQL

使用范围：

优点：语句只编译一次，减少编译次数，提高效率。

缺点:

原理：减少编译次数(检查SQL 语句在SGA 中的共享池中是否存在, 如果不存在，则编译、解析后执行：硬解析,

如果已存在，则取出编译结果执行：软解析)

事例执行过程:

DECLARE
time1 NUMBER;
time2 NUMBER;
BEGIN
time1 := dbms_utility.get_time;
EXECUTE IMMEDIATE 'truncate table t_demo';
FOR i IN 1..10000
LOOP
EXECUTE IMMEDIATE
'insert into t1(u_id) values(:x)' USING i;
END LOOP;
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)/100||'秒');
END;

执行时间：pst+batch用时：10.55秒



3 利用forall进行批处理<相似java中的批处理>

使用范围：当执行相似sql语句的次数比较多（例如用户登陆，对表频繁操作..）FORALL只能运行一条sql语句。

优点：减少与SQL引擎转替次数，提交效率。

缺点:循环时从开始到结束，一次性读完，不能取其中的某些循环进行操作。

原理：使用ForAll语句可以让PL/SQL引擎一次将多条SQL转发给SQL引擎，从而减少转发次数，提高系统性能。

事例执行过程:

CREATE OR REPLACE PROCEDURE p_test
AS

--定义新的类型(不是定义变量！)
TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
--用新的类型来定义变量
id_table id_table_type;
time1 NUMBER;
time2 NUMBER;
BEGIN
FOR i IN 1..10000
LOOP
--往两个数组中存放数据
id_table(i) := i;
END LOOP;
--★：一次性向SQL引擎传递了多条数据而不是一条
time1 := dbms_utility.get_time;
FORALL i IN 1..id_table.COUNT
INSERT INTO t_demo(u_id) VALUES(id_table(i));
time2 := dbms_utility.get_time;
dbms_output.put_line((time2-time1)*10||'毫秒');

end p_test;



call p_test();

执行时间：pst+batch用时：170毫秒



4 利用BULK COLLECT

使用范围：处理数据量比较少时。

优点：一次读取多行数据，提高效率。

缺点: 需要较大的内存开销

原理：将多个行引入一个或多个集合中。

事例执行过程:

DECLARE

TYPE books_aat



IS TABLE OF book%ROWTYPE

INDEX BY PLS_INTEGER;

book books_aat;

BEGIN

SELECT *

BULK COLLECT INTO book

FROM books

WHERE title LIKE '%PL/SQL%';

...

END;