master/slave db access with hibernate sessionfactory/transaction support?

[olegula]

Hi,

I am relatively new to Hibernate, could anyone please share the experience of configuring a master/slave db access with hibernate/transaction management?
Here is my problem, i have 2 datasources configured one for the master db another for slave. Then I have 2 sessionFactories pointing to corresponding datasources. Plus I have OpenSessionInViewFilter with singleSession and a HibernateTransactionManager.
My problem is that I have no idea how to inject 2 sessionFactories into the HibernateTransactionManager and then based on the operation(select/update) have my dao's receive/or ask for either a slave datasource(readOnly) or a master datasource.
Has anyone solved that kind of config issue?

Here is my config so far:

Spring config:

<!-- master -->
  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="url" value="${hibernate.master.connection.url}"/>
    <property name="driverClassName" value="${hibernate.connection.driver_class}"/>
    <property name="username" value="${hibernate.connection.username}"/>
    <property name="password" value="${hibernate.connection.password}"/>
    <property name="initialSize" value="4"/>
    <!-- property name="defaultAutoCommit" value="false"/-->
    
  </bean>

<bean id="readOnlyDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="url" value="${hibernate.slave.connection.url}"/>
    <property name="driverClassName" value="${hibernate.connection.driver_class}"/>
    <property name="username" value="${hibernate.connection.username}"/>
    <property name="password" value="${hibernate.connection.password}"/>
    <property name="initialSize" value="4"/>
    <!-- property name="defaultAutoCommit" value="false"/-->
    
  </bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:/hibernate.cfg.xml"/>
    <property name="hibernateProperties">
     <props>
    		<!-- prop key="hibernate.connection.autocommit">true</prop--> 
    		<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
    		<!-- prop key="hibernate.current_session_context_class">thread</prop-->
    		<prop key="hibernate.show_sql">true</prop>
			<!-- prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop -->
	    </props>  
	    
	</property>
  
  </bean>
  

<bean id="readOnlySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="readOnlyDataSource"/>
    <property name="configLocation" value="classpath:/hibernate.cfg.xml"/>
    <property name="hibernateProperties">
     <props>
    		<!-- prop key="hibernate.connection.autocommit">true</prop--> 
    		<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
    		<!-- prop key="hibernate.current_session_context_class">thread</prop-->
    		<prop key="hibernate.show_sql">true</prop>
			<!-- prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop -->
	    </props>  
	    
	</property>
  
  </bean>


<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory"/>
  </bean>


<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
		<property name="transactionManager"><ref local="transactionManager"/></property>
		
		<property name="transactionAttributes">
		
		<props>
			<prop key="*">PROPAGATION_REQUIRED</prop>
			<!-- prop key="get*">PROPAGATION_SUPPORTS,readOnly</prop-->
			
			<!--prop key="get*">PROPAGATION_REQUIRED,readOnly</prop>
	        <prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
	        <prop key="load*">PROPAGATION_REQUIRED,readOnly</prop>
	        <prop key="store*">PROPAGATION_REQUIRED</prop-->
			
		</props>
		</property>
	</bean>

<bean id="userDao" parent="baseTransactionProxy">
    <property name="target">
    	<bean class="com.xyz.myapp.persistence.hibernate.UserDaoHibernateImpl" >
    		 <property name="sessionFactory"><ref bean="sessionFactory"/></property> 
    		    		
   		</bean>
    </property>
  </bean>

Thank you in advance,

[olegula]

I think I found a solution using mysql Replication Driver. all transactions that are set to readonly in TransactionProxyFactoryBean config will automatically be sent to slaves.
If anyone has a better solution, I'd appreciate you sharing.

Thanks,