IDEA+SpringBoot+MyBatis Dynamic SQL搭建后台系统

l> 我的新书《Android App开发入门与实战》已于2020年8月由人民邮电出版社出版，欢迎购买。点击进入详情

1.开发环境

1. IDEA 2021.2.2(Ultimate Edition)
2. MySQL 8.0.26
3. Navicat Premium 15.0.9

2.创建工程

File – New – Project… – Spring Initializr:

3.安装mysql和Navicat Premium

test.sql文件，可以导入到mysql中：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80026
 Source Host           : localhost:3306
 Source Schema         : test

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

 Date: 17/10/2021 23:47:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'tom');
INSERT INTO `user` VALUES (2, 'jerry');

SET FOREIGN_KEY_CHECKS = 1;

4.插件下载

我们下载两个插件：

其中Properties to YAML Converter是将Spring Web项目自带的application.properties文件转换成application.yml。
方法也很简单，右键选择application.properties，然后在菜单中选择：Convert Properties to YAML。

5.pom.xml

pom.xml是项目的配置文件，比如引用了哪些库，库的版本号。还有项目的版本号等。
配置好后右键点击pom.xml，然后选择Maven – Reload project

<?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 https://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.5.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>demo</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.2.0</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>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.mybatis.dynamic-sql</groupId>
            <artifactId>mybatis-dynamic-sql</artifactId>
            <version>1.3.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.0</version>
                <configuration>
                    <!--配置文件的位置-->
                    <configurationFile>
                        src/main/resources/generatorConfig.xml
                    </configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
                <executions>
                    <execution>
                        <id>Generate MyBatis Artifacts</id>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>org.mybatis.generator</groupId>
                        <artifactId>mybatis-generator-core</artifactId>
                        <version>1.4.0</version>
                    </dependency>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.26</version>
                    </dependency>
                    <dependency>
                        <groupId>org.mybatis</groupId>
                        <artifactId>mybatis</artifactId>
                        <version>3.5.7</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

</project>

6.generatorConfig.xml

src – main --resources下创建generatorConfig.xml文件：
注意，我们用的是mybatis的dynamic sql，所以targetRuntime要设置成如下格式：

targetRuntime=“MyBatis3DynamicSql”

<?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=
                            "C:/Users/Cage/.m2/repository/mysql/mysql-connector-java/8.0.26/mysql-connector-java-8.0.26.jar"/>
    <context id="DB2Tables" targetRuntime="MyBatis3DynamicSql">
        <commentGenerator>
            <property name="suppressAllComments" value="true"/>
            <property name="suppressDate" value="true"/>
        </commentGenerator>
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/test"
                        userId="root" password="123456">
            <property name="nullCatalogMeansCurrent" value="true"/>
        </jdbcConnection>
        <javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
            <property name="forceBigDecimals" value="true"/>
            <property name="useJSR310Types" value="true"/>
        </javaTypeResolver>
        <javaModelGenerator targetPackage="com.example.demo.entity"
                            targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <javaClientGenerator targetPackage="com.example.demo.mapper"
                             targetProject="src/main/java"
                             type="ANNOTATEDMAPPER">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <table tableName="user" enableCountByExample="true" enableDeleteByExample="true" enableDeleteByPrimaryKey="true"
               enableInsert="true" enableSelectByExample="true" enableSelectByPrimaryKey="true"
               enableUpdateByExample="true" enableUpdateByPrimaryKey="true"/>
    </context>
</generatorConfiguration>

7.生成entity和mapper文件

如图所示，选择mybatis-generator:generate并且run即可：

可以看到，Mybatis Dynamic SQL去除了mapper.xml文件。

说到这里我们简单介绍下Dynamic SQL。

使用Dynamic SQL就好比是使用Java的方式来操作MyBatis。Dynamic SQL是用于生成动态SQL语句的框架，提倡使用Java API的方式来实现SQL操作，支持复杂查询和多表查询。

Dynamic SQL具有如下特性：

类型安全：可以确保参数类型和数据库字段类型相匹配；
富有表现力：语句的构建方式可以清楚地传达其含义；
使用灵活：可以使用and，or和nested条件的任意组合来构建where子句；
扩展性强：可以同时为MyBatis3, Spring JDBC和纯JDBC框架生成SQL语句；
轻量级：只需添加一个小的依赖项，没有传递依赖。

8.生成UserService和其实现类

public interface UserService {
    User selectByName(@RequestParam("name")String name);
}

@Service
public class UserServiceImpl implements UserService {

    private final UserMapper userMapper;

    @Autowired
    public UserServiceImpl(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    @Override
    public User selectByName(String name) {
        SelectStatementProvider selectStatement = SqlBuilder.select(UserDynamicSqlSupport.id, UserDynamicSqlSupport.name)
                .from(UserDynamicSqlSupport.user)
                .where(UserDynamicSqlSupport.name, isEqualTo(name))
                .build()
                .render(RenderingStrategies.MYBATIS3);
        return userMapper.selectMany(selectStatement).get(0);
    }
}

9.生成UserController

@RestController
public class UserController {

    private final UserService userService;

    @Autowired
    public UserController(UserService userService) {
        this.userService = userService;
    }

    @RequestMapping("/get")
    @ResponseBody
    private User get(String name){
        return userService.selectByName(name);
    }
}

10.运行

直接运行DemoApplication，然后在浏览器上访问如下网址：

http://localhost:8080/get?name=tom

运行结果：

11.Github源码

https://github.com/ddnosh/weekly-sample-backend

欢迎关注我的技术公众号：国民程序员，我们的目标：输出干货

1. 每天分享原创技术文章
2. 海量免费技术资料和视频学习资源
3. 分享赚钱门道，带领程序员走向财务自由