1.mybatis基础操作

一、前言

本篇将会带大家熟悉mybatis如何实现常用的增删改查。老鸟可以略过。

二、数据准备

DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',name VARCHAR(50) NOT NULL COMMENT '姓名',age INT COMMENT '年龄',gender TINYINT DEFAULT 0 COMMENT '性别,0|女,1|男',birthday DATE NOT NULL COMMENT '出生日期',major VARCHAR(50) COMMENT '专业',phone VARCHAR(100) COMMENT '电话号码',deleted INT DEFAULT 0 COMMENT '是否有效,0|有效,1|无效',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT='学生表';

全局配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""https://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><!-- 配置别名 -->xml<typeAliases><!-- 这里typeAlias和package是有序的,要先配置typeAlias再配置package --><typeAlias type="org.apache.ibatis.mytest.learntest.entity.Student" alias="student"/><package name="org.apache.ibatis.mytest.learntest.entity"/></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC"></transactionManager><dataSource type="POOLED"><property name="driver" value="com.mysql.cj.jdbc.Driver" /><property name="url" value="jdbc:mysql://localhost:3306/test?pedantic=true&amp;rewriteBatchedStatements=true&amp;allowMultiQueries=true&amp;useCursorFetch=true" /><property name="username" value="root" /><property name="password" value="123456" /></dataSource></environment></environments><mappers><!-- class:本地类文件 resource:磁盘路径 resource:classpath路径 url:网络路径 --><!-- 注意一个类不能被扫描到多次 --><mapper class="org.apache.ibatis.mytest.learntest.mapper.StudentMapper" />
<!--    <package name="org.apache.ibatis.mytest.learntest.mapper"/>--></mappers>
</configuration>

这里注意节点配置的顺序, 还有mappers节点的配置, mappers节点扫描的类不能重复扫描。

细心的人应该发现了, mybatis的配置文件(mybatis-config.xml)是使用DTD文件约束的。对于xml文件定于和约束有两种方式, 分别是DTD和schema。

简单来说, DTD适合用于较为简单的 XML 文档结构定义,只有 CDATA（字符数据）、ID（唯一标识符）等基本类型

schema 提供了强类型定义, 能够精确校验元素和属性的内容类型并且支持命名空间, 更适合现代 XML 应用。

既然mybatis使用DTD文件约束, 那么它的文档结构必然是很简单的。

建表语句

DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',name VARCHAR(50) NOT NULL COMMENT '姓名',age INT COMMENT '年龄',gender TINYINT DEFAULT 0 COMMENT '性别,0|女,1|男',birthday DATE NOT NULL COMMENT '出生日期',major INT COMMENT '专业',phone VARCHAR(100) COMMENT '电话号码',deleted INT DEFAULT 0 COMMENT '是否有效,0|有效,1|无效',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
) COMMENT='学生表';

实体类定义

import java.time.LocalDate;
import java.time.LocalDateTime;public class Student {private Integer id;private String name;private Integer age;private Integer gender;private LocalDate birthday;private Integer major;private String phone;private Integer deleted;private LocalDateTime createdAt;private LocalDateTime updatedAt;// ... 此处省略getter/setter
}

定义Mapper文件

public interface StudentMapper {int insertStudent(Student student);}

定义StudentMapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.apache.ibatis.mytest.learntest.mapper.StudentMapper"><insert id="insertStudent">insert into student(name, age, gender, birthday, major, phone)values(#{name}, #{age}, #{gender}, #{birthday}, #{major}, #{phone})</insert></mapper>

公共方法

private SqlSessionFactory sqlSessionFactory;@BeforeEachpublic void setup() {try (Reader reader = Resources.getResourceAsReader("your mybatis-config.xml path")) {// DefaultSqlSessionFactorysqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);} catch (IOException e) {throw new RuntimeException(e);}}

这里Resources.getResourceAsReader指定配置文件的位置即可

三、基本crud

1.insert

单条插入

@Testvoid testInsert() {// 注意openSession()方法默认是开启事务的,需要手动提交事务try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);Student student = getStudent();int i = mapper.insertStudent(student);System.out.println("i = " + i);}}private Student getStudent() {Student student = new Student();student.setName("小乔同学");student.setAge(18);student.setGender(1);student.setBirthday(LocalDate.of(2000, 9, 3));student.setMajor(1);student.setPhone("13400001814");return student;}

这里注意下openSession方法, 没有指定自动提交的话, 它默认是需要手动提交的, 也就是开启了事务, 咋们测试的时候用自动提交设置autoCommit为true即可

insert into values(…),(…)

在StudentMapper.xml文件中加一个代码模块

<insert id="insertValues">insert into student(name, age, gender, birthday, major, phone)values<foreach collection="list" item="item" index="index" separator=",">(#{item.name}, #{item.age}, #{item.gender}, #{item.birthday}, #{item.major}, #{item.phone})</foreach></insert>

在StudentMapper.java类中加入一个方法

int insertValues(List<Student> students);

再加一个测试类

@Testvoid batchInsert() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = getStudentList();int i = mapper.insertValues(students);System.out.println("i = " + i);}}

executeBatch

使用executeBatch批量插入

/*** executeBatch*/@Testvoid insertByExecuteBatch() {try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = getStudentList2();for (Student student : students) {int i = mapper.insertStudent(student);System.out.println("i = " + i);}sqlSession.flushStatements();}}/*** 测试数据*/
private List<Student> getStudentList2() {Student student1 = new Student();student1.setName("小黑同学");student1.setAge(19);student1.setGender(1);student1.setBirthday(LocalDate.of(2001, 9, 3));student1.setMajor(3);student1.setPhone("13500001815");Student student2 = new Student();student2.setName("小黄同学");student2.setAge(19);student2.setGender(1);student2.setBirthday(LocalDate.of(2002, 9, 3));student2.setMajor(2);student2.setPhone("13400001816");return List.of(student1, student2);}

注意在调用sqlSessionFactory.openSession的时候, 参数ExecutorType选择ExecutorType.BATCH, 执行完之后需要执行sqlSession.flushStatements()方法

讲白了就是for循环中在做addBatch动作, flushStatements在做executeBatch动作

2.update

单个update

在StudentMapper.xml文件中加一个sql模块

<update id="updateById">updatestudentsetname=#{name},age=#{age},gender=#{gender},birthday=#{birthday},major=#{major},phone=#{phone}whereid=#{id}</update>

其它修改

// StudentMapper.java
int updateById(Student student);// 测试方法
@Testvoid update() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);Student student = new Student();student.setId(1);student.setName("小乔同学");student.setAge(25);student.setGender(1);student.setBirthday(LocalDate.of(2000, 9, 3));student.setMajor(1);student.setPhone("13400001814");int i = mapper.updateById(student);}}

update没有什么好说的

批量update

executeBatch批量操作可以参考insert时候的executeBatch方法。那么想要在sql模块中写sql来执行update应该怎么办, 下面来演示一下

现在数据库中 小黑同学和小黄同学的年龄(age)都是19, 现在改成30和20

4,小黑同学,19
5,小黄同学,19

数据库连接添加属性

allowMultiQueries=true

这个是必须的

StudentMapper.xml文件中添加sql模块

<update id="batchUpdate"><foreach collection="list" item="item" separator=";">updatestudentsetname=#{item.name},age=#{item.age},gender=#{item.gender},birthday=#{item.birthday},major=#{item.major},phone=#{item.phone}whereid=#{item.id}</foreach></update>

StudentMapper.java中添加方法

int batchUpdate(List<Student> students);

测试

@Testvoid batchUpdate() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> studentList3 = this.getStudentList3();int i = mapper.batchUpdate(studentList3);}}private List<Student> getStudentList3() {Student student1 = new Student();student1.setId(4);student1.setName("小黑同学");student1.setAge(30);student1.setGender(1);student1.setBirthday(LocalDate.of(2001, 9, 3));student1.setMajor(3);student1.setPhone("13500001815");Student student2 = new Student();student2.setId(5);student2.setName("小黄同学");student2.setAge(20);student2.setGender(1);student2.setBirthday(LocalDate.of(2002, 9, 3));student2.setMajor(2);student2.setPhone("13400001816");return List.of(student1, student2);}

3.delete

delete就相对简单了, 咋们删除id为5的小黄同学

StudentMapper.xml文件中添加sql模块

<delete id="deleteById">delete from student where id=#{id}</delete>

StudentMapper.java中添加方法

int deleteById(int id);

测试

@Testvoid deleteById() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);int i = mapper.deleteById(5);}}

4.query

普通查询

StudentMapper.xml文件中添加sql模块

<select id="selectList" resultType="org.apache.ibatis.mytest.learntest.entity.Student">select * from student</select>

StudentMapper.java中添加方法

List<Student> selectList();

测试

@Testvoid queryAll() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = mapper.selectList();for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}}}

查询结果如下

1, 小乔同学, 25, 1, 2000-09-03, 1, 13400001814, 0, null, null
2, 小杜同学, 18, 0, 2001-09-03, 3, 13500001815, 0, null, null
3, 小白同学, 18, 0, 2002-09-03, 2, 13400001816, 0, null, null
4, 小黑同学, 30, 1, 2001-09-03, 3, 13500001815, 0, null, null

注意看createdAt和updateAt两个属性没有值哦。 在db中的字段名为create_at, 在实体类属性的名字是createdAt, mybatis在字段映射的时候没有匹配上, 就忽略了该属性

这种自动映射实体类字段的时候, 需要开启全局配置打开数据库字段与实体类属性映射时自动将数据库字段下划线转驼峰, 对应的全局配置属性为mapUnderscoreToCamelCase

此时我们在全局配置文件mybatis-config.xml中添加如下配置即可。

<settings><setting name="mapUnderscoreToCamelCase" value="false"/>
</settings>

这里需要注意settings标签节点所配置的位置, 需要放在配置文件的最开始, 尽量mybatis配置节点的顺序按照dtd约束的先后顺序放, 避免其它异常

此时再去查询结果就正常了

指定resultSetType和resultSetConcurrency

mybatis默认指定了resultSetConcurrency的值为ResultSet.CONCUR_READ_ONLY, 也就是ResultSet中的数据记录是只读的，不可以修改。但是resultSetType是可以修改的。
还记得在【JDBC操作回顾】一文中说过, 读取db中变化的数据需要这两个配置

1. 设置resultSetType为ResultSet.TYPE_SCROLL_INSENSITIVE
2. 设置resultSetConcurrency为ResultSet.CONCUR_UPDATABLE

既然mybatis限制了ResultSet中的数据记录是只读的, 那么我们在mysql数据库下仅能做的就是修改resultSetType的值

1. ResultSet.TYPE_FORWARD_ONLY(默认): 表示一个 ResultSet 对象的游标只能向前移动，而不能向后移动或滚动。
2. ResultSet.TYPE_SCROLL_INSENSITIVE: 允许游标前后滚动，其它session修改db中的数据’不会影响’到当前session的数据

下面用个例子示范resultSetType为ResultSet.TYPE_SCROLL_INSENSITIVE, resultSetConcurrency为ResultSet.CONCUR_READ_ONLY下结果集ResultSet移动的操作
在StudentMapper.xml文件中加入sql模块

<select id="selectListWithResultType" resultType="org.apache.ibatis.mytest.learntest.entity.Student" resultSetType="SCROLL_INSENSITIVE">select * from student</select>

在StudentMapper.java文件中添加方法

List<Student> selectListWithResultType();

现在只写这些还是不行的, 因为mybatis隐藏了ResultSet对象, 还需要找个扩展的地方来处理ResultSet

在mybatis-config.xml的配置文件中加一个typeHandler(类型处理器)

<typeHandlers><!-- 这里student使用别名 --><typeHandler handler="org.apache.ibatis.mytest.learntest.typehandlers.StudentResultSetTypeTypeHandler" javaType="student"/></typeHandlers>

注意它放的位置要在typeHandlers标签节点之后
这里大家看不懂没关系, 仅仅只是一个提前预知,只需要知道有这么个东西就行,后面会有详细介绍

类型处理器 StudentResultSetTypeTypeHandler的定义如下

public class StudentResultSetTypeTypeHandler extends BaseTypeHandler<Student> {@Overridepublic void setParameter(PreparedStatement ps, int i, Student parameter, JdbcType jdbcType) throws SQLException {ps.setObject(i, parameter);}@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Student parameter, JdbcType jdbcType) throws SQLException {ps.setObject(i, parameter);}@Overridepublic Student getNullableResult(ResultSet rs, String columnName) throws SQLException {Student student = new Student();int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");int gender = rs.getInt("gender");LocalDate birthday = rs.getObject("birthday", LocalDate.class);String major = rs.getInt("major");String phone = rs.getString("phone");int deleted = rs.getInt("deleted");LocalDateTime createdAt = rs.getObject("created_at", LocalDateTime.class);LocalDateTime updatedAt = rs.getObject("updated_at", LocalDateTime.class);student.setId(id);student.setName(name);student.setAge(age);student.setGender(gender);student.setBirthday(birthday);student.setMajor(major);student.setPhone(phone);student.setDeleted(deleted);student.setCreatedAt(createdAt);student.setUpdatedAt(updatedAt);System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());rs.first();return student;}@Overridepublic Student getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return null;}@Overridepublic Student getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {return null;}
}

这里注意使用了rs.first();来移动, 会导致测试类无限打印, 那也没关系, 至少说明可以达到ResultSet可以前后滚动的效果, 其它移动api大家可以自行尝试。

测试方法

@Test
void selectListWithResultType() {
try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = mapper.selectListWithResultType();for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}
}
}

现在我们先关闭typeHandler, 注释掉注册的那一行即可。

allowMultiQueries

多结果集测试
上面在update的时候演示了多条update的动作, 这里用查询来演示多结果集

数据库连接添加属性

allowMultiQueries=true

StudentMapper.xml文件添加sql模块

<resultMap id="studentResultMap" type="student"><id property="id" column="id"/><result property="name" column="name"/><result property="age" column="age"/><result property="gender" column="gender"/><result property="birthday" column="birthday"/><result property="major" column="major"/><result property="phone" column="phone"/><result property="deleted" column="deleted"/><result property="createdAt" column="created_at"/><result property="updatedAt" column="updated_at"/>
</resultMap><select id="selectWithAllowMultiQueries" resultMap="studentResultMap,studentResultMap">select * from student where id = 1;select * from student where id = 2 or id = 3;
</select>

注意这里需要用resultMap指定多个映射关系,每一个映射关系对应一条sql命令; 这里两条sql都指向同一个resultMap也是可以的。

StudentMapper.java添加一个方法

List<List<Student>> selectWithAllowMultiQueries();

注意多结果集返回的是多个list
测试

  @Testvoid allowMultiQueriesTest() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<List<Student>> studentLists = mapper.selectWithAllowMultiQueries();for (List<Student> students : studentLists) {for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}}}}

流式查询

mysql流式查询的关键是设置fetchSize为Integer.MIN_VALUE,也就是-2147483648
写个sql模块StudentMapper.xml

<select id="selectListWithStream" resultType="org.apache.ibatis.mytest.learntest.entity.Student" fetchSize="-2147483648">select * from student</select>

StudentMapper.java类中加一个方法

List<Student> selectListWithStream();

测试

@Testvoid selectListWithStream() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = mapper.selectListWithStream();for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}}}

这里大家测试的时候可以把断点打到DefaultResultSetHandler#getFirstResultSet方法的while循环这里, 查看ResultSet对象的rowData已经是一个ResultsetRowsStreaming对象了

游标查询

首先在StudentMapper.xml文件中添加sql模块

<select id="selectListWithCursor" resultType="org.apache.ibatis.mytest.learntest.entity.Student" fetchSize="2">select * from student
</select>

这里需要知道fetchSize就是游标每次查询的数据条数。

在StudentMapper.java文件中添加方法

Cursor<Student> selectListWithCursor();

测试

@Testvoid selectListWithCursor() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);Cursor<Student> cursor = mapper.selectListWithCursor();for (Student student : cursor) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}// 转DefaultCursor来调用close是因为它处理了异常, 不用手动处理((DefaultCursor<Student>) cursor).close();}}

连表查询

一对一(association)
假设每个学生都选了自己的专业,现在要查询学生选择的专业详情
新增专业表

CREATE TABLE majors (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '专业ID，主键',major_name VARCHAR(100) NOT NULL COMMENT '专业名称',department_id INT COMMENT '所属学院或系的ID（外键）',degree_type VARCHAR(50) COMMENT '学位类型（如本科、硕士、博士）',duration_years INT COMMENT '学制年限（年）',description TEXT COMMENT '专业描述',deleted INT DEFAULT 0 COMMENT '是否有效,0|有效,1|无效',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间'
) COMMENT='学校专业信息表';

插入几条数据

INSERT INTO majors (major_name, department_id, degree_type, duration_years, description, deleted)
VALUES
('软件工程', null, '本科', 4, '一个专注于软件系统开发与维护的专业。', 0),
('英语语言文学', null, '本科', 4, '专注于英语语言学、文学和文化研究的专业。',0),
('财务会计', null, '本科', 4, '为学生准备从事财务会计和管理的职业。',0),
('审计学', null, '本科', 4, '专注于金融机构内审计原则和实践的专业。',0);

新增专业(Majors)类

public class Majors {private Integer id;private String majorName;private Integer departmentId;private String degreeType;private Integer durationYears;private String description;private Integer deleted;private LocalDateTime createdAt;private LocalDateTime updatedAt;
}

Student.java类中也加入一个字段, 用作关联查询

private Majors majors;

StudentMapper.xml文件中添加sql模块

<resultMap id="associationResultMap" type="org.apache.ibatis.mytest.learntest.entity.Student"><id property="id" column="id"/><result property="name" column="name"/><result property="age" column="age"/><result property="gender" column="gender"/><result property="birthday" column="birthday"/><result property="phone" column="phone"/><result property="deleted" column="deleted"/><result property="createdAt" column="created_at"/><result property="updatedAt" column="updated_at"/><association property="majors" javaType="org.apache.ibatis.mytest.learntest.entity.Majors"><id property="id" column="m_id"/><result property="majorName" column="m_major_name"/><result property="departmentId" column="m_department_id"/><result property="degreeType" column="m_degree_type"/><result property="durationYears" column="m_duration_years"/><result property="description" column="m_description"/><result property="deleted" column="m_deleted"/><result property="createdAt" column="m_created_at"/><result property="updatedAt" column="m_updated_at"/></association></resultMap><select id="selectWithAssociation" resultMap="associationResultMap">selects.id,s.name,s.age,s.gender,s.birthday,s.major,s.phone,s.deleted,s.created_at,s.updated_at,m.id as m_id,m.major_name as m_major_name,m.department_id as m_department_id,m.degree_type as m_degree_type,m.duration_years as m_duration_years,m.description as m_description,m.deleted as m_deleted,m.created_at as m_created_at,m.updated_at as m_updated_atfromstudent sleft joinmajors m on s.major = m.id</select>

这里要注意resultMap内部元素的排序, 可以查看dtd文件中的配置

resultMap (constructor?,id\*,result\*,association\*,collection\*, discriminator?)>

association要放在result的后面

一对多
现在各个学生都考试得到了一个分数, 咋们建一个课程成绩表

drop table if exists scores;
CREATE TABLE scores (id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',student_id INT NOT NULL COMMENT '学生ID',subject VARCHAR(255) NOT NULL COMMENT '课程名称',score DECIMAL(5, 2) NOT NULL COMMENT '分数',exam_date DATE COMMENT '考试日期',FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) COMMENT '学生成绩表';

插入一些数据

INSERT INTO scores (student_id, subject, score, exam_date)
VALUES 
(1, '编程基础', 85.50, '2024-06-15'),
(1, '数据结构', 90.00, '2024-06-20'),
(2, '英语', 78.00, '2024-06-15'),
(2, '会计学原理', 88.00, '2024-06-18'),
(3, '篮球', 95.00, '2024-06-25');

创建课程成绩类

public class Scores {private Integer id;private String studentId;private String subject;private BigDecimal score;private LocalDate examDate;// ...省略getter/setter

StudentMapper.xml中添加sql模块

<resultMap id="collectionResultMap" type="student"><id property="id" column="id"/><result property="name" column="name"/><result property="age" column="age"/><result property="gender" column="gender"/><result property="birthday" column="birthday"/><result property="phone" column="phone"/><result property="deleted" column="deleted"/><result property="createdAt" column="created_at"/><result property="updatedAt" column="updated_at"/><collection property="scoresList" javaType="list" ofType="org.apache.ibatis.mytest.learntest.entity.Scores"><id property="id" column="sc_id"/><result property="studentId" column="sc_student"/><result property="subject" column="sc_subject"/><result property="score" column="sc_score"/><result property="examDate" column="sc_exam_date"/></collection></resultMap><select id="selectWithCollection" resultMap="collectionResultMap">selects.id,s.name,s.age,s.gender,s.birthday,s.major,s.phone,s.deleted,s.created_at,s.updated_at,sc.id sc_id,sc.student_id sc_student,sc.subject sc_subject,sc.score sc_score,sc.exam_date sc_exam_datefromstudent sleft joinscores sc on s.id = sc.student_id</select>

StudentMapper.java类中添加方法

List<Student> selectWithCollection();

测试

  @Testvoid selectWithCollection() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> students = mapper.selectWithCollection();for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());List<Scores> scoresList = student.getScoresList();for (Scores scores : scoresList) {System.out.println(scores.getId() + ", " + scores.getStudentId() + ", " + scores.getSubject() + ", " + scores.getScore() + ", " + scores.getExamDate());}}}}

执行函数

创建函数

DROP FUNCTION IF EXISTS test_func;
CREATE FUNCTION test_func (input_id INT)
RETURNS VARCHAR(64)
DETERMINISTIC
READS SQL DATA
BEGINDECLARE result VARCHAR(64);SELECT name FROM student s WHERE s.id = input_id INTO result;RETURN result;
END;
SHOW CREATE FUNCTION test_func;

StudentMapper.xml添加sql模块

<select id="selectNameByFunc" resultType="string">select test_func(#{id,jdbcType=INTEGER,mode=IN})
</select>

StudentMapper.java文件中添加方法

String selectNameByFunc(int id);

测试

  @Testvoid selectNameByFunc() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);String name = mapper.selectNameByFunc(1);System.out.println(name);}}

执行存储过程

创建存储过程

DROP PROCEDURE IF EXISTS get_student_by_id;
DELIMITER //
CREATE PROCEDURE get_student_by_id(IN id INTEGER, OUT name VARCHAR(64))
BEGIN-- 确保返回的结果集是正确的SELECT s.name INTO name FROM student s WHERE s.id = id;-- 返回数据SELECT name, phone  FROM student s WHERE s.id = id;SELECT * FROM student s WHERE s.id = id;
END //
DELIMITER ;

StudentMapper.xml中添加sql片段

<select id="selectProcedure" statementType="CALLABLE" parameterType="student" resultMap="studentResultMap,studentResultMap">{call get_student_by_id(#{id,jdbcType=INTEGER,mode=IN},#{name,jdbcType=VARCHAR,mode=OUT})}
</select>

StudentMapper.java文件中添加方法

List<List<Student>> selectProcedure(Student student);

测试

@Testvoid selectProcedure() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);Student param = new Student();param.setId(1);List<List<Student>> studentLists = mapper.selectProcedure(param);System.out.println("参数值返回name为:" + param.getName());for (List<Student> students : studentLists) {for (Student student : students) {System.out.println(student.getId() + ", " + student.getName() + ", " + student.getAge() + ", " + student.getGender() + ", " + student.getBirthday() + ", " + student.getMajor() + ", " + student.getPhone() + ", " + student.getDeleted() + ", " + student.getCreatedAt() + ", " + student.getUpdatedAt());}}}}

注意:

1. 存储过程的sql片段statementType属性要设置为CALLABLE
2. 入参要设置mode为IN, 出参要设置mode为OUT, 它将查询到的结果赋值到传入的参数上
3. 存储过程可以返回多结果集,多结果集要用多个两个list嵌套接受返回结果, 这里和allowMultiQueries的多结果集是一样的

返回map的查询

StudentMapper.xml写个sql模块

<select id="selectMap" resultType="java.util.Map">select * from student
</select>

StudentMapper.java类添加方法

List<Map<String, Object>> selectMap();

测试

@Testvoid selectMap() {try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Map<String, Object>> dataList = mapper.selectMap();for (Map<String, Object> map : dataList) {System.out.println(map);}}}

四、总结

这篇文章主要介绍了一些mybatis的常用场景, 需要读者注意的是mybatis约束文件dtd中配置的顺序,大家在配置全局属性(mybatis-config.xml)和sql的mapper.xml文件时注意多看几次dtd文件。从这篇文章也能看出mybatis是封装了很多jdbc的常用操作,隐藏了很多细节,然后通过配置暴露给用户自定义,接下将会带大家去详细的了解mybatis的内部结构。