借助sharding-jdbc实现数据分片

左手的ㄟ右手 2022-04-10 06:39 164阅读 0赞

#### 1、引入依赖： ####

<dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>${shardingjdbc.version}</version>
            </dependency>
            <dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-transaction</artifactId>
                <version>${shardingjdbc.version}</version>
            </dependency>
            <dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-config-yaml</artifactId>
                <version>${shardingjdbc.version}</version>
            </dependency>
            <dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-config-spring</artifactId>
                <version>${shardingjdbc.version}</version>
            </dependency>
            <dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-self-id-generator</artifactId>
                <version>${shardingjdbc.version}</version>
            </dependency>

#### 2、spring配置文件： ####

<bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            
            <property name="driverClassName" value="${data1.jdbc.driverClassName}" />
            <property name="url" value="${data1.jdbc.url}" />
            <property name="username" value="${data1.jdbc.username}" />
            <property name="password" value="${data1.jdbc.password}" />
            
            <property name="initialSize" value="1" />
            
            <property name="maxActive" value="20" />
            
            
            
            <property name="minIdle" value="1" />
            
            <property name="maxWait" value="60000" />
            
            <property name="removeAbandoned" value="true" />
            
            <property name="removeAbandonedTimeout" value="180" />
            
            <property name="timeBetweenEvictionRunsMillis" value="60000" />
            
            <property name="minEvictableIdleTimeMillis" value="300000" />
            
            <property name="validationQuery" value="SELECT 1 FROM DUAL" />
            
            <property name="testWhileIdle" value="true" />
            
            <property name="testOnBorrow" value="false" />
            
            <property name="testOnReturn" value="false" />
            
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="50" />
        </bean>
    
        <bean id="salveDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            
            <property name="driverClassName" value="${data2.jdbc.driverClassName}" />
            <property name="url" value="${data2.jdbc.url}" />
            <property name="username" value="${data2.jdbc.username}" />
            <property name="password" value="${data2.jdbc.password}" />
            
            <property name="initialSize" value="1" />
            
            <property name="maxActive" value="20" />
            
            
            
            <property name="minIdle" value="1" />
            
            <property name="maxWait" value="60000" />
            
            <property name="removeAbandoned" value="true" />
            
            <property name="removeAbandonedTimeout" value="180" />
            
            <property name="timeBetweenEvictionRunsMillis" value="60000" />
            
            <property name="minEvictableIdleTimeMillis" value="300000" />
            
            <property name="validationQuery" value="SELECT NOW() FROM DUAL" />
            
            <property name="testWhileIdle" value="true" />
            
            <property name="testOnBorrow" value="false" />
            
            <property name="testOnReturn" value="false" />
            
            <property name="poolPreparedStatements" value="true" />
            <property name="maxPoolPreparedStatementPerConnectionSize" value="50" />
        </bean>
    
        
        <rdb:strategy id="databaseShardingStrategy" sharding-columns="user_id" algorithm-class="com.wangcw.sharding.SingleKeyModuloDatabaseShardingAlgorithm"/>
    
        
        <rdb:strategy id="tableShardingStrategy" sharding-columns="id" algorithm-class="com.wangcw.sharding.SingleKeyModuloTableShardingAlgorithm"/>
    
        
        <rdb:data-source id="shardingDataSource">
            <rdb:sharding-rule data-sources="masterDataSource, salveDataSource" default-data-source="masterDataSource">
                <rdb:table-rules>
                    
                    <rdb:table-rule logic-table="t_user" actual-tables="t_user_0,t_user_1" database-strategy="databaseShardingStrategy" table-strategy="tableShardingStrategy">
                        
                        <rdb:auto-increment-column column-name="id"/>
                    </rdb:table-rule>
                </rdb:table-rules>
            </rdb:sharding-rule>
        </rdb:data-source>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="shardingDataSource" />
            
            <property name="configLocation" value="classpath:sqlMapcfg.xml" />
            
            <property name="mapperLocations" value="classpath*:mappers/*.xml"></property>
        </bean>
    
        
        <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.wangcw.dao" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        </bean>
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="shardingDataSource" />
        </bean>
    
        <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
            <property name="transactionManager">
                <ref local="transactionManager" />
            </property>
        </bean>
    
        
        <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
    
        
        <tx:annotation-driven transaction-manager="transactionManager" />

#### 3、分库算法 ####

public class SingleKeyModuloDatabaseShardingAlgorithm implements
            SingleKeyDatabaseShardingAlgorithm<Integer> { 
    
        /* 分库个数 */
        private int dbCount = 2;
    
        @Override
        public String doEqualSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            for (String each : availableTargetNames) { 
                if (each.endsWith(shardingValue.getValue() % dbCount + "")) { 
                    return each;
                }
            }
            throw new UnsupportedOperationException();
        }
    
        @Override
        public Collection<String> doInSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            Collection<String> result = new LinkedHashSet<>(
                    availableTargetNames.size());
            Collection<Integer> values = shardingValue.getValues();
            for (Integer value : values) { 
                for (String dataSourceName : availableTargetNames) { 
                    if (dataSourceName.endsWith(value % dbCount + "")) { 
                        result.add(dataSourceName);
                    }
                }
            }
            return result;
        }
    
        @Override
        public Collection<String> doBetweenSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
            Range<Integer> range = shardingValue.getValueRange();
            for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { 
                for (String each : availableTargetNames) { 
                    if (each.endsWith(i % dbCount + "")) { 
                        result.add(each);
                    }
                }
            }
            return result;
        }
    
        /** * 设置database分库的个数 * * @param dbCount */
        public void setDbCount(int dbCount) { 
            this.dbCount = dbCount;
        }
    
    }

#### 4、分表算法 ####

public class SingleKeyModuloTableShardingAlgorithm implements
            SingleKeyTableShardingAlgorithm<Integer> { 
    
        /* 分表个数 */
        private int tableCount = 2;
    
        @Override
        public String doEqualSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            for (String each : availableTargetNames) { 
                if (each.endsWith(shardingValue.getValue() % tableCount + "")) { 
                    return each;
                }
            }
            throw new UnsupportedOperationException();
        }
    
        @Override
        public Collection<String> doInSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            Collection<String> result = new LinkedHashSet<>(
                    availableTargetNames.size());
            Collection<Integer> values = shardingValue.getValues();
            for (Integer value : values) { 
                for (String tableNames : availableTargetNames) { 
                    if (tableNames.endsWith(value % tableCount + "")) { 
                        result.add(tableNames);
                    }
                }
            }
            return result;
        }
    
        @Override
        public Collection<String> doBetweenSharding(
                final Collection<String> availableTargetNames,
                final ShardingValue<Integer> shardingValue) { 
            Collection<String> result = new LinkedHashSet<>(
                    availableTargetNames.size());
            Range<Integer> range = shardingValue.getValueRange();
            for (Integer i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { 
                for (String each : availableTargetNames) { 
                    if (each.endsWith(i % tableCount + "")) { 
                        result.add(each);
                    }
                }
            }
            return result;
        }
    
        /** * 设置分表的个数 * * @param tableCount */
        public void setTableCount(int tableCount) { 
            this.tableCount = tableCount;
        }
    
    }

#### 5、至此，一个简单的数据分片就可以实现了。但是一般还需要考虑到分布式事务、关联查询和性能等问题，这些后续我会继续深入研究。 ####