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

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

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. 添加依赖项
<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模型类。

  1. 创建一个搜索参数POJO
    以下示例使用record
@Builder(toBuilder = true)
public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
  1. 创建一个搜索存储库接口
public interface UserProfileFilterRepository {

    List<UserProfile> fetchAll(UserProfileFilterParam filter);

    // 如果需要,可以添加其他方法
}
  1. 在步骤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.

  1. Add dependencies

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

&lt;dependency&gt;
			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
			&lt;artifactId&gt;querydsl-core&lt;/artifactId&gt;
			&lt;version&gt;${querydsl.version}&lt;/version&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
			&lt;artifactId&gt;querydsl-jpa&lt;/artifactId&gt;
			&lt;version&gt;${querydsl.version}&lt;/version&gt;
			&lt;classifier&gt;jakarta&lt;/classifier&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
			&lt;artifactId&gt;querydsl-sql&lt;/artifactId&gt;
			&lt;version&gt;${querydsl.version}&lt;/version&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
			&lt;artifactId&gt;querydsl-sql-spring&lt;/artifactId&gt;
			&lt;version&gt;${querydsl.version}&lt;/version&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;com.querydsl&lt;/groupId&gt;
			&lt;artifactId&gt;querydsl-apt&lt;/artifactId&gt;
			&lt;version&gt;${querydsl.version}&lt;/version&gt;
			&lt;classifier&gt;jakarta&lt;/classifier&gt;
		&lt;/dependency&gt;

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

  1. Create a search param pojo
    Below example is using record
@Builder(toBuilder = true)
public record UserProfileFilterParam(String city, String country, String state, Integer pincode) {}
  1. Create a search repository interface
public interface UserProfileFilterRepository {

    List&lt;UserProfile&gt; fetchAll(UserProfileFilterParam filter);

    //Add methods if needed
}
  1. Create an implementation of repo in step 3
@Repository
public class UserProfileFilterRepositoryImpl extends QuerydslRepositorySupport implements UserProfileFilterRepository {

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

    @Override
    public List&lt;UserProfile&gt; fetchAll(UserProfileFilterParam filter) {
        //Classes started with &#39;Q&#39; are autogenerated by QueryDSL when executed maven build command.
        QUserProfile userProfile = QUserProfile.userProfile; //root
        QAddress address = QAddress.address; //foreign keys
        JPQLQuery&lt;UserProfile&gt; 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

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

发表评论

匿名网友

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

确定