Java JPA-连接非实体类中的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25179180/
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
JPA- Joining two tables in non-entity class
提问by MaNn
I am a newbie ,tried to google but I am unable to solve my query. Please help.
我是一个新手，试图用谷歌搜索，但我无法解决我的问题。请帮忙。
I am trying to map two entities : PersonA and Person in my POJO class PersonC
我试图在我的 POJO 类 PersonC 中映射两个实体：PersonA 和 Person
@Entity
class PersonA{
String sample_field;
}
@Entity
class Person{
String id;
String name;
}
Above two are entities of jpa.
以上两个是jpa的实体。
Now I want to merge them into one pojo class.
现在我想将它们合并为一个 pojo 类。
class PersonC
{
Strind id;
String address;
}
Tried below code but when I try to fetch Address/Foreign key field it does not work.
尝试了下面的代码，但是当我尝试获取地址/外键字段时，它不起作用。
@SqlResultSetMapping(name="PersonC",
classes = {
@ConstructorResult(targetClass = PersonC.class,
columns = {@ColumnResult(name="name")
, @ColumnResult(name="address")
)}
where should I define @SqlResultSetMapping ,for which class from the above? ) })
我应该在哪里定义 @SqlResultSetMapping ，上面的哪个类？) })
采纳答案by zbig
@SqlResultSetMappingcan be placed at anyentity class (don't annotate POJOs - it won't work). Mapping to POJO class with @ConstructorResultwas added in version 2.1 of JPA. POJO used with the mapping has to have correct constructor.
@SqlResultSetMapping可以放置在任何实体类中（不要注释 POJO - 它不起作用）。@ConstructorResult在 JPA 2.1 版中添加了到 POJO 类的映射。与映射一起使用的 POJO 必须具有正确的构造函数。
All columns corresponding to arguments of the intended constructor must be specified using the columns element of the ConstructorResult annotation in the same order as that of the argument list of the constructor.
必须使用 ConstructorResult 注释的列元素以与构造函数的参数列表相同的顺序指定与预期构造函数的参数相对应的所有列。
Please consult following example with query usage and work out your case accordingly.
请参考以下带有查询用法的示例，并相应地计算出您的情况。
@Entity
public class Address {
@Id int id;
String street;
}
@SqlResultSetMapping(name="PersonDTOMapping",
classes = {
@ConstructorResult(targetClass = PersonDTO.class,
columns = {@ColumnResult(name="name"), @ColumnResult(name="street")}
)}
)
@Entity
public class Person {
@Id int id;
String name;
Address address;
}
public class PersonDTO {
String name;
String street;
public PersonDTO(String name, String street) {
this.name = name;
this.street = street;
}
}
// usage
Query query = em.createNativeQuery(
"SELECT p.name AS name, a.street AS street FROM Person p, Address a WHERE p.address_id=a.id",
"PersonDTOMapping");
List<PersonDTO> result = query.getResultList();
Please note that aliases (AS nameand AS street) has to match the names in @ColumnResults.
The example was tested against Ecliselink 2.5.1.
请注意别名（AS name和AS street）必须与@ColumnResults中的名称匹配。该示例针对 Ecliselink 2.5.1 进行了测试。
回答by xagaffar
Just found a bit simpler solution using JPQL. I stole part of the example from @zbig's answer:
刚刚使用 JPQL 找到了一个更简单的解决方案。我从@zbig 的回答中窃取了部分示例：
@Entity
public class Address {
@Id int id;
String street;
}
@Entity
public class Person {
@Id int id;
String name;
Address address;
}
public class PersonDTO {
String name;
String street;
public PersonDTO(String name, String street) {
this.name = name;
this.street = street;
}
}
List<PersonDTO> listOfPersons = em.createQuery("select new com.example.PersonDTO(p.name, a.street) " +
"from Person p, Address a " +
"WHERE p.address.id=a.id", PersonDTO.class).getResultList();
The benefit of this solution is that you don't need to use the @SqlResultSetMapping annotation, which mustbe placed on anyentity class, not the DTO class! And that's sometimes confusing because the entity class could only be partially related (when joining multiple tables for example).
这个方案的好处是不需要使用@SqlResultSetMapping注解，它必须放在任何实体类上，而不是DTO类！这有时会令人困惑，因为实体类只能部分相关（例如，在连接多个表时）。
More info here
更多信息在这里
回答by pchemeque
This post deals with the Hibernate.
这篇文章涉及 Hibernate。
The suggestion of putting the @SqlResultSetMappingand @NamedNativeQuery(or @NamedQuery) inside the @Entityclass definition is not elegant and evidently does not follow the separation of concerns principle.
把的建议@SqlResultSetMapping和@NamedNativeQuery（或@NamedQuery）内部@Entity类定义是不优雅，看样子不遵循的原则，关注分离。
The more proper solution is the usage of the @MappedSuperclassannotation as the following:
更合适的解决方案是使用@MappedSuperclass注释如下：
SingerExtended.java(the class must be abstract):
SingerExtended.java（类必须是抽象类）：
package pl.music.model.singer.extended;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.MappedSuperclass;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
@MappedSuperclass
@SqlResultSetMapping( // @formatter:off
name = "SingerExtendedMapping",
classes = @ConstructorResult(
targetClass = SingerExtendedDTO.class,
columns = {
@ColumnResult(name = "singer_id", type = Long.class),
@ColumnResult(name = "first_name"),
@ColumnResult(name = "last_name"),
@ColumnResult(name = "count_albums", type = Long.class)
}
)
)
@NamedNativeQueries({
@NamedNativeQuery(
name = "SingerExtendedAsc",
query = "select"
+ " singer.singer_id,"
+ " singer.first_name,"
+ " singer.last_name,"
+ " (select count(*) from album where album.singer_id = singer.singer_id) as count_albums"
+ " from singer"
+ " group by singer.singer_id"
+ " order by last_name collate :collation asc, first_name collate :collation asc",
resultSetMapping = "SingerExtendedMapping"
)
}) // @formatter:on
public abstract class SingerExtended {
}
then DAO class SingerExtendedDAO.java:
然后 DAO 类SingerExtendedDAO.java：
package pl.music.model.singer.extended;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@Component
public class SingerExtendedDAO {
@PersistenceContext
EntityManager entityManager;
@Autowired
private String collation;
public List<SingerExtendedDTO> getAll(Integer page, Integer count) {
TypedQuery<SingerExtendedDTO> query = entityManager.createNamedQuery("SingerExtendedAsc", SingerExtendedDTO.class);
query.setParameter("collation", collation);
if ((count != null) && (count.intValue() > 0)) {
query.setMaxResults(count.intValue());
if ((page != null) && (page.intValue() >= 0)) {
query.setFirstResult(count.intValue() * page.intValue());
}
}
List<SingerExtendedDTO> singerExtendedDTOs = query.getResultList();
return singerExtendedDTOs;
}
}
and finally the DTO class SingerExtendedDTO.java(you must provide "full" constructor):
最后是 DTO 类SingerExtendedDTO.java（您必须提供“完整”构造函数）：
package pl.music.model.singer.extended;
public class SingerExtendedDTO {
private Long singerId;
private String firstName;
private String lastName;
private Long countAlbums;
// IMPORTANT: this constructor must be defined !!!
public SingerExtendedDTO(Long singerId, String firstName, String lastName, Long countAlbums) {
this.singerId = singerId;
this.firstName = firstName;
this.lastName = lastName;
this.countAlbums = countAlbums;
}
... getters & setters ...
}
If all this is put together the way presented above, we obtain a proper solution:
如果按照上述方式将所有这些放在一起，我们将得到一个合适的解决方案：
- everything is in one package,
- query declaration does not pollute any unconcerned entity,
- separation of concerns is preserved (seperated query+mapping, DAO and DTO).
- 一切都在一个包裹中，
- 查询声明不会污染任何无关实体，
- 保留关注点分离（分离的查询+映射、DAO 和 DTO）。
