正文

springboot with appache sharding 3.1 单库分表

IP云
IP云V管理员/5 阅读
11/29
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

配置文件相关信息： 

#开发

server.port=7200

spring.application.name=BtspIsmpServiceOrderDev

eureka.client.serviceUrl.defaultZone=http://127.0.0.1:7761/eureka/

#表示eureka client间隔多久去拉取服务器注册信息,默认为30秒

eureka.client.registry-fetch-interval-seconds=10

#eureka客户端需要多长时间发送心跳给eureka服务器，表明它仍然活着，默认30秒

eureka.instance.lease-renewal-interval-in-seconds=5

#eureka服务器在接受到实力的最后一次发出的心跳后，需要等待多久才可以将此实例删除

eureka.instance.lease-expiration-duration-in-seconds=30

eureka.instance.health-check-url-path=/actuator/health

eureka.instance.hostname=${spring.cloud.client.ip-address}

eureka.instance.instance-id=${spring.cloud.client.ip-address}:${server.port}

eureka.instance.prefer-ip-address=true

management.endpoints.web.exposure.include=*

management.endpoint.health.show-details=ALWAYS

#log

logging.config=classpath:spring-logback.xml

logging.path=logs/btspismp/serviceorder

#出现错误时, 直接抛出异常

spring.mvc.throw-com.eshore.exception-if-no-handler-found=true

spring.main.allow-bean-definition-overriding=true

#文件上传大小限制，默认使用tomcat的上传文件大小限制，即1MB

spring.multipart.maxFileSize=10

spring.multipart.maxRequestSize=10

# 数据库访问配置

sharding.jdbc.datasource.names=btspismp

sharding.jdbc.datasource.btspismp.driver-class-name=net.sf.log4jdbc.DriverSpy

sharding.jdbc.datasource.btspismp.jdbc-url=jdbc:log4jdbc:mysql://192.168.115.31:3306/btspIsmp?characterEncoding=UTF-8&autoReconnect=true&useUnicode=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&useSSL=false

sharding.jdbc.datasource.btspismp.username=btspIsmp

sharding.jdbc.datasource.btspismp.password=iSMp123#@!

#所有数据节点

sharding.jdbc.config.sharding.tables.T_TMP_TEST.actual-data-nodes=btspismp.T_TMP_TEST_1,btspismp.T_TMP_TEST_2

#根据这个列分表

#sharding.jdbc.config.sharding.tables.T_TMP_TEST.table-strategy.inline.sharding-column=age

#sharding.jdbc.config.sharding.tables.T_TMP_TEST.table-strategy.inline.algorithm-expression=T_TMP_TEST_$->{age %3}

#分片列

sharding.jdbc.config.sharding.tables.T_TMP_TEST.table-strategy.standard.sharding-column=age

#IN,=

sharding.jdbc.config.sharding.tables.T_TMP_TEST.table-strategy.standard.precise-algorithm-class-name=com.eshore.ismp.config.TestTableShardingConfig

#BETWEEN

#sharding.jdbc.config.sharding.tables.T_TMP_TEST.table-strategy.standard.range-algorithm-class-name=com.eshore.ismp.config.OrderAcceptTableShardingConfig

#工单分表

sharding.jdbc.config.sharding.tables.T_ORDER_ACCEPT.actual-data-nodes=btspismp.T_ORDER_ACCEPT,btspismp.T_ORDER_ACCEPT_CRBT

sharding.jdbc.config.sharding.tables.T_ORDER_ACCEPT.table-strategy.standard.sharding-column=k_785_product_type

sharding.jdbc.config.sharding.tables.T_ORDER_ACCEPT.table-strategy.standard.precise-algorithm-class-name=com.eshore.ismp.config.OrderAcceptTableShardingConfig

sharding.jdbc.datasource.btspismp.type=com.zaxxer.hikari.HikariDataSource

spring.datasource.hikari.minimum-idle=5

spring.datasource.hikari.maximum-pool-size=15

spring.datasource.hikari.auto-commit=true

spring.datasource.hikari.idle-timeout=30000

spring.datasource.hikari.pool-name=DatebookHikariCP

spring.datasource.hikari.max-lifetime=30000

spring.datasource.hikari.connection-timeout=30000

spring.datasource.hikari.connection-test-query=SELECT 1

spring.datasource.hikari.leak-detection-threshold=1000

#JPA

spring.jpa.database=MYSQL

spring.jpa.show-sql=true

spring.jpa.hibernate.ddl-auto=none

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

#spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

#缓存配置redis

spring.cache.type=redis

spring.data.redis.repositories.enabled=false

# Redis数据库索引（默认为0）

spring.redis.database=0

# Redis服务器地址

spring.redis.host=192.168.115.31

# Redis服务器连接端口

spring.redis.port=6379

# Redis服务器连接密码（默认为空）

spring.redis.password=

# 连接池最大连接数（使用负值表示没有限制）

spring.redis.lettuce.pool.max-active=8

# 连接池最大阻塞等待时间（使用负值表示没有限制）

spring.redis.lettuce.pool.max-wait=-1ms

# 连接池中的最大空闲连接

spring.redis.lettuce.pool.max-idle=8

# 连接池中的最小空闲连接

spring.redis.lettuce.pool.min-idle=0

# 连接超时时间（毫秒）

spring.redis.timeout=5000ms

分表算法： 

package com.eshore.ismp.config;

import java.util.Collection;

import java.util.LinkedHashSet;

import java.util.LinkedList;

import java.util.List;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.stereotype.Component;

import groovy.util.logging.Slf4j;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;

import io.shardingsphere.api.algorithm.sharding.RangeShardingValue;

import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

import io.shardingsphere.api.algorithm.sharding.standard.RangeShardingAlgorithm;

@Component

public class OrderAcceptTableShardingConfig implements PreciseShardingAlgorithm<String>{

private Logger log=LoggerFactory.getLogger(this.getClass());

	@Override

	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {

		 for(String tableName:availableTargetNames) {

			 log.info("=====tableName:"+tableName);

		 }

		 log.info("=====shardingValue:"+shardingValue);

		 log.info("====logic table name:"+shardingValue.getLogicTableName());

		 log.info("====column name:"+shardingValue.getColumnName());

		 log.info("====sharding value String:"+shardingValue.toString());

		 int start=shardingValue.toString().indexOf("value=");

		 int end=shardingValue.toString().indexOf(")");

		 log.info("length"+shardingValue.toString().length()+",start:"+start+",end:"+end);

		 log.info("Value:"+shardingValue.toString().substring(start+6,end));

		 String valueString=shardingValue.toString().substring(start+6,end);

		 if(valueString.contentEquals("SWCL")) {

			 return "T_ORDER_ACCEPT_CRBT";

		 }else {

			 return "T_ORDER_ACCEPT";

		 }

	}

}

测试类： 

@RunWith(SpringRunner.class)

@SpringBootTest

public class BaseTest {

    @Autowired

    private ITbTestService tbTestService;

    @Autowired

    private JdbcTemplate jdbcTemplate;

    @Autowired

    private OrderAcceptSpsAttrJdbcDao jdbcDao;

    @Autowired

    private IOrderAcceptSpsAttrValueService orderSpsAttrValService;

    @Autowired

    private OrderAcceptStatusChangRecordJdbcDao orderAcceptStatusChangRecordJdbcDao;

    @Autowired

    private IOrderAcceptService orderAcceptService;

    @Autowired

    private ITestService testService;

@Test

    public void insertOrder() {

    	OrderAccept accept=new OrderAccept();

    	accept.setK785ProductType("SWCL");

    	accept.setCreateTime(new Date());

        orderAcceptService.save(accept);

        accept=new OrderAccept();

        accept.setK785ProductType("1");

        accept.setCreateTime(new Date());

        orderAcceptService.save(accept);

        System.out.println("============END=========");

    }

    @Test

    public void insertTest() {

    	TestEntity entity=new TestEntity();

    	entity.setAge(1);

    	testService.save(entity);

    	entity=new TestEntity();

    	entity.setAge(2);

    	testService.save(entity);

        System.out.println("============END=========");

    }

}

POM.XML: 

 <dependency>

		    <groupId>io.shardingsphere</groupId>

		    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>

		    <version>3.1.0</version>

		</dependency>

		<dependency>

		    <groupId>io.shardingsphere</groupId>

		    <artifactId>sharding-jdbc-spring-namespace</artifactId>

		    <version>3.1.0</version>

		</dependency>

		<dependency>

            <groupId>org.projectlombok</groupId>

            <artifactId>lombok</artifactId>

        </dependency>

这个版本没分表的数据分页有问题，怎么分页都是limit从0开始，不建议使用

其他的具体DAO和service和其他的一样。

相关阅读

目录[+]