• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Change databases at runtime?

 
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
lowercase baba
Posts: 13089
67
Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1971
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
There are 10 kinds of people in this world. Those that understand binary get this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic