jpa limit查询_jpa使用自定义查询条件

意思就是jpa中使用类似like(模糊查询)和 limit (分页)查询结合，通过以下例子解析：

可理解为like，实际效率是比模糊查询like效率高的

建表：sql

create tableorder_info

(

order_idvarchar(30) not null,

source_idvarchar(30),

serial_novarchar(30),

source_systemvarchar(20),

channel_markvarchar(5),

cust_typevarchar(4),

cust_namevarchar(60),

goods_skuvarchar(64),

order_provinc_codevarchar(6),

order_city_codevarchar(6),

flow_node_codevarchar(255),

statusint comment ‘0 未锁定 1 锁定 2挂起‘,

create_user_idbigint,

create_dtdatetime,

update_dtdatetime,

visible_statusvarchar(4) comment ‘0 可见 1 不可见‘,

lock_user_idbigint,

lock_user_namevarchar(64),

claim_dtdatetime,

verify_flagsmallint comment ‘null 未审核，0 自动审核未通过 1 自动审核通过‘,

create_user_novarchar(64) comment ‘创建人工号‘,

create_user_namevarchar(64),

oper_user_idbigint,

oper_user_novarchar(64) comment ‘处理人工号‘,

oper_user_namevarchar(64),

amountbigint,

disacountbigint comment ‘优惠金额‘,

pay_moneybigint,

pay_typevarchar(10) comment ‘HDFK 货到付款，ZXZF 在线支付‘,

payment_typevarchar(10) comment ‘XJZF 现金支付,POSC POS机刷卡等‘,

pay_statussmallint comment ‘0 未支付 1 已支付‘,

goods_namevarchar(200),

exception_typeint,

lock_user_novarchar(64),

flow_codevarchar(255),primary key(order_id)

);

实体类：

packagecn.chinaunicom.srigz.orders.api.database.model;importjava.io.Serializable;importjava.util.Date;importjava.util.List;importjavax.persistence.CascadeType;importjavax.persistence.Column;importjavax.persistence.Entity;importjavax.persistence.FetchType;importjavax.persistence.Id;importjavax.persistence.JoinColumn;importjavax.persistence.OneToMany;importlombok.Data;importlombok.NoArgsConstructor;

@Data

@Entity

@NoArgsConstructorpublic class OrderInfo implementsSerializable {private static final long serialVersionUID = 1063821955023696541L;

@IdprivateString orderId;privateString sourceId;privateString serialNo;privateString sourceSystem;privateString channelMark;privateString custType;privateString custName;privateString goodsSku;privateString orderProvincCode;privateString orderCityCode;privateString flowNodeCode;privateInteger status;privateLong createUserId;

@Column(name= "create_dt", columnDefinition = "DATETIME")privateDate createDt;

@Column(name= "update_dt", columnDefinition = "DATETIME")privateDate updateDt;privateString visibleStatus;privateLong lockUserId;privateString lockUserName;

@Column(name= "claim_dt", columnDefinition = "DATETIME")privateDate claimDt;privateInteger verifyFlag;privateString createUserNo;privateString createUserName;privateLong operUserId;privateString operUserName;privateLong amount;privateLong disacount;privateLong payMoney;privateString payType;privateString paymentType;privateInteger payStatus;privateInteger exceptionType;privateString lockUserNo;privateString flowCode;

@OneToMany(cascade= CascadeType.ALL,fetch =FetchType.LAZY)

@JoinColumn(name= "orderId")private ListgoodsInfos;

}

Repository：直接拷贝了，其实只要继承两个接口即可

packagecn.chinaunicom.srigz.orders.api.database.dao;importjava.util.List;importorg.springframework.data.jpa.repository.JpaRepository;importorg.springframework.data.jpa.repository.JpaSpecificationExecutor;importorg.springframework.data.jpa.repository.Modifying;importorg.springframework.data.jpa.repository.Query;importorg.springframework.transaction.annotation.Transactional;importcn.chinaunicom.srigz.orders.api.database.model.OrderInfo;public interface OrderRepository extends JpaRepository, JpaSpecificationExecutor{

@Query(value="SELECT * from order_info where lock_user_id=?1 AND status=1",nativeQuery=true)

ListselectMyOrders(String lockUserId);

@Modifying

@Transactional

@Query(value="UPDATE order_info SET lock_user_id = NULL AND status = 0 WHERE order_id = ?1",nativeQuery = true)voidunlockOrderByOrderId(String orderId);

}

service：主要是这块的代码实现

涉及Specification对象的使用，使用匿名内部类重写Predicate toPredicate方法，

在toPredicate方法里按如下例子，设置查询的条件

最终使用findAll的重载方法进行分页查询

/*** 自定义条件查询

* 分页*/@SuppressWarnings({"deprecation", "serial"})public Page findByPageAndParams(OrderInfo params, int pageSize, intpageNumber) {

Pageable pageable= newPageRequest(pageNumber, pageSize);

Specification orderInfo=new Specification() {//重写查询条件

@Overridepublic Predicate toPredicate(Root root, CriteriaQuery>query,

CriteriaBuilder criteriaBuilder) {//查询条件设置为：字段source_system中包含的条件查询

Path sourceSystem = root.get("sourceSystem");return criteriaBuilder.like(sourceSystem, "%"+params.getSourceSystem()+"%");

}

};returnorderRepository.findAll(orderInfo, pageable);

}

controller：Page对象封装了很多数据，我们只要取所需的即可

/***@paramorderInfo

*@parampageSize

*@parampageNum

*@return

*/@ApiOperation(value="按主题资源匹配查询", notes="SourceSystem")

@RequestMapping(value= "/api/v1/orders/findBySourceSystem" ,method =RequestMethod.POST)publicObject findBySourceSystem(

@ApiParam(required= true, value = "符合SourceSystem的条件") @RequestBody OrderInfo orderInfo,

@ApiParam(required= true, value = "每页条数") @RequestParam intpageSize,

@ApiParam(required= true, value = "查询第几页") @RequestParam intpageNum

){

Page page=orderService.findByPageAndParams(orderInfo,pageSize,pageNum);

List list=page.getContent();returnActionHelper.responseOk(list);

}