获取所有客户的查询,当指定了firstName和/或lastName时。

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

Query to get all customers when firstName and/or lastName is specified

问题

You can modify your query in the Repository class to achieve the desired functionality by using the COALESCE function in SQL. Here's the modified query:

@Query("SELECT u FROM PersonEntity u WHERE (:firstName IS NULL OR u.firstName = :firstName) AND (:lastName IS NULL OR u.lastName = :lastName)")
List<PersonEntity> searchByFirstAndOrLastName(@Param("firstName") String firstName, @Param("lastName") String lastName);

This query allows you to search for customers based on both first and last names if they are provided or search based on either first name or last name alone if one of them is provided.

英文:

I am working on creating a API using RESTful [Spring boot framework, Hibernate, Java 11]. Here, am trying to implement a query to get all customers when firstName and/or lastName is specified.
So,

  1. Both first and last names can be specified together.
  2. Either first or last name alone can be specified by the user.

I tried to implement both these cases in 1 API. But, didn't get the expected result.
These are my classes:

Entity:

@Entity
@Data
@Table(name = &quot;PERSON&quot;)
public class PersonEntity {

    @Id
    @Column(name = &quot;ID&quot;)
    private Long id;

    @Column(name = &quot;FIRST_NAME&quot;)
    private String firstName;

    @Column(name = &quot;LAST_NAME&quot;)
    private String lastName;
}

My Repository class:

import com.customer.data.management.entity.PersonEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PersonRepository extends JpaRepository&lt;PersonEntity, Long&gt; {

    @Query(&quot;select u from PersonEntity u where u.firstName = ?1 or u.lastName = ?2&quot;)
    List&lt;PersonEntity&gt; searchByFirstAndOrLastName(String firstName, String lastName);
}

Here is my Service interface:

import com.ustomer.data.management.entity.PersonEntity;
import com.customer.data.management.exception.BadRequestException;
import com.customer.data.management.exception.NotFoundException;

import java.util.List;

public interface PersonService {

    List&lt;PersonEntity&gt; searchByFirstAndOrLastName(String firstName, String lastName);
}

Then my ServiceImpl class:

import com.customer.data.management.entity.PersonEntity;
import com.customer.data.management.exception.BadRequestException;
import com.customer.data.management.exception.NotFoundException;
import com.customer.data.management.repository.PersonRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
 
@Service
@Transactional
public class PersonServiceImpl implements PersonService{

    @Autowired
    PersonRepository repository; 

      
    @Override
    public List&lt;PersonEntity&gt; searchByFirstAndOrLastName(String firstName, String lastName) {

        List&lt;PersonEntity&gt; customers =  repository.searchByFirstAndOrLastName(firstName, lastName);

        if(customers.size() &gt; 0) {
            return customers;
        } else {
            return new ArrayList&lt;PersonEntity&gt;();
        }
    }
}

My controller class:

What changes should I make to my query in Repository class to achieve this ?

答案1

得分: 1

你可以将 :column is null 添加为 or 条件与列一起使用,这样当列搜索值为 null 时,列将被忽略。

@Query("select u from PersonEntity u where (:firstName is null or u.firstName = :firstName)"
      + " and (:lastName is null or u.lastName = :lastName)")
List<PersonEntity> searchByFirstAndOrLastName(@Param("firstName") String firstName,
                                              @Param("lastName") String lastName);

案例分析:

  • 对于通过 firstName 和 lastName 进行搜索的情况,它的工作方式如下:

    u.firstName = :firstName and u.lastName = :lastName

  • 对于通过 firstName 和 lastName 为 null 进行搜索的情况,它的工作方式如下:

    u.firstName = :firstName and true

  • 对于通过 lastName 进行搜索,而 firstName 为 null 的情况,它的工作方式如下:

    true and u.lastName = :lastName

英文:

You can add :column is null as or condition with column so that column will be ignored when the column search value is null

@Query(&quot;select u from PersonEntity u where (:firstName is null or u.firstName = :firstName)&quot;
      +&quot; and (:lastName is null or u.lastName = :lastName)&quot;)
List&lt;PersonEntity&gt; searchByFirstAndOrLastName(@Param(&quot;firstName&quot;) String firstName,
                                              @Param(&quot;lastName&quot;) String lastName);

Case Analysis:

  • For case search by firstName and lastName it's work like

    u.firstName = :firstName and u.lastName = :lastName

  • For case search by firstName and lastName is null it's work like

    u.firstName = :firstName and true

  • For case search by lastName and firstName is null it's work like

    true and u.lastName = :lastName

huangapple
  • 本文由 发表于 2020年8月2日 21:33:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/63216620.html
匿名

发表评论

匿名网友

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

确定