JPA Query in 集合（:和?传参）

使用 :param的方式来传递参数，下面举个例子

@PersistenceContext
​​​​​​​EntityManager em

 

@Override
    public List<Map> ajaxRelationByCompanyUUID(String companyUUID, String ajaxQuery,List<Integer> types, Integer fetchSize) {
        String sql = "select * from Company where companyuuid= :companyuuid" +
                " and CompanyType in (:types)";
        Map<String, Object> params = new HashMap<String, Object>();
//公司uuid
        params.put("companyuuid",companyUUID);
//公司类型（集合）
        params.put("types",types);
//模糊查询
        if (StringUtils.isNotBlank(ajaxQuery)) {
            sql += " and (CompanyNameCN like :ajaxQuery )";
            params.put("ajaxQuery" ,"%" + ajaxQuery + "%");
        }
        Query query = em.createNativeQuery(sql);
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        return super.exectSqlMap(query);
    }

 exectSqlMap:

protected List<Map> exectSqlMap(Query query) {
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

 

或者

public SysAppversion GetApp(String appCode, BigDecimal appVersion)
    {
        String sql = "select * from sys_appversion where AppCode= ? and AppVersion> ? order by AppVersion desc limit 1 ";

        List<Object> params = ListUtil.initList(Object.class);
        params.add(appCode);
        params.add(appVersion);

        Query query = dao.getCurSession().createSQLQuery(sql).addEntity(SysAppversion.class);
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i, params.get(i));
        }
        if(query!=null)
            return (SysAppversion)query.list().get(0);
        return null;
    }

 

使用@Query注解

@Query(value = "select count(1) from wms_inboundorder " +
            " where State in (:states)" +
            " and IsValid = :isValid", nativeQuery = true)
    Integer findWmsInboundorderCount(@Param(value = "states") List<Integer> states, @Param(value = "isValid") Integer isValid);