SSM_多表查询小案例

案例"/>

SSM_多表查询小案例

文章目录

• 环境搭建
• • 多对一查询
  • 一对多查询
  • 多对多查询
  • 总结

环境搭建

• 跳转链接
  

• 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  //一个类只有一个主键Idprivate Long orderId;    //订单IDprivate Integer userId;     //用户IDprivate 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@Overridepublic 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