MyBatis - Introduction

Frame at a glance:
(1) Framework to solve technology integration problems: Spring framework is created due to the complexity of software development.
(2) The MVC framework to solve the problem of WEB layer: Spring MVC.
(3) The framework to solve the problem of data persistence: MyBatis is a Java based persistence layer framework, while Hibernate is a more encapsulation persistence layer framework.

mybatis framework (persistence layer framework)

Mybatis is an excellent persistence layer framework based on java. It encapsulates jdbc inside, so developers only need to pay attention to sql statement itself, and do not need to spend energy to deal with the complicated process of loading driver, creating connection, creating statement, etc. Mybatis configures various statements to be executed by xml or annotation, and generates the final executed sql statements by mapping java objects and sql dynamic parameters in statements. Finally, the mybatis framework executes sql and maps the results to java objects and returns them. This paper uses ORM to solve the problem of entity and database mapping, encapsulates jdbc, shields the access details of JDBC API bottom layer, so that we can complete the persistent operation of database without dealing with JDBC API.

ORM(Object Relational Mapping): object relational mapping, mapping and transformation of entity class objects in data table and object-oriented language.

MyBatis is a further encapsulation of JDBC. The general process of JDBC programming is as follows:

1) . create database Connection
2) . create operation command Statement
3) . use operation command to execute SQL
4) . process result set ResultSet
5) . release resources

The general process of MyBatis:

2 application examples

(1)application.properties:
Spring configuration information

debug=true
# Set the level of printing log and print sql statement
logging.level.root=INFO
logging.level.druid.sql.Statement=ERROR
#Change frank to the root path of his project package
logging.level.com.glp=DEBUG

#Spring MVC configuration parameters
spring.mvc.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8

#Configure database connection pool initialization parameters
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/java20_blog04
spring.datasource.username=root
spring.datasource.password=mysql

#Indicate mapper.xml Where is the file
mybatis.mapper-locations=classpath:mapper/**.xml 

be careful:
mybatis.mapper-locations=classpath:mapper/**.xml indicates mapper.xml Where is the file

(2)config.xml:
Used to generate entity class, sql operation interface and xml file (table structure)

<?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>
    <properties resource="application.properties"/>

    <!---Mybatis context-->
    <context id="MySqlContext" defaultModelType="hierarchical" targetRuntime="MyBatis3Simple">

        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true" />
            <property name="addRemarkComments" value="true"/>
        </commentGenerator>

        <!--Link information for configuration database-->
        <jdbcConnection driverClass="${spring.datasource.driver-class-name}"
                        connectionURL="${spring.datasource.url}"
                        userId="${spring.datasource.username}"
                        password="${spring.datasource.password}"/>

        <!--Entity class generation information-->
        <javaModelGenerator targetProject="SpringLast/src/test/java"
                            targetPackage="com.glp.model">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--mybatis Of xml File address-->
        <sqlMapGenerator targetProject="SpringLast/src/test/resources"
                         targetPackage="mapper">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--mybatis Of mapper Interface-->
        <javaClientGenerator targetProject="SpringLast/src/test/java" type="XMLMAPPER"
                             targetPackage="com.glp.mapper">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!--Table structure to generate-->
        <table tableName="articles" >
            <generatedKey column="id" sqlStatement="Mysql" identity="true"/>
            <columnOverride column="title" javaType="java.lang.String" jdbcType="VARCHAR" />
        </table>

        <table tableName="users">
            <generatedKey column="id" sqlStatement="Mysql" identity="true"/>
        </table>

    </context>

</generatorConfiguration>

(3)Generator:
For loading config.xml File, generate MyBatis file (interface class, entity class, XML).

package com.glp.tool;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;

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

public class Generator {

    private static final boolean OVERWRITE = true;

    private static final String CONFIG_PATH = "generator/config.xml";

    public static void main(String[] args) throws Exception {
        System.out.println("--------------------start generator-------------------");
        List<String> warnings = new ArrayList<>();
        ClassLoader classloader = Thread.currentThread().getContextClassLoader();
        InputStream is = classloader.getResourceAsStream(CONFIG_PATH);
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        DefaultShellCallback callback = new DefaultShellCallback(OVERWRITE);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        warnings.forEach(System.err::println);
        System.out.println("--------------------end generator-------------------");
    }
}

After running the generator, generate the interface class, entity class and table structure xml in the test folder:

Transfer the generated entity class, interface and xml file to src/main/java/com/glp:

(4) Entity class:
Articles:

package com.glp.model;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Date;

@Getter
@Setter
@ToString
public class Articles {
    private Integer id;

    private String coverImage;

    private Integer authorId;

    private String title;

    private String body;

    private Date publishedAt;

    private Users user;
}

Users:

package com.glp.model;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Date;

@Getter
@Setter
@ToString
public class Users {
    private Integer id;

    private String username;

    private String password;

    private String nickname;

    private String brief;

    private Date registeredAt;
}

(5) Interface:
ArticlesMapper

package com.glp.mapper;
import com.glp.model.Articles;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface ArticlesMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Articles record);

    Articles selectByPrimaryKey(Integer id);

    List<Articles> selectAll();

    int updateByPrimaryKey(Articles record);

    List<Articles> queryByUserId(Integer userId);
}

UsersMapper

package com.glp.mapper;
import com.glp.model.Users;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface UsersMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(Users record);

    Users selectByPrimaryKey(Integer id);

    List<Users> selectAll();

    int updateByPrimaryKey(Users record);
}

be careful:
Mapper annotation should be added to every interface class

(6) sql statement xml configuration file
ArticlesMapper

<?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.glp.mapper.ArticlesMapper">

  <resultMap id="BaseResultMap" type="com.glp.model.Articles">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="cover_image" jdbcType="VARCHAR" property="coverImage" />
    <result column="author_id" jdbcType="INTEGER" property="authorId" />
    <result column="title" jdbcType="VARCHAR" property="title" />
    <result column="body" jdbcType="VARCHAR" property="body" />
    <result column="published_at" jdbcType="TIMESTAMP" property="publishedAt" />
    <association property="user" resultMap="com.glp.mapper.UsersMapper.BaseResultMap" columnPrefix="prefix_users_"/>
  </resultMap>
  
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from articles
    where id = #{id,jdbcType=INTEGER}
  </delete>
  
  <insert id="insert" parameterType="com.glp.model.Articles">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into articles (cover_image, author_id, title, 
      body, published_at)
    values (#{coverImage,jdbcType=VARCHAR}, #{authorId,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, 
      #{body,jdbcType=VARCHAR}, #{publishedAt,jdbcType=TIMESTAMP})
  </insert>
  
  <update id="updateByPrimaryKey" parameterType="com.glp.model.Articles">
    update articles
    set cover_image = #{coverImage,jdbcType=VARCHAR},
      author_id = #{authorId,jdbcType=INTEGER},
      title = #{title,jdbcType=VARCHAR},
      body = #{body,jdbcType=VARCHAR},
      published_at = #{publishedAt,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
  
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select a.id, a.cover_image, a.author_id, a.title, a.body, a.published_at, u.id prefix_users_id, u.nickname prefix_users_nickname,
    u.brief prefix_users_brief
    from articles a join users u on a.author_id = u.id
    where a.id = #{id,jdbcType=INTEGER}
  </select>
  
  <select id="selectAll" resultMap="BaseResultMap">
    select id, cover_image, author_id, title, body, published_at
    from articles
  </select>

  <select id="queryByUserId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select id, cover_image, author_id, title, body, published_at
    from articles
    where author_id = #{id,jdbcType=INTEGER}
  </select>
</mapper>

UsersMapper

<?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.glp.mapper.UsersMapper">
  <resultMap id="BaseResultMap" type="com.glp.model.Users">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="password" jdbcType="CHAR" property="password" />
    <result column="nickname" jdbcType="CHAR" property="nickname" />
    <result column="brief" jdbcType="VARCHAR" property="brief" />
    <result column="registered_at" jdbcType="TIMESTAMP" property="registeredAt" />
  </resultMap>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from users
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.glp.model.Users">
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>
    insert into users (username, password, nickname, 
      brief, registered_at)
    values (#{username,jdbcType=VARCHAR}, #{password,jdbcType=CHAR}, #{nickname,jdbcType=CHAR}, 
      #{brief,jdbcType=VARCHAR}, #{registeredAt,jdbcType=TIMESTAMP})
  </insert>
  <update id="updateByPrimaryKey" parameterType="com.glp.model.Users">
    update users
    set username = #{username,jdbcType=VARCHAR},
      password = #{password,jdbcType=CHAR},
      nickname = #{nickname,jdbcType=CHAR},
      brief = #{brief,jdbcType=VARCHAR},
      registered_at = #{registeredAt,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select id, username, password, nickname, brief, registered_at
    from users
    where id = #{id,jdbcType=INTEGER}
  </select>
  <select id="selectAll" resultMap="BaseResultMap">
    select id, username, password, nickname, brief, registered_at
    from users
  </select>
</mapper>

(7) Write Controller layer and service layer:

1)ArticlesController:

package com.glp.Controller;

import com.glp.model.Articles;
import com.glp.service.ArticlesService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class ArticlesController {
    @Autowired
    private ArticlesService articlesService;

    @RequestMapping("/query/{id}")
    public Object queryById(@PathVariable("id") Integer id){
        Articles article = articlesService.queryById(id);
        return article;
    }

    @RequestMapping("/queryByUserId")
    public Object queryByUserId(Integer userId){
        List<Articles> articlesList=articlesService.queryByUserId(userId);
        return articlesList;
    }

}

2)ArticlesService

package com.glp.service;

import com.glp.mapper.ArticlesMapper;
import com.glp.model.Articles;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class ArticlesService {
    @Resource
    private ArticlesMapper articlesMapper;

    public Articles queryById(Integer id) {
        return articlesMapper.selectByPrimaryKey(id);
    }

    public List<Articles> queryByUserId(Integer userId) {
        return articlesMapper.queryByUserId(userId);
    }
}

(8) Startup class:

package com.glp;

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

@SpringBootApplication
@MapperScan(basePackages = "com.glp.mapper")//Find interface path
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}

be careful:
@MapperScan(basePackages = “ com.glp.mapper ”)Used to find the interface path

(9)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.2.4.RELEASE</version>
    </parent>

    <groupId>com.glp</groupId>
    <artifactId>SpringLast</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <!-- Parent project spring-boot-starter-parent These two properties have been configured in and added to the plug-in, so they are not required -->
        <!--        <java.version>1.8</java.version>-->
        <!--        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>-->
        <mysql.version>5.1.47</mysql.version>
    </properties>

    <dependencies>


        <!-- spring-boot-starter-web: be based on SpringBoot Development dependency package,
                                 Will rely on again spring-framework Basic dependency package, aop Dependent packages, web Dependent packages,
                                 Others will be introduced, such as json，tomcat，validation Equal dependence -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- mybatis-spring-boot-starter: Mybatis Frame in SpringBoot Dependency package integrated in,
                                Mybatis Database object relation mapping Object-Relationl Mapping(ORM)Framework,
                                Others include Hibernate etc. -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

     
        <!--&lt;!&ndash; Mybatis Code generation tool:generate xml File, entity class, interface ->-->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>

        <!--druid-spring-boot-starter: Ali Druid Database connection pool, the same runtime needs-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>

        <!-- mysql-connector-java: mysql Database driver package
                                It is not directly used at compile time, but it is needed at run time, so it uses
                                scope runtime -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
            <scope>runtime</scope>
        </dependency>

        <!-- spring-boot-devtools: SpringBoot Hot deployment dependency package for -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>

        <!-- lombok: simplify bean Framework of code -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- spring-boot-starter-test: SpringBoot Test framework -->
        <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>

    <build>
        <plugins>
            <!-- SpringBoot Of maven Package plug-ins -->
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>