java mysql dbcp连接池，批量更新速度慢的解决办法：executeBatch

1. url必须加上rewriteBatchedStatements=true

driverClassName=com.mysql.jdbc.Driver
#url= jdbc:mysql://192.168.0.184:3306/location?autoReconnect=true&useUnicode=true&characterEncoding=UTF8&allowMultiQueries=true&&useSSL=true&&rewriteBatchedStatements=true
url= jdbc:mysql://127.0.0.1:3306/location?autoReconnect=true&useUnicode=true&characterEncoding=UTF8&allowMultiQueries=true&&useSSL=true&&rewriteBatchedStatements=true
#username=111
username=root
password=123445
initialSize=5
maxTotal=80
maxIdle=10
minIdle=5
maxWaitMillis=5000
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true

removeAbandonedTimeout=1

2. 必须设置autoCommit为false

package com.hiexhibition.locate_engine_lite.db.mysql;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

/**
 * DBCP配置类
 * @author SUN
 */
public class JdbcPool {
    
    private static Properties properties = new Properties();
    private static DataSource dataSource;
    //加载DBCP配置文件
    JdbcPool(String filePath){
        try{
            FileInputStream is = new FileInputStream(filePath);  
            properties.load(is);
        }catch(IOException e){
            e.printStackTrace();
        }
        
        try{
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    //从连接池中获取一个连接
    public Connection getConnection(){
        Connection connection = null;
        try{
            connection = dataSource.getConnection();
        }catch(SQLException e){
            e.printStackTrace();
        }
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
   
}

3. 调用addBatch之后和executeBatch之后，需要调用commit

public static void main(String[] args) throws SQLException {
		StoreRunnable storeRunnable = new StoreRunnable();
		final String TABLE_NAME = "user_position";		//用户位置表
		
		Connection connection = DB.poolPosition.getConnection();		//数据库连接
		Statement statement = connection.createStatement();
		for(int i = 0; i < 1000; i++){
			Position pt = new Position();
			Map map_insert = storeRunnable.getMapByPositionForDB(1123L, pt);		//要插入的map
			String insertSql = DB.getInsertSql(TABLE_NAME, map_insert);			//插入sql语句
			try {
				statement.addBatch(insertSql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		long startTime = System.currentTimeMillis();
		statement.executeBatch();
		connection.commit();
		
		
		statement.close();
		System.out.println("耗时：" + (System.currentTimeMillis() - startTime));
	}