将简单的SQL查询转换为JPA ... OneToMany关系 [英] Convert simple SQL query to JPA... OneToMany Relationship

问题描述

我有一个JPA（Hibernate）模型...一个对象包含其他对象的列表。如果我想运行SQL查询来搜索BOTH对象，那么它相对比较简单，我做了一个LEFT JOIN，并在所有列上执行简单的 LIKE％searchString％。

  SELECT * FROM my_db.person LEFT JOIN my_db.record ON record.person_pk WHERE ....; 
  

我无法将其转换为JPA样式查询..模型如下所示

  @Access（AccessType.FIELD）
 @Entity 
 @Table（name =person）
 public class Person {
 
 @Column（nullable = true）
 private String name; 
 @Column（nullable = true）
私有字符串地址; 
 $ b $ @ @neToMany（cascade = {CascadeType.PERSIST，CascadeType.MERGE，CascadeType.REFRESH}，mappedBy =debtor）
 @LazyCollection（LazyCollectionOption.FALSE）
 private List< ;记录> records = new LinkedList<>（）; 
 
 ... 
 ... 
 
匹配ManyToOne 
 
 @Entity 
 @Table（name =record ）
 public class Record实现Serializable {
 
 @ManyToOne（cascade = {CascadeType.PERSIST，CascadeType.MERGE，CascadeType.REFRESH}）
 @JoinColumn（name =person_pk ）
私人人物; 
 
 @Column（nullable = true）
 private String factor1; 
 @Column（nullable = true）
 private String factor2; 
  

我想做一个JPA样式的搜索，搜索Person对象中所有可用的列，相关/嵌入记录对象列表中的列。例如，如果searhString是tom，Person实体1的名称是tom，则将返回（包含所有嵌入的对象），并且如果Person的实体10对于ex，因子1的值为tom，则此Person对象也会被返回。

谢谢 从Person中选择distinct p p
left join fetch p.records r
其中p.name类似于：search
或r.factor1 like：search


在将其作为参数传递之前，您需要在搜索周围添加％字符。

I have a JPA (Hibernate) model...One object contains a List of other objects. If i want to run a SQL query to search BOTH objects it is relatively straightforward, I do a LEFT JOIN and do a simple LIKE %searchString% on all columns.

SELECT * FROM my_db.person LEFT JOIN my_db.record ON record.person_pk WHERE .... ; 

I cannot convert this to a JPA style query.. The model is like this

@Access(AccessType.FIELD)
@Entity
@Table(name = "person")
public class Person {

    @Column(nullable = true)
    private String name;
    @Column(nullable = true)
    private String address;

    @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, mappedBy="debtor")
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<Record> records = new LinkedList<>();

...
...

The matching ManyToOne

@Entity
@Table(name = "record")
public class Record implements Serializable {

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
    @JoinColumn(name="person_pk")
    private Person person;

    @Column(nullable = true)
    private String factor1;
    @Column(nullable = true)
    private String factor2;

I would like to do a JPA style search that searches all available columns in the Person object AND all available columns in the List of related/embedded Record objects. For example if the searhString was "tom" and Person entity 1 had a name of "tom" this would be returned(with all embedded objects) and if Person entity 10, for ex, had a value of "tom" for factor1 this Person object would also be returned.

Thanks

解决方案

select distinct p from Person p
left join fetch p.records r
where p.name like :search 
  or r.factor1 like :search

You will need to add % characters around search before you pass it as a parameter.

这篇关于将简单的SQL查询转换为JPA ... OneToMany关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！