The project I'm on is looking at 're-architecting' a portion of our code, and we're looking at several options - Hibernate is one of them - but none of us know that much about it right now
Basically, we have 'our' database, but every so often we 'dump' data from other databases to update the information in ours. We start out by basically doing a direct copy of the data in the original format to some temporary 'dump' tables in our database. We then manipulate this data in different ways to match up with the format of corresponding data in our database - this isn't necessarily a 1-1 row conversion - some conversions take values from multiple rows in multiple tables to create a new row in one of our tables. Once we get the data representation in the format we want in our database, we interact with it through EJBs.
Right now, we're using a home-grown architecture that makes a lot of use of 'hand-rolled' JDBC statements. We're trying to think of alternatives to this, because it's a pain to develop and maintain all these JDBC statements.
We know we don't want to use EJBs to do this, because the 'dump' tables and objects are short lived, and we don't think we need all the 'extras' that using EJB entails.
So, is Hibernate a good choice worth looking into? Can it make a short connection to the database and pull the 'dump' tables we need, or does it maintain a connection back to the database the entire time we are making queries to it? Also, not necessarily required, but a plus - can Hibernate do the manipulations on the data from the original database to represent it more like the data we need to write to our database? Or is the representation that Hibernate provides very close to the way the tables are laid out in the originating database?
Write once, run anywhere, because there's nowhere to hide! - /. A.C.
From a database standpoint, you are coding your own ETL tool. ETL, Extract, transform and load. When there are great database tools out there already for ETL.
It does depend on you database, but my preferred and fastest way is through SQL. So for instance you dump the data into tables, like you do, then using SQL directly in the database you transform the data and insert into your real tables.
I just think there are better and faster tools than coding Java to do ETL.