[FIXED] How to always return the result of a query from hibernate despite an error present?

Issue

My Case

I have two entities, student and school which are structured as follows:

@Table(name = "Student")
public class Student
{
    @Id
    @Column(name = "id")
    private String id;
    
    @Column(name = "name")
    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "school")
    private School school 
}

@Table(name = "School")
public class School
{
    @Id
    @Column(name = "id")
    private String id;
    
    @Column(name = "state")
    private String state;
}

The following are the two views values:

Student

id	name	school
A	Albert	MCD
B	Kevin	LSU
C	Jack	NY
D	Robert	CA
E	Samuel

School

id	name
MCD	Mich
LSU	Los
CA	Car

So when in hibernate i'm doing a simple select * from Student, it throws an error because the 'NY' id does not exist in the school table.

(To be precise, it throws org.hibernate.LazyInitializationException: No row with the given identifier exists)

Final Goal

My goal is to bypass the error that is thrown an still return the correct entity (So Albert, Kevin, Robert, Samuel)

Solution

For a semi-scalable solution I changed the initial view and returned NULL if it is not present. The change makes the query a little slower but avoids doing one query per element (that would make the environment much slower).

Before

SELECT  ST.ID AS id,
        ST.NAME AS name,
        ST.ID AS school
FROM Student ST

Afther

SELECT  ST.ID AS id,
        ST.NAME AS name,
        SC.ID AS school
FROM Student ST
LEFT JOIN School SC
    ON ST.SCHOOL = SC.ID

Post release notes

Also for compound ids, the best solution seems to be a case when with all the various combinations.

Keep in mind that this is a very small example to understand the logic behind the problem, adding many tables in left join will make the cost of the query increases significantly. However, remains the best compromise after a week of trials and proposed solutions.

Answered By - G. Ciardini
Answer Checked By - Marie Seifert (JavaFixing Admin)