一，为什么要使用druid数据源?
1,druid的优点
Druid是阿里巴巴开发的号称为监控而生的数据库连接池
它的优点包括：
可以监控数据库访问性能
SQL执行日志
SQL防火墙
但spring boot和shardingjdbc默认使用的数据库连接池是 HikariCP
如果要在shardingsphere中使用druid,需要在项目中整合后才能生效
2,druid的官方代码站:
https://github.com/alibaba/druid/
说明：刘宏缔的架构森林是一个专注架构的博客，地址：https://www.cnblogs.com/architectforest
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，演示项目的相关信息
1，项目地址:
https://github.com/liuhongdi/shardingdruid
2, 功能说明：
为shardingsphere配置druid数据源，
监控功能需要可用
3，项目结构：如图:
4,用到的数据表:如图:
三，配置文件说明
1,pom.xml
<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>org.mybatis.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
server.error.include-stacktrace=always #thymeleaf
spring.thymeleaf.cache=false
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.mode=HTML
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html #shardingsphere
spring.shardingsphere.datasource.names=saleorder01,saleorder02
spring.shardingsphere.datasource.saleorder01.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saleorder01.driver-class-name=com.mysql.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.datasource.saleorder01.testWhileIdle=true
spring.shardingsphere.datasource.saleorder01.testOnBorrow=false
spring.shardingsphere.datasource.saleorder01.testOnReturn=false
spring.shardingsphere.datasource.saleorder01.poolPreparedStatements=true
spring.shardingsphere.datasource.saleorder01.maxPoolPreparedStatementPerConnectionSize=20
spring.shardingsphere.datasource.saleorder01.filters=stat,wall,log4j2
spring.shardingsphere.datasource.saleorder01.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 spring.shardingsphere.datasource.saleorder02.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.saleorder02.driver-class-name=com.mysql.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.datasource.saleorder02.connectionProperties=druid.stat.mergeSql\=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/*Mapper.xml
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
说明：使用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=0 DEFAULT 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 = shardingValue.getValue();
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 = shardingValue.getValue();
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_INS_FAIL.getMsg());
}
} //添加订单的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 = orderService.getAllOrder();
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() {
return this.orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
//商品名字
private String goodsName;
public String getGoodsName() {
return this.goodsName;
}
public void setGoodsName(String goodsName) {
this.goodsName = goodsName;
}
}
8,OrderMapper.xml
<?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.shardingdruid.demo.mapper.OrderMapper" >
<resultMap id="BaseResultMap" type="com.shardingdruid.demo.pojo.Order" >
<result column="orderId" property="orderId" jdbcType="BIGINT" />
<result column="goodsName" property="goodsName" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
orderId, goodsName
</sql>
<insert id="insertOneOrder" parameterType="com.shardingdruid.demo.pojo.Order" >
INSERT ignore INTO
t_order
(orderId,goodsName)
VALUES
(#{orderId},#{goodsName})
</insert>
<select id="selectAllOrder" resultType="com.shardingdruid.demo.pojo.Order">
select * from t_order order by orderId desc
</select>
</mapper>
9,其他代码，可以从github上参考
五，测试效果
1,查看订单列表，访问:
http://127.0.0.1:8080/order/list/
效果如图:
2,查看druid,访问:
http://127.0.0.1:8080/druid
登录后可以看到我们建立的连接:如图:
六，查看spring boot的版本:
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.3.RELEASE)
