<< 2013年8月26日 | 首页 | 2013年8月28日 >>

Dynamic DataSource Routing | SpringSource Team Blog

Anyways, here's the code for my Catalog:

package blog.datasource;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
 
public class Catalog extends SimpleJdbcDaoSupport {
 
   public List<Item> getItems() {
      String query = "select name, price from item";
      return getSimpleJdbcTemplate().query(query, new ParameterizedRowMapper<Item>() {
            public Item mapRow(ResultSet rs, int row) throws SQLException {
               String name = rs.getString(1);
               double price = rs.getDouble(2);
               return new Item(name, price);
            }
      });
   }
}

 

As you can see, the Catalog simply returns a list of Item objects. The Item just contains name and price properties:

package blog.datasource;
 
public class Item {
 
   private String name;
   private double price;
 
   public Item(String name, double price) {
      this.name = name;
      this.price = price;
   }
 
   public String getName() {
      return name;
   }
 
   public double getPrice() {
      return price;
   }
 
   public String toString() {
      return name + " (" + price + ")";
   }
 
}

 

Now, in order to demonstrate multiple DataSources, I created an enum for different Customer types (representing "levels" of membership I guess), and I created three different databases – so that each type of customer would get a distinct item list (I did mention that this would be a contrived example didn't I?). The important thing is that each of the databases are equivalent in terms of the schema. That way the Catalog's query will work against any of them – just returning different results. In this case, it's just the "item" table with 2 columns: name and price. And… here is the enum:

public enum CustomerType {
   BRONZE,
   SILVER,
   GOLD
}

 

It's time to create some bean definitions. Since I have 3 datasources where everything is the same except for the port number, I created a parent bean so that the shared properties can be inherited. Then, I added the 3 bean definitions to represent the per-CustomerType DataSources:

<bean id="parentDataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource"
         abstract="true">
   <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
   <property name="username" value="sa"/>
</bean>
 
<bean id="goldDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.gold}/blog"/>
</bean>
 
<bean id="silverDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.silver}/blog"/>
</bean>
 
<bean id="bronzeDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.bronze}/blog"/>
</bean>
 
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
   <property name="location" value="classpath:/blog/datasource/db.properties"/>
</bean>

 

Notice that I added a PropertyPlaceholderConfigurer so that I could externalize the port numbers in a "db.properties" file, like so:

db.port.gold=9001
db.port.silver=9002
db.port.bronze=9003

 

Now things start to get interesting. I need to supply the "routing" DataSource to my Catalogso that it can dynamically get connections from the 3 different databases at runtime based on the current customer's type. As I mentioned, the AbstractRoutingDataSource can be rather simple to implement. Here is my implementation:

package blog.datasource;
 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 
public class CustomerRoutingDataSource extends AbstractRoutingDataSource {
 
   @Override
   protected Object determineCurrentLookupKey() {
      return CustomerContextHolder.getCustomerType();
   }
}

…and the CustomerContextHolder simply provides access to a thread-bound CustomerType. In reality, the 'context' would likely hold more information about the customer. Also note that if you are using Acegi, then you could retrieve some information from the userDetails. For this example, it's just the customer "type":

public class CustomerContextHolder {
 
   private static final ThreadLocal<CustomerType> contextHolder =
            new ThreadLocal<CustomerType>();
 
   public static void setCustomerType(CustomerType customerType) {
      Assert.notNull(customerType, "customerType cannot be null");
      contextHolder.set(customerType);
   }
 
   public static CustomerType getCustomerType() {
      return (CustomerType) contextHolder.get();
   }
 
   public static void clearCustomerType() {
      contextHolder.remove();
   }
}

 

Finally, I just need to configure the catalog and routing DataSource beans. As you can see, the "real" DataSource references are provided in a Map. If you provide Strings, they can be resolved as JNDI names (or any custom resolution strategy can be provided – see the JavaDoc). Also, I've simply set the 'bronzeDataSource' as the default:

<bean id="catalog" class="blog.datasource.Catalog">
   <property name="dataSource" ref="dataSource"/>
</bean>
 
<bean id="dataSource" class="blog.datasource.CustomerRoutingDataSource">
   <property name="targetDataSources">
      <map key-type="blog.datasource.CustomerType">
         <entry key="GOLD" value-ref="goldDataSource"/>
         <entry key="SILVER" value-ref="silverDataSource"/>
      </map>
   </property>
   <property name="defaultTargetDataSource" ref="bronzeDataSource"/>
</bean>

 

Of course I'd like to see this working, so I've created a simple test (extending one of Spring's integration test support classes). I added 3 items to the "gold" database, 2 items to the "silver" database, and only 1 item to the "bronze" database. This is the test:

public class CatalogTests extends AbstractDependencyInjectionSpringContextTests {
 
   private Catalog catalog;
 
   public void setCatalog(Catalog catalog) {
      this.catalog = catalog;
   }
 
   public void testDataSourceRouting() {
      CustomerContextHolder.setCustomerType(CustomerType.GOLD);
      List<Item> goldItems = catalog.getItems();
      assertEquals(3, goldItems.size());
      System.out.println("gold items: " + goldItems);
 
      CustomerContextHolder.setCustomerType(CustomerType.SILVER);
      List<Item> silverItems = catalog.getItems();
      assertEquals(2, silverItems.size());
      System.out.println("silver items: " + silverItems);
 
      CustomerContextHolder.clearCustomerType();
      List<Item> bronzeItems = catalog.getItems();
      assertEquals(1, bronzeItems.size());
      System.out.println("bronze items: " + bronzeItems);
   }
 
   protected String[] getConfigLocations() {
      return new String[] {"/blog/datasource/beans.xml"};
   }
}

…and rather than simply taking a screenshot of the green bar, you'll notice I've provided some console output – the results!:

gold items: [gold item #1 (250.0), gold item #2 (325.45), gold item #3 (55.6)]
silver items: [silver item #1 (25.0), silver item #2 (15.3)]
bronze items: [bronze item #1 (23.75)]

 

As you can see, the configuration is simple. Better still, the data-access code is not concerned with looking up different DataSources. For more information, consult the JavaDoc for AbstractRoutingDataSource.

阅读全文……

标签 : ,
发表评论 RSS feed for responses to this blog entry
IT瘾于2013年8月27日 下午02时38分00秒发布 #

动态切换数据源(spring+hibernate) - liheping - 博客园

思路：动态切换数据源确切的来说是在同一类型数据库的情况下的。意思就是说 , 在系统中的使用的数据库分布在多台数据库服务器或者在同台服务器上的多个数据库. 在运行时期间根据某种标识符来动态的选择当前操作的数据库.
     1.数据源是相同类型的数据库: 一个SessionFactory+动态数据源+一个事务管理器
     2.数据源是不同类型的数据库: 根据类型 配置多套SessionFactory
 
模拟：两个mysql数据源+一个Access数据源

实现：

1.切换数据源需要标识符,标识符是Object类型
package lhp.example.context;
public enum DBType {
      dataSource1, dataSource2;
}

2.然后创建一个用于切换数据源(设置或者获得上下文)的工具类
复制代码
package lhp.example.context;

public class ContextHolder {
    private static final ThreadLocal<Object> holder = new ThreadLocal<Object>();

    public static void setDbType(DBType dbType) {
        holder.set(dbType);
    }

    public static DBType getDbType() {
        return (DBType) holder.get();
    }

    public static void clearDbType() {
        holder.remove();
    }
}
复制代码

3.创建动态数据源类,继承org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource这个类.
复制代码
package lhp.example.context;

import java.util.logging.Logger;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    public static final Logger logger = Logger.getLogger(DynamicDataSource.class.toString());

    @Override
    protected Object determineCurrentLookupKey() {
        DBType key = ContextHolder.getDbType();//获得当前数据源标识符
        //logger.info("当前数据源 :" + key);
        return key;
    }

}
复制代码

4.然后配置多个数据源
复制代码
<!-- 数据源1 ： mysql -->
    <bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/dec" />
        <property name="user" value="root" />
        <property name="password" value="" />
    </bean>
    <!-- 数据源2 ： mysql  -->
    <bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/lms" />
        <property name="user" value="root" />
        <property name="password" value="" />
    </bean>

    <!-- 数据源3 ：  access -->
    <bean id="dataSource3" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="sun.jdbc.odbc.JdbcOdbcDriver" />
        <property name="jdbcUrl" value="jdbc:odbc:accessTest" />
        <property name="user" value="administrator" />
        <property name="password" value="XLZX0309" />
    </bean>

    <!-- mysql 动态数据源设置-->
    <bean id="mysqlDynamicDataSource" class="lhp.example.context.DynamicDataSource">
        <property name="targetDataSources">
            <!-- 标识符类型 -->
            <map key-type="lhp.example.context.DBType">
                <entry key="dataSource1" value-ref="dataSource1" />
                <entry key="dataSource2" value-ref="dataSource2" />
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="dataSource1" />
    </bean>
复制代码

5.配置sessionFactory
复制代码
<!-- mysql sessionFactory -->
    <bean id="mysqlSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="mysqlDynamicDataSource" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop><!--create validate -->
                <prop key="hibernate.query.substitutions">true 1, false 0</prop>
            </props>
        </property>
    </bean>
    
    <!-- access sessionFactory -->
    <bean id="aceessSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource3" />
        <property name="hibernateProperties">
            <props>
                <!-- access 语法和MSSQL相似 所以用的MSSQL方言，或者可以使用第三方方言 -->
                <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
                <prop key="hibernate.jdbc.batch_size">30</prop>
                <prop key="hibernate.jdbc.fetch_size">50</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.format_sql">false</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop><!--create validate -->
                <prop key="hibernate.query.substitutions">true 1, false 0</prop>
                <prop key="hibernate.cglib.use_reflection_optimizer">true</prop>
                <!-- <prop key="hibernate.cache.use_second_level_cache">true</prop> -->
                <!-- <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop> -->
                <!-- <prop key="hibernate.cache.use_query_cache">true</prop> -->
                <!-- <prop key="hibernate.generate_statistics">true</prop> -->
                <!-- <prop key="hibernate.cache.provider_configuration_file_resource_path">classpath:ehcache.xml</prop> -->
            </props>
        </property>
    </bean>
复制代码

6.测试用例
复制代码
package lhp.example.junit;

import static org.junit.Assert.*;
import java.sql.DatabaseMetaData;
import lhp.example.context.ContextHolder;
import lhp.example.context.DBType;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class ServiceTest {
    private ApplicationContext context;
    //三个数据源的URL
    private String dataSource1_URL = "jdbc:mysql://127.0.0.1:3306/dec";
    private String dataSource2_URL = "jdbc:mysql://127.0.0.1:3306/lms";
    private String dataSource3_URL = "jdbc:odbc:accessTest";
    private SessionFactory mysqlSessionFactory;
    private SessionFactory aceessSessionFactory;

    @Before
    public void setUp() throws Exception {
        // 选择数据源初始化spring
        ContextHolder.setDbType(DBType.dataSource1);
        //
        String[] xmlFiles = new String[] { 
                "applicationContext-dataSource.xml",
                "applicationContext-hibernate.xml",
                "applicationContext-spring.xml" };
        //
        context = new ClassPathXmlApplicationContext(xmlFiles);
        //
        mysqlSessionFactory = (SessionFactory) context.getBean("mysqlSessionFactory");
        aceessSessionFactory = (SessionFactory) context.getBean("aceessSessionFactory");
    }

    @SuppressWarnings("deprecation")
    @Test
    public void mysqlDataSourceTest() {
        try {

            Session mysqlSession = mysqlSessionFactory.openSession();
            // 获得数据库元数据
            DatabaseMetaData meatData = mysqlSession.connection().getMetaData();

            // 默认启动数据源 dataSource1
            //断言当前数据源URL是否是dataSource1的URL
            assertEquals(dataSource1_URL, meatData.getURL());

            // 切换到数据源 dataSource2
            ContextHolder.setDbType(DBType.dataSource2);
            mysqlSession = mysqlSessionFactory.openSession();
            meatData = mysqlSession.connection().getMetaData();
            //断言当前数据源URL是否是dataSource2的URL
            assertEquals(dataSource2_URL, meatData.getURL());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    @SuppressWarnings("deprecation")
    @Test
    public void accessDataSourceTest() {
        try {
            Session accessSession = aceessSessionFactory.openSession();
            // 获得数据库元数据
            DatabaseMetaData meatData = accessSession.connection().getMetaData();
            //断言当前数据源URL是否是dataSource3的URL
            assertEquals(dataSource3_URL, meatData.getURL());


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
复制代码

阅读全文……

标签 : ,
发表评论 RSS feed for responses to this blog entry
IT瘾于2013年8月27日 下午02时32分00秒发布 #