MyBatis

05.MyBatis学习笔记

Mybatis逆向工程（XML 方式）

Posted by Chen Xingxu on September 15, 2020

05.MyBatis学习笔记–Mybatis逆向工程（XML 方式）

什么是逆向工程

MyBatis 的一个主要的特点就是需要程序员自己编写 SQL，那么如果表太多的话，难免会很麻烦，所以 MyBatis 官方提供了一个逆向工程，可以针对单表自动生成 MyBatis 执行所需要的代码（包括 mapper.xml、mapper.java、pojo 等）。一般在开发中，常用的逆向工程方式是通过数据库的表生成代码。

参考：

www.mybatis.org/generator/index.html

http://mybatis.org/generator/running/runningWithMaven.html

http://mybatis.org/generator/configreference/javaClientGenerator.html

Maven 配置

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>demo.yangxu</groupId>
    <artifactId>mybatis-generator</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <slf4j.version>1.6.1</slf4j.version>
        <logback.version>1.1.2</logback.version>
        <mysql-connector-java.version>5.1.30</mysql-connector-java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>${logback.version}</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
        </dependency>
    </dependencies>

    <build>
        <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
            <plugins>
                <plugin>
                    <groupId>org.mybatis.generator</groupId>
                    <artifactId>mybatis-generator-maven-plugin</artifactId>
                    <version>1.4.0</version>
                    <dependencies>
                        <dependency>
                            <groupId>mysql</groupId>
                            <artifactId>mysql-connector-java</artifactId>
                            <version>${mysql-connector-java.version}</version>
                        </dependency>
                    </dependencies>
                </plugin>
                <plugin>
                    <artifactId>maven-clean-plugin</artifactId>
                    <version>3.0.0</version>
                </plugin>
                <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.7.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.20.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-jar-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-install-plugin</artifactId>
                    <version>2.5.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-deploy-plugin</artifactId>
                    <version>2.8.2</version>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>

</project>

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>
    <context id="mybatisTables" targetRuntime="MyBatis3">
        <!--开启以下注释可以去掉逆向工程生成代码的注释-->
<!--        <commentGenerator >-->
<!--            <property name="suppressAllComments" value="true"/>-->
<!--        </commentGenerator>-->

        <!-- jdbc连接 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mybatis_learn"
                        userId="root"
                        password="root123456">
        </jdbcConnection>
        <!-- 类型转换 -->
        <javaTypeResolver >
            <!-- 是否使用bigDecimal， false可自动转化以下类型（Long, Integer, Short, etc.） -->
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>
        <!-- 生成实体类的包名和位置 注意targetProject的值为实体类放在工程中具体位置的相对路径，-->
        <javaModelGenerator targetPackage="demo.yangxu.mybatis.pojo" targetProject="${user.dir}\src\main\java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>
        <!-- 生成的SQLMapper映射文件包名和位置 -->
        <sqlMapGenerator targetPackage="mybatis\mapper"  targetProject="${user.dir}\src\main\resources">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>
        <!-- 生成DAO的包名和位置，这里配置将生成的dao类放在demo.yangxu.mybatis.mapper这个包下 -->
        <!-- 生成xml形式的mapper -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="demo.yangxu.mybatis.mapper"
                             targetProject="${user.dir}\src\main\java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>

        <!-- 配置表信息 -->
        <!-- schema即为数据库名 tableName为对应的数据库表 domainObjectName是要生成的实体类 enable*ByExample
             是否生成 example类
             参考：
             http://mybatis.org/generator/configreference/table.html-->
        <!-- 更改tableName和domainObjectName就可以 -->
        <table schema="mybatis_learn" tableName="mybatis_user" domainObjectName="User" >
            <property name="useActualColumnNames" value="true"/>
            <generatedKey column="ID" sqlStatement="Mysql" identity="true" />
            <columnOverride column="DATE_FIELD" property="startDate" />
            <ignoreColumn column="FRED" />
            <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" />
        </table>
    </context>
</generatorConfiguration>

生成代码

  1. 点击 Executing Maven Goal

  2. 选中逆向工程，执行以下命令

    mvn mybatis-generator:generate

  3. 为了方便测试，建议在 demo.yangxu.mybatis.pojo.User 中生成 toString 方法

    @Override
public String toString() {
    return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", age=" + age +
            ", phone=" + phone +
            ", desc='" + desc + '\'' +
            '}';
}

  4. 对 demo.yangxu.mybatis.pojo.User 进行序列化

    public class User implements Serializable {...}

  5. 由于数据库表中 desc 字段是 SQL 中的关键字，需要对 UserMapper.xml 进行修改，将 desc 改为 `desc`

    <?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="demo.yangxu.mybatis.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="demo.yangxu.mybatis.pojo.User">
       
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="phone" jdbcType="BIGINT" property="phone" />
    <result column="desc" jdbcType="VARCHAR" property="desc" />
  </resultMap>
  <sql id="Example_Where_Clause">
       
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
       
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
       
    id, username, age, phone, `desc`
  </sql>
  <select id="selectByExample" parameterType="demo.yangxu.mybatis.pojo.UserExample" resultMap="BaseResultMap">
       
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from mybatis_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
       
    select 
    <include refid="Base_Column_List" />
    from mybatis_user
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
       
    delete from mybatis_user
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <delete id="deleteByExample" parameterType="demo.yangxu.mybatis.pojo.UserExample">
       
    delete from mybatis_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="demo.yangxu.mybatis.pojo.User">
       
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into mybatis_user (username, age, phone, 
      `desc`)
    values (#{username,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{phone,jdbcType=BIGINT}, 
      #{desc,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="demo.yangxu.mybatis.pojo.User">
       
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into mybatis_user
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="username != null">
        username,
      </if>
      <if test="age != null">
        age,
      </if>
      <if test="phone != null">
        phone,
      </if>
      <if test="desc != null">
        `desc`,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="username != null">
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        #{age,jdbcType=INTEGER},
      </if>
      <if test="phone != null">
        #{phone,jdbcType=BIGINT},
      </if>
      <if test="desc != null">
        #{desc,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="demo.yangxu.mybatis.pojo.UserExample" resultType="java.lang.Long">
       
    select count(*) from mybatis_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map">
       
    update mybatis_user
    <set>
      <if test="record.id != null">
        id = #{record.id,jdbcType=BIGINT},
      </if>
      <if test="record.username != null">
        username = #{record.username,jdbcType=VARCHAR},
      </if>
      <if test="record.age != null">
        age = #{record.age,jdbcType=INTEGER},
      </if>
      <if test="record.phone != null">
        phone = #{record.phone,jdbcType=BIGINT},
      </if>
      <if test="record.desc != null">
        `desc` = #{record.desc,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
       
    update mybatis_user
    set id = #{record.id,jdbcType=BIGINT},
      username = #{record.username,jdbcType=VARCHAR},
      age = #{record.age,jdbcType=INTEGER},
      phone = #{record.phone,jdbcType=BIGINT},
      `desc` = #{record.desc,jdbcType=VARCHAR}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="demo.yangxu.mybatis.pojo.User">
    update mybatis_user
    <set>
      <if test="username != null">
        username = #{username,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        age = #{age,jdbcType=INTEGER},
      </if>
      <if test="phone != null">
        phone = #{phone,jdbcType=BIGINT},
      </if>
      <if test="desc != null">
        `desc` = #{desc,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parameterType="demo.yangxu.mybatis.pojo.User">
    update mybatis_user
    set username = #{username,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER},
      phone = #{phone,jdbcType=BIGINT},
      `desc` = #{desc,jdbcType=VARCHAR}
    where id = #{id,jdbcType=BIGINT}
  </update>
</mapper>

MyBatis 配置

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="config.properties">
    </properties>

    <plugins>
        <!--监控 sql 埋点 分页-->
        <plugin interceptor="demo.yangxu.mybatis.plugin.SqlPrintInterceptor"></plugin>
    </plugins>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mybatis/mapper/UserMapper.xml"/>

    </mappers>
</configuration>

config.properties

driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://127.0.0.1:3306/mybatis_learn?characterEncoding=utf8&amp;serverTimezone=UTC&amp;useSSL=false
url=jdbc:mysql://127.0.0.1:3306/mybatis_learn?characterEncoding=utf8&serverTimezone=UTC&useSSL=false
#url=jdbc:mysql://127.0.0.1:3306/mybatis_learn
username=root
password=root123456

MyBatis Plugin

SqlPrintInterceptor.java

package demo.yangxu.mybatis.plugin;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;

@Intercepts
        ({
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
        })
public class SqlPrintInterceptor implements Interceptor {
    private static final Logger log = LoggerFactory.getLogger(SqlPrintInterceptor.class);

    private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameterObject = null;
        if (invocation.getArgs().length > 1) {
            parameterObject = invocation.getArgs()[1];
        }

        long start = System.currentTimeMillis();

        Object result = invocation.proceed();

        String statementId = mappedStatement.getId();
        BoundSql boundSql = mappedStatement.getBoundSql(parameterObject);
        Configuration configuration = mappedStatement.getConfiguration();
        String sql = getSql(boundSql, parameterObject, configuration);

        long end = System.currentTimeMillis();
        long timing = end - start;
        if(log.isInfoEnabled()){
            log.info("执行sql耗时:" + timing + " ms" + " - id:" + statementId + " - Sql:" );
            log.info("   "+sql);
        }

        return result;
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {
    }

    private String getSql(BoundSql boundSql, Object parameterObject, Configuration configuration) {
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        if (parameterMappings != null) {
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = configuration.newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                    }
                    sql = replacePlaceholder(sql, value);
                }
            }
        }
        return sql;
    }

    private String replacePlaceholder(String sql, Object propertyValue) {
        String result;
        if (propertyValue != null) {
            if (propertyValue instanceof String) {
                result = "'" + propertyValue + "'";
            } else if (propertyValue instanceof Date) {
                result = "'" + DATE_FORMAT.format(propertyValue) + "'";
            } else {
                result = propertyValue.toString();
            }
        } else {
            result = "null";
        }
        return sql.replaceFirst("\\?", Matcher.quoteReplacement(result));
    }
}

业务类

建表 SQL

CREATE TABLE `mybatis_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `phone` bigint(20) DEFAULT NULL COMMENT '电话号码',
  `desc` varchar(255) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

entities

User.java

UserExample.java

由 Mybatis 逆向工程生成

DAO 层

UserMapper.java

UserMapper.xml

由 Mybatis 逆向工程生成

测试类

MybatisTest.java

package demo.yangxu.mybatis.test;

import demo.yangxu.mybatis.mapper.UserMapper;
import demo.yangxu.mybatis.pojo.User;
import demo.yangxu.mybatis.pojo.UserExample;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

@Slf4j
public class MybatisTest {
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        UserExample userExample = new UserExample();
        UserExample.Criteria criteria = userExample.createCriteria();
        criteria.andIdEqualTo(11L);
        List<User> userList = mapper.selectByExample(userExample);
        log.info("user:{}",userList.get(0));
    }
}

运行测试类结果：

CATALOG
    FEATURED TAGS
    JavaBasic Oracle ECourses Docker SpringBoot SpringCloudAlibaba SpringCloud Maven Git Jenkins MyBatis CentOS JavaScript SVN
    FRIENDS