需要导入的jar:
spring boot + mybaits 略:
sharding :
<dependency>
<groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>2.0.3</version> </dependency> <!--sharding 内部实现分布式主键--> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-self-id-generator</artifactId> <version>1.4.2</version> </dependency>
xxxMapper.java
@Mapper public interface OrderMapper{ int insert(Order record); List<Order> selectAll(); List<Order> selectByUserId(List list); }
xxxMapper.xml
<insert id="insert" parameterType="com.skill.skillmodel.model.Order"> insert into t_order (order_id, name, random, user_id) values (#{orderId,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{random,jdbcType=VARCHAR}, #{userId,jdbcType=BIGINT}) </insert> <select id="selectAll" resultMap="BaseResultMap"> select order_id , name, random,user_id from t_order </select> <select id="selectByUserId" resultMap="BaseResultMap"> select order_id , name, random,user_id from t_order where User_id in( <foreach collection="list" item="item" separator=","> #{item} </foreach> ) </select>
配置文件:
@Configuration public class DataSourceConfig { @Bean public IdGenerator getIdGenerator() { return new CommonSelfIdGenerator(); } @Bean(name = "shardingDataSource") DataSource getShardingDataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig; shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration()); // shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration()); shardingRuleConfig.getBindingTableGroups().add("t_order"); shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloDatabaseShardingAlgorithm.class.getName())); shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", ModuloTableShardingAlgorithm.class.getName())); // shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", DemoTableShardingAlgorithm.class.getName())); return new ShardingDataSource(shardingRuleConfig.build(createDataSourceMap())); // return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig); } @Bean TableRuleConfiguration getUserTableRuleConfiguration() { TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration(); orderTableRuleConfig.setLogicTable("t_order"); orderTableRuleConfig.setActualDataNodes("user_${0..1}.t_order_${0..1}"); orderTableRuleConfig.setKeyGeneratorColumnName("user_id"); return orderTableRuleConfig; } /** * 需要手动配置事务管理器 * * @param shardingDataSource * @return */ @Bean public DataSourceTransactionManager transactitonManager(DataSource shardingDataSource) { return new DataSourceTransactionManager(shardingDataSource); } @Bean @Primary public SqlSessionFactory sqlSessionFactory(DataSource shardingDataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(shardingDataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return bean.getObject(); } @Bean @Primary public SqlSessionTemplate testSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } private Map<String, DataSource> createDataSourceMap() { Map<String, DataSource> result = new HashMap<>(); result.put("user_0", createDataSource("user_0")); result.put("user_1", createDataSource("user_1")); return result; } private DataSource createDataSource(final String dataSourceName) { BasicDataSource result = new BasicDataSource(); result.setDriverClassName(com.mysql.jdbc.Driver.class.getName()); result.setUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName)); result.setUsername("root"); result.setPassword("root"); return result; } }
public class ModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) { for (String each : collection) { if (each.endsWith(Long.parseLong(preciseShardingValue.getValue().toString()) % 2+"")) { return each; } } throw new IllegalArgumentException(); } }
public class ModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) { for (String each : collection) { if (each.endsWith(Long.parseLong(preciseShardingValue.getValue().toString()) % 2+"")) { return each; } } throw new IllegalArgumentException(); } }
test测试:
@RunWith(SpringRunner.class) @SpringBootTest(classes = SkillmodelApplication.class) public class SkillmodelApplicationTests { @Autowired private OrderService orderService; @Resource private OrderMapper orderMapper; @Autowired private IdGenerator idGenerator; @Test public void insertSubTable(){ for (int i = 0; i < 10; i++) { Order order = new Order(); order.setOrderId(idGenerator.generateId().longValue()); order.setUserId((long) i); order.setRandom(RandomUtils.getRandomInt(12)); order.setName(RandomUtils.getRandomString(5)); orderMapper.insert(order); } for (int i = 10; i < 20; i++) { Order order = new Order(); order.setOrderId(idGenerator.generateId().longValue()); order.setUserId((long) i + 1); order.setRandom(RandomUtils.getRandomInt(12)); order.setName(RandomUtils.getRandomString(5)); orderMapper.insert(order); } } @Test public void selectAll(){ List<Order> orders = orderMapper.selectAll(); System.out.println(orders); } @Test public void selectByUserId(){ List<Long> userlist = new ArrayList<>(); userlist.add(0l); userlist.add(1l); userlist.add(3l); List<Order> orders = orderMapper.selectByUserId(userlist); System.out.println(orders); } }