源生jdbc多线程添加分布式数据库千万级别

标题：源生jdbc多线程添加分布式数据库千万级别

一. 采用对分片表的字段按32取模来进行分片
1.创建一个线程，并重写里面的run方法，run方法大概实现如下
public void run(){
PreparedStatement pstmt = null;
Connection connection = null;
try {
connection = ((DataSource)
//读取配置文件db数据库的配置，获取数据库的连接
SpringBeanUtil.getBean(“dataSource_default”)).getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
try{
/备份20P/
String sql = "insert into " + “acct_item_20p (ACCT_ITEM_ID,ACCT_ID,ACCT_ITEM_TYPE_ID,AMOUNT,BILL_ID,BILLING_CYCLE_ID,” +
“CREATE_DATE,CUST_ID,FEE_CYCLE_ID,GRP_ACCT_ITEM_TYPE_NBR,HAD_INVOICE_AMOUNT,ITEM_SOURCE_ID,NO_INVOICE_AMOUNT,OFFER_INST_ID” +
“,ORI_ACCT_ITEM_ID,ONE_ACCT_ITEM_ID,PAY_CYCLE_ID,PAYMENT_METHOD,PRESENT_AMOUNT,PROD_INST_ID,STATUS_CD,STATUS_DATE,REGION_ID,” +
"PARTNER_ID,BILL_XCHG_ID,PRD_ID,OFFER_ID,UNSURE_INCOME,EVENT_PRICING_STRATEGY_ID,LATN_ID,ACCT_BALANCE_ID,OPER_PAYOUT_ID,PLATFORM) " + " values " +
“(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”;
pstmt = connection.prepareStatement(sql);
int idx=1;
int leftIdx=0;
for(int i = start ; i <= end; i+=step){
pstmt.setBigDecimal(1,new BigDecimal(i));
pstmt.setBigDecimal(2,new BigDecimal(i));
pstmt.setBigDecimal(3,new BigDecimal(1));
pstmt.setBigDecimal(4,new BigDecimal(1));
pstmt.setBigDecimal(5,new BigDecimal(2));
pstmt.setBigDecimal(6,new BigDecimal(2));
pstmt.setString(7,new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”).format(new Date()));

           pstmt.addBatch();
            //N条提交一次（5000条一次性能总体来说还是可以的，具体最大优化值多少没有仔细研究过）
            if(idx%5000==0){
                pstmt.executeBatch();
                connection.commit();
                 //删除批次
                pstmt.clearBatch();

                //剩余量设置为0
                leftIdx=0;
                System.out.println("5000已入库");
            }else{
                leftIdx++;
            }
            idx++;
        }
        //最后再做一次检测
        if (leftIdx>0){
            pstmt.executeBatch();
            //删除批次
            connection.commit();
            pstmt.clearBatch();

        }
    }catch (SQLException e){
        e.printStackTrace();
    }finally {
        try {
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

二.创建线程池去执行
ExecutorService fixedThreadPool = Executors.newFixedThreadPool(32);
for(int i=0;i<32;i++){
fixedThreadPool.execute(new addDataThread(i));
}
fixedThreadPool.shutdown();
while(!fixedThreadPool.isTerminated()){
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}