use of Spring/Hibernate with two database user ??

[blackhawk]

Hello,

I'm trying to use Spring framework with Hibernate. Up until now I am using the HibernateTemplate to call Hibernate methods, and this works perfectly. I need now to have two user of oracle on one database,
when guest access my site, I use the low privilege user to access database,
when Administrator access my site, I use the hight privilege user to access database.

How could I use HibernateTemplate then? could i use a sessionfactory and create sessions using different connections?
thanks a lot

[kbaum]

Can you define two session factories, each pointing to a different data source with a different user name defined?

[blackhawk]

Can you define two session factories, each pointing to a different data source with a different user name defined?

How to use with the DAO???each dao set two sessionFactory???

How to use with eht transactionManager ???

[blackhawk]

Can you define two session factories, each pointing to a different data source with a different user name defined?

How to change my applicationContext.xml file???
my applicationContext.xml is below:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

<bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName"><value>oracle.jdbc.driver.O racleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@192.168.1.2:15 21:orcl</value></property>
<property name="username"><value>user1</value></property>
<property name="password"><value>123456</value></property>
</bean>

<bean id="dataSource2" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName"><value>oracle.jdbc.driver.O racleDriver</value></property>
<property name="url"><value>jdbc:oracle:thin:@192.168.1.2:15 21:orcl</value></property>
<property name="username"><value>user2</value></property>
<property name="password"><value>123456</value></property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSess ionFactoryBean">
<property name="dataSource"><ref local="dataSource1"/></property>
<property name="mappingResources">
<list>
<value>org/appfuse/model/User.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">net.sf.hibernate.dialect.H SQLDialect</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>

<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.orm.hibernate.Hibernate TransactionManager">
<property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>

<bean id="userDAO" class="org.appfuse.dao.hibernate.UserDAOHibernate" >
<property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>

<bean id="userManagerTarget" class="org.appfuse.service.impl.UserManagerImpl">
<property name="userDAO"><ref local="userDAO"/></property>
</bean>

<bean id="userManager"
class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager"><ref local="transactionManager"/></property>
<property name="target"><ref local="userManagerTarget"/></property>
<property name="transactionAttributes">
<props>
<prop key="save*">PROPAGATION_REQUIRED</prop>
<prop key="remove*">PROPAGATION_REQUIRED</prop>
<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>

</beans>

[kbaum]

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http&#58;//www.springframework.org/dtd/spring-beans.dtd">

<beans>

<bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc&#58;oracle&#58;thin&#58;@192.168.1.2&#58;1521&#58;orcl</value></property>
    <property name="username"><value>user1</value></property>
    <property name="password"><value>123456</value></property>
</bean>

<bean id="dataSource2" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc&#58;oracle&#58;thin&#58;@192.168.1.2&#58;1521&#58;orcl</value></property>
    <property name="username"><value>user2</value></property>
    <property name="password"><value>123456</value></property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
    <property name="dataSource"><ref local="dataSource1"/></property>
    <property name="mappingResources">
        <list>
            <value>org/appfuse/model/User.hbm.xml</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory2" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
    <property name="dataSource"><ref local="dataSource2"/></property>
    <property name="mappingResources">
        <list>
            <value>org/appfuse/model/User.hbm.xml</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

<!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
<bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
    <property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>


<bean id="transactionManager2" class="org.springframework.orm.hibernate.HibernateTransactionManager">
    <property name="sessionFactory2"><ref local="sessionFactory"/></property>
</bean>

<bean id="userDAO" class="org.appfuse.dao.hibernate.UserDAOHibernate">
     <property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>


<bean id="userDAO2" class="org.appfuse.dao.hibernate.UserDAOHibernate">
     <property name="sessionFactory"><ref local="sessionFactory2"/></property>
</bean>

<bean id="userManagerTarget" class="org.appfuse.service.impl.UserManagerImpl">
    <property name="userDAO"><ref local="userDAO"/></property>
</bean>


<bean id="userManagerTarget2" class="org.appfuse.service.impl.UserManagerImpl">
    <property name="userDAO"><ref local="userDAO2"/></property>
</bean>

<bean id="userManager"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager"><ref local="transactionManager"/></property>
    <property name="target"><ref local="userManagerTarget"/></property>
    <property name="transactionAttributes">
    <props>
        <prop key="save*">PROPAGATION_REQUIRED</prop>
        <prop key="remove*">PROPAGATION_REQUIRED</prop>
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>
</bean>

<bean id="userManager2"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager2"><ref local="transactionManager"/></property>
    <property name="target"><ref local="userManagerTarget"/></property>
    <property name="transactionAttributes">
    <props>
        <prop key="save*">PROPAGATION_REQUIRED</prop>
        <prop key="remove*">PROPAGATION_REQUIRED</prop>
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>
</bean>

</beans>

Much of this could be factored out using abstract parent bean definitions, especially with the TransactionProxyFactoryBean datasource definitions.

http://static.springframework.org/sp...an-definitions

[blackhawk]

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http&#58;//www.springframework.org/dtd/spring-beans.dtd">

<beans>

<bean id="dataSource1" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc&#58;oracle&#58;thin&#58;@192.168.1.2&#58;1521&#58;orcl</value></property>
    <property name="username"><value>user1</value></property>
    <property name="password"><value>123456</value></property>
</bean>

<bean id="dataSource2" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
    <property name="url"><value>jdbc&#58;oracle&#58;thin&#58;@192.168.1.2&#58;1521&#58;orcl</value></property>
    <property name="username"><value>user2</value></property>
    <property name="password"><value>123456</value></property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
    <property name="dataSource"><ref local="dataSource1"/></property>
    <property name="mappingResources">
        <list>
            <value>org/appfuse/model/User.hbm.xml</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

<!-- Hibernate SessionFactory -->
<bean id="sessionFactory2" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
    <property name="dataSource"><ref local="dataSource2"/></property>
    <property name="mappingResources">
        <list>
            <value>org/appfuse/model/User.hbm.xml</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.HSQLDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

<!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
<bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
    <property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>


<bean id="transactionManager2" class="org.springframework.orm.hibernate.HibernateTransactionManager">
    <property name="sessionFactory2"><ref local="sessionFactory"/></property>
</bean>

<bean id="userDAO" class="org.appfuse.dao.hibernate.UserDAOHibernate">
     <property name="sessionFactory"><ref local="sessionFactory"/></property>
</bean>


<bean id="userDAO2" class="org.appfuse.dao.hibernate.UserDAOHibernate">
     <property name="sessionFactory"><ref local="sessionFactory2"/></property>
</bean>

<bean id="userManagerTarget" class="org.appfuse.service.impl.UserManagerImpl">
    <property name="userDAO"><ref local="userDAO"/></property>
</bean>


<bean id="userManagerTarget2" class="org.appfuse.service.impl.UserManagerImpl">
    <property name="userDAO"><ref local="userDAO2"/></property>
</bean>

<bean id="userManager"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager"><ref local="transactionManager"/></property>
    <property name="target"><ref local="userManagerTarget"/></property>
    <property name="transactionAttributes">
    <props>
        <prop key="save*">PROPAGATION_REQUIRED</prop>
        <prop key="remove*">PROPAGATION_REQUIRED</prop>
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>
</bean>

<bean id="userManager2"
class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager2"><ref local="transactionManager"/></property>
    <property name="target"><ref local="userManagerTarget"/></property>
    <property name="transactionAttributes">
    <props>
        <prop key="save*">PROPAGATION_REQUIRED</prop>
        <prop key="remove*">PROPAGATION_REQUIRED</prop>
        <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
    </props>
</property>
</bean>

</beans>

Much of this could be factored out using abstract parent bean definitions, especially with the TransactionProxyFactoryBean datasource definitions.

http://static.springframework.org/sp...an-definitions

If acts according to making which you said,
It's too much complex, too much code should be involved,
Is there a easy way???

[kbaum]

If I extended parent bean definitions, I could have:

<beans>
    
    <bean id="dataSource1"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>oracle.jdbc.driver.OracleDriver</value></property>
        <property name="url">
            <value>jdbc&#58;oracle&#58;thin&#58;@192.168.1.2&#58;1521&#58;orcl</value>
        </property>
        <property name="username"><value>user1</value></property>
        <property name="password"><value>123456</value></property>
    </bean>
    
    <bean id="dataSource2" parent="dataSource1">
        <property name="username" value="user2"/>
        <property name="password" value="123456"/>
    </bean>
    
    <!-- Hibernate SessionFactory -->
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
        <property name="dataSource"><ref local="dataSource1"/></property>
        <property name="mappingResources">
            <list>
                <value>org/appfuse/model/User.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">
                    net.sf.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
    </bean>
    
    <!-- Hibernate SessionFactory -->
    <bean id="sessionFactory2" parent="sessionFactory">
        <property name="dataSource" ref="dataSource2"/>
    </bean>
    
    
    <!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
    <bean id="transactionManager"
        class="org.springframework.orm.hibernate.HibernateTransactionManager">
        <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>
    
    <bean id="transactionManager2"
        class="org.springframework.orm.hibernate.HibernateTransactionManager">
        <property name="sessionFactory"><ref local="sessionFactory2"/></property>
    </bean>
    
    <bean id="userDAO" class="org.appfuse.dao.hibernate.UserDAOHibernate">
        <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>
    
    <bean id="userDAO2" class="org.appfuse.dao.hibernate.UserDAOHibernate">
        <property name="sessionFactory"><ref local="sessionFactory2"/></property>
    </bean>
    
    <bean id="userManagerTarget" class="org.appfuse.service.impl.UserManagerImpl">
        <property name="userDAO"><ref local="userDAO"/></property>
    </bean>
    
    <bean id="userManagerTarget2"
        class="org.appfuse.service.impl.UserManagerImpl">
        <property name="userDAO"><ref local="userDAO2"/></property>
    </bean>
    
    <bean id="userManager"
        class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
        <property name="transactionManager"><ref local="transactionManager"/>
            </property>
        <property name="target"><ref local="userManagerTarget"/></property>
        <property name="transactionAttributes">
            <props>
                <prop key="save*">PROPAGATION_REQUIRED</prop>
                <prop key="remove*">PROPAGATION_REQUIRED</prop>
                <prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
            </props>
        </property>
    </bean>
    
    <bean id="userManager2" parent="userManager">
        <property name="transactionManager" ref="transactionManager2"/>
        <property name="target" ref="userManagerTarget2"/>
    </bean>
    
</beans>

[blackhawk]

To kbaum: Thank you very much!!!
It's a good idea.

but can I change the user name at runtime??
I only want to change the user name,
Is there any way to get and change the database user name at runtime ?

[kbaum]

I guess there could be a way, but I wouldn't recommend doing it. The data source is a shared singleton object. It's not thread safe to change it's state.

[blackhawk]

who have other solution?