Optimizing Relationships Between Entities in Hibernate

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:

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.