spring boot:配置shardingsphere(sharding jdbc)使用druid数据源(druid 1.1.23 / sharding-jdbc 4.1.1 / mybatis / spring boot 2.3.3)

2022-07-16 21:46:52

一，为什么要使用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)