How to search using condition OR spring jpa

Question

I am performing a search function in mvc spring. I want to do a search on both Firstname and LastName. If I enter a string in the search box and hit enter, I will do a search in firstName if do not find it performing a search in lastName and vice versa. That means I want to enter a string and do a search for all the fields. Here is my program content.

Employee:

@Entity
public class Employee implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String firstName;
    private String lastName;
    private int age;
    private double salary;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

Repository:

@Repository
public interface EmployeeDAO extends  JpaRepository<Employee,Integer>{
    List<Employee> findEmployeeByFirstNameOrLastName(String searchName);

}

Controller :

@RequestMapping(value = "/search")
    public ModelAndView searchByFirstName(@RequestParam String searchName) {
        ModelAndView view = new ModelAndView("showEmployee");
        return view;
    }

View :

 <div class="row">
        <form method="get" action="/search">
            <div class="small-3 columns">
                <input type="text" id ="txt" name="searchName" >
            </div>

            <div class="small-5 columns end">
                <button id="button-id" type="submit">Search</button>
            </div>
        </form>
    </div>

I had run but It can't not search both firstName or LastName. How two search both firstName or lastName with one prameter

Answer 1

Spring Data's derived queries are useful however it is often simpler and clearer to simply specify the query:

@Repository
public interface EmployeeDAO extends  JpaRepository<Employee,Integer>{

    @Query("select e from Employee e where e.firstName = :searchName 
               or e.lastName = :searchName)
    List<Employee> findEmployeeByFirstNameOrLastName(
             @Param("searchName") String searchName);

}

Answer 2

You need to provide two field:

List<Employee> findEmployeeByFirstNameOrLastName(String firstName, String lastName);

Then call it:

findEmployeeByFirstNameOrLastName("searchName", "searchName");

To get Employee whose firstName or lastName equals to "searchName"

The final code look like:

@Repository
public interface EmployeeDAO extends  JpaRepository<Employee,Integer>{
    List<Employee> findEmployeeByFirstNameOrLastName(String firstName, String lastName);

}


@RequestMapping(value = "/search")
public ModelAndView searchByFirstName(@RequestParam String searchName) {
    ModelAndView view = new ModelAndView("showEmployee");
    // Access employeeDAO here, or use employeeService if you using service
    List<Employee> employees = employeeDao.findEmployeeByFirstNameOrLastName(searchName, searchName);
    view.addObject("employees", employees);
    return view;
}

For more complex case, you might want to look at JPA Criteria API or QueryDsl to create combination of conditions instead of providing two fields firstName, lastName like above