• Spring Boot Jpa框架自定义查询语句返回自定义实体


    开始的想法源于netcore中通过lambd查询数据库后可以直接转为实体,所以试验在java中如何实现

    具体试验思路:

    利用现有数据库,定义一个获取车辆的api,调用获取车辆及司机信息

    1、现有车辆和司机模型

    车辆表(大概列一下,实际比这多):

    @Entity
    @Data
    @Table(name="car")
    public class Car implements Serializable {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private Integer id;
    
        /** 车牌号 */
        @ApiModelProperty("车牌号")
        @Column(name = "car_number",nullable = false)
        private String carNumber;
    
    
        /** 车辆容积 */
        @ApiModelProperty("最大载量")
        @Column(name = "car_volume",nullable = false)
        private Double carVolume;
    
    
        /** 燃料类型 */
        @ApiModelProperty("燃料类型")
        @Column(name = "fuel_type")
        private String fuelType;
    
        /** 百公里耗能 */
        @ApiModelProperty("百公里耗能")
        @Column(name = "energy_consumption")
        private Double energyConsumption;
    
    
        /** 车辆营运状态 */
        @ApiModelProperty("车辆状态")
        @Column(name = "car_operating_state")
        private Integer carOperatingState;
    
        /** 车辆状态 */
        @ApiModelProperty("车辆营运状态")
        @Column(name = "car_status",nullable = false)
        private Integer carStatus;
    }

    司机表

    @Entity
    @Data
    @Table(name="driver")
    public class Driver implements Serializable {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id")
        private Integer id;
    
        /** 姓名 */
        @ApiModelProperty("姓名")
        @Column(name = "name",nullable = false)
        @NotBlank
        private String name;
    
        /** 性别 */
        @ApiModelProperty("性别")
        @Column(name = "sex")
        private Integer sex;
    
        /** 年龄 */
        @ApiModelProperty("年龄")
        @Column(name = "age")
        private Integer age;
    
        /** 生日 */
        @ApiModelProperty("生日")
        @Column(name = "birthday")
        @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
        private Date birthday;
    
        /** 手机号 */
        @ApiModelProperty("手机号")
        @Column(name = "phone")
        private String phone;
    
    
        public void copy(Driver source){
            BeanUtil.copyProperties(source,this, CopyOptions.create().setIgnoreNullValue(true));
        }
    }

    2、定义返回模型

    最终返回

    @Data
    public class CarAndUnitTest {
    
        public CarAndUnitTest_Car<CarAndDriverTest> car;
    }
    @Data
    public class CarAndUnitTest_Car<T> {
        public Integer all;
        public Integer working;
        public Integer alarm;
        public List<T> list;
    }
    @Data
    public class CarAndDriverTest extends CarAndDriverTestBase {
        //车辆状态值
        private String carStatusStr;
    }
    @Data
    public class CarAndDriverTestBase {
        private Integer id;
        //车牌号
        private String car_number;
    
        //最大载量
        private Double car_volume;
        //车辆状态
        private Integer car_status;
        //司机ID
        private Integer driver_id;
        //司机电话
        private String phone;
        //司机名称
        private String name;
    
        public CarAndDriverTestBase(Integer id,String car_number,Double car_volume,Integer car_status,Integer driver_id,String phone,String name){
            this.id=id;
            this.car_number=car_number;
            this.car_volume=car_volume;
            this.car_status=car_status;
            this.driver_id=driver_id;
            this.phone=phone;
            this.name=name;
        }
    
        public CarAndDriverTestBase() {
        }
    }

    3、定义入参模型(这个不重要)

    @Data
    public class QueryPara {
    //    @ApiModelProperty("操作标识,1为选择机组车辆时的带条件选择,其他为不带条件的查询,可为空")
    //    private int operFlag;
    
        @ApiModelProperty("品类名称,操作标识为1的时候不能为空,品类名称从任务单中获得")
        private String procCate;
    
    }

    4、定义服务

    @ApiOperation("查询车辆和机组信息1")
        @PostMapping(value = "/getCarAndUnitTest")
        /*
            operFlag:操作标识 , proCategory:产品分类
         */
        public CarAndUnitTest getCarAndUnitTest(@RequestBody QueryPara query) {
    
            return carService.getCarAndUnit_1(query);
        }

    5、业务实现

    public CarAndUnitTest getCarAndUnit_1(QueryPara query){
    
            CarAndUnitTest r=new CarAndUnitTest();
    
            CarAndUnitTest_Car car= CarList(query);
            r.car=car;
    
            return  r;
        }
    
        //获取车
        private CarAndUnitTest_Car CarList(QueryPara query){
    
            CarAndUnitTest_Car r=new CarAndUnitTest_Car();
            List<CarAndDriverTest> list=allCar();
            r.list=list;
            r.all=list.size();
            r.working=0;
            r.alarm=0;
            return  r;
        }
    
        //所有车
        private  List<CarAndDriverTest> allCar(){
            List<CarAndDriverTest> list= new ArrayList<>();
    
            List<CarAndDriverTestBase> caranddriverlist=carRepository.allCarAndDriverTest();
    
            for(CarAndDriverTestBase obj:caranddriverlist){
    
                CarAndDriverTest add=new CarAndDriverTest();
                add.setId(obj.getId());
                add.setCar_status(obj.getCar_status());
                add.setCar_number(obj.getCar_number());
                add.setCar_volume(obj.getCar_volume());
                add.setDriver_id(obj.getDriver_id());
    
                add.setName(obj.getName());
                add.setPhone(obj.getPhone());
                add.setCarStatusStr(CarStatus.getName(obj.getCar_status()));
    
                list.add(add);
            }
            return list;
        }

    6、数据查询

    @Query(value="select new me.zhengjie.scheduling.car.domain.CarAndDriverTestBase( c.id,c.carNumber,c.carVolume,c.carStatus,c.driverId,d.phone,d.name) "+
                "FROM Car c LEFT JOIN Driver d ON c.driverId = d.id where c.flag = 1",nativeQuery = false)
        List<CarAndDriverTestBase> allCarAndDriverTest();

    7、最终结果

     

    记录细节,踩坑

    主要是这个

    @Query(value="select new me.zhengjie.scheduling.car.domain.CarAndDriverTestBase( c.id,c.carNumber,c.carVolume,c.carStatus,c.driverId,d.phone,d.name) "+
                "FROM Car c LEFT JOIN Driver d ON c.driverId = d.id where c.flag = 1",nativeQuery = false)
        List<CarAndDriverTestBase> allCarAndDriverTest();
    CarAndDriverTestBase:是我们查询结果需要转换的实体
    nativeQuery:false 或者nativeQuery不赋值,默认false.如果赋值true的话前面的value值是原生sql语句
    new me.zhengjie.scheduling.car.domain.CarAndDriverTestBase( c.id,c.carNumber,c.carVolume,c.carStatus,c.driverId,d.phone,d.name):
    我理解就是查询结果的每条数据通过调用实体的构造函数来赋值,但是又找不到实体在哪,干脆把路径补全

     注意构造函数的参数顺序

     还要注意实体的数据类型要和表实体的数据类型一致,名字可以不一样

     

     最后一点

    FROM Car c LEFT JOIN Driver d ON c.driverId = d.id where c.flag = 1
    其中的Car和Driver用的是表实体的name

    最最重要的: 

    1、查询语句中使用表实体名称

    2、CarAndDriverTestBase中属性的数据类型一定要和表实体属性的数据类型一致

  • 相关阅读:
    DS博客作业03--树
    DS博客作业02--栈和队列
    数据结构——线性表
    结构体
    C博客作业05--指针
    C语言博客作业04--数组
    面向对象设计大作业第二阶段:图书馆系统
    Java程序设计-有理数类的设计
    DS博客作业05-查找
    DS博客作业04-图
  • 原文地址:https://www.cnblogs.com/fuyujian/p/13191709.html
Copyright © 2020-2023  润新知