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<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的时候一定要按照字段类型来看是否增加单引号，否则插入数据会报错。