jpa+querydsl实现将字符串转为数字进行排序

最新推荐文章于 2024-07-03 18:49:06 发布

糯米团子>.<

最新推荐文章于 2024-07-03 18:49:06 发布

阅读量3.3k

点赞数 1

分类专栏： jpa

本文链接：https://blog.csdn.net/weixin_42799440/article/details/103526973

版权

jpa 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在进行用jpa+querydsl开发时遇到一个问题：就是在创建表结构时存入的字段是String类型的，但是要根据这个字段进行降序排序,经过百度和各种实践测试，得出以下结论
第一步：首先创建实体bean:创建实体时需要字段名与表里的字段名保持一致，用 @Column(name=“row_id”)声明，@Id注明是主键

@Data
@Entity
@Table(name="reptile_cpu_record",schema="appuser")
public class CPUTime {
    @Id
    @Column(name="row_id")
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private String rowId;
    @Column
    private String cpuTime;
    @Column
    private String executions;
    @Column
    private String cpuPerExec;
    @Column
    private String total;
    @Column
    private String elapsedTime;
    @Column
    private String cpu;
    @Column
    private String io;
    @Column
    private String sqlId;
    @Column
    private String sqlMoudle;
    @Column
    private String sqlText;
    @Column
    private Date createDate;
}

第二步：创建reposity接口类，编译代码，生成Q开头的实体类。

import org.springframework.data.repository.PagingAndSortingRepository;
public interface ReptileCpuTiRepository extends PagingAndSortingRepository<CPUTime, String>{

}

第三步:创建service类，返回QueryResults：实现直接将查询的结果进行了分页。
将字符串转为number进行排序：
StringTemplate s = Expressions.stringTemplate("to_number({0})",qcpuTime.cpuTime); jpaQuery.orderBy(new OrderSpecifier<>(Order.ASC, s));

@Service
public class ReptileService extends AbstractService<CPUTime,String> {

    public ReptileService(PagingAndSortingRepository<CPUTime, String> pagingRepository) {
        super(pagingRepository);
    }
    
	//total是总数
	//offset是从哪开始
	//limit是限制条数
    public QueryResults<CPUTime> getReptileCpuTiList(String pageSize, String pageNum,String type){
        QCPUTime qcpuTime=QCPUTime.cPUTime;
        JPAQuery<CPUTime> jpaQuery = jpaQueryFactory().select(Projections.bean(
                CPUTime.class,qcpuTime.createDate.as("createDate"),
                qcpuTime.cpu.as("cpu"),
                qcpuTime.cpuPerExec.as("cpuPerExec"),
                qcpuTime.rowId.as("rowId"),
                qcpuTime.executions.as("executions"),
                qcpuTime.cpuTime.as("cpuTime"),
                qcpuTime.total.as("total"),
                qcpuTime.elapsedTime.as("elapsedTime"),
                qcpuTime.io.as("io"),
                qcpuTime.sqlId.as("sqlId"),
                qcpuTime.sqlMoudle.as("sqlMoudle"),
                qcpuTime.sqlText.as("sqlText")
             ))
                .from(qcpuTime);
        StringTemplate s = Expressions.stringTemplate("to_number({0})",qcpuTime.cpuTime);
        if("1".equals(type)){
            jpaQuery.orderBy(new OrderSpecifier<>(Order.ASC, s));
        }else if("2".equals(type)){
            jpaQuery.orderBy(new OrderSpecifier<>(Order.DESC, s));
        }else{
            jpaQuery.orderBy(qcpuTime.createDate.desc(),new OrderSpecifier<>(Order.DESC, s));
        }
        QueryResults<CPUTime> cpulist = jpaQuery
                .offset(Long.parseLong(pageSize) * (Long.parseLong(pageNum) - 1))
                .limit(Long.parseLong(pageSize))
                .fetchResults();
        return cpulist;
    }
}

在Controller中直接调用

@Slf4j
@RestController
@RequestMapping("/reptile")
public class ReptileController {
    @Autowired
    private ReptileService reptileService;
    @ApiOperation(value ="cpu列表",notes = "cpu列表查询" )
    @ApiImplicitParams({
            @ApiImplicitParam(name="pageSize", value  = "每页条数",required=true, dataType ="String"),
            @ApiImplicitParam(name="pageNum", value  = "当前页数",required=true, dataType ="String"),
            @ApiImplicitParam(name="type", value  = "排序类型",required=true, dataType ="String")
    })
    @GetMapping(value = "/cpuTi")
    @ResponseBody
    public QueryResults<CPUTime> findReptileCpuTiList(String pageSize,String pageNum,String type){
        QueryResults<CPUTime> cpuTiList = reptileService.getReptileCpuTiList(pageSize, pageNum, type);
        return cpuTiList;
    }
    }