springdata jpa 自定义sql本地查询，查询结果只能是object型

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/qq_26819733/article/details/79661924

Spring Data Jpa 默认实现时hibernate，我们都知道hibernate使用HQL查询（Hibernate时JPA的实现之一），而不推荐使用sql查询，因为这样子就跟具体数据库耦合了，违背了初衷，但是没有union语句，我也只能用原生了。。。

接口声明：

    @Query(nativeQuery = true ,value = "SELECT topicId ,`type`  from " +
            "(SELECT topic_id as topicId , 2 as type ,create_time FROM collection where user_id in (SELECT focus_id from relation where fan_id = 1) " +
            "UNION " +
            "SELECT id as topicId , 1 as type , create_time FROM topic WHERE user_id in (SELECT focus_id from relation where fan_id = 1 )) " +
            "temp ORDER BY temp.create_time DESC;")
    List<Object> getAll();

    @Query(nativeQuery = true ,value = "SELECT topicId ,`type`  from " +
            "(SELECT topic_id as topicId , 2 as type ,create_time FROM collection where user_id in (SELECT focus_id from relation where fan_id = 1) " +
            "UNION " +
            "SELECT id as topicId , 1 as type , create_time FROM topic WHERE user_id in (SELECT focus_id from relation where fan_id = 1 )) " +
            "temp ORDER BY temp.create_time DESC;")
    List<Object[]> getAll2();

    @Query(nativeQuery = true ,value = "SELECT topicId ,`type`  from " +
            "(SELECT topic_id as topicId , 2 as type ,create_time FROM collection where user_id in (SELECT focus_id from relation where fan_id = 1) " +
            "UNION " +
            "SELECT id as topicId , 1 as type , create_time FROM topic WHERE user_id in (SELECT focus_id from relation where fan_id = 1 )) " +
            "temp ORDER BY temp.create_time DESC;")
    List<BigInteger[]> getAll3();

    @Query(nativeQuery= true ,value = "SELECT topicId ,`type`  from " +
            "(SELECT topic_id as topicId , 2 as type ,create_time FROM collection where user_id in (SELECT focus_id from relation where fan_id = ?1) " +
            "UNION " +
            "SELECT id as topicId , 1 as type , create_time FROM topic WHERE user_id in (SELECT focus_id from relation where fan_id = ?1 )) " +
            "temp ORDER BY temp.create_time DESC limit ?2 , ?3")
    List<Object[]> getAllRelationTopic(Long fanId  , Integer startLoacl , Integer size);
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

这里注意getAll、getAll2、getAll3的返回值，这三个方法是例子，getAllRelationTopic是正常我最终要写的逻辑
四个sql注解，使用nativeQuery，就是原始sql查询，返回一个集合的Object[]（对象数组），
接收时可以是Object、Object[]，其他类型不能接受

Spring Boot测试：（Spring boot测试类添加 @RunWith(SpringRunner.class) 和 @SpringBootTest 注解）

    @Test
    public void getAll() {

        List<Object> collection = repository.getAll();
        for (int i = 0; i < collection.size(); i++) {
            Object[] col = (Object[]) collection.get(i);
            RelationTopic relationTopic = new RelationTopic(((BigInteger) col[0]).longValue(), ((BigInteger) col[1]).intValue());
            System.out.println(relationTopic.toString());
        }
        System.out.println();
    }

    @Test
    public void getAll2() {

        List<Object[]> collection = repository.getAll2();
        for (int i = 0; i < collection.size(); i++) {
            Object[] col = collection.get(i);
            RelationTopic relationTopic = new RelationTopic(((BigInteger) col[0]).longValue(), ((BigInteger) col[1]).intValue());
            System.out.println(relationTopic.toString());
        }
        System.out.println();
    }

    @Test
    public void getAll3() {
        //java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to [Ljava.math.BigInteger;
        List<BigInteger[]> collection = repository.getAll3();
        for (int i = 0; i < collection.size(); i++) {
            BigInteger[] col = collection.get(i);
            RelationTopic relationTopic = new RelationTopic(col[0].longValue(), col[1].intValue());
            System.out.println(relationTopic.toString());
        }
        System.out.println();
    }

    @Test
    public void getAllRelationTopic() {
        List<Object[]> collection = repository.getAllRelationTopic(1L , 0 , 5);
        List<Long> longCollection = new ArrayList<>();
        for (int i = 0; i < collection.size(); i++) {
            Object[] col = collection.get(i);
            RelationTopic relationTopic = new RelationTopic(((BigInteger) col[0]).longValue(), ((BigInteger) col[1]).intValue());
            longCollection.add(relationTopic.getTopicId());
            System.out.println(relationTopic.toString());
        }
        System.out.println();
    }
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

测试方法getAll、getAll2、getAll3是对上面sql的测试，getAll3会报错，因为转不了类型
下面是getAllRelationTopic测试方法的解释
这里我将返回的数组构建成一个类。数字类型都是BigInteger，所以将Object数组元素转成BigInteger，其中接受值为List< BigInteger[]>的方法报错，可以知道只能用Object或者Object数组才能接受返回值，具体Object怎么组成的，通过Debug来查看具体类型，再转成你要的类型。

---

顺便记录一下，JPQL怎么生成新类:

@Query("select new space.xxhui.ec.interaction.POJO.RelationTopic( c.topicId , 2 ,c.createTime) from CollectionEntity c where c.userId in (select r.focusId from RelationEntity r where r.fanId = ?1 ) ")
    List<RelationTopic> getRelationTopic(Long fanId);
 
 
1
2

就是：new 包路径+类构造方法 ，这个也能看出，我这个类有个接受三个值的构造方法。