一、mybatis相关概念

  1. 对象/关系数据库映射（ORM）
    ORM全称Object/Relation Mapping：表示对象-关系映射的缩写
    ORM完成⾯向对象的编程语⾔到关系数据库的映射。当ORM框架完成映射后，程序员既可以利⽤⾯向
    对象程序设计语⾔的简单易⽤性，⼜可以利⽤关系数据库的技术优势。 ORM把关系数据库包装成⾯向对
    象的模型。 ORM框架是⾯向对象设计语⾔与关系数据库发展不同步时的中间解决⽅案。采⽤ORM框架
    后，应⽤程序不再直接访问底层数据库，⽽是以⾯向对象的⽅式来操作持久化对象，⽽ORM框架则将这
    些⾯向对象的操作转换成底层SQL操作。 ORM框架实现的效果：把对持久化对象的保存、修改、删除
    等操作，转换为对数据库的操作
  2. Mybatis简介
    MyBatis是⼀款优秀的基于ORM的半⾃动轻量级持久层框架，它⽀持定制化SQL、存储过程以及⾼级映
    射。 MyBatis避免了⼏乎所有的JDBC代码和⼿动设置参数以及获取结果集。 MyBatis可以使⽤简单的
    XML或注解来配置和映射原⽣类型、接⼝和Java的POJO （Plain Old Java Objects,普通⽼式Java对 象）
    为数据库中的记录。
  3. Mybatis历史
    原是apache的⼀个开源项⽬iBatis, 2010年6⽉这个项⽬由apache software foundation 迁移到了
    google code，随着开发团队转投Google Code旗下， ibatis3.x正式更名为Mybatis ，代码于2013年11
    ⽉迁移到Github。
    iBATIS⼀词来源于“internet”和“abatis”的组合，是⼀个基于Java的持久层框架。 iBATIS提供的持久层框
    架包括SQL Maps和Data Access Objects(DAO)
  4. Mybatis优势
    Mybatis是⼀个半⾃动化的持久层框架，对开发⼈员开说，核⼼sql还是需要⾃⼰进⾏优化， sql和java编
    码进⾏分离，功能边界清晰，⼀个专注业务，⼀个专注数据。
    分析图示如下：

image.png

二、Mybatis的基本应用

mybatis快速入门

MyBatis官⽹地址： http://www.mybatis.org/mybatis-3/
开发步骤：
①添加MyBatis的坐标
②创建user数据表
③编写User实体类
④编写映射⽂件UserMapper.xml
⑤编写核⼼⽂件SqlMapConfig.xml
⑥编写Dao、Mapper

实际任选一种即可，推荐mapper，这里演示两种都用了

⑦编写测试类

新建项目mybatis_quickStarter

导⼊MyBatis的坐标和其他相关坐标

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  7.     <groupId>com.lpy</groupId>
  8.     <artifactId>mybatis_quickStarter</artifactId>
  9.     <version>1.0-SNAPSHOT</version>
  11.     <properties>
  12.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  13.         <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
  14.         <java.version>1.8</java.version>
  15.         <maven.compiler.source>1.8</maven.compiler.source>
  16.         <maven.compiler.target>1.8</maven.compiler.target>
  17.     </properties>
  19.     <dependencies>
  20.         <dependency>
  21.             <groupId>org.mybatis</groupId>
  22.             <artifactId>mybatis</artifactId>
  23.             <version>3.4.5</version>
  24.         </dependency>
  25.         <dependency>
  26.             <groupId>mysql</groupId>
  27.             <artifactId>mysql-connector-java</artifactId>
  28.             <version>5.1.6</version>
  29.             <scope>runtime</scope>
  30.         </dependency>
  31.         <dependency>
  32.             <groupId>junit</groupId>
  33.             <artifactId>junit</artifactId>
  34.             <version>4.12</version>
  35.         </dependency>
  36.         <dependency>
  37.             <groupId>org.projectlombok</groupId>
  38.             <artifactId>lombok</artifactId>
  39.             <version>1.16.14</version>
  40.         </dependency>
  41.     </dependencies>
  42. </project>

创建user数据表

  1. CREATE TABLE `user` (
  2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3.   `username` varchar(50) DEFAULT NULL,
  4.   `password` varchar(50) DEFAULT NULL,
  5.   `birthday` varchar(50) DEFAULT NULL,
  6.   PRIMARY KEY (`id`) USING BTREE
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

编写User实体 

  1. package com.lpy.model;
  4. import lombok.Getter;
  5. import lombok.Setter;
  6. import lombok.ToString;
  8. /**
  9.  * @author lipengyu
  10.  */
  11. @Getter
  12. @Setter
  13. @ToString
  14. public class User {
  15.     private Integer id;
  17.     private String username;
  19.     private String password;
  21.     private String birthday;
  22. }

编写UserMapper映射⽂件 传统查询namespace为自定义

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  6. <mapper namespace="userMapper">
  8.     <select id="findAll" resultType="user">
  9.         select * from user
  10.     </select>
  12.     <!-- 查询需设置resultType或resultMap 否则会报-->
  13.     <!-- A query was run and no Result Maps were found for the Mapped Statement-->
  14.     <select id="findById" parameterType="int" resultType="user">
  15.         select * from user where id = #{id}
  16.     </select>
  18.     <select id="findOneByCondition" resultType="user" parameterType="user">
  19.         select * from user where username = #{username} and password = #{password}
  20.     </select>
  22.     <!-- insert想返回id值 需要配置useGeneratedKeys 和 keyProperty或keyColumn -->
  23.     <insert id="insertOne" parameterType="user" useGeneratedKeys="true" keyProperty="id">
  24.         insert into user values (#{id}, #{username}, #{password}, #{birthday})
  25.     </insert>
  27.     <update id="updateOne" parameterType="user">
  28.         update user set birthday = #{birthday} where id = #{id}
  29.     </update>
  31.     <delete id="deleteById" parameterType="int">
  32.         delete from user where id = #{id}
  33.     </delete>
  34. </mapper>

编写NewUserMapper映射⽂件 mapper查询，namespace为接口

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  6. <mapper namespace="com.lpy.mapper.IUserMapper">
  8.     <sql id="selectUser">
  9.         select * from user
  10.     </sql> 
  12.     <select id="findAll" resultType="com.lpy.model.User">
  13.         <include refid="selectUser"></include>
  14.     </select>
  16.     <select id="findByCondition" resultType="user" parameterType="user">
  17.         <include refid="selectUser"></include>
  18.         <where>
  19.             <if test="id != null">
  20.                 and id = #{id}
  21.             </if>
  22.             <if test="username != null">
  23.                 and username = #{username}
  24.             </if>
  25.             <if test="birthday != null">
  26.                 and birthday = #{birthday}
  27.             </if>
  28.         </where>
  29.     </select>
  31.     <select id="findByIds" resultType="user" parameterType="list">
  32.         <include refid="selectUser"></include>
  33.         <where>
  34.             <foreach collection="list" open="id in (" close=")" item="id" separator=",">
  35.                 #{id}
  36.             </foreach>
  37.         </where>
  38.     </select>
  39. </mapper>

编写MyBatis核⼼⽂件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  3.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6.     <properties resource="jdbc.properties" />
  8.     <settings>
  9.         <setting name="logImpl" value="STDOUT_LOGGING"/>
  10.     </settings>
  12.     <typeAliases>
  13.         <typeAlias type="com.lpy.model.User" alias="user"/>
  14.     </typeAliases>
  16.     <environments default="dev">
  17.         <environment id="dev">
  18.             <transactionManager type="JDBC"/>
  19.             <dataSource type="POOLED">
  20.                 <property name="driver" value="${jdbc.driver}"/>
  21.                 <property name="url" value="${jdbc.url}"/>
  22.                 <property name="username" value="${jdbc.username}"/>
  23.                 <property name="password" value="${jdbc.password}"/>
  24.             </dataSource>
  25.         </environment>
  26.     </environments>
  28.     <mappers>
  29.         <mapper resource="UserMapper.xml"/>
  30.         <mapper resource="NewUserMapper.xml"/>
  31.     </mappers>
  33. </configuration>

jdbc.properties

  1. jdbc.driver=com.mysql.jdbc.Driver
  2. jdbc.url=jdbc:mysql:///mybatis1
  3. jdbc.username=root
  4. jdbc.password=root

编写Dao、Mapper

  1. package com.lpy.dao;
  3. import com.lpy.model.User;
  5. import java.io.IOException;
  6. import java.util.List;
  8. /**
  9.  * @author lipengyu
  10.  */
  11. public interface IUserDao {
  13.     List<User> findAll() throws IOException;
  14. }

编写实现是为了测试传统使用statementId查询

  1. package com.lpy.dao.impl;
  3. import com.lpy.dao.IUserDao;
  4. import com.lpy.model.User;
  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;
  10. import java.io.IOException;
  11. import java.util.List;
  13. /**
  14.  * @author lipengyu
  15.  */
  16. public class IUserDaoImpl implements IUserDao {
  18.     @Override
  19.     public List<User> findAll() throws IOException {
  20.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapConfig.xml"));
  21.         SqlSession sqlSession = sqlSessionFactory.openSession(true);
  22.         List<User> users = sqlSession.selectList("userMapper.findAll");
  23.         sqlSession.close();
  24.         return users;
  25.     }
  26. }

mapper则使用动态代理

  1. package com.lpy.mapper;
  3. import com.lpy.model.User;
  5. import java.io.IOException;
  6. import java.util.List;
  8. /**
  9.  * @author lipengyu
  10.  */
  11. public interface IUserMapper {
  13.     List<User> findAll();
  15.     List<User> findByCondition(User user);
  17.     List<User> findByIds(List<Integer> ids);
  18. }

写测试类

这里用的是junit4，如果发现@Before不生效，请确认是不是其他版本

  1. import com.lpy.dao.IUserDao;
  2. import com.lpy.dao.impl.IUserDaoImpl;
  3. import com.lpy.mapper.IUserMapper;
  4. import com.lpy.model.User;
  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.Ignore;
  12. import org.junit.Test;
  14. import java.io.IOException;
  15. import java.io.InputStream;
  16. import java.util.Arrays;
  17. import java.util.List;
  19. /**
  20.  * @author lipengyu
  21.  */
  22. public class MybatisTest {
  25.     SqlSession sqlSession = null;
  26.     IUserMapper iUserMapper = null;
  28.     @Before
  29.     public void init() throws IOException {
  30.         // 1. Resources工具类，配置文件的加载，把配置文件加载成字节输入流
  31.         InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
  32.         // 2. 解析了配置文件，并创建sqlSessionFactory工厂
  33.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  34.         // 3. 生产sqlSession 默认开启一个事务，但是该事务不会自动提交，在进行增删改操作时，要收动提交事务
  35. //        sqlSession = sqlSessionFactory.openSession();
  36.         // 当传入true参数 事务自动提交
  37.         sqlSession = sqlSessionFactory.openSession(true);
  38.         // 4.sqlSession 调用方法
  39.         // sqlSession.selectList()...
  41.         // 获取mapper对象
  42.         iUserMapper = sqlSession.getMapper(IUserMapper.class);
  43.     }
  45.     @After
  46.     public void close() {
  47.         sqlSession.close();
  48.     }
  50.     @Ignore
  51.     @Test
  52.     public void test() {
  53.         System.out.println("1");
  54.     }
  56.     @Test
  57.     public void selectList() {
  58.         List<User> users = sqlSession.selectList("userMapper.findAll");
  59.         users.forEach(System.out::println);
  60.     }
  62.     @Test
  63.     public void insert() {
  64.         User user = new User();
  65.         user.setUsername("lpy");
  66.         user.setPassword("123");
  67.         user.setBirthday("2020-12-03");
  68.         sqlSession.insert("userMapper.insertOne", user);
  69.         System.out.println(user);
  70.         List<User> users = sqlSession.selectList("userMapper.findOneByCondition", user);
  71.         users.forEach(System.out::println);
  72.     }
  74.     @Test
  75.     public void update() {
  76.         List<User> users = sqlSession.selectList("userMapper.findAll");
  77.         Integer id = users.get(0).getId();
  78.         User param = new User();
  79.         param.setId(id);
  80.         param.setBirthday("2020-12-02");
  81.         sqlSession.update("userMapper.updateOne", param);
  82.         User user = sqlSession.selectOne("userMapper.findById", id);
  83.         System.out.println(user);
  84.     }
  86.     @Test
  87.     public void delete() {
  88.         List<User> users = sqlSession.selectList("userMapper.findAll");
  89.         Integer id = users.get(users.size() - 1).getId();
  90.         sqlSession.delete("userMapper.deleteById", id);
  91.         User user = sqlSession.selectOne("userMapper.findById", id);
  92.         System.out.println(user);
  93.     }
  95.     @Test
  96.     public void traditional() throws IOException {
  97.         IUserDao iUserDao = new IUserDaoImpl();
  98.         System.out.println(iUserDao.findAll());
  99.     }
  101.     @Test
  102.     public void mapper() {
  103.         System.out.println(iUserMapper.findAll());
  104.     }
  106.     @Test
  107.     public void dynamicCondition() {
  108.         User param = new User();
  109.         param.setId(1);
  110.         System.out.println(iUserMapper.findByCondition(param));
  111.         User param2 = new User();
  112.         param2.setUsername("lucy");
  113.         System.out.println(iUserMapper.findByCondition(param2));
  114.         User param3 = new User();
  115.         param3.setBirthday("2020-12-02");
  116.         System.out.println(iUserMapper.findByCondition(param3));
  117.     }
  119.     @Test
  120.     public void foreach() {
  121.         System.out.println(iUserMapper.findByIds(Arrays.asList(1,2)));
  122.     }
  123. }

mybatis 映射文件概述
image.png

Mybatis相应API介绍
SqlSession⼯⼚构建器SqlSessionFactoryBuilder
常⽤API： SqlSessionFactory build(InputStream inputStream)
通过加载mybatis的核⼼⽂件的输⼊流的形式构建⼀个SqlSessionFactory对象

  1. String resource = "org/mybatis/builder/mybatis-config.xml";
  2. InputStream inputStream = Resources.getResourceAsStream(resource);
  3. SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
  4. SqlSessionFactory factory = builder.build(inputStream);

其中， Resources ⼯具类，这个类在 org.apache.ibatis.io 包中。Resources 类帮助你从类路径下、⽂件系统或⼀个 web URL 中加载资源⽂件。
SqlSession⼯⼚对象SqlSessionFactory
SqlSessionFactory 有多个个⽅法创建SqlSession 实例。常⽤的有如下两个：
image.png
SqlSession会话对象
SqlSession 实例在 MyBatis 中是⾮常强⼤的⼀个类。在这⾥你会看到所有执⾏语句、提交或回滚事务和获取映射器实例的⽅法。
执⾏语句的⽅法主要有： 

  1. <T> T selectOne(String statement, Object parameter)
  2. <E> List<E> selectList(String statement, Object parameter)
  3. int insert(String statement, Object parameter)
  4. int update(String statement, Object parameter)
  5. int delete(String statement, Object parameter)

操作事务的⽅法主要有： 

  1. void commit()
  2. void rollback()

Mybatis的Dao层实现

传统开发方式

可以参照上方代码示例，需要实现DAO层，实际就是直接根据statementId匹配xml中的sql

代理开发方式

采⽤ Mybatis 的代理开发⽅式实现 DAO 层的开发，这种⽅式是目前主流。
Mapper 接⼝开发⽅法只需要程序员编写Mapper 接⼝（相当于Dao 接⼝），由Mybatis 框架根据接⼝
定义创建接⼝的动态代理对象，代理对象的⽅法体同上边Dao接⼝实现类⽅法。
Mapper 接⼝开发需要遵循以下规范：

  1. Mapper.xml⽂件中的namespace与mapper接⼝的全限定名相同
  2. Mapper接⼝⽅法名和Mapper.xml中定义的每个statement的id相同
  3. Mapper接⼝⽅法的输⼊参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
  4. Mapper接⼝⽅法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

mapper对应xml规则
image.png
示例参考上方代码

三、mybaits 配置文件深入

image.png

environments标签

数据库环境的配置，⽀持多环境配置
image.png
其中，事务管理器（transactionManager）类型有两种：

  • JDBC：这个配置就是直接使⽤了JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作
    ⽤域。
  • MANAGED：这个配置⼏乎没做什么。它从来不提交或回滚⼀个连接，⽽是让容器来管理事务的整个⽣
    命周期（⽐如 JEE 应⽤服务器的上下⽂）。 默认情况下它会关闭连接，然⽽⼀些容器并不希望这样，因
    此需要将 closeConnection 属性设置为 false 来阻⽌它默认的关闭⾏为。

其中，数据源（dataSource）类型有三种：

  • UNPOOLED：这个数据源的实现只是每次被请求时打开和关闭连接。
  • POOLED：这种数据源的实现利⽤“池”的概念将 JDBC 连接对象组织起来。
  • JNDI：这个数据源的实现是为了能在如 EJB 或应⽤服务器这类容器中使⽤，容器可以集中或在外部配
    置数据源，然后放置⼀个 JNDI 上下⽂的引⽤。

    mapper标签

    该标签的作⽤是加载映射的，加载⽅式有如下⼏种： ```xml

  1. 使⽤相对于类路径的资源引⽤，例如：

  2. 使⽤完全限定资源定位符（URL），例如：

  3. 使⽤映射器接⼝实现类的完全限定类名，例如：

  4. 将包内的映射器接⼝实现全部注册为映射器，例如： 使用package时需要保证类文件和mapper.xml文件同包同名 ```

    properties标签

    实际开发中，习惯将数据源的配置信息单独抽取成⼀个properties⽂件，该标签可以加载额外配置的properties⽂件
    image.png

    typeAliases标签

    类型别名是为Java 类型设置⼀个短的名字。原来的类型名称配置如下
    image.png
    配置typeAliases，为com.lagou.domain.User定义别名为user
    image.png
    上⾯我们是⾃定义的别名， mybatis框架已经为我们设置好的⼀些常⽤的类型的别名
    image.png

    映射配置⽂件mapper.xml

    动态sql语句概述
    Mybatis 的映射⽂件中，前⾯我们的 SQL 都是⽐较简单的，有些时候业务逻辑复杂时，我们的 SQL是
    动态变化的，此时在前⾯的学习中我们的 SQL 就不能满⾜要求了。
    参考的官⽅⽂档，描述如下：
    image.png

    SQL⽚段抽取

    sql 中可将重复的 sql 提取出来，使⽤时⽤ include 引⽤即可，最终达到 sql 重⽤的⽬的
    ```sql

    select * from user
<a name="KDXbL"></a> #### if 我们根据实体类的不同取值，使⽤不同的 SQL语句来进⾏查询。⽐如在 id如果不为空时可以根据id查询，如果username 不同空时还要加⼊⽤户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。java ```java User param = new User(); param.setId(1); System.out.println(iUserMapper.findByCondition(param)); User param2 = new User(); param2.setUsername("lucy"); System.out.println(iUserMapper.findByCondition(param2)); User param3 = new User(); param3.setBirthday("2020-12-02"); System.out.println(iUserMapper.findByCondition(param3)); 不同参数打印的sql不同 sql select * from user WHERE id = ? select * from user WHERE username = ? select * from user WHERE birthday = ? #### for 循环执⾏sql的拼接操作，例如： select from user WHERE id in ( ? , ? ) sql <select id="findByIds" resultType="user" parameterType="list"> <include refid="selectUser"></include> <where> <foreach collection="list" open="id in (" close=")" item="id" separator=","> #{id} </foreach> </where> </select> sql @Test public void foreach() { System.out.println(iUserMapper.findByIds(Arrays.asList(1,2))); } foreach标签的属性含义如下：
标签⽤于遍历集合，它的属性： - collection：代表要遍历的集合元素，注意编写时不要写#{} - open：代表语句的开始部分 - close：代表结束部分 - item：代表遍历集合的每个元素，⽣成的变量名 - sperator：代表分隔符
# 四、Mybatis复杂映射开发 新建项目mybatis_muiltitable > 这里我直接贴最后的代码了 导入pom.xml文件 xml <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.lpy</groupId> <artifactId>mybatis_muiltitable</artifactId> <version>1.0-SNAPSHOT</version> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.encoding>UTF-8</maven.compiler.encoding> <java.version>1.8</java.version> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> <scope>runtime</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.14</version> </dependency> <dependency> <groupId>org.hibernate.javax.persistence</groupId> <artifactId>hibernate-jpa-2.1-api</artifactId> <version>1.0.2.Final</version> </dependency> <dependency> <groupId>org.mybatis.caches</groupId> <artifactId>mybatis-redis</artifactId> <version>1.0.0-beta2</version> </dependency> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>3.7.5</version> </dependency> <dependency> <groupId>com.github.jsqlparser</groupId> <artifactId>jsqlparser</artifactId> <version>0.9.1</version> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>3.1.2</version> </dependency> </dependencies> </project> 新建sqlMapConfig.xml xml <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="jdbc.properties" /> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> <setting name="cacheEnabled" value="true"/> </settings> <typeAliases> <typeAlias type="com.lpy.model.User" alias="user"/> </typeAliases> <plugins> <!-- <plugin interceptor="com.lpy.plugin.MyPlugin">--> <!-- <property name="name" value="Bob"/>--> <!-- </plugin>--> <plugin interceptor="com.github.pagehelper.PageHelper"> <property name="dialect" value="mysql"/> </plugin> <plugin interceptor="tk.mybatis.mapper.mapperhelper.MapperInterceptor"> <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/> </plugin> </plugins> <environments default="dev"> <environment id="dev"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <package name="com.lpy.mapper"/> </mappers> </configuration> jdbc.properties xml jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql:///mybatis1 jdbc.username=root jdbc.password=root redis.properties xml redis.host=localhost redis.port=6379 redis.connectionTimeout=5000 redis.password= redis.database=0 ## ⼀对⼀查询 ⼀对⼀查询的模型
image.png
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户 ### 创建order表 java CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_time` datetime DEFAULT NULL, `total` decimal(18,2) DEFAULT NULL, `uid` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
⼀对⼀查询的sql语句
对应的sql语句： select from order o,user u where o.uid=u.id;
查询的结果如下：
创建Order和User实体 > 注释的部分后面会用到 java package com.lpy.model; import lombok.Getter; import lombok.Setter; import lombok.ToString; import java.io.Serializable; import java.util.Date; /** * @author lipengyu */ @Getter @Setter @ToString //@ToString(exclude = "user") public class Orders implements Serializable { private Integer id; private Date orderTime; private double total; private String uid; private User user; } java package com.lpy.model; import lombok.Getter; import lombok.Setter; import lombok.ToString; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import java.io.Serializable; /** * @author lipengyu */ @Getter @Setter @ToString @Table(name = "user") public class User implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; private String username; private String password; private String birthday; // @Transient // private List<Orders> orders; // @Transient // private List<Role> roles; } 在resources下的com.lpy.mapper下新建IOrderMapper.xml ### IOrderMapper.xml xml <?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.lpy.mapper.IOrderMapper"> <!--oneToOne1--> <resultMap id="orderMap1" type="com.lpy.model.Order"> <result column="order_time" property="orderTime"/> <result column="uid" property="user.id"/> <result column="username" property="user.username"/> <result column="password" property="user.password"/> <result column="birthday" property="user.birthday"/> </resultMap> <!--oneToOne2--> <resultMap id="orderMap2" type="com.lpy.model.Order"> <result column="id" property="id"/> <result column="order_time" property="orderTime"/> <result column="total" property="total"/> <result column="uid" property="uid"/> <association property="user" javaType="com.lpy.model.User"> <result column="uid" property="id"/> <result column="username" property="username"/> <result column="password" property="password"/> <result column="birthday" property="birthday"/> </association> </resultMap> <select id="findAll" resultMap="orderMap1"> SELECT * FROM orders o left join user u on o.uid = u.id </select> </mapper> 此处注意，因为我们在sqlMapConfig.xml中使用了mappers -> package 的方式，所以需要mapper类和xml文件同包同名。
这里展示了两种一对一查询的方式 java @Test public void oneToOne() { iOrderMapper.findAll().forEach(System.out::println); } java Orders(id=1, orderTime=Sat Oct 23 10:01:46 CST 2021, total=5.0, uid=1, user=User(id=1, username=lucy, password=1, birthday=2020-12-02)) Orders(id=2, orderTime=Mon Oct 25 10:01:46 CST 2021, total=10.0, uid=2, user=User(id=2, username=lip, password=2, birthday=2021-10-21)) Orders(id=3, orderTime=Wed Oct 27 10:01:46 CST 2021, total=15.0, uid=3, user=User(id=3, username=sc, password=3, birthday=2021-10-21)) ## ⼀对多查询 ⼀对多查询的模型同一对一的模型是相同的
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单
修改User和Orders实体 java package com.lpy.model; import lombok.Getter; import lombok.Setter; import lombok.ToString; import javax.persistence.*; import java.io.Serializable; import java.util.List; /** * @author lipengyu */ @Getter @Setter @ToString @Table(name = "user") public class User implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer id; private String username; private String password; private String birthday; @Transient private List<Orders> orders; // @Transient // private List<Role> roles; } java package com.lpy.model; import lombok.Getter; import lombok.Setter; import lombok.ToString; import java.io.Serializable; import java.util.Date; /** * @author lipengyu */ @Getter @Setter //@ToString @ToString(exclude = "user") public class Orders implements Serializable { private Integer id; private Date orderTime; private double total; private String uid; private User user; } 在resources下的com.lpy.mapper下新建IUserMapper.xml ### IUserMapper.xml ```java <?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"> 
  1. <resultMap id="userRoleMap" type="com.lpy.model.User">
  2.     <result column="id" property="id"/>
  3.     <result column="username" property="username"/>
  4.     <result column="password" property="password"/>
  5.     <result column="birthday" property="birthday"/>
  6.     <collection property="roles" ofType="com.lpy.model.Role">
  7.         <result column="rId" property="id"/>
  8.         <result column="roleName" property="roleName"/>
  9.     </collection>
  10. </resultMap>
  12. <select id="findAllWithRole" resultMap="userRoleMap">
  13.     SELECT u.id, u.username, u.password, u.birthday, r.id rId, r.role_name roleName
  14.     FROM user u
  15.              left join user_role ur on ur.user_id = u.id
  16.              left join role r on ur.role_id = r.id
  17. </select>

  1. sql是findAll那一条。<br />与OneToOne不同这里不再是**association**标签 而是**collection**标签
  2. ```java
  3. @Test
  4. public void oneToMany() {
  5.     System.out.println(iUserMapper.findAll());
  6. }

结果：

  1. User(id=1, username=lucy, password=1, birthday=2020-12-02, orders=[Orders(id=1, orderTime=Sat Oct 23 10:01:46 CST 2021, total=5.0, uid=1)])
  2. User(id=2, username=lpy, password=2, birthday=2021-10-21, orders=[Orders(id=2, orderTime=Mon Oct 25 10:01:46 CST 2021, total=10.0, uid=2)])
  3. User(id=3, username=sc, password=3, birthday=2021-10-21, orders=[Orders(id=3, orderTime=Wed Oct 27 10:01:46 CST 2021, total=15.0, uid=3)])

多对多查询

⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
多对多模型
image.png

创建role表

  1. CREATE TABLE `role` (
  2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3.   `role_name` varchar(50) DEFAULT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建user_role表

  1. CREATE TABLE `user_role` (
  2.   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3.   `user_id` int(11) unsigned DEFAULT NULL,
  4.   `role_id` int(11) unsigned DEFAULT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建Role实体

  1. package com.lpy.model;
  3. import lombok.Getter;
  4. import lombok.Setter;
  5. import lombok.ToString;
  7. import java.io.Serializable;
  9. /**
  10.  * @author lipengyu
  11.  */
  12. @Getter
  13. @Setter
  14. @ToString
  15. public class Role implements Serializable {
  16.     private Integer id;
  18.     private String roleName;
  19. }

修改User实体

  1. package com.lpy.model;
  4. import lombok.Getter;
  5. import lombok.Setter;
  6. import lombok.ToString;
  8. import javax.persistence.*;
  9. import java.io.Serializable;
  10. import java.util.List;
  12. /**
  13.  * @author lipengyu
  14.  */
  15. @Getter
  16. @Setter
  17. @ToString
  18. @Table(name = "user")
  19. public class User implements Serializable {
  21.     @Id
  22.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  23.     private Integer id;
  25.     private String username;
  27.     private String password;
  29.     private String birthday;
  31. //    @Transient
  32. //    private List<Orders> orders;
  34.     @Transient
  35.     private List<Role> roles;
  36. }

测试：

  1.     /**
  2.      * <collection property="orders" ofType="com.lpy.model.Role">
  3.      * xml配错偶然发现mybatis可以把A类型的数据设置到B类型的成员变量中
  4.      * 比如把Role设置到Orders中
  5.      */
  6.     @Test
  7.     public void manyToMany() {
  8.         iUserMapper.findAllWithRole().forEach(System.out::println);
  9.     }

结果：

  1. User(id=1, username=lucy, password=1, birthday=2020-12-02, roles=[Role(id=1, roleName=管理员)])
  2. User(id=2, username=lpy, password=2, birthday=2021-10-21, roles=[Role(id=2, roleName=游客)])
  3. User(id=3, username=sc, password=3, birthday=2021-10-21, roles=[Role(id=3, roleName=超级管理员)])

五、Mybatis注解开发

MyBatis的增删改查

常用注解：
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result ⼀起使⽤，封装多个结果集
@One：实现⼀对⼀结果集封装
@Many：实现⼀对多结果集封装
6.2 MyBatis的增删改查
我们完成简单的user表的增删改查的操作

IRoleMapper.xml

  1. @Insert("insert into role values(#{id}, #{roleName})")
  2. void insert(Role role);
  4. @Select("select id, role_name roleName from role where id = #{id}")
  5. List<Role> findById(Integer id);
  7. @Update("update role set role_name = #{roleName} where id = #{id}")
  8. void update(Role role);
  10. @Delete("delete from role where id = #{id}")
  11. void delete(Integer id);
  1. @Test
  2. public void annotationInsert() {
  3.     IRoleMapper iRoleMapper = sqlSession.getMapper(IRoleMapper.class);
  4.     Role role = new Role();
  5.     role.setId(3);
  6.     role.setRoleName("common");
  7.     iRoleMapper.insert(role);
  8. }
  10. @Test
  11. public void annotationSelect() {
  12.     System.out.println(iRoleMapper.findById(3));
  13. }
  15. @Test
  16. public void annotationUpdate() {
  17.     Role role2 = new Role();
  18.     role2.setId(3);
  19.     role2.setRoleName("common2");
  20.     iRoleMapper.update(role2);
  21. }
  23. @Test
  24. public void annotationDelete() {
  25.     iRoleMapper.delete(3);
  26. }

因为这里我们使⽤了注解替代的映射⽂件，所以我们只需要加载使⽤了注解的Mapper接⼝即可 （）

  1. <mappers>
  2.     <mapper class="com.lpy.mapper.IRoleMapper"/>
  3. </mappers>

当然当引入多个接口的时候会比较麻烦，所以还是采用package的方式会更方便

  1. <mappers>
  2.     <package name="com.lpy.mapper"/>
  3. </mappers>

MyBatis的注解实现复杂映射开发

实现复杂关系映射之前我们可以在映射⽂件中通过配置来实现，使⽤注解开发后，我们可以使⽤
@Results注解， @Result注解， @One注解， @Many注解组合完成复杂关系的配置
image.png

一对一查询

在IOrderMapper新增方法

  1.     @Select("select * from orders")
  2.     @Results({
  3.             @Result(id = true, column = "id", property = "id"),
  4.             @Result(column = "order_time", property = "orderTime"),
  5.             @Result(column = "total", property = "total"),
  6.             @Result(column = "uid", property = "uid"),
  7.             @Result(column = "uid", property = "user", javaType = User.class
  8.                     , one = @One(select = "com.lpy.mapper.IUserMapper.findById", fetchType = FetchType.LAZY))
  9.     })
  10.     List<Orders> findAll2();

在IUserMapper中新增方法

  1.     @Select("select * from user where id = #{id}")
  2.     User findById(Integer id);

新增测试方法

  1.     @Test
  2.     public void annotationOneToOne() {
  3.         iOrderMapper.findAll2().forEach(System.out::println);
  4.     }

但是此时会执行n + 1 条sql

  1. select * from orders 
  2. select * from user where id = ? 
  3. select * from user where id = ? 
  4. select * from user where id = ?

返回的数据实际上和上方一对一查询没什么区别

一对多查询

在IUserMapper中新增方法

  1.     @Select("select * from user")
  2.     @Results({
  3.             @Result(column = "id", property = "id"),
  4.             @Result(column = "username", property = "username"),
  5.             @Result(column = "password", property = "password"),
  6.             @Result(column = "birthday", property = "birthday"),
  7.             @Result(column = "id", property = "orders", javaType = List.class
  8.                     , many = @Many(select = "com.lpy.mapper.IOrderMapper.findByUserId", fetchType = FetchType.LAZY))
  9.     })
  10.     List<User> findAll2();

在IOrderMapper新增方法

  1.     @Select("select * from orders where uid = #{uid}")
  2.     List<Orders> findByUserId(String uid);

新增测试方法

  1.     @Test
  2.     public void annotationOneToMany() {
  3.         iUserMapper.findAll2().forEach(System.out::println);
  4.     }

同样也会执行n + 1条sql

  1. select * from user 
  2. select * from orders where uid = ? 
  3. select * from orders where uid = ? 
  4. select * from orders where uid = ?

多对多查询

在IUserMapper中新增方法

  1.     @Select("select * from user")
  2.     @Results({
  3.             @Result(column = "id", property = "id"),
  4.             @Result(column = "username", property = "username"),
  5.             @Result(column = "password", property = "password"),
  6.             @Result(column = "birthday", property = "birthday"),
  7.             @Result(column = "id", property = "roles", javaType = List.class
  8.                     , many = @Many(select = "com.lpy.mapper.IRoleMapper.findAllByUserId", fetchType = FetchType.LAZY))
  9.     })
  10.     List<User> findAllWithRole2();

在IRoleMapper中新增方法

  1.     @Select("SELECT r.id, r.role_name roleName FROM role r left join user_role ur on r.id = ur.role_id where ur.user_id = #{userId}")
  2.     List<Role> findAllByUserId(String userId);

新增测试方法

  1.     @Test
  2.     public void annotationManyToMany() {
  3.         iUserMapper.findAllWithRole2().forEach(System.out::println);
  4.     }

执行sql

  1. select * from user 
  2. SELECT r.id, r.role_name roleName FROM role r left join user_role ur on r.id = ur.role_id where ur.user_id = ?
  3. SELECT r.id, r.role_name roleName FROM role r left join user_role ur on r.id = ur.role_id where ur.user_id = ?
  4. SELECT r.id, r.role_name roleName FROM role r left join user_role ur on r.id = ur.role_id where ur.user_id = ?