• [Java]在JAVA中使用Oracle的INSERT ALL语法进行批量插入


    Oracle也提供了类似MySQL的批量插入语法,只是稍微别扭些,具体代码如下:

    package com.hy;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.text.MessageFormat;
    
    public class BatchInserter {
        
        // 连接到数据库的四大属性
        private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
        private static final String DBURL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        private static final String USER = "system";
        private static final String PSWD = "XXXXX";
        
        public static void batchInsert() {
            Connection conn = null;
            Statement stmt = null;
            
            try{
                Class.forName(DRIVER).newInstance();
                conn = DriverManager.getConnection(DBURL, USER, PSWD);
                stmt = conn.createStatement();
                
                StringBuilder sb=new StringBuilder();
                sb.append("INSERT ALL ");
                sb.append("   INTO firsttb(NAME, age,createdtime) values('E1','22',sysdate)");
                sb.append("   INTO firsttb(NAME, age,createdtime) values('E2','32',sysdate)");
                sb.append("   INTO firsttb(NAME, age,createdtime) values('E3','42',sysdate)");
                sb.append("select * from dual");
                String sql = sb.toString();
                stmt.executeUpdate(sql);
                
                sql = "select id,name,age,createdtime from firsttb";
                ResultSet rs = stmt.executeQuery(sql);
    
                int index = 0;
                while (rs.next()) {
                    index++;
    
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String age = rs.getString("age");
                    String cdate = rs.getString("createdtime");
    
                    String raw = "#{0},{1},{2},{3},{4}";
                    Object[] arr = { index, id, name, age, cdate };
                    String outStr = MessageFormat.format(raw, arr);
                    System.out.println(outStr);
                }
            } catch (Exception e) {
                System.out.print(e.getMessage());
            } finally {
                try {
                    stmt.close();
                    conn.close();
                } catch (SQLException e) {
                    System.out.print("Can't close stmt/conn because of " + e.getMessage());
                }
            }
        }
        
        /**
         * 执行点
         * @param args
         */
        public static void main(String[] args) {
            batchInsert();
        }
    }

    输出:

    #1,1,ANDY,20,2019-11-09 09:19:10
    #2,2,Bill,30,2019-11-09 09:19:39
    #3,3,Cindy,40,2019-11-09 09:19:45
    #4,4,E1,22,2019-11-09 10:25:54
    #5,5,E2,32,2019-11-09 10:25:54
    #6,6,E3,42,2019-11-09 10:25:54

    前三条是原有的,4,5,6三条是刚才插入的,一会再试试百万条到底需要多长时间。

    参考资料:

    https://www.oschina.net/question/234345_51170

  • 相关阅读:
    JavaScript&DOM
    avalon.js的循环操作在表格中的应用
    merge()
    建立表空间以及用户
    SSI框架下,用jxl实现导出功能
    SQL递归查询实现组织机构树
    vue+webpack实践
    使用canvas绘制一片星空
    在canvas中使用html元素
    CSS3-transform 转换/变换
  • 原文地址:https://www.cnblogs.com/heyang78/p/11806720.html
Copyright © 2020-2023  润新知