• [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

  • 相关阅读:
    Wireshark教程
    存储基础知识4——
    java核心技术 要点笔记3
    java核心技术 要点笔记2
    java核心技术 要点笔记1
    java Vamei快速教程22 内存管理和垃圾回收
    java Vamei快速教程21 事件响应
    php框架练习
    php之图片处理类缩略图加水印
    php之框架增加日志记录功能类
  • 原文地址:https://www.cnblogs.com/heyang78/p/11806720.html
Copyright © 2020-2023  润新知