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 包路径+类构造方法 ,这个也能看出,我这个类有个接受三个值的构造方法。