问题描述：
获取访问IP的地址数据并写入数据库
解决方案：
使用http://ip.taobao.com/service/getIpInfo.php?ip= 接口
返回结果样例：
{
"code":0,
"data":{
"ip":"117.147.169.51",
"country":"中国","area":"",
"region":"浙江","city":"绍兴",
"county":"XX",
"isp":"移动",
"country_id":"CN",
"area_id":"",
"region_id":"330000",
"city_id":"330600",
"county_id":"xx",
"isp_id":"100025"
}
}
源代码：
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
public class iphandle {
public static String connect(String urlStr) {
try {
StringBuilder sb = new StringBuilder();
URL url = new URL(urlStr);
// 得到connection对象。
HttpURLConnection connection = (HttpURLConnection) url
.openConnection();
// 设置请求方式
connection.setRequestMethod("GET");
// 连接
connection.connect();
// 得到响应码
int responseCode = connection.getResponseCode();
if (responseCode == HttpURLConnection.HTTP_OK) {
// 得到响应流
InputStream inputStream = connection.getInputStream();
// 获取响应
BufferedReader reader = new BufferedReader(
new InputStreamReader(inputStream, "utf-8"));
String line;
while ((line = reader.readLine()) != null) {
sb.append(line);
}
reader.close();
// 该干的都干完了,记得把连接断了
connection.disconnect();
return sb.toString();
}
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void main(String[] args) throws InterruptedException {
String sql = "select id,ip from IpVisitDetailes where id>2656 and id<=6000";
JSONArray jsonArray = JDBCTools.select(sql);
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = jsonArray.getJSONObject(i);
String ip = jsonObject.getString("ip");
String id = jsonObject.getString("id");
System.out.println(id);
String ipInfo = connect("http://ip.taobao.com/service/getIpInfo.php?ip="
+ ip);
if (ipInfo == null)
continue;
System.out.println(ipInfo);
if (ipInfo.contains("country\":\"中国")) {
JSONObject tmpJsonObject = JSONObject.parseObject(ipInfo);
tmpJsonObject = tmpJsonObject.getJSONObject("data");
String city = tmpJsonObject.getString("city");
String region = tmpJsonObject.getString("region");
String sql2 = "UPDATE IpVisitDetailes SET city=?,region=? WHERE id=?; ";
JDBCTools.update(sql2, city, region, id);
}
}
System.out.println("所有ip匹配成功");
}
}
附录：JDBC操作数据库的工具类（使用了JSON+可变参数）
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
/**
* 操作JDBC的工具类
* author:罗佳160341121
*/
public class JDBCTools {
/**
* 获取与数据库的连接
* 通过读取配置文件从数据库服务器获取一个连接
* @return
* @throws IOException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException{
Connection connection=null;
//1.读取配置文件
Properties properties=new Properties();
InputStream in=JDBCTools.class.getClassLoader().
getResourceAsStream("jdbc.properties");
properties.load(in);
//2.准备连接用的字符串
//驱动的全类名
String driver=properties.getProperty("driver");
//JDBC URL
String jdbcURL=properties.getProperty("jdbcURL");
//user
String user=properties.getProperty("user");
//password
String password=properties.getProperty("password");
//3.加载驱动程序
Class.forName(driver);
//4.建立连接
connection=DriverManager.getConnection(jdbcURL, user, password);
return connection;
}
/**
* 资源释放工具函数
* @param preparedStatement
* @param statement
* @param connection
* @param rs
*/
public static void releseSource(PreparedStatement preparedStatement,Statement statement,Connection connection,ResultSet rs){
//关闭连接
try {
if(connection!=null)connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
//关闭ResultSet对象
try {
if(rs!=null)rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
//关闭Statement对象
try {
if(statement!=null)statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
//关闭PreparedStatement对象
try {
if(preparedStatement!=null)preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 将数据集中的数据转成JSON数据格式
* 核心：使用了ResultSetMetaData查询结果的列名和类型
* @param resultSet 查询出来的数据集
* @return JSONArray 转换后的对象
*/
public static JSONArray resultSetToJSONArray(ResultSet resultSet) {
JSONArray jsonArray = new JSONArray();//所有数据
JSONObject rowObj = null;//每一行的数据
try {
//获取resultSet的元数据MetaData
ResultSetMetaData rsmd = resultSet.getMetaData();
while (resultSet.next()) {
rowObj = new JSONObject();
//获取列数
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnName(i);
String value = resultSet.getString(columnName);
rowObj.put(columnName, value);
}
jsonArray.add(rowObj);
}
} catch (SQLException e) {
e.printStackTrace();
}
return jsonArray;
}
/**
* 通用的数据更新方法
* 返回发生变化的行数
* @param sql
* @param args
* @return
*/
public static int update(String sql,String...args){
Connection connection=null;
PreparedStatement preparedStatement=null;
int result=-1;//返回值-变化行数
try {
connection=getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preparedStatement.setString(i+1, args[i]);
}
result=preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
releseSource(preparedStatement, null, connection, null);;
}
return result;
}
/**
* 通用的数据删除方法
* 返回发生变化的行数
* @param sql
* @param args
* @return
*/
public static int delete(String sql,String...args) {
Connection connection=null;
PreparedStatement preparedStatement=null;
int result=-1;//返回值-变化行数
try {
connection=JDBCTools.getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preparedStatement.setString(i+1, args[i]);
}
result=preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
releseSource(preparedStatement, null, connection, null);;
}
return result;
}
/**
* 数据库通用查询方法
* 返回JSON字符串
* @param sql
* @param args
* @return
*/
public static JSONArray select(String sql,String...args){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
JSONArray result=null;
try {
connection=getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preparedStatement.setString(i+1, args[i]);
}
resultSet=preparedStatement.executeQuery();
result=resultSetToJSONArray(resultSet);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
releseSource(preparedStatement, null, connection, resultSet);
}
return result;
}
public static int insert(String sql,String...args){
Connection connection=null;
PreparedStatement preparedStatement=null;
int result=-1;
try {
connection=getConnection();
preparedStatement=connection.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preparedStatement.setString(i+1, args[i]);
}
result=preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
releseSource(preparedStatement, null, connection, null);
}
return result;
}
public static void main(String[] args) throws IOException {
String sql="select ip from ip_page_number";
File f1 = new File("allip.txt");
FileOutputStream fos = new FileOutputStream(f1);
JSONArray jsonArray=select(sql);
for(int i=0;i<jsonArray.size();i++){
JSONObject jsonObject=jsonArray.getJSONObject(i);
String ip=jsonObject.getString("ip")+"\n";
fos.write(ip.getBytes());
}
fos.close();
}
}
数据库连接的配置文件
driver=com.mysql.jdbc.Driver
jdbcURL=jdbc:mysql://10.5.24.36:3306/weblog
user=root
password=root
