1. Mybatis基本实现

1.1 代码实现

1.1.1 步骤

第一步: 导入依赖

<dependencies>
    <!--mybatis 依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>

    <!--mysql 驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>

    <!--junit 单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>

    <!-- 添加slf4j日志api -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.20</version>
    </dependency>
    <!-- 添加logback-classic依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
    <!-- 添加logback-core依赖 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-core</artifactId>
        <version>1.2.3</version>
    </dependency>

    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
    </dependency>
</dependencies>

第二步: 编写核心配置文件

<?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>
    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://IP:端口/数据库名?characterEncoding=utf-8&useSSL=false"/>
                <property name="username" value="用户名"/>
                <property name="password" value="密码"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--包扫描:mapper包名-->
        <package name="mapper包名"/>
    </mappers>
</configuration>

第三步: 编写接口

public interface UserMapper {

    /*模糊查询*/
    List<User> findByUsername(@Param("username") String username);
}

第四步: 编写sql映射文件 UserMapper.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="link.xiaomo.mybatisdemo.mapper.UserMapper">

    <!--手动映射字段-->
    <resultMap id="brandMap" type="link.xiaomo.demo.pojo.Brand" autoMapping="true">
        <!--id不能省略-->
        <id property="id" column="id" />
        <!--配置名字不同的,其他的通过配置resultMap的autoMapping属性为true,自动生成-->
        <result property="brandName" column="brand_name"/>
        <result property="companyName" column="company_name"/>
    </resultMap>

    <!--字段列表-->
    <sql id="brandColumns">
        id,                          -- id
        brand_name ,        -- 品牌名
        company_name ,    -- 公司名
        ordered,                     -- 排序
        description,                 -- 描述
        status                       -- 状态
    </sql>
    <!--模糊查询-->
    <select id="findByUsername"?resultMap="brandMap">
        select * from tb_user where username like concat('%',#{username},'%')
    </select>
</mapper>

第五步: 回顾核心代码

@Before
public void before() throws IOException {
    factory = new SqlSessionFactoryBuilder()
        .build(Resources.getResourceAsStream("sqlMapConfig.xml"));
}

@After
public void after(){
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void testFindAll() throws IOException {

    sqlSession = factory.openSession();
    //代理dao
    //基于动态代理实现的
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    System.out.println(mapper.findAll()); //=>link.xiaomo.mybatisdemo.mapper.UserMapper.findAll

}

额外:日志配置文件logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <target>System.out</target>
        <encoder>
            <!-- 日志输出格式:%d表示日期时间,%-5level:日志级别  ,%c取类 %thread表示线程名(在哪个方法执行),
                %msg:日志消息,%n是换行符-->
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS}[%-5level] %c [%thread] :%msg%n</pattern>
        </encoder>
    </appender>

    <!-- 滚动记录文件,先将日志记录到指定文件,当符合某个条件时,将日志记录到其他文件
    %logger{36}表示logger是class的全名,36表示限制最长字符
    -->
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <encoder>
            <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36}-%msg%n</pattern>
            <charset>utf-8</charset>
        </encoder>
        <!-- 日志输出路径 -->
        <file>D:/xiaomo/code/logs/mybatis_demo4.log</file>
        <!-- 指定日志文件拆分和压缩规则 -->
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <!-- 通过指定压缩文件的名称,来确定分割文件方式 -->
            <fileNamePattern>D:/xiaomo/code/logs/bak/mybatis_demo4_bak-%d{yyyy-MMdd}.log%i.gz</fileNamePattern>
            <!-- 文件拆分大小 -->
            <maxFileSize>1MB</maxFileSize>
        </rollingPolicy>
    </appender>

    <!-- level:用来设计打印级别,大小写无关:TRACE,DEBUG,INFO,WARN,ERROR,ALL和OFF,默认debug
    <root>可以包含零个或多个<appender-ref>元素,标识这个输出位置将会被本日志级别控制。
 -->
    <root level="DEBUG">
        <!-- 注意:如果这里不配置关联打印位置,该位置将不会记录日志 -->
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="FILE"/>
    </root>
</configuration>

1.1.2 动态代理的回顾

代理dao开发底层用到的是动态代理

前提条件: 我们如果使用jdk的动态代理,必须提供接口!!!

我们的代理对象是动态创建,在运行时才会创建

接口

public interface Person {
    void rent();
}

实现类

public class FangDong implements Person{
    @Override
    public void rent() {
        System.out.println("房东有房,别墅,1000出租!!!");
    }
}

动态代理核心代码

public class Main {
    public static void main(String[] args) {
        // 房东
        FangDong fangDong = new FangDong();

        // 中介代理
        // 动态代理
        // 我们的目的 为了在不修改原有类的基础上对目标方法进行增强
        Person person = (Person) Proxy.newProxyInstance(
                fangDong.getClass().getClassLoader(), //类加载器
                fangDong.getClass().getInterfaces(), // 继承结构
                // 具体增强的逻辑
                new InvocationHandler() {
                    @Override
                    public Object invoke(Object proxy, Method method, 
                                         Object[] args) throws Throwable {
                        // method => 目标要执行的方法
                        // args => 目标方法执行的时候传递的参数
                        Object  result = method.invoke(fangDong,args); // => fangdong.rent();
                        System.out.println("中介额外加了2000块钱准备出租");
                        System.out.println("中介要收半个月的中介费");
                        return result;
                    }
                }
        );

        person.rent();
    }
}

1.2 代码优化

1.2.1 xml包扫描优化

编写 SQL 映射文件,将其在模块的 resources 目录下,并按mapper映射接口的同级创建目录。

【重要!!】在resources上递归创建多级文件夹的时候,文件夹层级之间应该以/分隔。

创建以后,需要修改mybatis核心配置中的包扫描方式,改为扫描文件夹

核心配置文件

<?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>
    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3309/mybatis?characterEncoding=utf-8&useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!--此处改为扫描包方式-->
    <mappers>
        <package name="link.xiaomo.demo.mapper"/>
    </mappers>
</configuration>

1.2.2 解决实体类属性和表字段不匹配

方法一:使用resultMap,手动指定每个字段的映射

<resultMap id="brandMap" type="link.xiaomo.demo.pojo.Brand" autoMapping="true">
    <!--id不能省略-->
    <id property="id" column="id" />
    <!--配置名字不同的,其他的通过配置resultMap的autoMapping属性为true,自动生成-->
    <result property="brandName" column="brand_name"/>
    <result property="companyName" column="company_name"/>
</resultMap>

<!--返回值类型选为resultMap,值为上面定义的resultMap的id-->
<select id="findAll" resultMap="brandMap">
    select
        id,               -- id
        brand_name ,      -- 品牌名
        company_name ,    -- 公司名
        ordered,          -- 排序
        description,      -- 描述
        status            -- 状态
    from tb_brand
</select>

方式二:查询的时候,给查询的字段起别名

<select id="findAll" resultType="link.xiaomo.demo.pojo.Brand">
    select
        id,                          -- id
        brand_name brandName,        -- 品牌名
        company_name companyName,    -- 公司名
        ordered,                     -- 排序
        description,                 -- 描述
        status                       -- 状态
    from tb_brand
</select>

1.2.3 \${}和#{}区别

${}底层使用的是statement执行,相当于把参数直接通过字符串拼接的方式,拼接进Sql,所以存在Sql注入的风险。且在使用的时候,需要在\${}的外侧加上单引号(  )。

#{}底层使用的是preparedStatement执行,相当于先将#{}变成占位符?,之后在传入参数替换,替换的过程会过滤敏感字符,所以避免了sql注入的风险。在执行的时候,程序会自动在参数两侧加单引号。

1.2.4 插入数据时返回主键

在执行insert语句的时候,在xml中的insert标签添加两个属性,分别是

useGeneratedKeys="true"keyProperty="id",分别表示“使用返回主键”和“将返回的id赋值给哪个参数”

<insert id="add" useGeneratedKeys="true" keyProperty="id" >
    insert into tb_brand (brand_name,company_name,ordered,description,status)
    values (#{brandName},#{companyName},#{ordered},#{description},#{status]);
</insert>

1.2.5 @Param命名参数

当Mapper接口使用多个参数的时候,应该对方法的参数使用@Param("参数名")进行修饰。例:

Brand findByCompanyNameAndBrandName(@Param("companyName") String companyName, 
                                        @Param("brandName") String  brandName);

1.2.6 lombok使用

依赖中添加lombok包,通过在实体类上添加注解,可以实现在编译时自动生成代码的功能

注解 功能
@Data 自动生成get/set/toString/equals/hashCode方法
@AllArgsConstructor 自动生成全参构造
@NoArgsConstructor 自动生成无参构造
@Builder 自动生成构造者模式,以实体类AAA为例:
AAA aaa = AAA.builder().属性1(值1).属性2(值2)....属性n(值n).build();
  • IDEA需要安装lombok插件才能正常使用lombok相关功能,高版本IDEA自带此插件

1.3 动态sql

【注意事项!】

  1. 动态sql中, 如果if判断的条件是判断某个字符串是否为空,则需要判断非null且非空字符串。if中的条件的and不能大写

    \<if test="字段 != null and 字段 != '' >\

  2. 不管是if还是sql中,如果需要写大于小于号,使用转义符>(大于),<(小于)

1.3.1 sql标签 + include标签

用于封装SQL中重复内容,一般用于包含表的字段

<!--定义-->
<sql id="brandColumns">
    id,                          -- id
    brand_name brandName,        -- 品牌名
    company_name companyName,    -- 公司名
    ordered,                     -- 排序
    description,                 -- 描述
    status                       -- 状态
</sql>

<!--执行-->
<select id="findAll" resultType="link.xiaomo.demo.pojo.Brand">
    select
        <!-- 【此处引用上面sql标签的内容】-->
        <include refid="brandColumns"></include>
    from tb_brand
</select>

1.3.2 where标签 + if标签

用于根据条件动态加入判断提交

<select id="findByCondition" resultMap="brandMap">
    select
    <include refid="brandColumns" />
    from tb_brand
    <where>
        <if test="status != null">
            and status = #{status}
        </if>
        <if test="companyName != null and companyName != ''">
            and company_name like concat('%',#{companyName},'%')
        </if>
        <if test="brandName != null and brandName != ''">
            and brand_name like concat('%',#{brandName},'%')
        </if>
    </where>
</select>

1.3.3 where + choose + when + otherwise标签

用于条件几选一的情况

<select id="findByConditionSingle" resultMap="brandMap">
    select
    <include refid="brandColumns" />
    from tb_brand
    <where>
        <choose>
            <when test="status != null">
                and status = #{status}
            </when>
            <when test="companyName != null and companyName != ''">
                and company_name like concat('%',#{companyName},'%')
            </when>
            <when test="brandName != null and brandName != ''">
                and brand_name like concat('%',#{brandName},'%')
            </when>
            <otherwise>
                <!--其他情况-->
            </otherwise>
        </choose>
    </where>
</select>

1.3.4 foreach标签

多用于 in (a,b,c) 语句

collection:给集合命名 open:开头拼接什么 close:结尾拼接什么

item:给循环的每个元素命名 separator:元素间用什么分隔

<select id="findByIdList" resultMap="brandMap">
    select
    <include refid="brandColumnsBase"></include>
    from tb_brand
    <foreach collection="list" open="where id in (" close=")" item="each" separator=",">
        #{each}
    </foreach>
</select>

1.3.5 set标签 + if标签

用于更新语句,根据条件动态更新

<update id="updateById">
    update store
    <set>
        <if test='shopOwner != null and shopOwner != ""'>
            shop_owner = #{shopOwner},
        </if>
        <if test='idNumber != null and idNumber != ""'>
            id_number = #{idNumber},
        </if>
        <if test='name != null and name != ""'>
            name = #{name},
        </if>
        <if test='industry != null and industry != ""'>
            industry = #{industry},
        </if>
        <if test='area != null and area != ""'>
            area = #{area},
        </if>
        <if test='phone != null and phone != ""'>
            phone = #{phone},
        </if>
        <if test='status != null'>
            status = #{status},
        </if>
        <if test='auditTime != null'>
            audit_time = #{auditTime}
        </if>
    </set>
    where id = #{id}
</update>

2. 注解开发

@Select

@Insert

@Update

@Delete

@results

2.1 注解开发MyBatis步骤

2.1.1 第一步 配置核心配置文件 sqlMapConfig.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>
    <environments default="default">
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED" >
               <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql:///mybatis?characterEncoding=utf-8&useSSL=false" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
      <package name="link.xiaomo.mybatisanno.mapper"/>
    </mappers>

</configuration>

2.1.2 第二步 编写UserDao

public interface UserMapper {
    @Select("select * from tb_user")
    public List<User> findAll();

    @Select("select * from tb_user where id = #{id}")
    User findById(Integer id);

    @Insert("insert into tb_user values (null,#{username},#{password},#{gender},#{addr})")
    void addUser(User user);

    @Update("update tb_user set password=#{password} where id = #{id}")
    void updateUser(User user);

    @Delete("delete from tb_user where id = #{id}")
    void delById(Integer id);
}

2.1.3 第三步 编写测试代码

public class UserMapperTest {

    private SqlSession sqlSession;
    private UserMapper userMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testFindAll() throws IOException {
        System.out.println(userMapper.findAll());

    }

    @Test
    public void findById() {
        System.out.println(userMapper.findById(1));
    }

    @Test
    public void addUser() {
        User user = User.builder()
                .username("tom")
                .password("admin")
                .gender("男")
                .addr("济南历下区")
                .build();
        userMapper.addUser(user);

    }

    @Test
    public void updateUser() {
        //改用户密码
        User user = User.builder()
                .id(5)
                .password("123")
                .build();
        userMapper.updateUser(user);
    }

    @Test
    public void delById() {
        userMapper.delById(5);
    }
}

2.2 在注解中如何手动映射列名和成员变量

参考resultMap标签

\<resultMap => @Results

\<id / result => @Result

? 主键列 @Result(id = true,property = "id",column = "id")

? 普通列 @Result(property = "brandName",column = "brand_name")

public interface BrandMapper {
    /*
        @Results => <resultMap
        @Result  => <id <result
        如何区分主键和其他列
            看id的值 true => 主键 false => 其他列
     */
    @Results({
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "brand_name",property ="brandName"),
        @Result(column = "company_name",property ="companyName")
    })
    @Select("select * from tb_brand where id = #{id}")
    public Brand findById(Integer id);
}

2.3 在注解开发中如何使用动态sql(自己扩展)

要在带注解的映射器接口类中使用动态 SQL,可以使用 script 元素.

public interface BrandDao {
    @Update({"<script>",
            "UPDATE tb_brand",
            "  <set>",
            "    <if test='brandName != null'>brand_name=#{brandName},</if>",
            "    <if test='companyName != null'>company_name=#{companyName},</if>",
            "    <if test='ordered != null'>ordered=#{ordered},</if>",
            "    <if test='description != null'>description=#{description}</if>",
            "    <if test='status != null'>status=#{status}</if>",
            "  </set>",
            "where id=#{id}",
            "</script>"})
    void updateBrandById(Brand brand);
}

3. MyBatis完成xml多表开发

3.1 一对一

表的映射关系

image-20220524112627945.png

3.1.1 方式一(2条sql方式完成查询)

目标 : 通过学生查询对应的班级信息

-- 根据id查询学生的信息
select * from student where sid = 1
-- 根据学生的cno去查询班级信息
select * from classes where cno = 上面查询的学生的班级id

接口

//StudentMapper
Student findStuAndClassesBySid(Integer sid);
//ClassesMapper
Classes findClassByStudentCno(Integer cno);

映射配置文件

<!--StudentMapper.xml-->
<resultMap id="stu2ClazzMap" type="link.xiaomo.multitable.pojo.Student" autoMapping="true">
    <id property="sid" column="sid"></id>
    <!-- 
        association:当我们需要的结果是一个对象的时候我们使用association标签
            1.property => mybaits执行另外一条查询结果封装到哪个对象中
            2.javaType => 目标对象的数据类型
            3.column   => 相关的列(主键或外键)
            4.select   => 目标的查询
    -->
    <association property="clazz"
                 javaType="link.xiaomo.multitable.pojo.Classes"
                 column="cno" 
                 select="link.xiaomo.multitable.mapper.ClassesMapper.findClassByStudentCno"/>

</resultMap>

<select id="findStuAndClassesBySid" resultMap="stu2ClazzMap">
    select * from student where sid = #{sid}
</select>

<!--ClassesMapper.xml-->
<select id="findClassByStudentCno" resultType="link.xiaomo.multitable.pojo.Classes">
    select * from classes where cid = #{cno}
</select>

resultMap标签: 手动映射我们表的列和java中成员变量的关系,当我们要执行多表查询时,我们就需要通过resultMap标签来手动对多表的结果进行封装

association标签: 当我们明确查询关系是一对一的情况,那么这时候我们就要使用association标签,作用就是将查询的另一方单个的数据封装到咱们成员变量中 把Classes信息封装到Student类中的classes成员变量中

association标签属性 \ 应用在对一的场景里

property => Student类中定义的对象的名称 classes 注意这里的名字写的是成员变量名称

javaType => 我们查询到的结果封装到集合中,集合中需要接受的是什么类型

column => 多表关系之间的关联条件,classes表和student表产生关联的就是cno列

select => 我们要封装的集合数据从哪里来,需要封装的classes对象来自于那条sql

<association property="clazz"
             javaType="link.xiaomo.multitable.pojo.Classes"
             column="cno"
             select="link.xiaomo.multitable.mapper.ClassesMapper.findClassByStudentCno"/>

测试代码

public class StudentMapperTest {
    private SqlSession sqlSession;
    private StudentMapper studentMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void findStuAndClassesBySid() {
        System.out.println(studentMapper.findStuAndClassesBySid(1));
    }
}

3.1.2 方式二(1条sql方式完成查询)

接口

public interface StudentMapper {
    Student findStudentAndClazzBySid(Integer sid);
}

映射配置文件

<?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="link.xiaomo.demo.mapper.StudentMapper">
    <resultMap id="student2ClazzMap" type="link.xiaomo.demo.pojo.Student" autoMapping="true">
        <id column="sid" property="sid" />
        <association property="clazz" javaType="link.xiaomo.demo.pojo.Classes" autoMapping="true">
            <id column="cid" property="cid"/>
        </association>
    </resultMap>

    <select id="findStudentAndClazzBySid" resultMap="student2ClazzMap">
        select *
        from student s
                 inner join classes c on s.cno = c.cid
        where sid = #{sid}
    </select>
</mapper>

测试代码

public class StudentMapperTest {

    private SqlSession sqlSession;
    private StudentMapper studentMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testFindClassAndStu(){
        Student s = studentMapper.findStudentAndClazzBySid(1);
        System.out.println(s);
    }

}

3.2 一对多

image-20220524112627945.png

3.2.1 方式一(2条sql方式完成查询)

目标 : 通过班级查询对应的学生信息

select * from classes where cid = 1;
select * from student where cno = 上面的班级的id;

接口

//ClassesMapper
Classes findClassByCid(Integer cid);
//StudentMapper
List<Student> findStuListByCno(Integer cno);

映射配置文件

<!-- ClassesMapper.xml -->
<resultMap id="classes2StuMap" type="link.xiaomo.multitable.pojo.Classes" autoMapping="true">
    <!--封装cid-->
    <id column="cid" property="cid"></id>
    <result column="cname" property="cname" />
    <!--当我们设计到封装其他表的数据的时候
            如果是对多的情况下 我们会使用 collection 标签来封装
            property : 我们回头查询到的数据封装到谁里面 => stuList
            ofType   : 集合里面放的是什么类型
            column   : 两个表之间的连接关系(主键 / 外键)
            select   : 执行的sql是谁 => namespace + "." + id
        -->
    <collection property="stuList"
                ofType="link.xiaomo.multitable.pojo.Student"
                column="cid"
                select="link.xiaomo.multitable.mapper.StudentMapper.findStuListByCno"
                />
</resultMap>

<!-- StudentMapper.xml -->
<select id="findStuListByCno" resultType="link.xiaomo.multitable.pojo.Student">
    select * from student where cno = #{cno}
</select>
  • resultMap标签: 手动映射我们表的列和java中成员变量的关系,当我们要执行多表查询时,我们就需要通过resultMap标签来手动对多表的结果进行封装

  • collection标签: 当我们明确查询关系是一对多的情况,那么这时候我们就要使用* collection标签,作用就是将查询的另一方多个的数据封装到咱们成员变量中 **把

  • collection标签属性 \ 应用在对多的场景里

property => Classes类中定义的集合的名称 stuList,注意这里的名字写的是成员变量名称

ofType => 我们查询到的结果封装到集合中,集合中需要接受的是什么类型

column => 多表关系之间的关联条件,classes表和student表产生关联的就是cid列

select => 我们要封装的集合数据从哪里来,需要封装的stuList集合来自于那条sql

<collection property="stuList"
            ofType="link.xiaomo.multitable.pojo.Student"
            column="cid"
            select="link.xiaomo.multitable.mapper.StudentMapper.findStuListByCno"
            />

测试代码

public class ClassesMapperTest {
    private SqlSession sqlSession;
    private ClassesMapper classesMapper;
    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        classesMapper = sqlSession.getMapper(ClassesMapper.class);
    }
    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testFindClassesAndStuList2() {
        Classes classes = classesMapper.findClassByCid(1);
        System.out.println(classes);
    }
}

3.2.2 方式二 (1条sql方式完成查询)

目标 : 通过班级查询对应的学生信息

接口

public interface ClassesMapper {
    Classes findClazzAndStuListByCid(@Param("cid")Integer cid);
}

映射配置文件

<?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="link.xiaomo.demo.mapper.ClassesMapper">

    <resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Classes" autoMapping="true">
        <id column="cid" property="cid"/>
        <collection property="stuList" autoMapping="true"
                    ofType="link.xiaomo.demo.pojo.Student"
        >
            <id column="sid" property="sid"></id>
        </collection>
    </resultMap>
    <select id="findClazzAndStuListByCid" resultMap="clazz2StuMap">
        select *
        from classes c
                 inner join student s on c.cid = s.cno
        where c.cid = #{cid}
    </select>
</mapper>

测试代码

public class ClassesMapperTest {

    private SqlSession sqlSession;
    private ClassesMapper classesMapper;
    private StudentMapper studentMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        classesMapper = sqlSession.getMapper(ClassesMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testFindClassAndStu(){
        Classes c = classesMapper.findClazzAndStuListByCid(1);
        System.out.println(c);
    }

}

4.3 多对多

image-20220524151832137.png

方式一(2条sql完成查询)

目标 : 通过课程查询对应的学生信息

select * from course where cid = 1;
SELECT * FROM student WHERE sid IN (SELECT sno FROM stu_cour WHERE cno = 上面查询的学科id)

接口

public interface CourseMapper {
    Course findCourseAndStuListByCid(Integer sid);
}

映射配置文件

<?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="link.xiaomo.demo.mapper.CourseMapper">

    <resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Course" autoMapping="true">
        <id column="cid" property="cid"/>
        <collection property="stuList"
                    ofType="link.xiaomo.demo.pojo.Student"
                    column="cid"
                    select="link.xiaomo.demo.mapper.StudentMapper.findStuListByCourseId">
        </collection>
    </resultMap>
    <select id="findCourseAndStuListByCid" resultMap="clazz2StuMap">
        select *
        from course
        where cid = #{cid}
    </select>
</mapper>

测试代码

public class CourseMapperTest {

    private SqlSession sqlSession;
    private ClassesMapper classesMapper;
    private CourseMapper courseMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        courseMapper = sqlSession.getMapper(CourseMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testFindClassAndStu(){
        Course c = courseMapper.findCourseAndStuListByCid(1);
        System.out.println(c);
    }
}

方式二(2条sql完成查询)

目标 : 通过课程查询对应的学生信息

select * from course t1
              join stu_cour t2 on t1.cid = t2.cno
              join student t3 on t2.sno = t3.sid
              where t1.cid = 1

接口

public interface CourseMapper {
    Course findCourseAndStuListByCid(Integer sid);
}

映射配置文件

<?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="link.xiaomo.demo.mapper.CourseMapper">

    <resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Course" autoMapping="true">
        <id column="cid" property="cid"/>
        <collection property="stuList" autoMapping="true"
                    ofType="link.xiaomo.demo.pojo.Student"
        >
            <id column="sid" property="sid"></id>
        </collection>
    </resultMap>
    <select id="findCourseAndStuListByCid" resultMap="clazz2StuMap">
        select *
        from course c
                 inner join stu_cour sc on sc.cno = c.cid
                 inner join student s on sc.sno = s.sid
        where c.cid = #{cid}
    </select>
</mapper>

测试代码

public class CourseMapperTest {

    private SqlSession sqlSession;
    private ClassesMapper classesMapper;
    private CourseMapper courseMapper;

    @Before
    public void before() throws Exception {
        sqlSession = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsStream("sqlMapConfig.xml"))
                .openSession();
        courseMapper = sqlSession.getMapper(CourseMapper.class);
    }

    @After
    public void after() throws Exception {
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testFindClassAndStu(){
        Course c = courseMapper.findCourseAndStuListByCid(1);
        System.out.println(c);
    }
}
如人饮水,冷暖自知。
最后更新于 2023-08-02