How can I order JPA to set a MySQL database column with text content as case sensitive by default upon creation?
A:
MySQL varchar type is not case-sensitive. What you want is varbinary.
CREATE TABLE IF NOT EXISTS `stuff` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varbinary(255) NOT NULL -- varbinary to be case sensitive
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Chadwick
2010-07-01 19:04:33
It's not strictly true that `varchar` is case insensitive - it depends on the table collation. For example, `utf8_unicode_cs` is case sensitive, whereas `utf8_unicode_cs` is not.
Mike
2010-07-01 19:20:07
But how can I have JPA do set this automatically upon automatic schema creation? I don't want to manipulate the generated schema by hand every time I use my program on a different computer or database instance.
ali
2010-07-01 19:23:46
I don't know anything about JPA, but I have just found [Character Encoding UTF-8 with JPA/Hibernate, MySql and Tomcat](http://mathiasrichter.blogspot.com/2009/10/character-encoding-utf-8-with.html). It may be of some use.
Mike
2010-07-01 19:30:03
+1
A:
The @Column annotation on your field can specify a columnDefinition attribute which may allow you to specify a case-sensitive collation for the column.
public abstract String columnDefinition(Optional) The SQL fragment that is used when generating the DDL for the column.
Defaults to the generated SQL to create a column of the inferred type.
Default:
""
In your case, for example, using the @Column annotation, you would use
@Column(name = "NAME_COL", columnDefinition = "VARCHAR(250) COLLATE latin1_general_cs") private String name;
Fly
2010-07-01 19:31:07