This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Change databases at runtime? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Change databases at runtime?" Watch "Change databases at runtime?" New topic
Author

Change databases at runtime?

Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1064
We have a publishing system where we have two databases where only one of them is current at any one moment.

Then, after we update data, we publish it to the "offline" database and then we switch the databases making the freshly updated offline database current and online.

My question is how to do DB switch programmatically. We're using Spring. Note, however, that a question like the one I'm asking here got the poster (on the Spring forum) to come to the JDBC forum instead to ask the question.

Just wondering what approaches might work best for this DB switch.

mike

fred rosenberger
lowercase baba
Bartender

Joined: Oct 02, 2003
Posts: 11257
    
  16

I don't know a thing about JDBC, but here's my 2-cents...

I would think you'd need some kind of publisher-subscriber. Your current app connects to a DB. It needs to know when it should switch, so something has to tell it.

When it gets the notification to switch, it can gracefully disconnect from the 'A' instance and connect to the 'B' (or vice versa).

I know that's kind of vague, but i'm hoping that this may spark more of a discussion.


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2347
    
  28

You need to use AbstractRoutingDataSource provided by Spring. Look at this blog that does something very similar to what you want to do.

The 2 minute explanation of this is that there is a context that is tied to a thread local variable. The AbstractRoutingDataSource encapsualtes a map of real data sources. It looks at the TL context to determine which datasource to route too. So, whenever he switches the context all consecutive calls go to the data source that is tied to that context.

The sample on the blog might make it clearer that my very short explanation, because it has real example. GO through the example carefully.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1064
Jayesh A Lalwani wrote:You need to use AbstractRoutingDataSource provided by Spring. Look at this blog that does something very similar to what you want to do.

The 2 minute explanation of this is that there is a context that is tied to a thread local variable. The AbstractRoutingDataSource encapsualtes a map of real data sources. It looks at the TL context to determine which datasource to route too. So, whenever he switches the context all consecutive calls go to the data source that is tied to that context.

The sample on the blog might make it clearer that my very short explanation, because it has real example. GO through the example carefully.


Thanks to both Bartenders for their input.

I had found this blog posting, but will now consider it more seriously based on the reply here.

My posting here was to help confirm I wasn't overlooking a simpler approach.

Thanks again!

-mike
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2347
    
  28

If you are already under spring and using standard Spring mechanisms to build your DAO code, then this is the approach is the simplest and best. Since Spring is managing your database connection, you need to tell Spring to switch the underlying DB connection, and this is the best way of doing it

If you were doing something non-standard, other approaches might be better. It's hard to say without looking at what exactly you are doing

Note that there is an inherent problem with how Spring models the DAO support that it assumes that you prefer to have ORM; ie; each DAO queries database and returns objects, and from the application layer you modify the objects and return them back to DAO. Although, this is good for most webapps, where the number of objects loaded at a time tend to be small, you will run into trouble if you have lot of data. If you have like 1 billion rows that you want to "publish", you are just not going to have enough memory to load all these objects into memory. You might have to think about batching your publishing, and/or using native database utilities rather than do it in Java, or investigating in using a a full fledged ETL utility. I'm getting a little ahead of your question here, and this might not be applicable to your case. Just trying to make sure that your approach might have potential pitfalls.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1064
Jayesh A Lalwani wrote:If you are already under spring and using standard Spring mechanisms to build your DAO code, then this is the approach is the simplest and best. Since Spring is managing your database connection, you need to tell Spring to switch the underlying DB connection, and this is the best way of doing it

If you were doing something non-standard, other approaches might be better. It's hard to say without looking at what exactly you are doing

Note that there is an inherent problem with how Spring models the DAO support that it assumes that you prefer to have ORM; ie; each DAO queries database and returns objects, and from the application layer you modify the objects and return them back to DAO. Although, this is good for most webapps, where the number of objects loaded at a time tend to be small, you will run into trouble if you have lot of data. If you have like 1 billion rows that you want to "publish", you are just not going to have enough memory to load all these objects into memory. You might have to think about batching your publishing, and/or using native database utilities rather than do it in Java, or investigating in using a a full fledged ETL utility. I'm getting a little ahead of your question here, and this might not be applicable to your case. Just trying to make sure that your approach might have potential pitfalls.



Great info.

I think all we're trying to do is switch the databases within Spring. We have about 100 tables, some with millions of rows. However, we are using JDBCTemplate, not ORM if that simplifies things. If Spring/JDBC isn't the optimal way to go, then we'll pick something else. This switch doesn't have to be done in Java/Spring, but it would be nice to be able to do it there.

We would update the offline database while the other one was online (not using Java for this update), then just need to programmtically switch to the freshly updated database. Updating the offline database would be done by sysadmins using other tools.

Our actual Java apps are using C3PO connection pooling for the application.

Would you still suggest the AbstractRoutingDataSource route or would doing this another way make more sense based on this additional detail?

I appreciate in advance your reply.

-mike
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2347
    
  28

Ahh ok.. got it. So, the "publishing" itself is outside of your application. You just need to switch when the publishing is done. Got it. Apologies if I missed it earlier


Yes, AbstractRoutingDataSource is perfect for you. However, note that there might be a slight hiccups during the switch

a) You will have to ensure that the data in your original database is "read only" during the publishing. Because, when the switch occurs, and if from the perspective of the user, some data is lost, it would be very weird.
b) Depending on how the AbstractRoutingDataSource is implemented, you will have some connections going to the old database and some connections going to the new database at the same time. You won't be able to get the old database offline immediately after the switch occurs. For example, if your AbstractRoutingDataSOurce is deciding to switch based on a TL variable, and the TL variable is initialized at the start over every HTTP request, then at the moment the switch occurs, the HTTP requests still being serviced will still open connections to old database. OTH, if the the TL variable is initialized from something that is stored in the HTTP session, then the app will be opening connections to the old database as long as the sessions are alive

For the most part, you still want to do switches during periods of low activity, even though you would design it to be seamless to the user.
Mike London
Ranch Hand

Joined: Jul 12, 2002
Posts: 1064
Jayesh A Lalwani wrote:Ahh ok.. got it. So, the "publishing" itself is outside of your application. You just need to switch when the publishing is done. Got it. Apologies if I missed it earlier


Yes, AbstractRoutingDataSource is perfect for you. However, note that there might be a slight hiccups during the switch

a) You will have to ensure that the data in your original database is "read only" during the publishing. Because, when the switch occurs, and if from the perspective of the user, some data is lost, it would be very weird.
b) Depending on how the AbstractRoutingDataSource is implemented, you will have some connections going to the old database and some connections going to the new database at the same time. You won't be able to get the old database offline immediately after the switch occurs. For example, if your AbstractRoutingDataSOurce is deciding to switch based on a TL variable, and the TL variable is initialized at the start over every HTTP request, then at the moment the switch occurs, the HTTP requests still being serviced will still open connections to old database. OTH, if the the TL variable is initialized from something that is stored in the HTTP session, then the app will be opening connections to the old database as long as the sessions are alive

For the most part, you still want to do switches during periods of low activity, even though you would design it to be seamless to the user.


Thanks very much. I'll report back on how this solution works for us.

Appreciate, very much, your great and extremely useful replies.

-mike
 
jQuery in Action, 2nd edition
 
subject: Change databases at runtime?