第一章：前言

  • 注解开发虽然不如 XML 开发那样，可以完全做到代码和配置文件分离，但是其开发灵活快速的特性，使得在企业开发中的地位日益显著，而 Mybatis 作为时下最流行的持久层框架，掌握 Mybatis 注解开发势在必行。

第二章：环境准备

  • sql 脚本：
  1. -- ----------------------------
  2. -- Table structure for account
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `account`;
  5. CREATE TABLE `account`  (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,
  7.   `user_id` int(11) NULL DEFAULT NULL COMMENT '外键',
  8.   `money` double NULL DEFAULT NULL COMMENT '存款',
  9.   PRIMARY KEY (`id`) USING BTREE,
  10.   INDEX `fk_user_id`(`user_id`) USING BTREE,
  11.   CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  12. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  14. -- ----------------------------
  15. -- Records of account
  16. -- ----------------------------
  17. INSERT INTO `account` VALUES (1, 1, 5000);
  18. INSERT INTO `account` VALUES (2, 2, 2000);
  19. INSERT INTO `account` VALUES (3, 3, 3000);
  21. -- ----------------------------
  22. -- Table structure for course
  23. -- ----------------------------
  24. DROP TABLE IF EXISTS `course`;
  25. CREATE TABLE `course`  (
  26.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  27.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  28.   PRIMARY KEY (`id`) USING BTREE
  29. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  31. -- ----------------------------
  32. -- Records of course
  33. -- ----------------------------
  34. INSERT INTO `course` VALUES (1, '语文');
  35. INSERT INTO `course` VALUES (2, '数学');
  36. INSERT INTO `course` VALUES (3, '英语');
  38. -- ----------------------------
  39. -- Table structure for student
  40. -- ----------------------------
  41. DROP TABLE IF EXISTS `student`;
  42. CREATE TABLE `student`  (
  43.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  44.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  45.   `age` int(11) NULL DEFAULT NULL COMMENT '学生年龄',
  46.   PRIMARY KEY (`id`) USING BTREE
  47. ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  49. -- ----------------------------
  50. -- Records of student
  51. -- ----------------------------
  52. INSERT INTO `student` VALUES (1, '张三', 20);
  53. INSERT INTO `student` VALUES (2, '李四', 25);
  55. -- ----------------------------
  56. -- Table structure for student_course
  57. -- ----------------------------
  58. DROP TABLE IF EXISTS `student_course`;
  59. CREATE TABLE `student_course`  (
  60.   `sid` int(11) NULL DEFAULT NULL COMMENT '外键，指向学生表的主键',
  61.   `cid` int(11) NULL DEFAULT NULL COMMENT '外键，指向课程表的主键',
  62.   INDEX `fk_sid`(`sid`) USING BTREE,
  63.   INDEX `fk_cid`(`cid`) USING BTREE,
  64.   CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  65.   CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `student` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  66. ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  68. -- ----------------------------
  69. -- Records of student_course
  70. -- ----------------------------
  71. INSERT INTO `student_course` VALUES (1, 1);
  72. INSERT INTO `student_course` VALUES (1, 2);
  73. INSERT INTO `student_course` VALUES (1, 3);
  74. INSERT INTO `student_course` VALUES (2, 3);
  76. -- ----------------------------
  77. -- Table structure for user
  78. -- ----------------------------
  79. DROP TABLE IF EXISTS `user`;
  80. CREATE TABLE `user`  (
  81.   `id` int(11) NOT NULL AUTO_INCREMENT,
  82.   `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',
  83.   `birthday` datetime(0) NULL DEFAULT NULL COMMENT '生日',
  84.   `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  85.   `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地址',
  86.   PRIMARY KEY (`id`) USING BTREE
  87. ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  89. -- ----------------------------
  90. -- Records of user
  91. -- ----------------------------
  92. INSERT INTO `user` VALUES (1, '张三', '2021-11-02 10:43:06', '男', '江苏苏州');
  93. INSERT INTO `user` VALUES (2, '李四', '2021-10-26 10:43:24', '女', '江苏南京');
  94. INSERT INTO `user` VALUES (3, '王五', '2021-10-26 10:43:45', '男', '江苏徐州');
  • 导入相关 jar 包的 Maven 坐标：
  1. <dependency>
  2.     <groupId>org.mybatis</groupId>
  3.     <artifactId>mybatis</artifactId>
  4.     <version>3.4.1</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>log4j</groupId>
  8.     <artifactId>log4j</artifactId>
  9.     <version>1.2.17</version>
  10. </dependency>
  11. <dependency>
  12.     <groupId>mysql</groupId>
  13.     <artifactId>mysql-connector-java</artifactId>
  14.     <version>8.0.21</version>
  15. </dependency>
  16. <dependency>
  17.     <groupId>junit</groupId>
  18.     <artifactId>junit</artifactId>
  19.     <version>4.13</version>
  20.     <scope>test</scope>
  21. </dependency>
  • mybatis-config.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.     <environments default="development">
  7.         <environment id="development">
  8.             <transactionManager type="JDBC"/>
  9.             <dataSource type="POOLED">
  10.                 <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
  11.                 <property name="url"
  12.                           value="jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowPublicKeyRetrieval=true"/>
  13.                 <property name="username" value="root"/>
  14.                 <property name="password" value="123456"/>
  15.             </dataSource>
  16.         </environment>
  17.     </environments>
  18.     <mappers>
  19.         <package name="com.github.mapper"/>
  20.     </mappers>
  21. </configuration>
  • log4j.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
  4. <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  6.  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
  7.    <param name="Encoding" value="UTF-8" />
  8.    <layout class="org.apache.log4j.PatternLayout">
  9.     <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
  10.    </layout>
  11.  </appender>
  12.  <logger name="java.sql">
  13.    <level value="debug" />
  14.  </logger>
  15.  <logger name="org.apache.ibatis">
  16.    <level value="info" />
  17.  </logger>
  18.  <root>
  19.    <level value="debug" />
  20.    <appender-ref ref="STDOUT" />
  21.  </root>
  22. </log4j:configuration>

第三章：Mybatis 注解实现常用的 CRUD

  • User.java
  1. package com.sunxiaping.domain;
  3. import java.io.Serializable;
  4. import java.util.Date;
  6. public class User implements Serializable {
  8.     private Integer id;
  10.     private String username;
  12.     private Date birthday;
  14.     private String gender;
  16.     private String address;
  18.     public Integer getId() {
  19.         return id;
  20.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  26.     public String getUsername() {
  27.         return username;
  28.     }
  30.     public void setUsername(String username) {
  31.         this.username = username;
  32.     }
  34.     public Date getBirthday() {
  35.         return birthday;
  36.     }
  38.     public void setBirthday(Date birthday) {
  39.         this.birthday = birthday;
  40.     }
  42.     public String getGender() {
  43.         return gender;
  44.     }
  46.     public void setGender(String gender) {
  47.         this.gender = gender;
  48.     }
  50.     public String getAddress() {
  51.         return address;
  52.     }
  54.     public void setAddress(String address) {
  55.         this.address = address;
  56.     }
  58.     @Override
  59.     public String toString() {
  60.         return "User{" +
  61.                 "id=" + id +
  62.                 ", username='" + username + '\'' +
  63.                 ", birthday=" + birthday +
  64.                 ", gender='" + gender + '\'' +
  65.                 ", address='" + address + '\'' +
  66.                 '}';
  67.     }
  68. }
  • UserMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.User;
  4. import org.apache.ibatis.annotations.*;
  6. import java.util.List;
  8. public interface UserMapper {
  10.     /**
  11.      * 查询所有用户信息
  12.      *
  13.      * @return
  14.      */
  15.     @Select(" SELECT * FROM `user`")
  16.     @Results({
  17.             @Result(id = true, property = "id", column = "id"),
  18.             @Result(property = "username", column = "username"),
  19.             @Result(property = "birthday", column = "birthday"),
  20.             @Result(property = "gender", column = "gender"),
  21.             @Result(property = "address", column = "address"),
  22.     })
  23.     List<User> findAll();
  25.     /**
  26.      * 添加用户信息
  27.      *
  28.      * @param user
  29.      */
  30.     @Insert(" INSERT INTO `user` (username,birthday,gender,address) VALUES (#{username},#{birthday},#{gender},#{address})")
  31.     void saveUser(User user);
  33.     /**
  34.      * 修改用户信息
  35.      *
  36.      * @param user
  37.      */
  38.     @Update(" UPDATE `user` set username = #{username},gender= #{gender} ,address = #{address} WHERE id = #{id} ")
  39.     void updateUser(User user);
  41.     /**
  42.      * 删除用户信息
  43.      *
  44.      * @param id
  45.      */
  46.     @Delete(" DELETE FROM `user` WHERE id = #{id} ")
  47.     void deleteById(Integer id);
  49. }
  • 测试：
  1. package com.sunxiaping;
  3. import com.sunxiaping.domain.User;
  4. import com.sunxiaping.mapper.UserMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.Date;
  16. import java.util.List;
  18. public class UserTest {
  19.     SqlSessionFactory sqlSessionFactory = null;
  20.     SqlSession sqlSession = null;
  22.     @Before
  23.     public void before() throws IOException {
  24.         String resource = "mybatis-config.xml";
  25.         InputStream inputStream = Resources.getResourceAsStream(resource);
  26.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  28.     }
  30.     @After
  31.     public void after() {
  32.         if (sqlSession != null) {
  33.             sqlSession.close();
  34.         }
  35.     }
  38.     @Test
  39.     public void testFindAll() throws IOException {
  40.         sqlSession = sqlSessionFactory.openSession(true);
  42.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  44.         List<User> userList = userMapper.findAll();
  46.         System.out.println("userList = " + userList);
  48.     }
  50.     @Test
  51.     public void testSaveUser() {
  52.         sqlSession = sqlSessionFactory.openSession(true);
  54.         User user = new User();
  55.         user.setUsername("zhangsan");
  56.         user.setBirthday(new Date());
  57.         user.setGender("男");
  58.         user.setAddress("江苏苏州");
  60.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  62.         userMapper.saveUser(user);
  63.     }
  66.     @Test
  67.     public void testUpdateUser() {
  68.         sqlSession = sqlSessionFactory.openSession(true);
  70.         User user = new User();
  71.         user.setId(1);
  72.         user.setUsername("张三");
  73.         user.setGender("女");
  74.         user.setAddress("江苏苏州");
  75.         user.setBirthday(new Date());
  77.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  79.         userMapper.updateUser(user);
  81.     }
  83.     @Test
  84.     public void testDeleteById() {
  85.         sqlSession = sqlSessionFactory.openSession(true);
  87.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  88.         userMapper.deleteById(1);
  89.     }
  91. }

第四章：Mybatis 注解实现一对一关联查询

  • 一对一的模型：

一对一的模型.png

  • 查询账号的同时查询用户的信息对应的 SQL ：
  1. SELECT * FROM `account`
  1. SELECT * FROM `user` WHERE id = #{id}
  • 示例：
  • User.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  4. import java.util.Date;
  6. /**
  7.  * @author 许大仙
  8.  * @version 1.0
  9.  * @since 2021-11-02 10:42
  10.  */
  11. public class User implements Serializable {
  12.     private Integer id;
  14.     private String username;
  16.     private Date birthday;
  18.     private String gender;
  20.     private String address;
  22.     public Integer getId() {
  23.         return id;
  24.     }
  26.     public void setId(Integer id) {
  27.         this.id = id;
  28.     }
  30.     public String getUsername() {
  31.         return username;
  32.     }
  34.     public void setUsername(String username) {
  35.         this.username = username;
  36.     }
  38.     public Date getBirthday() {
  39.         return birthday;
  40.     }
  42.     public void setBirthday(Date birthday) {
  43.         this.birthday = birthday;
  44.     }
  46.     public String getGender() {
  47.         return gender;
  48.     }
  50.     public void setGender(String gender) {
  51.         this.gender = gender;
  52.     }
  54.     public String getAddress() {
  55.         return address;
  56.     }
  58.     public void setAddress(String address) {
  59.         this.address = address;
  60.     }
  62.     @Override
  63.     public String toString() {
  64.         return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", gender='"
  65.             + gender + '\'' + ", address='" + address + '\'' + '}';
  66.     }
  67. }
  • Account.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  5. /**
  6.  * @author 许大仙
  7.  * @version 1.0
  8.  * @since 2021-11-02 10:44
  9.  */
  10. public class Account implements Serializable {
  11.     private Integer id;
  13.     private Double money;
  15.     /**
  16.      * 一个账户属于一个用户
  17.      */
  18.     private User user;
  20.     public Integer getId() {
  21.         return id;
  22.     }
  24.     public void setId(Integer id) {
  25.         this.id = id;
  26.     }
  28.     public Double getMoney() {
  29.         return money;
  30.     }
  32.     public void setMoney(Double money) {
  33.         this.money = money;
  34.     }
  36.     public User getUser() {
  37.         return user;
  38.     }
  40.     public void setUser(User user) {
  41.         this.user = user;
  42.     }
  44.     @Override
  45.     public String toString() {
  46.         return "Account{" + "id=" + id + ", money=" + money + ", user=" + user + '}';
  47.     }
  48. }
  • UserMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.User;
  4. import org.apache.ibatis.annotations.Mapper;
  5. import org.apache.ibatis.annotations.Result;
  6. import org.apache.ibatis.annotations.Results;
  7. import org.apache.ibatis.annotations.Select;
  9. /**
  10.  * @author 许大仙
  11.  * @version 1.0
  12.  * @since 2021-11-02 10:45
  13.  */
  14. @Mapper
  15. public interface UserMapper {
  17.     @Select("SELECT * FROM `user` WHERE id = #{id}")
  18.     @Results({
  19.             @Result(id = true, property = "id", column = "id"),
  20.             @Result(property = "username", column = "username"),
  21.             @Result(property = "birthday", column = "birthday"),
  22.             @Result(property = "gender", column = "gender"),
  23.             @Result(property = "address", column = "address"),
  24.     })
  25.     User findById(Integer id);
  26. }
  • AccountMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.Account;
  4. import com.github.entity.User;
  5. import org.apache.ibatis.annotations.*;
  7. import java.util.List;
  9. /**
  10.  * @author 许大仙
  11.  * @version 1.0
  12.  * @since 2021-11-02 10:45
  13.  */
  14. @Mapper
  15. public interface AccountMapper {
  17.     @Select("SELECT * FROM `account`")
  18.     @Results({
  19.             @Result(id = true, property = "id", column = "id"),
  20.             @Result(property = "money", column = "money"),
  21.             @Result(
  22.                     property = "user",
  23.                     javaType = User.class,
  24.                     column = "user_id",
  25.                     one = @One(select = "com.github.mapper.UserMapper.findById")
  26.             )
  27.     })
  28.     List<Account> findAll();
  29. }
  • 测试：
  1. package com.github.mybatis;
  3. import com.github.entity.Account;
  4. import com.github.mapper.AccountMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.List;
  17. /**
  18.  * @author 许大仙
  19.  * @version 1.0
  20.  * @since 2021-10-29 14:19
  21.  */
  22. public class MybatisTest {
  24.     SqlSession sqlSession;
  26.     @Before
  27.     public void before() throws IOException {
  28.         // 使用Mybatis的Resources读取mybatis-config.xml配置文件
  29.         InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
  30.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  31.         sqlSession = sqlSessionFactory.openSession();
  32.     }
  34.     @Test
  35.     public void test() throws IOException {
  36.         AccountMapper accountMapper = sqlSession.getMapper(AccountMapper.class);
  37.         List<Account> accountList = accountMapper.findAll();
  38.         accountList.forEach(System.out::println);
  39.     }
  41.     @After
  42.     public void after() {
  43.         if (null != sqlSession) {
  44.             sqlSession.close();
  45.         }
  46.     }
  48. }

第五章：Mybatis 注解实现一对多关联查询

  • 一对多的模型：

一对多的模型.png

  • 查询用户的同时查询账号的信息对应的 SQL ：
  1. SELECT * FROM `user`
  1. SELECT * FROM `account` where user_id = #{user_id}
  • 示例：
  • Account.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  5. /**
  6.  * @author 许大仙
  7.  * @version 1.0
  8.  * @since 2021-11-02 10:44
  9.  */
  10. public class Account implements Serializable {
  11.     private Integer id;
  13.     private Double money;
  15.     public Account(Integer id, Double money) {
  16.         this.id = id;
  17.         this.money = money;
  18.     }
  20.     public Integer getId() {
  21.         return id;
  22.     }
  24.     public void setId(Integer id) {
  25.         this.id = id;
  26.     }
  28.     public Double getMoney() {
  29.         return money;
  30.     }
  32.     public void setMoney(Double money) {
  33.         this.money = money;
  34.     }
  36.     @Override
  37.     public String toString() {
  38.         return "Account{" + "id=" + id + ", money=" + money + '}';
  39.     }
  40. }
  • User.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  4. import java.util.Date;
  5. import java.util.List;
  7. /**
  8.  * @author 许大仙
  9.  * @version 1.0
  10.  * @since 2021-11-02 10:42
  11.  */
  12. public class User implements Serializable {
  13.     private Integer id;
  15.     private String username;
  17.     private Date birthday;
  19.     private String gender;
  21.     private String address;
  23.     // 一个用户有多个账号
  24.     private List<Account> accountList;
  26.     public Integer getId() {
  27.         return id;
  28.     }
  30.     public void setId(Integer id) {
  31.         this.id = id;
  32.     }
  34.     public String getUsername() {
  35.         return username;
  36.     }
  38.     public void setUsername(String username) {
  39.         this.username = username;
  40.     }
  42.     public Date getBirthday() {
  43.         return birthday;
  44.     }
  46.     public void setBirthday(Date birthday) {
  47.         this.birthday = birthday;
  48.     }
  50.     public String getGender() {
  51.         return gender;
  52.     }
  54.     public void setGender(String gender) {
  55.         this.gender = gender;
  56.     }
  58.     public String getAddress() {
  59.         return address;
  60.     }
  62.     public void setAddress(String address) {
  63.         this.address = address;
  64.     }
  66.     public List<Account> getAccountList() {
  67.         return accountList;
  68.     }
  70.     public void setAccountList(List<Account> accountList) {
  71.         this.accountList = accountList;
  72.     }
  74.     @Override
  75.     public String toString() {
  76.         return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", gender='"
  77.             + gender + '\'' + ", address='" + address + '\'' + ", accountList=" + accountList + '}';
  78.     }
  79. }
  • AccountMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.Account;
  4. import org.apache.ibatis.annotations.Mapper;
  5. import org.apache.ibatis.annotations.Result;
  6. import org.apache.ibatis.annotations.Results;
  7. import org.apache.ibatis.annotations.Select;
  9. /**
  10.  * @author 许大仙
  11.  * @version 1.0
  12.  * @since 2021-11-02 10:45
  13.  */
  14. @Mapper
  15. public interface AccountMapper {
  17.     @Select("SELECT * FROM `account` where user_id = #{userId}")
  18.     @Results({
  19.             @Result(id = true, property = "id", column = "id"),
  20.             @Result(property = "money", column = "money")}
  21.     )
  22.     List<Account> findByUserId(Integer userId);
  23. }
  • UserMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.User;
  4. import org.apache.ibatis.annotations.*;
  6. import java.util.List;
  8. /**
  9.  * @author 许大仙
  10.  * @version 1.0
  11.  * @since 2021-11-02 10:45
  12.  */
  13. @Mapper
  14. public interface UserMapper {
  16.     @Select(" SELECT * FROM `user`")
  17.     @Results({
  18.             @Result(id = true, property = "id", column = "id"),
  19.             @Result(property = "username", column = "username"),
  20.             @Result(property = "birthday", column = "birthday"),
  21.             @Result(property = "gender", column = "gender"),
  22.             @Result(property = "address", column = "address"),
  23.             @Result(
  24.                     property = "accountList", // 被包含对象的变量名
  25.                     column = "id", // 根据查询出来的user表中的id去根据user_id查询
  26.                     many = @Many(select = "com.github.mapper.AccountMapper.findByUserId")),
  27.     })
  28.     List<User> findAll();
  30. }
  • 测试：
  1. package com.github.mybatis;
  3. import com.github.entity.User;
  4. import com.github.mapper.UserMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.List;
  17. /**
  18.  * @author 许大仙
  19.  * @version 1.0
  20.  * @since 2021-10-29 14:19
  21.  */
  22. public class MybatisTest {
  24.     SqlSession sqlSession;
  26.     @Before
  27.     public void before() throws IOException {
  28.         // 使用Mybatis的Resources读取mybatis-config.xml配置文件
  29.         InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
  30.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  31.         sqlSession = sqlSessionFactory.openSession();
  32.     }
  34.     @Test
  35.     public void test() throws IOException {
  36.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  37.         List<User> userList = userMapper.findAll();
  38.         userList.forEach(System.out::println);
  39.     }
  41.     @After
  42.     public void after() {
  43.         if (null != sqlSession) {
  44.             sqlSession.close();
  45.         }
  46.     }
  48. }

第六章：Mybatis 注解实现多对多关联查询

  • 多对多模型：

多对多模型.png

  • 查询学生的同时查询课程的信息对应的 SQL ：
  1. SELECT * FROM `student`
  1. SELECT c.* FROM `student_course` sc INNER JOIN `course` c ON sc.cid = c.id WHERE sc.sid = #{id}
  • 示例：
  • Student.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  4. import java.util.List;
  6. /**
  7.  * @author 许大仙
  8.  * @version 1.0
  9.  * @since 2021-11-02 11:06
  10.  */
  11. public class Student implements Serializable {
  13.     private Integer id;
  15.     private String name;
  17.     private Integer age;
  19.     private List<Course> courseList;
  21.     public Student() {}
  23.     public Student(Integer id, String name, Integer age, List<Course> courseList) {
  24.         this.id = id;
  25.         this.name = name;
  26.         this.age = age;
  27.         this.courseList = courseList;
  28.     }
  30.     public Integer getId() {
  31.         return id;
  32.     }
  34.     public void setId(Integer id) {
  35.         this.id = id;
  36.     }
  38.     public String getName() {
  39.         return name;
  40.     }
  42.     public void setName(String name) {
  43.         this.name = name;
  44.     }
  46.     public Integer getAge() {
  47.         return age;
  48.     }
  50.     public void setAge(Integer age) {
  51.         this.age = age;
  52.     }
  54.     @Override
  55.     public String toString() {
  56.         return "Student{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", courseList=" + courseList + '}';
  57.     }
  58. }
  • Course.java
  1. package com.github.entity;
  3. import java.io.Serializable;
  5. /**
  6.  * @author 许大仙
  7.  * @version 1.0
  8.  * @since 2021-11-02 11:07
  9.  */
  10. public class Course implements Serializable {
  12.     private Integer id;
  14.     private String name;
  16.     public Course() {}
  18.     public Integer getId() {
  19.         return id;
  20.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  26.     public String getName() {
  27.         return name;
  28.     }
  30.     public void setName(String name) {
  31.         this.name = name;
  32.     }
  34.     @Override
  35.     public String toString() {
  36.         return "Course{" + "id=" + id + ", name='" + name + '\'' + '}';
  37.     }
  38. }
  • CourseMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.Course;
  4. import org.apache.ibatis.annotations.Mapper;
  5. import org.apache.ibatis.annotations.Select;
  7. import java.util.List;
  9. /**
  10.  * @author 许大仙
  11.  * @version 1.0
  12.  * @since 2021-11-02 11:09
  13.  */
  14. @Mapper
  15. public interface CourseMapper {
  17.     @Select("SELECT c.* FROM `student_course` sc INNER JOIN `course` c ON sc.cid = c.id WHERE sc.sid = #{id}")
  18.     List<Course> findBySid(Integer id);
  19. }
  • StudentMapper.java
  1. package com.github.mapper;
  3. import com.github.entity.Student;
  4. import org.apache.ibatis.annotations.*;
  6. import java.util.List;
  8. /**
  9.  * @author 许大仙
  10.  * @version 1.0
  11.  * @since 2021-11-02 11:09
  12.  */
  13. @Mapper
  14. public interface StudentMapper {
  16.     @Select("SELECT * FROM `student`")
  17.     @Results({
  18.             @Result(id = true, property = "id", column = "id"),
  19.             @Result(property = "name", column = "name"),
  20.             @Result(property = "age", column = "age"),
  21.             @Result(
  22.                     property = "courseList", //被包含对象的变量名
  23.                     column = "id", //根据查询出来的student表中的id去中间表和course表中查询
  24.                     many = @Many(select = "com.github.mapper.CourseMapper.findBySid")
  25.             )
  26.     })
  27.     List<Student> findAll();
  29. }
  • 测试：
  1. package com.github.mybatis;
  3. import com.github.entity.Student;
  4. import com.github.mapper.StudentMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.List;
  17. /**
  18.  * @author 许大仙
  19.  * @version 1.0
  20.  * @since 2021-10-29 14:19
  21.  */
  22. public class MybatisTest {
  24.     SqlSession sqlSession;
  26.     @Before
  27.     public void before() throws IOException {
  28.         // 使用Mybatis的Resources读取mybatis-config.xml配置文件
  29.         InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
  30.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  31.         sqlSession = sqlSessionFactory.openSession();
  32.     }
  34.     @Test
  35.     public void test() throws IOException {
  36.         StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
  37.         List<Student> studentList = studentMapper.findAll();
  38.         studentList.forEach(System.out::println);
  39.     }
  41.     @After
  42.     public void after() {
  43.         if (null != sqlSession) {
  44.             sqlSession.close();
  45.         }
  46.     }
  47. }