springboot：配置shardingsphere（shardingjdbc）使⽤dr。。。⼀，为什么要使⽤druid数据源?
1,druid的优点
Druid是阿⾥巴巴开发的号称为监控⽽⽣的数据库连接池
它的优点包括：
可以监控数据库访问性能
SQL执⾏⽇志
SQL防⽕墙
但spring boot和shardingjdbc默认使⽤的数据库连接池是 HikariCP
如果要在shardingsphere中使⽤druid，需要在项⽬中整合后才能⽣效
2,druid的官⽅代码站:
github/alibaba/druid/
说明：作者：刘宏缔邮箱: 371125307@qq
⼆，演⽰项⽬的相关信息
1，项⽬地址:
github/liuhongdi/shardingdruid
2, 功能说明：
为shardingsphere配置druid数据源，
监控功能需要可⽤
3，项⽬结构：如图:
4,⽤到的数据表：如图:
三，配置⽂件说明
l
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<!--exclude log-->
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--druid begin-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>
<dependency>
<groupId>com.lmax</groupId>
<artifactId>disruptor</artifactId>
<version>3.4.2</version>
</dependency>
<!--sharding jdbc begin-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!--sharding jdbc  end-->
<!--mybatis begin-->
<dependency>
<groupId&batis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!--mybatis end-->
<!--pagehelper begin-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<!--pagehelper end-->
<!--mysql begin-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mysql end-->
<!--thymeleaf begin-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!--thymeleaf end-->
说明：1，为shardingsphere使⽤druid数据源时，不要使⽤: druid-spring-boot-starter这个包，因为它在会启动时⾃动从配置⽂件⽣成datasource,
所以在这⾥使⽤druid这个包
2，因为druid使⽤了log4j2，我们对spring-boot-starter-logging做了exclusion
2，配置application.properties:
#error
#thymeleaf
spring.thymeleaf.cache=false
ding=UTF-8
de=HTML
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
#shardingsphere
spring.shardingsphere.datasource.names=saleorder01,saleorder02
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saleorder01.sql.cj.jdbc.Driver
spring.shardingsphere.datasource.saleorder01.url=jdbc:mysql://127.0.0.1:3306/saleorder01?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder01.username=root
spring.shardingsphere.datasource.saleorder01.password=lhddemo
spring.shardingsphere.datasource.saleorder01.initial-size=5
spring.shardingsphere.datasource.saleorder01.min-idle=5
spring.shardingsphere.datasource.saleorder01.maxActive=20
spring.shardingsphere.datasource.saleorder01.maxWait=60000
spring.shardingsphere.datasource.saleorder01.timeBetweenEvictionRunsMillis=60000
spring.shardingsphere.datasource.saleorder01.minEvictableIdleTimeMillis=300000
spring.shardingsphere.datasource.saleorder01.validationQuery=SELECT 1
spring.shardingsphere.stWhileIdle=true
spring.shardingsphere.stOnBorrow=false
spring.shardingsphere.stOnReturn=false
spring.shardingsphere.datasource.saleorder01.poolPreparedStatements=true
spring.shardingsphere.datasource.saleorder01.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.saleorder01.filters=stat,wall,log4j2
spring.shardingsphere.tionProperties=Sql\=true;druid.stat.slowSqlMillis\=5000
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saleorder02.sql.cj.jdbc.Driver
spring.shardingsphere.datasource.saleorder02.url=jdbc:mysql://127.0.0.1:3306/saleorder02?characterEncoding=utf-8
spring.shardingsphere.datasource.saleorder02.username=root
spring.shardingsphere.datasource.saleorder02.password=lhddemo
spring.shardingsphere.datasource.saleorder02.filters=stat,wall,log4j2
spring.shardingsphere.tionProperties=Sql\=true;druid.stat.slowSqlMillis\=5000
spring.shardingsphere.sharding.default-data-source-name=saleorder01
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=orderId
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.shardingdruid.demo.algorithm.DatabasePreciseShardingAlgorithm spring.shardingsphere.sharding.binding-tables=t_order
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=saleorder0$->{1..1}.t_order_$->{1..2},saleorder0$->{2..2}.t_order_$->{3..4}
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.shardingdruid.demo.algorithm.OrderTablePreciseShardingAlgorithm spring.shardingsphere.props.sql.show=true
#mybatis
mybatis.mapper-locations=classpath:/mapper/*l
说明：使⽤druid数据源时，原有的shardingsphere配置中,jdbc-url要修改为url,
否则druid会报错
3，数据库和数据表:
我们创建两个库:saleorder01,saleorder02
然后在各个库内各创建两个数据表:
saleorder01库包括t_order_1,t_order_2
saleorder02库包括t_order_3,t_order_4
建表的sql:
CREATE TABLE `t_order_4` (
`orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`goodsName` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT'' COMMENT 'name',
PRIMARY KEY (`orderId`)
) ENGINE=InnoDB AUTO_INCREMENT=0DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='order4'
四，java代码说明
1,DatabasePreciseShardingAlgorithm.java
public class DatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
Long curValue = Value();
String curBase = "";
if (curValue > 0 && curValue<=200) {
curBase = "saleorder01";
} else {
curBase = "saleorder02";
}
return curBase;
}
}
分库的算法
2,OrderTablePreciseShardingAlgorithm.java
public class OrderTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {        Long curValue = Value();
String curTable = "";
if (curValue > 0 && curValue<=100) {
curTable = "t_order_1";
} else if (curValue > 100 && curValue<=200) {
curTable = "t_order_2";
} else if (curValue > 200 && curValue<=300) {
curTable = "t_order_3";
} else {
curTable = "t_order_4";
}
return curTable;
}
}
分表的算法
3,DruidConfig.java
@Configuration
public class DruidConfig {
/**
* Druid监控
*/
@Bean
public ServletRegistrationBean statViewServlet(){
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
Map<String,String> initParams = new HashMap<>();//这是配置的druid监控的登录密码
initParams.put("loginUsername","root");
initParams.put("loginPassword","root");
//默认就是允许所有访问
initParams.put("allow","127.0.0.1,192.168.3.4");
//⿊名单IP
initParams.put("deny","192.168.15.21");
bean.setInitParameters(initParams);
return bean;
}
/**
* web监控的filter
*/
@Bean
public FilterRegistrationBean webStatFilter(){
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
Map<String,String> initParams = new HashMap<>();
initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//过滤掉需要监控的⽂件
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return  bean;
}
}
说明：因为没有使⽤druid-spring-boot-starter，在代码中配置监控view
4,OrderController.java
@Controller
@RequestMapping("/order")
public class OrderController {
@Resource
private OrderService orderService;
//添加订单，参数：订单id/订单商品名字
@RequestMapping("/added")
@ResponseBody
public ServerResponseUtil added(@RequestParam(value="orderid",required = false,defaultValue = "0") Long orderId,                        @RequestParam(value="goodsname",required = false,defaultValue = "") String goodsName){
Order orderOne = new Order();
orderOne.setOrderId(orderId);
orderOne.setGoodsName(goodsName);
boolean isAdd = orderService.addOneOrder(orderOne);
System.out.println("isAdd:"+isAdd);
if (isAdd == true) {
return ServerResponseUtil.success("添加成功");
} else {
throw new ServiceException(ResponseCode.DATA_Msg());
}
}
//添加订单的form页⾯
@RequestMapping("/add")
public String add(){
return "order/add";
}
//订单的列表，参数：第⼏页
@GetMapping("/list")
public String list(Model model,
@RequestParam(value="currentPage",required = false,defaultValue = "1") Integer currentPage){        PageHelper.startPage(currentPage, Constant.ORDER_PAGE_SIZE);
List<Order> order_list = AllOrder();
model.addAttribute("order_list",order_list);
PageInfo<Order> pageInfo = new PageInfo<>(order_list);
model.addAttribute("pageInfo", pageInfo);
return "order/list";
}
}
5,OrderServiceImpl.java
@Service
public class OrderServiceImpl implements OrderService {
@Resource
private OrderMapper orderMapper;
//列出所有订单
@Override
public List<Order> getAllOrder() {
List<Order> order_list = orderMapper.selectAllOrder();
return order_list;
}
//添加订单
@Override
public boolean addOneOrder(Order orderOne) {
int num = orderMapper.insertOneOrder(orderOne);
if (num == 1) {
return true;
} else {
return false;
}
}
}
6,OrderMapper.java
@Repository
@Mapper
public interface OrderMapper {
int insertOneOrder(Order orderOne);
String selectNameById(String userId);
List<Order> selectAllOrder();
}
7,Order.java
public class Order {
/
/订单id
private Long orderId;
public Long getOrderId() {
derId;
}
public void setOrderId(Long orderId) {
}
//商品名字
private String goodsName;
public String getGoodsName() {
dsName;thymeleaf用法
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。