SpringBoot Jpa 实现复杂的动态sql查询

逃离我推掉我的手 2023-03-02 07:56 46阅读 0赞

## SpringBoot Jpa 实现复杂的动态sql查询 ##

这里说一下如何利用Jpa 实现复杂的动态sql查询，关于Jpa的介绍我就不多说了，相信小伙伴们都知道，好了，不多说，咱们直奔主题。

我这里会贴出关键部分代码以及说明：  
我的`SpringBoot版本是2.3.1`，不同版本可能会有那么一点点差异，但是大同小异啦，稍微修改一下就好。

##### 一 首先是实体类 #####

@Data
    @Entity
    @Table(name = "master_user")    //设置表名，不设置则默认下划线分隔开
    public class User { 
    
        /** * 设置userId主键自增，数据库字段默认下划线分隔开 userId对应数据路字段user_id */
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer userId;
    
        /** * 设置字段不能为空且唯一，后面指定字段对应mysql的数据类型以及说明 */
        @Column(nullable = false, unique = true, columnDefinition = "varchar(32) COMMENT '用户名'")
        private String username;
    
        @Column(nullable = false, columnDefinition = "tinyint(4) COMMENT '性别 0男 1女'")
        private Integer gender;
    
        @Column(nullable = false)
        private Integer age;
    
    }

##### 二 直接看查询 #####

我这里直接贴出service实现层，通过`Specification`实现动态查询，`Root即root` 获取实体类具体属性, `CriteriaBuilder即cb` 拼接查询条件的，拼接好查询条件之后，通过 `CriteriaQuery即query` 实现查询，如下所示：

@Override
    	public Page<User> findAllBySf(Integer gender, Integer age, String username, Integer pageNo, Integer pageSize) { 
            //动态查询
            Specification<User> sf = (Specification<User>) (root, query, cb) -> { 
                //用于添加所有查询条件
                List<Predicate> p = new ArrayList<>();
                if (null != gender) { 
                    Predicate p1 = cb.equal(root.get("gender").as(Integer.class), gender);
                    p.add(p1);
                }
                if (null != age) { 
                    Predicate p2 = cb.greaterThan(root.get("age").as(Integer.class), age);
                    p.add(p2);
                }
                if (!StringUtils.isEmpty(username)) { 
                    Predicate p3 = cb.like(root.get("username").as(String.class), "%" + username + "%");
                    p.add(p3);
                }
                Predicate[] pre = new Predicate[p.size()];
                Predicate and = cb.and(p.toArray(pre));     //查询条件 and
                //Predicate or = cb.or(p.toArray(pre)); //查询条件 or
                query.where(and);       //添加查询条件
    
                //设置排序
                List<Order> orders = new ArrayList<>();
                orders.add(cb.desc(root.get("age")));       //倒序
                orders.add(cb.asc(root.get("username")));   //正序
                return query.orderBy(orders).getRestriction();
            };
            Pageable pageable = PageRequest.of(pageNo, pageSize);
            return userDao.findAll(sf, pageable);
        }

具体的说明，我已经在代码注释里说明了，相信一看就明白。  
可以发现，JPA查询确实很方便，省去了xml文件，还可以自动生成数据库表，十分的方便。

那么，这个 `Specification` 是`哪儿来的`呢？我们继续看**dao层**

##### 三 这里是dao层 #####

我在这里添加了一些自定义查询，如果你要执行修改操作，也很简单，添加一个`@Modifying` 注解即可，我这里就不多加演示啦。

//BaseDao
    @NoRepositoryBean//基础dao,作用是不用每次都继承jpa的接口 不作为bean注入spring容器
    public interface BaseDao<T, V> extends JpaRepository<T, V>, JpaSpecificationExecutor<T> { 
    }
    
    
    @Repository("masterUserDao") //除非有多个同名的UserDao,否则这里不用添加 masterUserDao
    public interface UserDao extends BaseDao<User, Integer> { 
    
    	//这种我就不说了，几乎都懂的
        User findOneByUsernameAndAge(String username, Integer age);
        
        //?1 代表第一个参数 以此类推 顺序不能错
        @Query("select u from User u where u.gender = ?1 and u.age = ?2 and u.username like %?3%")
        List<User> query(Integer gender, Integer age, String username);
    
        //query2()是query()的另外以一种写法 和query()相比 顺序无所谓，因为指定了参数名
        @Query("select u from User u where u.gender = :gender and u.age = :age and u.username like %:username%")
        List<User> query2(@Param("gender") Integer gender,
                          @Param("age") Integer age,
                          @Param("username") String username);
    
        //query3()和query()1 2相比多了 nativeQuery=true 意思是原生sql
        @Query(value = "select * from master_user u where u.gender = ?1 and u.age = ?2 and u.username like %?3%", nativeQuery = true)
        List<User> query3(Integer gender, Integer age, String username);
    
    }

具体的测试我就不贴出来了，本地测过，都是ok的。顺便提醒一句，dao层的自定义@Query查询都是要传参的，不传会把它当成null进行查询。

好了，我这里只是简单的一个示例，其他更多的用法有待小伙伴们自己挖掘，谢谢阅读~