myBatis框架之入门(四)

Mybatis多表管理查询

多表关联关系分析:

多表关联:至少两个表关联.分析多表关系的经验技巧:从一条记录出发,不要从表整体去分析,比如分析A表和B表关系,A表中的一条记录对应B表中的几条记录,如果是一条,那么A到B就是一对一;如果是多条,那么A到B就是一对多.

　　一对一:例如从订单表出发,到用户表,是一对一的关系.

　　一对多,例如从用户表出发,到订单表,一条用户数据可以在订单表中存在多条记录,这就是一对多.通过主外键来体现一对多的表结构.一的一方是主表,多的一方是从表,从表中有一个字段是外键,指向了主表的主键.

　　多对多,用户和角色表,一个用户可以有很多角色,一个角色有很多用户,多对多通过中间表来表现.

多表关联的sql语句表达分析

　　笛卡尔积
　　　　SELECT *FROM USER,orders

　　关联查询

　　内关联 inner join on

　　　　SELECT * FROM USER u,orders o WHERE u.id=o.user_id

　　　　SELECT * FROM USER u INNER JOIN orders o ON u.id=o.user_id;

　　左外连接

　　　　SELECT * FROM USER u LEFT JOIN orders o ON u.id=o.user_id

　右外连接　

　　　　SELECT * FROM USER u RIGHT JOIN orders o ON u.id=o.user_id

一对一查询

需求：查询订单表全部数据，关联查询出订单对应的用户数据（username address）。

Sql语句自己来写，Mybatis只是帮我们执行sql语句同时封装结果集。

SQL语句：

SELECT o.id,o.user_id,o.number,o.note,u.address,u.username 
FROM orders

o LEFT JOIN USER u ON o.user_id=u.id

对于查询结果集，没有对应的pojo对象，因此MyBatis框架也不能封装结果集，但是可以将User对象放在Orders对象中，因为一对一关系，一个Orders对象可以对应一个User对象。需要手动映射方式，实现查询结果集封装。
pojo对象

public class Orders {

private Integer id;

private Integer userId;

private String number;

private Date createtime;

private String note;

private User user;

public User getUser() {

return user;

}

public void setUser(User user) {

this.user = user;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public Integer getUserId() {

return userId;

}

public void setUserId(Integer userId) {

this.userId = userId;

}

public String getNumber() {

return number;

}

public void setNumber(String number) {

this.number = number;

}

public Date getCreatetime() {

return createtime;

}

public void setCreatetime(Date createtime) {

this.createtime = createtime;

}

public String getNote() {

return note;

}

public void setNote(String note) {

this.note = note;

}

@Override

public String toString() {

return "Orders{" +

"id=" + id +

", userId=" + userId +

", number='" + number + '\'' +

", createtime=" + createtime +

", note='" + note + '\'' +

", user=" + user +

'}';

}

}

association（联合）标签，实现手动映射

　　propery属性：封装的pojo对象
　　javaType属性：封装的pojo对象类型

 <mapper namespace="com.itheima.mapper.OrdersMapper">

        <select id="queryOrdersUser" resultMap="ordersUserResultType" >

            SELECT o.id,o.user_id,o.number,o.note,u.address,u.username FROM

            orders o LEFT JOIN USER u

            ON o.user_id=u.id

        </select>

        <resultMap id="ordersUserResultType" type="orders">

            <id column="id" property="id"></id>

            <result column="user_id" property="userId"></result>

            <result column="number" property="number"></result>

            <result column="createtime" property="createtime"></result>

            <result column="note" property="note"></result>

            <!-- 手动映射，配置User对象-->

            <association property="user" javaType="user">

                <id column="user_id" property="id"></id>

                <result column="address" property="address"></result>

                <result column="username" property="username"></result>

            </association>

        </resultMap>

    </mapper>

@Test
public void queryOrdersUser(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
    List<Orders> list = mapper.queryOrdersUser();
    if(list != null && list.size() > 0){
        for (Orders orders : list){
            System.out.println(orders);
        }
    }
    sqlSession.close();
}

往往还有另外一种方式：新创建一个pojo，这个pojo里面增加两个字段username和address，然后使用resultType自动映射即可，但是这种方式不推荐，因为pojo多了之后很混乱，不容易做技术管理，而且这点方式也有点low
一对多查询
查询全部用户数据，关联查询出订单数据。

SQL语句：SELECT
u.id,u.username,u.sex,u.birthday,u.address,o.user_id,o.id,o.number,o.createtime,o.note
FROM USER u LEFT JOIN orders o ON u.id=o.user_id

一个用户对应多个订单数据，因此在pojo中，订单对象是存储在集合中，并保存在User对象中

pojo对象

public class User {

  

    private int id;

  

    private String username;

  

    private String sex;

  

    private Date birthday;

  

    private String address;

  

    private List<Orders> ordersList;

  

    public List<Orders> getOrdersList() {

    return ordersList;

 }

  

    public void setOrdersList(List<Orders> ordersList) {

    this.ordersList = ordersList;

 }

  

    public int getId() {

    return id;

 }

  

    public void setId(int id) {

    this.id = id;

 }

  

    public String getUsername() {

    return username;

 }

  

    public void setUsername(String username) {

    this.username = username;

 }

  

    public String getSex() {

    return sex;

 }

  

    public void setSex(String sex) {

    this.sex = sex;

 }

  

    public Date getBirthday() {

    return birthday;

 }

  

    public void setBirthday(Date birthday) {

    this.birthday = birthday;

 }

  

    public String getAddress() {

    return address;

 }

  

    public void setAddress(String address) {

    this.address = address;

 }

  

    @Override

  

    public String toString() {

    return "User{" +

        "id=" + id +

        ", username='" + username + '\'' +

        ", sex='" + sex + '\'' +

        ", birthday=" + birthday +

        ", address='" + address + '\'' +

                collection标签：

        property属性：封装的对应的属性名

        ofType属性：已经指定为一个集合List，需要指定集合中的泛型

        ", ordersList=" + ordersList +

        '}';

 }

}

    <select id="queryUserOrders" resultMap="userOrdersResultMap">

         SELECT u.id,u.username,u.sex,u.birthday,u.address,o.user_id,o.id,

         o.number,o.createtime,o.note

         FROM USER u LEFT JOIN orders o

         ON u.id=o.user_id

    </select>

    <resultMap id="userOrdersResultMap" type="user">

         <id column="id" property="id"></id>

         <result column="username" property="username"></result>

         <result column="sex" property="sex"></result>

         <result column="birthday" property="birthday"></result>

         <result column="address" property="address"></result>

         <collection property="ordersList" ofType="orders">

           <id column="id" property="id"></id>

           <result column="user_id" property="userId"></result>

           <result column="number" property="number"></result>

           <result column="createtime" property="createtime"></result>

           <result column="note" property="note"></result>

         </collection>

    </resultMap>

    @Test

    public void queryUserOrders(){

        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> list = mapper.queryUserOrders();

        if(list != null && list.size() > 0){

            for(User user : list){

                System.out.println(user);

            }

        }

        sqlSession.close();

    }

多对多查询

多对多：双向的一对多，从A表到B表出发是一对多的关系，从B表出发到A表也是一对多的关系
SQL语句：

SELECT r.RID,r.RNAME,r.RDESC,u.id,u.username,u.sex,u.birthday,u.address

FROM role r LEFT JOIN user_role ur

ON r.RID=ur.RID LEFT JOIN USER u ON u.id=ur.UID

pojo对象

public class Role {

    private Integer rid;

    private String rname;

    private String rdesc;

    private List<User> userList;

    public Integer getRid() {

        return rid;

    }

    public void setRid(Integer rid) {

        this.rid = rid;

    }

    public String getRname() {

        return rname;

    }

    public void setRname(String rname) {

        this.rname = rname;

    }

    public String getRdesc() {

        return rdesc;

    }

    public void setRdesc(String rdesc) {

        this.rdesc = rdesc;

    }

    public List<User> getUserList() {

        return userList;

    }

    public void setUserList(List<User> userList) {

        this.userList = userList;

    }

    @Override

    public String toString() {

        return "Role{" +

                "rid=" + rid +

                ", rname='" + rname + '\'' +

                ", rdesc='" + rdesc + '\'' +

                ", userList=" + userList +

                '}';

    }

}

手动映射roleMapper.xml

  <mapper namespace="com.itheima.mapper.RoleMapper">

        <select id="queryRoleUser" resultMap="roleUserResultMap">

            SELECT

            r.RID,r.RNAME,r.RDESC,u.id,u.username,u.sex,u.birthday,u.address FROM

            role r LEFT JOIN user_role ur ON r.RID=ur.RID LEFT JOIN USER u ON

            u.id=ur.UID

        </select>

        <resultMap id="roleUserResultMap" type="role">

            <id column="rid" property="rid"></id>

            <result column="rname" property="rname"></result>

            <result column="rdesc" property="rdesc"></result>

            <result column="" property=""></result>

            <collection property="userList" ofType="user">

                <id column="id" property="id"></id>

                <result column="username" property="username"></result>

                <result column="sex" property="sex"></result>

                <result column="birthday" property="birthday"></result>

                <result column="address" property="address"></result>

            </collection>

        </resultMap>

    </mapper>

    @Test

    public void queryRoleUser() {

  SqlSession sqlSession = sqlSessionFactory.openSession();

  RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);

  List<Role> roleList = mapper.queryRoleUser();

  if (roleList != null && roleList.size() > 0) {

    for (Role role : roleList) {

      System.out.println(role);

   }

 }

  sqlSession.close();

    }

小案例:

用户主键查询用户信息

<!--

 主键查询用户信息

-->

<select id="queryUserById" resultType="user" parameterType="int">

 select * from user where id = #{id}

</select>

/**

* 主键查询用户信息

*/

@Test

public void queryUserById(){

  SqlSession sqlSession = sqlSessionFactory.openSession();

  UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

  User user = userMapper.queryUserById(1);

  System.out.println(user);

  sqlSession.close();

}

订单查询用户信息一对一

<!--

 订单，查询订单对应的用户

-->

<select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">

 SELECT

o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address FROM

orders o LEFT JOIN USER u

 ON o.user_id = u.id

</select>

用户查询订单信息 一对多

<resultMap id="queryOrdersUserResultMap" type="orders">

  <id column="id" property="id"></id>

  <result column="user_id" property="userId"></result>

  <result column="number" property="number"></result>

  <result column="createtime" property="createtime"></result>

  <result column="note" property="note"></result>

  <association property="user" javaType="user">

    <id column="user_id" property="id"></id>

    <result column="username" property="username"></result>

    <result column="address" property="address"></result>

  </association>

</resultMap>

/**

* 订单查询用户信息，一对一

*/

@Test

public void queryOrdersUser(){

  System.out.println();

  SqlSession sqlSession = sqlSessionFactory.openSession();

  OrdersMapper ordersMapper =

sqlSession.getMapper(OrdersMapper.class);

  List<Orders> ordersList = ordersMapper.queryOrdersUser();

  if(ordersList != null && ordersList.size() > 0){

    for (Orders orders : ordersList){

      System.out.println(orders);

   }

 }

  sqlSession.close();

}

用户查询订单信息一对多

<!--

 根据用户查询订单，一对多

-->

<select id="queryUserOrders" resultMap="queryUserOrdersResultMap">

 SELECT u.id,u.username,u.sex,u.birthday,u.address,o.id

oid,o.number,o.createtime FROM USER u LEFT JOIN orders o

一对一延迟加载

针对的是关联对象

用户和订单从面相对象的角度来说就是关联对象，当只需要订单数据，尚不需要用户数据的时

候，就不应该去查询用户表，啥时候用到用户数据，啥时候查询

 ON u.id = o.user_id

</select>

<resultMap id="queryUserOrdersResultMap" type="user">

  <id column="id" property="id"></id>

  <result column="username" property="username"></result>

  <result column="sex" property="sex"></result>

  <result column="birthday" property="birthday"></result>

  <result column="address" property="address"></result>

  <collection property="ordersList" ofType="orders">

    <id column="oid" property="id"></id>

    <result column="number" property="number"></result>

    <result column="createtime" property="createtime"></result>

  </collection>

</resultMap>

/**

* 用户查询订单，一对多

*/

@Test

public void queryUserOrders(){

  System.out.println();

  SqlSession sqlSession = sqlSessionFactory.openSession();

  UserMapper mapper = sqlSession.getMapper(UserMapper.class);

  List<User> list = mapper.queryUserOrders();

  if(list != null && list.size() > 0){

    for(User user : list){

      System.out.println(user);

   }

 }

  sqlSession.close();

}

一对一延迟加载

针对的是关联对象

用户和订单从面相对象的角度来说就是关联对象，当只需要订单数据，尚不需要用户数据的时候，就不应该去查询用户表，啥时候用到用户数据，啥时候查询
关联的sql语句肯定要拆分了

association标签
column属性：关联查询条件的属性
select属性：指定另一个查询语句

<!--

 订单，查询订单对应的用户

 拆分SQL语句，实现延迟加载

-->

<select id="queryOrdersUser" resultMap="queryOrdersUserResultMap">

 SELECT o.id,o.user_id,o.number,o.createtime,o.note FROM orders o

</select>

<resultMap id="queryOrdersUserResultMap" type="orders">

  <id column="id" property="id"></id>

  <result column="user_id" property="userId"></result>

  <result column="number" property="number"></result>

  <result column="createtime" property="createtime"></result>

  <result column="note" property="note"></result>

  <!--

   column指定查询的参数

   select关联其他查询语句

  -->

  <association property="user" javaType="user" column="user_id"

select="queryUserByUserId">

   <!-- <id column="user_id" property="id"></id>

    <result column="username" property="username"></result>

    <result column="address" property="address"></result>-->

  </association>

</resultMap>

<select id="queryUserByUserId" parameterType="int" resultType="user">

 select id,username,sex,birthday,address from user where id=#

{user_id}

</select>

/**

* 订单查询用户信息，一对一

* 延迟加载

*/

@Test

public void queryOrdersUser(){

  System.out.println();

  SqlSession sqlSession = sqlSessionFactory.openSession();

  OrdersMapper ordersMapper =

sqlSession.getMapper(OrdersMapper.class);

  List<Orders> ordersList = ordersMapper.queryOrdersUser();

  if(ordersList != null && ordersList.size() > 0){

    for (Orders orders : ordersList){

      System.out.println(orders);

      System.out.println(orders.getUser());

   }

 }

  sqlSession.close();

}

开启延迟加载配置

<settings>

<!-- 开启延迟加载-->

<setting name="lazyLoadingEnabled" value="true" />

<!-- 关闭立即加载-->

<setting name="aggressiveLazyLoading" value="false" />

<!-- 设定tostring等方法延迟加载-->

<setting name="lazyLoadTriggerMethods" value="true" />

</settings>

一对多延迟加载

<!--

根据用户查询订单，一对多

延迟加载，拆分SQL语句

-->

<select id="queryUserOrders" resultMap="queryUserOrdersResultMap">

SELECT u.id,u.username,u.sex,u.birthday,u.address FROM USER u

</select>

<resultMap id="queryUserOrdersResultMap" type="user">

<id column="id" property="id"></id>

<result column="username" property="username"></result>

<result column="sex" property="sex"></result>

<result column="birthday" property="birthday"></result>

<result column="address" property="address"></result>

<collection property="ordersList" ofType="orders" column="id"

select="queryOrdersByUserId">

<!-- <id column="oid" property="id"></id>

<result column="number" property="number"></result>

<result column="createtime" property="createtime"></result>-->

</collection>

</resultMap>

<select id="queryOrdersByUserId" parameterType="int" resultType="orders">

select id,number,createtime from orders where user_id = #{user_id}

</select>

/**

* 用户查询订单，一对多

* 延迟加载

*/

@Test

public void queryUserOrders(){

System.out.println();

SqlSession sqlSession = sqlSessionFactory.openSession();

UserMapper mapper = sqlSession.getMapper(UserMapper.class);

List<User> list = mapper.queryUserOrders();

if(list != null && list.size() > 0){

for(User user : list){

System.out.println(user);

List<Orders> ordersList = user.getOrdersList();

System.out.println(ordersList);

}

}

sqlSession.close();

}

MyBatis一级缓存

一级缓存：是SqlSession级别的，也就是同一个SqlSession内执行相同select语句的时候，不
再去查询数据库，而是从Mybatis内部的缓存内存结构去直接拿到数据。
缓存失效时机：
sqlSession关闭
sqlSession提交事务（意味着可能是一个增删改的动作，需要更新缓存，那么这个时候Mybatis就会把已有的一级缓存给清理掉）
MyBatis二级缓存

开启二级缓存

<setting name="cacheEnabled" value="true" />

<!-- 具体Mapper.xml配置-->

<cache></cache>

执行结果

/**

* 主键查询用户信息

*/

@Test

public void queryUserById(){

SqlSession sqlSession = sqlSessionFactory.openSession();

UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

User user = userMapper.queryUserById(1);

System.out.println(user);

sqlSession.close();

SqlSession sqlSession1 = sqlSessionFactory.openSession();

UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);

User user1 = userMapper1.queryUserById(1);

System.out.println(user1);

sqlSession1.close();

}

ratio，两次都会去查询二级缓存，但是第一次的时候缓存中没有数据，第二次才有，所以命中率是0.5

注意：Mybatis的二级缓存在使用的时候有很多限制，在实际开发的时候Mybatis的二级缓存应用很少

比如在其他mapper.xml文件中有对user表的更新，那么UserMapper.xml的二级缓存就可能错误，我们很难要求对一张表的操作全部放到同一个mapper.xml中。

巴特西

myBatis框架之入门(四)

最新文章

热门文章