环境搭建
<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