searching related entity columns for matching keyword with Spring JPA

Question

I've used the following previously asked question to develop a nice enough solution to search across most of my columns by keyword. My issue is I have columns that are foreign keys to other entities and I'd like to include them in the search.

Neither of the following work:

SearchSpecification byBedroom = new SearchSpecification(new SearchCriteria("masterBedroom", ":", "searchVal")); Executes but returns only non-foreign entities OR

SearchSpecification byBedroom = new SearchSpecification(new SearchCriteria("masterBedroom.roomName", ":", "searchVal")); throws an exception - org.springframework.dao.InvalidDataAccessApiUsageException: Unable to locate Attribute with the the given name [masterBedroom.roomName] on this ManagedType.

What am I missing here?

Answer 1

I would suggest to use QueryDSL for filter based search as it's much more readable than Spring data Specifications or Criteria API.

1. Add dependencies

   <querydsl.version>5.0.0</querydsl.version>

<dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-core</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>${querydsl.version}</version>
            <classifier>jakarta</classifier>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql-spring</artifactId>
            <version>${querydsl.version}</version>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>${querydsl.version}</version>
            <classifier>jakarta</classifier>
        </dependency>

Run mvn clean install - it will generate querydsl model classes.

2. Create a search param pojo Below example is using record

@Builder(toBuilder = true)
public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}

3. Create a search repository interface

public interface UserProfileFilterRepository {

    List<UserProfile> fetchAll(UserProfileFilterParam filter);

    //Add methods if needed
}

4. Create an implementation of repo in step 3

@Repository
public class UserProfileFilterRepositoryImpl extends QuerydslRepositorySupport implements UserProfileFilterRepository {

    public UserProfileFilterRepositoryImpl() {
        super(UserProfile.class);
    }

    @Override
    public List<UserProfile> fetchAll(UserProfileFilterParam filter) {
        //Classes started with 'Q' are autogenerated by QueryDSL when executed maven build command.
        QUserProfile userProfile = QUserProfile.userProfile; //root
        QAddress address = QAddress.address; //foreign keys
        JPQLQuery<UserProfile> query = from(userProfile)
                .join(address).on(userProfile.address.id.eq(address.id));

        if(filter.city() != null) {
            query = query.where(address.city.likeIgnoreCase(filter.city()));
        }

        if(filter.country() != null) {
            query = query.where(address.country.likeIgnoreCase(filter.country()));
        }

        if(filter.state() != null) {
            query = query.where(address.state.likeIgnoreCase(filter.state()));
        }

        if(filter.pincode() != null) {
            query = query.where(address.pinCode.likeIgnoreCase(Integer.toString(filter.pincode())));
        }
        return query.orderBy(userProfile.id.asc()).fetch();
    }
}

More details: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl https://www.baeldung.com/rest-api-search-language-spring-data-querydsl