JPA EntityManager find with case sensitive key

Question

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.

Answer 1

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

Answer 2

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