jdbc连接Oracle/MySQL数据库进行批量导入操作,如何提高效率？？？

来源：互联网发布：php变量类型编辑：程序博客网时间：2024/05/07 09:23

使用JDBC连接Oracle数据库进行批量数据导入的时候，特别是大批量数据连续插入（百万级或以上），如何提高效率呢？

在JDBC编程接口中有两个方法特别值得注意：

（1）void addBatch() throws SQLException

Adds a set of parameters to this PreparedStatement object's batch of commands.

（2）int[] executeBatch() throws SQLException

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. The int elements of the array that is returned are ordered to correspond to the commands in the batch, which are ordered according to the order in which they were added to the batch.

通过使用addBatch（）和executeBatch（）这一对方法可以实现批量处理数据。

不过值得注意的是，首先需要在数据库链接中设置手动提交，connection.setAutoCommit(false)，然后在执行Statement之后执行connection.commit()。

详细步骤如下：

（1）获取数据库连接

1》Oracle数据库连接

package com.test.jdbc;import java.sql.Connection;import java.sql.DriverManager;import com.test.jdbc.IDBConnection;/** * 取得Oracle数据库连接 * */public class OracleDBConnection implements IDBConnection {//DB Driverprivate static final String DBDRIVER="oracle.jdbc.driver.OracleDriver";//DB URLprivate static final String DBURL="jdbc:oracle:thin:@127.0.0.1:1521:orcl"; //DB Userprivate static final String DBUSER="xxtmanage";//DB Passwordprivate static final String DBPASSWORLD="xxtinterface";//DB Connectionprivate Connection conn=null;//structorpublic OracleDBConnection() {//在构造方法中进行数据库连接System.out.println("OracleDBConnection.OracleDBConnection()");try {// 加载驱动程序Class.forName(DBDRIVER);//连接数据库this.conn=DriverManager.getConnection(DBURL, DBUSER, DBPASSWORLD);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}@Overridepublic Connection getConnection(){System.out.println("OracleDBConnection.getConnection()");return this.conn;}@Overridepublic void close(){System.out.println("OracleDBConnection.close()");if(this.conn!=null){try {this.conn.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}

2》MySQL数据库连接

package com.xxxAM.dbc;import java.sql.Connection;import javax.naming.Context;import javax.naming.InitialContext;import javax.sql.DataSource;public class MySqlDBConnection {/*private static final String DBDRIVER="org.gjt.mm.mysql.Driver";private static final String DBURL="jdbc:mysql://localhost:3306/xxxamdb";private static final String DBUSER="root";private static final String DBPASS="123123";private Connection conn=null;public MySqlDBConnection () throws Exception{try{//加载驱动程序Class.forName(DBDRIVER);//连接数据库this.conn=DriverManager.getConnection(DBURL,DBUSER,DBPASS);}catch(Exception e){throw e;}}public Connection getConnection()//取得数据库连接{return this.conn;}//关闭数据库操作public void close()throws Exception{if(this.conn!=null)//避免NullPointerException{try{this.conn.close();//数据库关闭}catch(Exception e){throw e;}}}*///利用数据库连接池，可以提高效率。。。private static final String DSNAME="java:comp/env/jdbc/xxxAM";private Connection conn=null;public DatabaseConnection()throws Exception{try{Context ctx=new InitialContext();DataSource ds=(DataSource)ctx.lookup(DSNAME);this.conn=ds.getConnection();}catch(Exception e){e.printStackTrace();}}public Connection getConnection(){return this.conn;}public void close()throws Exception{if(this.conn!=null){try{this.conn.close();}catch(Exception e){throw e;}}}}

(2) 在数据库中建表

建表操作不是本文的重点讲解内容。为了简单，这里直接给出编码方式建表的sql语句，代码如下：

String sql="create table GPS_LOG_cxc"+ "( licenseplateno VARCHAR2(20) not null,  "+ "  in_date        DATE default SYSDATE,"+ "  gps_time       DATE not null,"+ "  longitude      CHAR(10),"+ "  latitude       CHAR(9),"+ "  height         CHAR(9),"+ "  speed          CHAR(3),"+ "  direction      CHAR(3),"+ "  eff            CHAR(1),"+ "  car_stat1      CHAR(1),"+ "  car_stat2      CHAR(1)"+ ")";

当然，你也可以直接在数据中用sql语句或者手工建表

（3）两种批量执行SQL语句的情况

为了简单起见，直接在main（）方法中编码，代码如下：

第一种情况：一个SQL语句的批量传参：

1》PreparedStatement+batch （未使用clearBatch()方法时）处理方式：

package com.test.main;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.Date;import com.test.jdbc.OracleDBConnection;public class Test {public static void main(String[] args) throws Exception {// TODO Auto-generated method stub//得到Oracle数据库连接Connection conn=new OracleDBConnection().getConnection();conn.setAutoCommit(false);//设置为不自动提交 String sql="INSERT INTO  GPS_LOG_cxc "                  + "(licenseplateno ,in_date,gps_time,longitude,latitude,height,speed, direction , eff, car_stat1, car_stat2)"                  +"VALUES(?,?,?,?,?,?,?,?,?,?,?)";          PreparedStatement pstmt=conn.prepareStatement(sql);                            //每n条命令commit一次          int n=10000;          int[] updateCounts=null;           //记录起始时间           long startTime=System.currentTimeMillis();          //记录命令执行失败数          long faileNum=0;          //记录执行commit次数          long commitNum=0;          for(int i=1;i<=1000000;i++){          pstmt.setString(1, "粤A434XX");          pstmt.setDate(2, new java.sql.Date(new Date().getTime()));          pstmt.setDate(3, new java.sql.Date(new Date().getTime()));          pstmt.setString(4, "+113.36671");          pstmt.setString(5, "+23.08077");          pstmt.setString(6, "000");          pstmt.setString(7, "100");          pstmt.setString(8, "000");          pstmt.setString(9, "1");          pstmt.setString(10,"1" );          pstmt.setString(11, "8");              pstmt.addBatch();              if(i%n==0){                  try {                      pstmt.executeBatch();                      conn.commit();                      //pstmt.clearBatch();                    commitNum++;                      System.out.println("--commit-- ");                      } catch (BatchUpdateException bue) {                          // TODO Auto-generated catch block                              faileNum++;                              updateCounts=bue.getUpdateCounts();                          }                  }          }                    pstmt.executeBatch();          conn.commit();          //pstmt.clearBatch();                    long endTime=System.currentTimeMillis();        pstmt.close();          conn.close();                    long time=(endTime-startTime)/1000;          //统计数据          System.out.println("每次提交："+n+"条");          System.out.println("提交次数："+commitNum);          System.out.println("失败次数："+faileNum);          System.out.println("用时："+time+"秒");  }}

程序中是以100万条相同数据进行插入操作进行测试的，测试结果如下（跟机器性能有关系的哦）：

一次为：

每次提交：10000条提交次数：100失败次数：0用时：18秒

再一次为：

每次提交：10000条提交次数：100失败次数：0用时：18秒

测试结果可能会有浮动的，，，建议多测几次。

2》PreparedStatement+batch （使用clearBatch()方法时）处理方式：

package com.test.main;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.Date;import com.test.jdbc.OracleDBConnection;public class Test {public static void main(String[] args) throws Exception {// TODO Auto-generated method stub//得到Oracle数据库连接Connection conn=new OracleDBConnection().getConnection();conn.setAutoCommit(false);//设置为不自动提交 String sql="INSERT INTO  GPS_LOG_cxc "                  + "(licenseplateno ,in_date,gps_time,longitude,latitude,height,speed, direction , eff, car_stat1, car_stat2)"                  +"VALUES(?,?,?,?,?,?,?,?,?,?,?)";          PreparedStatement pstmt=conn.prepareStatement(sql);                            //每n条命令commit一次          int n=10000;          int[] updateCounts=null;           //记录起始时间           long startTime=System.currentTimeMillis();          //记录命令执行失败数          long faileNum=0;          //记录执行commit次数          long commitNum=0;          for(int i=1;i<=1000000;i++){          pstmt.setString(1, "粤A434XX");          pstmt.setDate(2, new java.sql.Date(new Date().getTime()));          pstmt.setDate(3, new java.sql.Date(new Date().getTime()));          pstmt.setString(4, "+113.36671");          pstmt.setString(5, "+23.08077");          pstmt.setString(6, "000");          pstmt.setString(7, "100");          pstmt.setString(8, "000");          pstmt.setString(9, "1");          pstmt.setString(10,"1" );          pstmt.setString(11, "8");              pstmt.addBatch();              if(i%n==0){                  try {                      pstmt.executeBatch();                      conn.commit();                      pstmt.clearBatch();                    commitNum++;                      System.out.println("--commit-- ");                      } catch (BatchUpdateException bue) {                          // TODO Auto-generated catch block                              faileNum++;                              updateCounts=bue.getUpdateCounts();                          }                  }          }                    pstmt.executeBatch();          conn.commit();          pstmt.clearBatch();                    long endTime=System.currentTimeMillis();        pstmt.close();          conn.close();                    long time=(endTime-startTime)/1000;          //统计数据          System.out.println("每次提交："+n+"条");          System.out.println("提交次数："+commitNum);          System.out.println("失败次数："+faileNum);          System.out.println("用时："+time+"秒");  }}

测试结果如下：

每次提交：10000条提交次数：100失败次数：0用时：14秒

第二种是多条SQL语句的批量处理，即Statement+batch处理方式，代码如下

package com.test.main;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.Statement;import java.util.Date;import com.test.jdbc.OracleDBConnection;public class Test {public static void main(String[] args) throws Exception {// TODO Auto-generated method stub//得到Oracle数据库连接Connection conn=new OracleDBConnection().getConnection();conn.setAutoCommit(false);//设置为不自动提交String sql="";Statement stmt=conn.createStatement();//每n条命令commit一次int n=100;//记录起始时间 long startTime=new Date().getTime();//记录命令执行失败数long faileNum=0;//记录执行commit次数long commitNum=0;for(int i=1;i<=1000;i++){sql="INSERT INTO  GPS_LOG_cxc "+ "(licenseplateno ,in_date,gps_time,longitude,latitude,height,speed, direction , eff, car_stat1, car_stat2)"+"VALUES("+"'粤A434XX'"+","+"to_date('2014-9-1 15:02:23','yyyy-mm-dd hh24:mi:ss')"+","+"to_date('2014-9-1 15:02:23','yyyy-mm-dd hh24:mi:ss')"+","+"'+113.36671'"+","+"'+23.08077'"+","+"'0'"+","+"'1'"+","+"'2'"+","+"'3'"+","+"'0'"+","+"'1'"+")";stmt.addBatch(sql);if(i%n==0){try {stmt.executeBatch();conn.commit();stmt.clearBatch();commitNum++;System.out.println("--commit-- ");} catch (BatchUpdateException bue) {// TODO Auto-generated catch blockfaileNum++;System.out.println(bue.toString());}}}stmt.executeBatch();conn.commit();stmt.clearBatch();stmt.close();conn.close();long endTime=new Date().getTime();long time=(endTime-startTime)/1000;//统计数据System.out.println("每次提交："+n+"条");System.out.println("提交次数："+commitNum);System.out.println("失败次数："+faileNum);System.out.println("用时："+time+"秒");}}

测试结果（1000条数据，每100条commit一次）如下：

每次提交：100条提交次数：10失败次数：0用时：331秒

（ps:不知道为什么这样会慢很多???---以后再找原因吧！！！）

最后注意：Oracle数据库中关于java.sql.Date类型的写法：

对于表中字段类型有java.sql.Date类型的表，在sql语句中要进行如下方形式的写法（如上例程序中所示）：

to_date('2014-9-1 15:02:23','yyyy-mm-dd hh24:mi:ss')

0 0