八、多WebApps与多mysql的集成

在高并发下，webApp、数据库服务通常由多台几点构成

写的方式主要主节点，读取在从节点。然后通过代理服务器做转发。但是mysql存在单点故障问题。

解决方案

Spring  + Ibatis 与mysql集成的方法

1、        准备多台mysql并配置好

2、        配置application-context.xml

<!-- 配置数据源开始 -->

    <beanid="dataSources"class="com.caland.sun.client.datasources.DefaultDataSourceService">

       <propertyname="dataSourceDescriptors">

             <set>

                  <bean class="com.caland.sun.client.datasources.DataSourceDescriptor">

                        <propertyname="identity" value="partition1"/>

                        <propertyname="targetDataSource" ref="dataSource1"/>

                        <propertyname="targetDetectorDataSource" ref="dataSource1"/>

                        <propertyname="standbyDataSource" ref="dataSource4"/>

                        <propertyname="standbyDetectorDataSource" ref="dataSource4"/>

                  </bean>

                  <beanclass="com.caland.sun.client.datasources.DataSourceDescriptor">

                        <propertyname="identity" value="partition2"/>

                        <propertyname="targetDataSource" ref="dataSource2"/>

                        <propertyname="targetDetectorDataSource" ref="dataSource2"/>

                        <propertyname="standbyDataSource" ref="dataSource5"/>

                        <propertyname="standbyDetectorDataSource" ref="dataSource5"/>

                  </bean>

                  <beanclass="com.caland.sun.client.datasources.DataSourceDescriptor">

                        <propertyname="identity" value="partition3"/>

                        <propertyname="targetDataSource" ref="dataSource3"/>

                        <propertyname="targetDetectorDataSource" ref="dataSource3"/>

                        <propertyname="standbyDataSource" ref="dataSource6"/>

                        <propertyname="standbyDetectorDataSource" ref="dataSource6"/>

                  </bean>

             </set>

       </property>

       <propertyname="haDataSourceCreator">

             <beanclass="com.caland.sun.client.datasources.ha.FailoverHotSwapDataSourceCreator">

                  <propertyname="detectingSql" value="update caland settimeflag=CURRENT_TIMESTAMP()"/>

             </bean>

       </property>

</bean>

<!-- 数据源1 -->

<beanid="dataSource1"class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <propertyname="jdbcUrl" value="${jdbc1.url}" />

       <propertyname="user" value="${jdbc1.username}" />

       <propertyname="password" value="${jdbc1.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <propertyname="checkoutTimeout"value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement"value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections"value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 数据源2 -->

       <beanid="dataSource2"class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <propertyname="jdbcUrl" value="${jdbc2.url}" />

       <propertyname="user" value="${jdbc2.username}" />

       <propertyname="password" value="${jdbc2.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <propertyname="checkoutTimeout"value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement" value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections"value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 数据源3 -->

<beanid="dataSource3"class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <propertyname="jdbcUrl" value="${jdbc3.url}" />

       <propertyname="user" value="${jdbc3.username}" />

       <propertyname="password" value="${jdbc3.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <property name="checkoutTimeout"value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement"value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections"value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 数据源4 -->

<bean id="dataSource4"class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <propertyname="jdbcUrl" value="${jdbc4.url}" />

       <propertyname="user" value="${jdbc4.username}" />

       <property name="password"value="${jdbc4.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <propertyname="checkoutTimeout"value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement"value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections"value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 数据源5 -->

<beanid="dataSource5"class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <property name="jdbcUrl"value="${jdbc5.url}" />

       <propertyname="user" value="${jdbc5.username}" />

       <propertyname="password" value="${jdbc5.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <propertyname="checkoutTimeout" value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement"value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections"value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 数据源6 -->

<beanid="dataSource6" class="com.mchange.v2.c3p0.ComboPooledDataSource">

       <propertyname="driverClass" value="${jdbc.driverClassName}" />

       <propertyname="jdbcUrl" value="${jdbc6.url}" />

       <propertyname="user" value="${jdbc6.username}" />

       <propertyname="password" value="${jdbc6.password}" />

       <propertyname="autoCommitOnClose" value="true"/>

<!--       <propertyname="checkoutTimeout"value="${cpool.checkoutTimeout}"/>-->

       <propertyname="initialPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="minPoolSize" value="${cpool.minPoolSize}"/>

       <propertyname="maxPoolSize" value="${cpool.maxPoolSize}"/>

       <propertyname="maxIdleTime" value="${cpool.maxIdleTime}"/>

       <propertyname="acquireIncrement"value="${cpool.acquireIncrement}"/>

       <propertyname="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>

</bean>

<!-- 配置数据源结束 --> 

   

<!-- 配置路由规则开始 -->

    <beanid="hashFunction"class="com.caland.core.dao.router.HashFunction"/>

<beanid="internalRouter"

       class="com.caland.sun.client.router.config.InteralRouterXmlFactoryBean">

       <!-- functionsMap是在使用自定义路由规则函数的时候使用 -->

       <propertyname="functionsMap">

             <map>

                  <entrykey="hash" value-ref="hashFunction"></entry>

             </map>

    </property>

       <propertyname="configLocations">

             <list>

                  <value>classpath:/dbRule/sharding-rules-on-namespace.xml</value>

             </list>

       </property>

</bean>

<!-- 配置路由规则结束 -->

3、        配置sharding-rules-on-namespace.xml。配置数据池连接规则

<rules>

<rule>

       <namespace>Order</namespace>

       <!--

                表达式如果不使用自定义路由规则函数,而是直接使用   taobaoId%2==0这种的话就不用在文件

                中配置<property name="functionsMap">中了

       -->

       <shardingExpression>hash.applyOrder(userId)== 1</shardingExpression>

       <shards>partition1</shards>

</rule>

<rule>

       <namespace>Order</namespace>

      <shardingExpression>hash.applyOrder(userId) ==2</shardingExpression>

       <shards>partition2</shards>

</rule>

<rule>

       <namespace>Order</namespace>

       <shardingExpression>hash.applyOrder(userId)== 3</shardingExpression>

       <shards>partition3</shards>

</rule>

<rule>

       <namespace>User</namespace>

       <!--

                表达式如果不使用自定义路由规则函数,而是直接使用   taobaoId%2==0这种的话就不用在文件

                中配置<property name="functionsMap">中了

       -->

       <shardingExpression>hash.applyUser(username)== 1</shardingExpression>

       <shards>partition1</shards>

</rule>

<rule>

       <namespace>User</namespace>

       <shardingExpression>hash.applyUser(username)== 2</shardingExpression>

       <shards>partition2</shards>

</rule>

<rule>

       <namespace>User</namespace>

       <shardingExpression>hash.applyUser(username)== 3</shardingExpression>

       <shards>partition3</shards>

</rule>

</rules>

4、        编写规则类

/**

 * 根据某种自定义的hash算法来进行散列,并根据散列的值进行路由

 *  常见的水平切分规则有:

基于范围的切分, 比如 memberId >10000 and memberId < 20000

基于模数的切分, 比如 memberId%128==1或者 memberId%128==2 或者...

基于哈希(hashing)的切分, 比如hashing(memberId)==someValue等

 * @author lixu

 *

 */

public class HashFunction{

/**

 * 对三个数据库进行散列分布

 * 1、返回其他值，没有在配置文件中配置的，如负数等，在默认数据库中查找

 * 2、比如现在配置文件中配置有三个结果进行散列，如果返回为0，那么apply方法只调用一次，如果返回为2，

 *   那么apply方法就会被调用三次，也就是每次是按照配置文件的顺序依次的调用方法进行判断结果，而不会缓存方法返回值进行判断

 * @param id

 * @return

 */

public int applyOrder(IntegeruserId) {

       //先从缓存获取 没有则查询数据库

       //input 可能是id，拿id到缓存里去查用户的DB坐标信息。然后把库的编号输出

       int result =(int)(userId % 1024);

       System.out.println("hash:"+ result);

       if(0 <= result&& result < 256){

             result = 0;

             System.out.println("在第1个数据库中");

       }

       if(256 <= result&& result < 512){

             result = 1;

             System.out.println("在第2个数据库中");

       }

       if(512 <= result&& result < 1024){

             result = 2;

             System.out.println("在第3个数据库中");

       }

       return result;

}

/**

 * 对三个数据库进行散列分布

 * 1、返回其他值，没有在配置文件中配置的，如负数等，在默认数据库中查找

 * 2、比如现在配置文件中配置有三个结果进行散列，如果返回为0，那么apply方法只调用一次，如果返回为2，

 *   那么apply方法就会被调用三次，也就是每次是按照配置文件的顺序依次的调用方法进行判断结果，而不会缓存方法返回值进行判断

 * @param id

 * @return

 */

public int applyUser(Stringusername) {

       //先从缓存获取 没有则查询数据库

       //input 可能是id，拿id到缓存里去查用户的DB坐标信息。然后把库的编号输出

       int result =Math.abs(username.hashCode() % 1024);//0---1023

       System.out.println("hash:"+ result);//333

       if(0 <= result&& result < 256){

             result = 1;

             System.out.println("在第1个数据库中");

       }

       if(256 <= result&& result < 512){

             result = 2;

             System.out.println("在第2个数据库中");

       }

       if(512 <= result&& result < 1024){

             result = 3;

             System.out.println("在第3个数据库中");

       }

       return result;

}

/**

       注：只调用一次

    taobaoId：3354

       在第1个数据库中

 

         注：调用了三次

    taobaoId：7043

       在第3个数据库中

       taobaoId：7043

       在第3个数据库中

       taobaoId：7043

       在第3个数据库中

 */

 

}

 

源码下载地址：http://download.csdn.net/detail/huwenfeng_2011/9587636