如何使用JDBC实现DataTable的服务器端处理，使其分页？-解网

问：

我有一个带有 DataTables 服务器端处理和 Oracle 数据库的 Spring Boot 应用程序。实际上，我从实现其中一个教程开始。成功了。本教程使用 JPA。我想使用JDBC实现相同的功能。我制作了所有相应的类、存储库、具有相同文件但没有 jpa 的新模型。但是当我尝试获取数据时，它允许我只获得第一页，而没有机会进入第二页。下面我将发布原始代码和添加代码的摘录。因此，原始教程使用了以下类：

@Entity
@Table(name = "MYUSERS")
public class User {

    @Id
    @Column(name = "USER_ID")
    private Long id;

    @Column(name = "USER_NAME")
    private String name;

    @Column(name = "SALARY")
    private String salary;

...getters and setters
}

和

@Entity
public class UserModel {

    @Id
    private Long id;
    private String name;
    private String salary;

    private Integer totalRecords;

    @Transient
    private Integer rn;

...getters and setters
}

我把这两个类替换成这样的类：

public class NewUser {

    private Long id;
    private String name;
    private String salary;
    private Integer totalRecords;
    private Integer rn;

...getters and setters
}

表格本身只有 3 个字段：id、name 和 salary，其他 2 个字段稍后创建并填写。原始作者为用户提供的存储库如下所示：

public interface UserRepository extends JpaRepository<User, Long> {

    @Query(value = "SELECT * FROM MYUSERS", nativeQuery = true)
    List<User> findAllByUsernames(List<String> listOfUsernames);
}

我自己的存储库如下所示：

@Repository
public class NewUserRepoImpl extends JdbcDaoSupport implements NewUserRepo {

    private static final String SELECT_ALL_SQL = "SELECT USER_ID as id, USER_NAME as name, SALARY as salary FROM MYUSERS";

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private final JdbcTemplate jdbctemplate;

    public NewUserRepoImpl(NamedParameterJdbcTemplate namedParameterJdbcTemplate, JdbcTemplate jdbctemplate, DataSource dataSource) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
        this.jdbctemplate = jdbctemplate;
        setDataSource(dataSource);
    }

    @Override
    public List<NewUser> findAll(PaginationCriteria pagination) {
        try {
            String paginatedQuery = AppUtil.buildPaginatedQueryForOracle(SELECT_ALL_SQL, pagination);
            return jdbctemplate.query(paginatedQuery, newUserRowMapper());
        } catch (DataAccessException e) {
            throw new EntityNotFoundException("No Entities Found");
        }
    }

    @Bean
    public RowMapper<NewUser> newUserRowMapper() {
        return (rs, i) -> {
            final NewUser newUser = new NewUser();
            newUser.setId(rs.getLong("ID"));
            newUser.setName(rs.getString("NAME"));
            newUser.setSalary(rs.getString("SALARY"));
            newUser.setTotalRecords(rs.getInt("TOTAL_RECORDS"));
            newUser.setTotalRecords(rs.getInt("RN"));
            return newUser;
        };
    }
}

buildPaginatedQueryForOracle 事物转换了我的查询并允许它获取 totalRecords 和 rn。下面我将发布它的输出，用于 orifinal 和我的查询（它们是相同的，我检查过）。

所以，主要部分，控制器。出于调试目的，我现在将新旧部分留在其中，只返回其中一个结果：

@RequestMapping(value="/users/paginated/orcl", method=RequestMethod.GET)
    @ResponseBody
    public String listUsersPaginatedForOracle(HttpServletRequest request, HttpServletResponse response, Model model) {

        DataTableRequest<User> dataTableInRQ = new DataTableRequest<User>(request);
        System.out.println(new Gson().toJson(dataTableInRQ));
        DataTableRequest<NewUser> dataTableInRQNew = new DataTableRequest<NewUser>(request);
        System.out.println(new Gson().toJson(dataTableInRQNew));
        PaginationCriteria pagination = dataTableInRQ.getPaginationRequest();
        System.out.println(new Gson().toJson(pagination));
        PaginationCriteria paginationNew = dataTableInRQNew.getPaginationRequest();
        System.out.println(new Gson().toJson(paginationNew));
        String baseQuery = "SELECT USER_ID as id, USER_NAME as name, SALARY as salary FROM MYUSERS";
        String paginatedQuery = AppUtil.buildPaginatedQueryForOracle(baseQuery, pagination);
        String paginatedQueryNew = AppUtil.buildPaginatedQueryForOracle(baseQuery, paginationNew);

        System.out.println(paginatedQuery);
        System.out.println(paginatedQueryNew);

        Query query = entityManager.createNativeQuery(paginatedQuery, UserModel.class);
        System.out.println("Query:");
        System.out.println(query);

        @SuppressWarnings("unchecked")
        List<UserModel> userList = query.getResultList();
        System.out.println(new Gson().toJson(userList));

        @SuppressWarnings("unchecked")
        List<NewUser> userListNew = newUserRepo.findAll(paginationNew);     
        System.out.println(new Gson().toJson(userListNew));

        DataTableResults<UserModel> dataTableResult = new DataTableResults<UserModel>();
        DataTableResults<NewUser> dataTableResultNew = new DataTableResults<NewUser>();
        dataTableResult.setDraw(dataTableInRQ.getDraw());
        dataTableResultNew.setDraw(dataTableInRQNew.getDraw());
        dataTableResult.setListOfDataObjects(userList);
        dataTableResultNew.setListOfDataObjects(userListNew);
        if (!AppUtil.isObjectEmpty(userList)) {
            dataTableResult.setRecordsTotal(userList.get(0).getTotalRecords()
                    .toString());
            if (dataTableInRQ.getPaginationRequest().isFilterByEmpty()) {
                dataTableResult.setRecordsFiltered(userList.get(0).getTotalRecords()
                        .toString());
            } else {
                dataTableResult.setRecordsFiltered(Integer.toString(userList.size()));
            }
        }
        if (!AppUtil.isObjectEmpty(userListNew)) {
            dataTableResultNew.setRecordsTotal(userListNew.get(0).getTotalRecords()
                    .toString());
            if (dataTableInRQ.getPaginationRequest().isFilterByEmpty()) {
                dataTableResultNew.setRecordsFiltered(userListNew.get(0).getTotalRecords()
                        .toString());
            } else {
                dataTableResultNew.setRecordsFiltered(Integer.toString(userListNew.size()));
            }
        }
        System.out.println(new Gson().toJson(dataTableResult));
        System.out.println(new Gson().toJson(dataTableResultNew));
        return new Gson().toJson(dataTableResult);
    }

因此，我在控制台中注销了所有可能的内容。输出如下：

{"uniqueId":"1579786571491","draw":"1","start":0,"length":5,"search":"","regex":false,"columns":[{"index":0,"data":"id","name":"ID","searchable":true,"orderable":true,"search":"","regex":false,"sortDir":"ASC"},{"index":1,"data":"name","name":"Name","searchable":true,"orderable":true,"search":"","regex":false},{"index":2,"data":"salary","name":"Salary","searchable":true,"orderable":true,"search":"","regex":false}],"order":{"index":0,"data":"id","name":"ID","searchable":true,"orderable":true,"search":"","regex":false,"sortDir":"ASC"},"isGlobalSearch":false,"maxParamsToCheck":3}
{"uniqueId":"1579786571491","draw":"1","start":0,"length":5,"search":"","regex":false,"columns":[{"index":0,"data":"id","name":"ID","searchable":true,"orderable":true,"search":"","regex":false,"sortDir":"ASC"},{"index":1,"data":"name","name":"Name","searchable":true,"orderable":true,"search":"","regex":false},{"index":2,"data":"salary","name":"Salary","searchable":true,"orderable":true,"search":"","regex":false}],"order":{"index":0,"data":"id","name":"ID","searchable":true,"orderable":true,"search":"","regex":false,"sortDir":"ASC"},"isGlobalSearch":false,"maxParamsToCheck":3}
{"pageNumber":0,"pageSize":5,"sortBy":{"mapOfSorts":{"id":"ASC"}},"filterBy":{"mapOfFilters":{},"globalSearch":false}}
{"pageNumber":0,"pageSize":5,"sortBy":{"mapOfSorts":{"id":"ASC"}},"filterBy":{"mapOfFilters":{},"globalSearch":false}}
SELECT * FROM (SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() total_records, ROWNUM AS RN FROM (SELECT BASEINFO.* FROM ( SELECT USER_ID as id, USER_NAME as name, SALARY as salary FROM MYUSERS ) BASEINFO ) FILTERED_ORDERED_RESULTS   ORDER BY id ASC ) WHERE RN > (0 * 5) AND RN <= (0 + 1) * 5 
SELECT * FROM (SELECT FILTERED_ORDERED_RESULTS.*, COUNT(1) OVER() total_records, ROWNUM AS RN FROM (SELECT BASEINFO.* FROM ( SELECT USER_ID as id, USER_NAME as name, SALARY as salary FROM MYUSERS ) BASEINFO ) FILTERED_ORDERED_RESULTS   ORDER BY id ASC ) WHERE RN > (0 * 5) AND RN <= (0 + 1) * 5 
Query:
org.hibernate.query.internal.NativeQueryImpl@3ea49a4
[{"id":3,"name":"user3","salary":"300","totalRecords":18},{"id":4,"name":"user4","salary":"400","totalRecords":18},{"id":5,"name":"user5","salary":"500","totalRecords":18},{"id":6,"name":"user6","salary":"600","totalRecords":18},{"id":7,"name":"user7","salary":"700","totalRecords":18}]
[{"id":3,"name":"user3","salary":"300","totalRecords":1},{"id":4,"name":"user4","salary":"400","totalRecords":2},{"id":5,"name":"user5","salary":"500","totalRecords":3},{"id":6,"name":"user6","salary":"600","totalRecords":4},{"id":7,"name":"user7","salary":"700","totalRecords":5}]
{"draw":"1","recordsFiltered":"18","recordsTotal":"18","data":[{"id":3,"name":"user3","salary":"300","totalRecords":18},{"id":4,"name":"user4","salary":"400","totalRecords":18},{"id":5,"name":"user5","salary":"500","totalRecords":18},{"id":6,"name":"user6","salary":"600","totalRecords":18},{"id":7,"name":"user7","salary":"700","totalRecords":18}]}
{"draw":"1","recordsFiltered":"1","recordsTotal":"1","data":[{"id":3,"name":"user3","salary":"300","totalRecords":1},{"id":4,"name":"user4","salary":"400","totalRecords":2},{"id":5,"name":"user5","salary":"500","totalRecords":3},{"id":6,"name":"user6","salary":"600","totalRecords":4},{"id":7,"name":"user7","salary":"700","totalRecords":5}]}

它帮助我意识到：

DataTableRequest从后面传入对于两个 JPA 来说都是相同的和 JDBC
PaginationCriteria也是一样的
paginatedQuery使用上述方法制作的方法是相同的。
在列表中已经看到了差异：其中 Jpa 列表使用本机查询检索的每行的 totalRecords 为 18，具有相同查询的 JDBC 存储库返回 1,2,3...对于每一个后续行。

这让我觉得我应该看看为 JPA 制作的查询。但是，正如您在日志中看到的那样，System.out.println 由于某种原因无法破译它。关于如何破译它的任何建议，更重要的是如何为每行获得正确的总结果，将不胜感激!!

Java Spring JDBC 数据表服务器端

0赞 guido 1/23/2020

你能提出一些建议吗？

0赞 guido 1/24/2020

对于任何感兴趣的人，我有一些进展，这里描述了新问题：stackoverflow.com/questions/59883559/......

答： 暂无答案

上一个：如何从 Java 程序运行 Linux 命令“netstat”？

下一个：Java 中 DataTable 的服务器端处理

如何使用JDBC实现DataTable的服务器端处理，使其分页？

How to implement Server-side processing of DataTables with JDBC so that it paginates?

评论