CODEWITHSUNDEEP
sqlhibernatejpaormjpa-2.0
user1843591
user1843591

Reputation: 1136

Convert simple SQL query to JPA... OneToMany Relationship

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

Upvotes: 0

Views: 2106

Answers (1)

jax
jax

Reputation: 38583

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.

Upvotes: 1

Related Questions