SpringBoot整合mybatis使用Druid做連接池的方式

更新時(shí)間：2023年08月25日 09:13:36 作者：hzau_itdog

這篇文章主要介紹了SpringBoot整合mybatis使用Druid做連接池的方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

項(xiàng)目構(gòu)建

使用的spring官方的eclipse版本為sts-4.0.1.RELEASE

下一步

finish

pom文件修改

增加配置

<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.0</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.6</version>
		</dependency>

完整pom如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.waterelephant</groupId>
	<artifactId>mybatis-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>mybatis-demo</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.0</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.6</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

修改配置文件

修改application.yml

application.yml配置如下

server:
  port: 8089
#默認(rèn)使用配置
spring:
  profiles:
    active: dev
#公共配置與profiles選擇無關(guān) mapperLocations指的路徑是src/main/resources
#公共配置與profiles選擇無關(guān)
mybatis:
#配置別名掃描包
  typeAliasesPackage: com.example.demo.model
#配置mapper.xml文件的位置
  mapperLocations: classpath:mapper/*.xml
---
#開發(fā)配置
spring:
  profiles: dev
  datasource:
    url: jdbc:mysql://ip:3306/beadwalletloan?autoReconnect=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useServerPrepStmts=false&rewriteBatchedStatements=true&serverTimezone=GMT%2B8
    username: username
    password: password
    driverClassName: com.mysql.jdbc.Driver
    # 使用druid數(shù)據(jù)源
    type: com.alibaba.druid.pool.DruidDataSource
    # 配置獲取連接等待超時(shí)的時(shí)間
    # 下面為連接池的補(bǔ)充設(shè)置，應(yīng)用到上面所有數(shù)據(jù)源中
    # 初始化大小，最小，最大
    initialSize: 1
    minIdle: 3
    maxActive: 20
    # 配置獲取連接等待超時(shí)的時(shí)間
    maxWait: 60000
    # 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接，單位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一個(gè)連接在池中最小生存的時(shí)間，單位是毫秒
    minEvictableIdleTimeMillis: 30000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打開PSCache，并且指定每個(gè)連接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，'wall'用于防火墻
    filters: stat,wall,slf4j
    # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多個(gè)DruidDataSource的監(jiān)控?cái)?shù)據(jù)
    useGlobalDataSourceStat: true

增加Druid的配置

package com.example.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
 * @ClassName DatasourceConfig
 * @Description TODO
 * @Author yueyiming
 * @Date 2019/4/3 15:20
 */
@Log4j2
@Configuration
public class DatasourceConfig {
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;
    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("${spring.datasource.connectionProperties}")
    private String connectionProperties;
    @Value("${spring.datasource.useGlobalDataSourceStat}")
    private boolean useGlobalDataSourceStat;
    @Bean
    @Primary
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);   //這里可以做加密處理
        datasource.setDriverClassName(driverClassName);
        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
        }
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }
    @Bean
    public ServletRegistrationBean druidServlet() {
        log.info("init Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名單
       // servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.1");
        // IP黑名單(共同存在時(shí)，deny優(yōu)先于allow)
        //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
        //控制臺(tái)管理用戶
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "9527");
        //是否能夠重置數(shù)據(jù) 禁用HTML頁面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

配置mapperScan掃描

在啟動(dòng)類上加上

@MapperScan(basePackages = {"com.example.*.mapper" }

至此配置完成

測(cè)試

mapper.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.example.demo.mapper.OperateBasicMapper">
    <select id="getById" resultType="map">
        select *
          from
            bw_operate_basic
            where
              id=#{id}
    </select>
</mapper>

mapper接口

package com.example.demo.mapper;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
/**
 * @ClassName OperateBasicMapper
 * @Description TODO
 * @Author yueyiming
 * @Date 2019/4/3 15:52
 */
public interface OperateBasicMapper {
     Map<String,Object> getById(@Param(value = "id") Long id);
}

service

package com.example.demo.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.demo.mapper.OperateBasicMapper;
import java.util.Map;
/**
 * @ClassName OperateBasicService
 * @Description TODO
 * @Author yueyiming
 * @Date 2019/4/3 16:02
 */
@Service
public class OperateBasicService {
    @Autowired
    private OperateBasicMapper operateBasicMapper;
    public Map<String,Object> getById(Long id){
        return operateBasicMapper.getById(id);
    }
}

controller

package com.example.demo.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.service.OperateBasicService;
/**
 * @ClassName TestController
 * @Description TODO
 * @Author yueyiming
 * @Date 2019/4/3 15:49
 */
@RestController
public class TestController {
    @Autowired
    private OperateBasicService operateBasicService;
    @RequestMapping(value="get/{id}")
    public Object test(@PathVariable Long id) {
        return operateBasicService.getById(id);
    }
}

訪問