Spring boot (VI) integrates mybatis to operate MySQL 8

1、 Introduction

1.1 introduction to mybatis

Mybatis is an excellent persistence layer framework, which supports customized SQL, stored procedures and advanced mapping. Mybatis avoids almost all JDBC code and manually setting parameters and getting result sets.

1.2 development history of mybatis

Mybatis was originally an open source project ibatis of Apache. In 2010, the project was migrated from Apache Software Foundation to Google Code, renamed mybatis, and migrated to GitHub in November 2013.

1.3 differences between mybatis and Hibernate

Mybatis and Hibernate are excellent persistence frameworks that support JDBC (Java database connection) and JTA (Java transaction API) transaction processing.

Mybatis benefits

Hibernate benefits

1.4 mybatis integration mode

There are two ways to integrate mybatis:

The XML version is an old-fashioned configuration integration method, with heavy integration of XML files, and all SQL statements are written in XML; The annotated version is relatively simple and does not require XML configuration. It only needs annotations and code to operate the data.

2、 Annotated mybatis integration

development environment

Mybatis spring boot is a version of mybatis officially launched by mybatis to integrate spring boot.

2.1 adding dependencies

Set POM XML file, add the following configuration

<dependency>
    <groupId>MysqL</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>

Add MySQL and mybatis support.

2.2 configuring database connections

Set application Properties file, add the following configuration

# MyBatis 配置
spring.datasource.url=jdbc:MysqL://172.16.10.79:3306/mytestdb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.MysqL.cj.jdbc.Driver
mybatis.type-aliases-package=com.hello.springboot.mapper

Mapper file description

Mapper is the core of mybatis, where SQL is stored and where configuration database is mapped.

2.3 setting mapperscan package path

Directly in the boot file springbootapplication Configure @ mapperscan on the Java class, so you can save the trouble of identifying @ mapper on each mapper separately.

@SpringBootApplication
@MapperScan("com.hello.springboot.mapper")
public class SpringbootApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootApplication.class,args);
    }
}

2.4 add code

In order to demonstrate the simplicity, we will not do too much hierarchical processing. Here we are divided into entity class, mapper interface and controller class. The controller directly calls mapper interface for data persistence.

User class:

public class User {
    private Long id;
    private String name;
    private int age;
    private String pwd;
    //省去set、get方法
}

Usermapper interface:

public interface UserMapper {
    @Select("select * from user")
    @Results({
            @Result(property = "name",column = "name")
    })
    List<User> getAll();

    @Select("select * from user where id=#{id}")
    User getById(Long id);

    @Insert({"insert into user(name,age,pwd) values(#{name},#{age},#{pwd})"})
    void install(User user);

    @Update({"update user set name=#{name} where id=#{id}"})
    void Update(User user);

    @Delete("delete from user where id=#{id}")
    void delete(Long id);
}

It can be seen that all SQL is written in mapper interface.

Notes in mapper

Controller:

@RestController
@RequestMapping("/")
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @RequestMapping("/")
    public ModelAndView index() {
        User user = new User();
        user.setAge(18);
        user.setName("Adam");
        user.setPwd("123456");
        userMapper.install(user);
        ModelAndView modelAndView = new ModelAndView("/index");
        modelAndView.addObject("count",userMapper.getAll().size());
        return modelAndView;
    }
}

So far, the configuration of mybatis project has been completed and you can run debugging.

Annotated GitHub source code download: https://github.com/vipstone/springboot-example/tree/master/springboot-mybatis

3、 Mybatis integration for XML

3.1 adding dependencies

Set POM XML file, add the following configuration

<dependency>
    <groupId>MysqL</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>

Add MySQL and mybatis support.

3.2 configuring database connections

Set application Properties file, add the following configuration

# MyBatis 配置
spring.datasource.url=jdbc:MysqL://172.16.10.79:3306/mytestdb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.MysqL.cj.jdbc.Driver
mybatis.type-aliases-package=com.hello.springboot.entity
mybatis.config-locations=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

3.3 setting mapperscan package path

Directly in the boot file springbootapplication Configure @ mapperscan on the Java class, so you can save the trouble of identifying @ mapper on each mapper separately.

@SpringBootApplication
@MapperScan("com.hello.springboot.mapper")
public class SpringbootApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringbootApplication.class,args);
    }
}

3.4 configuration XML file

This example sets up two XML files, mybatis config. Under resource / mybatis XML (configure the basic properties of mybatis) and usermapper.xml (SQL statement for user and data interaction) under resource / mybatis / mapper.

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>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="Long" type="java.lang.Long"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
        <typeAlias alias="LinkedList" type="java.util.LinkedList"/>
    </typeAliases>
</configuration>

UserMapper. xml

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

<!--namespace是命名空间，是mapper接口的全路径-->
<mapper namespace="com.hello.springboot.mapper.UserMapper">

    <!--resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象-->
    <resultMap id="userResultMap" type="com.hello.springboot.entity.User">
        <id property="name" column="username"></id>
    </resultMap>

    <!--sql – 可被其他语句引用的可重用语句块-->
    <sql id="colums">
        id,username,pwd
    </sql>

    <select id="findAll" resultMap="userResultMap">
        select
        <include refid="colums" />
        from  user
    </select>

    <select id="findById" resultMap="userResultMap">
        select
        <include refid="colums" />
        from  user
        where  id=#{id}
    </select>

    <insert id="insert" parameterType="com.hello.springboot.entity.User" >
       INSERT INTO
       		user
       		(username,pwd)
       	VALUES
       		(#{name},#{pwd})
    </insert>

    <update id="update" parameterType="com.hello.springboot.entity.User" >
        UPDATE
        users
        SET
        <if test="username != null">username = #{username},</if>
        <if test="pwd != null">pwd = #{pwd},</if>
        username = #{username}
        WHERE
        id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Long" >
       DELETE FROM
       		 user
       WHERE
       		 id =#{id}
    </delete>

</mapper>

The SQL mapping file has few top-level elements (in the order in which they should be defined):

Note: the XML header files of config and mapper in mybatis are different.

Config header file

<?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">

Mapper header file

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

Mapper XML configuration: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html

3.5 add code

For the convenience of demonstration, we add three classes for function display, namely entity class user Java, mapper interface usermapper Java and controller class usercontroller Java, use the controller class to directly call the usermapper method for data storage and query.

User. java

package com.hello.springboot.entity;
public class User {
    private Long id;
    private String name;
    private int age;
    private String pwd;
	//省略set/get方法
}

UserMapper. java

package com.hello.springboot.mapper;
import com.hello.springboot.entity.User;
import java.util.List;
public interface UserMapper {
    List<User> findAll();
    User findById(Long id);
    void insert(User user);
    void update(User user);
    void delete(Long id);
}

Note: the method name in mapper must be consistent with that in mapper XML, otherwise the executed SQL will not be found.

UserController. java

@RestController
@RequestMapping("/")
public class UserController {
    @Resource
    private UserMapper userMapper;

    @RequestMapping("/")
    public ModelAndView index() {
        User user = new User();
        user.setAge(18);
        user.setName("Adam");
        user.setPwd("123456");
        userMapper.insert(user);
        ModelAndView modelAndView = new ModelAndView("/index");
        modelAndView.addObject("count",userMapper.findAll().size());
        return modelAndView;
    }

}

So far, the configuration of mybatis project has been completed and you can run debugging.

Download GitHub source code in XML: https://github.com/vipstone/springboot-example/tree/master/springboot-mybatis-xml

4、 Summary

So far, we have mastered two integration methods of mybatis, annotation integration and XML integration. The annotation version is more in line with programmers' code writing habits and is suitable for simple and fast query; The XML version can flexibly and dynamically adjust SQL, which is more suitable for large-scale project development. The specific choice depends on the development scenario and personal preferences.