首页 > 代码库 > MyBatis高级映射查询(3)

MyBatis高级映射查询(3)

2024-11-02 19:59:02   204人阅读

一、数据库数据和项目搭建过程

1、主要要四张表，分别为user用户信息表、items商品表、orderdetail订单明细表、orders订单表。表的结构和数据如下：

表结构

技术分享 
CREATE DATABASE mybatis DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL COMMENT ‘商品名称‘,
  `price` FLOAT(10,1) NOT NULL COMMENT ‘商品定价‘,
  `detail` TEXT COMMENT ‘商品描述‘,
  `pic` VARCHAR(64) DEFAULT NULL COMMENT ‘商品图片‘,
  `createtime` DATETIME NOT NULL COMMENT ‘生产日期‘,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `orderdetail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `orders_id` INT(11) NOT NULL COMMENT ‘订单id‘,
  `items_id` INT(11) NOT NULL COMMENT ‘商品id‘,
  `items_num` INT(11) DEFAULT NULL COMMENT ‘商品购买数量‘,
  PRIMARY KEY (`id`),
  KEY `FK_orderdetail_1` (`orders_id`),
  KEY `FK_orderdetail_2` (`items_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL COMMENT ‘下单用户id‘,
  `number` VARCHAR(32) NOT NULL COMMENT ‘订单号‘,
  `createtime` DATETIME NOT NULL COMMENT ‘创建订单时间‘,
  `note` VARCHAR(100) DEFAULT NULL COMMENT ‘备注‘,
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL COMMENT ‘用户名称‘,
  `birthday` DATE DEFAULT NULL COMMENT ‘生日‘,
  `sex` CHAR(1) DEFAULT NULL COMMENT ‘性别‘,
  `address` VARCHAR(256) DEFAULT NULL COMMENT ‘地址‘,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
View Code

表数据

技术分享 
INSERT  INTO `items`(`id`,`name`,`price`,`detail`,`pic`,`createtime`) VALUES (1,‘台式机‘,3000.0,‘该电脑质量非常好！！！！‘,NULL,‘2015-02-03 13:22:53‘),(2,‘笔记本‘,6000.0,‘笔记本性能好，质量好！！！！！‘,NULL,‘2015-02-09 13:22:57‘),(3,‘背包‘,200.0,‘名牌背包，容量大质量好！！！！‘,NULL,‘2015-02-06 13:23:02‘);

INSERT  INTO `orderdetail`(`id`,`orders_id`,`items_id`,`items_num`) VALUES (1,3,1,1),(2,3,2,3),(3,4,3,4),(4,4,2,3);

INSERT  INTO `orders`(`id`,`user_id`,`number`,`createtime`,`note`) VALUES (3,1,‘1000010‘,‘2015-02-04 13:22:35‘,NULL),(4,1,‘1000011‘,‘2015-02-03 13:22:41‘,NULL),(5,10,‘1000012‘,‘2015-02-12 16:13:23‘,NULL);

INSERT  INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES (1,‘王五‘,NULL,‘2‘,NULL),(10,‘张三‘,‘2014-07-10‘,‘1‘,‘北京市‘),(16,‘张小明‘,NULL,‘1‘,‘河南郑州‘),(22,‘陈小明‘,NULL,‘1‘,‘河南郑州‘),(24,‘张三丰‘,NULL,‘1‘,‘河南郑州‘),(25,‘陈小明‘,NULL,‘1‘,‘河南郑州‘),(26,‘王五‘,NULL,NULL,NULL);
View Code

2、整个项目的目录结构

技术分享

3、首先创建mybatis配置文件SqlMapConfig.xml

技术分享 
 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6 <!-- 加载属性文件 -->
 7     <properties resource="db.properties">
 8         
 9     </properties>
10     
11     <!-- 和spring整合后 environments配置将废除-->
12     <environments default="development">
13         <environment id="development">
14         <!-- 使用jdbc事务管理，事务控制由mybatis-->
15             <transactionManager type="JDBC" />
16         <!-- 数据库连接池，由mybatis管理-->
17             <dataSource type="POOLED">
18                 <property name="driver" value="${jdbc.driver}" />
19                 <property name="url" value="${jdbc.url}" />
20                 <property name="username" value="${jdbc.username}" />
21                 <property name="password" value="${jdbc.password}" />
22             </dataSource>
23         </environment>
24     </environments>
25 
26     <mappers>
27         <mapper resource="sqlmap/Orders.xml" />
28     </mappers>
29 </configuration>
View Code

4、创建数据库配置文件db.properties

技术分享 
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
jdbc.username=root
jdbc.password=123
View Code

5、创建映射文件orders.xml文件

技术分享 
  1 <?xml version="1.0" encoding="UTF-8" ?>
  2 <!DOCTYPE mapper
  3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5 
  6 <mapper namespace="com.zhang.dao.OrdersDao">
  7 
  8     <select id="findOrdersUser"  resultType="com.zhang.domain.OrdersCustom">
  9         SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
 10         orders.user_id = user.id
 11     </select>
 12     
 13       <resultMap type="com.zhang.domain.Orders" id="OrdersUserResultMap">
 14         <id column="id" property="id"/>
 15         <result column="user_id" property="userId"/>
 16         <result column="number" property="number"/>
 17         <result column="createtime" property="createtime"/>
 18         <result column="note" property="note"/>
 19         
 20         <association property="user"  javaType="com.zhang.domain.User">
 21             <id column="user_id" property="id"/>
 22             <result column="username" property="username"/>
 23             <result column="sex" property="sex"/>
 24             <result column="address" property="address"/>
 25         </association>
 26     </resultMap>
 27     <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
 28         SELECT orders.*, USER.username,USER.sex,USER.address FROM orders,USER WHERE
 29         orders.user_id = user.id
 30     </select>
 31     
 32     <resultMap type="com.zhang.domain.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap" >
 33         
 34          <collection property="orderdetails" ofType="com.zhang.domain.Orderdetail">
 35              <id column="orderdetail_id" property="id"/>
 36              <result column="items_id" property="itemsId"/>
 37              <result column="items_num" property="itemsNum"/>
 38              <result column="orders_id" property="ordersId"/>
 39          </collection>
 40     </resultMap>
 41     
 42     <!-- 查询订单关联查询用户及订单明细，使用resultmap -->
 43     <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
 44         SELECT 
 45           orders.*,
 46           USER.username,
 47           USER.sex,
 48           USER.address,
 49           orderdetail.id orderdetail_id,
 50           orderdetail.items_id,
 51           orderdetail.items_num,
 52           orderdetail.orders_id
 53         FROM
 54           orders,
 55           USER,
 56           orderdetail
 57         WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
 58     </select>
 59     
 60     <!-- 查询用户及购买的商品 -->
 61     <resultMap type="com.zhang.domain.User" id="UserAndItemsResultMap">
 62         <!-- 用户信息 -->
 63         <id column="user_id" property="id"/>
 64         <result column="username" property="username"/>
 65         <result column="sex" property="sex"/>
 66         <result column="address" property="address"/>
 67         <!-- 订单信息一个用户对应多个订单，使用collection映射-->
 68          <collection property="ordersList" ofType="com.zhang.domain.Orders">
 69              <id column="id" property="id"/>
 70              <result column="user_id" property="userId"/>
 71             <result column="number" property="number"/>
 72             <result column="createtime" property="createtime"/>
 73             <result column="note" property="note"/>
 74              <!-- 订单明细 一个订单包括 多个明细-->
 75               <collection property="orderdetails" ofType="com.zhang.domain.Orderdetail">
 76                       <id column="orderdetail_id" property="id"/>
 77                      <result column="items_id" property="itemsId"/>
 78                      <result column="items_num" property="itemsNum"/>
 79                      <result column="orders_id" property="ordersId"/>
 80                      <!-- 商品信息一个订单明细对应一个商品-->
 81                    <association property="items" javaType="com.zhang.domain.Items">
 82                        <id column="items_id" property="id"/>
 83                        <result column="items_name" property="name"/>
 84                        <result column="items_detail" property="detail"/>
 85                        <result column="items_price" property="price"/>
 86                    </association>
 87               </collection>
 88          </collection>
 89     </resultMap>
 90     
 91     <!-- 查询用户及购买的商品信息，使用resultmap -->
 92     <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
 93         SELECT 
 94           orders.*,
 95           USER.username,
 96           USER.sex,
 97           USER.address,
 98           orderdetail.id orderdetail_id,
 99           orderdetail.items_id,
100           orderdetail.items_num,
101           orderdetail.orders_id,
102           items.name items_name,
103           items.detail items_detail,
104           items.price items_price
105         FROM
106           orders,
107           USER,
108           orderdetail,
109           items
110         WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
111     </select>
112 </mapper>
View Code

6、创建接口类OrderDao.java

技术分享 
 1 package com.zhang.dao;
 2 
 3 import java.util.List;
 4 
 5 import com.zhang.domain.Orders;
 6 import com.zhang.domain.OrdersCustom;
 7 import com.zhang.domain.User;
 8 public interface OrdersDao {
 9 
10         //查询订单关联查询用户信息
11         public List<OrdersCustom> findOrdersUser()throws Exception;
12         
13         //查询订单关联查询用户使用resultMap
14         public List<Orders> findOrdersUserResultMap()throws Exception;
15         
16         //查询订单(关联用户)及订单明细
17         public List<Orders>  findOrdersAndOrderDetailResultMap()throws Exception;
18         
19         //查询用户购买商品信息
20         public List<User>  findUserAndItemsResultMap()throws Exception;
21         
22         //查询订单关联查询用户，用户信息是延迟加载
23         public List<Orders> findOrdersUserLazyLoading()throws Exception;
24 }
View Code

7、创建各种实体类user、orders、items、ordersdetail类

user类

技术分享 
 1 package com.zhang.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.Date;
 5 import java.util.List;
 6 
 7 
 8 public class User implements Serializable {
 9     
10     //属性名和数据库表的字段对应
11     private int id;
12     private String username;// 用户姓名
13     private String sex;// 性别
14     private Date birthday;// 生日
15     private String address;// 地址
16     
17     //用户创建的订单列表
18     private List<Orders> ordersList;
19     
20     
21     public int getId() {
22         return id;
23     }
24     public void setId(int id) {
25         this.id = id;
26     }
27     public String getUsername() {
28         return username;
29     }
30     public void setUsername(String username) {
31         this.username = username;
32     }
33     public String getSex() {
34         return sex;
35     }
36     public void setSex(String sex) {
37         this.sex = sex;
38     }
39     public Date getBirthday() {
40         return birthday;
41     }
42     public void setBirthday(Date birthday) {
43         this.birthday = birthday;
44     }
45     public String getAddress() {
46         return address;
47     }
48     public void setAddress(String address) {
49         this.address = address;
50     }
51     @Override
52     public String toString() {
53         return "User [id=" + id + ", username=" + username + ", sex=" + sex
54                 + ", birthday=" + birthday + ", address=" + address + "]";
55     }
56     public List<Orders> getOrdersList() {
57         return ordersList;
58     }
59     public void setOrdersList(List<Orders> ordersList) {
60         this.ordersList = ordersList;
61     }
62 
63 
64 }
View Code

orders类

技术分享 
 1 package com.zhang.domain;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class Orders {
 7     private Integer id;
 8 
 9     private Integer userId;
10 
11     private String number;
12 
13     private Date createtime;
14 
15     private String note;
16     
17     //用户信息
18     private User user;
19     
20     //订单明细
21     private List<Orderdetail> orderdetails;
22 
23     public Integer getId() {
24         return id;
25     }
26 
27     public void setId(Integer id) {
28         this.id = id;
29     }
30 
31     public Integer getUserId() {
32         return userId;
33     }
34 
35     public void setUserId(Integer userId) {
36         this.userId = userId;
37     }
38 
39     public String getNumber() {
40         return number;
41     }
42 
43     public void setNumber(String number) {
44         this.number = number == null ? null : number.trim();
45     }
46 
47     public Date getCreatetime() {
48         return createtime;
49     }
50 
51     public void setCreatetime(Date createtime) {
52         this.createtime = createtime;
53     }
54 
55     public String getNote() {
56         return note;
57     }
58 
59     public void setNote(String note) {
60         this.note = note == null ? null : note.trim();
61     }
62 
63     public User getUser() {
64         return user;
65     }
66 
67     public void setUser(User user) {
68         this.user = user;
69     }
70 
71     public List<Orderdetail> getOrderdetails() {
72         return orderdetails;
73     }
74 
75     public void setOrderdetails(List<Orderdetail> orderdetails) {
76         this.orderdetails = orderdetails;
77     }
78 
79     
80     
81     
82 }
View Codel

items类

技术分享 
 1 package com.zhang.domain;
 2 
 3 import java.util.Date;
 4 
 5 public class Items {
 6     private Integer id;
 7 
 8     private String name;
 9 
10     private Float price;
11 
12     private String pic;
13 
14     private Date createtime;
15 
16     private String detail;
17 
18     public Integer getId() {
19         return id;
20     }
21 
22     public void setId(Integer id) {
23         this.id = id;
24     }
25 
26     public String getName() {
27         return name;
28     }
29 
30     public void setName(String name) {
31         this.name = name == null ? null : name.trim();
32     }
33 
34     public Float getPrice() {
35         return price;
36     }
37 
38     public void setPrice(Float price) {
39         this.price = price;
40     }
41 
42     public String getPic() {
43         return pic;
44     }
45 
46     public void setPic(String pic) {
47         this.pic = pic == null ? null : pic.trim();
48     }
49 
50     public Date getCreatetime() {
51         return createtime;
52     }
53 
54     public void setCreatetime(Date createtime) {
55         this.createtime = createtime;
56     }
57 
58     public String getDetail() {
59         return detail;
60     }
61 
62     public void setDetail(String detail) {
63         this.detail = detail == null ? null : detail.trim();
64     }
65 
66     @Override
67     public String toString() {
68         return "Items [id=" + id + ", name=" + name + ", price=" + price
69                 + ", pic=" + pic + ", createtime=" + createtime + ", detail="
70                 + detail + "]";
71     }
72     
73 }
View Code

orderdetail类

技术分享 
 1 package com.zhang.domain;
 2 
 3 public class Orderdetail {
 4     private Integer id;
 5 
 6     private Integer ordersId;
 7 
 8     private Integer itemsId;
 9 
10     private Integer itemsNum;
11     
12     //明细对应的商品信息
13     private Items items;
14     
15 
16     public Integer getId() {
17         return id;
18     }
19 
20     public void setId(Integer id) {
21         this.id = id;
22     }
23 
24     public Integer getOrdersId() {
25         return ordersId;
26     }
27 
28     public void setOrdersId(Integer ordersId) {
29         this.ordersId = ordersId;
30     }
31 
32     public Integer getItemsId() {
33         return itemsId;
34     }
35 
36     public void setItemsId(Integer itemsId) {
37         this.itemsId = itemsId;
38     }
39 
40     public Integer getItemsNum() {
41         return itemsNum;
42     }
43 
44     public void setItemsNum(Integer itemsNum) {
45         this.itemsNum = itemsNum;
46     }
47 
48     public Items getItems() {
49         return items;
50     }
51 
52     public void setItems(Items items) {
53         this.items = items;
54     }
55 
56     @Override
57     public String toString() {
58         return "Orderdetail [id=" + id + ", ordersId=" + ordersId
59                 + ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
60     }
61     
62 }
View Code

8、创建测试类TestMy.java

技术分享 
 1 package com.zhang.test;
 2 
 3 import java.io.InputStream;
 4 import java.util.List;
 5 
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 import org.junit.Before;
11 import org.junit.Test;
12 
13 import com.zhang.dao.OrdersDao;
14 import com.zhang.domain.Orders;
15 import com.zhang.domain.OrdersCustom;
16 import com.zhang.domain.User;
17 
18 public class TestMy {
19     private SqlSessionFactory sqlSessionFactory;
20 
21     // 此方法是在执行testFindUserById之前执行
22     @Before
23     public void setUp() throws Exception {
24         // 创建sqlSessionFactory
25         // mybatis配置文件
26         String resource = "SqlMapConfig.xml";
27         // 得到配置文件流
28         InputStream inputStream = Resources.getResourceAsStream(resource);
29         // 创建会话工厂，传入mybatis的配置文件信息
30         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
31     }
32     
33     
34     //一对一查询利用resultType
35     @Test
36     public void findOrdersUser() throws Exception {
37         SqlSession sqlSession = sqlSessionFactory.openSession();
38         OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
39         List<OrdersCustom> list=ordersDao.findOrdersUser();
40         System.out.println(list.size());
41     }
42     
43     //一对一查询利用resultMap
44     @Test
45     public void findOrdersUserResultMap() throws Exception {
46         SqlSession sqlSession = sqlSessionFactory.openSession();
47         OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
48         List<Orders> list=ordersDao.findOrdersUserResultMap();
49         System.out.println(list.size());
50     }
51     
52     //一对多查询
53     @Test
54     public void findOrdersAndOrderDetailResultMap() throws Exception {
55         SqlSession sqlSession = sqlSessionFactory.openSession();
56         OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
57         List<Orders> list=ordersDao.findOrdersAndOrderDetailResultMap();
58         System.out.println(list.size());
59     }
60     
61     //多对多查询
62     @Test
63     public void findUserAndItemsResultMap() throws Exception {
64         SqlSession sqlSession = sqlSessionFactory.openSession();
65         OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
66         List<User> list=ordersDao.findUserAndItemsResultMap();
67         System.out.println(list.size());
68     }
69     
70 }
View Code

二、一对一高级查询

1.需求:查询订单信息，关联查询创建订单的用户信息
2. 利用 resultType映射查询，创建pojo类继承Orders类
技术分享 
 1 package com.zhang.domain;
 2 
 3 //通过此类映射订单和用户查询的结果，让此类继承包括 字段较多的pojo类
 4 public class OrdersCustom extends Orders{
 5     
 6     //添加用户属性
 7     private String username;
 8     private String sex;
 9     private String address;
10     
11     
12     public String getUsername() {
13         return username;
14     }
15     public void setUsername(String username) {
16         this.username = username;
17     }
18     public String getSex() {
19         return sex;
20     }
21     public void setSex(String sex) {
22         this.sex = sex;
23     }
24     public String getAddress() {
25         return address;
26     }
27     public void setAddress(String address) {
28         this.address = address;
29     }
30     
31     
32 
33 }
View Code

3、编写findOrdersUser接口

技术分享

4、在Orders.xml文件定义SQL映射关系

技术分享

5、测试方法：

技术分享

6、利用resultMap映射查询，在Orders类中添加User属性：

技术分享

7、编写接口方法

技术分享

8、在Orders.xml中添加ResultMap信息：

技术分享

9、测试方法：

 技术分享

三、一对多查询

1.需求：查询订单及订单明细的信息。
2.在Orders类中新增属性。
技术分享

3、编写接口方法

技术分享

4、在order.xml编写resultMap，继承上面的resultMap

技术分享

技术分享

5、测试方法

技术分享

四、多对多查询

1.需求:查询用户及用户购买商品信息。
2.实现思路：
将用户信息映射到user中。
在user类中添加订单列表属性List<Orders> orderslist，将用户创建的订单映射到orderslist

 技术分享

3、在Orders中添加订单明细列表属性List<OrderDetail>orderdetials，将订单的明细映射到orderdetials

 技术分享

4、在OrderDetail中添加Items属性，将订单明细所对应的商品映射到Items

技术分享

5、编写接口方法

技术分享

6、测试方法

技术分享

五、知识点总结

1、resultMap与resultType：

resultType： 将查询结果按照sql列名pojo属性名一致性映射到pojo中。
场合：常见一些明细记录的展示，比如用户购买商品明细，将关联查询信息全部展示在页面时，此时可直接使用resultType将每一条记录映射到pojo中，在前端页面遍历list（list中是pojo）即可。
resultMap：使用association和collection完成一对一和一对多高级映射（对结果有特殊的映射要求）。
2、association
作用： 将关联查询信息映射到一个pojo对象中。（一个对象）
场合：为了方便查询关联信息可以使用association将关联订单信息映射为用户对象的pojo属性中，比如：查询订单及关联用户信息。使用resultType无法将查询结果映射到pojo对象的pojo属性中，根据对结果集查询遍历的需要选择使用resultType还是resultMap。     
3、collection
作用：将关联查询信息映射到一个list集合中。
场合：为了方便查询遍历关联信息可以使用collection将关联信息映射到list集合中，比如：查询用户权限范围模块及模块下的菜单，可使用collection将模块映射到模块list中，将菜单列表映射到模块对象的菜单list属性中，这样的作的目的也是方便对查询结果集进行遍历查询。

 

MyBatis高级映射查询(3)


联系
我们