Previously, I learned that the integration of single data source and multiple data sources of springboot + mybatis has been used. In this article, let's talk about the high-end usage of mybatis
1. Environmental collocation
1.1 pom dependency
- Based on springboot 2.5.6, you can also refer to the first article of mybatis
<!--Mysql Dependent package--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.0</version> </dependency>
1.2 yml configuration
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.0.100:3306/demo_test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
username: root
password: 123456
#mybatis resource file scanning and other related configurations
mybatis:
mapperLocations: classpath:mapper/*/*.xml
executorType: SIMPLE
configuration:
# The hump test is closed first
# map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
1.3 database table
user_info user information table
user_card user ID card table one user one ID card one to one
user_mobile user mobile number table a user has multiple mobile numbers
-- Export table demo_test.user_card structure CREATE TABLE IF NOT EXISTS `user_card` ( `user_id` bigint(20) NOT NULL COMMENT 'user ID', `card` varchar(50) DEFAULT NULL COMMENT 'ID', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ID card form'; -- Exporting table demo_test.user_card Data:~3 rows (about) DELETE FROM `user_card`; /*!40000 ALTER TABLE `user_card` DISABLE KEYS */; INSERT INTO `user_card` (`user_id`, `card`, `create_time`, `update_time`) VALUES (1, '111111', '2022-02-17 16:45:28', '2022-02-17 16:45:28'); /*!40000 ALTER TABLE `user_card` ENABLE KEYS */; -- Export table demo_test.user_info structure CREATE TABLE IF NOT EXISTS `user_info` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL COMMENT 'name', `age` int(11) DEFAULT NULL COMMENT 'Age', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; -- Exporting table demo_test.user_info Data:~3 rows (about) DELETE FROM `user_info`; /*!40000 ALTER TABLE `user_info` DISABLE KEYS */; INSERT INTO `user_info` (`id`, `name`, `age`, `create_time`, `update_time`) VALUES (1, 'Zhang San', 22, '2022-02-17 16:45:01', '2022-02-17 16:47:21'); /*!40000 ALTER TABLE `user_info` ENABLE KEYS */; -- Export table demo_test.user_mobile structure CREATE TABLE IF NOT EXISTS `user_mobile` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userId` bigint(20) DEFAULT NULL COMMENT 'user id', `mobile` varchar(50) NOT NULL COMMENT 'cell-phone number', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `userId` (`userId`,`mobile`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='User mobile phone table'; -- Exporting table demo_test.user_mobile Data:~2 rows (about) DELETE FROM `user_mobile`; /*!40000 ALTER TABLE `user_mobile` DISABLE KEYS */; INSERT INTO `user_mobile` (`id`, `userId`, `mobile`, `create_time`, `update_time`) VALUES (1, 1, '13011112222', '2022-02-17 16:45:51', '2022-02-17 16:45:51'), (2, 1, '13011112223', '2022-02-17 16:45:51', '2022-02-17 16:47:02'), (3, 1, '13011112224', '2022-02-17 16:45:51', '2022-02-17 16:47:02');
1.4 test code
@Resource
TestMapper testMapper;
@GetMapping("/data/selectBean")
public Object selectBean(@RequestParam(required = false) Long id) {
UserInfo userInfo = new UserInfo();
userInfo.setId(id);
List<UserInfo> userInfos = testMapper.selectBean(userInfo);
return userInfos;
}
1.5 java bean
User respectively_ info,user_card,user_mobile generates entity bean s and adds getter and setter methods
Only paste user_info bean, followed by one-to-one and one to many queries
@Data
public class UserInfo {
Long id;
String name;
Integer age;
//Personal ID card, one-to-one relationship
UserCard userCard;
//One to many relationship of personal mobile phone number
Set<UserMobile> userMobiles;
String createTime;
String updateTime;
}
2. resultMap mapping
2.1 direct mapping
- xml file, pay attention to the parameterType of the passed parameter and the resultType of the output parameter
<select id="selectBean" parameterType="com.example.demo.entity.UserInfo"
resultType="com.example.demo.entity.UserInfo">
select * from user_info where id = #{id}
</select>
- Test result output
[{
"id": 1,
"name": "Zhang San",
"age": 22,
"userCard": null,
"userMobileList": null,
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
}]
As a result, you can see that the createTime and updateTime in the original table have values, but the output is also empty.
resolvent:
- Open the hump configuration map underscore to camel case in yml: true
- Use custom mapping resultMap
Next, for the convenience of testing, open the hump configuration from here
2.2 user defined map
- The xml code is modified as follows
//For example, many queries under this xml need userInfo, which can be extracted for use
//The meaning of id alias needs to be unique. type: maps to the entity class in java bean
<resultMap id="userInfoMap" type="com.example.demo.entity.UserInfo">
//id primary key column / column name in the database property / column name in the corresponding entity class
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</resultMap>
//The output parameter here needs to use resultMap to point to the id above
<select id="selectBean" parameterType="com.example.demo.entity.UserInfo"
resultMap="userInfoMap">
select * from user_info where id = #{id}
</select>
- Test result output
[{
"id": 1,
"name": "Zhang San",
"age": 22,
"userCard": null,
"userMobileList": null,
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
}]
user_ The data in the info table can be seen. Next, let's see how to implement the one-to-one extension of userCard
3. One to one data query
3.1 Association implementation
<resultMap id="userInfoMap" type="com.example.demo.entity.UserInfo">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
<association property="userCard" javaType="com.example.demo.entity.UserCard">
<id property="userId" column="user_id"/>
<result property="card" column="card"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</association>
</resultMap>
<select id="selectBean" parameterType="com.example.demo.entity.UserInfo"
resultMap="userInfoMap">
select * from user_info u
left join user_card uc on u.id=uc.user_id
where u.id = #{id}
</select>
- Test result output
[{
"id": 1,
"name": "Zhang San",
"age": 22,
"userCard": {
"userId": 1,
"card": "111111",
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
},
"userMobileList": null,
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
}]
There is an obvious problem. If two tables have the same fields, mysql query will automatically remove the latter. In this case, many aliases need to be established to use
If this requirement cannot be met, the following method can also be used
3.2 implementation of primary key query
Return the expected complex type by executing another SQL mapping statement
- xml code modification
<resultMap id="userInfoMap" type="com.example.demo.entity.UserInfo">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
//The associated query property corresponds to the userCard column in the entity class
//Use the value of the current id column to query the corresponding sql query id selectUserCard
<association property="userCard" column="id" select="selectUserCard">
//The following mappings are similar
<id property="userId" column="user_id"/>
<result property="card" column="card"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</association>
</resultMap>
<select id="selectBean" parameterType="com.example.demo.entity.UserInfo"
resultMap="userInfoMap">
select * from user_info where id = #{id}
</select>
//Separate queries can be used for other businesses
<select id="selectUserCard" parameterType="Long" resultType="com.example.demo.entity.UserCard">
select * from user_card where user_id=#{id}
</select>
- Test result output
[{
"id": 1,
"name": "Zhang San",
"age": 22,
"userCard": {
"userId": 1,
"card": "111111",
"createTime": "2022-02-17 16:45:28",
"updateTime": "2022-02-17 16:45:28"
},
"userMobileList": null,
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
}]
The results show that the one-to-one requirement is realized. Next, analyze the one to many requirement
3. One to many data query
- xml code modification
<resultMap id="userInfoMap" type="com.example.demo.entity.UserInfo">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
//Query again with the value of id column. The sql id is selectUserMobile
<collection property="userMobiles" column="id" select="selectUserMobile">
<id property="userId" column="user_id"/>
<result property="mobile" column="mobile"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</collection>
</resultMap>
<!--left join user_card uc on u.id=uc.user_id-->
<select id="selectBean" parameterType="com.example.demo.entity.UserInfo"
resultMap="userInfoMap">
select * from user_info u where u.id = #{id}
</select>
<select id="selectUserMobile" parameterType="Long" resultType="com.example.demo.entity.UserMobile">
select * from user_mobile where user_id=#{id}
</select>
- Test result output
[{
"id": 1,
"name": "Zhang San",
"age": 22,
"userCard": null,
"userMobiles": [{
"userId": 1,
"mobile": "13011112223",
"createTime": "2022-02-17 16:45:51",
"updateTime": "2022-02-17 16:47:02"
}, {
"userId": 1,
"mobile": "13011112224",
"createTime": "2022-02-17 16:45:51",
"updateTime": "2022-02-17 16:47:02"
}, {
"userId": 1,
"mobile": "13011112222",
"createTime": "2022-02-17 16:45:51",
"updateTime": "2022-02-17 16:45:51"
}],
"createTime": "2022-02-17 16:45:01",
"updateTime": "2022-02-17 16:47:21"
}]
The above are all used by the author in the project. If you want to know other usage, please understand it by yourself
That is the whole content of this chapter.
Previous: Mybatis second words - mybatis, the joy of multiple data sources, do you understand?
Next: The fourth part of mybatis - let's peel off the heart of mybatis layer by layer and analyze the source code
Time and tide wait for no man