英文:
searching related entity columns for matching keyword with Spring JPA
问题
我使用了先前提出的问题来开发一个足够好的解决方案,以便通过关键字搜索大多数列。我的问题是,我有一些列是对其他实体的外键,我想在搜索中包括它们。
以下两种方法都不起作用:
-
SearchSpecification byBedroom = new SearchSpecification(new SearchCriteria("masterBedroom", ":", "searchVal"));
执行但仅返回非外键实体,或 -
SearchSpecification byBedroom = new SearchSpecification(new SearchCriteria("masterBedroom.roomName", ":", "searchVal"));
抛出异常 - org.springframework.dao.InvalidDataAccessApiUsageException: 无法在此ManagedType上找到具有给定名称[masterBedroom.roomName]的属性。
我在这里漏掉了什么?
英文:
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?
答案1
得分: 1
我建议使用QueryDSL来进行基于过滤的搜索,因为它比Spring Data Specifications或Criteria API更易阅读。
- 添加依赖项
<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>
运行mvn clean install
- 它将生成QueryDSL模型类。
- 创建一个搜索参数POJO
以下示例使用record:
@Builder(toBuilder = true)
public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
- 创建一个搜索存储库接口
public interface UserProfileFilterRepository {
List<UserProfile> fetchAll(UserProfileFilterParam filter);
// 如果需要,可以添加其他方法
}
- 在步骤3中创建存储库的实现
@Repository
public class UserProfileFilterRepositoryImpl extends QuerydslRepositorySupport implements UserProfileFilterRepository {
public UserProfileFilterRepositoryImpl() {
super(UserProfile.class);
}
@Override
public List<UserProfile> fetchAll(UserProfileFilterParam filter) {
// 以'Q'开头的类是在执行Maven构建命令时由QueryDSL自动生成的。
QUserProfile userProfile = QUserProfile.userProfile; // 根
QAddress address = QAddress.address; // 外键
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();
}
}
更多详情: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
英文:
I would suggest to use QueryDSL for filter based search as it's much more readable than Spring data Specifications or Criteria API.
-
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.
- Create a search param pojo
Below example is using record
@Builder(toBuilder = true)
public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
- Create a search repository interface
public interface UserProfileFilterRepository {
List<UserProfile> fetchAll(UserProfileFilterParam filter);
//Add methods if needed
}
- 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论