Hello dear Hibernate Community
Currently, an application (JavaEE) is connected to an application server (JBoss EAS), this via Hibernate (ORM) with an MS-SQL database (RDBMS), which has productive data and historical data. Historical data is almost never used; but must be available. By contrast, productive data is the data that is used for everyday use. Our data is classified in two different categories. There are data that stand alone (without relationship) and there are data that have a relationship to other objects. For example (relationship: position to item) there is a shelf with different positions (shelf space), which can be occupied by items.
The shelf must be part of the productive database, as it is constantly being worked on. If an item is on the shelf, but you do not need it, you could transfer it to an archive database (which does not exist yet). However, the relation to the position on the shelf must not be lost. Otherwise, the item was archived, but you will no longer have the position to this item and thus do not know where it is stored on the shelf.
I am going to integrate a second database (slave) on the same server (archive_db) in which the historical data is outsourced. So the productive database (master) is going to be held lean and the performance is going to improve.
My idea was to keep a single connection to the masterDB and to use a Trigger which is going to manage the data. This trigger is going to shift the data to the slaveDB or not.
e.g.: If someone want to update a reccord in the database (not knowing in which database master or slave the data are held) with use of Hibernate a trigger on the databasesite is going to kick in which is going to activate a stored procedure which is going to update the right reccord in the right database.
I think that there must be people somewhere who came across the same problem or people with the knowledge to validate my solution, or maybe open my eyes for another (better) way.
Thank you for taking the time and I am looking forward
regards Leobart
Since you already want to use triggers, you don’t need to do anything in Hibernate.
As for keeping a single connection, that’s not possible. The follower node needs it’s own DB connection, but you can route that from your application using a JDBC proxy.
Thanks for the answer vlad.
At the moment they have a connection to one db. I thought that there would be a way to integrate another db without changing the structure they are using currently. So they would make their usual CRUD commands to the Master_database and in the background we will have triggers which shift data from Master_db to slave_db in case of archiving or gets data from both databases if needed.
e.g. anyone want all person data. So they will ask the master_db to get all persons (they do not need to know that there is a slave_db (archive)). And then they will get all person data which comes from the Master_db and from the slave_db.
Using triggers was my solution to realize it (not 100% knowing if it works or if its a performant way). But mb there is also another way to do it. I guess there must be a dozen of companies who are using Hibernate on a RDBMS with more Databases who struggled with the same problem.
As said we are using one DB, but my goal is to add another Slave_Db. That it looks like in the picture in the appendix (one pictures = 1000 words ). Users should act the same way as before, where they only had one database and my solution is going to pass the right data from both databases
The Primary and the Follower nodes are deployed as separate processes and have their own IP addresses.
Now, database replication is for redundancy and fault tolerance, not for archiving. In your case, you probably need to use 2 schemas:
dbo - is the primary schema
archive - is the second schema
This way, you have a single DB server with 2 schemas and you can use a single DB connection. Then just use the fully-qualified object paths (e.g. Database.Schema.Table) to reference tables from one or both schemas.
Wow, your answers are pretty fast Vlad. Thank you!
The question is, how to get data from several schemas but only requesting one schma. My idea was to use trigger which will contain the logic to get Data from several schemas and return them. (Mb there is an other way?)
Maybe with an example my question gets bit more clear (sorry for my bad english in advance)
e.g. user selects all persondata from dbo (user doesnt know “archive”) and gets back persondata from dbo and archive (e.g union). Now he wants to update persondata from “Tom”. The thing is that he doesnt know that “Tom’s” data is lying in the archive. He sends now thru the Java Applikation (using Hibernate/Jboss) a statement to dbo to update “Tom’s” reccord. Now the trigger should kick in and update it in the archive
Use stored procedures for CRUD and configure them using @Loader, @SQLInsert, @SQLUpdate, and @SQLDelete. Check out this example for an example of how to use @SQLDelete to change the DELETE operation into an UPDATE.
The stored procedures will contain the logic of using the two schemas behind the schens.
). Users should act the same way as before, where they only had one database and my solution is going to pass the right data from both databases