Mybatis学习系列(五)关联查询

前面几节的示例基本都是一些单边查询,实际项目中,经常用到关联表的查询,比如一对一,一对多等情况。在Java实体对象中,一对一和一对多可是使用包装对象解决,属性使用List或者Set来实现,在mybatis中一对一和一对多可是使用association或者collection标签来配合实现。

在MyBatis中有两种方式实现关联查询:

1. 嵌套结果:使用嵌套结果映射来处理重复的联合结果的子集。首先,然让我们来查看这个元素的属性。所有的你都会看到,它和普通的只由 select 和resultMap 属性的结果映射不同

2. 嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型

一对一查询

示例:商品表products 和 商品分类category  一个商品对应一种分类,所以products->category是一对一的关系

1.嵌套结果实现(resultMap):

通过resultMap将查询结果中商品信息映射到Product对象中,在Product类中添加属性category,将关联结果映射到Product.category属性上。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<!-- 一对一查询 关联查询 使用resultMap映射结果集 -->
    <select id="oneToOneTestMap" parameterType="int" resultMap="productInfoMap">
        select p.*,c.id
        categoryId,c.name categoryName,c.remark categoryRemark
        from products p
        join category c
        on p.categoryId= c.Id
        where p.id
        =#{value}
    </select>
    <resultMap id="productInfoMap" type="com.sl.po.Product">
        <id column="id" property="Id" />
        <result column="name" property="Name" />
        <result column="description" property="Description" />
        <result column="unitprice" property="UnitPrice" />
        <result column="imageUrl" property="ImageUrl" />
        <result column="isnew" property="IsNew" />
        <result column="citycode" property="cityCode" />
 
   <!-- association:用于映射关联查询单个对象的信息
      property:要将关联查询的分类信息映射到属性category上
   -->
        <association property="category" javaType="com.sl.po.Category">
            <id column="categoryId" property="Id" />
            <result column="categoryName" property="Name" />
            <result column="categoryRemark" property="Remark" />
        </association>
    </resultMap>

注:也可以使用resultType配置实现,需要定义一个包装类含有product和category两个对象的属性即可。

2.嵌套查询实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<!-- 关联嵌套查询  -->
    <select id="oneToOneTestAssociationSelect" parameterType="int" resultMap="productInfoMap2">
        select p.*
        from products p
        where p.id =#{value}
    </select>
    <resultMap id="productInfoMap2" type="com.sl.po.Product" >
        <id column="id" property="Id" />
        <result column="name" property="Name" />
        <result column="description" property="Description" />
        <result column="unitprice" property="UnitPrice" />
        <result column="imageUrl" property="ImageUrl" />
        <result column="isnew" property="IsNew" />
        <result column="citycode" property="cityCode" />
        <!-- column:传递子查询参数,如果要处理符复合主键,使用column= "{prop1=col1,prop2=col2}"  -->
        <!-- select:子查询语句id -->
        <association property="category" javaType="com.sl.po.Category" column="categoryId" select="selectCategoryInfo">
        </association>
    </resultMap>
    <!-- 子查询 -->
    <select id="selectCategoryInfo" parameterType="int" resultType="com.sl.po.Category">
        select * from category where id=#{id}
    </select>

使用resultType、resultMap和嵌套查询对比分析:

resultType:使用resultType只需要将查询结果集中的列名与定义的pojo属性一一对应即可完成映射,缺点:resultType无法将查询结果映射到包装类的pojo属性中

resultMap:需要额外定义resultMap,在resultMap中将结果集列名与pojo对象属性一一配置。

嵌套查询:需要定义多个查询,上面示例定义两个查询,一个用于查询products表,一个用于查询category表。由于使用的是嵌套查询,当操作大型数据集合和列表时将会带来频繁操作数据库问题。即执行一条sql获取结果集(oneToOneTestAssociationSelect),根据该结果集,循环执行嵌套查询获取具体信息(selectCategoryInfo),与上面的嵌套结果查询相比,这种情况显然有明显不足。

3. 测试代码

定义Product和Category实体,添加Mapper接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
package com.sl.po;
 
import java.math.BigDecimal;
 
public class Product {
    private int Id;
    private String Name;
    private String Description;
    private BigDecimal UnitPrice;
    private String ImageUrl;
    private Boolean IsNew;
    private String cityCode;
    private int categoryId;
 
    private Category category;
 
 
    public Category getCategory() {
        return category;
    }
 
    public void setCategory(Category category) {
        this.category = category;
    }
 
    public int getCategoryId() {
        return categoryId;
    }
 
    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }
 
    public String getCityCode() {
        return cityCode;
    }
 
    public void setCityCode(String cityCode) {
        this.cityCode = cityCode;
    }
 
    public int getId() {
        return Id;
    }
 
    public void setId(int id) {
        this.Id = id;
    }
 
    public String getName() {
        return Name;
    }
 
    public void setName(String name) {
        this.Name = name;
    }
 
    public String getDescription() {
        return Description;
    }
 
    public void setDescription(String description) {
        this.Description = description;
    }
 
    public BigDecimal getUnitPrice() {
        return UnitPrice;
    }
 
    public void setUnitPrice(BigDecimal unitprice) {
        this.UnitPrice = unitprice;
    }
 
    public String getImageUrl() {
        return Name;
    }
 
    public void setImageUrl(String imageurl) {
        this.ImageUrl = imageurl;
    }
 
    public boolean getIsNew() {
        return IsNew;
    }
 
    public void setIsNew(boolean isnew) {
        this.IsNew = isnew;
    }
 
    @Override
    public String toString() {
        return "Product [Id=" + Id + ", Name=" + Name + ", Description=" + Description + ", UnitPrice=" + UnitPrice
                + ", ImageUrl=" + ImageUrl + ", IsNew=" + IsNew + ", cityCode=" + cityCode + ", categoryId="
                + categoryId + ", category=" + category + "]";
    }
 
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package com.sl.po;
 
import java.util.List;
 
public class Category {
    private int Id;
    private String Name;
    private String Remark;
 
    private List<Product> productList;
 
 
    public int getId() {
        return Id;
    }
    public List<Product> getProductList() {
        return productList;
    }
    public void setProductList(List<Product> productList) {
        this.productList = productList;
    }
    public void setId(int id) {
        Id = id;
    }
    public String getName() {
        return Name;
    }
    public void setName(String name) {
        Name = name;
    }
 
    public String getRemark() {
        return Remark;
    }
    public void setRemark(String remark) {
        Remark = remark;
    }
 
    @Override
    public String toString() {
        return "Category [Id=" + Id + ", Name=" + Name + ", Remark=" + Remark + ", productList=" + productList + "]";
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
// 一对一 使用resultType映射结果集
    // @Test
    public void testSelectProduct() {
 
        // 获取mapper接口的代理对象
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        ProductDetailInfo detailInfo = unitMapper.oneToOneTest(1);
 
        System.out.println(detailInfo);
 
        // 关闭会话
        session.close();
    }
 
    // 一对一 使用resultMap映射结果集
    // @Test
    public void testSelectProduct2() {
 
        // 获取mapper接口的代理对象
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        Product product = unitMapper.oneToOneTestMap(2);
 
        System.out.println(product);
 
        System.out.println(product.getCategory().toString());
 
        // 关闭会话
        session.close();
    }
 
//嵌套查询
    //一对一
 
    //@Test
    public void testSelectProductTest() {
 
        // 获取mapper接口的代理对象
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        Product product = unitMapper.oneToOneTestAssociationSelect(2);
 
        System.out.println(product);
 
        System.out.println(product.getCategory().toString());
 
        // 关闭会话
        session.close();
    }

一对多查询

将上面一对一的示例倒过来看,一种类别下有多个商品,所以category->products是一对多的关系

mybatis中可以通过使用resultMap的collection标签将关联查询的多条记录映射到一个list集合属性中。

1.嵌套结果实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- 一对多映射 -->
    <select id="oneToManyTest" resultMap="categoryInfo">
        select c.id cid,c.`name`
        cname,c.remark, p.*
        from category c
        join products p
        on p.categoryId= c.Id
        where c.id= #{cid}
    </select>
    <resultMap type="com.sl.po.Category" id="categoryInfo">
        <id column="cid" property="id" />
        <result column="cname" property="name" />
        <result column="remark" property="remark" />
        <!-- collection标签,一对多映射,关联当前分类下产品信息   property映射集合结果,ofType结果集类型 -->
        <collection property="productList" ofType="com.sl.po.Product">
            <id property="id" column="id" javaType="int" jdbcType="INTEGER" />
            <result column="name" property="Name" />
            <result column="description" property="Description" />
            <result column="unitprice" property="UnitPrice" />
            <result column="imageUrl" property="ImageUrl" />
            <result column="isnew" property="IsNew" />
            <result column="citycode" property="cityCode" />
        </collection>
    </resultMap>

2.嵌套查询实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<!-- 集合嵌套查询 -->
    <select id="oneToManyTestCollectionSelect" resultMap="categoryInfo2">
        select *
        from category c
        where c.id= #{id}
    </select>
    <resultMap id="categoryInfo2" type="com.sl.po.Category">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="remark" property="remark" />
        <!--collection 映射一对多结果集    column:传递嵌套查询参数    select:嵌套查询id-->
        <collection property="productList" ofType="com.sl.po.Product"  column="id"  select="selectProductByCategoryId">
        </collection>
    </resultMap>
    <!-- 嵌套子查询 -->
    <select id="selectProductByCategoryId" resultType="com.sl.po.Product">
        select *
        from products 
        where categoryId= #{id}
    </select>

测试代码:

定义Product和Category实体同上,添加Mapper接口

1
2
3
4
5
6
7
8
9
10
public interface UnitMapper {
 
    Category oneToManyTest(int cId);
 
    Product oneToOneTestAssociationSelect(int id);
 
        //嵌套查询中的子查询也需要定义接口
    Category selectCategoryInfo(int id);
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//一对多
@Test
    public void oneToManyTest() {
 
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        Category catrgoryInfo = unitMapper.oneToManyTest(1);
 
        System.out.println(catrgoryInfo);
 
        if (catrgoryInfo.getProductList().size() > 0) {
            for (Product pro : catrgoryInfo.getProductList()) {
                System.out.println(pro);
            }
        }
 
        // 关闭会话
        session.close();
 
    }
 
//嵌套查询 一对多
@Test
    public void testoneToManyTestCollectionSelect() {
 
        // 获取mapper接口的代理对象
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        Category category = unitMapper.oneToManyTestCollectionSelect(1);
 
        System.out.println(category);
 
        if (category.getProductList().size() > 0) {
            for (Product pro : category.getProductList()) {
                System.out.println(pro);
            }
        }
        // 关闭会话
        session.close();
    }

多对多查询

示例:一个订单包含多个商品,一个商品也可以对应多个订单,这个示例查询稍微扩展一下,增加用户信息,一个用户对应多个订单

实体对象定义:定义User实体,增加orders属性,用于映射当前用户的订单; 定义Order对象,增加orderItems属性,映射当前订单有哪些内容(产品);定义OrderItem实体,增加product属性,映射具体产品信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<!-- 多对多映射 查询用户信息及对应订单信息,订单详情 -->
    <!-- select * from orders o join `user` u on o.userId = u.id join orderItem 
        i on o.Id = i.orderid join products p on i.productid = p.Id -->
    <select id="manyToManyTest" resultMap="userAndOrderInfo">
        select u.*,
        o.id oid,
        o.createtime ocreatetime,
        o.userid ouserid,
        o.amount oamount,
        o.remark
        oremark,
        i.id iid,
        i.orderid iorderid,
        i.productid iproductid,
        i.createtime icreatetime,
        i.number inumber,
        i.price iprice,
 
        p.id pid,
        p.`name` pname,
        p.Description pdescription
 
        from orders o
        join `user` u on
        o.userId = u.id
        join orderItem i on o.Id = i.orderid
        join products p on
        i.productid = p.Id
        where u.id=#{id}
    </select>
    <resultMap type="com.sl.po.User" id="userAndOrderInfo">
        <id column="id" property="id" />
        <result column="sex" property="sex" />
        <result column="birthday" property="birthday" />
        <result column="address" property="address" />
        <result column="username" property="userName" />
        <!-- 映射用户对应的订单信息,一个用户可以有多个订单 -->
        <collection property="orders" ofType="com.sl.po.Order">
            <id column="oid" property="id" />
            <result column="ocreatetime" property="createTime" />
            <result column="ouserid" property="userId" />
            <result column="oamount" property="amount" />
            <result column="oremark" property="remark" />
            <!-- 订单对应的商品信息,一个订单可以有多个商品  -->
            <collection property="orderItems" ofType="com.sl.po.OrderItem">
                <id column="iid" property="id" />
                <result column="iorderid" property="orderId" />
                <result column="iproductid" property="productId" />
                <result column="icreatetime" property="createTime" />
                <result column="inumber" property="number" />
                <result column="iprice" property="price" />
                <!-- 映射商品信息 (OrderItem与商品product 一一对应)-->
                <association property="product" javaType="com.sl.po.Product">
                    <id column="pid" property="Id" />
                    <result column="pname" property="Name" />
                    <result column="pdescription" property="Description" />
                </association>
            </collection>
        </collection>
    </resultMap>

测试代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.sl.po;
 
import java.sql.Date;
import java.util.List;
 
public class User {
 
    private int id;
    private String sex;
    private Date birthday;
    private String address;
    private String userName;
 
    //映射当前用户订单列表
    private List<Order> orders;
 
 
    public List<Order> getOrders() {
        return orders;
    }
    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
 
    @Override
    public String toString() {
        return "User [id=" + id + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + ", userName="
                + userName + ", orders=" + orders + "]";
    }
 
 
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.sl.po;
 
import java.sql.Date;
import java.util.List;
 
public class Order {
 
    private int id;
    private int userId;
    private Date createTime;
    private int amount;
    private String remark;
 
    private User user;
    //映射订单内容(产品信息)
    private List<OrderItem> orderItems;
 
 
 
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public int getAmount() {
        return amount;
    }
    public void setAmount(int amount) {
        this.amount = amount;
    }
    public String getRemark() {
        return remark;
    }
    public void setRemark(String remark) {
        this.remark = remark;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    public List<OrderItem> getOrderItems() {
        return orderItems;
    }
    public void setOrderItems(List<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package com.sl.po;
 
public class OrderItem {
 
    private int id;
    private int orderId;
    private int productId;
    private int createTime;
    private int number;
    private int price;
 
    //订单项对应的具体产品
    private Product product;
 
 
    public Product getProduct() {
        return product;
    }
 
    public void setProduct(Product product) {
        this.product = product;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public int getOrderId() {
        return orderId;
    }
 
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
 
    public int getProductId() {
        return productId;
    }
 
    public void setProductId(int productId) {
        this.productId = productId;
    }
 
    public int getCreateTime() {
        return createTime;
    }
 
    public void setCreateTime(int createTime) {
        this.createTime = createTime;
    }
 
    public int getNumber() {
        return number;
    }
 
    public void setNumber(int number) {
        this.number = number;
    }
 
    public int getPrice() {
        return price;
    }
 
    public void setPrice(int price) {
        this.price = price;
    }
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
package com.sl.mapper;
 
import java.util.List;
 
import com.sl.po.Category;
import com.sl.po.Product;
import com.sl.po.ProductDetailInfo;
import com.sl.po.User;
 
public interface UnitMapper {
 
    User manyToManyTest(int id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
//多对多
    @Test
    public void manyToManyTest() {
 
        UnitMapper unitMapper = session.getMapper(UnitMapper.class);
 
        User userOrder = unitMapper.manyToManyTest(1);
 
        System.out.println(userOrder);
 
        // 关闭会话
        session.close();
 
    }

标签即属性说明

Association标签: 作用是可以将关联查询信息映射到一个pojo对象中

collection标签: 作用是可以将关联查询信息映射到一个集合中

Association和collection标签常用到的属性:

Property属性: 指定当前association标签内容映射到pojo对象中哪个属性。

javaType:映射属性的类型

typeHandler:类型处理器,使用这个属性,你可以覆盖默认的 typeHandler 类型处理器。 这个属性值是类的完全限定名或者是一个类型处理器的实现, 或者是类型别名

column:sql结果集列名,用在嵌套查询时传递参数,要 处 理 复 合 主 键 , 你 可 以 指 定 多 个 列 名 通 过 column= ” {prop1=col1,prop2=col2} ” 这种语法来传递给嵌套查询语 句。prop1 和 prop2 以参数对象形式来设置给目标嵌套查询语句。

select:嵌套查询映射语句的ID

fetchType:可选的。有效值为 lazy和eager。 如果使用了,它将取代全局配置参数lazyLoadingEnabled

columnPrefix:将含有指定前缀的结果集映射到当前标签下 例如:<association property="productInfo" columnPrefix="p_" /> 将结果集中以“p_”开头的列映射到productInfo属性上

 

原文链接:http://www.cnblogs.com/ashleyboy/p/9281837.html

发表评论