Hibernate unidirectional @OneToOne using inner join

Question

I am using:

Spring Boot
Spring Data JPA
Spring Data Rest
Hibernate
Embedded H2 Database

I am trying to define 2 classes, First and Second, such that there is a one-to-one mapping from First to Second. Since I am trying to host queries via Spring Data Rest, which will convert data to JSON, I believe it makes most sense to do EAGER fetches.

So, I have the following:

@Entity
public class First {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "FIRST_ID")
    private long id;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "SECOND_ID")
    private Second second;

    // more/getters/settings
}

@Entity
public class Second {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "SECOND_ID")
    private long id;

    // more/getters/settings
}

When I search for data of type First, I get a SELECT query for the First type and then individual SELECT queries for each Second where the SECOND_ID matches the foreign key referenced in First.

It makes most sense to do an INNER JOIN on this data to do everything in a single query. Such as:

SELECT * FROM FIRST
INNER JOIN SECOND ON FIRST.SECOND_ID

I can run this query directly on the database and get a table joining the two tables.

How can I get JPA/Hibernate to do this? This seems like an common operation and I feel like I am missing something obvious.

Edit: Note that I am using running the query via automatically generated REST endpoints from Spring Data Rest. I have defined a Spring Data repository:

@RepositoryRestResource(path = "first")
public interface FirstRepository extends CrudRepository<First, Long> {
}

Then, by accessing http://host/first, I cause Spring to run a findAll operation, which works, but, again, triggers a large number of SELECT queries.

First of all thank you very much for posting this question. I have trawled for HOURS to figure out how to create the exact use case for my needs with a Spring Data REST JPA oneToOne Unidirectional. — Beezer, Jun 13 '18 at 18:22
btw, and for anyone ineterested in the multiple @OneToOne use case where you would add a Third to the above example, _BEWARE_ that if you ALSO then define a CRUD repository for either the Second or Third, it will not work as expected. For some reason, you must not declare a repository for any of the one directionals (Second or subsequent Third or Fourth ad. infitintum.). I do not know why, but it will then fail if you exclude the JSON of say Third as such: `{ "firstName": "string", "second": { "secondName": "string" } }` So make sure if you want directional one2ones you omit. — Beezer, Jun 13 '18 at 19:20

Alan Hay · Accepted Answer · 2017-04-27T20:15:54.707

3

Firstly, the fetch hints on the association are only respected when an Entity is loaded using EntityManager#find().

If you were to get an individual First instance (http://host/first/1) you should see the instance being retrieved with its association in 1 query using LEFT OUTER JOIN (marking the relationship as optional = false) would result in an INNER JOIN) as Spring Data will delegate to the EntityManager#find() method.

Spring Data provides a convenience findAll() method that simply generates a query "Select f from First f" when you opt to get all instances and which is called when you hit http://host/first. So this obviously doesn't do a JOIN FETCH.

If you were to create a query:

@Query("select f from First f join fetch f.second")
public List<First> findAllEager();

and hit http://host/first/search/findAllEager then this should load everything in one select.

As that is obviously not very convenient you can then actually just redefine the findAll() method in your repository interface as below:

@Query("select f from First f join fetch f.second")
public Iterable<First> findAll();

Additional information: when loading an Entity using EntityManager#find() then a @OneToOne association is always eager in Hibernate unless marked as both non-optional and Lazy.

edited Apr 27 '17 at 20:15

answered Apr 27 '17 at 19:40

Alan Hay

22,665
4
56
110

Aha! "join fetch" in the HQL was what I was trying to figure out. This does work, but I think the solution from Cepr0 is a better solution for my use case, which avoids the `@Query` entirely. – Mike Apr 28 '17 at 15:34
Well if you can guarantee EntityGraph will do a join fetch then I guess so. But you can't always be sure of that.... – Alan Hay Apr 28 '17 at 15:40
Is that not the case? It seems to be doing joins for me, but I can't seem to find any documentation either way. – Mike Apr 28 '17 at 15:43
It is probably not specified HOW it should be implemented. A provider could do a join or a second select I guess and still meet the specification. – Alan Hay Apr 28 '17 at 15:45
Actually, I think this is the best approach after all. EntityGraph seems to only allow 1 level of subgraphs. – Mike Apr 28 '17 at 16:22

score 3 · Answer 2 · answered Apr 28 '17 at 12:14

3

Try this variant:

@RepositoryRestResource(path = "first")
public interface FirstRepository extends CrudRepository<First, Long> {

    @Override
    @EntityGraph(attributePaths = {"second"})
    Iterable<First> findAll();
}

answered Apr 28 '17 at 12:14

Cepr0

28,144
8
75
101

Thanks! This is just what I was looking for. I guess I didn't quite understand what `EntityGraph` did. – Mike Apr 28 '17 at 15:35
Even better, I guess I can define a `@NamedEntityGraph` and specify `includeAllAttributes = true` and avoid referencing individual attributes. – Mike Apr 28 '17 at 15:41
It seems one can only specify one level of sub graphs. See: http://stackoverflow.com/a/39020912/1612093 – Mike Apr 28 '17 at 16:28
1

What I would REALLY like to know, is if I do _NOT_ add a 2nd (i.e. it is null) using POS on First, how do I prevent it from being show in the findAll(); I could write my own Query I guess...thanks to ALL for this post. – Beezer Jun 13 '18 at 18:24

Hibernate unidirectional @OneToOne using inner join

2 Answers2