关于Spring boot 框架下的Access数据库分页访问请求，并遍历数据

关于Spring boot 框架下的Access数据库分页访问请求，并遍历数据

1. 添加依赖

<dependency>
      <groupId>com.hxtt</groupId>
      <artifactId>access-jdbc</artifactId>
      <version>3.0</version>
</dependency>

2. 字段数组（放置对应的对象中）

public static String[] column = {"DID","SID","DataTime","S1","S2","R1","R2","IsWarning"};

3. 数据访问层和业务逻辑层（直接贴代码）

   /**
     * 数据库的绝对路径
     */
    @Value("${datasource.access-url}")
    private String url;

    /**
     * 受限1000条的容量，分页处理
     * access 数据源处理
     * String sql = "select * from Data where DataTime > '"+time+"' order by DID desc";
     * @return
     */
    /**
     * 受限1000条的容量，分页处理
     * access 数据源处理
     * @param time 时间
     * @param page 页码
     * @param lastPageSize 最后一页的大小
     * @return
     */
    public List<Map<String, String>> dataAccess(String time, int page, int lastPageSize){
        String sql = "SELECT * FROM (SELECT TOP "+lastPageSize+" * FROM (SELECT TOP "+(page+1)*1000+lastPageSize+" * FROM Data ORDER BY DID DESC) ORDER BY DID) where DataTime > '"+time+"'  ORDER BY DID DESC";
        List<Map<String, String>> list = null;
        try {
            list = resolver(url, sql, Access.column);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * access 数据源总条数
     * @return
     */
    public List<Map<String, String>> dataAccessCount(String time){
        String sql = "select count(DID) as 'sum' from Data where DataTime > '"+time+"' order by DID desc";
        List<Map<String, String>> list = null;
        try {
            list = resolver(url, sql, Access.sum);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     *
     * @param mdbPath 数据库位置
     * @param sql 查询语句
     * @param column 字段数组
     * @return
     * @throws Exception
     */
    public List<Map<String, String>> resolver(String mdbPath, String sql, Object... column) throws Exception {
        List<Map<String, String>> entityList = new ArrayList<>();
        if (mdbPath.isEmpty() || sql.isEmpty() || column.length < 1) {
            throw new Exception("解析参数错误！");
        }
        Properties prop = new Properties();
        prop.put("charSet", "gb2312");                //解决中文乱码
        String dbUr1 = "jdbc:Access:///" + mdbPath;
        Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
        try {
            Connection conn = DriverManager.getConnection(dbUr1, prop);
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(sql);
            Map<String, String> mapList = null;
            while (result.next()) {
                mapList = new HashMap();
                for (Object col : column) {
                    mapList.put((String) col, result.getString((String) col));
                }
                entityList.add(mapList);
            }
        } catch (Exception e) {

        }
        return entityList;
    }

4. 调用逻辑代码

list = accessService.dataAccess(time, tempPage, lastPageSize);
      for(int i=0; i<list.size(); i++){
          Map<String, String> map = list.get(i);
          try {
              cm = new Corrosionmeter(
                      Integer.valueOf(map.get("SID")),format1.parse(map.get("DataTime")),map.get("S1"),
                      map.get("S2"),map.get("R1"),map.get("R2"),Integer.valueOf(map.get("IsWarning")));
          } catch (ParseException e) {
              e.printStackTrace();
          }
          corrosionmeterService.insert(cm);
      }

以上基本就能使用了，具体开发中根据需要，稍作调整就好了！