DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Implement Hibernate Second-Level Cache With NCache
  • Architectural Miscalculation and Hibernate Problem "Type UUID but Expression Is of Type Bytea"
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps
  • Simplify Java Persistence Using Quarkus and Hibernate Reactive

Trending

  • How to Submit a Post to DZone
  • Using Python Libraries in Java
  • The Smart Way to Talk to Your Database: Why Hybrid API + NL2SQL Wins
  • Enforcing Architecture With ArchUnit in Java
  1. DZone
  2. Data Engineering
  3. Databases
  4. Optimizing Relationships Between Entities in Hibernate

Optimizing Relationships Between Entities in Hibernate

See how to optimize relationships between entities in Hibernate.

By 
Jesus J. Puente user avatar
Jesus J. Puente
·
Apr. 22, 19 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
12.2K Views

Join the DZone community and get the full member experience.

Join For Free

In this post, following the example project that you have here, I am going to explain how to optimize queries to a database using JPA.

You will see different types of queries explaining how to make connections between lazy and eager tables. The tables will be joined by a single field, by several and even by one, but adding a static condition.

The example project is developed in Spring Boot with Hibernate using H2 as a database.

You can find the definition of the tables in the file schema.sql. The test data is loaded in the data.sql file.

This is the schema of the database:

Image title

On that occasion, I will not explain the code because it is very similar to other programs that I have explained in detail in other entries. You can find the other examples in http://www.profesor-p.com (in Spanish). I will focus on how to perform the different queries made in the program.

Explanatory Note: If the name of the entity or of the column in the Java classes has an uppercase letter in between, JPA will interpret that there is a hyphen in the middle, and that will be the table or column that it will look for in the database. In this way, if the   Invoiceheader.java  class was renamed to  InvoiceHeader.java  Hibernate, it would look for the invoice_header table in the database, and it would fail because it would not find it. As an example, you can see the line_details field of the invoiceDetails table, which in the Invoicedetails.java class (note that the D is lowercase) is called with the lineDetails variable.

Performing a 'select join' between the invoiceHeader table and customer table

  • Lazy Link

If you want to have the fields of the customer entity, in your invoice header entity, you must add this lines at the class 'Invoiceheader.java'

@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="customerid",referencedColumnName="id")
Customer customer;

When performing the search, the resulting query will be:

select
        invoicehea0_.id as id1_3_0_,
        invoicehea0_.customerid as customer2_3_0_,
        invoicehea0_.fiscalyear as fiscalye3_3_0_,
        invoicehea0_.numberinvoice as numberin4_3_0_ 
    from
        invoiceheader invoicehea0_ 
    where
        invoicehea0_.id=?

And when you read the value of field customer, a new query to find the customer's data will be executed:

 select
        customer0_.id as id1_1_0_,
        customer0_.active as active2_1_0_,
        customer0_.address as address3_1_0_,
        customer0_.name as name4_1_0_ 
    from
        customer customer0_ 
    where
        customer0_.id=?
  • Hard link
@ManyToOne(fetch=FetchType.EDGER)
@JoinColumn(name="customerid",referencedColumnName="id")
Customer customer;

Since the search type is set to FetchType.EDGER, make a single select with its corresponding left outer join:

 select
        invoicehea0_.id as id1_3_0_,
        invoicehea0_.customerid as customer2_3_0_,
        invoicehea0_.fiscalyear as fiscalye3_3_0_,
        invoicehea0_.numberinvoice as numberin4_3_0_,
        customer1_.id as id1_1_1_,
        customer1_.active as active2_1_1_,
        customer1_.address as address3_1_1_,
        customer1_.name as name4_1_1_ 
    from
        invoiceheader invoicehea0_ 
    left outer join
        customer customer1_ 
            on invoicehea0_.customerid=customer1_.id 
    where
        invoicehea0_.id=?
  • Curling the curl. Adding fixed values

But what if we want to link the two tables with one column and also with a fixed value in another?

In the customer table, the active column was defined, and we want it to only show us the data of the invoice when the value of that column is 1. For this, we need the help of the @JoinColumnsOrFormulas tag that allows us to make connections between two columns as well as establish values to the column of the destination table (in this case customer).

@ManyToOne(fetch=FetchType.EDGER)
@JoinColumnsOrFormulas({
 @JoinColumnOrFormula(column=@JoinColumn(name="customerid", referencedColumnName ="id") ),
 @JoinColumnOrFormula(formula = @JoinFormula(value="1",referencedColumnName = "active"))
})
Customer customer;

The query executed will be:

select
        invoicehea0_.id as id1_3_0_,
        invoicehea0_.customerid as customer2_3_0_,
        invoicehea0_.fiscalyear as fiscalye3_3_0_,
        invoicehea0_.numberinvoice as numberin4_3_0_,
        1 as formula1_0_,
        customer1_.id as id1_1_1_,
        customer1_.active as active2_1_1_,
        customer1_.address as address3_1_1_,
        customer1_.name as name4_1_1_ 
    from
        invoiceheader invoicehea0_ 
    left outer join
        customer customer1_ 
            on invoicehea0_.customerid=customer1_.id 
            and 1=customer1_.active 
    where
        invoicehea0_.id=?

In case you do not find any record, the customer variable will have a null value.

If the link type were lazy, as in the previous case, a query would be made on the invoiceheader table, and when the value of the customer variable was requested, it would be carried out on its corresponding table.

Linking Headers Invoices and Invoice Lines Entities

To join the two tables, we will put the following code in the class Invoicedetails.java

@OneToMany
@JoinColumns(
    {
        @JoinColumn(name="fiscalyear",referencedColumnName="fiscalyear"),
       @JoinColumn(name="numberinvoice",referencedColumnName="numberinvoice")
    }
)
@OrderBy("linea desc")
List<Invoicedetails> details;

As you can see, since there are two fields that join both tables, we will use the @JoinColumns tag with its corresponding @JoinColumn inside.

As we have not specified anything, the union will be made of the EAGER type, so the query made to the database will be the following:

 select
        invoicehea0_.id as id1_3_0_,
        invoicehea0_.customerid as customer2_3_0_,
        invoicehea0_.fiscalyear as fiscalye3_3_0_,
        invoicehea0_.numberinvoice as numberin4_3_0_,
        1 as formula1_0_,
        details1_.fiscalyear as fiscalye2_2_1_,
        details1_.numberinvoice as numberin5_2_1_,
        details1_.id as id1_2_1_,
        details1_.id as id1_2_2_,
        details1_.articleid as articlei6_2_2_,
        details1_.fiscalyear as fiscalye2_2_2_,
        details1_.linea_details as linea_de3_2_2_,
        details1_.numberarticles as numberar4_2_2_,
        details1_.numberinvoice as numberin5_2_2_,
        article2_.id as id1_0_3_,
        article2_.description as descript2_0_3_,
        article2_.price as price3_0_3_ 
    from
        invoiceheader invoicehea0_ 
    left outer join
        invoicedetails details1_ 
            on invoicehea0_.fiscalyear=details1_.fiscalyear 
            and invoicehea0_.numberinvoice=details1_.numberinvoice 
    left outer join
        article article2_ 
            on details1_.articleid=article2_.id 
    where
        invoicehea0_.id=?

The last "left outer join" referring to the article table is put by Hibernate because in the Invoicedetails.java class, we have the code:

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumns({
@JoinColumn(name="articleid",referencedColumnName="id")
})
Article articles;

so that it shows us the data of the article for each line of the article, and as this is marked the union to type EAGER, Hibernate is smart enough to make a single query to the database.

If we make a call to http://localhost:8080/1 we will observe the following output that returns the Invoiceheader.java class, and we will see the following:

{
    "id": 1,
    "yearFiscal": 2019,
    "numberInvoice": 1,
    "customerId": 1,
    "customer": {
        "id": 1,
        "name": "customer 1 name",
        "address": "customer 1 address",
        "active": 1
    },
    "details": [
        {
            "id": 2,
            "year": 2019,
            "invoice": 1,
            "linea": 2,
            "numberarticles": 3,
            "articles": {
                "id": 2,
                "description": "article 2 description",
                "price": 12.3
            }
        },
        {
            "id": 1,
            "year": 2019,
            "invoice": 1,
            "linea": 1,
            "numberarticles": 5,
            "articles": {
                "id": 1,
                "description": "article 1 description",
                "price": 10.1
            }
        }
    ]
}

Do you see something weird? The lines take them ordered from highest to lowest (first line 2, then line 1). Obviously, that is due to the label @OrderBy("linea desc") , of which we must highlight two things:

  • The field we refer to is how it is named in the entity. So, although in the database it is called lineaDetails, it is referenced by the label linea, which is how the variable is named
 @Column (name = "lineaDetails")
 int line;  // The 'D' in uppercase is intentionally set to map the field linea_details 
  • In the query, that makes the database not enter the clause 'order by.' In other words, it is the hibernate itself that orders the results. Take it into account when there are thousands of records returned.

And this shows the importance of establishing the type of union, because if we imagine an invoice that has thousands of lines (unlikely I know), if we establish the method of joining lazy instead of making a single query to the database, it would be 1000, which, of course, would slow down our query a lot, apart from unnecessarily overloading the server in the database.

If you liked this article, you find similar articles here (in Spanish) and you can follow me on Twitter.

Database Hibernate

Published at DZone with permission of Jesus J. Puente. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Implement Hibernate Second-Level Cache With NCache
  • Architectural Miscalculation and Hibernate Problem "Type UUID but Expression Is of Type Bytea"
  • How to Store Text in PostgreSQL: Tips, Tricks, and Traps
  • Simplify Java Persistence Using Quarkus and Hibernate Reactive

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!