本文共 8148 字，大约阅读时间需要 27 分钟。

jdbc-sharding整合spring-boot的使用。

demo简介：有一个brand表分别在dbs_0库和dbs_1库。有一个product表分别在dbs_0有product_0和product_1，即垂直分库水平分表，然后使用spring-boot整合jdbc-sharding的方式，完成增删改查操作。

一、ddl语句 数据库分别为：dbs_0  、dbs_1

DROP TABLE IF EXISTS `brand`;CREATE TABLE `brand` (  `id` bigint(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of brand-- ------------------------------ ------------------------------ Table structure for product-- ----------------------------DROP TABLE IF EXISTS `product`;CREATE TABLE `product` (  `id` bigint(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `brand_id` bigint(11) DEFAULT NULL,  `price` double(10,2) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of product-- ------------------------------ ------------------------------ Table structure for product_0-- ----------------------------DROP TABLE IF EXISTS `product_0`;CREATE TABLE `product_0` (  `id` bigint(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `price` double(10,2) DEFAULT NULL,  `brand_id` bigint(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of product_0-- ------------------------------ ------------------------------ Table structure for product_1-- ----------------------------DROP TABLE IF EXISTS `product_1`;CREATE TABLE `product_1` (  `id` bigint(11) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `price` double(10,2) DEFAULT NULL,  `brand_id` bigint(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、pom.xml文件

    		
                
     
      org.springframework.boot
                 
     
      spring-boot-starter-web
             
            
                
     
      org.mybatis.spring.boot
                 
     
      mybatis-spring-boot-starter
                 
     
      2.0.1
             
            
                
     
      mysql
                 
     
      mysql-connector-java
             
            
                
     
      com.alibaba
                 
     
      druid-spring-boot-starter
                 
     
      1.1.16
             
            
                
     
      org.apache.shardingsphere
                 
     
      sharding-jdbc-spring-boot-starter
                 
     
      4.0.0-RC1
             
        
   

三、app.properties

server.port=8080##mybatis.config-location=classpath:META-INF/mybatis-config.xmlmybatis.mapperLocations=classpath*:com/wj/mapper/**/*Mapper.xmlspring.shardingsphere.datasource.names=ds0,ds1# 配置2个数据源spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/dbs_0?characterEncoding=utf-8spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=123456spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/dbs_1?characterEncoding=utf-8spring.shardingsphere.datasource.ds1.username=rootspring.shardingsphere.datasource.ds1.password=123456# 分表配置spring.shardingsphere.sharding.tables.brand.actual-data-nodes=ds$->{0..1}.brandspring.shardingsphere.sharding.tables.brand.key-generator.column=idspring.shardingsphere.sharding.tables.brand.key-generator.type=SNOWFLAKEspring.shardingsphere.sharding.tables.product.actual-data-nodes=ds$->{0..1}.product_$->{0..1}spring.shardingsphere.sharding.tables.product.table-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.product.table-strategy.inline.algorithm-expression=product_$->{id%2}spring.shardingsphere.sharding.tables.product.key-generator.column=idspring.shardingsphere.sharding.tables.product.key-generator.type=SNOWFLAKE# 分库配置spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}

配置说明：物理数据库名称为：dbs_0和dbs_1。逻辑数据库名称为ds0、ds1。物理数据库表名brand、product_$->{0...1},逻辑数据库表brand、product。(解释：比如我们编写sql语句的使用，直接使用逻辑数据库表名称product就可以了。）

分表配置：brand为每个数据库一个表，product每个数据库有2个表即product_0、product_1。product_$->{0...1}是分片表达式，表示根据主键id会映射到对应的物理数据库。

分库配置：会根据id落在不同的数据库中(ds$->{id%2})，这里ds的物理数据库实际上是dbs_0和dbs_1。

四、增删改查----一律按照正常操作。

1）pojo

public class Brand {	private Long id;	private String name;	public String getName() {		return name;	}	public void setName(String name) {		this.name = name;	}	public Long getId() {		return id;	}	public void setId(Long id) {		this.id = id;	}}

public class Product {	private Long id;	private String name;	private double price;	private Long brand_id;		// 链接查询的扩展字段 ---	private String brand_name;

2）mapper

public interface BrandMapper {		List
   
     listAll();		void insertBrand(Brand brand);		void deleteById(Long id);}
   

public interface ProductMapper {		List
   
     findProductsByBrand(Long brand_id);		void insertProduct(Product product);		Product findById(Long id);		List
    
      listAll();		void deleteById(Long id);}
    
   

           
            
             
         
            
    
             insert into brand (id,name) values (#{id},#{name})    
           
        	select * from brand            
    
         	delete from brand where id = #{id}    
    	    
   

           
            
             
             
             
         
            
    
             insert into product (name,price,brand_id) values (        	 #{name}, #{price}, #{brand_id}        )    
           
        	select * from product t1 left join brand t2 on t1.brand_id = t2.id     	where t1.brand_id = #{brand_id} order by price desc limit 0, 100    	    	 
        	select * from product where id = #{id}              
        	select * from product t1     	    	
    
     		delete from product where id = #{id}	
       	 
   

3)service

@Transactional@Servicepublic class BrandServiceImpl implements BrandService {		@Autowired	private BrandMapper brandMapper;	@Override	public List
   
     listAll() {		return this.brandMapper.listAll();	}	@Override	public void insertProducts(Brand brand) {		this.brandMapper.insertBrand(brand);	}	@Override	public void deleteBrand(Long id) {		this.brandMapper.deleteById(id);	}}
   

@Service@Transactionalpublic class ProductServiceImpl implements ProductService {		@Autowired	private ProductMapper productMapper;	@Override	public List
   
     findProductsByBrand(Long brand_id) {		return this.productMapper.findProductsByBrand(brand_id);	}	@Override	public void insertProducts(Product product) {		this.productMapper.insertProduct(product);	}	@Override	public Product findById(Long id) {		return this.productMapper.findById(id);	}	@Override	public List
    
      listAll() {		return this.productMapper.listAll();	}	@Override	public void deleteProduct(Long id) {		this.productMapper.deleteById(id);	}}
    
   

4）controller

@RestControllerpublic class BrandProductController {	@Autowired	private BrandService brandService;	@Autowired	private ProductService productService;	@GetMapping("/init")	public Object add() {		for (long i = 0; i < 2; i++) {			Brand brand = new Brand();			brand.setId(i);			brand.setName("品牌" + i);			this.brandService.insertProducts(brand);			// 插入商品			for (int j = 0; j < 200; j++) {				Product p = new Product();				p.setName("商品" + j);				p.setBrand_id(i);				p.setPrice(Math.random() * 10);				this.productService.insertProducts(p);			}		}		return "success";	}	/**	 * 删除全部	 */	@GetMapping("/deleteAll")	public Object deleteAll() {		List
   
     brands = this.brandService.listAll();		for (Brand brand : brands) {			this.brandService.deleteBrand(brand.getId());		}		List
    
      listAll = this.productService.listAll();		for (Product product : listAll) {			this.productService.deleteProduct(product.getId());		}		return "success";	}	/**	 * 查询品牌	 */	@GetMapping("/findByBrand")	public Object findByBrand(Long id) {		List
     
       list = this.productService.findProductsByBrand(id);		return list;	}}
     
    
   

五、测试：符合预期！！

转载地址：http://rhuni.baihongyu.com/