1.sql脚本
/*
Navicat Premium Data Transfer
Source Server : syl
Source Server Type : MySQL
Source Server Version : 100509
Source Host : localhost:3306
Source Schema : jtsys
Target Server Type : MySQL
Target Server Version : 100509
File Encoding : 65001
Date: 01/07/2022 00:19:23
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for order_user
-- ----------------------------
DROP TABLE IF EXISTS `order_user`;
CREATE TABLE `order_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order_user
-- ----------------------------
INSERT INTO `order_user` VALUES (1, 'zhangsan', 15);
INSERT INTO `order_user` VALUES (2, 'lisi', 16);
SET FOREIGN_KEY_CHECKS = 1;
/*
Navicat Premium Data Transfer
Source Server : syl
Source Server Type : MySQL
Source Server Version : 100509
Source Host : localhost:3306
Source Schema : jtsys
Target Server Type : MySQL
Target Server Version : 100509
File Encoding : 65001
Date: 01/07/2022 00:20:14
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for order11
-- ----------------------------
DROP TABLE IF EXISTS `order11`;
CREATE TABLE `order11` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL COMMENT '用户id',
`create_time` datetime(0) DEFAULT NULL COMMENT '创建时间',
`note` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
`number` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '编号',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order11
-- ----------------------------
INSERT INTO `order11` VALUES (1, 1, '2022-06-30 22:56:01', '11', '123');
INSERT INTO `order11` VALUES (2, 1, '2022-06-30 22:56:15', '22', '1234');
INSERT INTO `order11` VALUES (3, 2, '2022-06-29 22:57:46', '33', '234');
INSERT INTO `order11` VALUES (4, 2, '2022-06-29 22:57:57', '44', '253');
SET FOREIGN_KEY_CHECKS = 1;
2.核心代码
2.1.控制层
package com.zyp.controller;
import com.zyp.common.NoLogin;
import com.zyp.dto.user.UserAddRequestDto;
import com.zyp.dto.user.UserInfoResponseDto;
import com.zyp.dto.user.UserResponseDto;
import com.zyp.service.UserService;
import com.zyp.util.PageResult;
import com.zyp.util.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import javax.validation.constraints.NotNull;
import java.util.List;
/**
* @author syl
* @description TODO
* @since 2022/6/29
*/
@RestController
@RequestMapping(value = "user/")
@Api(tags = "用户模块")
public class UserController {
@Autowired
private UserService userService;
@PostMapping("addUser")
@ApiOperation("新增单个用户")
@NoLogin
public Result addUser(@RequestBody @Validated UserAddRequestDto userAddRequestDto){
userService.addUser(userAddRequestDto);
return Result.ok("新增成功");
}
@PostMapping("addUsers")
@ApiOperation("新增多个用户")
@NoLogin
public Result addUsers(@RequestBody @Validated List<UserAddRequestDto> userAddRequestDto){
userService.addUser(userAddRequestDto);
return Result.ok("新增成功");
}
@GetMapping("selectUser")
@ApiOperation("分页获取所有用户")
@NoLogin
public Result selectUser(@RequestParam Integer pageSize,@RequestParam Integer pageCurrent){
PageResult<UserResponseDto> userList = userService.selectUser(pageCurrent, pageSize);
return Result.ok().put(userList);
}
@GetMapping("selectUserById")
@ApiOperation("查询单个用户详情")
@NoLogin
public Result selectUserById(@RequestParam Integer userId){
UserInfoResponseDto userInfo = userService.selectUserById(userId);
return Result.ok().put(userInfo);
}
@DeleteMapping("deleteUserById")
@ApiOperation("删除单个用户")
@NoLogin
public Result deleteUserById(@RequestParam @NotNull(message = "用户不能为空") Integer userId){
userService.deleteUserById(userId);
return Result.ok();
}
@GetMapping("getOrderInfoById")
@ApiOperation("查询订单详情")
@NoLogin
public Result getOrderInfoById(@RequestParam Integer orderId){
return userService.getOrderInfoById(orderId);
}
@GetMapping("geUserOrderById")
@ApiOperation("查询指定用户订单")
@NoLogin
public Result geUserOrderById(@RequestParam Integer userId){
return userService.geUserOrderById(userId);
}
@GetMapping("geUserOrderById1")
@ApiOperation("查询指定用户订单")
@NoLogin
public Result geUserOrderById1(@RequestParam Integer userId){
return userService.geUserOrderById1(userId);
}
}
service层
package com.zyp.service;
import com.zyp.dto.user.UserAddRequestDto;
import com.zyp.dto.user.UserInfoResponseDto;
import com.zyp.dto.user.UserResponseDto;
import com.zyp.util.PageResult;
import com.zyp.util.Result;
import java.util.List;
/**
* @author syl
* @description TODO
* @since 2022/6/29
*/
public interface UserService {
PageResult<UserResponseDto> selectUser(Integer pageCurrent, Integer pageSize);
int addUser(UserAddRequestDto userAddRequestDto);
UserInfoResponseDto selectUserById(Integer userId);
int addUser(List<UserAddRequestDto> userAddRequestDto);
int deleteUserById(Integer userId);
Result getOrderInfoById(Integer orderId);
Result geUserOrderById(Integer userId);
Result geUserOrderById1(Integer userId);
}
service实现类
package com.zyp.service.impl;
import com.zyp.dto.user.*;
import com.zyp.entity.OrderUser;
import com.zyp.mapper.UserMappper;
import com.zyp.service.UserService;
import com.zyp.util.PageResult;
import com.zyp.util.Result;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Objects;
/**
* @author syl
* @description TODO
* @since 2022/6/29
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMappper userMappper;
@Override
public PageResult<UserResponseDto> selectUser(Integer pageCurrent, Integer pageSize) {
if(pageCurrent<1){
pageCurrent=1;
}
if(pageSize<1){
pageSize=1;
}
Integer start=(pageCurrent-1)*pageSize;
int total= userMappper.getTotal();
List<UserResponseDto> resultList=userMappper.selectUser1(start,pageSize);
// List<OrderUser> userList=userMappper.selectUser(start,pageSize);
// List<UserResponseDto> resultList= Lists.newArrayList();
// userList.stream().map(user->{
// UserResponseDto userResponseDto=new UserResponseDto();
// userResponseDto.setUserName(user.getName());
// userResponseDto.setUserAge(user.getAge());
// return resultList.add(userResponseDto);
// }).collect(Collectors.toList());
return new PageResult<>(pageCurrent, pageSize, total, resultList);
}
@Override
public int addUser(UserAddRequestDto userAddRequestDto) {
OrderUser orderUser=new OrderUser();
BeanUtils.copyProperties(userAddRequestDto, orderUser);
int i=userMappper.addUser(orderUser);
Integer id = orderUser.getId();
System.out.println("id = " + id);
return i;
}
@Override
public UserInfoResponseDto selectUserById(Integer userId) {
return userMappper.selectUserById(userId);
}
@Override
public int addUser(List<UserAddRequestDto> userAddRequestDto) {
return userMappper.addUser1(userAddRequestDto);
}
@Override
public int deleteUserById(Integer userId) {
return userMappper.deleteUserById(userId);
}
@Override
public Result getOrderInfoById(Integer orderId) {
OrderInfo orderInfo=userMappper.getOrderInfoById(orderId);
if (Objects.isNull(orderInfo)) {
return Result.ok().put("订单"+orderId+"不存在");
}
return Result.ok().put(orderInfo);
}
@Override
public Result geUserOrderById(Integer userId) {
UserOrder userOrder=userMappper.geUserOrderById(userId);
return Result.ok().put(userOrder);
}
@Override
public Result geUserOrderById1(Integer userId) {
UserOrder userOrder=userMappper.geUserOrderById1(userId);
return Result.ok().put(userOrder);
}
}
Mapper层
package com.zyp.mapper;
import com.zyp.dto.user.*;
import com.zyp.entity.OrderUser;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author syl
* @description TODO
* @since 2022/6/29
*/
public interface UserMappper {
List<OrderUser> selectUser(@Param("start") Integer start, @Param("end") Integer pageSize);
int getTotal();
List<UserResponseDto> selectUser1(@Param("start") Integer start, @Param("end") Integer pageSize);
int addUser(@Param(value = "user") OrderUser orderUser);
UserInfoResponseDto selectUserById(@Param("id") Integer userId);
int addUser1(List<UserAddRequestDto> userAddRequestDto);
int deleteUserById(@Param("id") Integer userId);
OrderInfo getOrderInfoById(@Param("id") Integer orderId);
UserOrder geUserOrderById(@Param("id") Integer userId);
UserOrder geUserOrderById1(@Param("id") Integer userId);
}
实体类
用户表
package com.zyp.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
/**
* <p>
* 用户表
* </p>
*
* @author syl
* @since 2022-06-29
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("order_user")
@ApiModel(value="OrderUser对象", description="用户表")
public class OrderUser{
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@ApiModelProperty(value = "姓名")
private String name;
@ApiModelProperty(value = "年龄")
private Integer age;
}
订单表
package com.zyp.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.time.LocalDateTime;
/**
* <p>
* 订单表
* </p>
*
* @author syl
* @since 2022-06-29
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("order11")
@ApiModel(value="Order11对象", description="订单表")
public class Order11{
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@ApiModelProperty(value = "用户id")
private Integer userId;
@ApiModelProperty(value = "创建时间")
private LocalDateTime createTime;
@ApiModelProperty(value = "备注")
private String note;
@ApiModelProperty(value = "编号")
private String number;
}
DTO
package com.zyp.dto.user;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
/**
* @author syl
* @description TODO
* @since 2022/6/30
*/
@Data
public class Order {
@ApiModelProperty("订单id")
private Integer orderId;
@ApiModelProperty("订单创建时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
@ApiModelProperty("订单备注")
private String orderNote;
@ApiModelProperty("订单编号")
private String orderNumber;
}
package com.zyp.dto.user;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.Date;
/**
* @author syl
* @description TODO
* @since 2022/6/30
*/
@Data
@ApiModel("订单详情实体类")
public class OrderInfo {
@ApiModelProperty(value = "订单id")
private Integer orderId;
@ApiModelProperty(value = "备注")
private String note;
@ApiModelProperty(value = "编号")
private String number;
@ApiModelProperty(value = "创建时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
@ApiModelProperty(value = "用户")
private OrderUser orderUser;
}
package com.zyp.dto.user;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @author syl
* @description TODO
* @since 2022/6/30
*/
@Data
public class OrderUser {
private Integer userId;
@ApiModelProperty(value = "姓名")
private String userName;
@ApiModelProperty(value = "年龄")
private Integer userAge;
}
package com.zyp.dto.user;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
/**
* @author syl
* @description TODO
* @since 2022/6/29
*/
@ApiModel(value = "用户新增实体")
@Data
public class UserAddRequestDto {
@ApiModelProperty(value = "用户姓名")
@NotBlank(message = "用户姓名不能为空")
private String name;
@ApiModelProperty(value = "年龄")
@NotNull(message = "用户姓名不能为空")
private Integer age;
}
package com.zyp.dto.user;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @author syl
* @description 用户详情响应实体
* @since 2022/6/29
*/
@ApiModel(value = "用户详情响应实体")
@Data
public class UserInfoResponseDto {
@ApiModelProperty(value = "用户id")
private Integer userId;
@ApiModelProperty(value = "用户姓名")
private String userName;
@ApiModelProperty(value = "年龄")
private Integer userAge;
}
package com.zyp.dto.user;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
* @author syl
* @description TODO
* @since 2022/6/30
*/
@Data
public class UserOrder {
@ApiModelProperty(value = "用户id")
private Integer userId;
@ApiModelProperty(value = "姓名")
private String userName;
@ApiModelProperty(value = "年龄")
private Integer userAge;
private List<Order> orderList;
}
package com.zyp.dto.user;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* @author syl
* @description 用户分页查询响应实体
* @since 2022/6/29
*/
@ApiModel(value = "用户分页查询响应实体")
@Data
public class UserResponseDto {
@ApiModelProperty(value = "用户姓名")
private String userName;
@ApiModelProperty(value = "年龄")
private Integer userAge;
}
XML文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zyp.mapper.UserMappper">
<insert id="addUser" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="com.zyp.entity.OrderUser">
insert into order_user values (null,#{user.name},#{user.age});
</insert>
<insert id="addUser1">
insert into order_user (name,age) value
<foreach collection="list" item="user" separator=",">
(#{user.name},#{user.age})
</foreach>
</insert>
<delete id="deleteUserById">
delete from order_user where id=#{id}
</delete>
<select id="selectUser" resultType="com.zyp.entity.OrderUser">
select * from order_user limit #{start},#{end}
</select>
<select id="getTotal" resultType="java.lang.Integer">
select count(id) from order_user
</select>
<select id="selectUser1" resultMap="info">
select name,age from order_user limit #{start},#{end}
</select>
<select id="selectUserById" resultMap="info1">
select * from order_user where id=#{id}
</select>
<select id="getOrderInfoById" resultMap="orderInfo1">
SELECT
o.id,
o.create_time,
o.note,
o.number,
u.id uid,
u.name,
u.age
FROM
order11 o
LEFT JOIN order_user u ON o.user_id = u.id
where o.id=#{id}
</select>
<resultMap id="orderInfo1" type="com.zyp.dto.user.OrderInfo">
<id column="id" property="orderId"/>
<result column="create_time" property="createTime"/>
<result column="note" property="note"/>
<result column="number" property="number"/>
<!--一对一关联查询-->
<!--javaType表示javaBean的类型,此处为OrderUser,一般用于association-->
<association property="orderUser" javaType="com.zyp.dto.user.OrderUser">
<id column="uid" property="userId"/>
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
</association>
</resultMap>
<!--一对多关联 查询两次 分页有问题-->
<select id="geUserOrderById" resultMap="userOrder">
SELECT
u.id,
u.name,
u.age,
o.id oid,
o.create_time,
o.note,
o.number
FROM
order_user u
LEFT JOIN order11 o ON u.id = o.user_id
WHERE
u.id = #{id}
</select>
<resultMap id="userOrder" type="com.zyp.dto.user.UserOrder">
<id column="id" property="userId"/>
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
<!--ofType表示javaBean的类型,此处为Order,一般用于collection-->
<collection property="orderList" javaType="list" ofType="com.zyp.dto.user.Order">
<id column="oid" property="orderId"/>
<result column="create_time" property="createTime"/>
<result column="note" property="orderNote"/>
<result column="number" property="orderNumber"/>
</collection>
</resultMap>
<!--一对多关联 查询两次 分页没问题-->
<select id="geUserOrderById1" resultMap="userOrder1">
select * from order_user where id=#{id}
</select>
<resultMap id="userOrder1" type="com.zyp.dto.user.UserOrder">
<id column="id" property="userId"/>
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
<collection property="orderList" column="id" select="getOrderByUserId"/>
</resultMap>
<select id="getOrderByUserId" resultMap="order1">
select * from order11 where user_id=#{id}
</select>
<resultMap id="order1" type="com.zyp.dto.user.Order">
<id column="id" property="orderId"/>
<result column="create_time" property="createTime"/>
<result column="note" property="orderNote"/>
<result column="number" property="orderNumber"/>
</resultMap>
<resultMap id="info1" type="com.zyp.dto.user.UserInfoResponseDto">
<id column="id" property="userId"/>
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
</resultMap>
<resultMap id="info" type="com.zyp.dto.user.UserResponseDto">
<result column="name" property="userName"/>
<result column="age" property="userAge"/>
</resultMap>
</mapper>
分页实体
package com.zyp.util;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.List;
/**
* @author syl
* @description 分页实体
* @since 2021/3/28
*/
@Data
@ApiModel(value = "分页对象")
@NoArgsConstructor
public class PageResult<T> implements Serializable {
/**
* 每页显示条数
*/
@ApiModelProperty(value = "每页显示条数", dataType = "long")
private long size;
/**
* 当前页号
*/
@ApiModelProperty(value = "当前页号", dataType = "long")
private long current;
/**
* 记录总页数
*/
@ApiModelProperty(value = "记录总页数", dataType = "long")
private long pagesCount;
/**
* 总记录数
*/
@ApiModelProperty(value = "总记录数", dataType = "long")
private long total;
/**
* 结果集
*/
private List<T> data;
public PageResult(long pageCurrent,long pageSize,long pageTotal, List data){
this.current=pageCurrent;
this.size=pageSize;
this.total=pageTotal;
this.data=data;
this.pagesCount=(pageTotal-1)/pageSize+1;
}
}