jdbc优化[手工原创] -凯发k8国际

java 与oracle数据库效率优化

@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(i00==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;