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

Question

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)

Answer 1

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.

Answer 2

Solution 1: handle exception in getter
One approach that can be used is to modify your getter method using static methods: Hibernate.isInitialized and Hibernate.initialize and handle EntityNotFoundException

    public School getSchool() {
        if (!Hibernate.isInitialized(school)) {
            try {
                Hibernate.initialize(school);
            } catch (EntityNotFoundException ex) {
                school = null;
            }
        }
        return school;
    }

@Table(name = "Student")
@Entity
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;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public School getSchool() {
        if (!Hibernate.isInitialized(school)) {
            try {
                Hibernate.initialize(school);
            } catch (EntityNotFoundException ex) {
                school = null;
            }
        }
        return school;
    }

    public void setSchool(School school) {
        this.school = school;
    }
}

Solution 2: load child entity via separate repository
Remove ManyToOne relation from your entity

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

    @Column(name = "name")
    private String name;

    @Column(name = "school")
    private String school;
}

Create separate repository for School entity with optional loading by ID

@Repository
public interface SchoolRepository extends JpaRepository<School, String> {
    Optional<School> findById(String id);
}

Use SchoolRepository repository for loading entity in required places

        List<Student> students = studentRepository.findStudents();
        Student student = students.get(2);

        if (student.getSchool() != null) {
            School school = schoolRepository.findById(student.getSchool()).orElse(null);
        }

Solution 3: @NotFound(action = NotFoundAction.IGNORE) with LEFT JOIN FETCH
Define ignore @NotFound annotation

    @NotFound(action = NotFoundAction.IGNORE)
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "school")
    private School school 

Rewrite your query for loading Student entities. Use LEFT JOIN FETCH, it allows associations or collections of values to be initialized along with their parent objects using a single select. It significantly improves performance. Student and School entities will be initialized by one query.

@Repository
public interface StudentRepository extends JpaRepository<Student, String> {

    @Query(value = "select s from Student s LEFT JOIN FETCH s.school sc")
    List<Student> findStudents();
}

The main idea of that solution is that you will not have many EGEAR queries for initialization child entities, but only one and @NotFound will skip exception.
Hibernate will generate one batch query:

    select
        student0_.id as id1_34_0_,
        school1_.id as id1_31_1_,
        student0_.name as name2_34_0_,
        student0_.school as school3_34_0_,
        school1_.state as state2_31_1_ 
    from
        student student0_ 
    left outer join
        school school1_ 
            on student0_.school=school1_.id

And the second query just for one missed School entity to ensure that it is not present in DB

    select
        school0_.id as id1_31_0_,
        school0_.state as state2_31_0_ 
    from
        school school0_ 
    where
        school0_.id=?

Answer 3

You need to add @NotFound(action = NotFoundAction.IGNORE) on top of School in Student class. As mentioned by Davide d'Alto on his answer, there's no NY in table School so an exception will be risen by default.

Fixing the database should be the proper answer but sometimes it's not possible or might have other undesired consequences on other applications.

In this case, when annotating with @NotFound(action = NotFoundAction.IGNORE) the value will be set to null in case it's not found.

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

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

More info:

Baeldung - Hibernate EntityNotFoundException

JavaDoc

Answer 4

You said that you're using a simple SELECT * FROM Student query, which means you are using custom queries.

In your case, you should be able to change it to SELECT * FROM Student St LEFT JOIN School Sc ON Sc.ID = St.School;.

What that should do is load all of the records in one query and any student records that don't have a matching school should return null for the School attribute on the Student class.

Side Note: When working with relationships and JPA, if it's a simple relationship like this, you can use joins to ensure everything is loaded in one query, rather than JPA loading all of the parent entities and then issuing a query for each of those to fetch the child entities, so it can have quite the performance improvement.