How to transfer data from one database to another with Hibernate?

Question

I have an application A with a domain-model which is mapped to a database using Hibernate. I have another application B that uses exactly the same domain-model-classes as A and adds some additional classes.

My goal is to read data from database A in application B and transfer that data into the database of B (to make a copy of it). In addition, some the domain-classes of B have associations (OneToOne) to domain-classes of A (but in the database of B, of course).

What's the best strategy to accomplish this? I thought of two session factories and using Session.replicate() (how does that work?). Or should I better introduce an additional mapping layer between these two domain-models for loose coupling?

Answer 1

What is the purpose of the copying? Is that part of your application flow or logic? or just straight data copying?

If it is just for the sake of copying data over, there is no need to use hibernate. There are plenty of tools for it.

Answer 2

I've done this before to transfer data between two different database types (in my case DB2 and MS SQL Server). What I did was to create two separate session factories, and give both of them the same list of mapping files. Then I simply read records from one, and saved them to the other.

Of course, this assumed that both data sources were identical.

Answer 3

Hey McLaird or anyone that can help me, I have to databases that have the exact same schema, one in MySQl and the other one in Oracle, and I want to copy all the data from the MySQL one to the Oracle Database, so far i have both mapping files and a session for both database, how did you read the records from one and saved them into the other?

Answer 4

Like others have pointed out, I think we need to know exactly what it is you're trying to accomplish. If you're doing a one time migration, there are better tools out there than Hibernate to do ETL (Extract, Transform, Load).

If you really insist on doing this in Hibernate (this applies to you also, Daniel), I'd do something like:

1. Open session to database A.
2. Read all entities of the type you're trying to copy (make sure lazy loading is disabled)
3. Open session to database B.
4. Save or update the entities.

I'd do this in a separate tool, rather than in application A or B.

On the other hand, if this is part of the functionality of your applications (e.g., application A is the admin console to the data, while application B consumes the data), you may want to do things a little differently. It's hard to say without knowing what exactly you're looking for.

Finally, something to look into (I don't think this is what you're looking for, but maybe it'll help you look at your problem in a different way) is Hibernate Shards (http://shards.hibernate.org/).

Answer 5

Hey thank you very much for your answer... actually hibernate Shards looks really interesting. What I am doing is the following, so there is a webapplicatoin using a MySql database with a given schema X (I have the hibernate mapping filesand the schema is always the same) and the goal of my application is to change the data from MySQl into lets say oracle or hsql. I have worked with hibernate and i decided to use hibernate given the database independance feature, but it is a bit too much of a big load anyways I always get that mysql doesnt support a join of more than 61 tables or that there is no more java heap space. The application is supposed to be used just one time whenever the user wants to switch from one db to another (mysql <->oracle <->hsql) and unfortunately writing a script is always complicated, and foreign constrains are killing me too... if you know of any good data migration framework or any suggestion please do let me know! i would be really gratefull :D and thanks for the quick answer!

Answer 6

Hi, I also have an ApplicationA and another ApplicationB. What I want to do is Transfer some data from the database of ApplicationA (not all, the data that the user selects to transfer) to the database of ApplicationB. ApplicationB consists of the similar database schema and some additional entities. How would I be able to accomplish this? pls help! thnx