不高效的，通用的java处理思想

    经过我多年的观察和自身体会，java程序处理数据的基本思想是一条一条处理，当处理大数据量的时候，往往先把数据抽取到weblogic等中间件中，然后用面向对象的思想操作一条条操作数据。

    自从多年前开始学习数据库，才发现写java程序和SQL完全思路完全相反，SQL其实一种操作集合的语言，擅长对集合的操作。所以java程序员在操作大量数据的时候要转变思维，采用集合的操作方式，说的总是抽象的。多年没有写java代码了，原谅代码的拙劣。

    来做两个例子，需求是将test1中的某个字段更新到test2中，可能你会笑，一条SQL不就完了吗。万万实际上写真正的代码就不是这样了，或许业务代码很复杂。这里只是一个例子，抽象了问题：

    TestDemo1:运行时间为119s.

    TestDemo2:运行时间为1.7s.

    集合的写法比单条处理快解决100倍，事实上也是这样的，系统大数据量的操作都改造成存储过程。

drop table test1 purge;
drop table test2 purge;
create table test1 as select * from dba_objects where object_id is not null;
create table test2 as select * from dba_objects where object_id is not null;
alter table test1 modify object_id not null;
alter table test2 modify object_id not null;
create index ind_t1_object_id on test1(object_id);
create index ind_t2_object_id on test2(object_id);
exec dbms_stats.gather_table_stats(user,'test1',cascade => true);
exec dbms_stats.gather_table_stats(user,'test2',cascade => true);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TestDemo1 {
  public static void main(String args[]){
     long startTime =System.currentTimeMillis();
     TestVO testVO ;
     List list = selectDB("select * from test1 where rownum<1000");
     String sql;
     for(int i=0; i<list.size(); i++){
         testVO = (TestVO)list.get(i);
         sql="update test2 set object_name= '"+testVO.getObjectName()+"' where object_id="+testVO.getObjectId();
         updateDB(sql);
     }
     long endTime =System.currentTimeMillis();
     System.out.println("总耗时为："+(endTime-startTime));
  }

  public static List<TestVO> selectDB(String sql){
      List list = new ArrayList();
      TestVO testVO ;
      Connection con = null;
      PreparedStatement pre = null;
      ResultSet result = null;
      try
      {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          String url = "jdbc:oracle:thin:@10.10.11.14:1521:orcla";
          String user = "DEV";
          String password = "DEV";
          con = DriverManager.getConnection(url, user, password);
          pre = con.prepareStatement(sql);
          result =pre.executeQuery();
          while (result.next ()) {
              testVO = new TestVO();
              testVO.setObjectId(result.getInt("object_id"));
              testVO.setObjectName(result.getString("object_name"));
              list.add(testVO);
          }
          return list;
     }catch (Exception e)
      {
          e.printStackTrace();
          return list;
      }
      finally
      {
          try
          {
              if (result != null)
                  result.close();
              if (pre != null)
                  pre.close();
              if (con != null)
                  con.close();
          }
          catch (Exception e)
          {
              e.printStackTrace();
          }
      }

  }

  public static void updateDB(String sql){
      Connection con = null;
      PreparedStatement pre = null;
      ResultSet result = null;
      try
      {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          String url = "jdbc:oracle:thin:@10.10.11.14:1521:orcla";
          String user = "DEV";
          String password = "DEV";
          con = DriverManager.getConnection(url, user, password);
          pre = con.prepareStatement(sql);
          pre.execute(sql);
     }catch (Exception e)
      {
          e.printStackTrace();
      }
      finally
      {
          try
          {
              if (result != null)
                  result.close();
              if (pre != null)
                  pre.close();
              if (con != null)
                  con.close();
          }
          catch (Exception e)
          {
              e.printStackTrace();
          }
      }

  }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestDemo2 {
    public static void main(String args[]){
        long startTime =System.currentTimeMillis();
        String sql ="merge into test2 t2 using(select * from test1 where rownum <1000) t1"+
        " on (t1.object_id =  t2.object_id) "+
        " when matched then "+
        " update set t2.object_name=t1.object_name ";
        updateDB(sql);
        long endTime =System.currentTimeMillis();
        System.out.println("总耗时为："+(endTime-startTime));
     }

    public static void updateDB(String sql){
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet result = null;
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@10.10.11.14:1521:orcla";
            String user = "DEV";
            String password = "DEV";
            con = DriverManager.getConnection(url, user, password);
            pre = con.prepareStatement(sql);
            pre.execute(sql);
       }catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
    }
}