[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 parameters
mybatis.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
+ IDEA
Develop
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 toMoenyMapper
Interface 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 path
resources
below
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
+ MapperScannerConfigurer
To register
It can also be passed hereSqlSessionFactory
To 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
- For example, mapper interface:
-
Springboot configuration parameters:
- application. In the YML configuration file, specify
mybatis.mapper-locations=classpath:sqlmapper/*.xml
- application. In the YML configuration file, specify
-
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 the
mybatis-config.xml
In the file - First, in the configuration file, specify the loading parameters
mybatis.config-location=classpath:mybatis-config.xml
- Then specify the mapper
<mappers><mapper resource="sqlmapper/money-mapper.xml"/></mappers>
- This posture is common in integrating mybatis with non spring boot projects, and the related configuration of mybatis is usually placed in the
-
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