MyBatis学习总结[3]-多表查询

来源：互联网发布：opera linux 安装编辑：程序博客网时间：2024/06/05 02:41

一对一

方法一：嵌套查询语句

1.现在实体类里添加对应表的实体类

package me.model;import java.util.List;public class User {    private int id;    private String name;    private String age;    //对应表的实体类    private Car car;    .    .    .    getter,setter}

2.在对应的Mapper.xml文件的 resultMap 标签下添加 association 标签

<?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="me.dao.UserDao">  <resultMap type="User" id="User">    <id property="id" column="id"/>    <result property="name" column="name"/>    <result property="age" column="age"/>    <!-- 对应表，select:另一条语句的id，column:根据主表的哪个属性来执行select -->    <association property="car" column="id" select="getCar" />  </resultMap>  <select id="selectById" parameterType="int" resultType="User">    select * from users where id = #{id}  </select>  <!-- 一对一 -->  <!-- 嵌套语句 -->  <select id="selectUser"  resultMap="User">    select * from users   </select>  <select id="getCar" resultMap="Car">    select * from car where u_id=#{id}   </select></mapper>

3.创建相关借口并调用，结果如下，
这里写图片描述

可见分成两条语句来执行。

方法二：嵌套结果

1.实体类

package me.model;public class Car {    private int id;    private String name;    private String money;    private int age;    private int uId;     <!-- 对应实体 -->     private User user;    .    .    .    getter,setter}

2.Mapper

<?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="me.dao.UserDao">  <resultMap type="Car" id="Car">    <id property="id" column="id"/>    <result property="name" column="name"/>    <result property="money" column="money"/>    <result property="age" column="age"/>    <result property="uId" column="u_id"/>    <!-- 嵌套结果 -->    <association property="user" javaType="User">        <id property="id" column="u_id"/>        <result property="name" column="u_name"/>        <result property="age" column="u_age"/>    </association>  </resultMap>  <!-- 当两张表里有相同字段时，要为相同字段取别名，否则同名字段只有一个不为空 -->  <select id="selectCar" resultMap="Car">    select         c.*,        u.id as u_id,        u.name as u_name,        u.age as u_age        from car c,users u where u.id=c.u_id  </select> </mapper>

3.结果
这里写图片描述

一对多

方法一：嵌套查询语句

1.实体类

package me.model;import java.util.List;public class User {    private int id;    private String name;    private String age;    //对应表的实体类    private Car car;    //对应多方的实体    private List<Dog> dogs;    .    .    .    getter,setter}

2.mapper

<?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="me.dao.UserDao">  <resultMap type="User" id="User">    <id property="id" column="id"/>    <result property="name" column="name"/>    <result property="age" column="age"/>    <association property="car" column="id" select="getCar" />    <!-- 与一对一类似 -->    <collection property="dogs" column="id" select="selectDogsWithUid"/>  <resultMap type="Dog" id="Dog">        <id property="id" column="id"/>        <result property="name" column="name"/>        <result property="age" column="age"/>        <result property="sex" column="sex"/>        <result property="uId" column="u_id"/>  </resultMap>  <!-- 一对多 -->  <!-- 嵌套语句 -->  <select id="selectDogs"  resultMap="User">    select * from users   </select>  <select id="selectDogsWithUid"  resultMap="Dog">    select * from dogs where u_id=#{id}   </select></mapper>

3.结果
这里写图片描述

方法二：嵌套结果

Mappeer

<?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="me.dao.UserDao">  <resultMap type="User" id="User">    <id property="id" column="id"/>    <result property="name" column="name"/>    <result property="age" column="age"/>    <association property="car" column="id" select="getCar" />    <!-- 与一对一类似 -->    <collection property="dogs" ofType="Dog">        <id property="id" column="d_id"/>        <result property="name" column="d_name"/>        <result property="age" column="d_age"/>        <result property="sex" column="d_sex"/>        <result property="uId" column="d_u_id"/>    </collection>   </resultMap>  <!-- 嵌套结果 -->  <select id="selectUserWithDog" resultMap="User">        select             u.*,            d.id as d_id,            d.name as d_name,            d.age as d_age,            d.sex as d_sex,            d.u_id as d_u_id            from users u,dogs d where u.id=d.u_id;  </select></mapper>

0 0