JPA One to one inheritance matching: 2 Requests for what should be one

Question

I have a one-to-one relationship between a Media entitiy object and a MediaAnalysis , where the Media object is an abstract base class:

News Report Object

 @Entity @DiscriminatorValue("N") public class NewsReport extends Media { @Column(name = "BODY", nullable = false) private String body; NewsReport(){} public NewsReport(String title, String link, String author, String body) { super(title, link, author); this.body= body; } public String getBody() { return body; } } 

Media object

 @Entity @Inheritance( strategy = InheritanceType.SINGLE_TABLE ) @DiscriminatorColumn(name = "TYPE", length = 1, discriminatorType = DiscriminatorType.STRING) public abstract class Media { @Id @GeneratedValue(strategy= GenerationType.AUTO) private Long id; @Column(name = "TITLE", nullable = false) private String title; @Column(name = "LINK", length = 500, nullable = false) private String link; @Column(name = "AUTHOR", length = 45, nullable = false) private String author; @OneToOne(mappedBy = "media") private MediaAnalysis analysis; Media(){} public Media(String title, String link, String author) { this.title = title; this.link = link; this.author = author; } // getters public Optional<MediaAnalysis> getAnalysis() { return Optional.ofNullable(analysis); } } 

Media analysis

 @Entity public class MediaAnalysis { @Id @GeneratedValue(strategy= GenerationType.AUTO) private Long id; @Column(name = "SUCCESS", nullable = false) private Boolean success; @OneToOne @JoinColumn( name = "MED_ID", nullable = false, foreignKey = @ForeignKey(name="MEA_MED_FK") ) private Media media; @Column(name = "CONTENT", nullable = false) private String content; MediaAnalysis() { } public MediaAnalysis(Media media, Boolean success, String content) { this.media = media; this.success = success; this.content = content; } // getters public Media getMedia() { return media; } public String getContent() { return content; } } 

Now that I want to use my AnalysisRepository.getByMedia(..a NewsReport...)

 public interface AnalysisRepository extends JpaRepository<MediaAnalysis,Long> { @Query("SELECT a FROM MediaAnalysis a LEFT JOIN FETCH a.media WHERE a.media = ?1") Optional<MediaAnalysis> getByMedia(Media media); } 

To find MediaAnalysis on a NewsReport , for example, I would expect hibernate to start a single SELECT query, for example:

SELECT m. * from media analysis m, where m.med_id =?

But instead, when I turn on query logging, I see 2:

DEBUG ohSQL: 92 - select mediaanaly0_.id as id1_0_0_, media1_.id as id2_1_1_, mediaanaly0_.med_id as med_id3_0_0_, mediaanaly0_.success as success2_0_0_, media1_.author as author3_1_1_1, media__1_1_1_1 link_1_1_1_1 link_1_1_1_1_1_1_1 body as body6_1_1_, media1_.type as type1_1_1_ from mea_media_analysis mediaanaly0_ left external join med_media media1_ on mediaanaly0_.med_id = media1_.id where mediaanaly0_.med_id =?

TRACE ohtdsBasicBinder: 65 - binding parameter [1] as [BIGINT] - [1]

DEBUG ohSQL: 92 - select mediaanaly0_.id as id1_0_1_, mediaanaly0_.med_id as med_id3_0_1_, mediaanaly0_.success as success2_0_1_, media1_.id as id2_1_0_, media1_.author as author3_1_0_1, media1__1_1_1_1_1 link_1_1_1_1_1_1_l body as body6_1_0_, media1_.type as type1_1_0_ from mea_media_analysis mediaanaly0_ internal join med_media media1_ on mediaanaly0_.med_id = media1_.id where mediaanaly0_.med_id =?

TRACE ohtdsBasicBinder: 65 - binding parameter [1] as [BIGINT] - [1]

It seems to select MediaAnalysis first as expected, but then there is another query that seems unnecessary. The only difference I can tell between these two queries is the type of connection. I assume the problem is with Media inheritance.

Why is this happening? + What can I do to make sure this is a single request?

---

In the future, if I delete @Query from my repository, there are actually three queries!

DEBUG ohSQL: 92 - select mediaanaly0_.id as id1_0_, mediaanaly0_.med_id as med_id3_0_, mediaanaly0_.success as success2_0_ from mea_media_analysis mediaanaly0_ left external join med_media media1_ on mediaanaly0_.med_id = media1_.id where media1__id

TRACE ohtdsBasicBinder: 65 - binding parameter [1] as [BIGINT] - [1]

DEBUG ohSQL: 92 - select how media0_.id id2_1_0_, media0_.author how author3_1_0_, media0_.link how link4_1_0_, media0_.title how title5_1_0_, media0_.body how body6_1_0_, media0_.type how type1_1_0_, mediaanaly1_.id as id1_0_1_, mediaanaly1_. med_id as med_id3_0_1_, mediaanaly1_.success as success2_0_1_ from med_media media0_ left external join mea_media_analysis mediaanaly1_ to media0_.id = mediaanaly1_.med_id where media0_.id =?

TRACE ohtdsBasicBinder: 65 - binding parameter [1] as [BIGINT] - [1]

DEBUG ohSQL: 92 - select mediaanaly0_.id as id1_0_1_, mediaanaly0_.med_id as med_id3_0_1_, mediaanaly0_.success as success2_0_1_, media1_.id as id2_1_0_, media1_.author as author3_1_0_1, media1__1_1_1_1_1 link_1_1_1_1_1_1_l body as body6_1_0_, media1_.type as type1_1_0_ from mea_media_analysis mediaanaly0_ internal join med_media media1_ on mediaanaly0_.med_id = media1_.id where mediaanaly0_.med_id =?

TRACE ohtdsBasicBinder: 65 - binding parameter [1] as [BIGINT] - [1]

Answer 1

I have no experience with JpaRepository, just (a lot) with Hibernate (mostly CriteriaBuilder), but some ideas:

• try matching @OneToOne only one of two objects (should be MediaAnalysis for your example)
• try matching MediaAnalysis as a 1: n ratio (as if it were possible to have multiple MediaAnalysis on the media, which may well be in my understanding, maybe not in your domain).

See if any of these help in the generated queries.

Answer 2

I am trying to reproduce your problem in this dedicated branch on github . With the exception of the getter installer in entity classes, I am not changing anything from your sample code, especially for the repository class. From which a simple unit test code show me, there is nothing wrong with your request, and exactly 1 request is generated, as shown below:

 ---------- WATCH GENERATED QUERY HERE ---------- 2018-01-19 09:20:26.880 DEBUG 22769 --- [main] org.hibernate.SQL : select mediaanaly0_.id as id1_1_0_, media1_.id as id2_0_1_, mediaanaly0_.content as content2_1_0_, mediaanaly0_.med_id as med_id4_1_0_, mediaanaly0_.success as success3_1_0_, media1_.author as author3_0_1_, media1_.link as link4_0_1_, media1_.title as title5_0_1_, media1_.body as body6_0_1_, media1_.type as type1_0_1_ from media_analysis mediaanaly0_ left outer join media media1_ on mediaanaly0_.med_id=media1_.id where mediaanaly0_.med_id=? ---------- END OF GENERATED QUERY HERE ---------- 

However, your problem arises, perhaps because:

• Error in JPA / Hibernate / Spring Currently using the data version (you do not mention the specification here).
• An unnecessary call in your code at a different level (service, controller level).

Another thing that might be useful is that you could add @Fetch(FetchMode.JOIN) annotation in your relationship, for example:

 @Fetch(FetchMode.JOIN) @OneToOne @JoinColumn( name = "MED_ID", nullable = false, foreignKey = @ForeignKey(name="MEA_MED_FK") ) private Media media; 

But I strongly recommended that you check and / or update the version of your libraries and / or write a database test to make sure that the problem arose.

NTN.

Answer 3

One way to achieve only one database query is to specify the @OneToOne relationship only on the owner side, from which I understand this is a MediaAnalysis entity.

 public class MediaAnalysis { // ... @OneToOne(cascade = CascadeType.ALL) @JoinColumn(name = "MED_ID") private Media media; // ... } 

And completely remove the analysis field from the Media object.

Just tried this, and it worked, as expected, with the only request executed in the test:

 select mediaanaly0_.id as id1_3_0_, media1_.id as id2_2_1_, mediaanaly0_.content as content2_3_0_, mediaanaly0_.med_id as med_id4_3_0_, mediaanaly0_.success as success3_3_0_, media1_.author as author3_2_1_, media1_.link as link4_2_1_, media1_.title as title5_2_1_, media1_.body as body6_2_1_, media1_.type as type1_2_1_ from mediaanalysis mediaanaly0_ left outer join media media1_ on mediaanaly0_.med_id=media1_.id where mediaanaly0_.med_id=? 

You can find sample code in the github repository .