Hibernate or iBatis or something else?

Question

In my project i need to switch between databases during runtime. I tried to use Hibernate, but stuck in a place, where i need to map object with table in database. The problem is, that i have several tables with prefix: documents2001, documents2002 ... As i understood, i can't map class with table during runtime. I tried using iBatis, but the problem is in database changing during runtime. In iBatis, it is quite hard to do.\

Maybe some advices, what should i use?

My requirements:

• Ability to connect to different databases during runtime
• Ability to change table during runtime(if class is mapped to table, like it is in Hibernate).

UPDATE: Ok, i`ll try to explain:
I have to write application, which can connect to different databases during runtime. User of app can choose, which database to connect. All databases are with same structure. Additional to this, user can switch between tables in database. Tables are with the same structure.

• Why i assumed, i can not use Hibernate: In Hibernate class is mapped with table, so i can not change table during runtime. This does not allow me to choose table, to which i can connect.
• Why i assumed, i can not use iBATIS. In iBATIS it is very hard to connect to different database during runtime. So, user will not be able to connect to different database during runtime.

Maybe there is another tool i can use?

Answer 1

Do the tables have the same structure, just different names?

This is definitely possible to map in iBATIS.

You would just need to include something like this in your SQL Map:

<select id="selectAll" resultMap="result" parameterClass="myParameterClass">
    SELECT ColumnA, ColumnB FROM $tableName$
</select>

Assuming that I have understood your question correctly, that should solve your problem.

Answer 2

I don't fully understand your requirement, but this is a possibility:

Both Hibernate and iBatis can work from a DataSource, so you could create your own implementation of javax.sql.DataSource that returns different connections each time getConnection() is called.

Using a DataSource in Hibernate

Answer 3

Working with dynamic table names is trivial in Ibatis. Just use expressions like:

SELECT * FROM $tableName$

where tableName is a property on the parameter class.

Working with dynamic table names in Hibernate (or any JPA provider) is extraordinarily difficult if not impractical (or even impossible). This question has come up before. See JPA: How do I specify the table name corresponding to a class at runtime?.

Working with dynamic data sources in Ibatis will require you to write some code but not all that much. Basically, Ibatis works around the concept of a sqlMapClient, which has a data source and a list of queries that it can run. Just create one sqlMapClient for each database, each with a different data source, and have them include all the same query files (in the sql map config).

A DAO can be written such that it has multiple sqlMapClients injected and it selects which one to use at runtime. This is the part you'll have to write yourself but it's straightforward.

This is predicated on knowing the databases at compile-time. If the database isn't known until runtime then that's a little harder. It might still be possible but I'm not sure how Ibatis will react if you basically swap data sources at runtime from the sqlMapClient. It might work, it might blow up. You'll have to try it and see.

Hibernate may work here too along the same principle: you inject multiple persistence units into a DAO and use the right one at runtime. With Hibernate (or any JPA provider) you will have to watch out for making sure your managed objects are stored in the correct persistence unit. I can easily see this turning into a nightmare actually.

One general comment: it's not advised to go down the path of dynamic table names or databases so really consider what you're doing and why and ask yourself if this could be remedied by making some better design choices.

Answer 4

Hi, this is great and working for me. But I need to use the same logic for stored procedures too and it is not working. The DB is Oracle. It gives the standard error:

Check the parameter mapping for the 'king' property.
--- Cause: java.sql.SQLException: Invalid column index>

Pls help!