Introduction:

In the previous article, we introduced some common methods of spring-data-jpa. In this article, we introduced the integration of mybatis and Spring Boot, and some common methods.

Integrate:

There are two ways to integrate Spring applications, one is the integration of Spring Boot applications, and the other is the integration of Spring Boot applications. Click on me)

1. Add pom dependencies:

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

2. Two ways of integration

a. Through annotations:

1. Packet scanning:

@SpringBootApplication
@MapperScan("cn.sunxyz.mapper")
public class SpringBootMybatisApplication {

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

2. Annotation Statement:

@Mapper
public interface UserMapper {
}

Either of them can be chosen. It is suggested that package scanning be used.

b. Through configuration files

1). Configuration by yml

spring:
  datasource:
    ##Import the specified sql
    schema: import.sql
    url: jdbc:mysql://localhost:3306/spring-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    platform: mysql
#Using yml
mybatis:
  type-aliases-package: cn.sunxyz.domain
  mapper-locations: ['mapper/UserMapper.xml']

The classmates of attribute mybatis.xml can also be configured through xml
2. Configuration through xml
<1>. Configure the yml file first

#How to use xml
mybatis:
  config-location: mybatis-config.xml

<2>. Configure 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>
        <package name="cn.sunxyz.domain"/>
    </typeAliases>
    <mappers>
       <mapper resource="mapper/UserMapper.xml"></mapper>
        <!--<package name="cn.sunxyz.mapper"/>-->
    </mappers>
</configuration>

Note: The XXXMapper object in B.1 and B.2 needs to be hosted by @Mapper to Spring Book before it can be used properly.
Suggestion: A and b choose one, recommend using a. Annotation, of course, if you are used to the way of XML configuration can also use xml.

Use:

Two ways of sql query:

(Maybe three would be better)
Entity class object

public class User {

    private Integer id;

    private String name;

    private String password;
     
    /**Omit set/get**/   
}    

Annot a tions

1. General Query (Simple Query):

@Mapper
public interface UserMapper {

    // Get the primary key
    @Insert("INSERT INTO user(name,password) VALUES (#{name}, #{password}) ")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    @Delete("DELETE FROM user WHERE id　= #{id}")
    int delete(@Param("id") Integer id);

    @Update("UPDATE user SET name = #{name}, password = #{password} WHERE id = #{id}")
    int update(User user);

    @Select("SELECT id, name, password FROM user WHERE id = #{id}")
    @Results(id = "userMap", value = { @Result(column = "id", property = "id", javaType = Integer.class),
            @Result(column = "name", property = "name", javaType = String.class),
            @Result(column = "password", property = "password", javaType = String.class) })
    User findById(Integer id);

    @Select("SELECT * FROM user")
    @ResultMap("userMap")
    List<User> fingAll();
}

2. Complex Query (Dynamic sql):

The mappper object:

@Mapper
public interface UserMapper {

    // Dynamic generation of sql
    @SelectProvider(type = UserMapperProvider.class, method = "findByNameLike")
    List<User> findByNameLike(String name);

    //Using map with multiple parameters
    @SelectProvider(type = UserMapperProvider.class, method = "findByNameAndPassword")
    List<User> findByNameAndPassword(String name, String password);

    @InsertProvider(type = UserMapperProvider.class, method = "insert")
    int insertUser(User user);

    @DeleteProvider(type = UserMapperProvider.class, method = "delete")
    int deleteUser(Integer id);
}

The mappperProvider object:

public class UserMapperProvider {

    // Dynamic generation of sql
    public String findByName(String name) {
        String sql = "SELECT * FROM user";
        if (StringUtils.isEmpty(name)) {
            return sql;
        }
        sql += " WHERE name LIKE '%" + name + "%'";
        return sql;
    }

    // Use tool classes to prepare the same SQL statements
    public String findByNameLike(String name) {
        return new SQL() {
            {
                SELECT("id, name, password");
                FROM("user");
                WHERE("name LIKE '%" + name + "%'");
            }
        }.toString();
    }

    public String findByNameAndPassword(Map<String, Object> map) {

        String name = (String) map.get("param1");
        String password = (String) map.get("param2");

        return new SQL() {
            {
                SELECT("id, name, password");
                FROM("user");
                WHERE("name = " + name);
                AND();
                WHERE("password = " + password);
            }
        }.toString();

    }

    public String update(User user) {
        return new SQL() {
            {

                if (!StringUtils.isEmpty(user.getId())) {
                    UPDATE("user");
                    if (!StringUtils.isEmpty(user.getName())) {
                        SET("name = #{name}");
                    }
                    if (user.getPassword() != null) {
                        SET("password = #{password}");
                    }
                    WHERE("id = #{id}");
                }
            }
        }.toString();
    }

    public String insert(User user) {
        return new SQL() {
            {
                INSERT_INTO("user");
                VALUES("name", "#{name}");
                VALUES("password", "#{password}");

            }
        }.toString();
    }

    public String delete(Integer id) {
        return new SQL() {
            {
                DELETE_FROM("user");
                WHERE("id = #{id}");
            }
        }.toString();
    }

}

B). The way of xml:

3.xml configuration:
The mapper object:

@Mapper
public interface UserMapper {

    int insertUserXml(User user);
    
}  

The mapper.xml file:

<?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">
<!-- Specify the workspace, the same as the interface name, the source code is not looked at, guess should be through"There namespace.Lower method id"To locate the method -->
<mapper namespace="cn.sunxyz.mapper.UserMapper">
    <!-- If you do not need to return the primary key automatically, the useGeneratedKeys="true" keyProperty="id"Just remove it.(Of course, if you don't need to return the primary key automatically, just use the annotations.) -->
   <insert id="insertUserXml" parameterType="User" keyProperty="id" useGeneratedKeys="true">
       <![CDATA[
       INSERT INTO USER
       (
           name,
           password
       )
       VALUES
       (
           #{name, jdbcType=VARCHAR},
           #{password, jdbcType=VARCHAR}
       )
       ]]>
   </insert>
</mapper>  

Mixing:

4. Annotation/xml Mixing
These two methods can be used in conjunction with each other, for example:

@Mapper
public interface UserMapper {

    int insertUserXml(User user);

    @Delete("DELETE FROM user WHERE id　= #{id}")
    int delete(@Param("id") Integer id);
}   

Eliminate the mapper.xml file
Recommendation: In general, it is not recommended to use mixed queries. If there are simple queries and complex queries, recommendation 1. General queries (simple queries) and 3.xml configuration are mixed queries.

Paging:

Using PageHelper:

Add pom dependencies:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.1.0</version>
</dependency>

Add configuration:

@Configuration
public class MyBatisConfiguration {
   
    @Bean
    public PageHelper pageHelper() {
        PageHelper pageHelper = new PageHelper();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
   
}

Use:

    public void contextLoads() {
        PageHelper.startPage(1,2);//Unable to use dynamic sql synchronously with annotation generation
        List<User> users = userMapper.fingAll();
//        List<User> users = userMapper.findByNameLike("user");
        logger.info(users.toString());
    }

Pit: When using 2. Complex queries (dynamic sql) for queries, there will be exceptions. If you are planning to use PageHelper, be careful.

Conclusion:

After a long delay, I finally started again. There are still many things that I haven't mentioned in this article. There are many things and details in MyBatis that need to be noticed. If there aren't too many responsible queries in development, Spring Data JPA is recommended.

Reference material:

Spring Boot Integrates MyBatis
Chapter 5 springboot + mybatis
SpringBoot integration mybatis There are some extensions to MyBatis content in the recommendation
Spring Boot MyBatis Connects to Mysql Database [Learn Spring Boot from scratch]

Learning materials:

Official Documents
MyBatis Introductory Learning Course