java从端口获取数据库_Java之通过接口获取数据并用JDBC存储到数据库中

最近做数据同步功能，从接口获取数据然后存到数据库中以便后续对数据进行相关操作，下面就贴一下相关代码。

1 import com.alibaba.fastjson.JSON;

2 import com.alibaba.fastjson.JSONObject;

3

4 public class Digests {

5 private static final String APPKEY = "appkey";

6 private static final String SECRET = "secret";

7 private static final String OPENAPI_IP_PORT_HTTP = "ip";

8

9 /**

10 * 分页获取数据。

11 */

12 private static final String GET_DATA = "balabala";

13

14 //md5加密

15 public static final String md5(String s) {

16 char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',

17 'a', 'b', 'c', 'd', 'e', 'f' };

18 try {

19 MessageDigest mdTemp = MessageDigest.getInstance("MD5");

20 try {

21 mdTemp.update(s.getBytes("UTF-8"));

22 } catch (UnsupportedEncodingException e) {

23 mdTemp.update(s.getBytes());

24 }

25 byte[] md = mdTemp.digest();

26 int j = md.length;

27 char[] str = new char[j * 2];

28 int k = 0;

29 for (int i = 0; i < j; i) {

30 byte byte0 = md[i];

31 str[(k )] = hexDigits[(byte0 >>> 4 & 0xF)];

32 str[(k )] = hexDigits[(byte0 & 0xF)];

33 }

34 return new String(str).toUpperCase();

35 } catch (Exception e) {

36 }

37 return null;

38 }

39

40 //创建token

41 public static final String buildToken(String url, String paramJson,

42 String secret) {

43 String tempUrl = null;

44 if (url.contains("https://"))

45 tempUrl = url.substring("https://".length());

46 else {

47 tempUrl = url.substring("http://".length());

48 }

49 int index = tempUrl.indexOf("/");

50 String URI = tempUrl.substring(index);

51 String[] ss = URI.split("\\?");

52 if (ss.length > 1) {

53 return md5(ss[0] ss[1] secret);

54 }

55 return md5(ss[0] paramJson secret);

56 }

57

58 /**

59 * HTTP方式 分页获取数据。

60 */

61 public static String getData(double pageNo, Long startTime) throws Exception { //第一个参数是当前页数，第二个参数是请求数据的开始时间(为毫秒数)

62 String url = OPENAPI_IP_PORT_HTTP GET_DATA ;

63 Map map = new HashMap();

64 JSONObject jsonObject = JSONObject.parseObject(getDefaultUserUUID());

65 String opUserUuid = jsonObject.getString("data");

66 //System.out.println(opUserUuid);

67 map.put("appkey", APPKEY);// 设置APPKEY

68 map.put("time", System.currentTimeMillis());// 设置时间参数

69 map.put("pageNo", pageNo);// 设置当前页数

70 map.put("pageSize", 1000);// 设置一页多少条

71 map.put("opUserUuid", opUserUuid);// 设置操作用户UUID

72 map.put("startTime", startTime);// 设置开始时间

73 map.put("endTime", System.currentTimeMillis());// 设置结束时间

74 String params = JSON.toJSONString(map);

75 System.out.println(" ====== getData请求参数：【" params "】");

76 String data = HttpClientSSLUtils.doPost(

77 url "?token="

78 Digests.buildToken(url "?" params, null, SECRET),

79 params);

80 System.out.println(" ====== getData请求返回结果：【{" data "}】");

81

82 return data;

83 }

84 }

从接口拿到数据后，下面就将数据存到数据库中：

1 import net.sf.json.JSONArray;

2 import net.sf.json.JSONObject;

3

4 public class SyncDataFn {

5

6 public int jxJson() throws Exception {

7 //此处省略数据库连接相关语句，具体见上一篇properties配置文件连接数据库

8

9 // 创建Statement用于执行SQL语句

10 connection.setAutoCommit(false);

11 stmt = connection.createStatement();

12

13 Long maxtime;

14 String sqlMaxtime = "select max(eventTime) as maxTime from data";

15 ResultSet rs1 = stmt.executeQuery(sqlMaxtime); // 查询数据库看数据是否已经存在，表示只更新没有更新进来的数据

16 if (rs1.next()) { // 该条数据存在

17 maxtime = rs1.getLong("maxTime");

18 } else {

19 maxtime = (long) 0;

20 }

21 rs1.close();

22

23 //得到json数据

24 String json = Digests.getDoorEventsHistory(1, maxtime);

25 JSONObject jsonObject = (JSONObject) JSONObject.fromObject(json);

26 String to = (String) jsonObject.getString("data");

27 JSONObject toObject = JSONObject.fromObject(to);

28 double total = Integer.parseInt(toObject.getString("total"));

29 int page = (int) Math.ceil(total / 1000);

30 for (double k = 1; k <= page; k ) {

31

32 //得到json数据

33 String jsonTemp = Digests.getData(k, maxtime);

34 String data = JSONObject.fromObject(jsonTemp).getString("data");

35 String list = JSONObject.fromObject(data).getString("list");

36

37

38 JSONArray jsonArr = JSONArray.fromObject(list);

39

40 String dataName[] = new String[jsonArr.size()];

41 String eventType[] = new String[jsonArr.size()];

42 String eventTime[] = new String[jsonArr.size()];

43 String eventName[] = new String[jsonArr.size()];

44 String cardNo[] = new String[jsonArr.size()];

45 String personId[] = new String[jsonArr.size()];

46 String personName[] = new String[jsonArr.size()];

47 String deptName[] = new String[jsonArr.size()];

48

49 for (int i = 0; i < jsonArr.size(); i ) {

50

51 dataName[i] = jsonArr.getJSONObject(i).getString("dataName");

52 eventType[i] = jsonArr.getJSONObject(i).getString("eventType");

53 eventTime[i] = jsonArr.getJSONObject(i).getString("eventTime");

54 eventName[i] = jsonArr.getJSONObject(i).getString("eventName");

55 cardNo[i] = jsonArr.getJSONObject(i).getString("cardNo");

56 personId[i] = jsonArr.getJSONObject(i).getString("personId");

57 personName[i] = jsonArr.getJSONObject(i).getString("personName");

58 deptName[i] = jsonArr.getJSONObject(i).getString("deptName");

59 //如果得到的字段有null的，做相应处理

60 cardNo[i] = (cardNo[i] == "null") ? null "," : "'"

61 cardNo[i] "'";

62 personName[i] = (personName[i] == "null") ? null "," : "'"

63 personName[i] "',";

64 deptUuid[i] "',";

65 deptName[i] = (deptName[i] == "null") ? null "," : "'"

66 deptName[i] "',";

67

68 strSQL = "INSERT into door_events_history values(AUTOID_SEQ.NEXTVAL,"

69 "'" dataName[i] "','" eventType[i] "," eventTime[i] ",'" eventName[i] "'," cardNo[i] "," personId[i] "," personName[i] deptName[i] ")";

70

71 try {

72 String sql = "select cardNo,eventTime from data where cardNo = "

73 cardNo[i]

74 " and eventTime = "

75 eventTime[i];

76 ResultSet rs = stmt.executeQuery(sql); // 查询数据库看数据是否已经存在

77 if (rs.next()) { // 该条数据已经存在

78 } else {

79 stmt.executeUpdate(strSQL);

80 count ;

81 }

82 rs.close();

83 } catch (Exception e) {

84 e.printStackTrace();

85 } finally {

86

87 }

88 }

89

90 connection.commit();

91 } // for结束

92

93 // 先关闭Statement

94 if (stmt != null)

95 try {

96 stmt.close();

97 } catch (SQLException e) {

98 e.printStackTrace();

99 }

100 // 后关闭Connection

101 if (connection != null)

102 try {

103 connection.close();

104 } catch (SQLException e) {

105 e.printStackTrace();

106 }

107 log.info("当前时间===" new Date());

108 log.info("同步结束");

109 log.info("共更新了" count "条数据");

110 return count;

111 }

112 }

其中AUTOID_SEQ.NEXTVAL为Oracle中的自增序列

至此，数据已经同步到指定的数据库中啦，打完收工！

注意：拼接sql的时候一定要按照字段类型来看是否增加单引号，否则插入数据会报错。来源：https://www.icode9.com/content-2-358051.html