Multiple Databases Configuration

[Deluxouss]

Hi there,

I'm new with spring/hibernate and I'm starting a new project using those 2 great technologies.

My project is a bit complicated as it uses 2 (actually 3) database connections...

1 - Read only - localhost
2 - Update - Company's server in House
3 - Pending Queries System - this is an extra db to make sure we never loose info between 1 and 2. So if the company's internet is down we don't have any loss of data as they are always "cached" in this numero 3 db.

Anyway I m not here to talk about my architecture that I won't really be able to change, but to know HOW to do it with Spring/Hibernate.

I already create severals SessionFactories, DataSources, and transactionManagers.
apparently i have to use aop but i m not really sure if it is the best way

This is my config for aop

	<aop:config>
		<aop:pointcut id="defaultServiceOperation"
			expression="execution(* com.companyname.dao.*Impl.*(..))" />
		
		<aop:advisor pointcut-ref="defaultServiceOperation"
			advice-ref="defaultTxAdvice" />
		
		<!--
			<aop:pointcut id="noTxServiceOperation" expression="execution(*
			com.companyname.dao.*DF.*(..))" />

			<aop:advisor pointcut-ref="noTxServiceOperation"
			advice-ref="noTxAdvice" />
		-->

	</aop:config>

	<!-- ADVICE -->

	<tx:advice id="defaultTxAdvice" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="get*" read-only="true" />
			<tx:method name="*" />
		</tx:attributes>
	</tx:advice>

	<tx:advice id="noTxAdvice" transaction-manager="noTxManager">
		<tx:attributes>
			<tx:method name="*" propagation="NEVER" />
		</tx:attributes>
	</tx:advice>

Could anyone just help me first to make a project with 2 databases Slave/Master like?
Or does anyone has any ideas how to do my 3 db project?

[Marten Deinum]

Instead of trying to complicate things with multiplate tx manager,s essionfactories and what not, why not simply turn to your datasource/database provider. I know that mysql supports configuring a readonly db and update/insert one. IMHO you should fix that on the database level and it should be transparant for your application.

[Deluxouss]

That's exactly the kind of answers I didn't want to receive.
My project architecture is much more complicated than 2 dbs slave/master like.
ANd I really need to learn how to configure my project with severals databases.

Is it that complicated? I try to proove my boss that Spring/Hibernate is the way to go, so please help me promoting Spring!

Thank you

[Marten Deinum]

As stated I believe that that configuration at least the readonly/write configuration belongs at your datasource, that shouldn't be a burden of your application. But as that is IMHO of course and I'm probably wrong (from your pov).

I nowhere stated that you should abondon your architecture (multiple databases!) but apparently that is what you think.

I also strongly suggest instead of using multiple tx managers to use a JTA one with XA capable datasources, that already reduces your aop config. If you really need multiple tx managers you can do it but that can be hard to get working 100%.

IMHO it is easier to use annotations (@Transactional) to specify your transactions in this case instead of writing large portions of xml. Regarding the sessionfactories well you simple have to inject them all into the dao and use the one depending on your action (get/update/insert). There is no way spring can help you there, at least not out of the box.

I would really strive for easier configuration (which will make your full application easier btw). So what I probably would do, if you couldn't do it with the database, is to create a DataSource proxy which handles all the complexities (goto x for readonly, goto y for insert and do a delta to z) that way you would still have 1 datasource (for your applications perspective) 1 tx manager and 1 sessionfactory. Which all in all is easier, less resources and you need to implement the logic only once.

You could use a TargetSource for that, we did something similair to switch dynamically for different users, however you could do something like this to switch/route to the correct database. You might also want to take a look at the AbstractRoutingDataSource shipping with spring, which might be an easier fit.

[Deluxouss]

I totally agree with you Marten, annotations are great! I much rather use them than a huge xml file. ANd I agree that having severals sessionfactories and transaction manager doesn't seem the best way.

I don't really get what you mean here

I also strongly suggest instead of using multiple tx managers to use a JTA one with XA capable datasources, that already reduces your aop config.

What do you mean by XA capable datasources?

Last Question: Do you think I can overwrite the saveOrUpdate methods?

As my Object should have 2 save methods (one in the master db, one in the "pending request" db)..

Thanks for your time I appreciate