开始的想法源于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中属性的数据类型一定要和表实体属性的数据类型一致