SpringBoot2.X之旅，Mybatis+Durid+Mysql实践，mybatis generator代码生成器实现简单CURD和Druid Monitor（Web Project）

实践步骤：

环境准备，建数据库—>

新建web的maven工程，集成依赖—>

加入mybatis generator的maven插件—>

编写mybatis-generator.xml文件，配置.yml文件—>

运行生成实体类、mapper接口、mapping的xml文件—>

编写service—>编写controller—>

启动类加mapper扫描—>

启动测试—>

添加Druid Monitor。

一、开发环境

1、本文的开发环境是跟之前的一篇文章一样的SpringBoot2.X之旅，开篇 hello world（Web Project）

2、数据库5.7，表结构和基础数据，由于Navicat版本原因，如果不能直接运行，可以只执行里面的建表和插入数据sql：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50724
 Source Host           : localhost:3306
 Source Schema         : demo

 Target Server Type    : MySQL
 Target Server Version : 50724
 File Encoding         : 65001

 Date: 16/03/2019 10:01:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for order_info
-- ----------------------------
DROP TABLE IF EXISTS `order_info`;
CREATE TABLE `order_info`  (
  `order_id` int(255) NOT NULL AUTO_INCREMENT,
  `order_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `order_fee` decimal(65, 0) NOT NULL,
  `user_id` int(255) NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order_info
-- ----------------------------
INSERT INTO `order_info` VALUES (1, '第一个订单', 50, 1);
INSERT INTO `order_info` VALUES (2, '第二个订单', 120, 2);
INSERT INTO `order_info` VALUES (3, '第三个订单', 60, 2);

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES (1, '周伯通', '123abc', '123abc@qq.com');
INSERT INTO `user_info` VALUES (2, '黄药师', '456def', '456def@163.com');

SET FOREIGN_KEY_CHECKS = 1;

二、使用idea新建web项目，为coding做准备

1、添加相应的依赖：

2、参照https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter，添加druid连接池的依赖包：

<!--加入druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

3、添加mybatis generator代码生成器插件，注意：生成依赖包中mysql的连接依赖尽量用低版本，高版本会报错，找不到表主键，只生成insert的语句，我这里用的版本是5.1.17：

<!--添加mybatis generator生成器插件-->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.7</version>
                <!--生成插件的依赖-->
                <dependencies>
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.3.7</version>
                    </dependency>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.47</version>
                    </dependency>
                </dependencies>
                <!--所要做的事情-->
                <executions>
                    <execution>
                        <!--起个名-->
                        <id>mybatis generator</id>
                        <!--阶段-->
                        <phase>package</phase>
                        <!--目的-->
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <!--由于需要反复生成文件，这里是一些相关配置-->
                <configuration>
                    <!--允许移动生成文件-->
                    <verbose>true</verbose>
                    <!--允许覆盖文件，实际开发中这个不能为true，
                    不然可能把别人的劳动成果覆盖-->
                    <overwrite>true</overwrite>
                    <!--mybatis-generator配置文件的路径-->
                    <configurationFile>
                        src/main/resources/mybatis-generator.xml
                    </configurationFile>
                </configuration>
            </plugin>

4、最终的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.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cobra</groupId>
    <artifactId>mybatisdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatisdemo</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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--加入druid依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </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>
                <!--生成插件的依赖-->
                <dependencies>
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.3.7</version>
                    </dependency>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.47</version>
                    </dependency>
                </dependencies>
                <!--所要做的事情-->
                <executions>
                    <execution>
                        <!--起个名-->
                        <id>mybatis generator</id>
                        <!--阶段-->
                        <phase>package</phase>
                        <!--目的-->
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <!--由于需要反复生成文件，这里是一些相关配置-->
                <configuration>
                    <!--允许移动生成文件-->
                    <verbose>true</verbose>
                    <!--允许覆盖文件，实际开发中这个不能为true，
                    不然可能把别人的劳动成果覆盖-->
                    <overwrite>true</overwrite>
                    <!--mybatis-generator配置文件的路径-->
                    <configurationFile>
                        src/main/resources/mybatis-generator.xml
                    </configurationFile>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

5、在resources文件下，新建mybatis-generator.xml的代码生成文件，里面的内容不用记，可以直接去mybatis官网http://www.mybatis.org/generator/configreference/xmlconfig.html找到，下面是我自己整理的：

<?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="E:/mysql/mysql-connector-java-5.1.29.jar"/>
     -->
    <!--<classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.zip" />-->

    <context id="DB2Tables" targetRuntime="MyBatis3">
        <!--数据库驱动、地址、账号、密码-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=UTC"
                        userId="root"
                        password="123456">
        </jdbcConnection>
        <!--这个可以不要-->
        <!--<javaTypeResolver >-->
            <!--<property name="forceBigDecimals" value="false" />-->
        <!--</javaTypeResolver>-->
        <!--生成DataObject类存放的位置-->
        <javaModelGenerator targetPackage="com.cobra.mybatisdemo.dataobject" targetProject="src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>
        <!--生成映射文件存放的位置-->
        <sqlMapGenerator targetPackage="mapping"  targetProject="src/main/resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>
        <!--生成Dao类存放的位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.cobra.mybatisdemo.dao"  targetProject="src/main/java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>
        <!--需要生成文件对应表及实体类名-->
        <!--<table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer" >-->
            <!--<property name="useActualColumnNames" value="true"/>-->
            <!--<generatedKey column="ID" sqlStatement="DB2" identity="true" />-->
            <!--<columnOverride column="DATE_FIELD" property="startDate" />-->
            <!--<ignoreColumn column="FRED" />-->
            <!--<columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />-->
        <!--</table>-->
        <!--需要生成文件对应表及实体类名，我这里生成的是两张表user_info、order_info-->
        <table tableName="user_info" domainObjectName="UserInfo"  enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false"
               enableSelectByExample="fable" selectByExampleQueryId="false"></table>
        <table tableName="order_info" domainObjectName="OrderInfo"  enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false"
               enableSelectByExample="fable" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

6、新建dataobject、dao、mapping包用于存放生成的代码：

7、配置application.yml：

#数据源配置
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
#mybatis映射配置
mybatis:
  mapper-locations: classpath:mapping/*.xml
#日志输出sql语句
logging:
  level:
    com.cobra.mybatisdemo.dao: debug

三、生成代码：

1、点击Edit Configgurations，添加运行mybatis-generator运行按钮：

2、弹出窗口，点“+”，选择maven：

3、添加指令mybatis-generator:generate，命名mybatis-generator，点击apply，点击ok：

4、选择mybatis-generator，点击run，生成代码：

5、生成完成，得到：

6、新建测试类，注意，需要在类加注解@MapperScan("com.cobra.mybatisdemo.dao")，测试启动时，扫描dao包，注入bean（userInfoMapper飘红，可以无视），项目运行时，启动类也要加@MapperScan("com.cobra.mybatisdemo.dao")：

package com.cobra.mybatisdemo.dao;

import com.cobra.mybatisdemo.dataobject.UserInfo;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

/**
 * @Author: Baron
 * @Description:
 * @Date: Created in 2019/3/16 1:46
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@MapperScan("com.cobra.mybatisdemo.dao")
public class UserInfoMapperTest {

    @Autowired
    private UserInfoMapper userInfoMapper;

    @Test
    public void deleteByPrimaryKey() {
    }

    @Test
    public void insert() {
    }

    @Test
    public void insertSelective() {
    }

    @Test
    public void selectByPrimaryKey() {
        UserInfo userInfo = userInfoMapper.selectByPrimaryKey(1);
        System.out.println(userInfo.getUsername());
    }

    @Test
    public void updateByPrimaryKeySelective() {
    }

    @Test
    public void updateByPrimaryKey() {
    }
}

7、运行测试得到，说明代码没有问题：

四、实现CURD，这里为了演示，直接使用dataobject进行数据传递，省略DTO和VO，真实项目中可不能这样干：

1、service层的定义，UserService、UserServiceImpl：

package com.cobra.mybatisdemo.service;

import com.cobra.mybatisdemo.dataobject.UserInfo;

import java.util.List;

/**
 * @Author: Baron
 * @Description:
 * @Date: Created in 2019/3/16 11:52
 */
public interface UserService {

    /**
     * 添加用户
     * @param userInfo
     * @return
     */
    public int add(UserInfo userInfo);

    /**
     * 修改用户
     * @param userInfo
     * @return
     */
    public int update(UserInfo userInfo);

    /**
     * 删除用户
     * @param userId
     * @return
     */
    public int deleteByUserId(Integer userId);

    /**
     * 通过userId查找用户
     * @param userId
     * @return
     */
    public UserInfo findByUserId(Integer userId);

}

package com.cobra.mybatisdemo.service.impl;

import com.cobra.mybatisdemo.dao.UserInfoMapper;
import com.cobra.mybatisdemo.dataobject.UserInfo;
import com.cobra.mybatisdemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @Author: Baron
 * @Description:
 * @Date: Created in 2019/3/16 12:07
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserInfoMapper userInfoMapper;

    /**
     * 添加用户
     *
     * @param userInfo
     * @return
     */
    @Override
    public int add(UserInfo userInfo) {
        return userInfoMapper.insertSelective(userInfo);
    }

    /**
     * 修改用户
     *
     * @param userInfo
     * @return
     */
    @Override
    public int update(UserInfo userInfo) {
        return userInfoMapper.updateByPrimaryKeySelective(userInfo);
    }

    /**
     * 删除用户
     *
     * @param userId
     * @return
     */
    @Override
    public int deleteByUserId(Integer userId) {
        return userInfoMapper.deleteByPrimaryKey(userId);
    }

    /**
     * 通过userId查找用户
     *
     * @param userId
     * @return
     */
    @Override
    public UserInfo findByUserId(Integer userId) {
        return userInfoMapper.selectByPrimaryKey(userId);
    }
}

2、controller层的实现，UserController：

package com.cobra.mybatisdemo.controller;

import com.cobra.mybatisdemo.dataobject.UserInfo;
import com.cobra.mybatisdemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @Author: Baron
 * @Description:
 * @Date: Created in 2019/3/16 12:10
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    UserService userService;

    /**
     * 添加用户
     * @param userInfo
     * @return
     */
    @PostMapping("/add")
    public int add(UserInfo userInfo) {
        return userService.add(userInfo);
    }

    /**
     * 根据userId查找用户
     * @param userId
     * @return
     */
    @GetMapping("/{userId}")
    public UserInfo findByUserId(@PathVariable("userId") Integer userId) {
        return userService.findByUserId(userId);
    }

    /**
     * 更改用户信息
     * @param userInfo
     * @return
     */
    @PostMapping("/update")
    public int update(UserInfo userInfo) {
        return userService.update(userInfo);
    }

    /**
     * 根据userId删除用户
     * @param userId
     * @return
     */
    @DeleteMapping("/{userId}")
    public int deleteByUserId(@PathVariable("userId") Integer userId) {
        return userService.deleteByUserId(userId);
    }

}

3、在启动类上加mapper扫描@MapperScan("com.cobra.mybatisdemo.dao")：

package com.cobra.mybatisdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.cobra.mybatisdemo.dao")
public class MybatisdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisdemoApplication.class, args);
    }

}

4、启动，测试：

查找：

添加

修改：

删除：

五、番外，如果需要，可以添加Druid Monitor

1、配置如下：

#数据源配置
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/demo?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
    # 一、WebStatFilter配置，说明请参考Druid Wiki，配置_配置WebStatFilter
    druid:
      web-stat-filter:
        #是否启用StatFilter默认值false
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
        session-stat-enable: "true"
  #        session-stat-max-count:
  #        principal-session-name:
  #        principal-cookie-name:
  #        profile-enable:
      #二、是否启用StatViewServlet（监控页面）默认值为false（考虑到安全问题默认并未启动，如需启用建议设置密码或白名单以保障安全）
      stat-view-servlet:
        # Spring监控配置，说明请参考Druid Github Wiki，配置_Druid和Spring关联监控配置
        enabled: true
        url-pattern: "/druid/*"
        reset-enable: "true"
        login-username: "admin"
        login-password: "admin"
        allow: "192.168.0.100,127.0.0.1"
        deny: "192.168.0.101"
      #三、Spring监控AOP切入点，如x.y.z.service.*,配置多个英文逗号分隔
      aop-patterns: "com.cobra.mybatisdemo.service.impl.*"
#mybatis映射配置
mybatis:
  mapper-locations: classpath:mapping/*.xml
#日志输出sql语句
logging:
  level:
    com.cobra.mybatisdemo.dao: debug

2、重新启动，访问http://localhost:8080/druid/index.html：

 

附github地址：https://github.com/yaobaron/mybatisdemo