mybatis的对象关系映射-06

最新推荐文章于 2024-07-04 00:45:00 发布

昔我往昔

最新推荐文章于 2024-07-04 00:45:00 发布

阅读量652

点赞数

分类专栏：框架文章标签： mysql java mybatis

本文链接：https://blog.csdn.net/hekai7217/article/details/106187257

版权

框架专栏收录该内容

19 篇文章 1 订阅

订阅专栏

06-mybatis的对象关系映射

实际发中，一个业务可能会涉及到多个数据表的查询,那么多表查询就涉及到连接查询(等值连接),表与表之间有一个外键关联

但是程序中最终获取的表封装的对象, 对象与对象之间是没有外键关系的,对象和对象之间只有依赖关系

对象之间的关系主要有四种:

一对一关系：一个人对应一个身份证id

一对多的关系: 一个用户对应多个订单(重点)

多对一关系: 多个订单对应一个用户（重点）

多对多的关系: 多个学生对应多个老师，多个老师对应多个学生

MyBatis框架支持多表查询封装对象之间关系

一对多查询

多对一和一对一查询

上述两个标签都在手动映射标签中

1. 准备多表，表之间的关系用逻辑外键(用户表和订单表)

用户表:

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `pwd` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;


INSERT INTO `t_user` VALUES ('1', '梅超风', '123', '30');
INSERT INTO `t_user` VALUES ('2', '曲正风', '123', '40');
INSERT INTO `t_user` VALUES ('3', '黄药师', '123', '35');

订单表:


CREATE TABLE `t_order` (
  `oid` int(11) NOT NULL AUTO_INCREMENT,
  `onum` varchar(50) DEFAULT NULL,
  `oname` varchar(50) DEFAULT NULL,
  `oprice` decimal(10,0) DEFAULT NULL,
  `uid` int(10) NOT NULL,
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `t_order` VALUES ('1', '1001', '手机', '2000', '1', '2019-12-11 09:22:27');
INSERT INTO `t_order` VALUES ('2', '1002', 'ipad', '3000', '1', '2020-02-06 09:22:59');
INSERT INTO `t_order` VALUES ('3', '1003', '三体书籍', '70', '2', '2020-04-01 09:23:33');

2. 多对一查询

javabean对象的封装:

订单对象:
public class Order {

    private Integer oid;

    private String onum;

    private String oname;

    private Double oprice;

    private Integer uid;

    private Date createtime;
    
    // 以订单为中心  多个 订单 对应一个用户
    private User user;
}

用户对象

public class User {

    private Integer id;

    private String name;

    private String pwd;

    private Integer age;
}

Mapper接口:

    /**
     * 查询订单数据  
     * @return
     */
    List<Order> selectByOrderId();

mapper.xml 映射:


    <select id="selectByOrderId" parameterType="integer" resultMap="order_map">
        select * from t_order
    </select>

    <!--
    需要映射订单对象属性
    private User user;

   解决方案，使用关联查询
   <association property="user" column="uid" select=""/>
       property ：需要映射的属性
       column ：  要映射属性对应的外键列
       select ：  对应的查询方案, 对应查询的命名空间+.+功能id
                  如果是同一个命名空间 可以省略前面的命名空间 
 -->
    <resultMap id="order_map" type="Order">

        <id column="oid" property="oid"></id>
        <result column="onum" property="onum"></result>
        <result column="oname" property="oname"></result>
        <result column="oprice" property="oprice"></result>
        <result column="onum" property="onum"></result>
        <result column="uid" property="uid"></result>

        <association property="user" column="uid" select="cn.demo01.mapper.Many2OneMapper.findUserById">
        </association>

    </resultMap>

    <select id="findUserById" parameterType="integer" resultType="User">
        select * from t_user where id = #{id}
    </select>

测试代码:

    @Test
    public void testMany2one(){

        SqlSession sqlSession = SqlSessionUtils.getSqlSession();


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

        List<Order> orders = mapper.selectByOrderId();

        orders.forEach(System.out::println);

    }

日志:

DEBUG [main] - ==>  Preparing: select * from t_order 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: oid, onum, oname, oprice, uid, createtime
TRACE [main] - <==        Row: 1, 1001, 手机, 2000, 1, 2019-12-11 09:22:27.0
DEBUG [main] - ====>  Preparing: select * from t_user where id = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
TRACE [main] - <====    Columns: id, name, pwd, age
TRACE [main] - <====        Row: 1, 梅超风, 123, 30
DEBUG [main] - <====      Total: 1
TRACE [main] - <==        Row: 2, 1002, ipad, 3000, 1, 2020-02-06 09:22:59.0
TRACE [main] - <==        Row: 3, 1003, 三体书籍, 70, 2, 2020-04-01 09:23:33.0
DEBUG [main] - ====>  Preparing: select * from t_user where id = ? 
DEBUG [main] - ====> Parameters: 2(Integer)
TRACE [main] - <====    Columns: id, name, pwd, age
TRACE [main] - <====        Row: 2, 曲正风, 123, 40
DEBUG [main] - <====      Total: 1
DEBUG [main] - <==      Total: 3
Order{oid=1, onum='1001', oname='手机', oprice=2000.0, uid=1, createtime=Wed Dec 11 09:22:27 CST 2019, user=User{id=1, name='梅超风', pwd='123', age='30'}}
Order{oid=2, onum='1002', oname='ipad', oprice=3000.0, uid=1, createtime=Thu Feb 06 09:22:59 CST 2020, user=User{id=1, name='梅超风', pwd='123', age='30'}}
Order{oid=3, onum='1003', oname='三体书籍', oprice=70.0, uid=2, createtime=Wed Apr 01 09:23:33 CST 2020, user=User{id=2, name='曲正风', pwd='123', age='40'}}

多个订单对应一个用户 (订单 1001,1002 对应用户 梅超风)

3.一对多查询

以用户为中心查询用户的所有信息（包括订单），一个用户对应多个订单

javabean对象:

用户对象:

public class User {

    private Integer id;

    private String name;

    private String pwd;

    private Integer age;

	// 一个用户对应多个订单 
    private Set<Order> orders;
}

订单对象:

public class Order {

    private Integer oid;

    private String onum;

    private String oname;

    private Double oprice;

    private Integer uid;

    private Date createtime;
}

mapper接口方法:

    /**
     * 通过id查询 user
     * @param uid
     * @return
     */
    User selectUserById(Integer uid);

mapper.xml的映射:

   <select id="selectUserById" resultType="integer" resultMap="rs_user">

        select * from t_user where id = #{id}

    </select>
    <!-- private Set<Order> orders;
        集合属性映射
         <collection property="orders" column="id" select=""/>
             property:需要映射得集合 orders
             column : 部门的主键 id
             select : 关联查询，去根据uid查询出对应的订单信息
                      值关联查询功能的  命名空间+.+功能id
     -->
    <resultMap id="rs_user" type="User">

        <id property="id" column="id"></id>
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>
        <result property="age" column="age"/>

        <collection property="orders" column="id" select="selectOrderByOrderId"/>
    </resultMap>

<!--    关联查询
        通过uid 查询 查询对应的订单  -->
    <select id="selectOrderByOrderId" parameterType="integer" resultType="Order">
        select * from t_order where uid = #{uid}
    </select>

代码测试:

    @Test
    public void testMany2one(){

        SqlSession sqlSession = SqlSessionUtils.getSqlSession();

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

        User user = mapper.selectUserById(1);

        System.out.println(user);

    }

日志:

DEBUG [main] - ==>  Preparing: select * from t_user where id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, name, pwd, age
TRACE [main] - <==        Row: 1, 梅超风, 123, 30
DEBUG [main] - ====>  Preparing: select * from t_order where uid = ? 
DEBUG [main] - ====> Parameters: 1(Integer)
TRACE [main] - <====    Columns: oid, onum, oname, oprice, uid, createtime
TRACE [main] - <====        Row: 1, 1001, 手机, 2000, 1, 2019-12-11 09:22:27.0
TRACE [main] - <====        Row: 2, 1002, ipad, 3000, 1, 2020-02-06 09:22:59.0
DEBUG [main] - <====      Total: 2
DEBUG [main] - <==      Total: 1
User{id=1, name='梅超风', pwd='123', age=30, orders=[Order{oid=2, onum='1002', oname='ipad', oprice=3000.0, uid=1, createtime=Thu Feb 06 09:22:59 CST 2020}, Order{oid=1, onum='1001', oname='手机', oprice=2000.0, uid=1, createtime=Wed Dec 11 09:22:27 CST 2019}]}

Process finished with exit code 0

也可以通过等值连接查询:

修改mapper.xml 映射:


    <select id="selectUserById" parameterType="integer" resultMap="rs_user">
            select u.id,u.`name`,u.pwd,u.age,
			 o.oid,o.onum,o.oname,o.oprice,o.uid,o.createtime

            from t_user u join t_order o on u.id = o.uid
            where u.id = #{id}
    </select>

    <!-- private Set<Order> orders;
        集合属性映射
         <collection property="orders" column="id" select=""/>
             property:需要映射得集合 emps
             column : 部门的主键 id
             ofType : 需要映射集合的泛型的类型
             javaType: java的数据类型
     -->
    <resultMap id="rs_user" type="User">

        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="pwd" column="pwd"/>

        <collection property="orders" ofType="Order" javaType="list">
            <id property="oid" column="oid"/>
            <result property="onum" column="onum"/>
            <result property="oname" column="oname"/>
            <result property="oprice" column="oprice"/>
            <result property="createtime" column="createtime"/>
        </collection>
    </resultMap>

代码测试:

@Test
public void testMany2one(){

    SqlSession sqlSession = SqlSessionUtils.getSqlSession();

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

    User user = mapper.selectUserById(1);

    System.out.println(user);

}

日志：

DEBUG [main] - ==>  Preparing: select u.id,u.`name`,u.pwd,u.age, o.oid,o.onum,o.oname,o.oprice,o.uid,o.createtime from t_user u join t_order o on u.id = o.uid where u.id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, name, pwd, age, oid, onum, oname, oprice, uid, createtime
TRACE [main] - <==        Row: 1, 梅超风, 123, 30, 1, 1001, 手机, 2000, 1, 2019-12-11 09:22:27.0
TRACE [main] - <==        Row: 1, 梅超风, 123, 30, 2, 1002, ipad, 3000, 1, 2020-02-06 09:22:59.0
DEBUG [main] - <==      Total: 2
User{id=1, name='梅超风', pwd='123', age=30, orders=[Order{oid=1, onum='1001', oname='手机', oprice=2000.0, uid=null, createtime=Wed Dec 11 09:22:27 CST 2019}, Order{oid=2, onum='1002', oname='ipad', oprice=3000.0, uid=null, createtime=Thu Feb 06 09:22:59 CST 2020}]}

昔我往昔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mybatis的对象关系映射-06

06-mybatis的对象关系映射实际发中，一个业务可能会涉及到多个数据表的查询,那么多表查询就涉及到连接查询(等值连接),表与表之间有一个外键关联但是程序中最终获取的表封装的对象, 对象与对象之间是没有外键关系的,对象和对象之间只有依赖关系对象之间的关系主要有四种:一对一关系：一个人对应一个身份证id一对多的关系: 一个用户对应多个订单(重点)多对一关系: 多个订单对应一个用户（重点）多对多的关系: 多个学生对应多个老师，多个老师对应多个学生MyBatis框架支持多表查询封装对象之间关系
复制链接

扫一扫

专栏目录