Spring data JPA existsByField is case insensitive in MySQL, how to make it case sensitive

Question

ClientsRepository class

public interface ClientsRepository extends JpaRepository<ClientsEntity, Long> {

    boolean existsByClientId(String clientId);

}

ClientsEntity class

@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "clients")
public class ClientsEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String clientId;

}

clients table
| id | client_id | |---------------------|------------------| | 1 | ABC | |---------------------|------------------|

When calling existsByClientId("abc") it returns true, how can I force to check case?

Actual result:
existsByClientId("abc") --> true
existsByClientId("ABC") --> true

Expected result:
existsByClientId("abc") --> false
existsByClientId("ABC") --> true

Java version 8
Spring boot version 2.1.2.RELEASE
mysql-connector-java version 5.1.46

If the content is truth, check [this](https://stackoverflow.com/questions/3936967/mysql-case-insensitive-select). — samabcde, Dec 16 '19 at 06:24
@samabcde I have added the actual result, please have a look, and also edit my question. — Another coder, Dec 16 '19 at 06:56
Please check the collation by following ostrokach's answer (link in my previous comment). I suppose the case insensitive problem is due to database instead of the program. — samabcde, Dec 16 '19 at 06:59

samabcde · Answer 1 · 2019-12-16T13:15:10.430

By default, the SQL query derived is case sensitive. However, as said in the document

The method parser supports setting an IgnoreCase flag for individual properties (for example, findByLastnameIgnoreCase(…)) or for all properties of a type that supports ignoring case (usually String instances — for example, findByLastnameAndFirstnameAllIgnoreCase(…)). Whether ignoring cases is supported may vary by store, so consult the relevant sections in the reference documentation for the store-specific query method.

As we are using MySQL database, and the case sensitivity depends on server, database and connection collations, and the default collation(utf8mb4_0900_ai_ci) is case insensitive, making the comparison case insensitive. Creating unexpected result when comparing to other database.

From solution of MySQL case insensitive select, changing the column collation to case sensitive is simplest and no code change is required.

Spring data JPA existsByField is case insensitive in MySQL, how to make it case sensitive

1 Answers1