SpringBoot_JPA之命名查询

Named-Query

就是在Entity上定义相关的查询方法，然后在Session中查询的时候，可以调用这个方法，实现相关的查询

XML方式

Person类：

package com.tangbaobao.springbootjpa.pojo;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQuery;

/**
 * @author tangxuejun
 * @version 2018/10/28 11:39 PM
 */
@Data
@Entity
public class Person {
    @Id
    private Integer id;
    private String firstName;
    private String lastName;
}

orm.xml

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="
                     http://java.sun.com/xml/ns/persistence/orm
                     http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                 version="1.0">

    <package>com.tangbaobao.springbootjpa.pojo</package>

    <entity class="Person">
        <named-query name="person">
            <query>select p FROM Person p</query>
        </named-query>
    </entity>
</entity-mappings>

注解

在Entity上编写响应的HQL

package com.tangbaobao.springbootjpa.pojo;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQuery;

/**
 * @author tangxuejun
 * @version 2018/10/28 11:39 PM
 */
@Data
@Entity
@NamedQuery(name = "Person.findPerson",
        query = "SELECT p FROM Person p WHERE p.id=?1")
public class Person {
    @Id
    private Integer id;
    private String firstName;
    private String lastName;
}

编写接口(如果不用SpringBoot-jpa，可以用传统的Session来实现)

package com.tangbaobao.springbootjpa.dao;

import com.tangbaobao.springbootjpa.pojo.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface QueryAnnotationDao extends JpaRepository<Person, Integer> {
    List<Person> findPerson(int id);
}

编写测试类：

package com.tangbaobao.springbootjpa.pojo;

import com.tangbaobao.springbootjpa.dao.QueryAnnotationDao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import static org.junit.Assert.*;

@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
public class PersonTest {
    @Autowired
    private QueryAnnotationDao dao;
    @Test
    public void getFirstName() {
        System.out.println(dao.findPerson(1));
    }
}

上面的查询会在Person 类上加入一些注解，称之为侵入式设计，并且把SQL语句写在类上，总感觉有点别扭，可能是我没有领会到JPA的强大之处吧.

@Query 查询

Using named queries to declare queries for entities is a valid approach and works fine for a small number of queries. As the queries themselves are tied to the Java method that executes them, you can actually bind them directly by using the Spring Data JPA @Query annotation rather than annotating them to the domain class. This frees the domain class from persistence specific information and co-locates the query to the repository interface.

@Query适用于少量的查询，这种方式不会直接将查询HQL放在类上，更加简洁。

package com.tangbaobao.springbootjpa.dao;

import com.tangbaobao.springbootjpa.pojo.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

@Component
public interface QueryAnnotationDao extends JpaRepository<Person, Integer> {
    @Query(value = "SELECT p FROM Person p WHERE p.id = ?1 AND p.firstName = ?2")
    Person getPerson(int id, String name);
}

@Query中?是一个占位符，后面跟的数字代表是方法中的第几个参数

这样就查出来了

@Query注解源码:

/*
 * Copyright 2008-2016 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.jpa.repository;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import org.springframework.data.annotation.QueryAnnotation;

/**
 * Annotation to declare finder queries directly on repository methods.
 * 
 * @author Oliver Gierke
 * @author Thomas Darimont
 * @author Christoph Strobl
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD, ElementType.ANNOTATION_TYPE })
@QueryAnnotation
@Documented
public @interface Query {

	/**
	 * Defines the JPA query to be executed when the annotated method is called.
	 * 一般注解上都会有的字段，这个表示在调用这个被@Query修饰的方法的时候，会执行里边的HQL语句
	 */
	String value() default "";

	/**
	 * Defines a special count query that shall be used for pagination queries to lookup the total number of elements for
	 * a page. If non is configured we will derive the count query from the method name.
	 * 用于查询分页参数的统计
	 */
	String countQuery() default "";

	/**
	 * Defines the projection part of the count query that is generated for pagination. If neither {@link #countQuery()}
	 * not {@link #countProjection()} is configured we will derive the count query from the method name.
	 * 
	 * @return
	 * @since 1.6
	 */
	String countProjection() default "";

	/**
	 * Configures whether the given query is a native one. Defaults to {@literal false}.
	 */
	boolean nativeQuery() default false;

	/**
	 * The named query to be used. If not defined, a {@link javax.persistence.NamedQuery} with name of
	 * {@code $ domainClass}.${queryMethodName}} will be used.
	 */
	String name() default "";

	/**
	 * Returns the name of the {@link javax.persistence.NamedQuery} to be used to execute count queries when pagination is
	 * used. Will default to the named query name configured suffixed by {@code .count}.
	 * 
	 * @see #name()
	 * @return
	 */
	String countName() default "";
}

不想用HQL查询，想用原生sql？(nativeQuery)

@Query 注解中也提供了对原生查询的支持，只需要在Query注解中加入一个属性就可以了：

@Query(value = "select * from person p where p.id =?1", nativeQuery = true)
//value 中是原生的SQL语法，不是基于对象操作的HQL语法
    Person getPersonWithNative(int id);

想通过分页查询，并返回分页相应的值(countQuery)

SpringBoot -jpa简化了分页，在查询的时候，只需传入Pageable相应的参数，就可以返回一个Page，其中Page对象中包含了页数，总条数，以及查询的内容等。
代码演示：

package com.tangbaobao.springbootjpa.dao;

import com.tangbaobao.springbootjpa.pojo.Person;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

@Component
public interface QueryAnnotationDao extends JpaRepository<Person, Integer> {
    @Query(value = "SELECT p FROM Person p WHERE p.id = ?1 AND p.firstName = ?2",
            countQuery = "select count(p.id) from Person p")
    Page<Person> getPerson(int id, String name, PageRequest pageRequest);

    @Query(value = "select * from person p where p.id =?1",
            countQuery = "select count(*) from person",
            nativeQuery = true)
    Page<Person> getPersonWithNative(int id, Pageable pageable);
}

运行结果：