1 n个数据源配置
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
import com.alibaba.druid.pool.DruidDataSource;
@Component
@Configuration
@ConfigurationProperties(prefix = "database0")
public class Database0Config {
private String url;
private String username;
private String password;
private String driverClassName;
private String databaseName;
public DataSource createDataSource() {
DruidDataSource result = new DruidDataSource();
result.setDriverClassName(getDriverClassName());
result.setUrl(getUrl());
result.setUsername(getUsername());
result.setPassword(getPassword());
return result;
}
}
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
import com.alibaba.druid.pool.DruidDataSource;
@Component
@Configuration
@ConfigurationProperties(prefix = "database1")
public class Database1Config {
private String url;
private String username;
private String password;
private String driverClassName;
private String databaseName;
public DataSource createDataSource() {
DruidDataSource result = new DruidDataSource();
result.setDriverClassName(getDriverClassName());
result.setUrl(getUrl());
result.setUsername(getUsername());
result.setPassword(getPassword());
return result;
}
}
2 分库策略
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;
import com.google.common.collect.Range;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.Collection;
import java.util.LinkedHashSet;
/**
* 这里使用的都是单键分片策略 示例分库策略是: GoodsId<=20使用database0库 其余使用database1库
*
* @author yangyang
* @date 2019/1/30
*/
@Component
public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
@Autowired
private Database0Config database0Config;
@Autowired
private Database1Config database1Config;
public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
Long value = shardingValue.getValue();
if (value %2==0) {
return database0Config.getDatabaseName();
} else {
return database1Config.getDatabaseName();
}
}
public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
for (Long value : shardingValue.getValues()) {
if (value %2==0) {
result.add(database0Config.getDatabaseName());
} else {
result.add(database1Config.getDatabaseName());
}
}
return result;
}
public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(availableTargetNames.size());
Range<Long> range = shardingValue.getValueRange();
for (Long value = range.lowerEndpoint(); value <= range.upperEndpoint(); value++) {
if (value %2==0) {
result.add(database0Config.getDatabaseName());
} else {
result.add(database1Config.getDatabaseName());
}
}
return result;
}
}
3 分表策略
import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;
import com.google.common.collect.Range;
import org.springframework.stereotype.Component;
import java.util.Collection;
import java.util.LinkedHashSet;
/**
* 这里使用的都是单键分片策略
* 示例分表策略是:
* GoodsType为奇数使用goods_1表
* GoodsType为偶数使用goods_0表
* @author yangyang
* @date 2019/1/30
*/
@Component
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
public String doEqualSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
for (String each : tableNames) {
if (each.endsWith(shardingValue.getValue() % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
public Collection<String> doInSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
for (Long value : shardingValue.getValues()) {
for (String tableName : tableNames) {
if (tableName.endsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
public Collection<String> doBetweenSharding(final Collection<String> tableNames,
final ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<String>(tableNames.size());
Range<Long> range = shardingValue.getValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : tableNames) {
if (each.endsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
4 数据源设置
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
/**
* 分库分表设置
* @author admin
*
*/
@Configuration
public class DataSourceConfig {
@Autowired
private Database0Config database0Config;
@Autowired
private Database1Config database1Config;
@Autowired
private DatabaseShardingAlgorithm databaseShardingAlgorithm;
@Autowired
private TableShardingAlgorithm tableShardingAlgorithm;
@Bean
public DataSource getDataSource() throws SQLException {
//分库设置
Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(2);
//添加两个数据库database0和database1
dataSourceMap.put(database0Config.getDatabaseName(), database0Config.createDataSource());
dataSourceMap.put(database1Config.getDatabaseName(), database1Config.createDataSource());
//设置默认数据库
DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap, database0Config.getDatabaseName());
//分表设置,大致思想就是将查询虚拟表Goods根据一定规则映射到真实表中去
TableRule orderTableRule = TableRule.builder("goods")
.actualTables(Arrays.asList("goods_0", "goods_1"))
.dataSourceRule(dataSourceRule)
.build();
//分库分表策略
ShardingRule shardingRule = ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule))
.databaseShardingStrategy(new DatabaseShardingStrategy("goodsId", databaseShardingAlgorithm))
.tableShardingStrategy(new TableShardingStrategy("goodsType", tableShardingAlgorithm)).build();
DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
return dataSource;
}
}
5 mapper映射
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import com.sample.pojo.Goods;
@Mapper
@Repository
public interface GoodsMapper{
Integer insert(Goods goods);
Integer updateById(Goods goods);
Integer delete(Integer id);
List<Goods> list();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sample.mapper.GoodsMapper">
<resultMap id="BaseResultMap" type="com.sample.pojo.Goods">
<result column="goodsId" property="goodsId" />
<result column="goodsName" property="goodsName" />
<result column="goodsType" property="goodsType" />
</resultMap>
<sql id="Base_Column_List">
goodsId,
goodsName,
goodsType
</sql>
<insert id="insert" parameterType="com.sample.pojo.Goods">
INSERT INTO goods
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test='null != goodsId'>
goodsId,
</if>
<if test='null != goodsName'>
goodsName,
</if>
<if test='null != goodsType'>
goodsType
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test='null != goodsId'>
#{goodsId},
</if>
<if test='null != goodsName'>
#{goodsName},
</if>
<if test='null != goodsType'>
#{goodsType}
</if>
</trim>
</insert>
<delete id="delete">
DELETE FROM goods
WHERE goodsId = #{goodsId}
</delete>
<update id="updateById" parameterType="com.sample.pojo.Goods">
UPDATE goods
<set>
<if test='null != goodsName'>
#{goodsName},
</if>
<if test='null != goodsType'>
#{goodsType}
</if>
</set>
WHERE id = #{goodsId}
</update>
<select id="list" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM goods
order by goodsId
</select>
</mapper>
6 service
import java.util.List;
import com.sample.pojo.Goods;
public interface DatasourceServiceDemo {
Integer insert(Goods goods);
Integer updateById(Goods goods);
Integer delete(Integer id);
List<Goods> list();
}
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.sample.mapper.GoodsMapper;
import com.sample.pojo.Goods;
import com.sample.service.DatasourceServiceDemo;
@Service
@Transactional
public class DatasourceServiceDemoImpl implements DatasourceServiceDemo {
@Autowired
private GoodsMapper goodsMapper;
public Integer insert(Goods goods) {
return goodsMapper.insert(goods);
}
public Integer updateById(Goods goods){
return goodsMapper.updateById(goods);
}
public Integer delete(Integer id){
return goodsMapper.delete(id);
}
public List<Goods> list(){
return goodsMapper.list();
}
}
7 测试controller
import java.util.List;
import java.util.Random;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.sample.pojo.Goods;
import com.sample.service.DatasourceServiceDemo;
import com.sample.service.DubboDemoService;
@Controller
public class ServiceDemoController {
@Autowired
private DatasourceServiceDemo datasourceServiceDemo;
public static Long id=1l;
public static Random rand = new Random();
@ResponseBody
@RequestMapping("/insert")
public Goods insert() {
Goods goods=new Goods();
goods.setGoodsId(id++);
goods.setGoodsName("goods"+id);
goods.setGoodsType((long) rand.nextInt(5));
datasourceServiceDemo.insert(goods);
return goods;
}
@ResponseBody
@RequestMapping("/list")
public List<Goods> list(){
return datasourceServiceDemo.list();
}
}
参考来源:https://www.cnblogs.com/dalaoyang/p/10345388.html
感谢@dalaoyang
:)