Java之通過介面獲取數據並用JDBC存儲到資料庫中

最近做數據同步功能，從介面獲取數據然後存到資料庫中以便後續對數據進行相關操作，下麵就貼一下相關代碼。從介面拿到數據後，下麵就將數據存到資料庫中：其中AUTOID_SEQ.NEXTVAL為Oracle中的自增序列至此，數據已經同步到指定的資料庫中啦，打完收工！註意：拼接sql的時候一定要按照字 ...

最近做數據同步功能，從介面獲取數據然後存到資料庫中以便後續對數據進行相關操作，下麵就貼一下相關代碼。

 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的時候一定要按照欄位類型來看是否增加單引號，否則插入數據會報錯。