1.配置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 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.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.czhappy.bootlearn</groupId>
<artifactId>03-Spring-Boot-MyBatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>03-Spring-Boot-MyBatis</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-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 数据库 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- Druid Pool -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
</dependencies> <build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build> </project>

2.修改配置文件application.yml:

server:
port: 8080
servlet:
context-path: /web spring:
datasource:
druid:
# 数据库访问配置, 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/springbootlearn?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username: root
password: root
# 连接池配置
initial-size: 5
min-idle: 5
max-active: 20
# 连接等待超时时间
max-wait: 30000
# 配置检测可以关闭的空闲连接间隔时间
time-between-eviction-runs-millis: 60000
# 配置连接在池中的最小生存时间
min-evictable-idle-time-millis: 300000
validation-query: select 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-open-prepared-statements: 20
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters, 去掉后监控界面sql无法统计, 'wall'用于防火墙
filters: stat,wall
# Spring监控AOP切入点,如x.y.z.service.*,配置多个英文逗号分隔
aop-patterns: com.springboot.servie.* # WebStatFilter配置
web-stat-filter:
enabled: true
# 添加过滤规则
url-pattern: /*
# 忽略过滤的格式
exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*' # StatViewServlet配置
stat-view-servlet:
enabled: true
# 访问路径为/druid时,跳转到StatViewServlet
url-pattern: /druid/*
# 是否能够重置数据
reset-enable: false
# 需要账号密码才能访问控制台
login-username: druid
login-password: druid123
# IP白名单
# allow: 127.0.0.1
# IP黑名单(共同存在时,deny优先于allow)
# deny: 192.168.1.218 # 配置StatFilter
filter:
stat:
log-slow-sql: true

上述配置不但配置了Druid作为连接池,而且还开启了Druid的监控功能
3.运行项目

访问http://localhost:8080/web/druid

SpringBoot2+Druid+MyBatis+MySql实现增删改查-LMLPHP

输入登录用户名和密码,可以查看Druid的监控信息:

SpringBoot2+Druid+MyBatis+MySql实现增删改查-LMLPHP

4.实现增删改查功能:

创建测试表tb_student,并插入几条测试数据

CREATE TABLE `tb_student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sno` varchar(10) DEFAULT NULL,
`sname` varchar(100) DEFAULT NULL,
`ssex` char(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `tb_student` VALUES ('', '', '张三', 'M');
INSERT INTO `tb_student` VALUES ('', '', '李四', 'M');
INSERT INTO `tb_student` VALUES ('', '', '王五', 'F');

创建对应实体:

package com.czhappy.bootlearn.SpringBootMyBatis.entity;

import java.io.Serializable;

public class Student implements Serializable {
private static final long serialVersionUID = -339516038496531943L;
private Integer id;
private String sno;
private String name;
private String sex; public Integer getId() {
return id;
} public void setId(Integer id) {
this.id = id;
} public static long getSerialVersionUID() {
return serialVersionUID;
} public String getSno() {
return sno;
} public void setSno(String sno) {
this.sno = sno;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public String getSex() {
return sex;
} public void setSex(String sex) {
this.sex = sex;
}
}

使用注解方式创建StudentMapper:

package com.czhappy.bootlearn.SpringBootMyBatis.mapper;

import com.czhappy.bootlearn.SpringBootMyBatis.entity.Student;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component; @Component
@Mapper
public interface StudentMapper {
@Insert("insert into tb_student(sno,sname,ssex) values(#{sno},#{name},#{sex})")
int add(Student student); @Update("update tb_student set sname=#{name},ssex=#{sex} where sno=#{sno}")
int update(Student student); @Delete("delete from tb_student where sno=#{sno}")
int deleteBysno(String sno); @Select("select * from tb_student where sno=#{sno}")
@Results(id = "student",value= {
@Result(property = "sno", column = "sno", javaType = String.class),
@Result(property = "name", column = "sname", javaType = String.class),
@Result(property = "sex", column = "ssex", javaType = String.class)
})
Student queryStudentBySno(String sno);
}

创建service:

package com.czhappy.bootlearn.SpringBootMyBatis.service;

import com.czhappy.bootlearn.SpringBootMyBatis.entity.Student;

public interface StudentService {
int add(Student student);
int update(Student student);
int deleteBysno(String sno);
Student queryStudentBySno(String sno);
}
package com.czhappy.bootlearn.SpringBootMyBatis.service.impl;

import com.czhappy.bootlearn.SpringBootMyBatis.entity.Student;
import com.czhappy.bootlearn.SpringBootMyBatis.mapper.StudentMapper;
import com.czhappy.bootlearn.SpringBootMyBatis.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service; @Servicepublic class StudentServiceImp implements StudentService {
@Autowired
private StudentMapper studentMapper; @Override
public int add(Student student) {
return this.studentMapper.add(student);
} @Override
public int update(Student student) {
return this.studentMapper.update(student);
} @Override
public int deleteBysno(String sno) {
return this.studentMapper.deleteBysno(sno);
} @Override
public Student queryStudentBySno(String sno) {
return this.studentMapper.queryStudentBySno(sno);
}
}

创建测试controller:

package com.czhappy.bootlearn.SpringBootMyBatis.controller;

import com.czhappy.bootlearn.SpringBootMyBatis.entity.Student;
import com.czhappy.bootlearn.SpringBootMyBatis.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController; @RestController
public class TestController { @Autowired
private StudentService studentService; @RequestMapping( value = "/querystudent", method = RequestMethod.GET)
public Student queryStudentBySno(String sno) {
return this.studentService.queryStudentBySno(sno);
}
}

重启项目,测试查询接口:

SpringBoot2+Druid+MyBatis+MySql实现增删改查-LMLPHP

05-11 20:01