Transfer Mode of in Condition Parameters in Spring Data JPA

in Parameter Record of Custom sql Condition in Spring Data JPA

Make a record of this article for future viewing. I also hope that students who are encountering the same problem can be inspired. If you have a better method or my practice has problems, please let me know. Thank you very much.

Here are two simple ways to do it, pay attention to the following places:

Here I only record HQL in behind the parameter problem, details of the query method related code skipped, but also forgive me

Query interface:

/**
** paramString :  Customize  hql
** paramMap : hql Parameters of query criteria in 
** start Number of data starts 
** max : Maximum number of data pieces 
**/
public abstract List query(String paramString, Map paramMap, int start, int max);

Practice 1: Splicing conditions

No matter how many parameters there are in parentheses after in, using dynamic splicing, the splicing HQL process will not be coded, resulting in the following style:

HQL

String hql =  " select obj from Goods obj where obj.id in(:id_1,:id_2,id_3 ... ) "  ;

Query operation

Map<String, Long> paramMap = new HashedMap();
paramMap.put( " id_1 " ,Long.valueOf(4));
paramMap.put( " id_2 " , Long.valueOf(5));
~~~~~~~~~  Omit multiple parameter splicing  ~~~~~~~~~~~~~~~~~~
List<Goods> goods = goodsService.query(hql , paramMap, 1, 100);

Data can also be queried by using this dynamic splicing method, but there are 1 point and 1 point to note. Everyone knows that Object, as a superclass, can be used as a large generic type, but here, if it is a generic type of paramMap, when adding parameter values, if the attribute type of id is in the entity object, 1 must convert the past eg:

paramMap.put( " id_1 " ,Long.valueOf(4));  ----- ( Because my id  Yes  Long  Type )

Practice 2: Use collections

HQL

String hql =  " select obj from Goods obj where obj.id in(:id) " ;

Query operation of:

Map<String, Object> paramMap = new HashedMap();
ArrayList<Object> list = new ArrayList<>();
list.add(Long.valueOf(4));
list.add(Long.valueOf(5));
paramMap.put( " id " , list);
List<Goods> goods = goodsService.query( " select obj from Goods obj where obj.gc.id in(:id) " , paramMap, -1, -1);

The previous practice is, but there seems to be nothing wrong with writing like this. After all, Object is a superclass, and one Long can still be matched. The compilation will not be abnormal, but after running, when you visit this piece, there will be problems, and the specific anomaly will be subsidized. You can try it yourself:

Map<String, Object> paramMap = new HashedMap();
ArrayList<Object> list = new ArrayList<>();
list.add(4);
list.add(5);
paramMap.put( " id " , list);
List<Goods> goods = goodsService.query( " select obj from Goods obj where obj.gc.id in(:id) " , paramMap, -1, -1);

Note again here:

In hql, you must pay attention to the type of condition field when transferring parameters. If the generic type is a large type, when adding values, you must convert the corresponding type

To this, the above are two kinds of HQL ideas, but because I am doing Android, the company does not have Java background development, which also bothers me for a while.

However, I found a solution on the Internet, and some people said that this parameter should be written as Collection < > In fact, small data types are also possible. After all, this Collection, as a large interface of Java, is still relatively rare.

Spring Data JPA in Query Example

    /**
     *  Query all customers 
     *
     * @param customer
     * @return
     */
    @SuppressWarnings("unchecked")
    @RequestMapping(method = RequestMethod.POST, value = "/findAll")
    public @ResponseBody
    Iterable<Customer> findAll(@RequestBody Customer customer) {       
        Pageable pageable = new PageRequest(customer.getPageNumber(), customer.getSize(), Sort.Direction.DESC,
                "creationTime");
        Specification<Customer> specification = new Specification<Customer>() {
            @Override
            public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                List<Predicate> list = new ArrayList<Predicate>();                
                //  Owning user 
                String userId = customer.getUserId();
                if (!StringUtils.isEmpty(userId)) {
                    if (userId.contains(",")) { // in Query 
                        String[] ids = userId.split(",");
                        CriteriaBuilder.In<Object> in = cb.in(root.get("userId"));
                        for (int i = 0; i < ids.length; i++) {
                            in.value(ids[i]);
                        }
                        list.add(in);
                    } else { //  General inquiry 
                        list.add(cb.equal(root.get("userId").as(String.class), userId));
                    }
                }
                return cb.and(list.toArray(new Predicate[list.size()]));
            }
        };
        return this.customerRepository.findAll(specification, pageable);
    }