I’ve been working on an application using Hibernate on MySQL, which works fine.
I’ve now been asked to port it to SQL Server (no, I don’t know either, but there you go).
I’ve recreated the tables in SQL Server, but ran into problems trying to run my application. It quickly became clear that an hbm.xml file that works on MySQL doesn’t necessarily work on SQL Server.
I went through the Hibernate Wizard in Netbeans to see what Netbeans thought might work, and found a few differences:
(1) the SQL Server version includes a “schema=“dbo”” attribute in the “class” node
(2) for an autoincremented field, where the MySQL version has “generator class=“identity””, the SQL Server version has “generator class=“assigned””
(3) several field types are different, e.g. long instead of java.lang.Long, java.lang.Short instead of java.lang.Byte
However, I thought that one of the big benefits of using Hibernate was that these sort of differences should be transparent to the actual java code? instead, it looks as though I’m going to have to have two different versions of each hbm.xml file, and select the appropriate one depending on which hibernate.dialect I’m using, is that correct?
Sorry of this is a bit of a numpty question, but it kind of undermines one of the reasons I was using Hibernate in the first place! Sorry too if the answer’s obvious - it may be so obvious that it’s why there’s nothing I can easily find that tells me so. Or maybe I was just searching for the wrong thing - I’m not even entirely sure what key words to use in a web search to find what I’m after!
Further to the above, if I needed two versions of each .hbm.xml file, presumably I would also need two versions of each POJO created from the hbm file?
And I would need to take care of the different types mapped by the two databases (or, in each case, find a database field type that maps to the same Java type?)
Hibernate allows you to use database native types and constructs as well, which if you do might hurt portability as you see. I don’t know how your hbm.xml look like, but just because Netbeans suggests to use a different Java type for a column, does not mean that this is incompatible. Sometimes databases just don’t have proper types to represent all sizes that Java supports, in which case Hibernate uses the next best type.
As far as I know, SQL Server supports identity columns, so this should work. Don’t get confused by what Netbeans tells you. Just make sure that you let Hibernate through it’s hbm2ddl script generate the initial schema for SQL Server so that you use the correct definitions.
The schema/catalog is configurable in the hibernate.xml and possibly it’s already enough if you just set the default schema/catalog on the SQL Server datasource.
Thanks. I expect the root cause is the journey by which I arrived at this point - starting from a MySQL table structure (which got amended over time anyway), with Java code running java.sqlPreparedStatements. I then moved over to Hibernate, using Hibernate objects for data insertions, but retaining native SQL for doing queries, especially where I was needing to query across tables or only needing one or two fields from a result set.
Anyway, to answer my original question, I’ve ended up doing something along the lines you suggest:
(1) when using a SQL Server database, set the hibernate.default_schema to dbo (in hibernate.cfg.xml or in code)
(2) keep “identity” in the hbm file
(3) where I was using a SQL “float” type in MySQL, I’ve had to use “real” in SQL Server (“float” in SQL Server maps to double in Java!); further, where using “tinyint(1)” in MySQL for a boolean field, I’ve had to use “bit” in SQL Server (I still need to check whether that maps to Java boolean though, been having problems with my setup anyway just now!); the Short/Byte thing was an error in my manual conversion process!
That way, hopefully I’ll only need the one hbm file, and the one POJO for each table. Having multiple versions of the POJO for each table makes life even worse, it didn’t feel like the right answer at the time, and it certainly isn’t - the code then has to decide which one it should be using, or combine the two somehow, and going down that route lies madness:-)
Hibernate is usually smart enough to figure out the correct column type for you based on the Java type, so maybe you should just remove all the column type definitions in your hbm.xml files.