java 批量插入测试

package com.****.****.common.util;

import com.suning.pcdn.common.api.constant.IpAddressInfo;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class FileUtil {

    public static final String xx = "http://*******/ipLibrary.txt";

    public static List<String> trim(String[] source){
        List<String> list = new ArrayList<String>();
        for(String item : source){
            if(null==item || "".equals(item))
                continue;
            list.add(item);
        }
        return list;
    }

    public static void readFileFromServer() throws IOException {
        RequestConfig config = RequestConfig.custom().setConnectTimeout(60000).setSocketTimeout(15000).build();
        CloseableHttpClient client = HttpClientBuilder.create().setDefaultRequestConfig(config).build();
        HttpGet request = new HttpGet(xx);
        HttpResponse response = client.execute(request);
        BufferedReader br = null;
        InputStreamReader isr = null;
        String str = null;
        /**请求发送成功，并得到响应**/
        long beginMillis = System.currentTimeMillis();
        if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
            isr = new InputStreamReader(response.getEntity().getContent(),"utf-8");
            br = new BufferedReader(isr);
            while ((str = br.readLine()) != null) {
                String[] parms = str.split("\t");
                List<String> list = trim(parms);
                if(list.size() == 6){
                    JDBCOperation.Insert(list.get(0),list.get(1),list.get(2),list.get(3),list.get(4),list.get(5));
                }
            }
        }
        isr.close();
        br.close();
        if (client != null) {
            client.close();
        }
        long endMillis = System.currentTimeMillis();
        System.out.println(((endMillis - beginMillis) / 1000) + "秒");
    }

    public static void main(String[] args) throws Exception {
        FileUtil.readFileFromServer();
    }
}



class JDBCOperation {

    public static int Insert(String... parms){
        PreparedStatement preStatement=null;//创建PreparedStatement对象
        Connection conn = null;
        try {
            //1、准备Connection连接数据库
            conn = JDBCDevice.getConnection();
            //2、准备sql语句
            //sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
            String sql="insert into p_ip_refrence(begin_ip,end_ip,country_code,province_code,city_code,isp_code) values(?,?,?,?,?,?)";
            //3、准备prepareStatement
            //对占位符设置值，占位符顺序从1开始，第一个参数是占位符的位置，第二个参数是占位符的值。
            preStatement=conn.prepareStatement(sql);
            //4、占位符设置值
            for(int i=0;i<parms.length;i++){
                preStatement.setObject(i+1, parms[i]);
            }
            //5、执行sql
            return preStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }finally{
            //6、关闭数据库等
            JDBCTools.closeConnection(null, preStatement, conn);
        }
    }

    public static int[] batchInsert(List<IpAddressInfo> addressInfos){
        PreparedStatement preStatement=null;//创建PreparedStatement对象
        Connection conn = null;
        try {
            //1、准备Connection连接数据库
            conn = JDBCDevice.getConnection();
            //2、准备sql语句
            //sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
            String sql="insert into p_ip_test(begin_ip,end_ip,country_code,province_code,city_code,isp_code) values(?,?,?,?,?,?)";
            //3、准备prepareStatement
            //对占位符设置值，占位符顺序从1开始，第一个参数是占位符的位置，第二个参数是占位符的值。
            preStatement=conn.prepareStatement(sql);
            //4、占位符设置值
            for(int i = 0; i < addressInfos.size(); i++){
                preStatement.setString(1,addressInfos.get(i).getBeginIp());
                preStatement.setString(2, addressInfos.get(i).getEndIp());
                preStatement.setString(3, addressInfos.get(i).getCountryCode());
                preStatement.setString(4, addressInfos.get(i).getProvinceCode());
                preStatement.setString(5, addressInfos.get(i).getCityCode());
                preStatement.setString(6, addressInfos.get(i).getIspCode());
                preStatement.addBatch();
            }
            //5、执行sql
            return preStatement.executeBatch();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return null;
        }finally{
            //6、关闭数据库等
            JDBCTools.closeConnection(null, preStatement, conn);
        }
    }
}

class JDBCDevice{

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String jdbcUrl = "jdbc:mysql://********:****/***?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false";
    private static final String user = "*****";
    private static final String password = "******";

    public static Connection getConnection() throws Exception{
        Connection conn= null;
        if(null == conn){
            Class.forName(driver);
            conn = DriverManager.getConnection(jdbcUrl, user, password);
        }
        return conn;
    }

    /**
     * 功能：关闭数据库连接
     * @param rs
     * @param statement
     * @param conn
     */
    public static void closeConnection(ResultSet rs, Statement statement, Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }
}

参考： https://www.cnblogs.com/yunqing/p/6149167.html

Ip和long互转

https://www.cnblogs.com/happyday56/p/4064776.html