在Dropwizard中使用JDBi3映射到具有多对多关系的DTO类的问题

Question

我在DAO界面中使用PostgreSQL查询映射通过JDBi3检索的数据时遇到了问题。在我的Dropwzo应用程序中，我有Book DTO类，它与作者和类别DTO类有多对多关系，并且在将查询的行映射到BookDTO类时遇到了问题。以下是DTO类的代码片段：

class BookDTO {
  private Long bookId;
  // other fields are left for code brevity 
  private List<Long> authors; 
  private List<Long> categories;

  // empty constructor + constructor with all fields excluding Lists + getters + setters
}

class AuthorDTO {
  private Long authorId;
  // other fields are left for code brevity 
  private List<Long> books; 

  // empty constructor + constructor with all fields excluding List + getters + setters
}

class CategoryDTO {
   private Long categoryId;
   // other fields are left for code brevity 
   private List<Long> books;

   // empty constructor + constructor with all fields excluding List + getters + setters
}

…由于我使用JDBi3 DAO接口来执行CRUD操作，这就是我查询数据库中所有书籍的方法的样子：

    @Transaction
    @UseRowMapper(BookDTOACMapper.class)
    @SqlQuery("SELECT book.book_id AS b_id, book.title, book.price, book.amount, book.is_deleted, author.author_id AS aut_id, category.category_id AS cat_id FROM book " +
            "LEFT JOIN author_book ON book.book_id = author_book.book_id " +
            "LEFT JOIN author ON author_book.author_id = author.author_id " +
            "LEFT JOIN category_book ON book.book_id = category_book.book_id " +
            "LEFT JOIN category ON category_book.category_id = category.category_id ORDER BY b_id ASC, aut_id ASC, cat_id ASC")
    List<BookDTO> getAllBooks();

。这是BookDTOACMapper类的映射方法，如下所示：

public class BookDTOACMapper implements RowMapper<BookDTO> {

        @Override
        public BookDTO map(ResultSet rs, StatementContext ctx) throws SQLException {      
            final long bookId = rs.getLong("b_id");
            // normally retrieving values by using appropriate rs.getXXX() methods

            Set<Long> authorIds = new HashSet<>();
            Set<Long> categoryIds = new HashSet<>();
            long authorId = rs.getLong("aut_id");
            if (authorId > 0) {
                authorIds.add(authorId);
            }
            long categoryId = rs.getLong("cat_id");
            if (categoryId > 0) {
                categoryIds.add(categoryId);
            }    

            while (rs.next()) {
                if (rs.getLong("b_id") != bookId) {
                    break;
                } else {
                    authorId = rs.getLong("aut_id");
                    if (authorId > 0) { authorIds.add(authorId); }

                    categoryId = rs.getLong("cat_id");
                    if (categoryId > 0) { categoryIds.add(categoryId); }
                }
            }
            final List<Long> authorIdsList = new ArrayList<>(authorIds);
            final List<Long> categoryIdsList = new ArrayList<>(categoryIds);

            return new BookDTO(bookId, title, price, amount, is_deleted, authorIdsList, categoryIdsList);
        }
}

我遇到的问题是，当调用我的GET方法(在从BookDAO类调用< code>getAllBooks()方法的资源类中定义)时，显示不一致的结果，而查询本身返回正确的结果。

我设法在Stackoverflow，官方JDBi3 Docs API和Google群组上找到的许多问题都在考虑一对多关系，并使用@UseRowReducer注释，其中包含包含LinkedHashMapRowReducer元素的类。

使用的工具版本：
Dropwizard framework 1.3.8
PostgreSQL 11.7
Java8




Answer 1

正如zloster在他的回答中提到的，我选择了第二个选项(对于多对多的关系，我选择了第二个选项),即在< code >列表上方使用edit my PostgreSQL query < code > @ SQL query 注释

    @UseRowMapper(BookDTOACMapper.class)
    @SqlQuery("SELECT b.book_id AS b_id, b.title, b.price, b.amount, b.is_deleted, ARRAY_AGG(aut.author_id) as aut_ids, ARRAY_AGG(cat.category_id) as cat_ids " +
            "FROM book b " +
            "LEFT JOIN author_book ON author_book.book_id = b.book_id " +
            "LEFT JOIN author aut ON aut.author_id = author_book.author_id " +
            "LEFT JOIN category_book ON category_book.book_id = b.book_id " +
            "LEFT JOIN category cat ON cat.category_id = category_book.category_id " +
            "GROUP BY b_id " +
            "ORDER BY b_id ASC")
    List<BookDTO> getAllBooks();

因此map（…）BookDTOACMapper类的方法必须被编辑，现在看起来像这样：

  @Override
  public BookDTO map(ResultSet rs, StatementContext ctx) throws SQLException {
    final long bookId = rs.getLong("b_id");
    String title = rs.getString("title");
    double price = rs.getDouble("price");
    int amount = rs.getInt("amount");
    boolean is_deleted = rs.getBoolean("is_deleted");

    Set<Long> authorIds = new HashSet<>();
    Set<Long> categoryIds = new HashSet<>();

    /* rs.getArray() retrives java.sql.Array and after it getArray gets
       invoked which returns array of Object(s) which are being casted 
       into array of Long elements */
    Long[] autIds = (Long[]) (rs.getArray("aut_ids").getArray());
    Long[] catIds = (Long[]) (rs.getArray("cat_ids").getArray());

    Collections.addAll(authorIds, autIds);
    Collections.addAll(categoryIds, catIds);

    final List<Long> authorIdsList = new ArrayList<>(authorIds);
    final List<Long> categoryIdsList = new ArrayList<>(categoryIds);

    return new BookDTO(bookId, title, price, amount, is_deleted, authorIdsList, categoryIdsList);
  }

现在所有的结果都是一致的，下面是pgAdmin4中的查询截图。

Answer 2

匿名用户

这将太长而无法发表评论：

1. 这基本上是一个调试问题。为什么

while （rs.next（）） { if （rs.getLong（“b_id”） ！= bookId） { break; } else {

while 之后的第一个if 正在吃掉当前之后的行（调用行映射器时是当前行的行）。您跳过了对bookId，authorId等的处理（将数据放入Java对象中）。这就是为什么你会得到

不一致的结果，而查询本身返回正确的结果。

因此，您需要重新考虑如何处理数据。我看到两条路径：

< li >当停止给定< code>bookId的处理时，重新访问处理循环的逻辑以存储数据。这可以通过可滚动的< code >结果集来实现，即在< code >制动之前请求一个可滚动的< code >结果集;调用< code>rs.previous()。下次调用行映射器时，处理将从结果集中正确的行开始。 < li >使用SQL/PostgreSQL的强大功能并正确执行:https://DBA . stack exchange . com/questions/173831/convert-right-side-of-join-of-many to-many into-array聚合并调整数据库中的数据。数据库是这项工作的最佳工具。

也慢慢来，查查https://DBA . stack exchange . com/users/3684/Erwin-brands tetter的其他回答。他们对SQL和PostgreSQL给出了宝贵的见解。