前言
在当前的项目框架中,mybatis几乎是最常用的数据库orm框架,让我们更加简单的使用java代码操作数据库的各个字段。在真实的项目中,我们不仅仅只使用mybatis一种技术。加入了druid–数据库连接池,对连接数据库的操作做补充,加入通用mapper,对mybatis的简单数据库操作做补充,减少我们写代码的作业量。加入pageHelper插件,对mybatis的分页操作进行补充,单纯的mybatis,在项目开发中,会有一些不方便,键入上述的技术,都是为了简化项目开发。
完整的配置文件pom.xml 和 application.yml和 项目架构
pom.xml所需导入的包
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.0.RELEASE</spring-boot.version>
<druid.version>1.1.20</druid.version>
<druid-springboot.version>1.1.20</druid-springboot.version>
<mysql-jdbc.version>5.1.49</mysql-jdbc.version>
<mybatis-springboot.version>2.1.2</mybatis-springboot.version>
<tk-mybatis.version>2.0.2</tk-mybatis.version>
<!-- 特别注意版本问题 -->
<page.version>1.2.3</page.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--数据库-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid-springboot.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>${mysql-jdbc.version}</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-springboot.version}</version>
</dependency>
<!--通用mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>${tk-mybatis.version}</version>
</dependency>
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>${page.version}</version>
</dependency>
</dependencies>
application.yml的相关配置
server:
port: 8080
servlet:
context-path: /demo
#数据库连接信息,druid的配置
spring:
application:
# 应用名称
name: node07-boot-druid
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
url: jdbc:mysql://127.0.0.1:3306/dr?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: root
password: root123
driver-class-name: com.mysql.jdbc.Driver
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: false
max-pool-prepared-statement-per-connection-size: 20
validation-query: SELECT 1 FROM DUAL
test-on-borrow: false
test-on-return: false
test-while-idle: true
time-between-eviction-runs-millis: 60000
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
filters: stat,wall
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: admin
login-password: 123
# mybatis和通用mapper的配置
mybatis:
type-aliases-package: com.study.pagehelper.pojo
mapper:
mappers: com.study.pagehelper.commdao.BaseMapper
项目架构
1.Druid的使用
1)jar包导入
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
2)相关配置涵义
spring:
application:
# 应用名称
name: node07-boot-druid
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
url: jdbc:mysql://127.0.0.1:3306/dr?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: root
password: root123
#初始化连接个数
initial-size: 10
#最大连接池数量
max-active: 100
#最小连接池的数量
min-idle: 10
#获取连接的最大等待时间
max-wait: 60000
#是否支持缓存,orcale(支持游标的数据库)支持提高性能,mysql建议关闭
pool-prepared-statements: false
#缓存每个连接的大小PSCache
max-pool-prepared-statement-per-connection-size: 20
#检测查询语句是否有效
validation-query: SELECT 1 FROM DUAL
# validation-query-timeout: 5000
#申请连接时执行上述检测
test-on-borrow: false
#归还连接时执行上述连接
test-on-return: false
#检测查询,建议打开不影响性能,如果空闲时间大于time-between-eviction-runs-millis,执行检测
test-while-idle: true
#检测连接的空闲时间
time-between-eviction-runs-millis: 60000
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#filters: #配置多个英文逗号分隔(统计,sql注入,log4j过滤)
filters: stat,wall
#配置监控单元
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: admin
login-password: 123
3)druid的监控
druid的监控相关写在另一篇文章中
2.通用mapper的使用
1)jar包导入
<!--通用mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.2</version>
</dependency>
2)通用mpper的配置和使用
# 通用mapper的配置,配置初始化通用Mapper层,共用接口,之后项目中的接口都继承自本接口
mapper:
mappers: com.study.pagehelper.commdao.BaseMapper
BaseMapper的编写
package com.study.pagehelper.commdao;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
/**
* @ClassName BaseMapper
* @Author swj
* @Date 2020/10/9 14:23
*/
//和上述配置相同,使用注解或者在上述配置文件中配置
//@RegisterMapper
public interface BaseMapper<T> extends Mapper<T>, MySqlMapper<T> {
}
实体类—Subject的编写—形式为spring data Jpa中实体类的编写方式
package com.study.pagehelper.pojo;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @ClassName Subject
* @Author swj
* @Date 2020/10/9 11:26
*/
@Table(name = "subject")
public class Subject {
@Id
@Column(name = "guid")
private String guid;
@Column(name = "coding")
private String coding;
@Column(name = "createtime")
private String createtime;
@Column(name = "updatetime")
private String updatetime;
@Column(name = "subjectname")
private String subjectname;
@Column(name = "description")
private String description;
@Column(name = "remark")
private String remark;
public String getGuid() {
return guid;
}
public void setGuid(String guid) {
this.guid = guid;
}
public String getCoding() {
return coding;
}
public void setCoding(String coding) {
this.coding = coding;
}
public String getCreatetime() {
return createtime;
}
public void setCreatetime(String createtime) {
this.createtime = createtime;
}
public String getUpdatetime() {
return updatetime;
}
public void setUpdatetime(String updatetime) {
this.updatetime = updatetime;
}
public String getSubjectname() {
return subjectname;
}
public void setSubjectname(String subjectname) {
this.subjectname = subjectname;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
return "Subject{" +
"guid='" + guid + '\'' +
", coding='" + coding + '\'' +
", createtime='" + createtime + '\'' +
", updatetime='" + updatetime + '\'' +
", subjectname='" + subjectname + '\'' +
", description='" + description + '\'' +
", remark='" + remark + '\'' +
'}';
}
}
在启动类上加上。Mapper的扫描 @MapperScan(“com.study.pagehelper.dao”)
package com.study.pagehelper;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@MapperScan("com.study.pagehelper.dao")
public class PagehelperApplication {
public static void main(String[] args) {
SpringApplication.run(PagehelperApplication.class, args);
}
}
3)测试使用
编写测试文件,
package com.study.pagehelper;
import com.study.pagehelper.dao.SubjectDAO;
import com.study.pagehelper.pojo.Subject;
import com.study.pagehelper.service.SubjectService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import tk.mybatis.spring.annotation.MapperScan;
import java.util.List;
@SpringBootTest
@MapperScan("com.study.pagehelper.dao")
class PagehelperApplicationTests {
@Autowired
public SubjectDAO subjectDAO;
@Autowired
public SubjectService subjectService;
//测试通用mapper
@Test
void txMapperTest() {
List<Subject> subjects = subjectDAO.selectAll();
System.out.println(subjects);
}
//测试pagehelper
@Test
void SubjectServiceTese() {
System.out.println(subjectService.getAllLimit(1,2));
}
}
3.pageHelper的使用
pageHelper的简单原理,分页插件的使用,在启动后,mybatis在动态代理的过程中,插件相关的功能融入分页的功能。并不是直接对所有的返回list进行操作
package com.study.pagehelper.service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.study.pagehelper.dao.SubjectDAO;
import com.study.pagehelper.pojo.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @ClassName SubjectService
* @Author swj
* @Date 2020/10/9 14:40
*/
@Service
public class SubjectService {
@Autowired
SubjectDAO subjectDAO;
public PageInfo<Subject> getAllLimit(Integer start,Integer limit) {
//开启分页功能
PageHelper.startPage(start,limit);
List<Subject> subjectList = subjectDAO.selectAll();
//封装分页信息
PageInfo<Subject> subejctPageInfo = new PageInfo<>(subjectList);
return subejctPageInfo;
}
}
测试可以用上面的测试文件测试是否成功