Spring Data JPA Query Methods WHERE IS NULL條件查詢

Spring Data JPA 可用方法名稱查詢(query mehtods)撈取資料，查詢條件為WHERE <column> IS NULL的方法寫法如下。

例如資料表EMPLOYEE現有三筆資料資料。

+----+------+--------+
| ID | NAME |  AGE   |
+----+------+--------+
|  1 | John | 22     |
|  2 | Iris | (null) |
|  3 | Mary | (null) |
+----+------+--------+

對映的Entity類。

Employee

package com.abc.demo.entity;

import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;

@Entity
public class Employee implements Serializable {
    private static final Long serialVersionUID = 1L;

    @Id
    private Long id;

    private String name;

    private Integer age;

    // getters and setters...
}

查詢WHERE AGE IS NULL寫法其實跟查詢有值的條件一樣為findByAge(Integer age)，參數帶入null即可。

EmployeeRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    List<Employee> findByAge(Integer age);

}

產生的JPQL如下。

select 
    employee0_.id as id1_0_, 
    employee0_.age as age2_0_, 
    employee0_.name as name3_0_ 
from employee employee0_ 
where employee0_.age is null;

測試。

EmployeeRepositoryTests

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class EmployeeRepositoryTests {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Test
    void queryEmployeeListWhereAgeIsNull() {
        List<Employee> employeeList = employeeRepository.findByAge(null);
        System.out.println(employeeList); // [Employee(id=2, name=Iris, age=null), Employee(id=3, name=Mary, age=null)]

        Assertions.assertEquals(2, employeeList.size());
    }
    
}

• Spring Data JPA Query Method 方法名稱查詢範例