11

I'm trying to use the JPA EntityManager find() method. My primary key is a string which corresponds to a user's name.

I am using JPA / Hibernate / MYSQL.

My problem is a search for user 'David' matches user 'david' due, I assume, to the case insensitive string matching in the underlying MYSQL. This causes me a whole heap of problems!!

Has anybody got an elegant solution to this? I could do a native SQL call and use the BINARY operator as documented here: http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

Anyone got a better solution? Ta.

Corin Fletcher
  • 1,611
  • 1
  • 17
  • 25
  • ran into this today, it is 2017 and this is not yet fixed. I have 'Ken' in mysql and em.find(class,'ken') returns an object with username 'ken'. which cause other object related to this user throw FK invalid on delete. – Maxi Wu Jan 05 '17 at 08:36

2 Answers2

3

Can you change the column type such that it is case insensitive? The MySQL guide has information on how to do so.

This is more or less what you found with the BINARY operator but it applies to the column type instead of when you run a SELECT

Kevin
  • 30,111
  • 9
  • 76
  • 83
3

Had a similar Problem. You have to set the collation of the table or of the whole database to one which is case-sensitive.

See: Reference Manual 12.1.10 CREATE DATABASE SYNTAX and 9.1.3.2. Database Character Set and Collation

Community
  • 1
  • 1
Turbokiwi
  • 1,288
  • 10
  • 15