Spring data JPA to join subclasses in query

Question

Consider this class:

@Entity
@Table(name = "USER_TABLE")
@Inheritance(strategy = InheritanceType.JOINED)
public class User {

    @Id
    @GenericGenerator(name = "system-uuid", strategy = "uuid2")
    @GeneratedValue(generator="system-uuid")
    @Column(name = "id", unique = true)
    @Type(type= "org.hibernate.type.PostgresUUIDType")
    private UUID id;

    @Column(name = "USERNAME")
    private String userName;
}

and its simple child:

@Entity
@Table(name = "CUSTOMER")
public class Customer extends User{
        //some extra fields
}

I have another class having relation to user:

@Entity
@Table(name = "USER_ACTIVITY")
public class UserActivity {

    @ManyToOne
    @JoinColumn(name = "USERID")
    private User user;

    //id and other fields
}

This is the tricky part. I want to fetch all objects of type UserActivity which relates to a "Customer". I tried something like this:

public interface UserActivityRepository extends PagingAndSortingRepository<UserActivity,UUID> {
    @Query("select u from UserActivity u join Customer.id")
    List<UserActivity> findAllByUserIsNotNullOrderByCreatedDesc(Pageable pageable);
}

But hibernate tells me that I've specified an invalid path.

Could you help me on this?

Dragan Bozanovic · Accepted Answer · 2017-06-21 10:52:17Z

1

You can explicitly join with Customer or filter by User ids that belong to Customer ids:

select u from UserActivity u, Customer c where u.user.id = c.id
select u from UserActivity u where u.user.id in (select id from Customer)

Pick any you like better, as optimizers in modern databases will optimize both queries so that performance is the same in both cases.

answered Jun 21, 2017 at 10:52

Dragan Bozanovic

23.7k5 gold badges50 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abdullah Mohammad Motiullah · Accepted Answer · 2017-06-21 16:31:20Z

0

Use @DiscriminatorColumn(name = "TYPE") in your User entity.

@Entity
@Table(name = "USER_TABLE")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "TYPE")
@DiscriminatorValue("USER")
public class User {

    ...

}

Define a @DiscriminatorValue("CUSTOMER") in your Customer entity

@Entity
@Table(name = "CUSTOMER")
@DiscriminatorValue("CUSTOMER")
public class Customer extends User {

    ...

}

Query your repository like so

@Query("select u from UserActivity u join u.user user where TYPE(user) = 'CUSTOMER'")
List<UserActivity> findAllByUserIsNotNullOrderByCreatedDesc();

Note: @DiscriminatorValue annotation is not mandatory. JPA infers the column name based on the entity type.

edited Jun 21, 2017 at 16:31

answered Jun 21, 2017 at 10:43

Abdullah Mohammad Motiullah

12.7k6 gold badges72 silver badges84 bronze badges

4 Comments

Chris Over a year ago

Type takes the entity name, not the DiscriminatorValue. stackoverflow.com/questions/3765948/…

Abdullah Mohammad Motiullah Over a year ago

Yes it does. That's why TYPE(user). user is the mapped entity.

Chris Over a year ago

That's great that a provider supports it, but JPA requires the entity class as the input parameter, separating the values used in the discriminator column or even the mechanism used in determining classes from the actual type you are looking for. Your answer about the type is correct, but you don't need to specify the Discriminator annotations at all and just use the class.

Abdullah Mohammad Motiullah Over a year ago

Yeah i tested the above without @DiscriminatorValue("USER") and its still running fine. Thanks for the update @Chris

Collectives™ on Stack Overflow

Spring data JPA to join subclasses in query

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related