Java 如何创建与 JDBI SQL 对象 API 的一对多关系？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24337100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a one-to-many relationship with JDBI SQL object API?
提问by Tilman
I'm creating a simple REST application with dropwizard using JDBI. The next step is to integrate a new resource that has a one-to-many relationship with another one. Until now I couldn't figure out how to create a method in my DAO that retrieves a single object that holds a list of objects from another table.
我正在使用 JDBI 使用 dropwizard 创建一个简单的 REST 应用程序。下一步是集成与另一个具有一对多关系的新资源。到目前为止，我无法弄清楚如何在我的 DAO 中创建一个方法来检索一个对象，该对象包含另一个表中的对象列表。
The POJO representations would be something like this:
POJO 表示将是这样的：
User POJO:
用户 POJO：
public class User {
private int id;
private String name;
public User(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Account POJO:
帐户POJO：
public class Account {
private int id;
private String name;
private List<User> users;
public Account(int id, String name, List<User> users) {
this.id = id;
this.name = name;
this.users = users;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
}
The DAO should look something like this
DAO 应该看起来像这样
public interface AccountDAO {
@Mapper(AccountMapper.class)
@SqlQuery("SELECT Account.id, Account.name, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")
public Account getAccountById(@Bind("id") int id);
}
But when the method has a single object as return value (Accountinstead of List<Account>) there seems to be no way to access more than one line of the resultSet in the Mapper class. The only solution that comes close I could find is described at https://groups.google.com/d/msg/jdbi/4e4EP-gVwEQ/02CRStgYGtgJbut that one also only returns a Set with a single object which does not seem very elegant. (And can't be properly used by the resouce classes.)
但是，当该方法将单个对象作为返回值（Account而不是List<Account>）时，似乎无法访问 Mapper 类中超过一行的 resultSet。我能找到的唯一接近的解决方案在https://groups.google.com/d/msg/jdbi/4e4EP-gVwEQ/02CRStgYGtgJ 中进行了描述，但该解决方案也只返回一个带有单个对象的 Set 看起来不太像优雅的。（并且不能被资源类正确使用。）
There seems to be a way using a Folder2in the fluent API. But I don't know how to integrate that properly with dropwizard and I'd rather stick to JDBI's SQL object API as recommended in the dropwizard documentation.
似乎有一种方法可以在流畅的 API 中使用Folder2。但我不知道如何将它与 dropwizard 正确集成，我宁愿坚持使用 dropwizard 文档中推荐的 JDBI 的 SQL 对象 API。
Is there really no way to get a one-to-many mapping using the SQL object API in JDBI? That is such a basic use case for a database that I think I must be missing something.
真的没有办法在 JDBI 中使用 SQL 对象 API 来获得一对多的映射吗？这是一个数据库的基本用例，我想我一定遗漏了一些东西。
All help is greatly appreciated,
Tilman
非常感谢所有帮助，
蒂尔曼
采纳答案by Tilman
OK, after a lot of searching, I see two ways dealing with this:
好的，经过大量搜索，我看到了两种处理方法：
The first optionis to retrieve an object for each column and merge it in the Java code at the resource (i.e. do the join in the code instead of having it done by the database). This would result in something like
第一个选项是为每列检索一个对象并将其合并到资源的 Java 代码中（即在代码中进行连接，而不是由数据库完成）。这将导致类似
@GET
@Path("/{accountId}")
public Response getAccount(@PathParam("accountId") Integer accountId) {
Account account = accountDao.getAccount(accountId);
account.setUsers(userDao.getUsersForAccount(accountId));
return Response.ok(account).build();
}
This is feasible for smaller join operations but seems not very elegant to me, as this is something the database is supposed to do. However, I decided to take this path as my application is rather small and I did not want to write a lot of mapper code.
这对于较小的连接操作是可行的，但对我来说似乎不是很优雅，因为这是数据库应该做的事情。然而，我决定走这条路，因为我的应用程序很小，我不想写很多映射器代码。
The second optionis to write a mapper, that retrieves the result of the join query and maps it to the object like this:
在第二个选项是写一个映射，检索联接查询的结果，并将其映射到这样的对象：
public class AccountMapper implements ResultSetMapper<Account> {
private Account account;
// this mapping method will get called for every row in the result set
public Account map(int index, ResultSet rs, StatementContext ctx) throws SQLException {
// for the first row of the result set, we create the wrapper object
if (index == 0) {
account = new Account(rs.getInt("id"), rs.getString("name"), new LinkedList<User>());
}
// ...and with every line we add one of the joined users
User user = new User(rs.getInt("u_id"), rs.getString("u_name"));
if (user.getId() > 0) {
account.getUsers().add(user);
}
return account;
}
}
The DAO interface will then have a method like this:
DAO 接口将有一个这样的方法：
public interface AccountDAO {
@Mapper(AccountMapper.class)
@SqlQuery("SELECT Account.id, Account.name, User.id as u_id, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")
public List<Account> getAccountById(@Bind("id") int id);
}
Note:Your abstract DAO class will quietly compile if you use a non-collection return type, e.g. public Account getAccountById(...);. However, your mapper will only receive a result set with a single row even if the SQL query would have found multiple rows, which your mapper will happily turn into a single account with a single user. JDBI seems to impose a LIMIT 1for SELECTqueries that have a non-collection return type. It is possible to put concrete methods in your DAO if you declare it as an abstract class, so one option is to wrap up the logic with a public/protected method pair, like so:
注意：如果您使用非集合返回类型，例如public Account getAccountById(...);. 但是，即使 SQL 查询会找到多行，您的映射器也只会收到包含单行的结果集，您的映射器会很乐意将其变成具有单个用户的单个帐户。JDBI似乎强加LIMIT 1了SELECT具有非收集返回类型的查询。如果将 DAO 声明为抽象类，则可以在 DAO 中放置具体方法，因此一种选择是使用公共/受保护方法对包装逻辑，如下所示：
public abstract class AccountDAO {
@Mapper(AccountMapper.class)
@SqlQuery("SELECT Account.id, Account.name, User.id as u_id, User.name as u_name FROM Account LEFT JOIN User ON User.accountId = Account.id WHERE Account.id = :id")
protected abstract List<Account> _getAccountById(@Bind("id") int id);
public Account getAccountById(int id) {
List<Account> accountList = _getAccountById(id);
if (accountList == null || accountList.size() < 1) {
// Log it or report error if needed
return null;
}
// The mapper will have given a reference to the same value for every entry in the list
return accountList.get(accountList.size() - 1);
}
}
This still seems a little cumbersome and low-level to me, as there are usually a lot of joins in working with relational data. I would love to see a better way or having JDBI supporting an abstract operation for this with the SQL object API.
这对我来说仍然有点麻烦和低级，因为在处理关系数据时通常有很多连接。我很想看到更好的方法，或者让 JDBI 使用 SQL 对象 API 支持抽象操作。
回答by Manikandan
I have a small library which will be very useful to maintain one to many & one to one relationship. It also provide more feature for default mappers.
我有一个小型图书馆，这对于维持一对多和一对一的关系非常有用。它还为默认映射器提供了更多功能。
回答by Matthew
There's an old google groups post where Brian McAllistair (One of the JDBI authors) does this by mapping each joined row to an interim object, then folding the rows into the target object.
有一个旧的 google 组帖子，其中 Brian McAllistair（JDBI 的作者之一）通过将每个连接的行映射到一个临时对象，然后将行折叠到目标对象中来做到这一点。
See the discussion here. There's test code here.
Personally this seems a little unsatisfying since it means writing an extra DBO object and mapper for the interim structure. Still I think this answer should be included for completeness!
就个人而言，这似乎有点令人不满意，因为这意味着要为临时结构编写额外的 DBO 对象和映射器。我仍然认为为了完整性应该包括这个答案！
回答by spinlok
In JDBI v3, you can use @UseRowReducerto achieve this. The row reducer is called on every row of the joined result which you can "accumulate" into a single object. A simple implementation in your case would look like:
在 JDBI v3 中，您可以使用@UseRowReducer来实现这一点。对连接结果的每一行调用行缩减器，您可以“累积”到单个对象中。在您的情况下，一个简单的实现如下所示：
public class AccountUserReducer implements LinkedHashMapRowReducer<Integer, Account> {
@Override
public void accumulate(final Map<Integer, Account> map, final RowView rowView) {
final Account account = map.computeIfAbsent(rowView.getColumn("a_id", Integer.class),
id -> rowView.getRow(Account.class));
if (rowView.getColumn("u_id", Integer.class) != null) {
account.addUser(rowView.getRow(User.class));
}
}
}
You can now apply this reducer on a query that returns the join:
您现在可以在返回连接的查询上应用这个 reducer：
@RegisterBeanMapper(value = Account.class, prefix = "a")
@RegisterBeanMapper(value = User.class, prefix = "u")
@SqlQuery("SELECT a.id a_id, a.name a_name, u.id u_id, u.name u_name FROM " +
"Account a LEFT JOIN User u ON u.accountId = a.id WHERE " +
"a.id = :id")
@UseRowReducer(AccountUserReducer.class)
Account getAccount(@Bind("id") int id);
Note that your Userand Accountrow/bean mappers can remain unchanged; they simply know how to map an individual row of the user and account tables respectively. Your Accountclass will need a method addUser()that is called each time the row reducer is called.
请注意，您的User和Account行/bean 映射器可以保持不变；他们只知道如何分别映射用户和帐户表的单个行。您的Account类将需要一个方法addUser()，每次调用行缩减器时都会调用该方法。
