day-2021-9-30(mybaits动态sql多表操作)

9.5.5 <choose>标签：多条件

• 语法

	<select id="">
		<choose>
			<when test=""></when>
			<when test=""></when>
			<otherwise></otherwise>
		</choose>
	</select>

9.5.6 <sql>标签

• MyBatis为我们提供了代码片段，使用<sql>定义公共SQL语句，使用<include>将需要的SQL片段拼凑到指定的位置。

<!-- 所有字段名称SQL片段 -->
<sql id="user_all_column">uid, username, password, name, email, birthday, sex, state</sql>

<select id="selectAll" parameterType="string" resultMap="userResultMap">
	select <include refid="user_all_column" /> from user
</select>

9.6 关系映射：一对多

9.6.1 mapper接口

• UserMapper，已有

  public interface UserMapper {
      /**
       * 通过id查询详情
       * @param uid
       * @return
       */
      public User selectById(String uid);
  }
  

• OrderMapper

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.Order;
  import org.apache.ibatis.annotations.Param;
  
  /**
   * @author 桐叔
   * @email liangtong@itcast.cn
   */
  public interface OrderMapper {
  
      /**
       * 查询指定用户的所有的订单
       * @param uid
       * @return
       */
      public Order findOrdersByUserId(@Param("uid") Integer uid) ;
  }
  
  

9.6.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="com.czxy.ssm.mapper.OrderMapper">


    <select id="findOrdersByUserId" parameterType="string" resultType="order" >
		select * from orders where uid = #{uid}
	</select>

</mapper>

2. 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="com.czxy.ssm.mapper.UserMapper">

	<resultMap type="com.czxy.ssm.domain.User" id="userResultMap">
		<id column="uid" property="uid"/>
		<result column="username" property="username"/>
		<collection property="orderList" column="uid" select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId" />
	</resultMap>



    <select id="selectById" parameterType="string" resultMap="userResultMap">
		select * from user where uid = #{id}
	</select>
    
</mapper>

9.6.3 核心配置文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MyAKEPOf-1633306083789)(assets/image-20210723104134065.png)]

9.6.4 测试

public class TestUserMapper {

    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("u001");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }
    
}

9.7 关系映射：多对一

9.7.1 mapper接口

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
public interface OrderMapper {

    /**
     *
     * @param oid
     * @return
     */
    public Order selectById(@Param("oid") String oid);
}

9.7.2 映射文件：OrdersMapper.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.czxy.ssm.mapper.OrderMapper">

	<resultMap id="ordersResultMap" type="order">
		<id property="oid" column="oid"></id>
		<result property="ordertime" column="ordertime" ></result>
		<result property="total" column="total" ></result>
		<result property="state" column="state" ></result>
		<result property="address" column="address" ></result>
		<result property="name" column="name" ></result>
		<result property="telephone" column="telephone" ></result>
		<result property="uid" column="uid" ></result>
		<association property="user" select="com.czxy.ssm.mapper.UserMapper.selectById" column="uid" />
	</resultMap>
	<select id="selectById" resultMap="ordersResultMap">
		select * from orders where oid = #{oid}
	</select>

</mapper>

9.7.3 测试类package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.czxy.ssm.vo.UserVo;
import org.junit.Test;

import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**

• @author 桐叔

• @email liangtong@itcast.cn
  */
  public class TestOrderMapper {

  @Test
  public void testSelectById() {
  OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

   Order order = orderMapper.selectById("x001");
   System.out.println(order);
  
   MyBatisUtils.commitAndclose();
  

  }

}