querydsl jpa.md

• Predicate 关键字
• • NE : not equal 不等于 .ne -GE : greater than or equal 大于等于 .goe
  • LE : less than or equal 小于等于 .loe
  • GT : greater than 大于 .gt
  • LT : less than 小于 .lt
  • EQ : equal 等于 .eq
  • 可以参考Hibernate部分

说明：

• • Sort sort = new Sort(Sort.Direction.DESC, "updateTime");中的"updateTime"中的"updateTime"，不是数据库字段名，而是QXXXX.update对应的字段名。public final DateTimePath<java.util.Date> updateTime = createDateTime("updateTime", java.util.Date.class);**
• 第一种使用

多个约束条件，使用and
@RequestMapping(value = "/testMessage/{staionid}", method = RequestMethod.GET)@ResponseBodypublic List<OperatorDevice> test(@PathVariable String staionid) {Predicate predicate = QOperatorDevice.operatorDevice.envtype.eq(1).and(QOperatorDevice.operatorDevice.stationid.ne("101437000_03"));List<OperatorDevice> list = (List<OperatorDevice>) operatorDeviceRepository.findAll(predicate);return list;}

• 第二种，多个有可能为空的条件
• • 查询时可能传过多个查询条件，有的条件为空

public PageBase<StationVO> queryStationList(Integer pageIndex, Integer pageSize, String area_code,String station_name, Integer station_status, String station_id) {// TODO Auto-generated method stubPageable pageable = new PageRequest(pageIndex, pageSize);List<BooleanExpression> predicates = new ArrayList<>();if (StringUtils.isNotBlank(station_name)) {predicates.add(qStationOrig.stationName.like("%" + station_name + "%"));}if (StringUtils.isNotBlank(area_code)) {predicates.add(qStationOrig.areaCode.eq(area_code));}if (station_status != null) {predicates.add(qStationOrig.stationStatus.longValue().eq((long) station_status));}if (StringUtils.isNotBlank(station_id)) {predicates.add(qStationOrig.stationId.eq(station_id));}Page<StationOrig> pageList = stationOrigRepository.findAll(ExpressionUtils.allOf(predicates.toArray(new Predicate[] {})), pageable);List<StationVO> list = new ArrayList<>();for (StationOrig stationOrig : pageList) {StationVO stationVO = new StationVO();stationVO.setArea_code(stationOrig.getAreaCode());stationVO.setStation_id(String.valueOf(stationOrig.getStationId()));stationVO.setStation_name(stationOrig.getStationName());stationVO.setStation_status(stationOrig.getStationStatus());list.add(stationVO);}PageBase<StationVO> pageBase = new PageBase<>();pageBase.setPageIndex(pageList.getNumber());pageBase.setPageObject(list);pageBase.setPageSize(pageList.getSize());pageBase.setTotalPage(pageList.getTotalPages());pageBase.setTotalCount((long) pageList.getNumberOfElements());return pageBase;}

• 第三种，多个可能为空的条件连表查询

public PageBase<DeviceVO> queryStationEquipmentDdList(Integer pageIndex, Integer pageSize, String equipment_id,String station_name, String operator_name) {// TODO Auto-generated method stubList<BooleanExpression> predicates = new ArrayList<>();QStationOrig qStationOrig = QStationOrig.stationOrig;if (StringUtils.isNotBlank(station_name)) {predicates.add(qStationOrig.stationName.like("%" + station_name + "%"));}if (StringUtils.isNotBlank(operator_name)) {predicates.add(qStationEquipmentDd.manufacturerName.like(("%" + operator_name + "%")));}if (StringUtils.isNotBlank(equipment_id)) {predicates.add(qStationEquipmentDd.equipmentId.eq(equipment_id));}Pageable pageable = new PageRequest(pageIndex, pageSize);QueryResults<StationEquipmentDd> results = jpaQueryFactory.select(qStationEquipmentDd).from(qStationEquipmentDd).leftJoin(qStationOrig).on(qStationEquipmentDd.stationId.eq(qStationOrig.stationId)).where(ExpressionUtils.allOf(predicates.toArray(new Predicate[] {}))).offset(pageable.getOffset()).limit(pageable.getPageSize()).fetchResults();List<StationEquipmentDd> list = results.getResults();List<DeviceVO> deviceVOs = new ArrayList<>();Page<StationEquipmentDd> page = PageableExecutionUtils.getPage(list, pageable, results::getTotal);for (StationEquipmentDd stationEquipmentDd : page) {DeviceVO deviceVO = new DeviceVO();deviceVO.setCurrent(stationEquipmentDd.getCurrent());deviceVO.setEquipment_id(stationEquipmentDd.getEquipmentId());deviceVO.setGun_index((int) stationEquipmentDd.getGun_index());deviceVO.setOperator_name(stationEquipmentDd.getManufacturerName());deviceVO.setPower(stationEquipmentDd.getPower());deviceVO.setVol_max(stationEquipmentDd.getVol_max());deviceVO.setVol_min(stationEquipmentDd.getVol_min());deviceVO.setEquipment_type("直流");deviceVOs.add(deviceVO);}PageBase pageBase = new PageBase<>();pageBase.setPageIndex(page.getNumber());pageBase.setPageSize(page.getSize());pageBase.setPageObject(deviceVOs);pageBase.setTotalCount(page.getTotalElements());pageBase.setTotalPage(page.getTotalPages());return pageBase;}

• jpa Sort
• • sort 用来进行排序，使用方法如下：

@RequestMapping("/findByName")
public void findByName4(){//按照ID倒序排列System.out.println("直接创建sort对象，通过排序方法和属性名");Sort sort = new Sort(Sort.Direction.DESC,"id");List<Customer> result = repository.findByName4("Bauer",sort);for (Customer customer:result){System.out.println(customer.toString());}System.out.println("-------------------------------------------");//按照ID倒序排列System.out.println("通过Sort.Order对象创建sort对象");Sort sortx = new Sort(new Sort.Order(Sort.Direction.DESC,"id"));List<Customer> resultx = repository.findByName4("Bauer",sort);for (Customer customer:result){System.out.println(customer.toString());}System.out.println("-------------------------------------------");System.out.println("通过排序方法和属性List创建sort对象");List<String> sortProperties = new ArrayList<>();sortProperties.add("id");sortProperties.add("firstName");Sort sort2 = new Sort(Sort.Direction.DESC,sortProperties);List<Customer> result2 = repository.findByName4("Bauer",sort2);for (Customer customer:result2){System.out.println(customer.toString());}System.out.println("-------------------------------------------");System.out.println("通过创建Sort.Order对象的集合创建sort对象");List<Sort.Order> orders = new ArrayList<>();orders.add(new Sort.Order(Sort.Direction.DESC,"id"));orders.add(new Sort.Order(Sort.Direction.ASC,"firstName"));List<Customer> result3 = repository.findByName4("Bauer",new Sort(orders));for (Customer customer:result3){System.out.println(customer.toString());}System.out.println("-------------------------------------------");
}

上面几种方法都测试过了，都属于正常的。第一种和第二种一样，第三种多个字段的方向都一样，第四种可以自由指定方向 可以在原生@Query中使用，也可以在Perdicate中使用，如下

@Query("select c from Customer c where c.firstName=:name or c.lastName=:name")
List<Customer> findByName4(@Param("name") String name2,Sort sort);
第一种办法测试不行，还需要验证
第二种测试正常：@RequestMapping(value = "/testMessage/{station}", method = RequestMethod.GET)@ResponseBodypublic List<OperatorDevice> test(@PathVariable String station) {Predicate predicate =QOperatorDevice.operatorDevice.envtype.eq(1).and(QOperatorDevice.operatorDevice.stationid.ne("101437000_03"));List<OperatorDevice> list =  (List<OperatorDevice>) operatorDeviceRepository.findAll(predicate,new Sort(Sort.Direction.DESC,"id"));return list;}第三种jPAQueryFactory.select(qStationOrig).from(qStationOrig).orderBy(qStationOrig.fullStationId.desc()).limit(1).fetchFirst();

QueryDsl链接

Predicate 是原生Sql的where后面的部分。 Predicate 处理非String字段时，比如long型数据，可以使用longValue处理，示例如下

Predicate predicate = qtCity.id.longValue().lt(3).and(qtCity.name.like("shanghai"));

• queryDsl Pageable

• Pageable可以和sort配置使用，先排序完之后再分页，其中page是要显示的页码，从0开始，size是每页显示的个数

@RequestMapping(value = "/testMessage2/{station}/{size}/{num}", method = RequestMethod.GET)@ResponseBodypublic Page<OperatorDevice> test2(@PathVariable String station, @PathVariable String size,@PathVariable String num) {int pageSize = Integer.parseInt(size);int pageNum = Integer.parseInt(num);Predicate predicate = QOperatorDevice.operatorDevice.envtype.eq(1).and(QOperatorDevice.operatorDevice.stationid.ne("101437000_03"));List<Sort.Order> sortList = new ArrayList<>();sortList.add(new Sort.Order(Sort.Direction.DESC, "id"));sortList.add(new Sort.Order(Sort.Direction.DESC, "deviceid"));Sort sort = new Sort(sortList);Pageable pageable = new PageRequest(pageNum, pageSize, sort);Page<OperatorDevice> pagelist = operatorDeviceRepository.findAll(predicate, pageable);return pagelist;}实际排序分页结果为：{"content":[{"id":11,"canlogin":0,"operatorid":"91110113MA01CF8F83","operator":"中恒","deviceid":"4000121111","hlhtOperatorId":"","envtype":1,"ratecurrent":"","ratevoltage":"","station":"北京二七剧场充电站","stationid":"101437000_05"},{"id":10,"canlogin":0,"operatorid":"91110113MA01CF8F83","operator":"智充","deviceid":"1000121115","hlhtOperatorId":"","envtype":1,"ratecurrent":"","ratevoltage":"","station":"北京二七剧场充电站","stationid":"101437000_04"}],"last":false,"totalPages":3,"totalElements":5,"number":0,"size":2,"sort":[{"direction":"DESC","property":"id","ignoreCase":false,"nullHandling":"NATIVE","descending":true,"ascending":false},{"direction":"DESC","property":"deviceid","ignoreCase":false,"nullHandling":"NATIVE","descending":true,"ascending":false}],"first":true,"numberOfElements":2}

说明：content中的0,1为数据，last为是否为最末页。totalPages为所有页码数，totalElements为所有元素个数。size为每页显示的个数，number为页码，从0开始 sort部分为显示的制定的sort规则。first 为是否为首页，传入的为0，所以为首页。numberOfElements 为数据个数，比如这次获取的是0、1两个，所以为2，如果未获取到则为0 第二个图就是未获取到数据

• Pageable的第二种使用方式，在原生Query中使用

	@RequestMapping(value = "/testMessage2/{station}/{size}/{num}", method = RequestMethod.GET)@ResponseBodypublic List<OperatorDevice> test2(@PathVariable String station, @PathVariable String size,@PathVariable String num) {int pageSize = Integer.parseInt(size);int pageNum = Integer.parseInt(num);Sort sort = new Sort(Sort.Direction.DESC,"id");// 说明"id" 不是数据表中字段的名字，而是QOperatorDevice.id对应的id名字==Pageable pageable = new PageRequest(pageNum, pageSize);List<OperatorDevice> list = operatorDeviceRepository.testFind(pageable);return list;}
operatorDeviceRepository中：@Query(value="select * from ep_operator_deivce order by ?#{#pageable}", nativeQuery=true)List<OperatorDevice> testFind(org.springframework.data.domain.Pageable pageable);

 显示结果如上，可以根据Pageable分页，但是不能显示分页信息，只显示数据信息。不如第一种效果明显。同时在Pageable中加入Sort排序会报错。

• 第三种实现方式

	   @RequestMapping(value = "/queryPagedlog", method = RequestMethod.POST)public TResponse<String> queryPagedlog(@RequestBody PagedLogQueryParam pagedLogQueryParam) {log.info("queryOperatorLog:{}", JSON.toJSONString(pagedLogQueryParam));QUpLoadLogGiftInfo upLoadLogGiftInfo = QUpLoadLogGiftInfo.upLoadLogGiftInfo;	       Pageable pageable = new PageRequest(pagedLogQueryParam.getPage(), pagedLogQueryParam.getSize());QueryResults<UpLoadLogGiftInfo> results = jpaQueryFactory.select(upLoadLogGiftInfo).from(upLoadLogGiftInfo).where(upLoadLogGiftInfo.deviceid.eq(pagedLogQueryParam.getEquipmentId())).orderBy(upLoadLogGiftInfo.uptime.desc()).offset(pageable.getOffset()).limit(pageable.getPageSize()).fetchResults();	           List<UpLoadLogGiftInfo> list = results.getResults();//用翻页控件Page<UpLoadLogGiftInfo> listPage = PageableExecutionUtils.getPage(list, pageable, results::getTotal);String retJson = JSONObject.toJSONString(listPage);log.info("queryOperatorLog result:{}", retJson);return TResponse.valueOf(TResponse.Status.OK, retJson);}

使用分页控件的方式

• Pageable资料

使用jpa更新某个对象数据时，可以使用update，但update需要填写所有字段，这样比较麻烦，可以使用responsity.save方法，把id指定为要更新的id，重新save一次就是更新。id为null就是保存新的

使用deljpa时获取数据，但数据可能在多个表中，需要指定要获取的字段

public PageBase<DeviceVO> queryStationEquipmentDdList(Integer pageIndex, Integer pageSize, String equipment_id,String station_name, String operator_name) {// TODO Auto-generated method stubList<BooleanExpression> predicates = new ArrayList<>();// QStationOrig qStationOrig = QStationOrig.stationOrig;if (StringUtils.isNotBlank(station_name)) {predicates.add(qStationOrig.stationName.like("%" + station_name + "%"));}if (StringUtils.isNotBlank(operator_name)) {predicates.add(qStationEquipmentDd.manufacturerName.like(("%" + operator_name + "%")));}if (StringUtils.isNotBlank(equipment_id)) {predicates.add(qStationEquipmentDd.equipmentId.eq(equipment_id));}Pageable pageable = new PageRequest(pageIndex, pageSize);QueryResults<Tuple> results = jpaQueryFactory.select(qStationEquipmentDd.current, qStationEquipmentDd.equipmentId, qStationEquipmentDd.gun_index,qStationEquipmentDd.manufacturerId, qStationEquipmentDd.manufacturerName,qStationEquipmentDd.power, qStationEquipmentDd.vol_max, qStationEquipmentDd.vol_min,qStationOrig.stationName).from(qStationEquipmentDd).leftJoin(qStationOrig).on(qStationEquipmentDd.stationId.eq(qStationOrig.stationId)).where(ExpressionUtils.allOf(predicates.toArray(new Predicate[] {}))).offset(pageable.getOffset()).limit(pageable.getPageSize()).fetchResults();List<DeviceVO> deviceVOs = new ArrayList<>();for (Tuple tuple : results.getResults()) {DeviceVO deviceVO = new DeviceVO();deviceVO.setCurrent(tuple.get(qStationEquipmentDd.current));// 或者使用	deviceVO.setCurrent(tuple.get(0,Doubule.Class));deviceVO.setEquipment_id(tuple.get(qStationEquipmentDd.equipmentId));deviceVO.setGun_index(tuple.get(qStationEquipmentDd.gun_index).intValue());deviceVO.setOperator_name(tuple.get(qStationEquipmentDd.manufacturerName));deviceVO.setPower(tuple.get(qStationEquipmentDd.power));deviceVO.setVol_max(tuple.get(qStationEquipmentDd.vol_max));deviceVO.setVol_min(tuple.get(qStationEquipmentDd.vol_min));deviceVO.setEquipment_type("直流");deviceVO.setStation_name(tuple.get(qStationOrig.stationName));deviceVOs.add(deviceVO);}Page<Tuple> page = PageableExecutionUtils.getPage(results.getResults(), pageable, results::getTotal);PageBase pageBase = new PageBase<>();pageBase.setPageIndex(page.getNumber());pageBase.setPageSize(page.getSize());pageBase.setPageObject(deviceVOs);pageBase.setTotalCount(page.getTotalElements());pageBase.setTotalPage(page.getTotalPages());return pageBase;}

• dsl事务
• • 事务可以保证多个操作一起成功，在dsljpa中有功能实现了事务。@Transactional(readOnly = false, rollbackFor = Throwable.class)
• • 代码：

@Override@Transactional(readOnly = false, rollbackFor = Throwable.class)@Modifyingpublic Response<String> deleteStationEquipmentDdByEquipmentId(String equipmentId) {// TODO Auto-generated method stubStationEquipmentDd stationEquipmentDd = jpaQueryFactory.select(qStationEquipmentDd).from(qStationEquipmentDd).where(qStationEquipmentDd.equipmentId.eq(equipmentId)).fetchFirst();if (stationEquipmentDd != null && StringUtils.isNotBlank(stationEquipmentDd.getStationId())) {log.info("equipmentid:{} in station:{} ,delete it first", equipmentId, stationEquipmentDd.getStationId());return Response.valueOf(ResponseStatusEnum.DEIVCE_EXISTS_IN_SAAS);}try {jpaQueryFactory.delete(qStationEquipmentDd).where(qStationEquipmentDd.equipmentId.eq(equipmentId)).execute();jpaQueryFactory.delete(qStationConnectorOrig).where(qStationConnectorOrig.connectorId.like(equipmentId + "0_")).execute();} catch (Exception e) {// TODO: handle exceptionlog.info(e.getMessage());return Response.valueOf(ResponseStatusEnum.EXCEL_INCOMPATIBLE_FIELD);}return Response.valueOf(ResponseStatusEnum.OK, "success");}

• jpa 缓存的问题
• • jpa有默认缓存，Hibernate也是有的。保存时save是保存到内存中，saveAndFlush 是保存在数据库中。
  • update时， 数据更新了，立刻查询数据，则获取到的数据发现是没有改变的，此时查询数据库会发现数据已经更新了，但jpa获取到的数据则没有更新。原因就是jpa的缓存。目前没有发现很合适的办法处理
  • update之后，把id设置为null，则程序就会报错。原因是jpa的缓存问题，实际update没有更新到数据库，此时把id设置为null，就会报错。
  • jpa的缓存问题目前没有很好的办法处理，只能尽量避免。

• 子查询
• • 子查询时不能使用jpafactory了，而要使用JPAExpressions。如果子查询结果不唯一，则使用eqAll或使用eqAny。子查询中不用使用feach

List<String> list = jpaQueryFactory.select(qStationConnectorOrig.connectorId).from(qStationConnectorOrig).where(qStationConnectorOrig.equipmentId.eqAll(JPAExpressions.select(qStationEquipmentDd.equipmentId).from(qStationEquipmentDd).leftJoin(qStationOrig).on(qStationOrig.stationId.eq(qStationEquipmentDd.stationId)).where(qStationOrig.stationId.eq(station_id)))).fetch();

• https://www.v2ex.com/t/350737
• NumberExpression
  • NumberExpression 可以用来获取某个条件的sum

NumberExpression<Integer> numberExpression = new CaseBuilder().when(qStationEquipmentDd.current.gt(20.00)).then(1).otherwise(0);
List<Integer> fetch = jpaQueryFactory.select(numberExpression.sum()).from(qStationEquipmentDd).fetch();
// 可以获取电流大于20的sum值
NumberExpression<Integer> connectorTypeGroup = new CaseBuilder()
.when(qStationConnector.isRemoved.eq(DataStatusEnum.NORMAL.getCode()).and(qStationConnector.connectorType.eq(ConnectType.FAST.getValue())))
.then(EquipmentStatisticalTypeEnum.DIRECT.getCode()).when(qStationConnector.isRemoved.eq(DataStatusEnum.NORMAL.getCode()).and(qStationConnector.connectorType.in(ConnectType.AC_OUTLET.getValue(), ConnectType.SLOW.getValue())))
.then(EquipmentStatisticalTypeEnum.ALTERNATING.getCode()).otherwise(-1);

• in 多个查询条件

 QStationEquipmentOrig qStationEquipmentOrig = QStationEquipmentOrig.stationEquipmentOrig;List<StationEquipmentOrig> fetch = jpaQueryFactory.select(qStationEquipmentOrig).from(qStationEquipmentOrig).where(qStationEquipmentOrig.equipmentId.in(devices)).fetchAll().fetch();
或者使用Predicate
com.querydsl.core.types.Predicate predicate = qStationEquipmentOrig.equipmentId.in(devices);

• 多个where 条件

除了使用上面的List<BooleanExpression> predicates之外，还有两种方法
1、使用where中连接多个，可以使用and或者,
List<StationEquipmentOrig> fetch = jpaQueryFactory.select(qStationEquipmentOrig).from(qStationEquipmentOrig).where(qStationEquipmentOrig.equipmentId.eq("C601161019QJQLPXY"),qStationEquipmentOrig.manufacturerId.eq("101437000")).fetchAll().fetch();List<StationEquipmentOrig> fetch = jpaQueryFactory.select(qStationEquipmentOrig).from(qStationEquipmentOrig).where(qStationEquipmentOrig.equipmentId.eq("C601161019QJQLPXY").and(qStationEquipmentOrig.manufacturerId.eq("101437000")) ).fetchAll().fetch();
这两种是一样的
2、使用JPAQuery 添加where条件    
JPAQuery<StationEquipmentOrig> jpaQuery = jpaQueryFactory.select(qStationEquipmentOrig).from(qStationEquipmentOrig).where(qStationEquipmentOrig.equipmentId.eq("C601161019QJQLPXY"));jpaQuery.where(qStationEquipmentOrig.manufacturerId.eq("101437000"));List<StationEquipmentOrig> fetch = jpaQuery.fetch();

• 连表查询
•  

jpaQueryFactory.select(qStation, qCecConnector, qOperator).from(qCecConnector).leftJoin(qStation).on(qStation.operatorId.eq(qCecConnector.operatorId).and(qCecConnector.stationId.eq(qStation.stationId))).leftJoin(qOperator).on(qCecConnector.operatorId.eq(qOperator.operatorId)).where(qCecConnector.operatorId.eq(operatorId).and(qCecConnector.connectorId.eq(connectorId))).fetchFirst();

• 把获取的数据直接生成bean
  • 说明：实体类不能采用内部类。并且只匹配对应字段（根据字段名），如没有则为空。如有多的字段忽略
  • 使用Projections.bean

public List<PersonIDCardDto> findByDTOUseBean(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.bean(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}这样返回的数据直接就是List<PersonIDCardDto>

• 使用Projections.fields

public List<PersonIDCardDto> findByDTOUseFields(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.fields(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}

• 使用Projections.constructor

实体类需要添加构造函数
public List<PersonIDCardDto> findByDTOUseConstructor(){Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());return queryFactory.select(Projections.constructor(PersonIDCardDto.class, QPerson.person.name, QPerson.person.address, QIDCard.iDCard.idNo)).from(QIDCard.iDCard, QPerson.person).where(predicate).fetch();}