springboot druid的坑 解决springboot整合druid遇到的坑
weixin_43845184 人气:0想了解解决springboot整合druid遇到的坑的相关内容吗,weixin_43845184在本文为您仔细讲解springboot druid的坑的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:springboot整合druid,druid的坑,下面大家一起来学习吧。
springboot整合druid的坑
项目环境
- 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 # 下面为连接池的补充设置,应用到上面所有数据源中 # 初始化大小,最小,最大 initialSize: 5 minIdle: 5 maxActive: 20 # 配置获取连接等待超时的时间 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
配置数据源时有个问题:username,password,url,driver-class-name既可以配在spring.datasource下面,也可以配置在spring.datasource.druid下面。
如果直接配在spring.datasource下面,当配置多个数据源时,不方便指定前缀。
如果配置在spring.datasource.druid下面,需要在配置类(@Configuration)中获取相应的值。如果不配置会报找不到url,driver-class-name的错。
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的方式,如果不配置会一直找不到sql //默认使用注解的方式 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/*"); //控制台管理用户,加入下面2行 进入druid后台就需要登录 //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慢的标准,执行时间超过slowSqlMillis的就是慢。 statFilter.setMergeSql(true); //SQL合并配置 statFilter.setSlowSqlMillis(1000);//slowSqlMillis的缺省值为3000,也就是3秒。 return statFilter; } @Bean public WallFilter wallFilter(){ WallFilter wallFilter = new WallFilter(); //允许执行多条SQL WallConfig config = new WallConfig(); config.setMultiStatementAllow(true); wallFilter.setConfig(config); return wallFilter; } }
这里有个坑:找不到xml中的sql
org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.test.page.dao.UserDao.findById
出现上面问题的原因有很多。
例如:
1:检查xml文件所在package名称是否和Mapper interface所在的包名一一对应;
2:检查xml的namespace是否和xml文件的package名称一一对应;
3:检查方法名称是否对应;
如果不是上面这几种问题:
在配置类中添加setMapperLocations(),并且指定xml的位置
@Bean("mysqlSqlSessionFactory") public SqlSessionFactory mysqlSqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(mysqlDataSource()); //使用xml的方式,如果不配置会一直找不到sql //默认使用注解的方式 sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath:mapper/*.xml")); return sqlSessionFactoryBean.getObject(); }
springboot整合alibaba/druid
一,用快速建立建立spring boot项目
springboot 版本号为1.5.12和选择相对应的工具(如版本不一致会导致运行错误)
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 # 数据源其他配置 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 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'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后台管理的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", "");//默认就是允许所有访问 initParams.put("deny", "192.168.15.21");//禁止访问的ip bean.setInitParameters(initParams); return bean; } /** * 配置druid后台管理的filter * * @return */ @Bean public FilterRegistrationBean druidFilter() { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String, String> initParams = new HashMap<>(); //设置不拦截的路径 *.cs *.js /druid/* initParams.put("exclusions","*.js,*.css,/druid/*"); bean.setInitParameters(initParams); //设置filter拦截 那些请求 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) { //-自动使用驼峰命名属性映射字段 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); }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容