纯真ip数据库与全国行政区域规划关联
- - Java - 编程语言 - ITeye博客想实现一个功能:根据用户在公网上的ip地址对应到该用户所在的行政区划,精确到县. 这个功能能不能实现的关键还是在能不能找到一个数据较为完善的ip地址库. 在网上找了一下,还是觉得纯真ip数据库好用. 1 下载纯真ip数据库,然后解压成文件导入自己建的数据库(见附件). 2 下载全国省市县行政规划(见附件).
package com.auto; import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; 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; /** * * @author Feng * */ public class UpdateIPData { ArrayList<String[]> queryList = new ArrayList<String[]>(); public static void main(String argv[]) throws SQLException{ Connection conn = null; String url = null; String user = null; String password = null; String sql = null; UpdateIPData imp = new UpdateIPData(); try { Class.forName("com.mysql.jdbc.Driver"); //加载mysq驱动 } catch (ClassNotFoundException e) { System.out.println("驱动加载错误"); e.printStackTrace();//打印出错详细信息 } try { url = "jdbc:mysql://localhost:3306/git_jeeshop?useUnicode=true&characterEncoding=UTF-8"; user = "root"; password = "root"; conn = DriverManager.getConnection(url,user,password); } catch (SQLException e) { System.out.println("数据库链接错误"); e.printStackTrace(); } new UpdateIPData().updateCode(conn); conn.close(); } public void updateCode(Connection conn) throws SQLException { //查询出 全国省市编码的编号和区域 ,以便后面做匹配 String querySql = "select code,name from t_area "; Statement pstm = conn.createStatement(); ResultSet rs = pstm.executeQuery(querySql); while(rs.next()) { String code = rs.getString("code"); String name = rs.getString("name"); String[] strArray = {code,name}; queryList.add(strArray); } //查询出需要修改的数据 String sql = "select ip_from ,descname,code from t_ipcode where code is null"; rs = pstm.executeQuery(sql); ArrayList<String[]> updateList = new ArrayList<String[]>(); while(rs.next()) { long ipFrom = rs.getLong("ip_from"); String descName = rs.getString("descname"); String code = rs.getString("code"); String[] strArray = {String.valueOf(ipFrom),descName,code}; updateList.add(strArray); } //修改数据 String updateSql = "update t_ipcode set code =? where ip_from =?"; PreparedStatement pstm2 = conn.prepareStatement(updateSql); conn.setAutoCommit(false); List<String[]> provenceData = this.getChildName(null); long index = 0; System.out.println("----------开始循环t_ipcode表"); for(String[] strArray:updateList) {//循环t_ipcode表 index++; String descName = strArray[1];//ip地址对应的地区的名字 String provenceCode = ""; String cityCode = ""; String countryCode = ""; //找省份的编码 for(String[] provence:provenceData) { String code = provence[0]; String name = provence[1]; if(descName.contains(name)) { //如果找到当前省的code provenceCode = code; } } if(provenceCode.equals("")) { System.out.println("-----------descName:"+descName+"未找到省份编码"); continue; }else { //找城市的编码 List<String[]> cityList = this.getChildName(provenceCode); for(String[] city:cityList) { String code = city[0]; String name = city[1]; if(descName.contains(name)) { //如果找到当前市的code cityCode = code; } } if(cityCode.equals("")) { pstm2.setLong(2, Long.valueOf(strArray[0])); pstm2.setString(1, provenceCode); pstm2.addBatch(); }else { //找县的编码 List<String[]> countryList = this.getChildName(cityCode); for(String[] country:countryList) { String code = country[0]; String name = country[1]; if(descName.contains(name)) { //如果找到当前县的code countryCode = code; } } if(countryCode.equals("")) { pstm2.setLong(2, Long.valueOf(strArray[0])); pstm2.setString(1, cityCode); pstm2.addBatch(); }else { pstm2.setLong(2, Long.valueOf(strArray[0])); pstm2.setString(1, countryCode); pstm2.addBatch(); } } } if(index%1000==0) { pstm2.executeBatch(); System.out.println("已经提交:"+index+"行"); } } pstm2.executeBatch(); conn.setAutoCommit(true); conn.close(); } /** * 获取省市县数据的方法 * @return */ private List<String[]> getChildName(String code) { List<String[]> list = new ArrayList<String[]>(); for(String[] strArray:queryList) { if(code==null) { //查找省级数据 if(strArray[0].endsWith("0000")) { String[] s = {strArray[0],strArray[1]}; list.add(s); } }else { //查找当前数据的子数据 code = code.replace("0", ""); if(strArray[0].startsWith(code)) { String[] s = {strArray[0],strArray[1]}; list.add(s); } } } return list; } }