动态拼接字符串查询,超详细附源码(nativesql)

1、entity层

// order.java

@Entity
@Data
@Table(name = "order")
@DynamicUpdate
public class Order {
    
     /**
     * 主键(订单号id)，自己设置
     */
    @Id
    private Long id;
    
     /**
     *商品名称
     */
    @Column(name = "product_name")
    private String productName;

     /**
     * 商品数量
     */
    @Column(name = "product_size")
    private Integer productSize;
    
     /**
     * 商品单价
     */
    @Column(name = "product_price")
    private BigDecimal productPrice;
    
     /**
     * 买家姓名
     */
    @Column(name = "buyer_name")
    private String buyerName;
    
     /**
     * 买家电话
     */
    @Column(name = "buyer_phone")
    private String buyerPhone;
    
    /**
     * 买家id
     */
    @Column(name = "buyer_id")
    private Long buyerId;

    /**
     * 创建时间
     */
    @Column(name = "create_time")
    private Date createTime;

    /**
     * 更新时间
     */
    @Column(name = "update_time")
    private Date updateTime;

    /**
     * 乐观锁
     */
    @JsonIgnore
    @Version
    @Column(name = "version")
    private int version;

    @PreUpdate
    protected void onUpdate() {
        updateTime = new Date();
    }

    @PrePersist
    protected void onCreate() {
        createTime = new Date();
        updateTime = new Date();
    }
}

2、service层

//  OrderService.java

@Service
public class OrderService {

    @PersistenceContext
    private EntityManager entityManager;
    
    public Page<Map<String, Object>> orderList(UploadOrderSearchVo vo) {
        Pageable pageable = PageRequest.of(vo.getPage(), vo.getSize());
        //查询sql
        StringBuilder nativeQuerySql = new StringBuilder(" select id, product_name as productname, product_price as productprice, product_size as productsize, buyer_name as buyername, buyer_phone as buyerphone, create_time as createtime from order ");
        //统计总数的sql
        StringBuilder nativeCountSql = new StringBuilder("SELECT COUNT(*) FROM ( select count(id) from order ) as x  ");
        StrBuilder strBuilder1 = new StrBuilder();
        StrBuilder strBuilder2 = new StrBuilder();
        strBuilder1.append(nativeQuerySql);
        strBuilder2.append(nativeCountSql);
       
        Map<String, Object> properties = Maps.newHashMap();

        //收件人姓名查询
        if (vo.getBuyerName() != null) {
            vo.setBuyerName("%" + vo.getBuyerName() + "%");
            properties.put("buyerName", vo.getBuyerName());
            strBuilder1.append(" and  buyer_name LIKE :buyerName");
            strBuilder2.append(" and  buyer_name LIKE :buyerName");
        }

        //收件人手机号查询
        if (vo.getBuyerPhone() != null) {
            vo.setBuyerPhone("%" + vo.getBuyerPhone() + "%");
            properties.put("buyerPhone", vo.getBuyerPhone());
            strBuilder1.append(" and  buyer_phone LIKE :buyerPhone");
            strBuilder2.append(" and  buyer_phone LIKE :buyerPhone");
        }

        //商品名称查询
        if (vo.getProductName() != null) {
            vo.setProductName("%" + vo.getProductName() + "%");
            properties.put("productName", vo.getProductName());
            strBuilder1.append(" and  product_name LIKE :productName");
            strBuilder2.append(" and  product_name LIKE :productName");
        }

        //订单号查询
        if (vo.getId() != null) {
            vo.setId("%" + vo.getId() + "%");
            properties.put("orderId", vo.getId());
            strBuilder1.append(" and  id LIKE :orderId");
            strBuilder2.append(" and  id LIKE :orderId");
        }

        //添加having
        strBuilder1.append("  having  1 = 1 ");
        strBuilder2.append("  having  1 = 1 ");

        //下单时间查询
        if (vo.getStartTime() != null && vo.getEndTime() != null) {
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(vo.getEndTime());
            calendar.add(Calendar.DAY_OF_MONTH, 1);
            properties.put("start", vo.getStartTime());
            properties.put("end", calendar.getTime());
            strBuilder1.append(" and  create_time  >=  :start   and  create_time  <=  :end");
            strBuilder2.append(" and  create_time  >=  :start   and  create_time  <=  :end");
        }

        //添加假名
        strBuilder2.append("  ) as X ");

        strBuilder1.append(" order by create_time DESC ");

        Query dataQuery = entityManager.createNativeQuery(strBuilder1.toString());
        Query countQuery = entityManager.createNativeQuery(strBuilder2.toString());

        for (String s : properties.keySet()) {
            dataQuery.setParameter(s, properties.get(s));
            countQuery.setParameter(s, properties.get(s));
        }

        //分页
        dataQuery.setFirstResult((int) pageable.getOffset());
        dataQuery.setMaxResults(pageable.getPageSize());

        //将查询结果转换为Map
        dataQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        //获取总条数
        long totalEles = countQuery.getResultList() == null || countQuery.getResultList().size() == 0 ? 0 : Long.parseLong(countQuery.getResultList().get(0).toString());

        List<Map<String, Object>> resultList = dataQuery.getResultList();

        return new PageImpl<>(resultList, pageable, totalEles);
    }

}

3、vo层

//  UploadOrderSearchVo.java

@Data
public class UploadOrderSearchVo {

    private Date startTime; //开始时间

    private Date endTime; //结束时间

    private String buyerName;  //收件人姓名

    private String buyerPhone;  //收件人手机号

    private String productName;  //商品名称

    private Long id;  //订单号

    private Integer page;

    private Integer size;
}

4、controller层

//  OrderController

@RestController
@RequestMapping("/v1/order")
public class OrderController {
    private final OrderService orderService;

    public OrderController(OrderService orderService) {
        this.orderService = orderService;
    }

     /**
     * 订单列表
     */
    @ApiOperation(value = "订单列表")
    @PostMapping("/list")
    public Resp<Object> list(@RequestBody UploadOrderSearchVo vo) {
        return Resp.success(orderService.list(vo));
    }//此处我用的是自己封装的返回体
 }   

联表查询的查询方式与此类似，只是left join几张表而已，后期我也会更新多表的动态查询。