更新ip库

2019独角兽企业重金招聘Python工程师标准>>>

import java.io.File; 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.HashMap; import java.util.Map;

public class UpdateMysql implements Runnable { String sql = ""; public UpdateMysql(String sql) { try { this.sql = sql; } catch (Exception ex) { ex.printStackTrace(); } }

@Override
public void run() {
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		String db = "com.mysql.jdbc.Driver";
	    Class.forName(db);
		con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/数据库名?user=root&password=&useUnicode=true&characterEncoding=utf-8");
		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);
		
		StringBuffer bfResult = new StringBuffer();
		
		while (rs.next()) {
			String info = rs.getString("ip_end");
			String[] names = info.split("\\.");
			String ip = "";
			for (String s : names) {
				int d = 0;
				if (!s.equals("000")) {
					d = Integer.parseInt(s);
				}
				if (ip.length() == 0) {
					ip += String.valueOf(Integer.valueOf(s));
				} else {
					ip += "." + String.valueOf(Integer.valueOf(s));
				}
			}
			String uri = "http://ip.taobao.com/service/getIpInfo.php?ip="
					+ ip;
			String str_country = "";
			String str_isp = "";
			String str_region = "";
			String str_county = "";
			String str_city = "";
			try {
				String content = HttpUtils.getFromURI(uri);
				System.out.println(uri);
				System.out.println(content);
				Map contentObj = AgentUtils.getObjectMapper().readValue(
						content, Map.class);
				String code = String.valueOf(contentObj.get("code"));
				if (code == "1") {
					System.out.println(contentObj.get("data"));
					continue;
				}
				Object _data = contentObj.get("data");
				if (_data instanceof String) {
					System.err.println("-=========================== 无效ip地址："+ info + " -===========================-");
					continue;
				}
				Map data = (Map) _data;
				String country = (String) data.get("country");
				String countryId = (String) data.get("country_id");
				if (country == "" || "IANA".equals(countryId.toUpperCase())) {
					continue;
				}
				String isp = (String) data.get("isp");
				if (isp == "") {
					continue;
				}
				String region = (String) data.get("region");
				if (region == "") {
					continue;
				}
				String county = (String) data.get("county");
				if (county == "") {
					continue;
				}
				String city = (String) data.get("city");
				if (city == "") {
					continue;
				}
				str_country = new String(country.getBytes(), "UTF-8");
				str_isp = new String(isp.getBytes(), "UTF-8");
				str_region = new String(region.getBytes(), "UTF-8");
				str_county = new String(county.getBytes(), "UTF-8");
				str_city = new String(city.getBytes(), "UTF-8");
				
				if (rs.getString("province").equals(str_region)
						&& rs.getString("county").equals(str_county)
						&& rs.getString("city").equals(str_city)) {
					continue;
				}
				
				Map m = new HashMap();
				m.put("id", rs.getInt("id"));
				m.put("ip_start", rs.getString("ip_start"));
				m.put("ip_end", rs.getString("ip_end"));
				m.put("country", str_country);
				m.put("province", str_region);
				m.put("city", str_city);
				m.put("county", str_county);
				m.put("operators", str_isp);
				String sql = "update ip_location set province = '"
						+ str_region + "', city = '" + str_city
						+ "', county = '" + str_county
						+ "'where ip_end = '" + info + "'";
				PreparedStatement ps = con.prepareStatement(sql);
				ps.executeUpdate();
				
				bfResult.append(AgentUtils.getObjectMapper().writeValueAsString(m));
				

			} catch (Exception e) {
				System.out.println("国家/地区: " + str_country);
				System.out.println("运营商: " + str_isp);
				System.out.println("省份: " + str_region);
				System.out.println("县: " + str_county);
				System.out.println("城市: " + str_city);
				System.out.println("error of is : " + uri);

				e.printStackTrace();
			}
		}
		AgentUtils.getObjectMapper().writeValue(new File("C:/Users/root/Desktop/Test.txt"), bfResult.toString());
		System.out.println("It is done....");
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

public static void main(String[] args) {
	int threads = 1;
	int nums = 1000;
	int total = 444956;
	if (total % nums == 0) {
		threads = total / nums;
	} else {
		threads = total / nums + 1;
	}
	for (int i = 0; i < threads; i++) {
		String sql = "select * from ip_location where id between "
				+ (nums * i + 1) + " and " + nums * (i + 1);
		System.out.println(sql);
		UpdateMysql upd = new UpdateMysql(sql);
		upd.run();
	}
}

} import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors;

public class ThreadUpdater {

public static void main(String[] args) {
	try {
		System.out.println("---------starting update ip location data--------------");
		int threads = 1;
		int nums = 1000;
		int total = 444956;
		if (total % nums == 0) {
			threads = total / nums;
		} else {
			threads = total / nums + 1;
		}
		
		//
		ExecutorService threadPool = Executors.newFixedThreadPool(1);
		
		int i = 0;
		for (int i = 0; i < threads; i++) {
			String sql = "select * from ip_location where id between " + (nums * i + 1) + " and " + nums * (i + 1);
			System.out.println(sql);
			threadPool.execute(new UpdateMysql(sql));
		}

		Thread.sleep(60 * 1000);

		threadPool.shutdown();

	} catch (Exception ex) {
		ex.printStackTrace();
	}
}

转载于:https://my.oschina.net/u/2274625/blog/355630