CODEWITHSUNDEEP
mysqlspring-bootspring-data-jpa
logi0517
logi0517

Reputation: 845

Spring data JPA with MySQL server: case is ignored on Entity class by default

I have the following entity class:

@Entity
@Table(name = "employee")
public class Employee {

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

  @NotNull
  @NaturalId
  private String tagId;

  @NotNull
  @Size(max = 255, message = "Employee name can not be longer than 255 character")
  private String name;

  @Type(type = "yes_no")
  private boolean isInside;

  @PastOrPresent(message = "Last RFID timestamp can not be in the future")
  private ZonedDateTime lastSwipe;

  //Constuctors, getters and setters
}

With the following JpaRepository:

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

  Optional<Employee> findByTagId(String tagId);

}

Let's say I have an employee in the database, with tagId "SomeStringForID". Right now, if I query the database using the findByTagId method where tagId equals "sOmEStringforid" for example, the employee is found in the database. If I try to save another employee with tagId "sOmEStringforid", I will get an exception, thanks to the @NaturalId annotation.

Any idea what causes this behaviour? Spring named queries have options for IgnoreCase, so I'm pretty sure this should not be the default behaviour. I checked one of my older projects too, where to my surprise I have found the same behaviour. I tried both JDK 8 and 11 versions.

Upvotes: 0

Views: 395

Answers (1)

Rentius2407
Rentius2407

Reputation: 1148

MySQL is not case sensitive. You can query for a record using a value of 'a' and it can return a record 'A' or 'a'.

See the following:

MySQL case insensitive select

MySQL case sensitive query

How can I make SQL case sensitive string comparison on MySQL?

Upvotes: 1

Related Questions