How to Configure and Use Multiple Databases in Spring

[Jan-Martijn]

Hi everyone,

i've build a webportal with the help of Springframework. The persistency of the entities is saved in DB 1, but my applicatie has to communicate to a second database. Some tables in this database need to mapped to entities.

The application-context.xml is shown below, in witch i configured database access to DB 1.

Can someone tel me how to configured it so, that i can easely use a second database?

	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="jpaVendorAdapter">
			<bean
				class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="true" />
				<property name="generateDdl" value="true" />
				<property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>			
			</bean>			
		</property>
		<property name="loadTimeWeaver">
			<bean class="org.springframework.instrument.classloading.ReflectiveLoadTimeWeaver" />
		</property>
	</bean>


	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/webloket_dji" />
		<property name="username" value="webloket" />
		<property name="password" value="webloket" />
	</bean>

	<bean id="transactionManager"
		class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" 
			ref="entityManagerFactory" />
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
		<property name="transactionManager" ref="transactionManager"/>
		<property name="transactionAttributes">
			<props>
				<prop key="save*">PROPAGATION_REQUIRED</prop>
				<prop key="update*">PROPAGATION_REQUIRED</prop>
				<prop key="delete*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>

when i need to query the entities, i extend "JpaDaoSupport" en use:

 getJpaTemplate()

i hope someone has some useful tips

kind regards,

Jan-Martijn

[Bjoern]

just create that whole stuff a second time. (datasource, and so on)

Since I'm not using the JPA templates but the hibernate templates instead, I don't know what's the exanct thing that you inject to your DAOs/repositorys (when extending JpaDaoSupport), but there will be something (I think it should be the entityManagerFactory).

For access to the other database simply inject the newly created one.

PS: sorry for terrible language but i'm too lazy to rewrite it atm. If it's not understandable, let me know. In that case, I'll edit it later

[Jan-Martijn]

just create that whole stuff a second time. (datasource, and so on)

Since I'm not using the JPA templates but the hibernate templates instead, I don't know what's the exanct thing that you inject to your DAOs/repositorys (when extending JpaDaoSupport), but there will be something (I think it should be the entityManagerFactory).

For access to the other database simply inject the newly created one.

PS: sorry for terrible language but i'm too lazy to rewrite it atm. If it's not understandable, let me know. In that case, I'll edit it later

Hey Bjoern,

Thnx for your reply, do you mean something like this (ofcourse than whit the correct database data)?

 	<!-- DATABASE FOR WEBLOKET -->
	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="jpaVendorAdapter">
			<bean
				class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="true" />
				<property name="generateDdl" value="true" />
				<property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>			
			</bean>			
		</property>
		<property name="loadTimeWeaver">
			<bean class="org.springframework.instrument.classloading.ReflectiveLoadTimeWeaver" />
		</property>
	</bean>


	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/webloket_dji" />
		<property name="username" value="webloket" />
		<property name="password" value="webloket" />
	</bean>

 	<!-- DATABASE FOR MYACCOUNT -->
	<bean id="entityManagerFactory"
		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="jpaVendorAdapter">
			<bean
				class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="true" />
				<property name="generateDdl" value="true" />
				<property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>			
			</bean>			
		</property>
		<property name="loadTimeWeaver">
			<bean class="org.springframework.instrument.classloading.ReflectiveLoadTimeWeaver" />
		</property>
	</bean>


	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/webloket_dji" />
		<property name="username" value="webloket" />
		<property name="password" value="webloket" />
	</bean>
	<!-- end... -->

	<bean id="transactionManager"
		class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" 
			ref="entityManagerFactory" />
		<property name="dataSource" ref="dataSource" />
	</bean>
	
	<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
		<property name="transactionManager" ref="transactionManager"/>
		<property name="transactionAttributes">
			<props>
				<prop key="save*">PROPAGATION_REQUIRED</prop>
				<prop key="update*">PROPAGATION_REQUIRED</prop>
				<prop key="delete*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>

[Bjoern]

yes, kind of.
You would have to use different ids for the different beans. for example:

<bean id="entityManagerFactoryWebLocket" [...]
<property name="dataSource" ref="dataSourceWebLocket" /> [...]

<bean id="dataSourceWebLocket" [...]

and for the other DB:

<bean id="entityManagerFactoryMyAccount" [...]
<property name="dataSource" ref="dataSourceMyAccount" /> [...]

<bean id="dataSourceMyAccount" [...]

and so on. A problem is that you will also have to define two transactionManagers, which should be no problem with any classical apporach but might be troublesome if you try to use the @Transactional annotation.

[Marten Deinum]

A problem is that you will also have to define two transactionManagers, which should be no problem with any classical apporach but might be troublesome if you try to use the @Transactional annotation.

It depends... If you have processes spanning multiple database you need a JTA transaction manager and can do with 1 transactionmanager.

[Jan-Martijn]

It depends... If you have processes spanning multiple database you need a JTA transaction manager and can do with 1 transactionmanager.

I understand that the config shown above will work in my case, but how do I tell spring in witch database he can find specific entities. The current application is using the JpaTemplate.

[Bjoern]

Create different DAOs/Repositories for the different databases. especially when you look at them in the sense of repositories, that they are two different places where you can put your data to be stored or get stored data from.

I can only imagine scenarios where it's good to have an own repository for each db.

For queries that need data from both databases:
These queries are something your application does. Sounds like domain logic to me, so I would put it in the right domain service (or maybe entity) where it is needed.

Like this you'll just have two Repositories/Daos with differently configured entityManagerFactorys injected. You can access both databases just as you wish. If there are references to be set between entities from one database to enities from the other, i think this can't be done automatically by hibernate (please correct me if i'm wrong)

But if you need them there are plenty of workarounds, I think. I just did something like this. I'm not sure if what I did is really "good". That's why I won't post it in detail. (What I did was injecting the repository to my entities by using an aspect with after advice on the constructor call. Finally in teh getter for the reference I checked if the reference is null and in that case made the call to teh repository. Pretty much some kind of self-made lazy loading)

If this is exactly what you need, feel free to ask for more. I'll post it then and maybe let someone with more experience correct me, if necessary

[Jan-Martijn]

Create different DAOs/Repositories for the different databases. especially when you look at them in the sense of repositories, that they are two different places where you can put your data to be stored or get stored data from.

I can only imagine scenarios where it's good to have an own repository for each db.

For queries that need data from both databases:
These queries are something your application does. Sounds like domain logic to me, so I would put it in the right domain service (or maybe entity) where it is needed.

Like this you'll just have two Repositories/Daos with differently configured entityManagerFactorys injected. You can access both databases just as you wish. If there are references to be set between entities from one database to enities from the other, i think this can't be done automatically by hibernate (please correct me if i'm wrong)

But if you need them there are plenty of workarounds, I think. I just did something like this. I'm not sure if what I did is really "good". That's why I won't post it in detail. (What I did was injecting the repository to my entities by using an aspect with after advice on the constructor call. Finally in teh getter for the reference I checked if the reference is null and in that case made the call to teh repository. Pretty much some kind of self-made lazy loading)

If this is exactly what you need, feel free to ask for more. I'll post it then and maybe let someone with more experience correct me, if necessary

Hey Bjorn,

I think that's exactly what i need. I loved to see how you solved this problem. So if you could post a bit of that code I would be very greatful

I haven't a time to solve this, because i ran in another problem with hibernate wich needs solving first. I can't update collections in a entity. Very frustrating because i've tried every posibility i could think of (cascade annotation, implementing the equals and hashcode methods). So i'm also standing open for solution for this 'small' problem.

Thnx a lot!

Jan-Martijn

[asavasamuel]

Bjoern


Here is an ORM that works with MySQL
https://www.kellermansoftware.com/p-...ess-layer.aspx