在spring中配置多数据库读取
来源:未知 责任编辑:智问网络 发表时间:2013-11-04 19:54 点击:次
在应对大量用户读取的系统中,对数据库的操作通常采用读写分离方式,往一个数据库写入,然后通过复制将数据同步到另外的多个数据库中,读操作都从这些数据库中操作,在采用spring来配置多数据库时,并不能直接支持从多个DataSource中获得数据库连接,为此需要开发一个DataSource的代理,代理实现javax.sql.DataSource接口。该代理根据一定的策略从已有的多个DataSource中选择一个,提供给SessionFactory,供数据访问层使用。原理如下图所示:
DataSource Proxy选择DataSource的方式可以根据实际进行设计,这里为简单,就采用随机方式选择一个。源代码如下:
[java] view plaincopy
package code;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Random;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class RandomDataSource implements DataSource {
<span style="white-space:pre"> </span>private List<DataSource> dataSourcePool ;
<span style="white-space:pre"> </span>protected Log log = LogFactory.getLog(getClass());
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>private static ThreadLocal<DataSource> dsHolder = new ThreadLocal<DataSource>();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 从已有DataSource中随机选择一个
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private DataSource randomDs(){
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>int size = dataSourcePool.size();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Random r= new Random();
<span style="white-space:pre"> </span>int t = r.nextInt(size);
<span style="white-space:pre"> </span>dsHolder.set(dataSourcePool.get(t));
<span style="white-space:pre"> </span>return dsHolder.get();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public Connection getConnection() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Connection conn = randomDs().getConnection();
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public Connection getConnection(String username, String password)
<span style="white-space:pre"> </span>throws SQLException {
<span style="white-space:pre"> </span>Connection conn = randomDs().getConnection(username, password);
<span style="white-space:pre"> </span>log.info("conn URL---->"+conn.getMetaData().getURL());
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public PrintWriter getLogWriter() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().getLogWriter();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public int getLoginTimeout() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().getLoginTimeout();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public void setLogWriter(PrintWriter out) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>dsHolder.get().setLogWriter(out);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public void setLoginTimeout(int seconds) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>dsHolder.get().setLoginTimeout(seconds);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public boolean isWrapperFor(Class<?> iface) throws SQLException {
<span style="white-space:pre"> </span>// TODO Auto-generated method stub
<span style="white-space:pre"> </span>return dsHolder.get().isWrapperFor(iface);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public <T> T unwrap(Class<T> iface) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().unwrap(iface);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public List<DataSource> getDataSourcePool() {
<span style="white-space:pre"> </span>return dataSourcePool;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setDataSourcePool(List<DataSource> dataSourcePool) {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>this.dataSourcePool = dataSourcePool;
<span style="white-space:pre"> </span>}
}
为了可用性,实际生产环境中需要判断取到的DataSource对应的数据库是否可访问,若不能访问,则要取其他可用的DataSource。
在springframework中的配置如下:
<span style="white-space:pre"></span>
<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:context="http://www.springframework.org/schema/context"
xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
<bean id="dataSource" class="code.RandomDataSource">
<property name="dataSourcePool">
<list>
<ref bean="dataSource1" />
<ref bean="dataSource2" />
</list>
</property>
</bean>
<bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://192.168.2.192/ebook"/>
<property name="user" value="test"/>
<property name="password" value="test"/>
</bean>
<bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://192.168.2.105/ebook"/>
<property name="user" value="test"/>
<property name="password" value="test"/>
</bean>
<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
</props>
</property>
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"
p:sessionFactory-ref="sessionFactory"/>
</beans>
这里连接的是MySQL数据库,如果是其他数据库,需要将DataSource1和DataSource2中的参数以及
<span style="font-size:18px;">hibernate.dialect<span style="font-family: Arial, Verdana, sans-serif; white-space: normal; background-color: rgb(255, 255, 255); ">做相应修改。</span></span>
<span style="font-family: Arial, Verdana, sans-serif; white-space: normal; background-color: rgb(255, 255, 255); "><span style="font-size:18px;">
</span></span>
DataSource Proxy选择DataSource的方式可以根据实际进行设计,这里为简单,就采用随机方式选择一个。源代码如下:
[java] view plaincopy
package code;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Random;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class RandomDataSource implements DataSource {
<span style="white-space:pre"> </span>private List<DataSource> dataSourcePool ;
<span style="white-space:pre"> </span>protected Log log = LogFactory.getLog(getClass());
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>private static ThreadLocal<DataSource> dsHolder = new ThreadLocal<DataSource>();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 从已有DataSource中随机选择一个
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private DataSource randomDs(){
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>int size = dataSourcePool.size();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Random r= new Random();
<span style="white-space:pre"> </span>int t = r.nextInt(size);
<span style="white-space:pre"> </span>dsHolder.set(dataSourcePool.get(t));
<span style="white-space:pre"> </span>return dsHolder.get();
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public Connection getConnection() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>Connection conn = randomDs().getConnection();
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public Connection getConnection(String username, String password)
<span style="white-space:pre"> </span>throws SQLException {
<span style="white-space:pre"> </span>Connection conn = randomDs().getConnection(username, password);
<span style="white-space:pre"> </span>log.info("conn URL---->"+conn.getMetaData().getURL());
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return conn;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public PrintWriter getLogWriter() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().getLogWriter();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public int getLoginTimeout() throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().getLoginTimeout();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public void setLogWriter(PrintWriter out) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>dsHolder.get().setLogWriter(out);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public void setLoginTimeout(int seconds) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>dsHolder.get().setLoginTimeout(seconds);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public boolean isWrapperFor(Class<?> iface) throws SQLException {
<span style="white-space:pre"> </span>// TODO Auto-generated method stub
<span style="white-space:pre"> </span>return dsHolder.get().isWrapperFor(iface);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>@Override
<span style="white-space:pre"> </span>public <T> T unwrap(Class<T> iface) throws SQLException {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>return dsHolder.get().unwrap(iface);
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public List<DataSource> getDataSourcePool() {
<span style="white-space:pre"> </span>return dataSourcePool;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>public void setDataSourcePool(List<DataSource> dataSourcePool) {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>this.dataSourcePool = dataSourcePool;
<span style="white-space:pre"> </span>}
}
为了可用性,实际生产环境中需要判断取到的DataSource对应的数据库是否可访问,若不能访问,则要取其他可用的DataSource。
在springframework中的配置如下:
<span style="white-space:pre"></span>
<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:context="http://www.springframework.org/schema/context"
xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
<bean id="dataSource" class="code.RandomDataSource">
<property name="dataSourcePool">
<list>
<ref bean="dataSource1" />
<ref bean="dataSource2" />
</list>
</property>
</bean>
<bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://192.168.2.192/ebook"/>
<property name="user" value="test"/>
<property name="password" value="test"/>
</bean>
<bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://192.168.2.105/ebook"/>
<property name="user" value="test"/>
<property name="password" value="test"/>
</bean>
<!-- Hibernate SessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.format_sql">true</prop>
</props>
</property>
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- Transaction manager for a single Hibernate SessionFactory (alternative to JTA) -->
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"
p:sessionFactory-ref="sessionFactory"/>
</beans>
这里连接的是MySQL数据库,如果是其他数据库,需要将DataSource1和DataSource2中的参数以及
<span style="font-size:18px;">hibernate.dialect<span style="font-family: Arial, Verdana, sans-serif; white-space: normal; background-color: rgb(255, 255, 255); ">做相应修改。</span></span>
<span style="font-family: Arial, Verdana, sans-serif; white-space: normal; background-color: rgb(255, 255, 255); "><span style="font-size:18px;">
</span></span>
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>