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
https://www.cnblogs.com/happyday56/p/4064776.html