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

╰+攻爆jí腚メ 2022-09-29 15:52 70阅读 0赞

经过我多年的观察和自身体会，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();
                }
            }
        }
    }