SSM_多表查询小案例

环境搭建

• 跳转链接
  https://blog.csdn.net/qq_44509920/article/details/107738382

• pom

<properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>

        <!-- 热部署模块 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <!-- 这个需要为 true -->
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

• application.properties

spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Hongkong
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#开启驼峰命名规则
mybatis.configuration.map-underscore-to-camel-case=true

#mapper接口开启debug
logging.level.com.czxy.ssm.mapper=debug

#加载映射文件
mybatis.mapper-locations=classpath:mapper/*.xml

• 多表查询所需要创建的表
  

多对一查询

• 需求：展示所有订单信息，以及订单所属客户姓名手机号
  

对应orders、user表

CREATE TABLE `orders` (
  `order_id` int NOT NULL COMMENT '订单编号',
  `user_id` int NOT NULL COMMENT '下单用户id',
  `order_price` double NOT NULL COMMENT '订单金额',
  `payment` varchar(20) DEFAULT NULL COMMENT '支付方式',
  `state` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '订单状态',
  `order_createtime` date DEFAULT NULL COMMENT '下单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`order_id`),
  KEY `FK_orders_1` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `orders` */

insert  into `orders`(`order_id`,`user_id`,`order_price`,`payment`,`state`,`order_createtime`,`note`) values (10000563,6,100,'会员','已支付','0202-03-21','汽车精洗'),(10000564,8,600,'支付宝','已支付','2020-03-26','土豪客户'),(10000565,6,320,'会员','已支付','2020-03-29','普通保养'),(10000566,10,80,'微信','已退单','2020-03-29','预约洗车，取消订单');

CREATE TABLE `user` (
  `uid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `phone` varchar(15) DEFAULT NULL COMMENT '手机号',
  `sex` char(2) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`uid`,`username`,`birthday`,`phone`,`sex`,`address`) values (1,'王五','1996-12-25',NULL,NULL,NULL),(2,'小花','1998-06-02','13659596872','2','江苏沭阳'),(6,'张小明','1996-01-06','15263671526','2','江苏南京'),(8,'张三丰','1998-12-25','15966633888','1','北京朝阳'),(10,'张三','1998-10-16','15777778696','1','北京海淀'),(12,'刘皇叔','2000-03-22','17888888888','1','江苏连云港');

多个订单对应一个用户，一个用户有多个订单

实体类

• Orders类

package com.czxy.ssm.model;

import org.springframework.format.annotation.DateTimeFormat;

import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.util.Date;

@Table(name = "orders")
public class Orders {
   

    @Id  //一个类只有一个主键Id
    private Long orderId;    //订单ID
    private Integer userId;     //用户ID
    private Double orderPrice;  //订单金额
    private String payment;     //支付方式
    private String state;          //订单状态
    private Date orderCreatetime; //下单时间
    private String note;        //备注

    private User user;          //一个订单只对应一个用户


    @Transient//Transient:不关联
    private String phone;//手机号

    @Transient
    @DateTimeFormat(pattern = "yyyy-MM-dd")//日期类型转换
    private Date startTime;//开始时间

    @Transient
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date endTime;//结束时间

    //全部get set   @Transient的不需要tostring
    
    @Override
    public String toString() {
   
        return "Orders{" +
                "orderId=" + orderId +
                ", userId=" + userId +
                ", orderPrice=" + orderPrice +
                ", payment='" + payment + '\'' +
                ", state='" + state + '\'' +
                ", orderCreatetime=" + orderCreatetime +
                ", note='" + note + '\'' +
                '}';
    }
}

• User类

package com.czxy.ssm.model;

import javax.persistence.Id;
import javax.persistence.Table;
import java.util.ArrayList