access 查询数据接口demo

背景:中间服务采集access数据库, 将采集的数据通过接口返回;

/**
     * 同步全部数据
     */
    @RequestMapping(value = "syncDataAll", method = RequestMethod.GET)
    @ResponseBody
    public CommonResult syncDataAll(@RequestParam String jsonData) throws IOException, SQLException, ClassNotFoundException {
        System.out.println("日期"+new Date() +"jsonData:"+jsonData);
        List<HashMap> hashMaps = accessService.syncData(jsonData);
        return CommonResult.success(hashMaps);
    }

方法:

@Override
    public List<HashMap> syncData(String jsonData) throws IOException {
        ObjectMapper objectMapper = new ObjectMapper();
        Map<String, Object> map = objectMapper.readValue(jsonData,
                new TypeReference<Map<String, Object>>() {
                });
        String tableName = (String) map.get("tableName");
        String dataUrl = (String) map.get("dataUrl");
        String userName = (String) map.get("userName");
        String password = (String) map.get("password");
        PreparedStatement ps = null;
        Connection ct = null;
        ResultSet rs = null;
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            //String url = "jdbc:ucanaccess://C:/Users/chengcheng/Desktop/accessDb/SDAF2000.MDB;openExclusive=false;ignoreCase;jackcessOpener=com.client.config.CryptCodecOpener";
            String url = "jdbc:ucanaccess:" + dataUrl +
                    ";openExclusive=false;ignoreCase;jackcessOpener=com.client.config.CryptCodecOpener";
            url = url.replace("\", "//");
            ct = DriverManager.getConnection(url, userName, password);//没有用户名和密码的时候直接为空
            if (tableName.equals("txsj")) {
                ps = ct.prepareStatement("select * from " + tableName + " order by syrq desc");
            } else if (tableName.equals("SDCH235")) {
                ps = ct.prepareStatement("select * from " + tableName + " order by 测试日期 desc");
            } else {
                ps = ct.prepareStatement("select * from " + tableName + " order by TestDate desc");
            }
            rs = ps.executeQuery();
            List<HashMap> arrayList = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                HashMap row = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    Object object = rs.getObject(i);
                    row.put(metaData.getColumnLabel(i), object);
                }
                arrayList.add(row);
            }
            System.out.println(arrayList);
            formatDate(arrayList);
            return arrayList;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }