CODEWITHSUNDEEP
javamysqlhibernatejpaspring-data
Byron Voorbach
Byron Voorbach

Reputation: 4485

Issue with uppercase and lowercase using MySql Springdata and Hibernate

I have a database which contains an Assignment table. The table looks like this:

ID | CHARACTER_ASS | TYPE_ASS

Inside the character column letters and numbers are stored. Ie. 'A', 'a', "Aa". "AA", 1 etc... Now when i execute the following query in my query console:

select * from assignment a where a.CHARACTER_ASS = 'A'

It will return the lowercase entry in my database and the uppercase entry as well. Now I've been looking for an answer to this question and someone told me that this might because I'm running on a Windows OS. I've looked this up and could only find that there could be a problem with naming table names with/without lowercase (?).

One of the other problems is that I'm using spring data. In spring data I don't have direct control of the query so I'm wondering if there is a specific fix for spring data.

I'd like to hear your answers regarding this issue :)

Thanks in advance

EDIT

My SHOW CREATE TABLE assignment:

CREATE TABLE `assignment` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CHARACTER_ASS` varchar(45) NOT NULL,
  `TYPE_ASS` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

EDIT2

I changed my Assignment table Collation to: latin1_general_cs and I tried querying manually in the MySql query editor, but I still get A and a back as result. Then i tried to adjust the collation of the table to the same value as I did with the table. Am i doing something wrong or should the adjustment work?

EDIT3

Ok I just fixxed it. Instead of using the latin1_general_cs I used utf8_bin. Seems that utf8 doesn't have a Case Sensitive Collation :) Thank you JB Nizet for pointing me in the right direction!

Upvotes: 1

Views: 2966

Answers (2)

JB Nizet
JB Nizet

Reputation: 691695

Specify a case-sensitive collation when you create the table:

CREATE TABLE assignment (
    ID int(11) NOT NULL AUTO_INCREMENT,
    CHARACTER_ASS varchar(45) NOT NULL,
    TYPE_ASS int(11) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8, COLLATE=latin1_general_cs

Note that the default collation can also be set globally, at the database level: http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

Upvotes: 2

Nedret Recep
Nedret Recep

Reputation: 728

What is the character set and collation for that field?

could you execute SHOW CREATE TABLE tablename;

Upvotes: 0

Related Questions