世界ip地址查询，包括数据库和查询代码[原创]

这是我整理的一个ip地址查询数据库，希望能对大家有点帮助，我已经在我的项目里用了。
经过测试，在我的机器上查询一个ip大约耗时0.016秒，查询结果与ip138大部分一致

使用方法：
1.导入下面的sql脚本（本人是从mysql导出来的）
2.参照下面程序中得search方法，数据库相关参数记得改成你自己的，加上驱动包

import java.io.*;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;

public class Test {

	private static Connection conn;
	private static String driver = "com.mysql.jdbc.Driver"; 
	private static String ulr = "jdbc:mysql://数据库ip:3306/数据库名";
	private static String username = "数据库用户名";
	private static String pwd = "数据库密码";

	static {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(ulr, username, pwd);
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(-1);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(-1);
		}
	}

	/**
	 * @param args
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws IOException, SQLException {
		for (int i = 0; i < 50; i++) {
			testSearch();
		}
	}

	private static void testSearch() throws SQLException {
		String ip = getRandIp();
		String[] array = search(ip);
		if (array == null || array.length == 0)
			System.out.println("ip=" + ip + ",返回结果：null,null");
		if (array.length == 1)
			System.out.println("ip=" + ip + ",返回结果：" + array[0] + ",null");
		if (array.length == 2)
			System.out.println("ip=" + ip + ",返回结果：" + array[0] + ","
					+ array[1]);
	}

	/**
	 * 搜索ip地址所在城市的方法
	 * @param ip
	 * @return
	 * @throws SQLException
	 */
	private static String[] search(String ip) throws SQLException {
		long ipLong = parseLong(ip);
		String sql = "select * from ip_address where min_ip<=? order by min_ip desc LIMIT 1;";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setLong(1, ipLong);
		ResultSet rs = ps.executeQuery();
		Map<String, Object> minMap = null;
		if (rs.next()) {
			minMap = new HashMap<String, Object>();
			minMap.put("min_ip", rs.getLong("min_ip"));
			minMap.put("max_ip", rs.getLong("max_ip"));
			minMap.put("city", rs.getString("city"));
			minMap.put("line", rs.getString("line"));
		}
		rs.close();
		ps.close();

		sql = "select * from ip_address where max_ip<=? order by max_ip desc LIMIT 1;";
		ps = conn.prepareStatement(sql);
		ps.setLong(1, ipLong);
		rs = ps.executeQuery();
		Map<String, Object> maxMap = null;
		if (rs.next()) {
			maxMap = new HashMap<String, Object>();
			maxMap.put("min_ip", rs.getLong("min_ip"));
			maxMap.put("max_ip", rs.getLong("max_ip"));
			maxMap.put("city", rs.getString("city"));
			maxMap.put("line", rs.getString("line"));
		}
		rs.close();
		ps.close();

		if (minMap == null && maxMap == null)
			return null;
		else if (minMap == null && maxMap != null)
			return new String[] { getProperty(maxMap, "city"),
					getProperty(maxMap, "line") };
		else if (minMap != null && maxMap == null)
			return new String[] { getProperty(minMap, "city"),
					getProperty(minMap, "line") };
		else if (minMap != null && maxMap != null) {
			long min_ip = Long.parseLong(minMap.get("min_ip").toString());
			long max_ip = Long.parseLong(maxMap.get("max_ip").toString());
			if (Math.abs(min_ip - ipLong) <= Math.abs(max_ip - ipLong))
				return new String[] { getProperty(minMap, "city"),
						getProperty(minMap, "line") };
			else
				return new String[] { getProperty(maxMap, "city"),
						getProperty(maxMap, "line") };
		} else
			return null;
	}

	private static String getProperty(Map<String, Object> map, String key) {
		Object value = map.get(key);
		return value == null ? null : value.toString();
	}

	private static String getRandIp() {
		Random random = new Random();
		return random.nextInt(256) + "." + random.nextInt(256) + "."
				+ random.nextInt(256) + "." + random.nextInt(256);
	}

	private static long parseLong(String ipStr) {
		String[] ipArray = ipStr.split("\\.");
		long sum = 0;
		final long[] weights = { 256 * 256 * 256, 256 * 256, 256, 1 };
		for (int i = 0; i < 4; i++) {
			if (ipArray[i].trim().length() == 0)
				return -1;
			if (!ipArray[i].matches("^\\d+$"))
				return -1;
			int n = Integer.parseInt(ipArray[i]);
			if (n > 255)
				n = 255;
			if (n < 0)
				n = 0;
			sum += weights[i] * n;
		}
		return sum;
	}

}

评论

1 楼 doxw 2012-03-28  

谢谢分享