同⼀条SQL语句，MyBatis查询结果与MySQL执⾏结果居然不⼀致！
⼀、前⾔
不知道⼤家平时在开发过程中有没有遇到这样的问题，同⼀条SQL语句，MyBatis 查询结果与 MySQL 执⾏结果居然不⼀致，具体说应该是MyBatis 查询结果⽐MySQL 执⾏结果的数据更少。不要不相信，如果不注意，这样的坑，你还真有可能会踩的。
⼆、代码演⽰
下⾯⽤代码简单演⽰⼀下：
1. 创建两张⽤于测试的数据表：
teacher表：
CREATE TABLE`teacher`(
`id`int(11)NOT NULL,
`name`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`scid`int(11)NULL DEFAULT NULL,
`code`int(255)NULL DEFAULT NULL,
PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
测试数据：
INSERT INTO`teacher`(`id`,`name`,`scid`)VALUES(1,'jack',1);
INSERT INTO`teacher`(`id`,`name`,`scid`)VALUES(2,'jack',1);
INSERT INTO`teacher`(`id`,`name`,`scid`)VALUES(3,'jack',2);
INSERT INTO`teacher`(`id`,`name`,`scid`)VALUES(4,'jack',2);
school表：
CREATE TABLE`school`(
`id`int(11)NOT NULL,
`name`varchar(255)CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`code`int(11)NULL DEFAULT NULL,
PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
测试数据：
INSERT INTO`school`(`id`,`name`,`code`)VALUES(1,'兰陵⾼中',1001);
INSERT INTO`school`(`id`,`name`,`code`)VALUES(2,'城东⾼中',1002);
2. 创建⼀个springboot项⽬，项⽬结构如下：
3. 导⼊maven依赖：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId&batis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>            <version>2.1.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>            <version>1.2.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>springboot结构
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
4. 编写l配置⽂件：
server:
port: 8070
spring:
datasource:
druid:
driver-class-name: sql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/bg-learnsp?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false      username: root
password: root
mybatis:
mapper-locations: classpath:mapper/*.xml
logging:
level:
root: info
com.learn.mapper: debug
5. 编写数据库相关实体类：
Teacher类：
package ity;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
private int scid;
private int code;
}
School类：
package ity;
import lombok.Data;
import java.util.List;
@Data
public class School {
private int id;
private int code;
private String name;
// School 与Teacher 是⼀对多的关系
private List<Teacher> teachers;
}
6. 编写school相关mapper：
SchoolMapper.java：
package com.learn.mapper;
import ity.School;
import java.util.List;
public interface SchoolMapper {
List<School>selectAll();
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.learn.mapper.SchoolMapper">
<resultMap id="BaseResultMap"type="ity.School">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="code" jdbcType="INTEGER" property="code"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<collection property="teachers" ofType="ity.Teacher">            <result column="tname" jdbcType="VARCHAR" property="name"/>        </collection>
</resultMap>
<select id="selectAll" resultMap="BaseResultMap">
SELECT
s.id,
s.NAME,
t.NAME AS tname
FROM
school s,
teacher t
WHERE
s.id = t.scid
</select>
</mapper>
7. SchoolService：
@Service
public class SchoolService {
@Autowired
private SchoolMapper schoolMapper;
public List<School>listAll(){
return schoolMapper.selectAll();
}
}
8. SchoolController：
@RestController
public class SchoolController {
@Autowired
private SchoolService schoolService;
@GetMapping("/listAllSchool")
public List<School>listAll(){
return schoolService.listAll();
}
}
9. 启动类添加相关配置：
@SpringBootApplication
@MapperScan("com.learn.mapper")
@EnableTransactionManagement
public class SpringbootDataerrorApplication {
public static void main(String[] args){
SpringApplication.run(SpringbootDataerrorApplication.class, args);
}
}
测试：
上⾯的school与teacher是⼀对多的关系，在l⽂件中使⽤resultMap标签配置它们的对应映射关系。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。