SpringdataJPA使用@query注解完成条件分页查询原生SQL遇到的问题
1.先说一下前端到后端的流程
基本功能就是页面展示table数据,通过分页的方式
页面写的展示表字段
默认的查询数据,有页数,每页数据量,以及客户id,默认情况就是点击这个页面进来后响应展示数据.
vue页面
<template>
<div class="content">
<div class="row">
<div class="col-md-12">
<div class="box">
<el-form :inline="true" :model="listQuery" class="demo-form-inline" label-width="75px">
<el-form-item label="客户号" prop="customerid">
<el-input v-model.trim="listQuery.customerid" placeholder="" clearable style="width: 200px;"></el-input>
</el-form-item>
<el-form-item>
<button class="btn btn-default" type="button" @click="listSearch">查询</button>
<el-upload
class="upload-demo"
action=""
accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
:http-request="uploadFile"
:show-file-list="false"
multiple>
<button class="btn btn-import bg-green" type="button">导入</button>
</el-upload>
</el-form-item>
</el-form>
<!-- 这个是表信息-->
<div class="box-body">
<div class="table-responsive">
<el-table
:key="tableKey"
v-loading="listLoading"
:data="list"
border
fit
stripe
highlight-current-row
style="width: 100%;"
>
<el-table-column
label="客户号"
prop="customerid"
align="left"
min-width="200">
</el-table-column>
<el-table-column
label="券码号"
prop="couponid"
align="left"
min-width="240">
</el-table-column>
<!-- <el-table-column-->
<!-- label="服务使用状态"-->
<!-- prop="servicestatus"-->
<!-- align="left"-->
<!-- min-width="240">-->
<!-- </el-table-column>-->
<!-- <el-table-column-->
<!-- label="使用通道"-->
<!-- prop="serviceChannel"-->
<!-- align="left"-->
<!-- min-width="240">-->
<!-- </el-table-column>-->
<!-- <el-table-column-->
<!-- label="项目供应商"-->
<!-- prop="supplierName"-->
<!-- align="left"-->
<!-- min-width="100">-->
<!-- </el-table-column>-->
<!-- <el-table-column-->
<!-- label="状态"-->
<!-- align="left"-->
<!-- min-width="80">-->
<!-- <template slot-scope="scope">-->
<!-- <span>{{scope.row.serviceStatus == 0 ? '正常':'暂停'}}</span>-->
<!-- </template>-->
<!-- </el-table-column>-->
<!-- <el-table-column label="操作" align="left" class-name="small-padding fixed-width" width="110">-->
<!-- <template slot-scope="scope">-->
<!-- <el-button class="btn1" @click="handleEditRow(scope.row)" type="text">修改</el-button>-->
<!-- <el-button class="btn1" @click="handleDeleteRow(scope.row)" type="text">删除</el-button>-->
<!-- </template>-->
<!-- </el-table-column>-->
</el-table>
</div>
</div>
<div class="box-footer clearfix">
<!-- 控制分页模块 -->
<pagination align="right" v-show="total>0" :total="total" :page.sync="listQuery.pageNum" :limit.sync="listQuery.pageSize" @pagination="getList" />
</div>
</div>
</div>
</div>
</div>
</template>
<script>
import { getPageService } from '@/api/couponid/couponid'
// import { getSupplier } from '@/api/supplierManagement/supplier'
import { upLoadcouponidDef } from '@/api/common'
import { parseTime } from '@/utils/ruoyi'
import Pagination from '@/components/Pagination'
export default {
name: 'Projects',
components: { Pagination },
data() {
return {
tableKey: 0,
title: '添加项目',
list: null,
total: 0,
listLoading: false,
listQuery: {
pageNum: 1,
pageSize: 10,
customerid: null
},
listRow: {
customerid:null,
couponid:null
}
}
},
created() {
this.getList()
// this.list = this.pageInfo.list
// this.total = this.pageInfo.total
},
methods: {
getList() {
this.listLoading = true
getPageService(this.listQuery).then(response => {
this.listLoading = false
this.list = response.content
this.total = response.totalElements
})
.catch(() => {
this.listLoading = false
})
},
reset() {
this.listQuery = {
pageNum: 1,
pageSize: 10,
customerid: null
}
},
//查询
listSearch() {
this.listQuery.pageNum = 1
this.getList()
},
// 导入
uploadFile(item) {
const form = new FormData();
form.append('file', item.file);
upLoadcouponidDef(form).then(res => {
if (res.success == 'SUCCESS') {
this.msgSuccess('导入成功!')
this.reset()
this.getList(); //导入成功刷新列表
}else if (res.success == 'FALSE'){
this.msgError(res.message)
}
}).catch(err => {
this.msgError(err)
})
},
// 关闭
closeWriteoOffVisible() {
this.projectVisible = false
},
// 保存
// saveService(formName){
// this.$refs[formName].validate((valid) => {
// if (valid) {
// if (this.listRow.serviceID) {
// updateService(this.listRow).then(res=>{
// this.msgSuccess('修改成功')
// this.projectVisible = false
// this.reset()
// this.getList()
// })
// }else{
// saveService(this.listRow).then(res=>{
// this.msgSuccess('添加成功')
// this.projectVisible = false
// this.reset()
// this.getList()
// })
// }
// } else {
// console.log('error submit!!');
// return false;
// }
// });
// },
}
}
</script>
<style lang="scss" scoped>
::v-deep{
.el-table thead{
color: #888888;
font-size: 13px;
}
.el-table tr{
color: #888888;
font-size: 13px;
}
.el-table--medium th{
padding: 8px 0;
}
.el-table--medium td{
padding: 8px 0;
}
.btn1{
color: #37A8AF;
font-size: 13px;
}
.el-table--striped .el-table__body tr.el-table__row--striped td{
background: #f3f4f5;
}
.el-pagination{
padding: 0;
margin-right: -4px;
}
.pagination-container{
padding: 0;
margin: 0;
}
.el-dialog__header{
display: none;
}
.el-dialog__body{
padding: 25px 30px 60px;
font-size: 13px;
color: #888888;
}
.modal-body{
padding: 20px;
}
.el-dialog{
width: 95%;
margin-top: 10vh !important;
}
.el-form-item{
margin-bottom: 15px;
}
.el-input__inner{
color: #555555;
border: 1px solid #cccccc;
}
.el-select-dropdown {
position: absolute !important;
top: 30px !important;
left: 0 !important;
}
}
/deep/.el-form-item__error{
padding-top: 1px;
}
.demo-form-inline{
padding: 40px 0 0 35px;
}
.box{
margin-bottom: 0;
}
.box-body{
padding-top: 0;
}
.btn-default{
margin-right: 10px;
}
.btn-import{
margin-right: 10px;
}
.close-btn{
margin-right: 0px;
margin-left: 10px;
}
.upload-demo{
display: inline-block;
}
</style>
就是点击页面后会执行getList()方法,点击查询按钮也会执行这个方法,重置也会执行这个方法,
这个getList方法
会去找这个路径,
到js页面,也就是'@/api/couponid/couponid' couponid是js文件
// 获取服务分页列表
export function getPageService(query) {
return request({
url: '/couponid/getPagecouponid',
method: 'get',
params: query
})
}
根据上面的路径就会去找对应后台控制器层上面路径进行匹配,后台控制器层,前端参数传过来的,CouponidModel是前端查询条件信息,一般也跟表字段信息保持一致.分页查询默认情况是查询第一页,默认条数是10.
@RequestMapping("/getPagecouponid")
public Page<Cuscouponinfo> getPageService(CouponidModel couponidmodel, int pageNum, int pageSize){
System.out.println("走这里");
return couponidservice.findAll(couponidmodel,pageNum,pageSize);
}
执行service层
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
//上面是使用的包
@Transactional
public Page<Cuscouponinfo> findAll(CouponidModel couponidmodel, int pageNum, int pageSize) {
//如果客户好是空的，设置客户号为空
System.out.println("couponidmodel.getCustomerID():"+couponidmodel.getCustomerid());
//这里是判断页面查询条件是不是空的,就是这个输入框是空的,用户没有选择
if("".equals(couponidmodel.getCustomerid())){
couponidmodel.setCustomerid(null);
}
if(StringUtils.isNoneEmpty(couponidmodel.getCustomerid())) {
couponidmodel.setCustomerid("%"+couponidmodel.getCustomerid().trim()+"%");
}
System.out.println("?????????jintian hesuan ");
String custoemrid = couponidmodel.getCustomerid();
Pageable pageRequest = new PageRequest(pageNum-1,pageSize, Sort.Direction.DESC,"customerid");
Page<Cuscouponinfo> pageList= couponidrepository.getinfo(custoemrid,pageRequest);
System.out.println(pageList);
return pageList;
}
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface CouponidRepository extends JpaRepository<Cuscouponinfo,Long> {
@Query(value = "SELECT c.id,case c.customerid when '1' then '' else c.customerid end customerid,c.couponid,c.serviceid,c.businesscode from cus_coupon_info c where 1=1 " +
"and ( :customerid is null or customerid like :customerid ) ",
countQuery ="SELECT count(*) from cus_coupon_info where 1=1 " +
"and ( :customerid is null or customerid like :customerid ) ",
nativeQuery = true)
Page<Cuscouponinfo> getinfo(@Param("customerid") String customerid, Pageable pageRequest);
}
这是正确的写法,但是我对这个框架是不了解的,我之前是没有写countQuery="" 这个的,然后去搜索了一下@Query注解的作用,因为我们这里使用的是分页功能,所以countQuery=""里面的内容是必须要写的,原理我也不太了解.这里就先记录下,不写就会报错.
2.@Query中countQuery的介绍
@Query源码
/*
* Copyright 2008-2020 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* https://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.data.jpa.repository;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import org.springframework.data.annotation.QueryAnnotation;
/**
* Annotation to declare finder queries directly on repository methods.
*
* @author Oliver Gierke
* @author Thomas Darimont
* @author Christoph Strobl
*
* @see Modifying
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD, ElementType.ANNOTATION_TYPE })
@QueryAnnotation
@Documented
public @interface Query {
/**
* Defines the JPA query to be executed when the annotated method is called.
*/
String value() default "";
/**
* Defines a special count query that shall be used for pagination queries to lookup the total number of elements for
* a page. If none is configured we will derive the count query from the original query or {@link #countProjection()} query if any.
*/
String countQuery() default "";
/**
* Defines the projection part of the count query that is generated for pagination. If neither {@link #countQuery()}
* nor {@link #countProjection()} is configured we will derive the count query from the original query.
*
* @return
* @since 1.6
*/
String countProjection() default "";
/**
* Configures whether the given query is a native one. Defaults to {@literal false}.
*/
boolean nativeQuery() default false;
/**
* The named query to be used. If not defined, a {@link javax.persistence.NamedQuery} with name of
* {@code $ domainClass}.${queryMethodName}} will be used.
*/
String name() default "";
/**
* Returns the name of the {@link javax.persistence.NamedQuery} to be used to execute count queries when pagination is
* used. Will default to the named query name configured suffixed by {@code .count}.
*
* @see #name()
* @return
*/
String countName() default "";
}
注释@Query提供了某些计数属性，如countQuery，countQueryName。
定义一个特殊的count查询，用于分页查询时，查找页面元素的总个数。如果没有配置，将根据方法名派生一个count查询。
countQuery用于分页，如果要求分页结果返回正确的总计数，@Query 注释可以在 countQuery 属性中与计数查询一起提供。此查询在结果查询之后单独执行，其结果用于填充返回的 Page 的 totalCount 属性。
注意这里记录一下我使用的场景,当我没有写countQuery="" 时,value="" 里面我写的是select * ,因为我测试的数据是少于10条的,不满足分页条件,所以没有报错,当数据库中超过10条数据时,就会引发报错,这个我当时是没有测试出来的,所以总结就是countQuery是必须要写的,这样可以有保证.
3.@Query中nativeQuery = true解释
原生sql: 所谓原生sql，也就是说这段sql拷贝到数据库中，然后把参数值给一下就能运行了
- 有nativeQuery = true时，是可以执行原生sql语句
@Query(value = "select * from product_rel where audit_id=?1 and process_object=0 ",nativeQuery = true)
List<ProductRel> findAllByProductAuditId(Integer id);
这个时候，把select * from product_rel where audit_id=?1 and process_object=0拷贝到数据库中，并给audit_id赋一个值，那么这段sql就可以运行。其中数据库表在数据库中的表名就是product_rel，字段audit_id在数据库中也是真实存在的字段名。要求必须为数据库中的表名和字段名,
2.没有nativeQuery = true时，就不是原生sql,@Query中value值的sql语句就只能按照规范来写。否则就会报错,规范就是：表名必须是所写的实体类名，字段名也必须是实体类中的自定义的变量名，
@Query("select ratio from MdmRatio where enabledNum=1 ")
List<MdmUtilThreeProjection> findByMdmUtilThreeProjection();
select ratio from MdmRatio中的MdmRatio为实体名，不是真正的数据库表名，真正的数据库表名是mdm_ratio，而查询条件中的enabledNum在数据库中真正的名字是enabled_num。
这两个的作用是一样的，只是写法不同。
参考:
https://blog.csdn.net/qq_34626094/article/details/122332931
https://blog.csdn.net/seanxwq/article/details/119803302
https://blog.csdn.net/listeningsea/article/details/122364597
https://blog.csdn.net/weixin_42043101/article/details/103600196
