2

The database for one the application that I am working on is not confirmed yet by the business.

  • Best guess is Oracle and DB2.
  • What I've heard is initially the project will go live with DB2 V9 and then to Oracle 11g.

We are using Spring 3.0.5, Hibernate 3.5, JPA2 and JBoss 5 for this project

So what are the best practices here going into the build phase and test phase?

  1. Shall I build using DB2 first and worry about Oracle later (this doesn't sound right)?
  2. Or, shall I write using JPA (Hibernate) and then generate the database schema?
  3. Or something else?

PS: I've no control over the choice of the DB, what and when, as these are strategic decision made by people sitting in nice rooms getting fat cheques and big bonuses.

Thanks, Adi

adi
  • 1,711
  • 3
  • 29
  • 50
  • What kind of project is this? "go live with DB2 V9 and then to Oracle 11g" sounds like your people don't know what they want. –  Jan 31 '12 at 11:24
  • Web application it is .. and I said it is a management decision purely based by savings.. there is nothing more I can say. – adi Jan 31 '12 at 11:27

4 Answers4

4

Obviously you are loosing the access to specific features of the database if you are writing your application database agnostic. The database is, except for automatic optimizations done by JPA and Hibernate, reduced to common features. You have to set some things to automatic and trust JPA/Hibernate to do it right that you could set specifically if you knew the database (e.g. id generator strategies).

But it seems that the specific developer features of the database are not relevant for the decision so they can't be relevant to the application. What other reasons may influence the decision (like price, money, cash, personal relations, management tools, hardware requirements, existing knowledge and personell) can only be speculated about.

So IMHO you don't have a choice. Strictly avoid anything database specific. That includes letting the JPA/Hibernate generate the schema (your point #2). In this project setup you shouldn't tinker with the database manually.

Well... sadly there ARE some hidden traps in JPA/Hibernate developement that make it database dependent (e.g. logarithmic functions are not mapped consistenly). So you should run all your tests against all possible databases from day one. As you write "Best guess is..." you should just grab any database available and test against it. Should be easly setup with the given stack.

And you should try to accelerate the decision about the database used, if possible.

Hauke Ingmar Schmidt
  • 11,559
  • 1
  • 42
  • 50
  • 1
    Suppose we are testing this application using multiple databases.. how can we test in a CI environment (say Jenkins). Could Spring be configured to test against all databases.. one after another? – adi Jan 31 '12 at 12:20
  • 1
    If you are talking about unit testing then you would write one test setup / datasource per database and inject it into the class to test or the test application context. You still would have one test per database but with the different setups. Those test would run sequential as normal. – Hauke Ingmar Schmidt Jan 31 '12 at 12:48
2

Just "write using JPA (Hibernate)" develop it to be de database agnostic. Put all you business logic in java code not stored procedures.

If you are using spring you don't need jboss you could use just tomcat, about a quarter of the foot print, and much simpler imho.

Spring vs Jboss and jboss represents all that is bad, while spring represents all that is good in Java enterprise development

Community
  • 1
  • 1
NimChimpsky
  • 46,453
  • 60
  • 198
  • 311
0

We have add this issue and had to migrate late in the project, leading to a lot of extra works, frustrations and delays.

My advise is to define an abstract layer. Go to the point you may have a data model without any database, say with tables or text files.

Then when you have to switch to some database, you can optimize for it, while staying free to continue application development on any already developped model. So you don't delay the developpers on the app while one is tuning the DB2 layer. When everything is duly validated, the team can switch on it.

lalebarde
  • 1,684
  • 1
  • 21
  • 36
0

I will disagree with the currently accepted answer suggesting avoiding database specific things. From a performance perspective, that would be a pity, and it's definitely doable.

JPA/Hibernate and also jOOQ can abstract over a lot of things and if you're using the query builder APIs of either technology (criteria query in JPA, or jOOQ for more advanced SQL), you can get very far in a vendor agnostic way without removing all the vendor specific stuff. For example, you can easily create a vendor specific predicate like this:

.where(oracle ? oracleCondition() : db2Condition())

What you should do from the very beginning of such a project, once you know you'll have to support both dialects is to run integration tests on both database products. For this, I recommend testcontainers, which makes running such tests quite simple. If you have to add support for another dialect, and if you're using one of the above abstractions, you can simply add another testcontainers configuration, check if your application still works, tweak 2-3 things, and you're set.

Disclaimer: I work for the company behind jOOQ.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509