【Mybatis】--高级查询之 一对一、一对多、多对多查询基于注解的实现完整案例

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
99
100
101
102
103
104
105
106
107
/*
Navicat MySQL Data Transfer

Source Server         : sougu
Source Server Version : 50545
Source Host           : localhost:3306
Source Database       : mybatis

Target Server Type    : MYSQL
Target Server Version : 50545
File Encoding         : 65001

Date: 2020-10-14 19:59:07
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(32) DEFAULT NULL,
  `item_price` float(6,1) DEFAULT NULL,
  `item_detail` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000004 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES ('1000001', '华为手机', '5000.0', '华为P40');
INSERT INTO `tb_item` VALUES ('1000002', '小米', '5300.0', '小米10至尊版');
INSERT INTO `tb_item` VALUES ('1000003', '一加', '4000.0', '一+8');

-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `order_number` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pkuid` (`user_id`),
  CONSTRAINT `pkuid` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES ('1001', '1001', '10000234');
INSERT INTO `tb_order` VALUES ('1002', '1002', '10000345');

-- ----------------------------
-- Table structure for tb_orderdetail
-- ----------------------------
DROP TABLE IF EXISTS `tb_orderdetail`;
CREATE TABLE `tb_orderdetail` (
  `id` int(11) NOT NULL,
  `order_id` int(32) DEFAULT NULL,
  `item_id` int(32) DEFAULT NULL,
  `total_price` double(20,1) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_orderid` (`order_id`),
  KEY `fk_itemid` (`item_id`),
  CONSTRAINT `fk_itemid` FOREIGN KEY (`item_id`) REFERENCES `tb_item` (`id`),
  CONSTRAINT `fk_orderid` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_orderdetail
-- ----------------------------
INSERT INTO `tb_orderdetail` VALUES ('1004', '1002', '1000003', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10001', '1001', '1000001', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10002', '1001', '1000002', null, '1');
INSERT INTO `tb_orderdetail` VALUES ('10003', '1002', '1000002', null, '1');

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('1001', 'cxx', 'cru123', '陈小小', '22', '男', '1998-03-16 11:33:14', '2020-10-15 11:33:43', '2020-10-16 19:52:11');
INSERT INTO `tb_user` VALUES ('1002', 'cbb', 'cjh234', '陈宝宝', '23', '男', '1997-07-18 11:34:40', '2020-10-15 11:34:54', '2020-10-16 19:52:18');

-- ----------------------------

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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.hisoft</groupId>
    <artifactId>mybatisdemo1</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <!-- 单元测试依赖 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- 日志依赖-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.29</version>
        </dependency>

    </dependencies>
    <!--    控制配置maven构建项目的参数设置，设置jdk的版本-->
    <build>
        <!--        配置插件-->
        <plugins>
            <!--            配置具体的插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <!--                插件名字-->
                <artifactId>maven-compiler-plugin</artifactId>
                <!--                插件版本-->
                <version>3.8.1</version>
                <!--                配置插件的信息-->
                <configuration>
                    <!--                    告诉maven我们写的项目是在jdk1.8上编译的-->
                    <source>1.8</source>
                    <!--                    告诉maven我们的程序应该运行在1.8的jdk上-->
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

1
2
3
4
5
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:/ / /suogu
username=root
password=root

1
2
3
4
5
6
log4j.rootLogger=DEBUG,A1
log4j.logger.org.mybatis=DEBUG
log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%-d{yyyy-MM-dd HH:mm:ss,SSS} [%t] [%c]-[%p] %m%n

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
<?xml version="1.0" encoding="UTF-8" ?>
<!--Mybatis约束信息-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--Mybatis的主配置文件-->
<configuration>
    <!-- 导入数据源-->
    <properties resource="jdbc.properties"></properties>
    <!--开启驼峰匹配-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql"/>
            <!-- 该参数默认为false -->
            <!-- 设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用 -->
            <!-- 和startPage中的pageNum效果一样-->
            <property name="offsetAsPageNum" value="true"/>
            <!-- 该参数默认为false -->
            <!-- 设置为true时，使用RowBounds分页会进行count查询 -->
            <property name="rowBoundsWithCount" value="true"/>
            <!-- 设置为true时，如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->
            <!-- （相当于没有执行分页查询，但是返回结果仍然是Page类型）-->
            <property name="pageSizeZero" value="true"/>
            <!-- 3.3.0版本可用 - 分页参数合理化，默认false禁用 -->
            <!-- 启用合理化时，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页 -->
            <!-- 禁用合理化时，如果pageNum<1或pageNum>pages会返回空数据 -->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>
    <!--配置环境，名字可以任意起-->
    <environments default="development">
        <!--配置development环境,id的值与上方default的值一样-->
        <environment id="development">
            <!--配置事务类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!--            配置数据源（连接池）-->
            <dataSource type="POOLED">
                <!--                配置连接数据库的四个基本信息-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
     <!--    引入映射配置文件-->
    <mappers>
         <package name="com.hisoft.dao"/>
     </mappers>
</configuration>

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
package com.hisoft.pojo;

import java.util.Date;

public class TbUser {
    private int id;
    private String username;
    private String password;
    private String name;
    private int age;
    private String sex;
    private Date birthday;
    private Date created;
    private Date updated;

    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 getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    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 Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    public Date getUpdated() {
        return updated;
    }

    public void setUpdated(Date updated) {
        this.updated = updated;
    }

    public TbUser() {
    }

    public TbUser(int id, String username, String password, String name, int age, String sex, Date birthday, Date cerated, Date updated) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.birthday = birthday;
        this.created = created;
        this.updated = updated;

    }

    @Override
    public String toString() {
        return "TbUser{" +
                "id=" + id +
                ", username='" + username + ''' +
                ", password='" + password + ''' +
                ", name='" + name + ''' +
                ", age=" + age +
                ", sex='" + sex + ''' +
                ", birthday=" + birthday +
                ", created=" + created +
                ", updated=" + updated +
                '}';
    }
}

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
package com.hisoft.pojo;

import java.util.List;

//订单
public class Order {
    private int id;
    private Long userId;
    private String orderNumber;
    private TbUser tbuser;
    private List<OrderDetail> orderDetails;

    public List<OrderDetail> getOrderDetails() {
        return orderDetails;
    }

    public void setOrderDetails(List<OrderDetail> orderDetails) {
        this.orderDetails = orderDetails;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getOrderNumber() {
        return orderNumber;
    }

    public void setOrderNumber(String orderNumber) {
        this.orderNumber = orderNumber;
    }

    public TbUser getTbuser() {
        return tbuser;
    }

    public void setTbuser(TbUser tbuser) {
        this.tbuser = tbuser;
    }

    public Order() {
    }

    public Order(int id, Long userId, String orderNumber, TbUser tbuser, List<OrderDetail> orderDetails) {
        this.id = id;
        this.userId = userId;
        this.orderNumber = orderNumber;
        this.tbuser = tbuser;
        this.orderDetails = orderDetails;
    }

    @Override
    public String toString() {
        return "Order{" +
                "id=" + id +
                ", userId=" + userId +
                ", orderNumber='" + orderNumber + ''' +
                ", tbuser=" + tbuser +
                ", orderDetails=" + orderDetails +
                '}';
    }
}

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
package com.hisoft.pojo;

import java.io.Serializable;
//订单明细--需要实现序列化接口
public class OrderDetail implements Serializable {
    private int id;
    private int orderID;
    private int itemId;
    private double totalPrice;
    private int status;
    private Item item;

    public Item getItem() {
        return item;
    }

    public void setItem(Item item) {
        this.item = item;
    }

    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 getItemId() {
        return itemId;
    }

    public void setItemId(int itemId) {
        this.itemId = itemId;
    }

    public double getTotalPrice() {
        return totalPrice;
    }

    public void setTotalPrice(double totalPrice) {
        this.totalPrice = totalPrice;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public OrderDetail() {
    }

    public OrderDetail(int id, int orderID, int itemId, double totalPrice, int status, Item item) {
        this.id = id;
        this.orderID = orderID;
        this.itemId = itemId;
        this.totalPrice = totalPrice;
        this.status = status;
        this.item = item;
    }

    @Override
    public String toString() {
        return "OrderDetail{" +
                "id=" + id +
                ", orderID=" + orderID +
                ", itemId=" + itemId +
                ", totalPrice=" + totalPrice +
                ", status=" + status +
                ", item=" + item +
                '}';
    }
}

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
package com.hisoft.pojo;

import java.io.Serializable;
//商品详情--需要实现序列化接口
public class Item implements Serializable {
    private int id;
    private String itemName;
    private double itemPrice;
    private  String itemDetail;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public double getItemPrice() {
        return itemPrice;
    }

    public void setItemPrice(double itemPrice) {
        this.itemPrice = itemPrice;
    }

    public String getItemDetail() {
        return itemDetail;
    }

    public void setItemDetail(String itemDetail) {
        this.itemDetail = itemDetail;
    }

    public Item() {
    }

    public Item(int id, String itemName, double itemPrice, String itemDetail) {
        this.id = id;
        this.itemName = itemName;
        this.itemPrice = itemPrice;
        this.itemDetail = itemDetail;
    }

    @Override
    public String toString() {
        return "Item{" +
                "id=" + id +
                ", itemName='" + itemName + ''' +
                ", itemPrice=" + itemPrice +
                ", itemDetail='" + itemDetail + ''' +
                '}';
    }
}

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
package com.hisoft.dao;

import com.hisoft.pojo.Order;
import com.hisoft.pojo.OrderDetail;
import com.hisoft.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface UserMapper {
    @Select("select * from user")
    //    查询所有用户
    public List<User> selectAll();
//高级查询之一对一映射查询：查询订单，并且查询出下单人的信息

    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "order_id",property = "orderID"),
            @Result(column = "item_id",property = "itemId"),
            @Result(column = "item_id",property = "item",one = @One(select = "com.hisoft.dao.ItemMapper.findItemById"))
    } )
    @Select("select * from tb_orderdetail where id = #{id}")
    public OrderDetail findOrderDetailById(@Param("id") int id);
    //多对多注解查询：查询订单，查询出下单人信息并且查询出订单详情
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "order_number",property = "orderNumber"),
            @Result(column = "id",property = "orderDetails",
                    many = @Many(select = "com.hisoft.dao.OrderDetailMapper.findOrderDetailById"))
    } )
    @Select("select * from tb_order where id = #{orderNumber}")
    public Order queryOrderAndDetailsByOrderNumber(@Param("orderNumber") int orderNumber);

}

1
2
3
4
5
6
7
8
9
10
11
12
package com.hisoft.dao;

import com.hisoft.pojo.Item;
import org.apache.ibatis.annotations.Select;

public interface ItemMapper {
    //根据订单详情id查询商品详情
    @Select("select * from tb_item where id = #{id}")
    public Item findItemById(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
package com.hisoft.dao;

import com.hisoft.pojo.OrderDetail;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderDetailMapper {
    //根据订单id查询订单详情和商品详情
    @Results({
            @Result(column = "id",property = "id"),
            @Result(column = "order_id",property = "orderID"),
            //没写一对多的实现，把下面@Result这个去掉即是一对多
            @Result(column = "item_id",property = "item",
                    one = @One(select = "com.hisoft.dao.ItemMapper.findItemById"))
    })
    @Select("select * from tb_orderdetail where order_id = #{orderid}")
    public List<OrderDetail> findOrderDetailById(int orderid);
}

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
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.hisoft.dao.UserMapper;
import com.hisoft.pojo.Order;
import com.hisoft.pojo.OrderDetail;
import com.hisoft.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.apache.ibatis.io.Resources;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class UserTest {
    //new 一个userDao1对象
    private UserMapper userMapper;
    private SqlSession sqlSession;//定义成全局的对象
    private InputStream in;

    @Before
    public void init() throws IOException {
        //读取配置文件
        String resource = "mybatis-config.xml";
        in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = ssfb.build(in);
        //生产sqlSession对象
        sqlSession = sqlSessionFactory.openSession();
        //使用sqlSession创建dao接口的代理对象
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    //查询所有用户信息
    public void selectAll() {
        //设置分页参数，第一个参数：第几页，第二个参数：每页多少条数据
        PageHelper.startPage(2,2);
        List<User> users = userMapper.selectAll();
        for (User user : users) {
            System.out.println(user);
        }
        //可以通过Pageinfo获取分页的信息
        PageInfo<User> pageInfo = new PageInfo<User>(users);
        System.out.println("总页数："+pageInfo.getPages());
        System.out.println("当前页："+pageInfo.getPageNum());
        System.out.println("总记录数："+pageInfo.getTotal());
    }
    
    //**************************************************
    //高级查询之一对一查询 映射；订单详情查商品
    @Test
    public void findItemById() {
        OrderDetail orderDetail = userMapper.findOrderDetailById(1004);
        System.out.println(orderDetail);
    }
    //映射多对多：订单查订单详情
    @Test
    public void queryOrderAndDetails(){
       Order order = userMapper.queryOrderAndDetailsByOrderNumber(1001);
        System.out.println(order);
    }
    @After
    public void destroy() throws IOException {
//        提交事务
        sqlSession.commit();//不开启事务是写入不到数据库里去的
        //释放资源
        sqlSession.close();
        in.close();
    }
}