Mybatis多表联合查询,嵌套查询,动态SQL

JavaEE 同时被 2 个专栏收录
7 篇文章 0 订阅
1 篇文章 0 订阅

Mybatis多表联合查询,嵌套查询,动态SQL

Mybatis多表联合查询

一对一

一对一查询:通过一方关联查询出另外一方的关系数据

  1. 创建数据库和相关数据表
  2. 创建需要关联查询的实体类,里面包含相关的属性
//丈夫查妻子
public class Husband {    
    private Integer id;    
    private String name;   
    private Wife wife;
}
public class Wife {

    private Integer id;
    private String name;
}
  1. 编写HusbandMapper接口,为了调用方法
public interface HusbandMapper {

    List<Husband> findByName(String name);
}
  1. 编写相关的Sql语句
    • 关联查询的时候需要用resultMap进行自定义标签的映射
    • 非关联单表查询的时候可以直接使用resultType进行查询
    • property表示属性对应的是实体类的字段名称
    • column表示的是自己定义的属性值,与sql语句定义的字段名称相同
    • associationjavaType在一对一关联查询的时候使用
    • association里映射的是被关联查询的表和属性值
   <resultMap id="husbandAndWife" type="husband">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="wife" javaType="wife">
            <id property="id" column="wid"/>
            <result property="name" column="wname"/>
        </association>
    </resultMap>
    
    <select id="findByName" parameterType="string" resultMap="husbandAndWife">
        select h.id,h.name,w.id wid, w.name wname from t_husband h,t_wife w 
        where h.id = w.id and h.name = #{name}
    </select>
  1. 编写HusbandMapperTest测试类
public class CustomerMapperTest {

    private SqlSessionFactory sqlSessionFactory;

   @Before
   public void init() throws IOException {
       //加载核心配置文件
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config");
       //创建会话工厂
       sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   }

   @Test
   public void test(){
       //打开会话工厂
       SqlSession sqlSession = sqlSessionFactory.openSession();
       //获取内容
       CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
       //遍历Customer表数据
       List<Customer> ret = mapper.findByName("aa");
       //输出
       System.out.println(ret);
   }

一对多

一个用户可以关联查询出自己的订单信息

  1. 创建数据库和相关数据表
  2. 创建需要关联查询的实体类,里面包含相关的属性
public class Customer {

    private Integer id;
    private String name;
    
    private List<Order> orders;
}
public class Order {

    private Integer id;
    private String goods;
    private Integer cid;
}
  1. 编写CustomerMapper接口,为了调用方法
public interface CustomerMapper {

    List<Customer> findByName(String name);
}
  1. 编写相关的Sql语句
    • collectionofType用于对多查询的场合
    • 左外连接,以左边表为基础和交集取值
<resultMap id="customerAndOrders" type="customer">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="orders" ofType="order">
        <id property="id" column="id"/>
        <result property="o.goods" column="o.goods"/>
        <result property="o.c_id" column="o.c_id"/>
        </collection>
    </resultMap>

    <select id="findByName" resultMap="customerAndOrders">
        select c.* ,o.id oid,o.goods,o.c_id from t_customer c 
        left join t_order o on c.id = o.c_id where c.name = #{name}
    </select>
  1. 编写CustomerMapperTest测试类
public class CustomerMapperTest {

    private SqlSessionFactory sqlSessionFactory;

   @Before
   public void init() throws IOException {
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config");
       sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   }

   @Test
   public void test(){
       SqlSession sqlSession = sqlSessionFactory.openSession();
       CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
       List<Customer> ret = mapper.findByName("aa");
       System.out.println(ret);
   }

多对多

一个老师对应多个学生,一个学生也对应多个老师

  1. 创建数据库和相关数据表
  2. 创建需要关联查询的实体类,里面包含相关的属性
public class Teacher {

    private Integer id;
    private String name;

    private List<Student> students;
}
public class Student {

    private Integer id;
    private String name;
}
  1. 编写TeacherMapper接口,为了调用方法
public interface TeacherMapper {

    List<Teacher> findByName(String name);
}
  1. 编写相关的Sql语句
    • 多对多查询需要借助一个t_s中间表来完成关联
  <resultMap id="teacherAndStudent" type="teacher">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" ofType="student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>

    <select id="findByName" resultMap="teacherAndStudent">
        select t.id,t.name,s.id sid,s.name sname from t_teacher t
            left join t_s ts on t.id = ts.t_id
            left join t_student s on ts.s_id = s.id
            where t.name = #{name}
    </select>
  1. 编写TeacherMapperTest测试类
public class TeacherMapperTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void test(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> ret = mapper.findByName("唐丹");
        System.out.println(ret);
    }
}

Mybatis的嵌套查询

嵌套查询是将原来多表查询中的联合查询语句拆成单个表的查询,再使用MyBatis的语法嵌套在一起

嵌套查询使用时,先查询a表的信息,然后依赖a和b表的外键约束,利用in(),再次查询b表对应到a表上的信息。该方式可以改为饿汉式,内存使用较小,但需要多次访问数据库而导致消耗时间多。

一对一嵌套查询

查询一个订单,同时查询出该订单所属的用户

	<select id="findById" resultType="customer">
        select * from t_customer where id = #{id}
    </select>

    <resultMap id="orderAndCustomer2" type="order">
        <id property="id" column="id"/>
        <result property="goods" column="goods"/>
        <result property="cid" column="cid"/>
        <association property="customer"
                     javaType="customer"
                     column="c_id"
                     select="findById"/>
    </resultMap>

    <select id="findByGoods2" resultMap="orderAndCustomer2">
        select * from t_order where goods = #{goods}
    </select>

一对多嵌套查询

查询一个用户,与此同时查询该用户具有的订单

	<resultMap id="baseMap" type="order">
        <id property="id" column="id"/>
        <result property="goods" column="goods"/>
        <result property="cid" column="c_id"/>
    </resultMap>

    <select id="findByCid" resultMap="baseMap">
        select * from t_order where c_id = #{cid}
    </select>

    <resultMap id="customerAndOrders2" type="customer">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="orders"
                    ofType="order"
                    column="id"
                    select="findByCid"/>

    </resultMap>

    <select id="findByName2" resultMap="customerAndOrders2">
        select * from t_customer where name = #{name}
    </select>

动态Sql查询

<insert id="save" parameterType="girl" keyProperty="id" useGeneratedKeys="true">
        insert into girl values (null, #{name},#{age},#{address})
    </insert>

    <sql id="selectAll">
        select * from girl
    </sql>

    <select id="findById" parameterType="int" resultType="girl">
        <include refid="selectAll"/> where id = #{id}
    </select>

    <select id="findAll" resultType="girl">
        <include refid="selectAll"/>
    </select>

Where标签

  • ****标签相当于 where 1=1

  • 如果没有条件,就不会拼接where关键字

  • where标签可以忽略我们成立条件前面的and或者or关键字


    <select id="findByGirl" resultType="girl">
        select * from girl
        <where>
            <if test="name != null and name != ''">
                and name = #{name}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="address != null and address != ''">
                and address = #{address}
            </if>
        </where>
    </select>

    <select id="findByIds" resultType="girl">
        select * from girl
        <where>
            <foreach collection="list" open="id in (" item="id" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

Set标签

  • set标签在更新的时候,自动加上set关键字,然后会去掉最后一个条件的逗号
 <update id="update" parameterType="girl">
        update girl
        <set>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="age != null and age != ''">
                age = #{age},
            </if>
            <if test="address != null and address != ''">
                address = #{address}
            </if>
        </set>
        where id = #{id}
	</update>

Foreach标签

  • 标签用来对数据进行循环遍历
    • collection:代表遍历的集合元素
    • open:代表语句开始的部分
    • close:代表语句结束的部分
    • item:代表遍历集合的每一个元素,生成的变量名
    • sperator:代表分隔符
 <select id="findByIds" resultType="girl">
        select * from girl
        <where>
            <foreach collection="list" open="id in (" item="id" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>
  • 0
    点赞
  • 0
    评论
  • 7
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

Debug_EDM

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值