比赛订单表字段
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import javax.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
/**
* @author lzq
* @version 1.0
* @date 2020/12/15 10:10
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "compation_order")
public class CompationOrder {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY,generator="JDBC")
private Integer joinId;
@NotNull(message = "赛事id不能为空")
private Integer compationId;
private String compationName;
private LocalDateTime competitionStartTime;
private LocalDateTime competitionEndTime;
private Integer status;
private String orderNo;
private LocalDateTime orderTime;
private LocalDateTime payTime;
private BigDecimal payMoney;
private BigDecimal totalFee;
private Integer compationType;
private String proName;
private String teamName;
@NotNull(message = "项目id不能为空")
private Integer proId;
private String groupName;
@NotNull(message = "组别id不能为空")
private Integer groupId;
private Integer sexId;
private String sexName;
private Integer userId;
@ApiModelProperty(value = "选手信息")
private List<UserCompetitionCopy> userList;
}
订单对应的参赛选手和教练表字段
package com.sport.sportadminserver.po.auth;
import lombok.Data;
/**
* @author lzq
* @version 1.0
* @date 2020/12/15 10:10
*/
@Data
public class UserCompetitionCopy {
/**
* 主键id
*/
private Integer id;
/**
* 真实姓名
*/
private String fullName;
/**
* 身份证
*/
private String idCard;
/**
* 性别 0-男 1-女
*/
private Integer competitionSex;
/**
* 教练名称
*/
private String coachFullName;
/**
* 教练身份证
*/
private String coachIdCard;
}
mapper.xml映射和sql
<resultMap id="BaseResultMap" type="com.sport.sportadminserver.po.auth.CompationOrder">
<result column="join_id" jdbcType="INTEGER" property="joinId" />
<result column="compation_id" jdbcType="INTEGER" property="compationId" />
<result column="compation_name" jdbcType="VARCHAR" property="compationName" />
<result column="competition_start_time" jdbcType="TIMESTAMP" property="competitionStartTime" />
<result column="competition_end_time" jdbcType="TIMESTAMP" property="competitionEndTime" />
<result column="status" jdbcType="INTEGER" property="status" />
<result column="order_no" jdbcType="VARCHAR" property="orderNo" />
<result column="order_time" jdbcType="TIMESTAMP" property="orderTime" />
<result column="pay_time" jdbcType="TIMESTAMP" property="payTime" />
<result column="pay_money" jdbcType="DECIMAL" property="payMoney" />
<result column="compation_type" jdbcType="INTEGER" property="compationType" />
<result column="pro_name" jdbcType="VARCHAR" property="proName" />
<result column="team_name" jdbcType="VARCHAR" property="teamName" />
<result column="pro_id" jdbcType="INTEGER" property="proId" />
<result column="group_name" jdbcType="VARCHAR" property="groupName" />
<result column="group_id" jdbcType="INTEGER" property="groupId" />
<result column="sex_id" jdbcType="INTEGER" property="sexId" />
<result column="sex_name" jdbcType="VARCHAR" property="sexName" />
<result column="user_id" jdbcType="INTEGER" property="userId" />
<result column="total_fee" jdbcType="DECIMAL" property="totalFee" />
<!-- property:实体类里对应的集合名称 ofType:用来指定对象类型-->
<collection property="userList" ofType="com.sport.sportadminserver.po.auth.UserCompetitionCopy">
<result property="id" jdbcType="INTEGER" column="id" />
<result column="full_name" jdbcType="VARCHAR" property="fullName" />
<result column="id_card" jdbcType="VARCHAR" property="idCard" />
<result column="coach_id_card" jdbcType="VARCHAR" property="coachIdCard" />
<result column="coach_full_name" jdbcType="VARCHAR" property="coachFullName" />
<result column="competition_sex" jdbcType="INTEGER" property="competitionSex" />
</collection>
</resultMap>
<select id="queryAllOrder" resultMap="BaseResultMap">
SELECT
compation.*, users.id,
users.id_card,
users.full_name,
users.coach_id_card,
users.coach_full_name,
users.competition_sex
FROM
`compation_order` compation
LEFT JOIN user_competition users ON compation.order_no = users.order_no;
</select>
查询结果
{
"code": "200",
"message": "查询成功",
"data": [
{
"joinId": 149,
"compationId": 66,
"compationName": "测试",
"competitionStartTime": "2020-12-26 00:00:00",
"competitionEndTime": "2020-12-27 23:59:59",
"status": 7,
"orderNo": "2020120720120583305677_66",
"orderTime": "2020-12-07 20:12:05",
"payTime": null,
"payMoney": 400,
"totalFee": null,
"compationType": 1,
"proName": "花剑",
"teamName": "1",
"proId": 1,
"groupName": "U8组",
"groupId": 1,
"sexId": 1,
"sexName": "男",
"userId": 11527,
"userList": [
{
"id": 649,
"fullName": null,
"idCard": null,
"competitionSex": null,
"coachFullName": "测试",
"coachIdCard": "330106199005234019"
},
{
"id": 650,
"fullName": null,
"idCard": null,
"competitionSex": null,
"coachFullName": "徐达",
"coachIdCard": "330106199005234019"
},
{
"id": 665,
"fullName": "测试",
"idCard": "110101201201015291",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
},
{
"id": 667,
"fullName": "测试",
"idCard": "110101201201014272",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
},
{
"id": 670,
"fullName": "v",
"idCard": "110101201201013616",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
}
]
},
{
"joinId": 196,
"compationId": 66,
"compationName": "测试",
"competitionStartTime": "2020-12-26 00:00:00",
"competitionEndTime": "2020-12-27 23:59:59",
"status": 3,
"orderNo": "2020120815311751701896_66",
"orderTime": "2020-12-08 15:31:18",
"payTime": "2020-12-08 15:31:26",
"payMoney": 165,
"totalFee": 0.01,
"compationType": 0,
"proName": "花剑",
"teamName": null,
"proId": 1,
"groupName": "U8组",
"groupId": 1,
"sexId": 1,
"sexName": "男",
"userId": 11522,
"userList": [
{
"id": 796,
"fullName": "刘苏霆",
"idCard": "11010120120201563X",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
}
]
},
{
"joinId": 201,
"compationId": 66,
"compationName": "测试",
"competitionStartTime": "2020-12-26 00:00:00",
"competitionEndTime": "2020-12-27 23:59:59",
"status": 7,
"orderNo": "2020120816502221739363_66",
"orderTime": "2020-12-08 16:50:23",
"payTime": null,
"payMoney": 165,
"totalFee": null,
"compationType": 0,
"proName": "花剑",
"teamName": null,
"proId": 1,
"groupName": "U8组",
"groupId": 1,
"sexId": 1,
"sexName": "男",
"userId": 11521,
"userList": [
{
"id": 797,
"fullName": null,
"idCard": null,
"competitionSex": null,
"coachFullName": "刘振奇",
"coachIdCard": "410725199709093236"
},
{
"id": 798,
"fullName": "恩",
"idCard": "110101201202019139",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
}
]
},
{
"joinId": 199,
"compationId": 66,
"compationName": "测试",
"competitionStartTime": "2020-12-26 00:00:00",
"competitionEndTime": "2020-12-27 23:59:59",
"status": 7,
"orderNo": "2020120816061002386920_66",
"orderTime": "2020-12-08 16:06:10",
"payTime": null,
"payMoney": 165,
"totalFee": null,
"compationType": 0,
"proName": "重剑",
"teamName": null,
"proId": 2,
"groupName": "U16组",
"groupId": 5,
"sexId": 1,
"sexName": "男",
"userId": 11522,
"userList": [
{
"id": 799,
"fullName": "了",
"idCard": "110101200403074033",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
}
]
},
{
"joinId": 200,
"compationId": 66,
"compationName": "测试",
"competitionStartTime": "2020-12-26 00:00:00",
"competitionEndTime": "2020-12-27 23:59:59",
"status": 3,
"orderNo": "2020120816462442530813_66",
"orderTime": "2020-12-08 16:46:24",
"payTime": "2020-12-08 16:46:30",
"payMoney": 165,
"totalFee": 0.01,
"compationType": 0,
"proName": "花剑",
"teamName": null,
"proId": 1,
"groupName": "U16组",
"groupId": 5,
"sexId": 1,
"sexName": "男",
"userId": 11522,
"userList": [
{
"id": 800,
"fullName": null,
"idCard": null,
"competitionSex": null,
"coachFullName": "六天",
"coachIdCard": "110101200403071692"
},
{
"id": 801,
"fullName": "啦",
"idCard": "110101200403076119",
"competitionSex": 0,
"coachFullName": null,
"coachIdCard": null
}
]
}
]
}