Pfthb

I have a JAVA POJO class with hibernate annotation for postgresql database.

Now, I have a requirement that we support multiple databases in our application. My question is : Should we use the same class with other databases (Oracle, MySQL, SQL Server) or should I write separate annotated class for each different database ?

Reason: To support special characters we are using database proprietary types instead of hibernate types like

// for oracle
@Column(sql-type="nvarchar2")
private String name;

// for sql server
@Column(sql-type="nvarchar")
private String name;

// hibernate doesn't support different proprietary sql types at same type like this
@Column(sql-type="nvarchar","nvarchar2")
private String name;

If it's ok to use nvarchar for all String typed columns by default, you could extend Oracle and MS SQLServer dialects and do something like this:

public class CustomOracleDialect extends org.hibernate.dialect.Oracle10gDialect 

 @Override
 protected void registerCharacterTypeMappings() 
 super.registerCharacterTypeMappings();
 registerColumnType(Types.VARCHAR, "nvarchar2");
 




public class CustomSQLServerDialect extends org.hibernate.dialect.SQLServer2012Dialect 

 public CustomSQLServerDialect() 
 super();
 registerColumnType(Types.VARCHAR, "nvarchar");
 

Then configure these dialects in dependence to the database type used.

The same exact class WILL work with other DB engines, as far as Hibernate is concerned all it cares about is the dialect for the most part. However, some DB engines don't support the identity generation strategy for ID fields for example (from past experience). Depending on what DB engines you are required to work with you may have to get creative a bit but, for the most part, as long as you don't have any DB-engine-specific code in your entity classes, everything should work just fine. I for example switched a project from HSQLDB to SQLite and the only thing that did not work was the identity generation i mentioned earlier. If i were you i would experiment with different dialects and thoroughly test everything.

EDIT

Just saw your edit and that is definitely engine-specific code. In this case you might indeed need different entities to accommodate the specific data type you want to explicitly assign to each column.

Good luck!