Query the database using discriminator values

Question

I am using single table inheritance strategy. I want to perform a search in the database by filtering it using the discriminator type. How am I to write a function in JPA to perform this operation.

The normal way of defining methods using findBy... method does not produce results.

Here is my parent class

@Entity
@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(
        name="Leave_Type",
        discriminatorType=DiscriminatorType.STRING
        )
public class LeaveQuota {
// the fields and the required methods
}

Here are the two child entities

@Entity
@DiscriminatorValue("Annual")
public class AnnualLeave extends LeaveQuota {
// the fields and the required methods
}

@Entity
@DiscriminatorValue("Casual")
public class CasualLeave extends LeaveQuota {
// the fields and the required methods
}

I want to query the database by filtering the Annual leaves and Casual leaves separately. Which means when I search for annual leaves, all records in the discriminator column with value "annual" should be retrieved. How can I implement this. Thanks in advance!

Marpet

please share snippet of your code where you execute the query and sql output, are you sure "Leave_Type" column first letter in uppercase? – Durant

I haven't written a code. That is what i have asked for on how to implement it. "Leave_Type" In the database it is saved using lowercase. – Marpet

Answer 1

Create a repository for AnnualLeave.java namely AnnualLeaveRepo.java and CausualLeave.java namely as shown below:

AnnualLeaveRepo.java

@Repository
public interface AnnualLeaveRepo extends JpaRepository<AnnualLeave,Integer>   {

    @Query("from AnnualLeave")
    public List<AnnualLeave> getLeaves();

    //consider noOfLeave is member of AnnualLeave.java class
    public List<AnnualLeave> findByNoOfLeave(int noOfLeave); 

}

CausalLeaveRepo.java

@Repository
public interface CausalLeaveRepo extends JpaRepository<CausalLeave,Integer>   {

    @Query("from CausalLeave")
    public List<CausalLeave> getLeaves();
}

Now when you use findAll() or getLeaves() or findByNoOfLeave(int) methods or any other custom abstract method of AnnualLeaveRepo class, it will automatically filter result with Leave_Type="Annual".

similarly, when you use findAll() or getLeaves() methods or any other custom abstract method of CausalLeaveRepo class, it will automatically filter result with Leave_Type="Causal".

You don't have to filter out explicitly.

note If you have any properties in your class having relationships(@OneToMany etc..) with LeaveQuota entity or it's inherited entities then don't forget to use the @JsonIgnore annotation on those properties. Else you'll get a stackoverflow error

Breastsummer

The strategy used here is single_table. So will I be able to create a repository for annual leave which does not have a separate table in the database? – Marpet

Yes you can create two repository namely annual leave and causal leave while having single table strategy. – Breastsummer

I tried the above. But the result received iterates infinitely. I mean I get a stackoverflow error – Marpet

Okay. I have uploaded example on GitHub link. Please refer it and you can run it on your local. Make necessary changes in application.properties. – Breastsummer

@Breastsummer Is it not possible to use a single repository? This solution seems ugly. – Raulrausch