Spring Boot集成Druid、MyBatis基本步骤
- Jar包引入
- application.yml配置数据源信息
- 指定MyBatis mapper路径
- 可使用MyBatis generatorConfig生成Mapper Domain等,后修改不合适的命名,删除不需要的接口等
- 数据库操作测试,Druid监控界面查看SQL性能等指标
POM依赖
使用的版本
<druid.version>1.1.10</druid.version>
<oracle.version>12.1.0.1.0</oracle.version>
<mybatis.version>1.3.2</mybatis.version>
具体jar
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- db connect 根据数据库选择其中之一即可 -->
<!-- oracle -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc7</artifactId>
<version>${oracle.version}</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Yml Datasource及Mapper Location配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:oracle:thin:@10.92.3.114:1521/atstestdb
username: jats001
password: jats001
driver-class-name: oracle.jdbc.driver.OracleDriver
max-active: 10
max-wait: 10000
# mybatis mapper locations
mybatis:
mapper-locations:
- classpath*:mapper/*Mapper.xml
为什么Durid数据源配置spring.datasource打头
可以查看DruidDataSourceAutoConfigure.class中数据源的配置格式,还是以spring.datasource开头的。
DruidDataSource的配置属性(除url,driverClassName,username,password等)是以spring.datasource.druid打头的
如何自定义DataSource
从下图可以看出,如果DataSource不存在时,DataSourceAutoConfiguration会装载DataSource Bean,
会读取spring.datasource下是否有相应的数据库配置和DriverClass,没有或者配置错误的话会报如下错误:
[demo] [DEBUG] [2019-11-14 19:14:13.242] [o.s.b.d.LoggingFailureAnalysisReporter:report:37] [main] Application failed to start due to an exception
org.springframework.boot.autoconfigure.jdbc.DataSourceProperties$DataSourceBeanCreationException: Failed to determine a suitable driver class
at org.springframework.boot.autoconfigure.jdbc.DataSourceProperties.determineDriverClassName(DataSourceProperties.java:233)
at org.springframework.boot.autoconfigure.jdbc.DataSourceProperties.determineUsername(DataSourceProperties.java:327)
at com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceWrapper.afterPropertiesSet(DruidDataSourceWrapper.java:45)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1862)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1799)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:874)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:778)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:528)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1287)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1207)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireByType(AbstractAutowireCapableBeanFactory.java:1518)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1413)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.resolveBeanByName(AbstractAutowireCapableBeanFactory.java:454)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:543)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:513)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:653)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:224)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:116)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:334)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1429)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:207)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.resolveBeanByName(AbstractAutowireCapableBeanFactory.java:454)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:543)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:513)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:653)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:224)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:116)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessProperties(CommonAnnotationBeanPostProcessor.java:334)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1429)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:879)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:878)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
at com.springboot.demo.DemoWebApplication.main(DemoWebApplication.java:10)
可以自定义一个DataSource。如application.yml中配置为:
spring:
datasource:
druid:
url: jdbc:oracle:thin:@127.0.0.1:1521/db
username: root
password: root
driver-class-name: oracle.jdbc.driver.OracleDriver
max-active: 10
max-wait: 10000
自定义DuridConfig.java创建DataSource,这样就不依赖DataSourceAutoConfiguration创建DataSource,也不会加载配置文件中spring.datasource.driver-class-name这些配置了。
package com.springboot.demo.service.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource.druid")
@Bean
public DataSource druid(){
return new DruidDataSource();
}
}
利用MyBatis generatorConfig生成Mapper及实体类
resources目录下新建generatorConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC " -//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<classPathEntry
location="/Users/zangdaiyang/software/repository/com/oracle/ojdbc7/12.1.0.1.0/ojdbc7-12.1.0.1.0.jar"/>
<context id="my" targetRuntime="MyBatis3">
<commentGenerator>
<property name="suppressDate" value="false"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<jdbcConnection driverClass="oracle.jdbc.driver.OracleDriver"
connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:db" userId="root"
password="root"/>
<javaModelGenerator targetPackage="com.springboot.demo.service.dao.domain"
targetProject="/Users/zangdaiyang/code/demo/demo-service/src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<sqlMapGenerator targetPackage="resources.mapper"
targetProject="/Users/zangdaiyang/code/demo/demo-service/src/main">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<javaClientGenerator targetPackage="com.springboot.demo.service.dao.mapper"
targetProject="/Users/zangdaiyang/code/demo/demo-service/src/main/java" type="XMLMAPPER">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<table tableName="z_users" domainObjectName="User"
enableCountByExample="false" enableUpdateByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
selectByExampleQueryId="false">
</table>
</context>
</generatorConfiguration>
Pom中添加插件
<build>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>${mybatis.version}</version>
<configuration>
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
</plugin>
</plugins>
</build>
IDEA中操作双击运行该插件即生成xml、Mapper、实体类
改造生成的mapper(添加Mapper注解),添加需要的方法
package com.springboot.demo.service.dao.mapper;
import com.springboot.demo.service.dao.domain.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper {
int deleteByPrimaryKey(String urid);
int insert(User record);
int insertSelective(User record);
User selectByPrimaryKey(String urid);
int updateByPrimaryKeySelective(User record);
int updateByPrimaryKey(User record);
// 对应UserMapper.xml也要添加元素
User selectByUserId(String userId);
}
测试
新建UserService
package com.springboot.demo.web.service;
import com.springboot.demo.service.dao.domain.User;
import com.springboot.demo.service.dao.mapper.UserMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class UserService {
@Resource
private UserMapper userMapper;
public boolean addUser(User user) {
return userMapper.insert(user) > 0;
}
public User queryUserById(String userId) {
return userMapper.selectByPrimaryKey(userId);
}
}
新建Controller
package com.springboot.demo.web.controller;
import com.springboot.demo.service.dao.domain.User;
import com.springboot.demo.web.model.ResultInfo;
import com.springboot.demo.web.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
@RestController
@Slf4j
public class UserController {
@Resource
private UserService userService;
@GetMapping("/user/queryUserById/{userId}")
public User queryUserById(@PathVariable String userId) {
log.info("Query user by Id.");
return userService.queryUserById(userId);
}
@PostMapping("/user/add")
public ResultInfo addUser(@RequestBody User user) {
log.info("Add user.");
userService.addUser(user);
return new ResultInfo();
}
}
启动服务,Postman发送请求调用插入和查询,后进入Durid监控界面查看sql性能等
进入http://localhost:10095/demo/druid/sql.html查看 (Durid连接池不仅有日志监控,还能有效的监控DB池连接和SQL的执行情况,web-stat-filter默认为true-开启状态)
MyBatis PageHelper分页
Pom依赖
使用版本
<pagehelper.version>1.2.3</pagehelper.version>
jar包引入
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${pagehelper.version}</version>
</dependency>
Yml中添加
pagehelper:
helperDialect: oracle/mysql
reasonable: true
supportMethodsArguments: true
pageSizeZero: true
params: count=countSql
查询中分页
UserController新增
@GetMapping("/user/query")
public List<User> queryUsers(@PathParam("pageNum") int pageNum, @PathParam("pageSize") int pageSize) {
log.info("Query users.");
return userService.selectAll(pageNum, pageSize);
}
UserService新增
public List<User> selectAll(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
return userMapper.selectAll();
}
参考:
Druid连接池的意义以及使用
Spring Boot2.0配置Druid数据库连接池(单数据源、多数据源、数据监控)
Spring Boot 整合 PageHelper
Spring插件之PageHelper(一)的配置
了解Spring Boot的自动配置