创建数据库表的 sql:
DROP DATABASE IF EXISTS mytest;
CREATE DATABASE IF NOT EXISTS mytest;
USE mytest;
CREATE TABLE t_user(
user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(255) NOT NULL ,
password VARCHAR(255) NOT NULL ,
phone VARCHAR(255) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
使用 Spring Initializr 方式创建项目 springboot-mybatis,完整的 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>
<groupId>com.wuychn</groupId>
<artifactId>springboot2-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springboot2-mybatis</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.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.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.54</version>
</dependency>
<!-- PageHelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
<!-- druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!-- mybatis generator 自动生成代码插件 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
<configuration>
<configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
</plugin>
</plugins>
<resources>
<!-- 下面的配置,把src/main/java和src/main/resources下面的所有文件都加载,这样就可以将mapper.xml放到类路径下 -->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*</include>
</includes>
</resource>
</resources>
</build>
</project>
application.yml:
server:
port: 8080
spring:
jackson:
time-zone: GMT+8
date-format: yyyy-MM-dd HH:mm:ss
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:///mytest?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username: root
password: root
druid:
#初始化数
initial-size: 5
#最小空闲连接数
min-idle: 5
#最大连接数
max-active: 100
#获取连接等待超时的时间
max-wait: 60000
#间隔多久才进行一次检测
time-between-eviction-runs-millis: 60000
#配置一个连接在池中最小生存的时间
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false
test-on-return: false
#配置监控统计拦截的filters
filters: stat, wall
#打开PSCache,并指定每个连接上PSCache的大小。oracle设为true,mysql设为false。分库分表较多推荐设置为false
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
#慢查询
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
#合并多个DruidDataSource的监控数据
use-global-data-source-stat: true
mybatis:
#mapper文件路径
mapper-locations: com/wuychn/*/mapper/*Mapper.xml
#别名扫描,多个用逗号隔开
# type-aliases-package: com.wuychn.user.entity
#控制台打印sql
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#pagehelper分页插件
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params: count=countSql
returnPageInfo: check
使用 mybatis generator 逆向工程自动生成代码,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="D:\.m2\mysql\mysql-connector-java\5.1.45\mysql-connector-java-5.1.45.jar"/>
<context id="test" targetRuntime="MyBatis3">
<!-- 生成的Java文件的编码-->
<property name="javaFileEncoding" value="UTF-8"/>
<commentGenerator>
<!-- 是否去除自动生成的注释,true:是:false:否 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库连接的信息:驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mytest?serverTimezone=GMT"
userId="root"
password="root">
</jdbcConnection>
<!--
<jdbcConnection driverClass="oracle.jdbc.OracleDriver"
connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
userId=""
password="">
</jdbcConnection>
-->
<!-- 默认false,把JDBC DECIMAL和NUMERIC类型解析为Integer,为true时把JDBC DECIMAL和NUMERIC类型解析为java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="true"/>
</javaTypeResolver>
<!-- targetProject:生成PO类的位置 -->
<javaModelGenerator targetPackage="com.wuychn.user.entity"
targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="true"/>
<!-- 清理从数据库返回的值前后的空格 -->
<property name="trimStrings" value="false"/>
</javaModelGenerator>
<!-- targetProject:mapper映射文件生成的位置 -->
<sqlMapGenerator targetPackage="com.wuychn.user.mapper"
targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false"/>
</sqlMapGenerator>
<!-- targetPackage:mapper接口生成的位置 -->
<javaClientGenerator type="XMLMAPPER"
targetPackage="com.wuychn.user.dao"
targetProject=".\src\main\java">
<!-- enableSubPackages:是否让schema作为包的后缀 -->
<property name="enableSubPackages" value="false"/>
</javaClientGenerator>
<!-- 要生成的表 tableName是数据库中的表名或视图名,domainObjectName是实体类名-->
<table tableName="t_user" domainObjectName="User" schema=""
enableCountByExample="false"
enableUpdateByExample="false"
enableDeleteByExample="false"
enableSelectByExample="false"
selectByExampleQueryId="false"/>
</context>
</generatorConfiguration>
生成的 User.java :
package com.wuychn.user.entity;
import lombok.Data;
@Data
public class User {
private Integer userId;
private String userName;
private String password;
private String phone;
}
UserMapper.java:
package com.wuychn.user.dao;
import com.wuychn.user.entity.User;
import java.util.List;
public interface UserMapper {
Integer deleteByPrimaryKey(Integer userId);
Integer insert(User record);
Integer insertSelective(User record);
User selectByPrimaryKey(Integer userId);
Integer updateByPrimaryKeySelective(User record);
Integer updateByPrimaryKey(User record);
// 自己加的
List<User> findAll();
}
UserMapper.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wuychn.user.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.wuychn.user.entity.User">
<id column="user_id" jdbcType="INTEGER" property="userId"/>
<result column="user_name" jdbcType="VARCHAR" property="userName"/>
<result column="password" jdbcType="VARCHAR" property="password"/>
<result column="phone" jdbcType="VARCHAR" property="phone"/>
</resultMap>
<sql id="Base_Column_List">
user_id, user_name, password, phone
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from t_user
where user_id = #{userId,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_user
where user_id = #{userId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.wuychn.user.entity.User">
insert into t_user (user_id, user_name, password,
phone)
values (#{userId,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{phone,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.wuychn.user.entity.User">
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userId != null">
user_id,
</if>
<if test="userName != null">
user_name,
</if>
<if test="password != null">
password,
</if>
<if test="phone != null">
phone,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userId != null">
#{userId,jdbcType=INTEGER},
</if>
<if test="userName != null">
#{userName,jdbcType=VARCHAR},
</if>
<if test="password != null">
#{password,jdbcType=VARCHAR},
</if>
<if test="phone != null">
#{phone,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.wuychn.user.entity.User">
update t_user
<set>
<if test="userName != null">
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="phone != null">
phone = #{phone,jdbcType=VARCHAR},
</if>
</set>
where user_id = #{userId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.wuychn.user.entity.User">
update t_user
set user_name = #{userName,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
phone = #{phone,jdbcType=VARCHAR}
where user_id = #{userId,jdbcType=INTEGER}
</update>
<!-- 自己加的 -->
<select id="findAll" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from t_user
</select>
</mapper>
在启动类添加 @MapperScan 注解,指定 mapper 接口所在的包,另外,还添加了一个 @EnableTransactionManagement 注解,用于开启事务:
package com.wuychn;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@SpringBootApplication
@MapperScan(basePackages = "com.wuychn.*.dao") // 扫描mapper接口,指定mapper接口所在的包
@EnableTransactionManagement // 开启事务,还需要在Service上使用@Transaction注解
public class SpringBoot2MyBatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBoot2MyBatisApplication.class, args);
}
}
新建 DruidConfig.java,用于配置Druid监控:
package com.wuychn.config;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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;
@Configuration
public class DruidConfig {
// 配置Druid的监控
// 1、配置一个管理后台的Servlet
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
// 登录账户和密码
servletRegistrationBean.addInitParameter("loginUsername", "druid");
servletRegistrationBean.addInitParameter("loginPassword", "druid");
// 允许访问的ip,默认就是允许所有访问
servletRegistrationBean.addInitParameter("allow", "");
// 不能访问的ip
servletRegistrationBean.addInitParameter("deny", "");
// 是否能够重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
// 2、配置一个web监控的filter
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
// 添加过滤规则
filterRegistrationBean.addUrlPatterns("/*");
// 下面的url不过滤
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
UserService.java:
package com.wuychn.user.service;
import com.wuychn.common.ResponseEntity;
import com.wuychn.user.entity.User;
public interface UserService {
ResponseEntity save(User user);
ResponseEntity delete(Integer userId);
ResponseEntity update(User user);
ResponseEntity getById(Integer userId);
ResponseEntity findAll(int pageNum, int pageSize);
}
UserServiceImpl.java:
package com.wuychn.user.service.impl;
import com.github.pagehelper.PageHelper;
import com.wuychn.common.ResponseEntity;
import com.wuychn.exception.MyException;
import com.wuychn.user.dao.UserMapper;
import com.wuychn.user.entity.User;
import com.wuychn.user.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@Transactional
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public ResponseEntity save(User user) {
Integer row = userMapper.insert(user);
if (row <= 0) {
throw new MyException("保存失败");
}
return new ResponseEntity(1, "保存成功", null);
}
@Override
public ResponseEntity delete(Integer userId) {
Integer row = userMapper.deleteByPrimaryKey(userId);
if (row <= 0) {
throw new MyException("删除失败");
}
return new ResponseEntity(1, "删除成功", null);
}
@Override
public ResponseEntity update(User user) {
Integer row = userMapper.updateByPrimaryKeySelective(user);
if (row <= 0) {
throw new MyException("更新失败");
}
return new ResponseEntity(1, "更新成功", null);
}
@Override
public ResponseEntity getById(Integer userId) {
User user = userMapper.selectByPrimaryKey(userId);
return new ResponseEntity(1, "获取数据成功", user);
}
/*
* 使用分页插件PageHelper分页
* 将pageNum和pageSize参数传递给startPage方法即可分页
* pageNum 开始页数
* pageSize 每页显示的数据条数
* */
@Override
public ResponseEntity findAll(int pageNum, int pageSize) {
PageHelper.startPage(pageNum, pageSize);
List<User> list = userMapper.findAll();
return new ResponseEntity(1, "获取数据成功", list);
}
}
ResponseEntity.java 作为统一的返回信息,其代码如下:
package com.wuychn.common;
import lombok.Data;
import java.io.Serializable;
@Data
public class ResponseEntity implements Serializable {
private static final Long serialVersionUID = -1L;
private Integer code;
private String msg;
private Object data;
public ResponseEntity(Integer code, String msg, Object data) {
this.code = code;
this.msg = msg;
this.data = data;
}
}
自定义异常 MyException.java:
package com.wuychn.exception;
public class MyException extends RuntimeException {
public MyException(String msg) {
super(msg);
}
}
全局异常处理 GlobalExceptionHandler.java:
package com.wuychn.exception;
import com.wuychn.common.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.ResponseBody;
@ControllerAdvice
@ResponseBody
public class GlobalExceptionHandler {
@ExceptionHandler(MyException.class)
public ResponseEntity handMyException(MyException e) {
return new ResponseEntity(0, e.getMessage(), null);
}
@ExceptionHandler(RuntimeException.class)
public ResponseEntity handRuntimeException(RuntimeException e) {
return new ResponseEntity(0, "哇哦~出了点小问题...", null);
}
}
UserController.java:
package com.wuychn.user.controller;
import com.wuychn.common.ResponseEntity;
import com.wuychn.user.entity.User;
import com.wuychn.user.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@PostMapping("/save")
public ResponseEntity save(User user) {
return userService.save(user);
}
@PostMapping("/delete/{userId}")
public ResponseEntity delete(@PathVariable("userId") Integer userId) {
return userService.delete(userId);
}
@PostMapping("/update")
public ResponseEntity update(User user) {
return userService.update(user);
}
@GetMapping("/get/{userId}")
public ResponseEntity getById(@PathVariable("userId") Integer userId) {
return userService.getById(userId);
}
@GetMapping("/findAll")
public ResponseEntity findAll(@RequestParam(required = false, defaultValue = "1") Integer pageNum, @RequestParam(required = false, defaultValue = "10") Integer pageSize) {
return userService.findAll(pageNum, pageSize);
}
}
项目结构如图:
到这里,整合就完成了。启动项目,访问 http://localhost:8080/druid 可以看到 Druid 的监控页面。