jersist
2021-06-30 11:52
采纳率: 100%
浏览 134
已采纳

希望能实现从钉钉上获取考勤数据并自动写入/更新mysql数据库,恳求帮助

目前可以单独读取数据,但是无法自动将这些数据写入数据库。。。

这里是实现从钉钉上读取考勤数据的

public class DingRecordSyncController {
    public static String getAccessToken() {
        Map<String,Object> map = new HashMap<>();
        String corpId = "xxxxxx";
        map.put("corpid", corpId);
        String corpSecret = "xxxxxxxx";
        map.put("corpsecret", corpSecret);
        //通用
        String accessTokenUrl = "https://oapi.dingtalk.com/gettoken";
        return httpGetStringResult(accessTokenUrl, map);//获取access_token
    }



    public static String httpGetStringResult(String url,Map<String,Object> param){
        String content = null;
        CloseableHttpClient httpClient = HttpClients.createDefault();
        if(param != null && !param.isEmpty()){
            StringBuffer strparams = new StringBuffer();
            for (Map.Entry<String, Object> map : param.entrySet()) {
                strparams.append(map.getKey()).append("=").append(map.getValue().toString()).append("&");
            }
            strparams = strparams.deleteCharAt(strparams.length()-1);
            url = url + "?" + strparams;
        }
        HttpGet httpGet = new HttpGet(url);
        CloseableHttpResponse response = null;

        try {
            response = httpClient.execute(httpGet);
            HttpEntity entity = response.getEntity();
            content = EntityUtils.toString(entity,"UTF-8");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if(null!=response){
                    response.close();
                }
            } catch (IOException e) {

                e.printStackTrace();
            }
        }

        return content;
    }

    public static String doPost(String requestUrl,JSONObject json){
        HttpClient client = new DefaultHttpClient();
        HttpPost post = new HttpPost(requestUrl);
        post.setHeader("Content-Type", "application/json");
        post.addHeader("Authorization", "Basic YWRtaW46");
        String result = "";
        try {
            StringEntity s = new StringEntity(json.toString(), "utf-8");
            s.setContentEncoding(new BasicHeader("contentType",
                    "application/json"));
            post.setEntity(s);
            // 发送请求
            HttpResponse httpResponse = client.execute(post);
            // 获取响应输入流
            InputStream inStream = httpResponse.getEntity().getContent();
            BufferedReader reader = new BufferedReader(new InputStreamReader(inStream, StandardCharsets.UTF_8));
            StringBuilder strber = new StringBuilder();
            String line = null;
            while ((line = reader.readLine()) != null)
                strber.append(line).append("\n");
            inStream.close();
            result = strber.toString();
            System.out.println(result);
            if (httpResponse.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {

                System.out.println("请求服务器成功,做相应处理");

            } else {
                System.out.println("请求服务端失败");
            }
        } catch (Exception e) {
            System.out.println("请求异常");
            throw new RuntimeException(e);
        }
        return result;
    }

    public static void getAttendances(List<AttendanceQvo> users, String workDateFrom, String workDateTo, String access_Token) {
        List<String> list = Arrays.asList("01175538335835636975","1508444258660454","0127414515631031760","012535596746860683","0127415710271256414","0127415710271256414");//这里我只会固定写员工ID,如果大佬愿意完善那就太棒了(这个问题可以忽略)
        int listSize=list.size();
        int toIndex = 50;
        for(int i = 0;i < list.size();i += 50){
            if(i+50 > listSize){        //作用为toIndex最后没有50条数据则剩余几条newList中就装几条
                toIndex = listSize-i;
            }
            List<String> newList = list.subList(i,i + toIndex);
            Boolean hasMore = true;
            int offset = 0;//为了分页
            do{
                Map<String,Object> mapParam = new HashMap<>();
                mapParam.put("workDateFrom", workDateFrom);
                mapParam.put("workDateTo", workDateTo);
                mapParam.put("userIdList", newList);
                mapParam.put("offset", offset * 50);
                mapParam.put("limit", 50);
                String attendanceStr = getAttendance(mapParam, access_Token);
                JSONObject firstJson = JSONObject.parseObject(attendanceStr);

                hasMore = firstJson.getBoolean("hasMore");
                JSONArray recordFirst = firstJson.getJSONArray("recordresult");//当前部门下的userList
                for(int j = 0;j < recordFirst.size(); j++) {
                    JSONObject record = recordFirst.getJSONObject(j);
                    AttendanceQvo attendanceQvo = new AttendanceQvo();

                    attendanceQvo.setCheckType(record.getString("checkType"));
                    attendanceQvo.setUserId(record.getString("userId"));
                    attendanceQvo.setWorkDate(record.getLong("workDate"));
                    attendanceQvo.setUserCheckTime(record.getLong("userCheckTime"));
                    attendanceQvo.setGroupId(record.getInteger("groupId"));
                    users.add(attendanceQvo);

                }
                if(hasMore) {//有下一页偏移量加一
                    offset++;
                }
            } while (hasMore);
        }
    }
    public static String getAttendance(Map<String, Object> map ,String access_token_str) {
        String dingDingAttendance = "https://oapi.dingtalk.com/attendance/list?access_token="+access_token_str;
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("workDateFrom",map.get("workDateFrom"));
        jsonObject.put("workDateTo",map.get("workDateTo"));
        jsonObject.put("limit",map.get("limit"));
        jsonObject.put("offset",map.get("offset"));
        jsonObject.put("userIdList",map.get("userIdList"));
        return doPost(dingDingAttendance,jsonObject);//获取考勤记录

    }



    public static String getPreDayOrAfterDay(String current, int flag) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();//获取日历实例
        try {
            calendar.setTime(sdf.parse(current));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        calendar.add(Calendar.DAY_OF_MONTH, flag);  //设置为前一天
        String yesterday = sdf.format(calendar.getTime());//获得前一天

        return yesterday;
    }

    public static void main(String[] args) {
        List<AttendanceQvo> users = new ArrayList<>();
        Date date = new Date();
        String currentDate = new SimpleDateFormat("yyyy-MM-dd").format(date);
        String yesterday = getPreDayOrAfterDay(currentDate, -1);
        String workDateFrom = yesterday + " " + "06:00:00";
        String workDateTo = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);//当前时间
        String access_token_str = getAccessToken();//获取access_token
        JSONObject jsonStr = JSONObject.parseObject(access_token_str); //转json结构
        String access_Token = jsonStr.getString("access_token");//获取access_Token
        getAttendances(users, workDateFrom,workDateTo, access_Token);//凌晨的
        System.out.println("获取从"+workDateFrom+"到"+workDateTo+"时间的打卡记录"+ Arrays.asList(users));
    }

以上是我的src/main/java/ding/DingRecordSyncController.java, 目录是这样的

img

AttendanceQvo里是这样的:

public class AttendanceQvo implements Serializable {
    private String checkType;     //上下午
    private String userId;        //员工ID
    private long userCheckTime;//实际打卡时间
    private long workDate;      //工作日
    private int groupId;
}

j2m是连接并输出到数据库(百度的,只能实现输入固定值,想要实现将上述读取的钉钉考勤数据写入到数据库)

public class j2m {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/kaoqin?useUnicode=true&characterEncoding=utf-8&useSSL=false";
            String user = "root";
            String password = "xxxx";
            List<AttendanceQvo> users = new ArrayList<>();
            Arrays.asList(users);
  
            // 建立数据库连接,获得连接对象conn
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "insert into kqdemo1 (checkType,userId,userCheckTime,workDate) values(?,?,?,?)";

            PreparedStatement ps = conn.prepareStatement(sql);


            ps.setString(1,"");
            ps.setString(2, "");
            ps.setString(3, "");
            ps.setString(4, "");
            int count1=ps.executeUpdate();
            conn.close();
            System.out.println("操作完毕!"+"更新数据的行数:"+count1);
        } catch (ClassNotFoundException  | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

自学小白一枚,恳求大佬花些时间指点一下,十分感谢!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • Silence丶你的名字 2021-06-30 12:01
    已采纳

    是mysql数据库吗? 是只需要把读取到的数据写到数据库就好还是有其他要求

    点赞 打赏 评论
  • Lymj丶 2021-06-30 13:39

    读取到数据之后,在数据库建表,然后改插入语句就可以了鸭

    点赞 打赏 评论
  • 大黄瓜de冬天 2021-06-30 14:24

    你获取数据用的json,解析json拿到集合,在循环集合得到具体数据,循环插入到1,2,3,4就可以了。如果要优化,那可以改成最后只提交一条sql语句,批量插入写法

    img

    点赞 打赏 评论

相关推荐 更多相似问题