How to select from subclass entity property using Spring Data / JPQL?

Question

I need to query on a property of a subclass. Here are the entities involved:

@Entity
@Data
public class Course {

    @Id
    private Long id;
    
    @ManyToOne
    @JoinColumn(name = "personId")
    private Person person;

}

@Entity
@Data
@Inheritance(strategy = InheritanceType.JOINED)
public class Person {

    @Id
    private Long id;

    private String name;

}

@Entity
@Data
@EqualsAndHashCode(callSuper=true)
public class Student extends Person {

    @ManyToOne
    @JoinColumn(name="studentTypeId")
    private StudentType studentType;
            
}

@Entity
@Data
public class StudentType {

    @Id
    private Long id;
    
    @Convert(converter = StudentTypeConverter.class)
    private StudentTypeEnum name;
    
    @RequiredArgsConstructor
    @Getter
    @ToString
    public static enum StudentTypeEnum {
        GRADUATE("Graduate");
        
        private final String name;
        
    }

}

I'm trying to query using a Spring Data repository:

public interface CourseRepository extends Repository<Course>  {
    
    List<Course> findByCoursePersonStudentTypeName(@Param("studentTypeName") String studentTypeName);
}

This doesn't work though. It generates an error stating No property StudentTypeName found for Person!. This makes sense since the property exists only on Student.

How can I write this method (preferably) or use JPQL to find the courses by type of student?

@pirho - No. In my case, it usually is a Student but I need the flexibility for other types of people to attend. — James
– James, Commented Jun 17, 2021 at 17:10
Why do you wrap StudentTypeEnum with StudentType? What is the role of StudentTypeConverter, what does it do? Is it jsut that you can use enum name as String? — pirho
– pirho, Commented Jun 23, 2021 at 7:49

Christian Beikov · Accepted Answer · 2021-06-21 13:03:20Z

1

+50

How about this:

@Query("select c from Course c join c.person as p where UPPER(p.studentType.name) = UPPER(?1)")
List<Course> findByCoursePersonStudentTypeName(String studentTypeName);

This uses a feature of Hibernate called implict casting.

answered Jun 21, 2021 at 13:03

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

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

2 Comments

James Over a year ago

This works great. Could you please provide a link to Hibernate documentation discussing implicit casting?

Christian Beikov Over a year ago

There is unfortunately no documentation about this that I could find. I'll create an issue for adding documentation about this: hibernate.atlassian.net/browse/HHH-14699

tremendous7 · Accepted Answer · 2021-06-17 21:13:24Z

1

try this

@Query("select c from Course c join c.person as p, Student s where p.id = s.id and s.studentType.name = ?1")
List<Course> findByCoursePersonStudentTypeName(StudentTypeEnum studentTypeEnum);

edited Jun 17, 2021 at 21:13

answered Jun 15, 2021 at 16:49

tremendous7

7766 silver badges10 bronze badges

5 Comments

James Over a year ago

+1 This is close. If I change, the parameter type from String to StudentTypeEnum it works. But I really would like to keep the parameter as a String and let the converter convert to a StudentTypeEnum.

tremendous7 Over a year ago

@James updated the answer using StudentTypeEnum

James Over a year ago

Thanks. But this requires me to pass in GRADUATE. I would like to be able to pass in Graduate, graduate, etc. The converter that I built ignores case. How would you modify the query to keep using String ?

tremendous7 Over a year ago

@James what about invoking the aforementioned query by StudentTypeEnum passing the following new StudentTypeConverter().convertToEntityAttribute(studentTypeString) as parameter?

James Over a year ago

Yeah, that would normally work well. But the problem here is that this is directly exposed to the client via Spring Data Rest.

Grzegorz Kawalec · Accepted Answer · 2021-06-18 19:11:41Z

The following query in the repository worked for me:

public interface CourseRepository extends Repository<Course, Long> {

    @Query("select c" +
            " from Course c" +
            " inner join Student s on c.person.id = s.id" +
            " where s.studentType is not null" +
            " and s.studentType.name = :studentTypeName")
    List<Course> findByCoursePersonStudentTypeName(@Param("studentTypeName") StudentType.StudentTypeEnum studentTypeName);

}

Below I am inserting the enumeration type converter code you indicated you use in the @Converter annotation:

public class StudentTypeConverter implements AttributeConverter<StudentType.StudentTypeEnum, String> {

    private final Map<String, StudentType.StudentTypeEnum> groupByName = Arrays
            .stream(StudentType.StudentTypeEnum.values())
            .collect(Collectors.toMap(StudentType.StudentTypeEnum::getName, type -> type));

    @Override
    public String convertToDatabaseColumn(StudentType.StudentTypeEnum attribute) {
        return attribute.getName();
    }

    @Override
    public StudentType.StudentTypeEnum convertToEntityAttribute(String dbData) {
        return groupByName.get(dbData);
    }
}

I also added one type to the enumeration

        GRADUATE("Graduate"),
        OTHER("Other type");

Below is given the method call and its result:

List<Course> result = courseRepository.findByCoursePersonStudentTypeName(StudentType.StudentTypeEnum.GRADUATE);

I also paste the data that was given in the database:

student_type
person
student
course

ehe888 · Accepted Answer · 2021-06-24 19:16:08Z

Honestly speaking, your design voilated very basic Object Oriented principals and is problematic for future maintenance and scalability.

You are trying to use a super class Person to generify different sub types of Person who can register to a Course. But on the other side you want to find the Course linked to a specific subtype of Person which completely break the Generic setup. That's why the Java compiler cannot help you automatically link the query to the solid subtype and Java runtime gives you an exception because they don't know if any subtype of Person has a StudentType property. The language just refuses to do things which could lead to error or unexpected result.

As some other replies has pointed out, you can achieve what you want by using the facilities provided Hibernate (Or other ORM). But this solution is obviously an Anti-Pattern, because it leaks business knowledge into SQL queries.

It turns your design from Object Oriented to SQL Oriented.

My suggestion is to refactor your model, one option is to generified your course class which seems polymophism in your business domain:

public abstract class Course<T extends Person> {
    ...

    @ManyToOne
    @JoinColumn(name = "personId")
    private T person;
}

public class CourseRegisteredByStudent extends Course<Student> {
    
}

public class CourseRegisteredByStaff extends Course<Staff> {
   
}

public interface CousreRegsiteredByStudentRepositry extends Repository<CousreRegsiteredByStudent> { 
    List<CousreRegsiteredByStudent> findByType(@Param("studentTypeName") String studentTypeName);
}

...

Here is a link of the demo of how to use Hibernate Any mapping to handle generic typed entity.

https://github.com/zzantozz/testbed/tree/master/hibernate-any (I borrow the link from this thread https://stackoverflow.com/a/7001162/1109002)

Although there is still some improvement space, this design split the concerns with clear boundaries and avoid leaking domain logic into Repository layer. When design domain models, if you have to write a custom SQL instead of default Repository functions provided by the persistence framework then you should stop and think if there is something wrong on the model design.

Ulrich · Accepted Answer · 2024-06-28 13:51:58Z

0

findByChildAttribute

In your case, you need a method like this findByPersonName(String name)

Hope it useful!

answered Jun 28, 2024 at 13:51

Ulrich

214 bronze badges

1 Comment

SmellyCat Over a year ago

This answer needs to be edited to include more detail on how the findByChildAttribute can be used to find the right courses.

grigouille · Accepted Answer · 2024-09-23 16:02:17Z

0

I would try this :

select c from Course c join fetch c.person inner join Student s on (c.person = s) where s.studentType.name = :studentTypeName

And convert your string to a StudentTypeEnum before sending it to the query with some function like :

public static StudentTypeEnum convert(String text) {
    return text == null ? null : StudentTypeEnum.valueOf(text.toUpperCase());
}

answered Sep 23, 2024 at 16:02

grigouille

7634 silver badges18 bronze badges

Collectives™ on Stack Overflow

How to select from subclass entity property using Spring Data / JPQL?

6 Answers 6

2 Comments

5 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

5 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related