Win a copy of Rust Web Development this week in the Other Languages forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

How to make a Join with JPQL, to search in a column of an Jointable?

 
Ranch Hand
Posts: 43
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I try to figure out how to to make a join with subtable.

Here my SQL script in short, which is making clear I hope what I want:
I have made a JPQL which working for the demoEntity.
This is working without the join line. If I do a request with postman al the demo records are returned than.

But with adding the join line than I get the error message: "...InvalidDataAccessApiUsageException: Unable to locate Attribute  with the the given name [demo_id] on this ManagedType..."

Here is the demoEntity and loggingEntity relation:On the logging Entity I had nothing defined (unidirectional), but that field "demo_id" is defined/exist in the database.
So I thought JPQL is maybe this property expecting in the LoggingEntity so I have defined this property but I get still the same error message:


--

Than the next step is than to extend the search opportunity

I will continu to search the internet, but if someone has an idea how to solve this would be very helpfull.
 
Sheriff
Posts: 22568
122
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nico van de Kamp wrote:But with adding the join line than I get the error message: "...InvalidDataAccessApiUsageException: Unable to locate Attribute  with the the given name [demo_id] on this ManagedType..."


This is JPA, not SQL. First of all, in JPA the field is not called demo_id but (probably) demo - the name of the property in the entity, not the name of the column in the database. And besides that, the join needs to be traversed from the DemoEntity side, because that's what you already have. You need to use Join<DemoEntity, LoggingEntity> h = demoEntityRoot.join("loggings", JoinType.INNER);, as the name of the property in DemoEntity is loggings.

Than the next step is than to extend the search opportunity


The infotekst property exists in LoggingEntity, not DemoEntity. That means you cannot use demoEntityRoot.get. You'll need to use your join instead: h.get("infotekst"). That will use the other entity involved in the join (LoggingEntity) instead of the root (DemoEntity).
 
Nico van de Kamp
Ranch Hand
Posts: 43
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Rob,

Thanks. I was already experimenting with it and  was trying this:

But I was also different things trying and I removed . I have put it back it is working now.

The only thing I detected now is that, if I add , that the constructor in the DemoEntity is missing this field. So I need to find out this, but I will do this tommorow.
 
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One of the major strengths of JPA is that you don't normally have to code JOINs in JPQL. Instead you code the entity relationships (OneToMany, ManyToOne, OneToOne, ManyToMany) as annotations of the Entity class definitions and let JPA handle the joining.

You have a lot of code there and it's early in the morning, but I suspect that if you leveraged JPA properly you'd have a lot less code and it would be easier to understand.

That CriteriaBuilder especially bothers me because I suspect that all those constant parameters should be in an array of Strings and fed in via a simple query. CriteriaBuilder is primarily for when you want to make query components optional.
 
Rob Spoor
Sheriff
Posts: 22568
122
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nico van de Kamp wrote:The only thing I detected now is that, if I add , that the constructor in the DemoEntity is missing this field. So I need to find out this, but I will do this tommorow.


That's probably because of the multiselect you use that doesn't match the result of the query. If you only want DemoEntity instances in your results, you should just select(demoEntityRoot).

But Tim is right about using CriteriaBuilder for something that can just as easily be done with JPQL (warning: may contain small errors):

You can even use a named query for this, by annotating DemoEntity with @NamedQuery. Just make sure to use a unique name; I always prefix queries with the entity that is annotated with the query.

Edit: you may want to add a DISTINCT to that clause, because the joining on demo.loggings may cause DemoEntity instances to be duplicated if there are multiple joined LoggingEntity instances that match the criteria.
 
Tim Holloway
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Again. JPA means that you shouldn't be coding JOINS.

A proper JPA query would look more like this:

The JOIN is implicit in the relationship between the Demo entity class and the Logging entity class, if you've properly annotated them.
 
Nico van de Kamp
Ranch Hand
Posts: 43
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:One of the major strengths of JPA is that you don't normally have to code JOINs in JPQL. Instead you code the entity relationships (OneToMany, ManyToOne, OneToOne, ManyToMany) as annotations of the Entity class definitions and let JPA handle the joining.

You have a lot of code there and it's early in the morning, but I suspect that if you leveraged JPA properly you'd have a lot less code and it would be easier to understand.




I have moved from JPA (findByNaamContainingIgnoreCaseOrOmschrijvingContainingIgnoreCase) to JPQL, because of the N+1 issue.

On the project were I'm working on, there are 4 tables, the main table has 800 records, logging table 2400 records and two tables with static data, which mean nothing in my opinion.

But to open to frontend for a list of records takes nearly 12 seconds!!! I saw in de console log that for every single record al the subrecords would be retrieved in separated querys, instead of just 1 query, I believe a kind of 2800 query's were executed.
So I moved to JPQL and now it is one query and take 24 - 18 ms, ok also with less data I understand.

Tim Holloway wrote:
That CriteriaBuilder especially bothers me because I suspect that all those constant parameters should be in an array of Strings and fed in via a simple query. CriteriaBuilder is primarily for when you want to make query components optional.



If I understand this, I have a question about this with specification and JpaSpecificationExecutor.
 
Tim Holloway
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Forgive my blindness, but I can't find the explanation of what "N+1" error means.

Anyway, if you have a set of 4 tables joined off of another table all using annotations, generally you'd lazy-fetch them to avoid sucking them all into RAM at once.

But if you wanted to be more fine-grained, like, say, selecting only log records for a demo that are timestamped within a range, you'd still lazt-fetch, but your JPQL would be like so:


You can make multi-table joins just by referencing other tables or table properties in the same statement. If at all possible, JPA will convert that into a single SQL command and execute it optimally. The one caveat is that if you detach your results, you need to force-fetch the dependent items first.

I do this all the time. In fact, I keep 2 distinct persistence logic layers in most of my apps. The lower-level (DAO) layer handles table-level stuff, the higher-level (service) layer handles the whole group of records as a working set.

If you're seeing tons of SQL operations when doing something like this, then chances are that you're either not relating the Entity objects properly or your queries are not properly formed.
 
Tim Holloway
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Incidentally, if you're only going to use "zoekWaarde" in lower-case, I'd recommend that you convert it in Java code and pass it that way to the query instead of repeatedly converting it in Query language. More efficient and it simplifies the query.
 
Rob Spoor
Sheriff
Posts: 22568
122
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Again. JPA means that you shouldn't be coding JOINS.

A proper JPA query would look more like this:

The JOIN is implicit in the relationship between the Demo entity class and the Logging entity class, if you've properly annotated them.


I think you're right. However, you need to use demo.loggings.infotekst instead of logging.infotekst.
 
Tim Holloway
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Rob Spoor wrote:
I think you're right. However, you need to use demo.loggings.infotekst instead of logging.infotekst.



Agreed. It got lost in all those superfluous "lower" calls.
 
Nico van de Kamp
Ranch Hand
Posts: 43
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Tim Holloway wrote:Forgive my blindness, but I can't find the explanation of what "N+1" error means.



You're blindness? I have noticed that you know a lot more than I do about this. So I must be very thankful to you and Rob.
But what my colleague suggest and I mean is https://www.vincenzoracca.com/en/blog/framework/jpa/jpa-fetch/]this and/or this this

Tim Holloway wrote:I do this all the time. In fact, I keep 2 distinct persistence logic layers in most of my apps. The lower-level (DAO) layer handles table-level stuff, the higher-level (service) layer handles the whole group of records as a working set.


This is hard to understand for me or to visualize it what you exactly mean byt this. I know so far DAO = Data Access Object layer, I'm working with a service layer and a DTO. But what or why do you split up?
Or do you mean entity for the table stuff and of course the service layer for handling the records?

Tim Holloway wrote:If you're seeing tons of SQL operations when doing something like this, then chances are that you're either not relating the Entity objects properly or your queries are not properly formed.


This is for sure! I had first solved it with JPQL. I found it on Baeldung. But my colleague has refactored it, this was not the way to go we need to solve it with JPA. But that doesn't perform due that we have not properly formed our model with JPA. So I have add the JPQL again, at least it is performing now from 12 seconds to 18 ms!

But I will try to find out if also will work with JPA/Hibernate.
 
Tim Holloway
Saloon Keeper
Posts: 24826
172
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your first link doesn't resolve.

Let's see if I can explain my terms better.

First, I have no DTOs. A DTO was a concept designed for transferring data in POJOs back and forth between the persistence layer and the business logic layer (and sometimes to other places like the presentation layer). JPA needs no DTOs because its Entity objects are POJOs.

Secondly, the difference in the Service layer and the DAO layer is that the Service layer methods deal with connected sets of Entity objects.Foe example, I have a demo program (very outdated now, alas) that was designed to managed a public transit (bus) system, defining drivers, buses, routes and schedules. I might want to edit a bus route, and that would require me to pull up the list of bus stops, add/remove stops and update schedules. So I'd pull the Bus route object and it would in turn reference a List of Bus Stops. Or I might pull the route and its reference to Schedule objects, which would reference a list of Stops and times.

So I would pull and save these working sets of objects as arguments from the business (editing) layer to the server layer.

But since a Service object can reference many different Entity types, it can get messy if it attempts all the necessary CRUD operations itself. So I put any necessary database logic (cross-item validation, setting defaults, and so forth) in the Service layer, but do the actual data I/O in the DAO layer.

Each class in the DAO layer handles one Entity class and only one. Usually. If there is a very tight parent-child coupling like Inventory and InventoryItem, I might use a single DAO to manage them as a unit. But the critical thing is that DAO class methods are relatively simple and solely concerned with creating, saving, finding, updating and deleting a single Entity type. The Service methods may then invoke the DAOs as required and all run under a single Transaction.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic