JPA 自定义返回字段

向右看齐 2022-04-24 02:30 330阅读 0赞

实体类：User.java

@Data
    @Accessors(chain = true)
    @EqualsAndHashCode(callSuper = true)
    @Entity
    @Table(name = "user")
    public class User extends AbstractEntity {
    
        @Column(unique = true)
        private String username;
    
        private String password;
    
        private String nickname;
    
        private String email;
    
        @Column(name = "org_id")
        private Long orgId;
    
        @Column(name = "org_name")
        private String orgName;
    }

DTO类：UserDTO.java

import lombok.Value;
    
    /**
     * @author wu qilong
     * @date 2019/4/11
     * @Description: 自定义返回值，字段名称要和User实体类中的一致，加上lombok.@Value注解。
     */
    @Value
    public class UserDTO {
    
        private String username;
    
        private String nickname;
    
        private String email;
    }

repository类：UserRepository.java

/**
     * @author Wu Qilong
     * @date 2019/4/11
     * @Description:
     */
    public interface UserRepository extends JpaRepository<User, Long> {
    
        /**
         * 用户名查询
         *
         * @param username
         * @return
         */
        Optional<User> findByUsername(String username);
    
    
        /**
         * 用户名查询
         * @param username
         * @return
         */
        <T> Optional<T> findByUsername(String username, Class<T> type);
    }

测试：

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class UoServerApplicationTests {
    
        @Autowired
        UserRepository userRepository;
    
        @Test
        public void contextLoads() {
            UserDTO userDTO = userRepository.findByUsername("wuqilong", UserDTO.class).get();
            Console.log(userDTO);
        }
    }

*  注意：返回的DTO中属性名称需要和实体类中字段名一致，且加上lombok包中的@Value注解，这种方式对于只需要返回表中部分的字段很方便，如果需要自定义返回字段名称，可以参考以下方法：
 *  需求：统计注册人数

1.  定义一个返回对象
2.  使用`@Query(value = "select new com.wd.cloud.uoserver.dto.TjVO(u.orgId ,count(u.orgId)) from User u group by orgId")`进行查询。

@Value
    public class TjVO {
    
        Long orgId;
    
        Long registerCount;
    }

/**
         * 按机构统计注册人数
         * @param pageable
         * @return
         */
        @Query(value = "select new com.wd.cloud.uoserver.dto.TjVO(u.orgId ,count(u.orgId)) from User u group by orgId")
        Page<TjVO> tjByOrgId(Pageable pageable);

或者也可以用下面的方法：

定义一个接口，用getXxx定义返回字段。xxx需要和返回字段的别名一致。  
如果不一致，可以使用org.springframework.beans.factory.annotation包中的@Value进行映射

public interface TjDTO {
    
        /**
         * 所属机构
         * @return
         */
        Long getOrgId();
        /**
         * 注册时间
         * @return
         */
        String getRegisterDate();
    
        /**
         * 注册数量
         * @return
         */
        String getRegisterCount();
    
        /**
         * 管理员数量 当别名与该getXXX名称不一致时，可以使用该注解调整
         * @return
         */
        @Value("#{target.adminCount}")
        Long getManagerCount();
    
    }

repository类：UserRepository.java添加方法tjByOrgId(),返回orgId和registerCount

/**
         * 按天统计注册人数
         * @return
         */
        @Query(value = "select DATE_FORMAT(gmt_create,\"%Y-%m-%d\") as registerDate,count(*) as registerCount from user group by registerDate",nativeQuery = true)
        List<TjDTO> tj();
    
        /**
         * 按机构统计注册人数
         * @param pageable  分页
         * @return
         */
        @Query(value = "select org_id as orgId,count(*) as registerCount from user group by orgId",
                countQuery = "select count(*) from user group by org_id", nativeQuery = true)
        Page<TjDTO> tjByOrgId(Pageable pageable);

测试：

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class UoServerApplicationTests {
    
        @Autowired
        UserRepository userRepository;
    
        @Test
        public void contextLoads() {
            List<TjDTO> tjDTOList = userRepository.tj();
            tjDTOList.forEach(tjDTO -> {
                Console.log("registerDate={},registerCount={}", tjDTO.getRegisterDate(), tjDTO.getRegisterCount());
            });
        }
    }

结果日志：

Hibernate: select DATE_FORMAT(gmt_create,"%Y-%m-%d") as registerDate,count(*) >as registerCount from user group by registerDate
    registerDate=2019-01-28,registerCount=7393
    registerDate=2019-03-07,registerCount=1

*  需求：根据机构分组，统计机构总人数和用户类型为2的人数

@Component
    public class SpecBuilder {
    
        @PersistenceContext
        private EntityManager entityManager;
    
        public List<Object[]> tj(Long orgId) {
            CriteriaBuilder cb = entityManager.getCriteriaBuilder();
            CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
            Root<User> root = query.from(User.class);
            //拼接where条件
            List<Predicate> predicateList = new ArrayList<Predicate>();
            if (orgId != null) {
                predicateList.add(cb.equal(root.get("orgId"), orgId));
            }
            //加上where条件
            query.where(ArrayUtil.toArray(predicateList, Predicate.class));
            query.multiselect(root.get("orgId"),
                    cb.count(root),
                    cb.sum(cb.<Integer>selectCase().when(cb.equal(root.get("userType"), 2), 1).otherwise(0)));
            query.groupBy(root.get("orgId"));
            //最终sql:  select org_id,count(id),sum(case when user_type=2 then 1 else 0 end) from user where org_id=?1 group by org_id;
            TypedQuery<Object[]> typedQuery = entityManager.createQuery(query);
            return typedQuery.getResultList();
        }
    
    }

测试：

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class UoServerApplicationTests {
    
        @Autowired
        SpecBuilder specBuilder;
    
        @Test
        public void contextLoads() {
            List<Object[]> tjDTOList1 = specBuilder.tj(169L);
            tjDTOList1.forEach(tjDTO -> {
                Console.log("orgId={},总人数={},管理员人数={}", tjDTO[0], tjDTO[1], tjDTO[2]);
            });
        }
    }

![format_png][]

感谢[https://www.hutool.cn/][https_www.hutool.cn] 作者提供hutool工具包

参考：[https://docs.spring.io/spring-data/jpa/docs/2.1.5.RELEASE/reference/html/\#projections][https_docs.spring.io_spring-data_jpa_docs_2.1.5.RELEASE_reference_html_projections]

[https://www.jianshu.com/p/fbd157c3b4a4][https_www.jianshu.com_p_fbd157c3b4a4]

[format_png]: /images/20220221/71d07e7f0a1b4cd4a44c29779ea13bf8.png
[https_www.hutool.cn]: https://www.hutool.cn/
[https_docs.spring.io_spring-data_jpa_docs_2.1.5.RELEASE_reference_html_projections]: https://docs.spring.io/spring-data/jpa/docs/2.1.5.RELEASE/reference/html/#projections
[https_www.jianshu.com_p_fbd157c3b4a4]: https://www.jianshu.com/p/fbd157c3b4a4