Learn to integrate MyBatis Mapper (TkMybatis) in Spring Boot. The general Mapper is a framework that can implement any MyBatis general method. The project provides normal addition, deletion, modification and query operations as well as Example related single table operations. General Mapper is used to solve 90% of the basic operations in MyBatis. It can be used to develop conveniently and save developers a lot of time.

1 General

General Mapper can greatly facilitate developers. You can choose the general method according to your own needs at will, and you can also develop your own general method conveniently.

It is very convenient to use the addition, deletion, modification and query of MyBatis single table.

Single table operation is supported, and general multi table joint query is not supported.

Using general Mapper can realize database operation without XML file, and only need to inherit relevant Mapper interface in TkMybatis. But if you have special needs, you can customize XXXMapper.xml File to implement the operation of complex sql statements.

Why general Mapper?

When I first used MyBatis, I wrote it completely first, and then used the MyBatis code generator (MBG for short). In the process of using MBG, I found a very troublesome problem. If the database field changes frequently, I need to regenerate the code repeatedly, and because of the MBG override, I need to generate the code and append the code XML, which results in a lot of comparison and modification for each regeneration. In addition to this problem, there is also a problem. Just basic methods such as adding, deleting, modifying and checking have generated a large number of XML content. Without adding a handwritten method, the code may have been hundreds of lines, with a lot of content, which seems to be a hindrance.

Because many people are using MBG, MBG defines many common single table methods. In order to solve the problems mentioned above, and to avoid too many project refactoring in order to be compatible with MBG, a general Mapper is generated based on MBG and some JPA annotations. General Mapper can easily let you get the basic single table method, and it is also very convenient to extend the general method. Using general Mapper can greatly improve your work efficiency.

2 basic use

1. Create project, introduce dependency

Create Spring Boot project Spring Boot mybatis tkmybatis and add Web/MySQL Driver dependency as follows:

Then add the Druid/Mapper dependency (Spring Boot version) to the pom file manually. The final dependency is as follows:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
        <version>5.1.27</version>
    </dependency>
    <dependency>
        <groupId>tk.mybatis</groupId>
        <artifactId>mapper-spring-boot-starter</artifactId>
        <version>2.1.5</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
</dependencies>

Then in application.properties Add the configuration of database related information in the configuration file as follows:

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=000000
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/cxy35?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&autoReconnectForPools=true

1. New entity class

Manually create or use code generator to generate User entity class, and add related notes as follows:

@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "JDBC")
    private Integer id;
    private String username;
    private String password;
    private Boolean enabled;
    private Boolean locked;
    // @Column(name = "c_address")
    private String address;
    private String nickName;
    private Date createTime;
    private Date updateTime;

    // getter/setter
}

Note:

• @Table: Specifies the table name corresponding to the entity class, if the table name is t_user, the class name is TUser, this annotation is not required (default hump naming rule).
• @Column: Specifies the column name corresponding to this property, if the column name is create_time, the property name is createTime, this annotation (default hump naming rule) is not required.
• @id: identify the primary key id of the database table corresponding to this field.
• @GeneratedValue: Specifies the generation rule of primary key id, in which strategy means to use the primary key generation policy provided by the database. The generator is configured as "JDBC". After the data is inserted, the primary key id will be automatically filled into the entity class, similar to normal mapper.xml The selectKey label configured in.

1. New Mapper interface

public interface UserMapper extends Mapper<User> {
}

Here, we inherit the most basic general Mapper interface in TkMybatis, so that we can automatically have some interfaces in this Mapper without writing XXXMapper.xml Documents.

Relevant interfaces are as follows:

In addition to the Mapper interface, the official also provides several useful general Mapper interfaces, which can be used for inheritance and use. The summary is as follows:

• Mapper interface:

IdsMapper interface:

ConditionMapper interface:

• MySQL mapper interface:

• SqlServerMapper interface:

1. Configure scanning of Mapper interface

You can configure it through @ MapperScan annotation on the startup class or on the configuration class of MyBatis.

@SpringBootApplication
@tk.mybatis.spring.annotation.MapperScan(basePackages = "com.cxy35.sample.springboot.mybatis.tkmybatis.mapper")
public class SpringBootMybatisTkmybatisApplication {

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

}

1. test

Inject UserMapper into the test class to complete the test, as follows:

@SpringBootTest
class SpringBootMybatisTkmybatisApplicationTests {

    @Autowired
    UserMapper userMapper;

    @Test
    public void insertSelective() {
        User user = new User();
        user.setUsername("zhangsan");
        user.setPassword("123456");
        user.setAddress("Hangzhou");
        user.setNickName("zs");
        user.setCreateTime(new Date());
        user.setUpdateTime(new Date());
        userMapper.insertSelective(user);
    }

    @Test
    public void deleteByPrimaryKey() {
        userMapper.deleteByPrimaryKey(2);
    }

    @Test
    public void updateByPrimaryKeySelective() {
        User user = new User();
        user.setId(1);
        user.setUsername("zhangsan2");
        user.setPassword("654321");
        user.setNickName("zs2");
        user.setAddress("Shanghai");
        userMapper.updateByPrimaryKeySelective(user);
    }

    @Test
    public void selectAll() {
        List<User> users = userMapper.selectAll();
        System.out.println(users);
    }

    @Test
    public void selectByExample() {
        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andLike("username", "zhangsan%");
        criteria.andEqualTo("address", "Hangzhou");
        List<User> users = userMapper.selectByExample(example);
        System.out.println(users);
    }

}

3 customization XXXMapper.xml (not required)

Although most complex requirements can be operated through the combination of TkMyBatis. But if you have special needs, you can customize XXXMapper.xml File, to realize the operation of complex sql statement, here take the joint table query as an example.

At UserMapper.java New under package UserMapper.xml , add the following:

<?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">
<!-- For testing, not required -->
<mapper namespace="com.cxy35.sample.springboot.mybatis.tkmybatis.mapper.UserMapper">
    <select id="selectByRoleId" parameterType="java.lang.Integer" resultType="com.cxy35.sample.springboot.mybatis.tkmybatis.pojo.User">
        SELECT
            u.*
        FROM
            t_user u
        INNER JOIN t_user_role ur ON u.id = ur.user_id
        WHERE
            ur.role_id = #{roleId, jdbcType=INTEGER};
    </select>
</mapper>

The difference between the above xml file and the normal xml file is that there is no need to use resultMap for field mapping. Of course, if you want to add a new return field mapping to the returned Map, just add a new field directly.

Note: do not write some basic methods in TkMyBatis in the xml file, otherwise an error will be reported and the method will be repeated.

Next, modify UserMapper.java , add the corresponding interface:

public interface UserMapper extends Mapper<User> {
    // For testing, not required
    List<User> selectByRoleId(Integer roleId);
}

above UserMapper.xml Put it in UserMapper.java Under the package, it will be automatically scanned. But it will be ignored when the project is packaged, so you need to pom.xml Configure the Maven build time resource path in.

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
        </resource>
    </resources>
</build>

About XXXMapper.xml The location of documents and other plans will not be described here. For details, please refer to Spring Boot integrates MyBatis .

Finally, add methods to the test class to complete the test:

@Test
public void selectByRoleId() {
    List<User> users = userMapper.selectByRoleId(2);
    System.out.println(users);
}

4 code generator plug-in

• General Mapper plug-in: tk.mybatis.mapper.generator.MapperPlugin It can generate entity classes with @ Table/@Id/@Column and other annotations for automatic mapping with database fields.
• General code generator plug-in: tk.mybatis.mapper.generator.TemplateFilePlugin In fact, it is an extension of MyBatis Generator. Using this extension, you can easily write code in Freemarker template language.

First, at pom.xml Add MBG dependency (because it is based on MBG plug-in) and general Mapper plugin dependency as follows:

<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.3.7</version>
</dependency>
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-generator</artifactId>
    <version>1.1.5</version>
</dependency>

Next, create a new one in the src/main/resources directory generatorConfig.xml File to configure the code generator.

<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!-- Import external property file -->
    <properties resource="application.properties"/>

    <context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
        <property name="beginningDelimiter" value="`"/>
        <property name="endingDelimiter" value="`"/>
        <property name="javaFileEncoding" value="UTF-8"/>
        <!--Configure whether to use universal Mapper Built in annotation extension, default true-->
        <!--<property name="useMapperCommentGenerator" value="false"/>-->

        <!-- Generate serialization method for model-->
        <!--<plugin type="org.mybatis.generator.plugins.SerializablePlugin"/>-->
        <!-- Generated for Java Create a model toString method -->
        <!--<plugin type="org.mybatis.generator.plugins.ToStringPlugin"/>-->

        <!-- currency Mapper Plug in, which can generate annotated entity classes -->
        <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
            <!-- Generated Mapper Interface will automatically inherit the interface configured here -->
            <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
            <!-- Case sensitive, default false. If the database is case sensitive, you need to configure true，So when the table name is USER When, the @Table(name = "USER") Annotation, otherwise use lowercase user The table will not be found. -->
            <property name="caseSensitive" value="true"/>
            <!-- Forced generation of annotation, default false，If set to true，Whether the database name and field name are consistent or not, an annotation (including @Table and @Column).  -->
            <property name="forceAnnotation" value="true"/>
            <!-- Generating model Add a constant to the field name for ease of use Example Used when splicing query conditions. -->
            <property name="generateColumnConsts" value="true"/>
            <!-- -->
            <!--<property name="generateDefaultInstanceMethod" value="true"/>-->
            <!-- Start and end separators, for those with keywords. -->
            <property name="beginningDelimiter" value="`"/>
            <property name="endingDelimiter" value="`"/>
            <!-- Configure whether to enable lombok, The following six annotations are supported -->
            <!-- Note: when configuring Data After, Getter Setter ToString EqualsAndHashCode Will be ignored-->
            <!--<property name="lombok" value="Getter,Setter,Data,ToString,Accessors,EqualsAndHashCode"/>-->
            <!--<property name="lombokEqualsAndHashCodeCallSuper" value="true"/>-->
            <!-- -->
            <!--<property name="swagger" value="true"/>-->
        </plugin>

        <!-- General code generator plug-in: support based on Freemarker Template generates various codes (not measured) -->
        <!-- mapper Interface -->
        <!--<plugin type="tk.mybatis.mapper.generator.TemplateFilePlugin">
            <property name="targetProject" value="spring-boot-dao/spring-boot-mybatis-tkmybatis/src/main/java"/>
            <property name="targetPackage" value="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper"/>
            <property name="templatePath" value="generator/mapper.ftl"/>
            <property name="fileName" value="${tableClass.shortClassName}${mapperSuffix}.java"/>
            <property name="mapperSuffix" value="Dao"/>
        </plugin>-->

        <!-- mapper.xml -->
        <!--<plugin type="tk.mybatis.mapper.generator.TemplateFilePlugin">
            <property name="targetProject" value="spring-boot-dao/spring-boot-mybatis-tkmybatis/src/main/java"/>
            <property name="targetPackage" value="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper"/>
            <property name="templatePath" value="generator/mapperXml.ftl"/>
            <property name="fileName" value="${tableClass.shortClassName}${mapperSuffix}.xml"/>
            <property name="mapperSuffix" value="Dao"/>
            <property name="mapperPackage" value="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper"/>
        </plugin>-->

        <!-- Configure database connection -->
        <jdbcConnection driverClass="${spring.datasource.driver-class-name}"
                        connectionURL="${spring.datasource.url}"
                        userId="${spring.datasource.username}"
                        password="${spring.datasource.password}">
            <!-- solve mysql Drive upgrade to 8.0 Do not generate the specified database code after -->
            <property name="nullCatalogMeansCurrent" value="true" />
        </jdbcConnection>

        <!-- Specify build model Path to -->
        <javaModelGenerator targetPackage="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.model" targetProject="spring-boot-dao/spring-boot-mybatis-tkmybatis/src/main/java"/>

        <!-- Specify build mapper The path of the interface -->
        <javaClientGenerator targetPackage="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper" targetProject="spring-boot-dao/spring-boot-mybatis-tkmybatis/src/main/java" type="XMLMAPPER"/>

        <!-- Not required -->
        <!-- Specify build mapper.xml Path to -->
        <!--<sqlMapGenerator targetPackage="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper" targetProject="spring-boot-dao/spring-boot-mybatis-tkmybatis/src/main/java"/>-->

        <!-- tableName have access to % wildcard -->
        <table tableName="t_user">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
        <!--<table tableName="t_user_role">
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>-->
    </context>
</generatorConfiguration>

Finally, at com.cxy35 . sample.springboot.mybatis Create a new mbg package under. Tkmybatis to store the configuration code related to the code generator and the final generated business code. newly build Generator.java As follows:

public class Generator {

    public static InputStream getResourceAsStream(String path) {
        return Thread.currentThread().getContextClassLoader().getResourceAsStream(path);
    }

    public static void main(String[] args) throws Exception {
        //Warnings during MBG execution
        List<String> warnings = new ArrayList<String>();
        //When the generated code is duplicate, overwrite the original code
        boolean overwrite = true;
        //Read our MBG configuration file
        InputStream is = Generator.class.getResourceAsStream("/generatorConfig.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        is.close();

        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        //Create MBG
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        //Execute build code
        myBatisGenerator.generate(null);
        //Output warning information
        for (String warning : warnings) {
            System.out.println(warning);
        }
    }
}

Run the above main method to generate code automatically, as follows:

• TUser.java

@Table(name = "`t_user`")
public class TUser {
    /**
     * Primary key id
     */
    @Id
    @Column(name = "`id`")
    @GeneratedValue(generator = "JDBC")
    private Integer id;

    /**
     * user name
     */
    @Column(name = "`username`")
    private String username;

    /**
     * password
     */
    @Column(name = "`password`")
    private String password;

    /**
     * Enable or not: 1 enabled; 0 not enabled
     */
    @Column(name = "`enabled`")
    private Boolean enabled;

    /**
     * Locked: 1 locked; 0 unlocked
     */
    @Column(name = "`locked`")
    private Boolean locked;

    /**
     * address
     */
    @Column(name = "`address`")
    private String address;

    /**
     * Nickname?
     */
    @Column(name = "`nick_name`")
    private String nickName;

    /**
     * Creation time
     */
    @Column(name = "`create_time`")
    private Date createTime;

    /**
     * Update time
     */
    @Column(name = "`update_time`")
    private Date updateTime;

    public static final String ID = "id";

    public static final String DB_ID = "id";

    public static final String USERNAME = "username";

    public static final String DB_USERNAME = "username";

    public static final String PASSWORD = "password";

    public static final String DB_PASSWORD = "password";

    public static final String ENABLED = "enabled";

    public static final String DB_ENABLED = "enabled";

    public static final String LOCKED = "locked";

    public static final String DB_LOCKED = "locked";

    public static final String ADDRESS = "address";

    public static final String DB_ADDRESS = "address";

    public static final String NICK_NAME = "nickName";

    public static final String DB_NICK_NAME = "nick_name";

    public static final String CREATE_TIME = "createTime";

    public static final String DB_CREATE_TIME = "create_time";

    public static final String UPDATE_TIME = "updateTime";

    public static final String DB_UPDATE_TIME = "update_time";

    // getter/setter
}

• TUserMapper.java

public interface TUserMapper extends Mapper<TUser> {
}

• TUserMapper.xml (not required)

<?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.cxy35.sample.springboot.mybatis.tkmybatis.mbg.mapper.TUserMapper">
  <resultMap id="BaseResultMap" type="com.cxy35.sample.springboot.mybatis.tkmybatis.mbg.model.TUser">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="enabled" jdbcType="BIT" property="enabled" />
    <result column="locked" jdbcType="BIT" property="locked" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="nick_name" jdbcType="VARCHAR" property="nickName" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
  </resultMap>
</mapper>

Extended reading:

• Mybatis general Mapper code generator
• General Mapper special code generator
• MyBatis Generator details

• Spring Boot tutorial collection (you can read the full text directly at the bottom left of wechat).
• Spring Boot tutorial collection sample code: https://github.com/cxy35/spring-boot-samples
• Sample code of this article: https://github.com/cxy35/spring-boot-samples/tree/master/spring-boot-dao/spring-boot-mybatis-tkmybatis

Scan code concerns WeChat programmer 35, gets the latest technology dry cargo, chatting programmers 35, 35 programmers official account. Independent site: https://cxy35.com