Spring Boot Jpa框架自定义查询语句返回自定义实体
2021-05-05 22:27
标签:query 如何 bsp roc image loading ext date 任务 开始的想法源于netcore中通过lambd查询数据库后可以直接转为实体,所以试验在java中如何实现 具体试验思路: 利用现有数据库,定义一个获取车辆的api,调用获取车辆及司机信息 1、现有车辆和司机模型 车辆表(大概列一下,实际比这多): 司机表 2、定义返回模型 最终返回 3、定义入参模型(这个不重要) 4、定义服务 5、业务实现 6、数据查询 7、最终结果 记录细节,踩坑 主要是这个 注意构造函数的参数顺序 还要注意实体的数据类型要和表实体的数据类型一致,名字可以不一样 最后一点 最最重要的: 1、查询语句中使用表实体名称 2、CarAndDriverTestBase中属性的数据类型一定要和表实体属性的数据类型一致 Spring Boot Jpa框架自定义查询语句返回自定义实体 标签:query 如何 bsp roc image loading ext date 任务 原文地址:https://www.cnblogs.com/fuyujian/p/13191709.html@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));
}
}
@Data
public class CarAndUnitTest {
public CarAndUnitTest_Car
@Data
public class CarAndUnitTest_Car
@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() {
}
}
@Data
public class QueryPara {
// @ApiModelProperty("操作标识,1为选择机组车辆时的带条件选择,其他为不带条件的查询,可为空")
// private int operFlag;
@ApiModelProperty("品类名称,操作标识为1的时候不能为空,品类名称从任务单中获得")
private String procCate;
}
@ApiOperation("查询车辆和机组信息1")
@PostMapping(value = "/getCarAndUnitTest")
/*
operFlag:操作标识 , proCategory:产品分类
*/
public CarAndUnitTest getCarAndUnitTest(@RequestBody QueryPara query) {
return carService.getCarAndUnit_1(query);
}
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
@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
@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:是我们查询结果需要转换的实体
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
文章标题:Spring Boot Jpa框架自定义查询语句返回自定义实体
文章链接:http://soscw.com/index.php/essay/82924.html