JPA查询方式：方法命名规则、@Query查询、结果集类型转化器

JPA查询方式：方法命名规则、@Query查询、结果集类型转化器

往期文章直通车

1. SpringBoot整合SpringData JPA
2. JPA的一对一、一对多、多对多查询

方法命名规则查询

在持久层接口中，可以直接根据方法的名字进行条件查询

/**
 * 通过邮箱模糊查询
 * @param email 邮箱
 * @param pageable 分页
 * @return 用户分页
 */
Page<User> findUserByEmailLike(@Param("email") String email, Pageable pageable);

等同于SQL

select * from user where email like :email limit ?,?

1. @Query查询（基于对象操作）

/**
 * 注意：使用in条件必须入参为list
 * @param blogIds blogId的List
 * @return 返回Dto，包含Blog和User对象
 */
@Query(value = "SELECT " +
		"new com.devilvan.pojo.UserBlogDto(" +
		"u.id," +
		"u.username," +
		"u.email," +
		"u.admin," +
		"u.telephone," +
		"u.identification," +
		"u.introduction," +
		"b.id," +
		"b.title," +
		"b.description)" +
		"FROM User u LEFT OUTER JOIN Blog b ON b.userId = u.id " +
		"WHERE 1=1 AND b.status = 1 AND b.id in ( ?1 ) " +
		"ORDER BY b.created DESC")
List<UserBlogDto> findBlog2User(List<Long> blogIds);

结果集封装对象

@Setter
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class UserBlogDto {
    // User部分
    private Long userId;
    private String username;
    private String email;
    private Boolean admin;
    private String telephone;
    private String identification;
    private String introduction;
    // Blog部分
    private Long blogId;
    private String title;
    private String description;
}

测试代码

/**
 * JPA
 * nativeQuery = false
 * Query注解中使用自定义对象全参构造
 */
@Test
public void userDtoQuery() {
    List<Long> blogIds = new ArrayList<>();
    blogIds.add(9L);
    blogIds.add(10L);
    blogIds.add(18L);
    blogIds.add(23L);
    blogIds.add(29L);
    List<UserBlogDto> dtoList = blogRepository.findBlog2User(blogIds);
    for (UserBlogDto dto : dtoList) {
        System.out.println(dto);
    }
}

结果

小结

这里由于结合了User和Blog表中的字段，所以最终封装到UserBlogDto类中，
注意：在这里的SQL操作都是基于对象，使用的为对象属性，非数据库字段。

2. @Query查询（基于SQL，需要nativeQuery）

由于JPA擅长的是表关系处理，而非复杂的、定制的SQL的处理，nativeQuery保留了写SQL的方法，而写复杂SQL还是需要Mybatis这样的框架更方便。

持久层SQL

@Query(value = "SELECT \n" +
		"a.userId,a.username,a.email,a.admin,a.telephone,a.identification," +
		"a.introduction,a.blogId,a.title,a.description \n" +
		"from (select\n" +
		"u.id as userId,\n" +
		"u.username,\n" +
		"u.email,\n" +
		"u.admin,\n" +
		"u.telephone,\n" +
		"u.identification,\n" +
		"u.introduction,\n" +
		"b.id as blogId,\n" +
		"b.title,\n" +
		"b.description \n" +
		"from `USER` u LEFT OUTER JOIN BLOG b ON b.user_id = u.id \n" +
		"WHERE 1=1 \n" +
		"AND b.status = 1 \n" +
		"AND b.id in ( :blogIds ) \n" +
		"ORDER BY b.created DESC) as a",

		countQuery = "SELECT COUNT(*) " +
		"from (select\n" +
		"u.id as userId,\n" +
		"u.username,\n" +
		"u.email,\n" +
		"u.admin,\n" +
		"u.telephone,\n" +
		"u.identification,\n" +
		"u.introduction,\n" +
		"b.id as blogId,\n" +
		"b.title,\n" +
		"b.description \n" +
		"from `USER` u LEFT OUTER JOIN BLOG b ON b.user_id = u.id \n" +
		"WHERE 1=1 \n" +
		"AND b.status = 1 \n" +
		"AND b.id in ( :blogIds ) \n" +
		"ORDER BY b.created DESC) as a",

		nativeQuery = true)
Page<UserBlogBo> findBlog2User2(List<Long> blogIds, Pageable pageable);

结果集封装接口

/**
 * @Description 用户表查询部分字段
 * @author Evad.Wu
 * @date 2022-06-22
 */
public interface UserBlogBo{
	Long getUserId();
	String getUsername();
	String getEmail();
	boolean isAdmin();
	String getTelephone();
	String getIdentification();
	String getIntroduction();
	Long getBlogId();
	String getTitle();
	String getDescription();

	default UserBlogDto getUserBlogDto() {
		UserBlogDto userBlogDto = new UserBlogDto();
		userBlogDto.setUserId(getUserId());
		userBlogDto.setUsername(getUsername());
		userBlogDto.setEmail(getEmail());
		userBlogDto.setAdmin(isAdmin());
		userBlogDto.setTelephone(getTelephone());
		userBlogDto.setIdentification(getIdentification());
		userBlogDto.setIntroduction(getIntroduction());
		userBlogDto.setBlogId(getBlogId());
		userBlogDto.setTitle(getTitle());
		userBlogDto.setDescription(getDescription());
		return userBlogDto;
	}
}

结果

小结

1. 这里模拟了嵌套查询，而非nativeQuery模式不能支持嵌套查询这类复杂查询
2. 不能直接返回自定义类型实体。返回的类型UserBlogBo为接口类型，提供了结果集字段的get方法，也可以通过defalut块，实例化成对象。
3. 但尽管使用default块的方式，仍然不能很好的复用。但后来得知返回结果集类型可以先使用Object[]接收，然后再通过类型转换成自定义对象。

3. 结果集类型转换器

1. 上一部分讲到了复杂SQL的结果集只能保存到接口中，而不能直接实例化成实体，这样不方便做复杂SQL的查询。
2. 而结果集又可以解析成Object[]的形式，顺着这个思路，将Object[]通过反射转化成对象。

类型转换器

/**
 * @Description JPA复杂查询结果集转化类
 * @author Evad.Wu
 * @date 2022-06-24
 */
public class CastEntityConvertor {
	/**
	 * 转换实体类
	 * @param list JPA结果集
	 * @param clazz 待转化的自定义类型
	 * @return 转化后的自定义类型结果集
	 */
	public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz){
		List<T> returnList = new ArrayList<>();
		if(CollectionUtils.isEmpty(list)){
			return returnList;
		}
		Object[] co = list.get(0);
		Class<?>[] c2 = new Class[co.length];
		//确定构造方法
		for (int i = 0; i < co.length; i++) {
			if(co[i]!=null){
				if (co[i] instanceof BigInteger) {
					c2[i] = Long.class;
				} else if (co[i] instanceof Character) {
					c2[i] = String.class;
				}
				else if(co[i] instanceof Time || co[i] instanceof Timestamp || co[i] instanceof Date) {
					c2[i] = java.util.Date.class;
				}
				else if(co[i] instanceof Byte || co[i] instanceof Short || co[i] instanceof Integer) {
					c2[i] = Integer.class;
				} else {
					c2[i] = co[i].getClass();
				}
			}else {
				c2[i]=String.class;
			}
		}
		try {
			for (Object[] o : list) {
				for (int i = 0; i < o.length; i++) {
					if (o[i] instanceof BigInteger) {
						o[i] = Long.parseLong(String.valueOf(o[i]));
					} else if (o[i] instanceof Character) {
						o[i] = String.valueOf(o[i]);
					}
					else if (o[i] instanceof Byte || o[i] instanceof Short || o[i] instanceof Integer) {
						o[i] = Integer.parseInt(String.valueOf(o[i]));
					}
				}
				Constructor<T> constructor = clazz.getConstructor(c2);
				T newInstance = constructor.newInstance(o);
				returnList.add(newInstance);
			}
		} catch (NoSuchMethodException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
			e.printStackTrace();
		}
		return returnList;
	}
}

持久层SQL

@Query(value = "SELECT \n" +
        "a.userId,a.username,a.email,a.admin,a.telephone,a.identification," +
        "a.introduction,a.blogId,a.title,a.description \n" +
        "from (select\n" +
        "u.id as userId,\n" +
        "u.username,\n" +
        "u.email,\n" +
        "u.admin,\n" +
        "u.telephone,\n" +
        "u.identification,\n" +
        "u.introduction,\n" +
        "b.id as blogId,\n" +
        "b.title,\n" +
        "b.description \n" +
        "from `USER` u LEFT OUTER JOIN BLOG b ON b.user_id = u.id \n" +
        "WHERE 1=1 \n" +
        "AND b.status = 1 \n" +
        "AND b.id in ( :blogIds ) \n" +
        "ORDER BY b.create_time DESC) as a",
        countQuery = "SELECT COUNT(*) " +
                "from (select\n" +
                "u.id as userId,\n" +
                "u.username,\n" +
                "u.email,\n" +
                "u.admin,\n" +
                "u.telephone,\n" +
                "u.identification,\n" +
                "u.introduction,\n" +
                "b.id as blogId,\n" +
                "b.title,\n" +
                "b.description \n" +
                "from `USER` u LEFT OUTER JOIN BLOG b ON b.user_id = u.id \n" +
                "WHERE 1=1 \n" +
                "AND b.status = 1 \n" +
                "AND b.id in ( :blogIds ) \n" +
                "ORDER BY b.create_time DESC) as a",
        nativeQuery = true)
Page<Object[]> findBlog2UserConvert(List<Long> blogIds, Pageable pageable);

测试代码

/**
 * JPA复杂查询结果集，使用反射转换为自定义对象
 */
@Test
public void nativeQueryPageConvert() {
	List<Long> blogIds = new ArrayList<>();
	blogIds.add(9L);
	blogIds.add(10L);
	blogIds.add(18L);
	blogIds.add(23L);
	blogIds.add(29L);
	PageRequest pageRequest = PageRequest.of(0,5);
	Page<Object[]> blog2User2 = blogRepository.findBlog2UserConvert(blogIds, pageRequest);
	long start = System.currentTimeMillis();
	List<UserBlogDto2> list = CastEntityConvertor.castEntity(blog2User2.getContent(), UserBlogDto2.class);
	System.out.println("Time: " + (System.currentTimeMillis() - start));
	for (UserBlogDto2 userBlogDto : list) {
		System.out.println(userBlogDto.getBlogId());
	}
}

结果

参考网址

mysql对应java的数据类型