最近做数据同步功能,从接口获取数据然后存到数据库中以便后续对数据进行相关操作,下面就贴一下相关代码。
- 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<String, Object> map = new HashMap<String, Object>();
- 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 JSONArray jsonArr = JSONArray.fromObject(list);
- 37
- 38 String dataName[] = new String[jsonArr.size()];
- 39 String eventType[] = new String[jsonArr.size()];
- 40 String eventTime[] = new String[jsonArr.size()];
- 41 String eventName[] = new String[jsonArr.size()];
- 42 String cardNo[] = new String[jsonArr.size()];
- 43 String personId[] = new String[jsonArr.size()];
- 44 String personName[] = new String[jsonArr.size()];
- 45 String deptName[] = new String[jsonArr.size()];
- 46
- 47 for (int i = 0; i < jsonArr.size(); i++) {
- 48
- 49 dataName[i] = jsonArr.getJSONObject(i).getString("dataName");
- 50 eventType[i] = jsonArr.getJSONObject(i).getString("eventType");
- 51 eventTime[i] = jsonArr.getJSONObject(i).getString("eventTime");
- 52 eventName[i] = jsonArr.getJSONObject(i).getString("eventName");
- 53 cardNo[i] = jsonArr.getJSONObject(i).getString("cardNo");
- 54 personId[i] = jsonArr.getJSONObject(i).getString("personId");
- 55 personName[i] = jsonArr.getJSONObject(i).getString("personName");
- 56 deptName[i] = jsonArr.getJSONObject(i).getString("deptName");
- 57 //如果得到的字段有null的,做相应处理
- 58 cardNo[i] = (cardNo[i] == "null") ? null + "," : "'"
- 59 + cardNo[i] + "'";
- 60 personName[i] = (personName[i] == "null") ? null + "," : "'"
- 61 + personName[i] + "',";
- 62 + deptUuid[i] + "',";
- 63 deptName[i] = (deptName[i] == "null") ? null + "," : "'"
- 64 + deptName[i] + "',";
- 65
- 66 strSQL = "INSERT into door_events_history values(AUTOID_SEQ.NEXTVAL,"
- 67 + "'"+ dataName[i]+ "','"+ eventType[i]+ ","+ eventTime[i]+ ",'"+ eventName[i]+ "',"+ cardNo[i]+ ","+ personId[i]+ ","+ personName[i]+ deptName[i] + ")";
- 68
- 69 try {
- 70 String sql = "select cardNo,eventTime from data where cardNo = "
- 71 + cardNo[i]
- 72 + " and eventTime = "
- 73 + eventTime[i];
- 74 ResultSet rs = stmt.executeQuery(sql); // 查询数据库看数据是否已经存在
- 75 if (rs.next()) { // 该条数据已经存在
- 76 } else {
- 77 stmt.executeUpdate(strSQL);
- 78 count++;
- 79 }
- 80 rs.close();
- 81 } catch (Exception e) {
- 82 e.printStackTrace();
- 83 } finally {
- 84
- 85 }
- 86 }
- 87
- 88 connection.commit();
- 89 } // for结束
- 90
- 91 // 先关闭Statement
- 92 if (stmt != null)
- 93 try {
- 94 stmt.close();
- 95 } catch (SQLException e) {
- 96 e.printStackTrace();
- 97 }
- 98 // 后关闭Connection
- 99 if (connection != null)
- 100 try {
- 101 connection.close();
- 102 } catch (SQLException e) {
- 103 e.printStackTrace();
- 104 }
- 105 log.info("当前时间===" + new Date());
- 106 log.info("同步结束");
- 107 log.info("共更新了"+ count + "条数据");
- 108 return count;
- 109 }
- 110 }
其中AUTOID_SEQ.NEXTVAL为Oracle中的自增序列
至此,数据已经同步到指定的数据库中啦,打完收工!
注意:拼接sql的时候一定要按照字段类型来看是否增加单引号,否则插入数据会报错。