[springboot mybatis series] mappermapper interface and SQL bind several postures

[DB series] the mapper interface of the spring boot series mybatis binds several postures to SQL

Usually when we use Mybatis to develop, we will choose XML file to write corresponding SQL, then establish the binding relation between Mapper interface and XML file of SQL, then we can execute SQL by calling mapper interface in the project.

So how to bind mapper interface to SQL? This article will introduce four common postures

• Default policy
• Springboot configuration parametersmybatis.mapper-locations
• <mapper>appoint
• Sqlsessionfactory specifies

<!– more –>

1. Environmental preparation

1. Database preparation

Use MySQL as the example database of this article and add a new table

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name ` varchar (20) not null default '' comment 'user name',
  `Money ` int (26) not null default '0' comment 'money',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_ at` timestamp NOT NULL DEFAULT CURRENT_ Timestamp comment 'creation time',
  `update_ at` timestamp NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ Timestamp comment 'update time',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. Project environment

With the help ofSpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEADevelop

POM dependencies are as follows

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

DB configuration informationapplication.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

II. Example demonstration

After setting up the environment, prepare the corresponding entity class and mapper interface

1. Entity class, mapper interface

Database entity class:MoneyPo

@Data
@NoArgsConstructor
@AllArgsConstructor
public class MoneyPo {
    private Integer id;

    private String name;

    private Long money;

    private Integer isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;
}

A basic mapper interface

@Mapper
public interface MoneyMapper {
    int savePo(@Param("po") MoneyPo po);
}

A demo service

@Repository
public class MoneyRepository {
    private Random random = new Random();

    public void testMapper() {
        MoneyPo po = new MoneyPo();
        po.setName("mybatis user");
        po.setMoney((long) random.nextInt(12343));
        po.setIsDeleted(0);

        moneyMapper.savePo(po);
        System.out.println("add record: " + po);
}

2. SQL file

Write the XML file of SQL as follows

<?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.git.hui.boot.mybatis.mapper.MoneyMapper">

    <insert id="savePo" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo" useGeneratedKeys="true"
            keyProperty="po.id">
      INSERT INTO `money` (`name`, `money`, `is_deleted`)
      VALUES
      (#{po.name}, #{po.money}, #{po.isDeleted});
    </insert>
</mapper>

3. Mapper and SQL binding

The above is the basic knowledge of curd implementation at the code level, which is basically the routines of mybatis operation. There is nothing to pay special attention to; Next, let’s move on to the topic of this article

How to tell mybatis toMoenyMapperInterface associated with XML file

3.1 default mode

The default binding method is adopted, which does not require us to do additional operations. The focus is to follow the rules

• The directory structure of XML is completely consistent with the package path of mapper interface
• The XML file name is exactly the same as the mapper interface name (note that the case should be exactly the same)

Please note that the other one above is exactly the same

When the default binding method is used, an example is shown in the figure above; Special attention should be paid to the case of file names and the directory level of XML files

If you use the above method, you will still be prompted with problems during execution. The idea of troubleshooting is to check whether the class file generated in the target directory is together with the XML file, as shown in the following figure is a normal case

Again

• Based on the above case, we can directly write the XML file together with the mapper interface instead of the resource pathresourcesbelow

3.2 springboot configuration

Springboot provides a simple configuration to specify the binding between mapper interface and SQL, which can be configured in one line

mybatis:
  mapper-locations: classpath:sqlmapper/*.xml

Using this method is relatively simple. It does not require the XML file to be consistent with the mapper interface file name; No path hierarchy is specified

3.3 mapper label

The mapper tag needs to be placed in the configuration file of mybatis, so we first specify the file path through the configuration parameter of springboot

mybatis:
  configuration:
    config-location: classpath:mybatis-config.xml

Under resource file, create a new filemybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//ibatis.apache.org//DTD Config 3.1//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <mappers>
        <mapper resource="sqlmapper/money-mapper.xml"/>
    </mappers>
</configuration>

It is also feasible to specify the registration relationship through the mapper tag above. For details, please refer to the official documents!

https://mybatis.org/mybatis-3/configuration.html#mappers

3.4 SqlSessionFactory

In the previous blog post on mapper interface registration, we introducedqlSessionFactory+ MapperScannerConfigurerTo register

It can also be passed hereSqlSessionFactoryTo specify the name of the XML file

@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setMapperLocations(
            //Set the location of mybatis XML. Here, the mybatis annotation method is used, and the XML file is not configured
            new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*.xml"));
    //Register typehandler for global use
    bean.setTypeHandlers(new Timestamp2LongHandler());
    bean.setPlugins(new SqlStatInterceptor());
    return bean.getObject();
}

4. Summary

This paper mainly introduces four gestures of mapper interface and SQL file relationship binding, and understands the characteristics of several different gestures. In the actual project development, select one

• Default: under the resource resource directory, the directory level of the XML file is completely consistent with the package level of the mapper interface, and the XML file name is also completely consistent with the file name of the mapper interface

  • For example, mapper interface:com.git.hui.boot.mybatis.mapper.MoneyMapper
  • Corresponding XML file:com/git/hui/boot/mybatis/mapper/MoneyMapper.xml

• Springboot configuration parameters:

  • application. In the YML configuration file, specifymybatis.mapper-locations=classpath:sqlmapper/*.xml

• Mybatis config configuration file

  • This posture is common in integrating mybatis with non spring boot projects, and the related configuration of mybatis is usually placed in themybatis-config.xmlIn the file
  • First, in the configuration file, specify the loading parametersmybatis.config-location=classpath:mybatis-config.xml
  • Then specify the mapper <mappers><mapper resource="sqlmapper/money-mapper.xml"/></mappers>

• Sqlsessionfactory specifies

  • You can specify mapper files directly in sqlsessionfactory

//Set the location of mybatis XML. Here, the mybatis annotation method is used, and the XML file is not configured
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*.xml"));

In addition to the above methods, mybatis also supports the XML free method, which completely relies on annotations to realize SQL assembly. Therefore, there is no mapping relationship binding. For the case of annotations, please refer to the mybatis + annotation integration in the blog [DB series]

3. Can not miss the source code and related knowledge points

0. Project

• Project: https://github.com/liuyueyi/spring-boot-demo
• Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/104-mybatis-ano
• Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/103-mybatis-xml

Mybatis blog series

• [DB series] several ways of mapper registration of mybatis in springboot series
• [DB series] mybatis plus multi data source configuration
• [DB series] mybatis realizes multi data source switching based on abstractroutingdatasource and AOP
• [DB series] mybatis multi data source configuration and use
• [DB series] multi data source configuration and use of jdbctemplate
• [DB series] mybatis plus code is automatically generated
• [DB series] mybatisplus integration
• [DB series] mybatis + annotation integration
• [DB series] mybatis + XML integration

1. A gray blog

The above contents are only the words of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct and be grateful

The following is a gray personal blog, which records all blog posts in study and work. Welcome to visit

• A personal blog https://blog.hhui.top
• A gray blog spring special blog http://spring.hhui.top