解決springboot整合druid遇到的坑

更新時(shí)間：2021年08月12日 12:01:19 作者：weixin_43845184

這篇文章主要介紹了解決springboot整合druid遇到的坑，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

springboot整合druid的坑

項(xiàng)目環(huán)境

springboot 2.1.6.RELEASE
jdk 1.8

pom.xml配置

<?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 https://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.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.test</groupId>
    <artifactId>page</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>page</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <druid.version>1.1.10</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

application.yml

server:
  port: 8001
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      mysql:
        name: mysql
        url: jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
        # 下面為連接池的補(bǔ)充設(shè)置，應(yīng)用到上面所有數(shù)據(jù)源中
        # 初始化大小，最小，最大
        initialSize: 5
        minIdle: 5
        maxActive: 20
        # 配置獲取連接等待超時(shí)的時(shí)間
        maxWait: 60000
#      oracle:
#        name: oracle
#        url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
#        username: zs
#        password: 123456
#        initialSize: 5
#        minIdle: 5
#        maxActive: 20
#        maxWait: 60000
mybatis:
  mapper-locations:
  - classpath*:mapper/*.xml

配置數(shù)據(jù)源時(shí)有個(gè)問題：username,password,url,driver-class-name既可以配在spring.datasource下面，也可以配置在spring.datasource.druid下面。

如果直接配在spring.datasource下面，當(dāng)配置多個(gè)數(shù)據(jù)源時(shí)，不方便指定前綴。

如果配置在spring.datasource.druid下面，需要在配置類（@Configuration）中獲取相應(yīng)的值。如果不配置會(huì)報(bào)找不到url,driver-class-name的錯(cuò)。

DruidDataSourceConfiguration配置類

package com.test.page.config;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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.core.io.support.PathMatchingResourcePatternResolver;
@Configuration
@MapperScan(sqlSessionTemplateRef = "mysqlSqlSessionTemplate",basePackages = "com.test.page.dao")
public class DruidDataSourceConfiguration {
    private static final String PRE="spring.datasource.druid.mysql.";
    @Value("${"+PRE+"url}")
    private String url;
    @Value("${"+PRE+"driver-class-name}")
    private String driverClassName;
    @Value("${"+PRE+"username}")
    private String username;
    @Value("${"+PRE+"password}")
    private String password;
    @Value("${"+PRE+"initialSize}")
    private int initialSize;
    @Value("${"+PRE+"maxActive}")
    private int maxActive;
    @Value("${"+PRE+"minIdle}")
    private int minIdle;
    @Value("${"+PRE+"maxWait}")
    private long maxWait;
    @Bean("mysqlDataSource")
    public DruidDataSource mysqlDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setPassword(password);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMaxWait(maxWait);
        return druidDataSource;
    }
    @Bean("mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(mysqlDataSource());
        //使用xml的方式，如果不配置會(huì)一直找不到sql
        //默認(rèn)使用注解的方式
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
    @Bean("mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate()throws Exception{
        return new SqlSessionTemplate(mysqlSqlSessionFactory());
    }
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //控制臺(tái)管理用戶，加入下面2行 進(jìn)入druid后臺(tái)就需要登錄
        //servletRegistrationBean.addInitParameter("loginUsername", "admin");
        //servletRegistrationBean.addInitParameter("loginPassword", "admin");
        return servletRegistrationBean;
    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }
    @Bean
    public StatFilter statFilter(){
        StatFilter statFilter = new StatFilter();
        statFilter.setLogSlowSql(true); //slowSqlMillis用來配置SQL慢的標(biāo)準(zhǔn)，執(zhí)行時(shí)間超過slowSqlMillis的就是慢。
        statFilter.setMergeSql(true); //SQL合并配置
        statFilter.setSlowSqlMillis(1000);//slowSqlMillis的缺省值為3000，也就是3秒。
        return statFilter;
    }
    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();
        //允許執(zhí)行多條SQL
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);
        return wallFilter;
    }
}

這里有個(gè)坑：找不到xml中的sql

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.test.page.dao.UserDao.findById

出現(xiàn)上面問題的原因有很多。

例如：

1：檢查xml文件所在package名稱是否和Mapper interface所在的包名一一對(duì)應(yīng)；

2：檢查xml的namespace是否和xml文件的package名稱一一對(duì)應(yīng)；

3：檢查方法名稱是否對(duì)應(yīng)；

如果不是上面這幾種問題：

在配置類中添加setMapperLocations(),并且指定xml的位置

@Bean("mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(mysqlDataSource());
        //使用xml的方式，如果不配置會(huì)一直找不到sql
        //默認(rèn)使用注解的方式
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

springboot整合alibaba/druid

一，用快速建立建立spring boot項(xiàng)目

springboot 版本號(hào)為1.5.12和選擇相對(duì)應(yīng)的工具（如版本不一致會(huì)導(dǎo)致運(yùn)行錯(cuò)誤）

pom.xml中添加

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.1.9</version>
</dependency>
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>

二添加yml文件

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/dept?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
#   數(shù)據(jù)源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
#   配置監(jiān)控統(tǒng)計(jì)攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，'wall'用于防火墻
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

三建立config包

3.1 建立dataSourcesConfig類

package cn.vp.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataSourcesConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource getDataSources() {
        return new DruidDataSource();
    }
    /**
     * 配置druid后臺(tái)管理的servlet
     *
     * @return
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "admin");
        initParams.put("allow", "");//默認(rèn)就是允許所有訪問
        initParams.put("deny", "192.168.15.21");//禁止訪問的ip
        bean.setInitParameters(initParams);
        return bean;
    }
    /**
     * 配置druid后臺(tái)管理的filter
     *
     * @return
     */
    @Bean
    public FilterRegistrationBean druidFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String, String> initParams = new HashMap<>();
        //設(shè)置不攔截的路徑  *.cs *.js    /druid/*
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        //設(shè)置filter攔截 那些請(qǐng)求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

3.2 建立MybatisConfig類

package cn.vp.config;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MyBatisConfig {
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                //-自動(dòng)使用駝峰命名屬性映射字段   userId    user_id
                configuration.setMapUnderscoreToCamelCase(true);
                //使用列別名替換列名 select user as User
                configuration.setUseColumnLabel(true);
            }
        };
    }
}

四，dao層的建立

package cn.vp.dao;
import cn.vp.entity.Department;
import org.apache.ibatis.annotations.*;
@Mapper
public interface DepartmentDao {
    @Insert("INSERT INTO `Department` (`departmentName`) VALUES (#{departmentName})")
    public int addDepartment(Department department);
    @Delete("DELETE FROM  department where id=#{id}")
    public int deleteById(Integer id);
    @Update("UPDATE `Department` SET `departmentName`=#{departmentName} WHERE (`id`=#{id})")
    public int updateDepartment(Department department);
    @Select("SELECT  * from department where id=#{id}")
    public Department queryById(@Param("id") Integer id);
}

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: