create two connectors to DB using hibernate

Question

I have an application based on Spring and hibernate.

My task is to create two connectors in this application to database - one connector is only to READ and second is to READ, WRITE etc.

How my configuration should looks like.

Now in WEB-INF folder i have 3 files:

hibernate-context:

    <?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns="http://www.springframework.org/schema/beans" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:p="http://www.springframework.org/schema/p" 
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="
            http://www.springframework.org/schema/beans 
            http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
            http://www.springframework.org/schema/tx 
            http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context-3.0.xsd
            ">

    <context:property-placeholder location="/WEB-INF/spring.properties" />

    <!-- Enable annotation style of managing transactions -->
    <tx:annotation-driven transaction-manager="transactionManager" />   

    <!-- Declare the Hibernate SessionFactory for retrieving Hibernate sessions -->
    <!-- See http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/orm/hibernate3/annotation/AnnotationSessionFactoryBean.html -->                           
    <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/SessionFactory.html -->
    <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/Session.html -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
                 p:dataSource-ref="dataSource"
                 p:configLocation="${hibernate.config}"
                 p:packagesToScan="com.esb.scs"/>

    <!-- Declare a datasource that has pooling capabilities-->   
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                destroy-method="close"
                p:driverClass="${app.jdbc.driverClassName}"
                p:jdbcUrl="${app.jdbc.url}"
                p:user="${app.jdbc.username}"
                p:password="${app.jdbc.password}"
                p:acquireIncrement="5"
                p:idleConnectionTestPeriod="60"
                p:maxPoolSize="100"
                p:maxStatements="50"
                p:minPoolSize="10" />

    <!-- Declare a transaction manager-->
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" 
                p:sessionFactory-ref="sessionFactory" />



</beans>

hibernate.cfg.xml:

  <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="show_sql">true</property>
    <property name="hbm2ddl.auto">update</property>
  </session-factory>
</hibernate-configuration>

spring.properties:

   # database properties
app.jdbc.driverClassName=com.mysql.jdbc.Driver
app.jdbc.url=jdbc:mysql://localhost/database
app.jdbc.username=user
app.jdbc.password=password

#hibernate properties
hibernate.config=/WEB-INF/hibernate.cfg.xml

how can i create two connections to database in one application?

My question is becouse i have two databases with replication, where one is only for read and the secound is for write...

With your help i've created file like this:

<?xml version="1.0" encoding="UTF-8"?>
<beans  xmlns="http://www.springframework.org/schema/beans" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:p="http://www.springframework.org/schema/p" 
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="
            http://www.springframework.org/schema/beans 
            http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
            http://www.springframework.org/schema/tx 
            http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context-3.0.xsd
            ">

    <context:property-placeholder location="/WEB-INF/spring.properties" />

    <!-- Enable annotation style of managing transactions -->
    <tx:annotation-driven transaction-manager="transactionManager" />   
    <tx:annotation-driven transaction-manager="transactionManagerr" />  

    <!-- Declare the Hibernate SessionFactory for retrieving Hibernate sessions -->
    <!-- See http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/orm/hibernate3/annotation/AnnotationSessionFactoryBean.html -->                           
    <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/SessionFactory.html -->
    <!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/Session.html -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
                 p:dataSource-ref="dataSource"
                 p:configLocation="${hibernate.config}"
                 p:packagesToScan="com.esb.scs"/>

    <!-- Declare a datasource that has pooling capabilities-->   
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                destroy-method="close"
                p:driverClass="${app.jdbc.driverClassName}"
                p:jdbcUrl="${app.jdbc.url}"
                p:user="${app.jdbc.username}"
                p:password="${app.jdbc.password}"
                p:acquireIncrement="5"
                p:idleConnectionTestPeriod="60"
                p:maxPoolSize="100"
                p:maxStatements="50"
                p:minPoolSize="10" />

    <!-- Declare a transaction manager-->
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" 
                p:sessionFactory-ref="sessionFactory" />



    <bean id="sessionFactoryr" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
                 p:dataSource-ref="dataSourcer"
                 p:configLocation="${hibernate.config}"
                 p:packagesToScan="com.esb.scs"/>

    <!-- Declare a datasource that has pooling capabilities-->   
    <bean id="dataSourcer" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                destroy-method="close"
                p:driverClass="${app.jdbc.driverClassName}"
                p:jdbcUrl="${app.jdbc.url}"
                p:user="${appr.jdbc.username}"
                p:password="${appr.jdbc.password}"
                p:acquireIncrement="5"
                p:idleConnectionTestPeriod="60"
                p:maxPoolSize="100"
                p:maxStatements="50"
                p:minPoolSize="10" />

    <!-- Declare a transaction manager-->
    <bean id="transactionManagerr" class="org.springframework.orm.hibernate3.HibernateTransactionManager" 
                p:sessionFactory-ref="sessionFactoryr" />



</beans>

and now when i have in service :

@Resource(name="sessionFactory")
    private SessionFactory sessionFactory;

    @Resource(name="sessionFactoryr")
    private SessionFactory sessionFactoryr;

and when i trying to make query with sessionFactoryr

i get Error:

No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here

but i have

<filter>
        <filter-name>hibernateFilter</filter-name>
        <filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class>
      </filter>
      <filter-mapping>
        <filter-name>hibernateFilter</filter-name>
        <url-pattern>/*</url-pattern>
      </filter-mapping>

in web.xml

Answer 1

To set up two transaction managers, just declare them in your app context, I use a properties file and read in connection details. I set what type of access hibernate has in that prop file (and also restrict permissions on the db server) :

<bean id="sessionFactoryOne"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
    p:dataSource-ref="dataSource" p:configLocation="WEB-INF/classes/hibernate.cfg.xml"
    p:packagesToScan="com.mycompany" />

    <bean id="dataSourceONE" class="com.mchange.v2.c3p0.ComboPooledDataSource"
    destroy-method="close" p:driverClass="${app.jdbc.driverClassName}"
    p:jdbcUrl="${app.jdbc.url}" p:user="${app.jdbc.username}" p:password="${app.jdbc.password}"
    p:acquireIncrement="5" p:idleConnectionTestPeriod="60" p:maxPoolSize="10"
    p:maxStatements="50" p:minPoolSize="10" />

    <!-- Declare a transaction manager-->
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager" 
        p:sessionFactory-ref="sessionFactory">   

<bean id="sessionFactoryTWO" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
             p:dataSource-ref="dataSourceTWO"
             p:configLocation="WEB-INF/classes/hibernateTWO.cfg.xml"
             p:packagesToScan="com.mycompany"/>

<!-- Declare a datasource that has pooling capabilities-->   
<bean id="dataSourceTWO" class="com.mchange.v2.c3p0.ComboPooledDataSource"
            destroy-method="close"
            p:driverClass="${two.jdbc.driverClassName}"
            p:jdbcUrl="${two.jdbc.url}"
            p:user="${two.jdbc.username}"
            p:password="${two.jdbc.password}"
            p:acquireIncrement="2"
            p:idleConnectionTestPeriod="60"
            p:maxPoolSize="5"
            p:maxStatements="50"
            p:minPoolSize="1" />

<!-- Declare a second transaction manager-->
<bean id="transactionManagerTWO" class="org.springframework.orm.hibernate4.HibernateTransactionManager" 
        p:sessionFactory-ref="sessionFactoryTWO">   
        <qualifier value="Traveller"/>              

and then you can reference them like in yr service layer like so :

@Resource(name = "sessionFactoryTWO")
private SessionFactory sessionFactoryTWO;

@Resource(name = "sessionFactory")
private SessionFactory sessionFactory;

then for methods in your service layer you will have to make methods transactional :

@Transactional(readOnly = true)
public void myReadOnlyMethod(Whatever whatever)

@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public void myWriteMethod(Whatever whatever)