通过Java实现url接口返回的Json数据定时入库
一、概述
接到一个需求,需要访问url并传入参数,将url返回Json数据入库进行筛选分析。
数据的筛选分析这里掠过,主要记录通过使用Java实现数据的定时入库。
二、处理逻辑
1.通过Java访问url,得到返回的Json数据。
2.提取Json数据。
3.数据入库(效率问题)。
4.服务器配置定时任务。
三、具体实现
1.访问处理url并返回Json的类:HttpRequestUtil
package Arvin.JsonObj2;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
public class HttpRequestUtil {
public static String getJsonContent(String urlStr) {
try {
// obtain HttpURLConnection Object
URL url = new URL(urlStr);
HttpURLConnection httpCon = (HttpURLConnection) url.openConnection();
httpCon.setConnectTimeout(3000);
httpCon.setDoInput(true);
httpCon.setRequestMethod("GET");
// 获取相应码
int respCode = httpCon.getResponseCode();
if (respCode == 200) {
return ConvertStream2Json(httpCon.getInputStream());
}
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return "";
}
private static String ConvertStream2Json(InputStream inputStream) {
String jsonStr = "";
// ByteArrayOutputStream相当于内存输出流
ByteArrayOutputStream out = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len = 0;
// 将输入流转移到内存输出流中
try {
while ((len = inputStream.read(buffer, 0, buffer.length)) != -1) {
out.write(buffer, 0, len);
}
// 将内存流转换为字符串
jsonStr = new String(out.toByteArray());
} catch (IOException e) {
e.printStackTrace();
}
return jsonStr;
}
}
2.Oracle数据库操作类:InsertOrl
package Arvin.JsonObj2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class InsertOrl {
private static String USERNAME = "user";
private static String PASSWORD = "pwd";
private static String DRIVER = "oracle.jdbc.OracleDriver";
private static String URL = "jdbc:oracle:thin:@1127.0.0.1:1521:orcl";
private static String timeStr = "yyyyMMdd";
// 建立数据库连接
Connection connection = null;
// 预编译语句对象
PreparedStatement pstm = null;
// 结果集对象
ResultSet rs = null;
SimpleDateFormat sf = new SimpleDateFormat(timeStr);
String pt_d = sf.format(System.currentTimeMillis());
/**
* 获取Connection对象
*
* @return
*/
public Connection getConnection() {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (ClassNotFoundException e) {
throw new RuntimeException("class not find !", e);
} catch (SQLException e) {
throw new RuntimeException("get connection error!", e);
}
return connection;
}
/**
* 查询数据
*
* @throws SQLException
*/
public String SelectData(String tableName) throws SQLException {
connection = getConnection();
String sqlStr = "select cell_id, lac from " + tableName + " where 1 = 1";
pstm = connection.prepareStatement(sqlStr);
// pstm.setString(1, tableName);
rs = pstm.executeQuery();
String cellLac = "";
while (rs.next()) {
String cellId = rs.getString("cell_id");
String lac = rs.getString("lac");
if (cellLac == "") {
cellLac = cellLac + cellId + "," + lac;
} else {
cellLac = cellLac + "|" + cellId + "," + lac;
}
}
return cellLac;
}
/**
* 向数据库中删除今天的数据
*
*/
public void DeleteData() {
// String sql = "delete from GY_4G_LJB where pt_d = pt_d";
connection = getConnection();
String sqlStr = "delete from GY_4G_LJB where pt_d=?";
try {
// 执行删除数据操作
pstm = connection.prepareStatement(sqlStr);
pstm.setString(1, pt_d);
pstm.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ReleaseResource();
}
}
/**
* 添加数据
*
* @param cellId
* @param lac
* @param age
* @param address
* @throws SQLException
*/
public void AddData(List<List<String>> list, int batchSize) throws SQLException {
connection = getConnection();
connection.setAutoCommit(false);
// String sql = "insert into GY_4G_LJB values('1','25555','170***','2018')";
// String sql = "select count(*) from idstapp.GY_4G_LJB where 1 = 1";
String sqlStr = "insert into GY_4G_LJB values(?,?,?,?,?,?)";
int count = 0;
pstm = connection.prepareStatement(sqlStr);
for (Object arrayList : list) {
pstm.setString(1, (String) ((ArrayList<?>) arrayList).get(0));
pstm.setString(2, (String) ((ArrayList<?>) arrayList).get(1));
pstm.setString(3, (String) ((ArrayList<?>) arrayList).get(2));
pstm.setString(4, (String) ((ArrayList<?>) arrayList).get(3));
pstm.setString(5, (String) ((ArrayList<?>) arrayList).get(4));
pstm.setString(6, (String) ((ArrayList<?>) arrayList).get(5));
// 把一个SQL命令加入命令列表
pstm.addBatch();
if (++count % batchSize == 0) {
pstm.executeBatch();
count = 0;
}
}
// 执行批量更新
pstm.executeBatch();
// 语句执行完毕,提交本事务
connection.commit();
pstm.close();
connection.close();
}
/**
* 释放资源
*/
public void ReleaseResource() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.主函数,解析Json中的数据并入库:App
package Arvin.JsonObj2;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
/**
* /home/jsdxadm/jars
*
* @author Arvin
*
*/
public class App {
private static String JSON_URL = "http:url=";
private static String timeStr = "yyyyMMdd";
final static int batchSize = 10000;
public static void main(String[] args) throws SQLException {
// 开始时间
Long startTime = System.currentTimeMillis();
// 获取TimeFlag字段
SimpleDateFormat sf = new SimpleDateFormat(timeStr);
String pt_d = sf.format(System.currentTimeMillis());
// 获取时间标志:时分
String timeFlag = new SimpleDateFormat("yyMMddHHmm").format(System.currentTimeMillis());
InsertOrl io = new InsertOrl();
// 拼接接口URL
String cellLac = io.SelectData("gy_cell_lac");
String ALL_URL = JSON_URL + cellLac;
List<List<String>> list = new ArrayList<List<String>>();
// JSON转化为字符串
String s = HttpRequestUtil.getJsonContent(ALL_URL);
JSONObject ja = JSON.parseObject(s);
// 获取JSON中的电话和时间并插入数据库
if (ja instanceof Map) {
Map<?, ?> mRst = (Map<?, ?>) ja.get("result");
// System.out.println("mRst:" + mRst);
List<?> lUser = (List<?>) mRst.get("sectorUsersList");
// System.out.println("lUser:" + lUser);
for (Object object : lUser) {
// "cellId":"49","lac":"255555"
Map<?, ?> mo = (Map<?, ?>) object;
List<?> lPOJO = (List<?>) mo.get("userPOJOList");
String cellId = mo.get("cellId").toString();
String lac = mo.get("lac").toString();
for (Object object2 : lPOJO) {
List<String> li = new ArrayList<String>();
Map<?, ?> mo2 = (Map<?, ?>) object2;
String uPhone = mo2.get("phone").toString();
String uTime = mo2.get("time").toString();
li.add(cellId);
li.add(lac);
li.add(uPhone);
li.add(uTime);
li.add(pt_d);
li.add(timeFlag);
list.add(li);
}
}
io.AddData(list, batchSize);
// System.out.println("数据插入完成");
Long endTime = System.currentTimeMillis();
System.out.println("Done---Time:" + timeFlag + "---Run:" + (endTime - startTime) / 1000 + "s");
}
}
}
4.将Java程序打包,放置在服务器,并设置定时任务。
4.1打Jar包
4.2定时任务
$ crontab -e
添加一下定时任务,每十分钟执行一次
*/10 * * * * /etc/alternatives/java -Xms256M -Xmx256M -jar /home/vmuser/java/JO.jar >>/home/vmuser/java/JO.out 2>&1
至此任务完成。