创建数据库表的 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>springboot-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>springboot-mybatis</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.17.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>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.datatype</groupId>
<artifactId>jackson-datatype-joda</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.module</groupId>
<artifactId>jackson-module-parameter-names</artifactId>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.1.2</version>
</dependency>
<!-- alibaba的druid数据库连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.0</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.4</version>
</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:
datasource:
name: test
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql:///mytest?useUnicode=true&characterEncoding=UTF-8
username: root
password: root
#使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
#配置监控统计拦截的filters,去掉后监控界面sql无法统计
filters: stat
#最大活动连接: 连接池在同一时间能够分配的最大活动连接的数量,如果设置为非正数则表示不限制
maxActive: 60
#连接池初始化连接数
initialSize: 5
#最大等待时间: 当没有可用连接时, 连接池等待连接被归还的最大时间(单位是毫秒), 超过时间则抛出异常, 如果设置为-1 表示无限等待
maxWait: 60000
#最小空闲连接: 连接池中容许保持空闲状态的最小连接数量, 低于这个数量将创建新的连接, 如果设置为0则不创建,默认与initialSize相同
minIdle: 5
#空闲连接回收器执行间隔,单位毫秒
timeBetweenEvictionRunsMillis: 60000
#空闲连接能够保持空闲的最小时间,单位毫秒,在这个区间不会被回收器回收
minEvictableIdleTimeMillis: 300000
#SQL查询, 用来验证从连接池取出的连接
validationQuery: select 1
#是否对空闲连接进行校验,如果检测失败, 则连接将被从池中去除。注意: 设置为true后,validationQuery参数必须设置为非空字符串才能生效
testWhileIdle: true
#Validate the connection before borrowing it from the pool.
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
mybatis:
#对应mapper映射文件的所在路径
mapper-locations: com.wuychn.**.mapper/*Mapper.xml
#对应实体类的路径,不能使用通配符?
type-aliases-package: com.wuychn.user.domain
#打印sql语句,log-impl指定的值为 org.apache.ibatis.logging.Log 接口的某个实现类
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#配置打印sql语句,不支持通配符吗?
#logging:
# level:
# com.wuychn.user.mapper: debug
#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.domain"
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.mapper"
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>
点击 Edit Configurations:
添加配置:
运行:
即可生成相应的文件:
除此之外,还可以通过运行 main 方法的方式生成代码,可以参考这里。
生成的 User.java :
package com.wuychn.user.domain;
import lombok.Data;
import java.io.Serializable;
@Data
public class User implements Serializable {
private static final long serialVersionUID = -8834098627708150746L;
private Integer userId;
private String userName;
private String password;
private String phone;
}
UserMapper.java :
package com.wuychn.user.mapper;
import com.wuychn.user.domain.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.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.wuychn.user.domain.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.domain.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.domain.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.domain.User">
update t_user
<set>
<if test="userName != null and userName != ''">
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null and password != ''">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="phone != null and phone != ''">
phone = #{phone,jdbcType=VARCHAR},
</if>
</set>
where user_id = #{userId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.wuychn.user.domain.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>
UserMapper.xml 中,IDEA 会显示很多警告或是错误信息,而这些警告或错误不影响运行,解决办法是,File-Settings-Editor-Language Injections,找到如下配置,取消勾选:
接下来需要配置扫描项目中的 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("com.wuychn.*.mapper") // 扫描项目中的mapper(dao)
@EnableTransactionManagement // 开启事务,还需要在Service上使用@Transaction注解
public class SpringBootMyBatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootMyBatisApplication.class, args);
}
}
UserService.java:
package com.wuychn.user.service;
import com.wuychn.common.ResponseEntity;
import com.wuychn.user.domain.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.domain.User;
import com.wuychn.user.mapper.UserMapper;
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);
}
}
在 UserServiceImpl 中,注入 UserMapper 的地方 IDEA 可能会提示报错,但是不影响项目的运行。可以做如下配置:File-Settings-Editor-Inspections,找到Spring-Spring Core-Code-Autowiring for Bean Class,把后面的钩去掉:
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;
}
public ResponseEntity() {
}
}
自定义异常 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.domain.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(Integer pageNum, Integer pageSize) {
return userService.findAll(pageNum, pageSize);
}
}
项目结构如图:
到这里,Spring Boot 整合 MyBatis 就完成了。