使用Spring JPA搜索与关键字匹配的相关实体列。

huangapple
go评论95阅读模式
英文:

searching related entity columns for matching keyword with Spring JPA

问题

我使用了先前提出的问题来开发一个足够好的解决方案，以便通过关键字搜索大多数列。我的问题是，我有一些列是对其他实体的外键，我想在搜索中包括它们。

以下两种方法都不起作用：

  1. SearchSpecification byBedroom = new SearchSpecification(new SearchCriteria("masterBedroom", ":", "searchVal"));
    执行但仅返回非外键实体，或

  2. 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更易阅读。

  1. 添加依赖项
  1. <querydsl.version>5.0.0</querydsl.version>
  3. <dependency>
  4.     <groupId>com.querydsl</groupId>
  5.     <artifactId>querydsl-core</artifactId>
  6.     <version>${querydsl.version}</version>
  7. </dependency>
  8. <dependency>
  9.     <groupId>com.querydsl</groupId>
  10.     <artifactId>querydsl-jpa</artifactId>
  11.     <version>${querydsl.version}</version>
  12.     <classifier>jakarta</classifier>
  13. </dependency>
  14. <dependency>
  15.     <groupId>com.querydsl</groupId>
  16.     <artifactId>querydsl-sql</artifactId>
  17.     <version>${querydsl.version}</version>
  18. </dependency>
  19. <dependency>
  20.     <groupId>com.querydsl</groupId>
  21.     <artifactId>querydsl-sql-spring</artifactId>
  22.     <version>${querydsl.version}</version>
  23. </dependency>
  24. <dependency>
  25.     <groupId>com.querydsl</groupId>
  26.     <artifactId>querydsl-apt</artifactId>
  27.     <version>${querydsl.version}</version>
  28.     <classifier>jakarta</classifier>
  29. </dependency>

运行mvn clean install - 它将生成QueryDSL模型类。

  1. 创建一个搜索参数POJO
    以下示例使用record：
  1. @Builder(toBuilder = true)
  2. public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
  1. 创建一个搜索存储库接口
  1. public interface UserProfileFilterRepository {
  3.     List<UserProfile> fetchAll(UserProfileFilterParam filter);
  5.     // 如果需要，可以添加其他方法
  6. }
  1. 在步骤3中创建存储库的实现
  1. @Repository
  2. public class UserProfileFilterRepositoryImpl extends QuerydslRepositorySupport implements UserProfileFilterRepository {
  4.     public UserProfileFilterRepositoryImpl() {
  5.         super(UserProfile.class);
  6.     }
  8.     @Override
  9.     public List<UserProfile> fetchAll(UserProfileFilterParam filter) {
  10.         // 以'Q'开头的类是在执行Maven构建命令时由QueryDSL自动生成的。
  11.         QUserProfile userProfile = QUserProfile.userProfile; // 根
  12.         QAddress address = QAddress.address; // 外键
  13.         JPQLQuery<UserProfile> query = from(userProfile)
  14.                 .join(address).on(userProfile.address.id.eq(address.id));
  16.         if (filter.city() != null) {
  17.             query = query.where(address.city.likeIgnoreCase(filter.city()));
  18.         }
  20.         if (filter.country() != null) {
  21.             query = query where(address.country.likeIgnoreCase(filter.country()));
  22.         }
  24.         if (filter.state() != null) {
  25.             query = query.where(address.state.likeIgnoreCase(filter.state()));
  26.         }
  28.         if (filter.pincode() != null) {
  29.             query = query.where(address.pinCode.likeIgnoreCase(Integer.toString(filter.pincode())));
  30.         }
  31.         return query.orderBy(userProfile.id.asc()).fetch();
  32.     }
  33. }

更多详情：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.

  1. Add dependencies

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

  1. &lt;dependency&gt;
  2. 			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
  3. 			&lt;artifactId&gt;querydsl-core&lt;/artifactId&gt;
  4. 			&lt;version&gt;${querydsl.version}&lt;/version&gt;
  5. 		&lt;/dependency&gt;
  6. 		&lt;dependency&gt;
  7. 			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
  8. 			&lt;artifactId&gt;querydsl-jpa&lt;/artifactId&gt;
  9. 			&lt;version&gt;${querydsl.version}&lt;/version&gt;
  10. 			&lt;classifier&gt;jakarta&lt;/classifier&gt;
  11. 		&lt;/dependency&gt;
  12. 		&lt;dependency&gt;
  13. 			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
  14. 			&lt;artifactId&gt;querydsl-sql&lt;/artifactId&gt;
  15. 			&lt;version&gt;${querydsl.version}&lt;/version&gt;
  16. 		&lt;/dependency&gt;
  17. 		&lt;dependency&gt;
  18. 			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
  19. 			&lt;artifactId&gt;querydsl-sql-spring&lt;/artifactId&gt;
  20. 			&lt;version&gt;${querydsl.version}&lt;/version&gt;
  21. 		&lt;/dependency&gt;
  22. 		&lt;dependency&gt;
  23. 			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
  24. 			&lt;artifactId&gt;querydsl-apt&lt;/artifactId&gt;
  25. 			&lt;version&gt;${querydsl.version}&lt;/version&gt;
  26. 			&lt;classifier&gt;jakarta&lt;/classifier&gt;
  27. 		&lt;/dependency&gt;

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

  1. Create a search param pojo
    Below example is using record
  1. @Builder(toBuilder = true)
  2. public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
  1. Create a search repository interface
  1. public interface UserProfileFilterRepository {
  3.     List&lt;UserProfile&gt; fetchAll(UserProfileFilterParam filter);
  5.     //Add methods if needed
  6. }
  1. Create an implementation of repo in step 3
  1. @Repository
  2. public class UserProfileFilterRepositoryImpl extends QuerydslRepositorySupport implements UserProfileFilterRepository {
  4.     public UserProfileFilterRepositoryImpl() {
  5.         super(UserProfile.class);
  6.     }
  8.     @Override
  9.     public List&lt;UserProfile&gt; fetchAll(UserProfileFilterParam filter) {
  10.         //Classes started with &#39;Q&#39; are autogenerated by QueryDSL when executed maven build command.
  11.         QUserProfile userProfile = QUserProfile.userProfile; //root
  12.         QAddress address = QAddress.address; //foreign keys
  13.         JPQLQuery&lt;UserProfile&gt; query = from(userProfile)
  14.                 .join(address).on(userProfile.address.id.eq(address.id));
  16.         if(filter.city() != null) {
  17.             query = query.where(address.city.likeIgnoreCase(filter.city()));
  18.         }
  20.         if(filter.country() != null) {
  21.             query = query.where(address.country.likeIgnoreCase(filter.country()));
  22.         }
  24.         if(filter.state() != null) {
  25.             query = query.where(address.state.likeIgnoreCase(filter.state()));
  26.         }
  28.         if(filter.pincode() != null) {
  29.             query = query.where(address.pinCode.likeIgnoreCase(Integer.toString(filter.pincode())));
  30.         }
  31.         return query.orderBy(userProfile.id.asc()).fetch();
  32.     }
  33. }

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

huangapple
  • 本文由 发表于 2023年2月18日 12:14:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/75491144.html
  • java
  • spring-boot
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定