Spring Data repository查询忽略大小写

1. Overview

Spring Data JPA queries, by default, are case-sensitive. In other words, the field value comparisons are case-sensitive.

In this tutorial, we’ll explore how to quickly create a case insensitive query in a Spring Data JPA repository.

2. Dependencies

Firstly, let’s make sure we have Spring Data and H2 database dependencies in our pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <version>2.7.11</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
    <version>2.1.214</version>
</dependency>

The latest versions of these Spring Boot Starter JPA and H2 are available on Maven Central.

3. Initial Setup

Let’s say we have a Passenger entity with id, firstName, and lastName attributes:

@Entity
class Passenger {
 
    @Id
    @GeneratedValue
    @Column(nullable = false)
    private Long id;
 
    @Basic(optional = false)
    @Column(nullable = false)
    private String firstName;
 
    @Basic(optional = false)
    @Column(nullable = false)
    private String lastName;
 
    // constructor, static factory, getters, setters
}

Also, let’s prepare our test class by populating the database with some sample Passenger data:

@DataJpaTest
@RunWith(SpringRunner.class)
public class PassengerRepositoryIntegrationTest {

    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private PassengerRepository repository;

    @Before
    public void before() {
        entityManager.persist(Passenger.from("Jill", "Smith"));
        entityManager.persist(Passenger.from("Eve", "Jackson"));
        entityManager.persist(Passenger.from("Fred", "Bloggs"));
        entityManager.persist(Passenger.from("Ricki", "Bobbie"));
        entityManager.persist(Passenger.from("Siya", "Kolisi"));
    }
    
    //...
}

4. IgnoreCase for Case Insensitive Queries

Now, suppose we want to perform a case-insensitive search to find all passengers with a given firstName.

To do so, we’ll define our PassengerRepository as:

@Repository
public interface PassengerRepository extends JpaRepository<Passenger, Long> {
    List<Passenger> findByFirstNameIgnoreCase(String firstName);
}

Here, the IgnoreCase keyword ensures that the query matches are case insensitive.

We can also test that out with the help of a JUnit test:

@Test
public void givenPassengers_whenMatchingIgnoreCase_thenExpectedReturned() {
    Passenger jill = Passenger.from("Jill", "Smith");
    Passenger eve = Passenger.from("Eve", "Jackson");
    Passenger fred = Passenger.from("Fred", "Bloggs");
    Passenger siya = Passenger.from("Siya", "Kolisi");
    Passenger ricki = Passenger.from("Ricki", "Bobbie");

    List<Passenger> passengers = repository.findByFirstNameIgnoreCase("FrED");

    assertThat(passengers, contains(fred));
    assertThat(passengers, not(contains(eve)));
    assertThat(passengers, not(contains(siya)));
    assertThat(passengers, not(contains(jill)));
    assertThat(passengers, not(contains(ricki)));
}

Despite having passed “FrED” as the argument, our returned list of passengers contains a Passenger with the firstName as “Fred”. Clearly, with the help of the IgnoreCase keyword, we have achieved a case insensitive match.

5. Conclusion

In this quick tutorial, we learned how to create a case insensitive query in a Spring Data repository.

Finally, code examples are available over on GitHub.