Springboot Jpa自定义查询的结果集

如果要自定义查询的结果集有3种方法：

1. 使用List<Object[]>来接收结果，只支持原生sql查询。
2. 使用自定义对象来接收结果，支持JPA，JPQL查询。
3. 使用自定义的接口来映射结果，支持JPA，JPQL，原生sql查询。

官网文档链接 https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

数据准备

entity

package com.example.demo;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * @author peter
 * 2019/1/24 14:54
 */
@Entity
@Data
public class Person {
    @Id
    @GeneratedValue
    private Long id;
    private String name;
    private Integer age;
    private int gender;
}

数据库

CREATE TABLE person
(
  id     BIGINT AUTO_INCREMENT
    PRIMARY KEY,
  age    INT          NULL,
  gender INT          NOT NULL,
  name   VARCHAR(255) NULL
)

INSERT INTO person(age, gender, name) VALUES (12,1,'小明');
INSERT INTO person(age, gender, name) VALUES (15,1,'小明');
INSERT INTO person(age, gender, name) VALUES (13,1,'小明');
INSERT INTO person(age, gender, name) VALUES (18,0,'小明');
INSERT INTO person(age, gender, name) VALUES (12,1,'小小');
INSERT INTO person(age, gender, name) VALUES (12,0,'小红');

使用自定义对象来接收结果

自定义的对象

package com.example.demo;
import lombok.AllArgsConstructor;
import lombok.Data;

/**
 * @author peter
 * 2019/1/24 16:01
 */
@Data
@AllArgsConstructor
public class NameResult {
    private String name;
    private int age;
}

（1）JPA 查询

	List<NameResult> findByName(String name);

（2） JPQL 查询

	@Query("select " +
            "new com.example.demo.NameResult(name,age) " +
            "from Person where name = :name")
    List<NameResult> queryName(@Param("name") String name);

注意：new 关键字 对象必须是类的全路径。该类必须有一个全部参数的构造器。

自定义接口来映射结果

package com.example.demo;

/**
 * @author peter
 * 2019/1/24 16:09
 */
public interface NameResultInterface {
    String getName();
    int getAge();
    default String getPersonInfo(){
        return getName().concat(";")
        .concat(String.valueOf(getAge()));
    }
}

(1) JPA查询

List<NameResultInterface> findByAge(int age);

(2)JPQL查询

	@Query("select " +
            "p.name as name," +
            "p.age as age " +
            "from Person p where name = :name")
    List<NameResultInterface> queryNameInterface(@Param("name") String name);

(3) 原生sql查询

package com.example.demo;
/**
 * @author peter
 * 2019/1/24 16:32
 */
public interface PersonStatistic {
    int getGender();
    int getNum();
    default String toStringInfo() {
        return "gender=" + getGender() + "; num=" + getNum();
    }
}

	@Query(value = "select " +
            "p.gender as gender," +
            "count(p.id) AS num " +
            "from person p GROUP BY p.gender",nativeQuery = true)
    List<PersonStatistic> personStatistic();

测试类及dao层

package com.example.demo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author peter
 * 2019/1/24 16:01
 */
@SuppressWarnings("ALL")
public interface PersonDao extends JpaRepository<Person,Long> {
    List<NameResult> findByName(String name);

    List<NameResultInterface> findByAge(int age);

    @Query("select " +
            "new com.example.demo.NameResult(name,age) " +
            "from Person where name = :name")
    List<NameResult> queryName(@Param("name") String name);

    @Query("select " +
            "p.name as name," +
            "p.age as age " +
            "from Person p where name = :name")
    List<NameResultInterface> queryNameInterface(@Param("name") String name);

    @Query(value = "select " +
            "p.gender as gender," +
            "count(p.id) AS num " +
            "from person p GROUP BY p.gender",nativeQuery = true)
    List<PersonStatistic> personStatistic();

}

package com.example.demo;

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.SpringRunner;

import java.util.List;

/**
 * @author peter
 * 2019/1/24 16:04
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class PersonDaoTest {
    private static final String NAME = "小明";
    @Autowired
    private PersonDao personDao;
    @Test
    public void queryName() {
        List<NameResult> nameResults = personDao.queryName(NAME);
        System.out.println(nameResults);
    }
    @Test
    public void queryNameInterfaceTest() {
        List<NameResultInterface> resultInterfaces = personDao.queryNameInterface("小明");
        resultInterfaces.forEach(o -> {
            System.out.println(o.getPersonInfo());
        });
    }
    @Test
    public void findByNameTest() {
        List<NameResult> byName = personDao.findByName(NAME);
        byName.forEach(System.out::println);
    }
    @Test
    public void findByAgeTest() {
        List<NameResultInterface> byAge = personDao.findByAge(18);
        byAge.forEach(o -> {
            System.out.println(o.getPersonInfo());
        });
    }
    @Test
    public void personStatisticTest(){
        List<PersonStatistic> statistics = personDao.personStatistic();
        statistics.forEach(o->{
            System.out.println(o.toStringInfo());
        });
    }

}

demo 源码