spring JPA 中IN 的用法

原创已于 2022-05-03 21:11:27 修改 · 4.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

于 2022-05-03 10:29:17 首次发布

数据库专栏收录该内容

14 篇文章

订阅专栏

博客讨论了在JPA和MyBatis中构建复杂查询的差异，尤其是处理空值和子查询的情况。作者指出，JPA的SQL构建不如MyBatis灵活，对于大量数据的查询可能导致性能问题。文中给出了一种优化SQL查询的建议，即避免使用`UNION`以提高性能，并展示了如何在JPA中实现类似逻辑的SQL查询。此外，还提供了具体的JPA查询代码实现作为示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一些语法并不向mybatis那样灵活，写起来也是很费劲

1. 查询条件

比如：

要实现某个查询条件，要满足

1. 改字段为空的场景，

2. 该字段不为空，但是要根据该条件再过滤一次的场景

2. 常规的sql两种实现方法

① select * from a A where A.sys_id is null union select * from a A where A.sys_id is not null and A.sys_id in (select id from B where ..... );

② select * from a A where A.sys_id is null or A.sys_id in (select id from B where ..... );

以上两种sql对比，如果条件较少，①还可以，如果条件较多，并不怎么好写，同时性能

union要进行一次去重的操作。

我这个表大概50W条数据，而且不重复。所以系统每插入一条数据，就要走全表查一次索引，50W条数据就要查50W次，当然卡死。

提出建议：使用union all。这样就不会走去重的逻辑了。

因此想到了使用sql②，但是用jpa的sql语法并不像mybatis的写法那么灵活，该怎么写呢？

3 以下是jpa的sql

select ftagentpo0_.agentid as agentid1_1_, ftagentpo0_.clusterstate as clusters2_1_, ftagentpo0_.description as descript3_1_, ftagentpo0_.hport as hport4_1_, ftagentpo0_.agentip as agentip5_1_, ftagentpo0_.ismonitor as ismonito6_1_, ftagentpo0_.ismanually as ismanual7_1_, ftagentpo0_.agentname as agentnam8_1_, ftagentpo0_.password as password9_1_, ftagentpo0_.port as port10_1_, ftagentpo0_.priority as priorit11_1_, ftagentpo0_.serverid as serveri12_1_, ftagentpo0_.status as status13_1_, ftagentpo0_.sysid as sysid14_1_, ftagentpo0_.transferstatus as transfe15_1_, ftagentpo0_.updatetime as updatet16_1_, ftagentpo0_.agentver as agentve17_1_ from ft_agent ftagentpo0_ where ftagentpo0_.transferstatus=? and (ftagentpo0_.sysid is null or ftagentpo0_.sysid in (select ftsyspo1_.id from ft_sys ftsyspo1_ where ftsyspo1_.userid=?)) order by ftagentpo0_.sysid desc, ftagentpo0_.status asc, ftagentpo0_.agentid desc limit ?

4. 代码实现

protected Specification<FtAgentPO> createSpec(FtAgentPO.Criteria criteria,String type) {
        Specification<FtAgentPO> spec = (root, query, builder) -> {
            if (criteria == null) {
                return builder.and(new Predicate[]{});
            }
            List<Predicate> predicates = new ArrayList<Predicate>();
            Optional.ofNullable(criteria.getSysId()).ifPresent((value) -> predicates.add(builder.equal(root.get("sysId"), value)));
            Optional.ofNullable(criteria.getServerId()).ifPresent((value) -> predicates.add(builder.equal(root.get("serverId"), value)));
            Optional.ofNullable(criteria.getIp()).ifPresent((value) -> predicates.add(JPAPredicateHelper.like(builder, root.get("ip"), value)));
            Optional.ofNullable(criteria.getStatus()).ifPresent((value) -> predicates.add(builder.equal(root.get("status"), value)));
            Optional.ofNullable(criteria.getZone()).ifPresent((value) -> predicates.add(builder.equal(root.get("zone"), value)));
            Optional.ofNullable(criteria.getEnv()).ifPresent((value) -> predicates.add(builder.equal(root.get("env"), value)));
            Optional.ofNullable(criteria.getClusterState()).ifPresent((value) -> predicates.add(builder.equal(root.get("clusterState"), value)));
            if(type.equals("eq")){
                Optional.ofNullable(criteria.getName()).ifPresent((value) -> predicates.add(builder.equal(root.get("name"), value)));
            }else {
                Optional.ofNullable(criteria.getName()).ifPresent((value) -> predicates.add(JPAPredicateHelper.like(builder, root.get("name"), value)));
            }
            Optional.ofNullable(criteria.getTransferStatus()).ifPresent((value) -> predicates.add(builder.equal(root.get("transferStatus"), value)));
            if (criteria.getSysIdIsNull() != null) {
                if (criteria.getSysIdIsNull()) {
                    predicates.add(builder.isNull(root.get("sysId")));
                } else {
                    predicates.add(builder.isNotNull(root.get("sysId")));
                    handleUserSys(root, query, builder, predicates);
                }
            } else {
                handleUserSys(root, query, builder, predicates);
            }
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        return spec;
    }

   private void handleUserSys(Root<FtAgentPO> root, CriteriaQuery<?> query, CriteriaBuilder builder, List<Predicate> predicates) {
        if (!BFTModelHelper.checkAdmin()){
            String userId = BFTModelHelper.getUserId();

            Subquery<String> subsubquery = query.subquery(String.class);
            Root<FtSysPO> subsubroot = subsubquery.from(FtSysPO.class);
            subsubquery.select(subsubroot.get("id"));
            subsubquery.where(builder.equal(subsubroot.get("userId"), userId));

            Predicate managePredicate = builder.or(
                    builder.isNull(root.get("sysId")),
                    root.get("sysId").in(subsubquery)
            );
            predicates.add(managePredicate);
        }
    }

主要是以上的sql写法。