SpringDataJPA笔记(8)-EntityManager

EntityManager，以及union查询，left join查询

EntityManager是JPA中用于增删改查的接口，而通常我们使用的是Hibernate-EntityManager

Hibernate-EntityManager实现了标准的JPA，可以把它看成hibernate-core和JPA之间的适配器，它并不直接提供ORM的功能，而是对hibernate-core进行封装，使得Hibernate符合JPA的规范

使用案例

    @ApiOperation(value = "查询", httpMethod = "GET")
    @GetMapping("/search")
    public List<CatView> search() {
        Query query = entityManager.createNativeQuery("select id as id, name as name from cat_tb");

        List<CatView> list = query.getResultList();
        log.info("list: {}", JSON.toJSONString(list));
        return list;
    }
    @ApiOperation(value = "查询union", httpMethod = "GET")
    @GetMapping("/search/union")
    public List<CatView> searchUnion() {
        Query query = entityManager.createNativeQuery("select c.id as id, c.name as name from cat_tb c  union all select d.id as id, d.name as name from dog_tb d");

        List<CatView> list = query.getResultList();
        log.info("list: {}", JSON.toJSONString(list));
        return list;
    }

在使用JPA的时候，是不支持union查询的，只能使用原生sql查询，且在使用Query注解的时候无法使用普通类去接收查询的数据

使用Query注解方法

    @Query(nativeQuery = true, value = "select c.id as id, c.name as name from cat_tb c  union all select d.id as id, d.name as name from dog_tb d",
            countQuery = "select count(*) from (select c.id as id from cat_tb c  union all select d.id as id from dog_tb d) a")
    <T> Page<T> findUnion(Pageable pageable, Class<T> type);

使用普通类和接口类去接收查询数据

    @ApiOperation(value = "union query查询", httpMethod = "GET")
    @GetMapping("/left/union")
    public Page<CatView> findUnion(@RequestParam int pageSize, @RequestParam int pageNum) {
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        return catRepository.findUnion(pageable, CatView.class);
    }

    @ApiOperation(value = "union query查询2", httpMethod = "GET")
    @GetMapping("/left/union2")
    public Page<CatView2> findUnion2(@RequestParam int pageSize, @RequestParam int pageNum) {
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        return catRepository.findUnion(pageable, CatView2.class);
    }

第一个方法会报错，第二个方法会得到正确的数据

第一个类是普通类

@Data
@AllArgsConstructor
public class CatView {

    private Long id;
    private String name;
}

第二个是接口类

public interface CatView2 {

    Long getId();
    String getName();
}

补充使用left join的案例，均得到正确的返回

    @Query("select new com.mt.demo.jpa.entity.view.CatView(c.id , d.name) from CatEntity c left join DogEntity d on c.id = d.id")
    <T> Page<T> findQuery(Pageable pageable, Class<T> type);

    @Query("select c.id as id, d.name as name from CatEntity c left join DogEntity d on c.id = d.id")
    <T> Page<T> findQuery2(Pageable pageable, Class<T> type);

    @ApiOperation(value = "left join query查询", httpMethod = "GET")
    @GetMapping("/left/join")
    public Page<CatView> leftJoin(@RequestParam int pageSize, @RequestParam int pageNum) {
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        return catRepository.findQuery(pageable, CatView.class);
    }

    @ApiOperation(value = "left join query查询2", httpMethod = "GET")
    @GetMapping("/left/join2")
    public Page<CatView2> leftJoin2(@RequestParam int pageSize, @RequestParam int pageNum) {
        Pageable pageable = PageRequest.of(pageNum, pageSize);
        return catRepository.findQuery2(pageable, CatView2.class);
    }

源码参考 GITHUB
欢迎关注微信交流