通过 Bean Searcher 在数据库中对数据的排序 -云博客

通过 Bean Searcher 在数据库中对数据的排序

# 数据库 2024-05-05 13:11 0 33 来源：云博客

想要H5页面中实现升序,降序的排序功能 :

问题 :

　　点击排序按钮只可以降序,不能升序

原因 :

　　小于0时无法排序, 原因是返回值为 int 类型, 小于0的值变为0,所以无法排序

但是在解决这个问题同时发现了另外一个问题, 因为要做分页处理(每页数据为15条), 数据超过15条数据时, 那么排序只会排当前页查到的数据, 前面页码数据不参与排序

所以需要在数据库中排序而不能在代码中排序(在代码中排序需要将所有数据全部查询出来, 数据过大时会对数据产生影响,不建议使用)

代码 :

 1 @Secured(‘permitAll‘) 2  def piles(Integer page, Integer size, String searchTerm, String sort, String order, Boolean online) { 3 page = page ?: 0 4 size = size ?: 15 5 sort = sort ?: ‘income‘ 6 order = order ?: ‘desc‘ 7 def user = securityService.currUser 8 def piles = ChargePile.createCriteria().list { 9 eq ‘deleted‘, false10 if (online) {11 eq ‘status‘, ModelConst.CHARGE_PILE__STATUS__ONLINE12 } else {13 eq ‘status‘, ModelConst.CHARGE_PILE__STATUS__OFFLINE14  }15  chargeSite {16 eq ‘platform‘, PlatformHolder.loadPlatform()17 eq ‘status‘, ModelConst.CHARGE_SITE__STATUS__APPROVED18  agent {19 eq ‘user‘, user20  }21  }22 if (searchTerm) {23 searchTerm = ‘%‘ + searchTerm + ‘%‘24  or {25 ilike ‘pileNo‘, searchTerm26  chargeSite {27 ilike ‘name‘, searchTerm28  }29  }30  }31  projections {32 property ‘id‘33 property ‘pileNo‘34 property ‘location‘35 property ‘portCount‘36  chargeSite {37 property ‘name‘38 property ‘address‘39 property ‘addressDetail‘40  }41 property ‘status‘42 property ‘type‘43  }44  maxResults size45 firstResult page * size46 }.collect { Object[] it ->47 def pileId = it[0]48 def pile = ChargePile.load(pileId as Long)49 def pileReport = PileReport.createCriteria().get {50 eq ‘type‘, ModelConst.REPORT__TYPE__DATE51 eq ‘time‘, TimeUtils.getYesterdayyyyyMMdd()52 eq ‘chargePile‘, pile53 } as PileReport54  [55  id : pileId,56 pileNo : it[1],57 total : it[3],58 siteName: it[4],59 address : AddressUtils.getFullAddress(it[5] as District, it[6] as String).concat(it[2] as String),60 status : it[7],61 busy : it[7] == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.busyPortCount(it[0] as long, it[3] as int) : 0,62 broken : it[7] == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.brokenPortCount(it[0] as long, it[3] as int) : 0,63 avaliable : it[7] == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.avaliablePortCount(it[0] as long, it[3] as int) : 0,64 usedRate: pileReport ? String.format(‘%.2f‘, pileReport?.useRate * 100) : ‘0‘,　　　　　　　　　　// 排序字段65 income : pileReport ? String.format(‘%.2f‘, pileReport?.pileIncomeAmount / 100) : ‘0.00‘,　　 // 排序字段66  ]67  } as List68 def result = [69  piles: listSort(piles, sort, order),　　　　// 上述图片中方法70  online: countPiles(ModelConst.CHARGE_PILE__STATUS__ONLINE),71  offline: countPiles(ModelConst.CHARGE_PILE__STATUS__OFFLINE)72  ]73 render result as JSON

通过 Bean Searcher 在数据库中进行排序代码如下 :

 1 @SearchBean(tables = ‘‘‘charge_pile p 　　　　// sql语句 2 left join pile_report r on r.charge_pile_id = p.id and r.type = 1 and r.time = :time // on后面是对局部条件做限制  3 left join charge_site s on p.charge_site_id = s.id  4 left join agent a on s.agent_id = a.id ‘‘‘, 5 joinCond = ‘‘‘p.deleted = 0 and a.user_id = :userId and s.platform_id = :platformId and // joinCond 相当于在sql中的where, where后面是对全局做限制 6 (s.name like :search or p.pile_no like :search)‘‘‘)　　// 实现条件搜索(设备名称或设备编号) 7 class PileReportBean implements BeanAware { 8  9 @DbField(‘p.id‘)10  Long id11 12 @DbField(‘p.pile_no‘)13  String pileNo14 15 @DbField(‘p.location‘)16  String location17 18 @DbField(‘p.port_count‘)19  Integer portCount20 21 @DbField(‘p.status‘)22 int status23 24 @DbField(‘p.type‘)25 int type26 27 @DbField(‘s.name‘)28  String name29 30 @DbField(‘s.address_detail‘)31  String address32 33 @DbField(‘r.use_rate‘)34  BigDecimal useRate35 36 @DbField(‘r.pile_income_amount‘)37  Integer pileIncomeAmount38 39 40  @Override41 void afterAssembly() {42 43  }44 45 }

控制层代码 :

 1 @Secured(‘permitAll‘)　　//权限 所有人可以访问 2  def piles(Integer page, Integer size, String searchTerm, String sort, String orderMode, Boolean online) { 3 page = page ?: 0 4 size = size ?: 15 5 sort = sort ?: ‘pileIncomeAmount‘ 6 orderMode = orderMode ?: ‘desc‘ 7 def user = securityService.currUser 8 log.info("user:${user},") 9 def status = online ? 1 : 210 def params = [　　　　　　　　　　　　　　　　　　　　// sql的 wehre 条件11  platformId : PlatformHolder.loadPlatform().id,12  time : TimeUtils.getYesterdayyyyyMMdd(),13  userId : user.id,14  status : status,15 search : ‘%‘ +searchTerm + ‘%‘,　　// 搜索条件(通过前端传入)16  sort : sort,　　　　　　　　　　　　// 排序字段(前端传入)17  order : orderMode,　　　　　　　　 // 排序方式(desc or asc)18  max : size,19  offset : page20  ]21 22 def piles = searcher.search(PileReportBean, params)23 piles = piles.dataList?.collect { it ->24  [25  id : it.id,26  pileNo : it.pileNo,27 total : it?.portCount,28  siteName : it.name,29  address : it.address,30  status : it.status,31 busy : it.status == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.busyPortCount(it.id, it.portCount) : 0,32 broken : it.status == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.brokenPortCount(it.id, it.portCount) : 0,33 avaliable : it.status == ModelConst.CHARGE_PILE__STATUS__ONLINE ? pileCacheService.avaliablePortCount(it.id, it.portCount) : 0,34 useRate : it.useRate ? String.format(‘%.2f‘, it?.useRate * 100) : ‘0‘,35 pileIncomeAmount: it.pileIncomeAmount ? String.format(‘%.2f‘, it?.pileIncomeAmount / 100) : ‘0.00‘36  ]37  }38 def result = [39  piles: piles,40  online: countPiles(ModelConst.CHARGE_PILE__STATUS__ONLINE),41  offline: countPiles(ModelConst.CHARGE_PILE__STATUS__OFFLINE)42  ]43  render result as JSON44 }