JDBC的大量新增

標籤：res   com   values   UI   read   url   .com   exce   ack   

資料庫

:

大量新增代碼：

package com.hyhl.test;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.logging.Level;import java.util.logging.Logger;import org.omg.PortableInterceptor.INACTIVE;import com.hyhl.DrugFactory.vo.DrugMumberDetailVo;import com.hyhl.patient.util.DataSourceUtils;import com.hyhl.util.DateTimeUtil;public class JDBCBatchInsert {    private String o_url = "jdbc:mysql://localhost:3306/hy_iwrs_test";    private Connection conn = null;    private String userName = "root";    private String password = "root";        /**     * 使用PreparedStatement做插入     * @param size     */    public void insertByBatchPrepareStat(List<DrugMumberDetailVo> list){        String startTi = DateTimeUtil.getFormatDateTime(new java.util.Date());        System.out.println("PreparedStatement做插入的開始時間"+startTi);         Connection conn = null;        try {            Class.forName("com.mysql.jdbc.Driver");            conn = DriverManager.getConnection(o_url, userName, password);            conn.setAutoCommit(false);            String sql = "INSERT drug_number_detail_test(random,projectId,groupName,status) VALUES(?,?,?,?)";            PreparedStatement prest = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);            for(DrugMumberDetailVo enter:list){              prest.setString(1, enter.getRandom());              prest.setInt(2, enter.getProject());              prest.setInt(3, enter.getGroup());              prest.setInt(4, enter.getStatus());              prest.addBatch();            }           prest.executeBatch();           conn.commit();           conn.close();           String startTi1 = DateTimeUtil.getFormatDateTime(new java.util.Date());           System.out.println("PreparedStatement做插入的結結時間"+startTi1);         } catch (SQLException ex) {            Logger.getLogger(JDBCBatchInsert.class.getName()).log(Level.SEVERE, null, ex);        } catch (ClassNotFoundException ex) {            Logger.getLogger(JDBCBatchInsert.class.getName()).log(Level.SEVERE, null, ex);        }    }    

 測試類別：

package com.hyhl.test;import java.util.ArrayList;import java.util.List;import javax.persistence.criteria.CriteriaBuilder.In;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.hyhl.DrugFactory.vo.DrugMumberDetailVo;public class JDBCBatchInsertTest {  JDBCBatchInsert jdbcBatchInsert = null;    public JDBCBatchInsertTest() {            }    @Before    public void setUp() {        jdbcBatchInsert = new JDBCBatchInsert();    }    @After    public void tearDown() {        jdbcBatchInsert = null;    }     @Test     public void testPrepare() {                  List<DrugMumberDetailVo> list=new ArrayList<DrugMumberDetailVo>();         for(int i=0;i<20000;i++){             DrugMumberDetailVo query=new DrugMumberDetailVo();             query.setGroup(0);             query.setProject(3);             query.setStatus(1);              int startNumber=0;                if(i+1<10){                    startNumber=Integer.valueOf("2100000"+String.valueOf(i+1));                }else if (i+1<100){                    startNumber=Integer.valueOf("210000"+String.valueOf(i+1));                }else if(i+1<1000){                    startNumber=Integer.valueOf("21000"+String.valueOf(i+1));                }else if(i+1<10000){                    startNumber=Integer.valueOf("2100"+String.valueOf(i+1));                }else if(i+1<100000){                    startNumber=Integer.valueOf("210"+String.valueOf(i+1));                }else if(i+1<1000000){                    startNumber=Integer.valueOf("21"+String.valueOf(i+1));                }                query.setRandom(String.valueOf(startNumber));             list.add(query);                      }         jdbcBatchInsert.insertByBatchPrepareStat(list);     }     }

效率比for迴圈插入資料快跟多

 

JDBC的大量新增