使用JDBi3将问题映射到具有多到多关系的DTO类

Question

在DAO接口中使用JDBi3查询映射检索到的数据时，我遇到了问题。在我的Dropwizard应用程序中，我有BookDTO类，它与Author类和分类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
}

...and，因为我使用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();

...and这是BookDTOACMapper类的map方法，如下所示：

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类调用BookDAO方法)时，显示不一致的结果，而查询本身是返回适当的结果。

我在Stackoverflow、官方的JDBi3文档API和Google组上发现了许多问题，它们正在考虑一对多的关系，并使用包含类的@UseRowReducer注释来影响LinkedHashMapRowReducer<TypeOfEntityIdentifier, EntityName>，但在这种情况下，我无法找到实现它的方法。欢迎任何例子/建议。)事先谢谢你。

已使用工具的版本

Dropwizard框架1.3.8

PostgreSQL 11.7

Java8

Answer 1

这段时间太长了，不能发表评论：

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

while (rs.next()) { if (rs.getLong("b_id") != bookId) { break；}break{

在if之后的第一个while正在吃当前之后的行(在调用行映射器时wass当前的行)。您正在跳过bookId、authorId等的处理(将数据放在Java对象中)。

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

1. 因此，您需要重新检查处理数据的方式。我看到两条路：

- Revisit the logic of the processing loop to store the data when stopping the processing for given `bookId`. It is possible to achieve this with scrollable `ResultSet`s - i.e. request a scrollable `ResultSet` and before the `brake;` call `rs.previous()`. On the next call to the row mapper the processing will start from the correct line in the result set.
- Use the power of the SQL/PostgreSQL and do it properly: [https://dba.stackexchange.com/questions/173831/convert-right-side-of-join-of-many-to-many-into-array](https://dba.stackexchange.com/questions/173831/convert-right-side-of-join-of-many-to-many-into-array) Aggregate and shape the data in the database. The database is the best tool for this job.

​

还有，花点时间检查一下https://dba.stackexchange.com/users/3684/erwin-brandstetter的其他答案。它们在SQL和PostgreSQL中提供了宝贵的见解。

Answer 2

正如罗布斯特在他的回答中提到的，我选择了第二个选项(由这个答案选择多到多关系)，即使用编辑我的PostgreSQL查询@SqlQuery注释( List<BookDTO> getAllBooks();方法)。Query在SELECT语句中使用array_agg聚合函数将我的结果分组到数组中，现在如下所示：

    @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);
  }

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