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

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 minMap = null;

if (rs.next()) {

minMap = new HashMap();

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 maxMap = null;

if (rs.next()) {

maxMap = new HashMap();

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 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;

}

}

下载次数: 119

分享到：

2011-12-05 16:52

浏览 2754

评论

1 楼

doxw

2012-03-28

谢谢分享