Spring Boot的@Query注解

发表于 更新于 分类于 ， ， 置顶 精品
阅读次数： 本文字数： 2.4k 阅读时长 ≈ 9 分钟

摘要

@Query说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.ANNOTATION_TYPE})
@QueryAnnotation
@Documented
public @interface Query {
    // 指定JPA Query语句,当nativeQuery=true时是原生的sql语句
    String value() default "";
    //指定count的JPA Query语句,不指定则自动生成,当nativeQuery=true时是原生的sql语句,countQuery主要用来配合 value实现分页功能
    String countQuery() default "";
    //依据哪个字段来count,如果未配置countQuery（）或countProjection（），将从原始查询派生count查询
    String countProjection() default "";
    // 默认是false，表示value 里面是不是原生的Sql 语句
    boolean nativeQuery() default false;
    //指定一个query 的名字，必须是唯一的。 如果不指定，默认的生成规则是：{$domainClass}.${queryMethodName}。使用命名查询时会用到。
    String name() default "";
    //指定一个count 的query 名字，必须是唯一的。如果不指定，默认的生成规则是：{$domainClass}.${queryMethodName}.count
    String countName() default "";
}

基本用法示例

  • @Entity

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
@Data
@Entity
@Table(name = "tbl_country")
public class Country implements Serializable{
	private static final long serialVersionUID = 1L;

	/*
	 * 主键自增
	 */
	@Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name="id")
	private Long id;

	/*
	 * 中文简称
	 */
	@Column(name="name_zh")
	private String nameZh;

	/*
	 * 英文简称
	 */
	@Column(name="name_en")
	private String nameEn;

	/*
	 * 英文全称
	 */
	@Column(name="name_en_full")
	private String nameEnFull;

	/*
	 * 两字母代码
	 */
	@Column(name="code_two")
	private String codeTwo;

	/*
	 * 三字母代码
	 */
	@Column(name="code_three")
	private String codeThree;

	/*
	 * 数字代码
	 */
	@Column(name="num_code")
	private String numCode;

	/*
	 * 备注
	 */
	@Column(name="remark")
	private String remark;


	public Country(Long id, String nameZh, String nameEn) {
		this.id = id;
		this.nameZh = nameZh;
		this.nameEn = nameEn;
	}

	public Country() {
	}

  • Dto

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
49
50
51
52
53
54
55
@Data
public class CountryDto implements Serializable {
    private static final long serialVersionUID = 1L;

    /*
     * 主键自增
     */
    private Long id;

    /*
     * 中文简称
     */
    private String nameZh;

    /*
     * 英文简称
     */
    private String nameEn;

    /*
     * 英文全称
     */
    private String nameEnFull;

    /*
     * 两字母代码
     */
    private String codeTwo;

    /*
     * 三字母代码
     */
    private String codeThree;

    /*
     * 数字代码
     */
    private String numCode;

    /*
     * 备注
     */
    private String remark;

    public CountryDto() {
    }

    public CountryDto(Long id, String nameZh, String nameEn) {
        this.id = id;
        this.nameZh = nameZh;
        this.nameEn = nameEn;
    }


}

  • Jpa

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public interface CountryJpaRepository extends BaseJpaRepository<Country, Long> {
    //sql查询，只有SELECT * 时才可以直接返回@Entity对象，但要有无参构造方法，page要指定countQuery
    @Query(value = "SELECT * FROM tbl_country WHERE id > ?1", countQuery = "SELECT count(*) FROM tbl_country WHERE id > ?1", nativeQuery = true)
    Page<Country> findByIdAfter(Long id, Pageable pageable);

    //sql查询，只有SELECT * 时才可以直接返回@Entity对象
    @Query(value = "SELECT * FROM tbl_country  WHERE name_zh = ?1",nativeQuery = true)
    Country findCountrySqlByName(String name);

    //hql查询部分属性时，返回@Entity对象，要在@Entity中创建对应的构造方法
    @Query(value = "SELECT new Country(id,nameZh,nameEn) FROM Country WHERE nameZh = ?1")
    Country findCountryHqlByName(String name);

    //hql查询部分属性时，返回非@Entity对象，要创建对应的构造方法，且必须使用全路径
    @Query(value = "SELECT new com.example.demo.CountryDto(id,nameZh,nameEn) FROM Country WHERE nameZh = ?1")
    CountryDto findCountryDtoNewHqlByName(String name);

}

说明

  • 返回@Entity对象，查询全部属性时，sql和hql都支持直接转换

  • 返回@Entity对象，查询部分属性时，sql不支持直接转换，hql时，实体要有对应的构造方法

  • hql也可以返回Dto对象，查询部分属性时，同样需要Dto有对应的构造方法，注意Dto必须使用全路径

sql查询部分属性直接转换成对象的方法

  • 方法1，前一篇文章Spring Boot自定义JpaRepository基类中通过自定义JpaRepository基类，转换@Entity对象和Dto对象都支持

  • 方法2，注入自定义的结果转换器，只能转换为Dto对象，下面说明如何实现

注入自定义的结果转换器

  • 无论是sql还是hql，查询部分属性时，jpa输出的结果都会被转化为Map对象，我们只需要注入一个能将Map转换为指定对象的转换器就可以了

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
/**
 * <h1>注入自定义查询结果转换器</h1>
 */
@Slf4j
@Configuration
public class JpaDtoConfig {

    @Autowired
    private ApplicationContext applicationContext;

    //这里用到了上文“Spring Boot自定义JpaRepository基类”中介绍过的JpaUtil，通过json作为中间媒介
    @Autowired
    private JpaUtil jpaUtil;


    /**
     * 初始化注入@JpaDto对应的Converter
     */
    @PostConstruct
    public void init() {
        Map<String, Object> map = applicationContext.getBeansWithAnnotation(JpaDto.class);
        for (Object o : map.values()) {
            Class c = o.getClass();
            log.info("Jpa添加Converter,class={}", c.getName());
            DefaultConversionService defaultConversionService = ((DefaultConversionService) DefaultConversionService.getSharedInstance());
            //添加Map对象的转换器，将Map对象转换为指定的对象类型
            defaultConversionService.addConverter(Map.class, c, m -> {
                try {
                    return jpaUtil.mapToObject(m, c, false);
                } catch (Exception e) {
                    throw new FatalBeanException("Jpa结果转换出错,class=" + c.getName(), e);
                }
            });
        }
    }

}

  • 定义标记注解

1
2
3
4
5
6
7
8
9
/**
 * <h1>注解标记，声明在Dto对象上，表示该对象作为结果对象时，可以被自定义结果转换器转化</h1>
 */
@Documented
@Component
@Target(value = {ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface JpaDto {
}

  • 此时Dto对象上要加上@JpaDto注解

1
2
3
4
5
6
@JpaDto
@Data
public class CountryDto implements Serializable {
    …………………………
    …………………………
}

  • JpaRepository示例

1
2
3
4
5
6
7
8
9
10
11
12
13
public interface CountryJpaRepository extends BaseJpaRepository<Country, Long> {
    //hql查询部分属性时，一定有使用as别名，这里返回值不能是@Entity，并且其要标注@JpaDto注解
    @Query(value = "SELECT id as id,nameZh as nameZh,nameEn as nameEn FROM Country WHERE nameZh = ?1")
    CountryDto findCountryDtoHqlByName(String name);

    //sql查询部分属性时，一定有使用as别名，这里返回值不能是@Entity，并且其要标注@JpaDto注解
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE name_zh = ?1",nativeQuery = true)
    CountryDto findCountryDtoSqlByName(String name);

    //sql查询部分属性时，一定有使用as别名，这里返回值不能是@Entity，并且其要标注@JpaDto注解，page要指定countQuery
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE id > ?1", countQuery = "SELECT count(*) FROM tbl_country WHERE id > ?1", nativeQuery = true)
    Page<CountryDto> findByIdAfterDto(Long id, Pageable pageable);
}

说明

  • 返回注解了@JpaDto注解的对象时，hql和sql都支持部分字段查询，但要注意字段要加as别名，别名与Dto对象属性名称要一致；

  • 部分字段查询时，Dto对象中不必创建对应的构造方法；

  • 查询部分属性时，不支持返回@Entity对象，即使在@Entity类上增加@JpaDto注解也不好使，说明其返回的不是Map对象

  • hql返回部分属性时，如果直接返回@Entity对象，会抛异常Failed to convert from type [java.lang.Object[]] to type [com.example.demo.Country]，提示类型转换失败，因其返回类型是java.lang.Object[]，所以也说明为什么要用构造方法的形式接收返回数据，就是要与数组位置一一对应上，所以hql返回部分属性，就要使用构造方法的形式；

    1
    2
    3
    4
    5
    6
    7
    //错误
    @Query(value = "SELECT id as id,nameZh as nameZh,nameEn as nameEn FROM Country WHERE nameZh = ?1")
    Country findCountryHqlByNameError(String name);

    //正确
    @Query(value = "SELECT new Country(id,nameZh,nameEn) FROM Country WHERE nameZh = ?1")
    Country findCountryHqlByNameOk(String name);

  • sql返回部分属性时，如果直接返回@Entity对象，会抛异常No such column: 'code_three'.，提示缺少字段。说明该形式在封装@Entity对象时需要遍历其所有@Column字段并为其赋值，此时可以返回Dto或者Map，也可以使用自定义JpaRepository的方式，还有一种方法就是使用命名查询，就是@NamedNativeQuery和@SqlResultSetMapping；

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    //错误
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE name_zh = ?1",nativeQuery = true)
    Country findCountrySqlByNameError(String name);

    //正确，之后可以将map转换为@Entity对象，如通过json的方式
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE name_zh = ?1",nativeQuery = true)
    Map findCountrySqlByNameOk(String name);

    //正确
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE name_zh = ?1",nativeQuery = true)
    Object[] findCountrySqlByNameOk2(String name);

    //正确，CountryDto要加上@JpaDto注解
    @Query(value = "SELECT id,name_zh as nameZh,name_en as nameEn FROM tbl_country WHERE name_zh = ?1",nativeQuery = true)
    CountryDto findCountrySqlByNameOk3(String name);

命名查询

  • @NamedQuery，用于执行hql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@NamedQueries({
        @NamedQuery(
                //指定名称，必须全局唯一
                name = "HQL_FIND_ALL_COUNTRY",
                //要指定的hql
                query = "from Country"
        )
})
@Data
@Entity
@Table(name = "tbl_country")
public class Country implements Serializable {
    …………
}

  • @NamedNativeQuery和@SqlResultSetMapping，用于执行sql，可以指定结果类型，不指定结果类型时返回java.lang.Object[]

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
@NamedNativeQueries({
        @NamedNativeQuery(
                //指定名称，必须全局唯一
                name = "SQL_FIND_ALL_COUNTRY",
                //要指定的sql
                query = "select * from tbl_country",
                //指定返回类型
                resultClass = Country.class
        ),
        @NamedNativeQuery(
                //指定名称，必须全局唯一
                name = "SQL_FIND_ALL_COUNTRY2",
                //要指定的sql
                query = "select * from tbl_country",
                //通过@SqlResultSetMapping封装返回类型，这里是@SqlResultSetMapping的名称
                resultSetMapping = "SQL_RESULT_COUNTRY_ALL"
        ),
        @NamedNativeQuery(
                //指定名称，必须全局唯一
                name = "SQL_FIND_SOME_FIELD_COUNTRY",
                //要指定的sql
                query = "select id,name_zh as nameZh,name_en as nameEn from tbl_country",
                //通过@SqlResultSetMapping封装返回类型，这里是@SqlResultSetMapping的名称
                resultSetMapping = "SQL_RESULT_COUNTRY_SOME_FIELD"
        )
})
@SqlResultSetMappings({
        @SqlResultSetMapping(
                //指定名称，必须全局唯一
                name = "SQL_RESULT_COUNTRY_ALL",
                //指定返回类的@Entity类型
                entities = {
                        @EntityResult(
                                //指定返回类的@Entity类型
                                entityClass = Country.class
                        )
                }

        ),
        @SqlResultSetMapping(
                //指定名称，必须全局唯一
                name = "SQL_RESULT_COUNTRY_SOME_FIELD",
                //查询部分属性时，可以通过构造方法封装结果数据
                classes = {
                        @ConstructorResult(
                                //指定返回类的@Entity类型
                                targetClass = Country.class,
                                //指定调用的构造方法
                                columns = {
                                        @ColumnResult(name = "id", type = Long.class),
                                        @ColumnResult(name = "nameZh"),
                                        @ColumnResult(name = "nameEn")
                                }
                        )
                }
        )
})
@Data
@Entity
@Table(name = "tbl_country")
public class Country implements Serializable {
    …………
    public Country(Long id, String nameZh, String nameEn) {
        this.id = id;
        this.nameZh = nameZh;
        this.nameEn = nameEn;
    }

    public Country() {
    }
}

说明

  • 命名查询的注解只能声明在对应的@Entity类型上

  • @NamedNativeQuery名称必须全局唯一，可以通过entityManager.createNamedQuery(String queryName)进行调用

  • sql查询部分字段时@Entity一定要有对应的构造方法

  • 返回类型必须是@Entity对象

  • @SqlResultSetMapping可以单独使用，其功能就是将sql的查询结果转换为指定的类型,entityManager.createNativeQuery(String sqlString, String resultSetMapping)

使用方法

  • 通过entityManager的方法进行调用

    1
    2
    3
    List list = entityManager.createNamedQuery("SQL_FIND_ALL_COUNTRY").getResultList();

    List list = entityManager.createNativeQuery("select id,name_zh as nameZh,name_en as nameEn from tbl_country","SQL_RESULT_COUNTRY_SOME_FIELD").getResultList();

  • 通过在JpaRepository声明指定的@Query方法，并指定其name属性为对应的@NamedNativeQuery的name值

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    public interface CountryJpaRepository extends BaseJpaRepository<Country, Long> {
        @Query(name = "HQL_FIND_ALL_COUNTRY")
        List<Country> nameHqlFindAll();

        @Query(name = "SQL_FIND_ALL_COUNTRY")
        List<Country> nameSqlFindAll();

        @Query(name = "SQL_FIND_ALL_COUNTRY2")
        List<Country> nameSqlFindAll2();

        @Query(name = "SQL_FIND_SOME_FIELD_COUNTRY")
        List<Country> nameSqlFindSomeField();
    }

关于在@Query注解中使用Mysql的原生sql时的一些小技巧

  • 动态sql通过mysql的if语句实现

    1
    2
    3
    4
    5
    @Query(value = "SELECT * FROM tbl_country WHERE if(?1!='', name_zh = ?1, 1=1)",nativeQuery = true)
    Country findCountrySqlByName(String name);

    @Query(value = "SELECT * FROM tbl_country WHERE if(:name!='', name_zh = :name, 1=1)",nativeQuery = true)
    Country findCountrySqlByName(@Param("name") String name);

  • 拼接like使用mysql的CONCAT语句

    1
    2
    3
    4
    5
    @Query(value = "SELECT * FROM tbl_country WHERE name like CONCAT('%',?1,'%'))",nativeQuery = true)
    Country findCountrySqlByName(String name);

    @Query(value = "SELECT * FROM tbl_country WHERE name like CONCAT('%',:name,'%')",nativeQuery = true)
    Country findCountrySqlByName(@Param("name") String name);

  • 日期比较时可以直接使用>和<等比较符号，参数为Date、LocalDate和LocalDateTime

    1
    2
    @Query(value = "SELECT count(*) from tbl_country r where r.update_time > :lastTime", nativeQuery = true)
    Long countCountryByDate(@Param("lastTime") LocalDateTime lastTime);