CODEWITHSUNDEEP
hibernatejpaspring-data-jpaspring-datajpql
James
James

Reputation: 3184

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

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?

Upvotes: 3

Views: 4215

Answers (6)

grigouille
grigouille

Reputation: 705

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());
}

Upvotes: 0

Ulrich
Ulrich

Reputation: 21

findByChildAttribute

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

Hope it useful!

Upvotes: 0

ehe888
ehe888

Reputation: 158

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.

Upvotes: 0

Christian Beikov
Christian Beikov

Reputation: 16400

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.

Upvotes: 1

Grzegorz Kawalec
Grzegorz Kawalec

Reputation: 325

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);

result

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

  1. student_type
    student_type

  2. person
    person

  3. student
    student

  4. course
    course

Upvotes: 0

tremendous7
tremendous7

Reputation: 751

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);

Upvotes: 1

Related Questions