JPA Hibernate - Multiple Database Dialects and nvarchar(length) data type

Question

I have to do a project using JPA + Hibernate in which I'm using 3 dialects: MySQL5InnoDBDialect, MSSQL2012Dialect and Oracle12cDialect.

Right now I have a specification which is telling me that for some column from:

Oracle database, I have to use NVARCHAR2(LENGTH) data type
MySql database, I have to use VARCHAR(LENGTH) data type
MSSQL database, I have to use NVARCHAR(LENGTH) data type

... and here is my problem..

If I use:

@Column(name="columnName" length = 255) private String columnName; hibernate generates varchar(255) and this is good just for MySQL
If I use:

@Column(name="columnName", columnDefinition="nvarchar2(255)") private String columnName; it's not possible in MySQL, i get error because of columnDefinition, but in oracle is okay

I tried to customize MySQL dialect creating

public class CustomMySQL5InnoDBDialect extends MySQL5InnoDBDialect{

public CustomMySQL5InnoDBDialect() {
    super();
    registerColumnType(Types.NVARCHAR, "nvarchar2($l)");//$l not $1
    registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName());
}

}

and giving this class in hibernate configuration for MySQL dialect. I have the same problem in MySQL if I'm using columnDefinition property.

Can you help with this problem please?

Unknown Unknown · Accepted Answer · 2019-01-25T16:12:31

The solution is to make use of the feature that the JPA API spec provides you with for just this situation. Define a file orm.xml for each datastore that you need to support, and enable the requisite one when using each database. See this link for details of the file format. That way you don't need to think about hacking the internal features of whichever JPA provider you are using, and you also retain JPA provider portability, as well as database portability

The idea of putting schema specific information info (static) Java annotations is an odd one, even more so when wanting database portability.

JPA Hibernate - Multiple Database Dialects and nvarchar(length) data type

1 Answers