JPA uses custom query Conditions

Tao sir: http://www.cnblogs.com/it-taosir/p/9874033.html

This means that the combination of like (fuzzy query) and limit (paging) queries in JPA is analyzed through the following example:

It can be understood as like, which is more efficient than like in fuzzy search.

Table creation: SQL

Create Table order_info (order_id varchar (30) not null, source_id varchar (30), serial_no varchar (30), source_system varchar (20), channel_mark varchar (5 ), cust_type varchar (4), cust_name varchar (60), goods_sku varchar (64), order_provinc_code varchar (6), order_city_code varchar (6), flow_node_code varchar (255 ), status int comment '0 unlocked 1 locked 2 pend', create_user_id bigint, create_dt datetime, update_dt datetime, visible_status varchar (4) Comment '0 visible 1 invisible ', lock_user_id bigint, lock_user_name varchar (64), claim_dt datetime, verify_flag smallint comment 'null unaudited, 0 automatic review failed 1 automatic review passed ', create_user_no varchar (64) Comment 'create employee ID ', create_user_name varchar (64), oper_user_id bigint, oper_user_no varchar (64) Comment 'process employee number', oper_user_name varchar (64), amount bigint, disacount bigint comment 'discount ant ', pay_money bigint, pay_type varchar (10) Comment 'hdfk cash on delivery, zxzf online payment ', payment_type varchar (10) Comment 'xjzf cash payment, posc pos Card swiping, etc ', pay_status smallint comment '0 unpaid 1 paid ', goods_name varchar (200), exception_type int, lock_user_no varchar (64), flow_code varchar (255), primary key (order_id ));

 

Entity class:

package cn.chinaunicom.srigz.orders.api.database.model;import java.io.Serializable;import java.util.Date;import java.util.List;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.OneToMany;import lombok.Data;import lombok.NoArgsConstructor;@Data@Entity@NoArgsConstructorpublic class OrderInfo implements Serializable {    private static final long serialVersionUID = 1063821955023696541L;    @Id    private String orderId;    private String sourceId;    private String serialNo;    private String sourceSystem;    private String channelMark;    private String custType;    private String custName;    private String goodsSku;    private String orderProvincCode;    private String orderCityCode;    private String flowNodeCode;    private Integer status;    private Long createUserId;    @Column(name = "create_dt", columnDefinition = "DATETIME")    private Date createDt;    @Column(name = "update_dt", columnDefinition = "DATETIME")    private Date updateDt;    private String visibleStatus;    private Long lockUserId;    private String lockUserName;    @Column(name = "claim_dt", columnDefinition = "DATETIME")    private Date claimDt;    private Integer verifyFlag;    private String createUserNo;    private String createUserName;    private Long operUserId;    private String operUserName;    private Long amount;    private Long disacount;    private Long payMoney;    private String payType;    private String paymentType;    private Integer payStatus;    private Integer exceptionType;    private String lockUserNo;    private String flowCode;    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY)    @JoinColumn(name = "orderId")    private List<GoodsInfo> goodsInfos;}

 

Repository: directly copied. In fact, you only need to inherit two interfaces.

package cn.chinaunicom.srigz.orders.api.database.dao;import java.util.List;import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.JpaSpecificationExecutor;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.transaction.annotation.Transactional;import cn.chinaunicom.srigz.orders.api.database.model.OrderInfo;public interface OrderRepository extends JpaRepository<OrderInfo,String>, JpaSpecificationExecutor<OrderInfo> {    @Query(value="SELECT * from order_info where lock_user_id=?1 AND status=1",nativeQuery=true)    List<OrderInfo> selectMyOrders(String lockUserId);    @Modifying    @Transactional    @Query(value="UPDATE order_info SET lock_user_id = NULL AND status = 0 WHERE order_id = ?1",nativeQuery = true)    void unlockOrderByOrderId(String orderId);}

 

Service: mainly the code implementation of this section

Use of specification objects. Use an anonymous internal class to override the predicate topredicate method,

In the topredicate method, set the query conditions as follows:

The findall overload method is used for paging query.

/*** Custom condition query * page */@ suppresswarnings ({"deprecation", "serial"}) Public page <orderinfo> findbypageandparams (orderinfo Params, int pagesize, int pagenumber) {pageable = new pagerequest (pagenumber, pagesize); specification <orderinfo> orderinfo = new specification <orderinfo> () {// rewrite the query condition @ override public predicate topredicate (root <orderinfo> root, criteriaquery <?> Query, criteriabuilder) {// the query condition is set to path <string> sourcesystem = root. get ("sourcesystem"); Return criteriabuilder. like (sourcesystem, "%" + Params. getsourcesystem () + "%") ;}}; return orderrepository. findall (orderinfo, pageable );}

 

Controller: The Page Object encapsulates a lot of data. We only need to obtain the required data.

/*** @ Param orderinfo * @ Param pagesize * @ Param pagenum * @ return */@ apioperation (value = "query by topic resource matching", notes = "sourcesystem ") @ requestmapping (value = "/API/V1/orders/findbysourcesystem", method = requestmethod. post) Public object findbysourcesystem (@ apiparam (required = true, value = "meets sourcesystem conditions") @ requestbody orderinfo, @ apiparam (required = true, value = "number of entries per page") @ requestparam int pagesize, @ apiparam (required = true, value = "query page") @ requestparam int pagenum) {page <orderinfo> page = orderservice. findbypageandparams (orderinfo, pagesize, pagenum); List <orderinfo> List = page. getcontent (); Return actionhelper. responseok (list );}

 

JPA uses custom query Conditions