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

Issue

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 = "PERSON")
public class PersonEntity {

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

    @Column(name = "FIRST_NAME")
    private String firstName;

    @Column(name = "LAST_NAME")
    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<PersonEntity, Long> {

    @Query("select u from PersonEntity u where u.firstName = ?1 or u.lastName = ?2")
    List<PersonEntity> 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<PersonEntity> 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<PersonEntity> searchByFirstAndOrLastName(String firstName, String lastName) {

        List<PersonEntity> customers =  repository.searchByFirstAndOrLastName(firstName, lastName);

        if(customers.size() > 0) {
            return customers;
        } else {
            return new ArrayList<PersonEntity>();
        }
    }
}

My controller class:

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

Solution

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("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);

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

Answered By - Eklavya