spring和hibernate结合配置两个数据源的问题

Question

我按照spring文档里的方法配置如下：<beans>
  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
          <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
      <property name="url"><value>jdbc:mysql://127.0.0.1:3306/huaian_city</value></property>
      <property name="username"><value>root</value></property>
      <property name="password"><value></value></property>
      <property name="maxActive"><value>100</value></property>
      <property name="maxWait"><value>3000</value></property>
      <property name="maxIdle"><value>20</value></property>
  </bean>  <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName"><value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value></property>
    <property name="url"><value>jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=NANYANGCity;</value></property>
    <property name="username"><value>sa</value></property>
    <property name="password"><value>suncer</value></property>
    <property name="maxActive"><value>100</value></property>
    <property name="maxWait"><value>3000</value></property>
    <property name="maxIdle"><value>20</value></property>
  </bean>  <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="mappingResources">
      <list>
        <value>product.hbm.xml</value>
      </list>
    </property>
    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.MySQLDialect
        hibernate.show_sql=true
      </value>
    </property>
  </bean>  <bean id="sessionFactory2" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource2"/>
    <property name="mappingResources">
      <list>
        <value>inventory.hbm.xml</value>
      </list>
    </property>
    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.SQLServerDialect
      </value>
    </property>
  </bean>
         <bean
        id="defaultLobHandler"
        class="org.springframework.jdbc.support.lob.DefaultLobHandler">
    </bean>
  <bean id="myTxManager" class="org.springframework.transaction.jta.JtaTransactionManager"/>  <bean id="rootdao" class="com.suncer.db.dao.RootDAO">
    <property name="sessionFactory" ref="sessionFactory"/>
  </bean>  <bean id="rootdao2" class="com.suncer.db.dao.RootDAO2">
    <property name="sessionFactory" ref="sessionFactory2"/>
  </bean>  <!-- this shows the Spring 1.x style of declarative transaction configuration -->
  <!-- it is totally supported, 100% legal in Spring 2.x, but see also above for the sleeker, Spring 2.0 style -->
  <bean id="myProductService"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager" ref="myTxManager"/>
    <property name="target">
      <bean class="com.suncer.itf.impl.SuncerImpl">
        <property name="RootDAO" ref="rootdao"/>
        <property name="RoorDAO2" ref="rootdao2"/>
      </bean>
    </property>
    <property name="transactionAttributes">
      <props>        
        <prop key="*">PROPAGATION_SUPPORTS</prop>
      </props>
    </property>
  </bean></beans>    
问题1：
web服务器用的是jboss，在启动的时候报如下错误：
    org.springframework.transaction.TransactionSystemException: JTA UserTransaction is not available at JNDI location [java:comp/UserTransaction]; nested exception is javax.naming.NameNotFoundException: UserTransaction not bound javax.naming.NameNotFoundException: UserTransaction not bound
问题2：当我把有关事务的配置都去掉：
<bean id="myTxManager" class="org.springframework.transaction.jta.JtaTransactionManager"/>
<bean id="myProductService"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager" ref="myTxManager"/>
    <property name="target">
      <bean class="com.suncer.itf.impl.SuncerImpl">
        <property name="RootDAO" ref="rootdao"/>
        <property name="RoorDAO2" ref="rootdao2"/>
      </bean>
    </property>
    <property name="transactionAttributes">
      <props>        
        <prop key="*">PROPAGATION_SUPPORTS</prop>
      </props>
    </property>
  </bean>
  
可以启动了，不过在运行过程中，发现点击操作第二个数据源即sqlserver数据库的页面之后后台会报：unclosed connection ，forgot call close()  on your session?
而且如果点太快会打不开连接：Cannot get a connection, pool exhausted.
求正确的配置方法，高手帮忙啊。 

Answer 1

此回复为自动发出,仅用于显示而已，并无任何其他特殊作用
楼主【wowtianwen】截止到2008-07-22 11:04:08的历史汇总数据(不包括此帖)：
发帖的总数量:13                       发帖的总分数:370                      每贴平均分数:28                       
回帖的总数量:12                       得分贴总数量:0                        回帖的得分率:0%                       
结贴的总数量:12                       结贴的总分数:290                      
无满意结贴数:4                        无满意结贴分:160                      
未结的帖子数:1                        未结的总分数:80                       
结贴的百分比:92.31 %               结分的百分比:78.38 %                  
无满意结贴率:33.33 %               无满意结分率:55.17 %                  
值得尊敬

Answer 2

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/kydata?useUnicode=true&amp;characterEncoding=GBK</value>
</property>
<property name="username">
<value>root</value>
</property>
<property name="password">
<value>123456</value>
</property>
</bean>
<bean id="dataSource2000"
class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<property name="driverClassName">
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</property>
<property name="url">
<value>jdbc:microsoft:sqlserver://192.168.1.24:1433</value>
</property>
<property name="username">
<value>xxxz</value>
</property>
<property name="password">
<value>xxxxx</value>
</property>
</bean>
<!-- 数据库B的数据连接池 -->
<bean id="dataSource2000B" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
<property name="url" value="jdbc:microsoft:sqlserver://192.168.1.24:1433"/>
<property name="username" value="xxxx"/>
<property name="password" value="xxxxxx"/>
</bean><bean id="sessionFactorymysql"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.MySQLDialect
</prop>
<prop key="hibernate.show_sql">true</prop>

</props>
</property>
<property name="mappingResources">
<list>
<value>com/hibernate/vo/bddata.hbm.xml</value>

</list>
</property></bean>
<bean id="sessionFactorysql2000"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource2000" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.connection.autocommit">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>com/ky/hibernate/othervo/PqmDfindIndex.hbm.xml</value>
</list>
</property>
</bean> 
<!-- 数据C -->
<bean id="sessionFactorysql2000"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource2000" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.connection.autocommit">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>com/hibernate/othervo/PqmDfindIndex.hbm.xml</value>
</list>
</property>
</bean> 
<!-- 数据B -->
<bean id="sessionFactorysql2000B"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource">
<ref bean="dataSource2000B" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.connection.autocommit">true</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>com/hibernate/othervo/FileInfoEvent.hbm.xml</value>
</list>
</property>
</bean> 这是我配置三个数据源的代码 LZ试试看吧 我可以正常运行

Answer 3

楼上这么配置，应该是在业务类里面只调用的是一个数据库的连接吧，否则的话是跨数据库的事务这个要有jta来做，而用jta的话，需要有xdatasource来支持，像楼上这样的datasource是不支持的

Answer 4

如果不设置jta的话，你用两个数据库也只是两个独立的数据库而已，这样可以这么改一下，但是在业务层似乎只能得到一个transferManager，也就是这样<beans>
  <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
          <property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
      <property name="url"><value>jdbc:mysql://127.0.0.1:3306/huaian_city</value></property>
      <property name="username"><value>root</value></property>
      <property name="password"><value></value></property>
      <property name="maxActive"><value>100</value></property>
      <property name="maxWait"><value>3000</value></property>
      <property name="maxIdle"><value>20</value></property>
  </bean>  <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName"><value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value></property>
    <property name="url"><value>jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=NANYANGCity;</value></property>
    <property name="username"><value>sa</value></property>
    <property name="password"><value>suncer</value></property>
    <property name="maxActive"><value>100</value></property>
    <property name="maxWait"><value>3000</value></property>
    <property name="maxIdle"><value>20</value></property>
  </bean>  <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="mappingResources">
      <list>
        <value>product.hbm.xml</value>
      </list>
    </property>
    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.MySQLDialect
        hibernate.show_sql=true
      </value>
    </property>
  </bean>  <bean id="sessionFactory2" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource2"/>
    <property name="mappingResources">
      <list>
        <value>inventory.hbm.xml</value>
      </list>
    </property>
    <property name="hibernateProperties">
      <value>
        hibernate.dialect=org.hibernate.dialect.SQLServerDialect
      </value>
    </property>
  </bean>
  <bean
        id="defaultLobHandler"
        class="org.springframework.jdbc.support.lob.DefaultLobHandler">
    </bean>
  
  <bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
  </bean>
  <bean id="transactionManager2"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory2" />
  </bean>
  <bean id="rootdao" class="com.suncer.db.dao.RootDAO">
    <property name="sessionFactory" ref="sessionFactory"/>
  </bean>
  <!-- this shows the Spring 1.x style of declarative transaction configuration -->
  <!-- it is totally supported, 100% legal in Spring 2.x, but see also above for the sleeker, Spring 2.0 style -->
  <bean id="myProductService"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager" ref="transactionManager"/>
    <property name="target">
      <bean class="com.suncer.itf.impl.SuncerImpl">
        <property name="RootDAO" ref="rootdao"/>
      </bean>
    </property>
    <property name="transactionAttributes">
      <props>        
        <prop key="*">PROPAGATION_SUPPORTS</prop>
      </props>
    </property>
  </bean></beans>myProductService中配置transferManager的时候只能配置一个，不像jta可以指定出去所以在service中就能有一个sessionFactory

Answer 5

还有一段代码<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactorymysql" />
</property>
</bean>
<bean id="hibernateTemplate2000" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactorysql2000"/>
</property>
</bean>
<bean id="hibernateTemplate2000B" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="sessionFactorysql2000B"/>
</property>
</bean>

Answer 6

所有的事务都交给hibernateTemplate来处理了

Answer 7

6楼的朋友，我按照你说得改了下，
可以运行的时候好多:unclosed connection ，forgot call close()  on your session? 提示