mysql分库存储数据

末蓝、 2022-05-29 20:46 209阅读 0赞

业务场景:

张三李四王五,根据不同算法,分别存到不同的数据库里面.

那么怎么配置数据库呢?

我们暂时配置,三个主库,三个从库.

![70][]

jdbc.properties配置文件,配置六个数据库

jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb1?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password
    
    jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb2?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password
    
    jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb3?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password
    
    jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb4?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password
    
    jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb5?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password
    
    jdbc.url=jdbc\:mysql\://192.168.1.57\:3306/mydb6?useUnicode\=true&characterEncoding\=utf-8
    jdbc.username=root
    jdbc.password=password

Spring核心配置：

<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
           <property name="location">
             <value>classpath:/jdbc.properties</value>
           </property>
        </bean>
    
    
    
        <bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="${jdbc.driverClassName}" />
            <property name="jdbcUrl" value="${jdbc1.url}" />
            <property name="user" value="${jdbc1.username}" />
            <property name="password" value="${jdbc1.password}" />
            <property name="autoCommitOnClose" value="true"/>
    
            <property name="initialPoolSize" value="${cpool.minPoolSize}"/>
            <property name="minPoolSize" value="${cpool.minPoolSize}"/>
            <property name="maxPoolSize" value="${cpool.maxPoolSize}"/>
            <property name="maxIdleTime" value="${cpool.maxIdleTime}"/>
            <property name="acquireIncrement" value="${cpool.acquireIncrement}"/>
            <property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>
        </bean>
    	
        
            <bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="${jdbc.driverClassName}" />
            <property name="jdbcUrl" value="${jdbc2.url}" />
            <property name="user" value="${jdbc2.username}" />
            <property name="password" value="${jdbc2.password}" />
            <property name="autoCommitOnClose" value="true"/>
    
            <property name="initialPoolSize" value="${cpool.minPoolSize}"/>
            <property name="minPoolSize" value="${cpool.minPoolSize}"/>
            <property name="maxPoolSize" value="${cpool.maxPoolSize}"/>
            <property name="maxIdleTime" value="${cpool.maxIdleTime}"/>
            <property name="acquireIncrement" value="${cpool.acquireIncrement}"/>
            <property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>
        </bean>
    	
        
        <bean id="dataSource3" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="${jdbc.driverClassName}" />
            <property name="jdbcUrl" value="${jdbc1.url}" />
            <property name="user" value="${jdbc1.username}" />
            <property name="password" value="${jdbc1.password}" />
            <property name="autoCommitOnClose" value="true"/>
    
            <property name="initialPoolSize" value="${cpool.minPoolSize}"/>
            <property name="minPoolSize" value="${cpool.minPoolSize}"/>
            <property name="maxPoolSize" value="${cpool.maxPoolSize}"/>
            <property name="maxIdleTime" value="${cpool.maxIdleTime}"/>
            <property name="acquireIncrement" value="${cpool.acquireIncrement}"/>
            <property name="maxIdleTimeExcessConnections" value="${cpool.maxIdleTimeExcessConnections}"/>
        </bean>
    
    
    
    
        <bean id="dataSources" class="com.caland.sun.client.datasources.DefaultDataSourceService">
            <property name="dataSourceDescriptors">
    		
                <set>
                    <bean class="com.caland.sun.client.datasources.DataSourceDescriptor">
                        <property name="identity" value="partition1"/>
                        
                        <property name="targetDataSource" ref="dataSource1"/>
                        
                        <property name="targetDetectorDataSource" ref="dataSource1"/>
                        
                        <property name="standbyDataSource" ref="dataSource4"/>
                        
                        <property name="standbyDetectorDataSource" ref="dataSource4"/>
                    </bean>
                    <bean class="com.caland.sun.client.datasources.DataSourceDescriptor">
                        <property name="identity" value="partition2"/>
                        <property name="targetDataSource" ref="dataSource2"/>
                        <property name="targetDetectorDataSource" ref="dataSource2"/>
                        <property name="standbyDataSource" ref="dataSource5"/>
                        <property name="standbyDetectorDataSource" ref="dataSource5"/>
                    </bean>
                    <bean class="com.caland.sun.client.datasources.DataSourceDescriptor">
                        <property name="identity" value="partition3"/>
                        <property name="targetDataSource" ref="dataSource3"/>
                        <property name="targetDetectorDataSource" ref="dataSource3"/>
                        <property name="standbyDataSource" ref="dataSource6"/>
                        <property name="standbyDetectorDataSource" ref="dataSource6"/>
                    </bean>
                </set>
            </property>
              
            <property name="haDataSourceCreator">
                <bean class="com.caland.sun.client.datasources.ha.FailoverHotSwapDataSourceCreator">
                    <property name="detectingSql" value="update caland set timeflag=CURRENT_TIMESTAMP()"/>
                </bean>
            </property>
        </bean>
    
    
        
        <bean id="hashFunction" class="com.caland.core.dao.router.HashFunction"/>
        <bean id="internalRouter"
            class="com.caland.sun.client.router.config.InteralRouterXmlFactoryBean">
            
            <property name="functionsMap">
                <map>
                    <entry key="hash" value-ref="hashFunction"></entry>  指定使用哪个方法
                </map>
            </property>
            <property name="configLocations">  指定配置路由的xml路径
                <list>
                    <value>classpath:/dbRule/sharding-rules-on-namespace.xml</value>
                </list>
            </property>
        </bean>
      
    
    路由的配置文件
    <rules>
      <rule>
            <namespace>User</namespace>
            
    		
            <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>

上文中配置的hashFunction

public class HashFunction{  
        /** 
         * 对三个数据库进行散列分布 
         * 1、返回其他值，没有在配置文件中配置的，如负数等，在默认数据库中查找 
         * 2、比如现在配置文件中配置有三个结果进行散列，如果返回为0，那么apply方法只调用一次，如果返回为2， 
         *   那么apply方法就会被调用三次，也就是每次是按照配置文件的顺序依次的调用方法进行判断结果，而不会缓存方法返回值进行判断 
         * @param id 
         * @return 
         */  
        public int applyUser(String username) {  
            //先从缓存获取 没有则查询数据库  
            //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;  
        }  
    }

到此一个简单的分库配置完成,可以写个测试类,存储三个用户,张三,李四,王五.进行测试.这里就不写测试了.

[70]: /images/20220530/760936895fbd47af83af6df56919067b.png