Mybatis(三)多表查询、注解开发、一对一、一对多、多对多查询、缓存（一级缓存、二级缓存）

1.Mybatis多表查询

1.1 一对一查询

1.1.1 一对一查询的模型

MapperScannerConfigurer

  用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

1.1.2一对一查询的语句

  对应的sql语句：select * from orders o,user u where o.uid=u.id;

查询的结果如下：

1.1.3 创建Order和User实体

public class Order {
   

    private int id;
    private Date ordertime;
    private double total;

    //代表当前订单从属于哪一个客户
    private User user;
}

public class User {
   
    
    private int id;
    private String username;
    private String password;
    private Date birthday;

}

1.1.4 创建OrderMapper接口

public interface OrderMapper {
   
    List<Order> findAll();
}

1.1.5 配置OrderMapper.xml

<mapper namespace="com.itheima.mapper.OrderMapper">
    <resultMap id="orderMap" type="com.itheima.domain.Order">
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        select * from orders o,user u where o.uid=u.id
    </select>
</mapper>

其中还可以配置如下：

<resultMap id="orderMap" type="com.itheima.domain.Order">
    <result property="id" column="id"></result>
    <result property="ordertime" column="ordertime"></result>
    <result property="total" column="total"></result>
    <association property="user" javaType="com.itheima.domain.User">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap>

1.1.6 测试结果

OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> all = mapper.findAll();
for(Order order : all){
   
    System.out.println(order);
}

1.2 一对多查询

1.2.1 一对多查询的模型

  用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

1.2.2 一对多查询的语句

对应的sql语句：select *,o.id oid from user u left join orders o on u.id=o.uid;

查询的结果如下：

1.2.3 修改User实体

public class Order {
   

    private int id;
    private Date ordertime;
    private double total;

    //代表当前订单从属于哪一个客户
    private User user;
}

public class User {
   
    
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
}

1.2.4 创建UserMapper接口

public interface UserMapper {
   
    List<User> findAll();
}

1.2.5 配置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.zy.mapper.UserMapper">
        <!--    sql片段的抽取-->

    <select id="findAll" resultType="com.zy.entity.User">
        select * from user
    </select>
    <delete id="delete" parameterType="int">
        delete from user where id=#{id}
    </delete>
    <update id="update" parameterType="com.zy.entity.User">
        update  user set value username=#{username},password=#{password}
    </update>
    <insert id="insert" parameterType="com.zy.entity.User">
        insert into  user(username,password) value {#{username},#{password}}
    </insert>
    <select id="findByCollection" parameterType="user" resultType="user">
        selcet * from user
        <where>
        <if test="id!=0">
          and  id=#{id}
        </if>
        <