MyBatis基础知识笔记【下】

最新推荐文章于 2022-12-24 14:05:31 发布

这个饕字怎么读

最新推荐文章于 2022-12-24 14:05:31 发布

阅读量312

点赞数

分类专栏： Java 笔记文章标签： mybatis java eclipse mysql

本文链接：https://blog.csdn.net/weixin_44427114/article/details/109234297

版权

笔记同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

Java

16 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在MyBatis中实现一对多和多对多的关联查询，包括左外连接实现的一对多查询、分步查询以及多对多查询的关联和分步方法。同时，还讲解了动态SQL的使用，如`<if>`、`<choose>`、`<when>`、`<otherwise>`、`<where>`、`<trim>`、`<set>`和`<foreach>`元素，展示了在不同场景下动态构建SQL语句的方法。

摘要由CSDN通过智能技术生成

本篇博客内容：

MyBatis 实现 “一对多” 查询
MyBatis 实现 “多对多” 查询
MyBatis 实现动态SQL

8 一对多查询

“一对多”对于“多对一”的那方其实操作和一对一完全一致，参照上两节，这里只举例一对多

8.1 一对多关联查询

8.1.1 类的设计

public class User {
	private Integer userId;
	private String name;
	private Integer gender;
	private IdCard idCard; // 这个例子中不会用到该属性
	private List<Order> orders;

public class Order {
	private Integer orderId;
	private Integer price;
	private User user;

8.1.2 接口方法和xml配置

public User getUserWithOrdersById(Integer userId);

<!-- public User getUserWithOrdersById(Integer userId); -->
<select id="getUserWithOrdersById" resultMap="getUserWithOrders">
	select user_id,name,gender,order_id,price
	from t_user u left join t_order o
	on u.user_id = o.uid
	where u.user_id = #{userId}
</select>
<resultMap type="cyt.mybatis.bean.User" id="getUserWithOrders">
	<id column="user_id" property="userId"/>
	<result column="name" property="name"/>
	<result column="gender" property="gender"/>
	<collection property="orders" ofType="cyt.mybatis.bean.Order">
		<id column="order_id" property="orderId"/>
		<result column="price" property="price"/>
	</collection>
</resultMap>

collection 和 association 不同的是，类名用 ofType 而不是 javaType
这里依然使用左外连接

8.1.3 测试

@Test
void testUser() {
	try {
		UserMapper dao = session.getMapper(UserMapper.class);
		User users = dao.getUserWithOrdersById(4);
		System.out.println(users);
	} finally {
		session.close();
	}
}

8.2 一对多分步查询

8.2.1 OrderMapper设置

接口设置

public List<Order> searchOrdersByUid(Integer userId);

xml设置

<!-- public List<Order> searchOrdersByUid(Integer userId); -->
<select id="searchOrdersByUid" resultType="cyt.mybatis.bean.Order">
	select * from t_order where uid = #{id};
</select>

测试省略，这一步是为了分部查询中求出一个user，然后用一个uid再去查询order的基础步骤，故在order中设置

8.2.2 UserMapper设置

接口设置

public User getUserWithOrdersByIdStep(Integer userId);

xml设置

<!-- public User getUserWithOrdersByIdStep(Integer userId); -->
<select id="getUserWithOrdersByIdStep" resultMap="getUserWithOrdersStep">
	select * from t_user where user_id = #{id}
</select>
<resultMap type="cyt.mybatis.bean.User" id="getUserWithOrdersStep">
	<id column="user_id" property="userId"/>
	<result column="name" property="name"/>
	<result column="gender" property="gender"/>
	<collection property="orders" select="cyt.mybatis.dao.OrderMapper.searchOrdersByUid"
	column="user_id"></collection>
</resultMap>

测试

@Test
void testUser() {
	try {
		UserMapper dao = session.getMapper(UserMapper.class);
		User users = dao.getUserWithOrdersByIdStep(4);
		System.out.println(users.getName());
		System.out.println("=====================");
		System.out.println(users.getOrders());
	} finally {
		session.close();
	}
}

结果：

DEBUG 10-22 13:41:25,034 ==>  Preparing: select * from t_user where user_id = ?  (BaseJdbcLogger.java:137) 
DEBUG 10-22 13:41:25,072 ==> Parameters: 4(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-22 13:41:25,117 <==      Total: 1  (BaseJdbcLogger.java:137) 
Skywang
=====================
DEBUG 10-22 13:41:25,119 ==>  Preparing: select * from t_order where uid = ?;  (BaseJdbcLogger.java:137) 
DEBUG 10-22 13:41:25,120 ==> Parameters: 4(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-22 13:41:25,122 <==      Total: 4  (BaseJdbcLogger.java:137) 
[Order [orderId=6, price=156, user=null], Order [orderId=7, price=200, user=null], Order [orderId=8, price=234, user=null], Order [orderId=9, price=23, user=null]]

可以看出分步查询在使用到的时候才会去执行下一步的 sql 语句

8.2.3 查询多个user的情况

例如查询 user 时 id 限制是大于或小于约束。如：

select user_id,name,gender,order_id,price
from t_user u left join t_order o
on u.user_id = o.uid
where u.user_id <= 2;

封装的结果集完全不变，只需要加上对应接口和设置xml中的sql语句即可

public List<User> searchUsersWithOrdersBThanId(Integer userId);

<!-- public List<User> searchUsersWithOrdersBThanId(Integer userId); -->
<select id="searchUsersWithOrdersBThanId" resultMap="getUserWithOrders">
	select user_id,name,gender,order_id,price
	from t_user u left join t_order o
	on u.user_id = o.uid
	where u.user_id >= #{userId}
</select>
<!-- 这里用到 resultMap 和上方用过的 resultMap 一致，故不需要额外设置 -->

这里 resultMap=“getUserWithOrders” 是 8.1 节设置的结果集封装

9 多对多查询

9.1 多对多关联查询

9.1.1 创建SellEntry, Product类

public class Product {
	private Integer productId;
	private String name;
	private String brand;

public class SellEntry {
	private Integer amount;
	private Product product;
	private Order order;

Order中添加List

public class Order {
	private Integer orderId;
	private Integer price;
	private User user;  // 此例子中无应用
	private List<SellEntry> sellEntries;

9.1.2 接口和xml配置

public Order getOrderWithSellsById(Integer orderId);

<!-- public Order getOrderWithSellsById(Integer orderId); -->
<select id="getOrderWithSellsById" resultMap="getOrderWithSells">
	select order_id, price, product_id, name, brand, amount
	from t_order o left join(t_order_product op,t_product p)
	on o.order_id=op.oid and op.pid=p.product_id
	where o.order_id = #{oid};
</select>
<resultMap type="cyt.mybatis.bean.Order" id="getOrderWithSells">
	<id column="order_id" property="orderId"/>
	<result column="price" property="price"/>
	<collection property="sellEntries" ofType="cyt.mybatis.bean.SellEntry">
		<result column="product_id" property="product.productId"/>
		<result column="name" property="product.name"/>
		<result column="brand" property="product.brand"/>
		<result column="amount" property="amount"/>
	</collection>
</resultMap>

关键要看 sql 语句，为三个表的关联查询，理清逻辑

9.1.3 测试

@Test
void testIdCard() {
	try {
		IdCardMapper dao = session.getMapper(IdCardMapper.class);
		IdCard idCard = dao.getIdCardByCardNo(1001);
		System.out.println(idCard);
	} finally {
		session.close();
	}
}

9.2 多对多分步查询

9.2.1 创建SellEntryMapper接口与映射文件

public interface SellEntryMapper {
	public List<SellEntry> searchEntrySellEntryByOrderId(Integer orderId);
}

<!-- public List<SellEntry> searchEntrySellEntryByOrderId(Integer orderId); -->
<select id="searchEntrySellEntryByOrderId" resultMap="searchEntrySellEntry">
	select o.order_id, o.price, p.product_id, p.name, p.brand, op.amount
	from t_order_product op, t_order o, t_product p
	where o.order_id=op.oid and op.pid=p.product_id
	and o.order_id = #{oid};
</select>
<resultMap type="cyt.mybatis.bean.SellEntry" id="searchEntrySellEntry">
	<result column="amount" property="amount"/>
	<result column="order_id" property="order.orderId"/>
	<result column="price" property="order.price"/>
	<result column="product_id" property="product.productId"/>
	<result column="name" property="product.name"/>
	<result column="brand" property="product.brand"/>
</resultMap>

测试

@Test
void testSellEntry() {
	try {
		SellEntryMapper dao = session.getMapper(SellEntryMapper.class);
		List<SellEntry> sellEntries = dao.searchEntrySellEntryByOrderId(1);
		System.out.println(sellEntries);
	} finally {
		session.close();
	}
}

上述操作为分步操作做准备，即完成能通过orderId查询到所有sells的任务

9.2.1 OrderMapper接口方法和映射文件设置

接口方法

public Order getOrderWithSellsByIdStep(Integer orderId);

映射文件设置

<!-- public Order getOrderWithSellsByIdStep(Integer orderId); -->
<select id="getOrderWithSellsByIdStep" resultMap="getOrderWithSellsStep">
	select * from t_order where order_id = #{id};
</select>
<resultMap type="cyt.mybatis.bean.Order" id="getOrderWithSellsStep">
	<id column="order_id" property="orderId"/>
	<result column="price" property="price"/>
	<collection property="sellEntries" select="cyt.mybatis.dao.SellEntryMapper.searchEntrySellEntryByOrderId"
		column="order_id">
	</collection>
</resultMap>

9.2.3 测试及结果

OrderMapper dao = session.getMapper(OrderMapper.class);
Order order = dao.getOrderWithSellsByIdStep(1);
System.out.println(order);

DEBUG 10-22 16:15:12,117 ==>  Preparing: select * from t_order where order_id = ?;  (BaseJdbcLogger.java:137) 
DEBUG 10-22 16:15:12,151 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-22 16:15:12,207 <==      Total: 1  (BaseJdbcLogger.java:137) 
DEBUG 10-22 16:15:12,209 ==>  Preparing: select o.order_id, o.price, p.product_id, p.name, p.brand, op.amount from t_order_product op, t_order o, t_product p where o.order_id=op.oid and op.pid=p.product_id and o.order_id = ?;  (BaseJdbcLogger.java:137) 
DEBUG 10-22 16:15:12,210 ==> Parameters: 1(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 10-22 16:15:12,213 <==      Total: 2  (BaseJdbcLogger.java:137) 
Order [orderId=1, price=145, user=null, sellEntries=[SellEntry [ammount=1, product=Product [productId=1, name=prod1, brand=nike], order=Order [orderId=1, price=145, user=null, sellEntries=null]], SellEntry [ammount=2, product=Product [productId=4, name=prod4, brand=adidas], order=Order [orderId=1, price=145, user=null, sellEntries=null]]]]

总结 Mybatis 查询：

一对一两种方式，级联和association，一对多则用collection。
关联查询和分步查询主要差别：关联直接一个语句带过，对应mapper中的接口和xml设置都只有一个；分步查询则需要两个sql，也就是对应 mapper 中的接口和xml设置都需要两个。

10 动态SQL

<if>
<choose><when><otherwise>
<where>
<trim>
<set>
<foreach>

10.1 本节使用例子的先行配置：

Goods类

public class Goods {
	private Integer goodsId;
	private String name;
	private Integer price;
	private Integer quantity;

GoodSearchCondition类

public class GoodSearchCondition {
	private Integer id;
	private String name;
	private Integer minPrice;
	private Integer maxPrice;
	private Integer minQuantity;
	private Integer maxQuantity;

mapper.java

public interface GoodsMapper {
	public List<Goods> searchGoodsByCondition(GoodSearchCondition condition);
}

10.2 <if>元素

在MyBatis中，元素是最常用的判断语句，它类似于Java中的if语句，主要用于实现某些简单的条件选择。其基本使用示例如下：

select * from t_customer where 1=1 
<if test="username !=null and username !=''">
	and username like concat('%',#{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
	and jobs= #{jobs}
</if>

例子

在以上现行配置下配置映射文件
mapper.xml

<!-- public List<Goods> searchGoodsByCondition(GoodSearchCondition condition); -->
<select id="searchGoodsByCondition" resultType="cyt.mybatis.dynamicsql.bean.Goods">
	select goods_id,name,price,quantity from tbl_goods
	where 1=1
	<if test="id!=null">
		and goods_id = #{id}
	</if>
	<if test="name!=null and name!=''">
		and name = #{name}
	</if>
	<if test="minPrice!=null">
		and price > #{minPrice}
	</if>
	<if test="maxPrice!=null">
		and price <![CDATA[<]]> #{maxPrice}
	</if>
	<if test="minQuantity!=null">
		and quantity > #{minQuantity}
	</if>
	<if test="maxQuantity!=null">
		and quantity <![CDATA[<]]> #{maxQuantity}
	</if>
</select>

where 1=1 保证了where后面的条件成立，又避免了where后面第一个词是and或者or之类的关键词。不过“where 1=1”这种写法对于初学者来将不容易理解，并且也不够雅观。

10.3 <choose>、<when>、<otherwise>元素

主要用法：

select * from t_customer where 1=1
<choose>
	<when test="username !=null and username !=''">
				and username like concat('%',#{username}, '%')
	</when>
	<when test="jobs !=null and jobs !=''">
				and jobs= #{jobs}
	</when>
	<otherwise>
			and phone is not null
	</otherwise>
</choose>

10.4 <where>、<trim>元素

<where>添加在if外层，则可以不写之前的where 1=1，会根据情况自动组装sql而不会出错

<where>元素示例

<!-- public List<Goods> searchGoodsByCondition(GoodSearchCondition condition); -->
<select id="searchGoodsByCondition" resultType="cyt.mybatis.dynamicsql.bean.Goods">
	select goods_id,name,price,quantity from tbl_goods
	<where>
	<if test="id!=null">
		and goods_id = #{id}
	</if>
	......
	<if test="maxQuantity!=null">
		and quantity <![CDATA[<]]>= #{maxQuantity}
	</if>
	</where>
</select>

<trim>元素示例

<!-- public List<Goods> searchGoodsByConditionTrim(GoodSearchCondition condition); -->
<select id="searchGoodsByConditionTrim" resultType="cyt.mybatis.dynamicsql.bean.Goods">
	select goods_id,name,price,quantity from tbl_goods
	<trim prefix="where" prefixOverrides="and">
		<if test="id!=null">
			and goods_id = #{id}
		</if>
		......
		<if test="maxQuantity!=null">
			and quantity <![CDATA[<]]>= #{maxQuantity}
		</if>
	</trim>
</select>

10.5 <set>元素

在MyBatis中可以使用动态SQL中的元素进行处理
使用和元素对username和jobs进行更新判断，并动态组装SQL。这样就只需要传入想要更新的字段即可

主要用法：

<update id="updateCustomer"  parameterType="com.itheima.po.Customer">
        update t_customer 
        <set>
            <if test="username !=null and username !=''">
                  username=#{username},
            </if>
            <if test="jobs !=null and jobs !=''">
                  jobs=#{jobs},
            </if>
        </set>
        where id=#{id}
</update>

10.6 <foreach>元素

示例

public List<Goods> searchGoodsByIds(@Param("ids") List<Integer> ids);

需要注意这里使用别名 @Param 避免出错

<!-- public List<Goods> searchGoodsByIds(@Param("ids") List<Integer> ids); -->
<select id="searchGoodsByIds" resultType="cyt.mybatis.dynamicsql.bean.Goods">
	select goods_id,name,price,quantity from tbl_goods
	where goods_id in
	<foreach collection="ids" item="id" open="(" close=")" separator=",">
		#{id}
	</foreach>
</select>

测试：

GoodsMapper dao = session.getMapper(GoodsMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(101);
ids.add(104);
ids.add(105);
List<Goods> goods = dao.searchGoodsByIds(ids);
System.out.println(goods);

item：配置的是循环中当前的元素。
index：配置的是当前元素在集合的位置下标。
collection：配置的list是传递过来的参数类型（首字母小写），它可以是一个array、list（或collection）、Map集合的键、POJO包装类中数组或集合类型的属性名等。
open和close：配置的是以什么符号将这些集合元素包装起来。
separator：配置的是各个元素的间隔符。

====== MyBatis 끝나다 ！======