Spring Data JPA ：查

可参考Spring Data -Specification用法和常用查询方法（in，join，equal等）

1.命名查询在StudentRepository接口中定义命名查询，不需要实现类参考使用 Spring Data JPA 简化 JPA 开发

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

@Repository
public interface FruitRepository extends JpaRepository<Fruit, Long>, JpaSpecificationExecutor<Fruit> {

    Fruit findByName(String name);

}

Service层

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class FruitServiceImpl implements FruitService {

    @Autowired
    private FruitRepository fruitRepository;

    @Override
    public Fruit findByName(String name) {
        return fruitRepository.findByName(name);
    }

}

2.分页查询 pageNumber是从0开始， pageNumber=0,pageSize=3 就是获取前3条参考创建分页Pageable变量

创建Pageable对象，再查询

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

@Service
public class FruitServiceImpl implements FruitService {

    @Autowired
    private FruitRepository fruitRepository;

    // 分页获取
    @Override
    public List<Fruit> findAllPageable(Integer pageNumber, Integer pageSize) {

        Pageable pageable = PageRequest.of(pageNumber, pageSize); // 创建分页对象

        Page<Fruit> fruits = fruitRepository.findAll(pageable);
        Long total = fruits.getTotalElements(); // 符合条件的总记录条数
        List<Fruit> fruitList = fruits.getContent();// 这一页的所有记录
        return fruitList;
    }
}

3.先排序再分页查询 : 创建Sort对象，再用Sort对象创建 Pageable对象，再查询参考Spring Data JPA 多属性排序

根据一个字段排序
根据多个字段排序，排序方式一样
根据多个字段排序，排序方式不一样

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.domain.Sort.Order;
import org.springframework.stereotype.Service;

@Service
public class FruitServiceImpl implements FruitService {

    @Autowired
    private FruitRepository fruitRepository;

    @Override
    public List<Fruit> findAllPageableBySort(Integer pageNumber, Integer pageSize) {

        Sort sort = Sort.by(Direction.DESC, "name");// 排序方式  根据name降序排列
        Pageable pageable = PageRequest.of(pageNumber, pageSize, sort);// 分页对象
    
        Page<Fruit> fruits = fruitRepository.findAll(pageable);// 查询

        Long total = fruits.getTotalElements(); // 符合条件的总记录条数
        List<Fruit> fruitList = fruits.getContent();// 这一页的所有记录

        return fruitList;
    }

    // 根据多个条件先排序 再分页获取
    @Override
    public List<Fruit> findAllPageableByMultiSort(Integer pageNumber, Integer pageSize) {

        // 根据多个条件排序 先根据name降序排列,再根据color的升序排列
        List<Order> orders = new ArrayList<Sort.Order>();
        orders.add(new Order(Direction.DESC, "name"));
        orders.add(new Order(Direction.ASC, "color"));
        Sort sort = Sort.by(orders);
        Pageable pageable = PageRequest.of(pageNumber, pageSize, sort);// 分页对象

        Page<Fruit> fruits = fruitRepository.findAll(pageable);// 查询
        
        Long total = fruits.getTotalElements(); // 符合条件的总记录条数
        List<Fruit> fruitList = fruits.getContent();// 这一页的所有记录

        return fruitList;
    }
}

4.条件化查询用查询条件创建Specification对象参考Spring Data JPA Specification查询

使用Criteria查询 Criteria查询是面向对象查询， root就是一个对象，root.get("name")就是name属性。可以级联获取属性

每一个查询条件创建一个Specification对象，如果有多个查询条件，就把多个Specification对象and或or成最后一个总的Specification对象。

有实体类Person如下

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Data
@Entity
@Table(name = "tb_person_info")
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column
    private String firstName;
    @Column
    private String lastName;
    @Column
    private Integer age;
    @Column
    private String phone;
    @Column
    private String address;
}

它的form-PersonRequest如下

import lombok.Data;

@Data
public class PersonRequest {

    private Long id;
    private String firstName;
    private String lastName;
    private Integer age;
    private String phone;
    private String address;
}

它的form-PersonResponse如下

import lombok.Data;

@Data
public class PersonResponse {

    private Long id;
    private String firstName;
    private String lastName;
    private Integer age;
    private String phone;
    private String address;
}

Repository层如下

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;


@Repository
public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {

}

如果对它进行组合条件查询，如果查询条件互相都是and关系，那么很好做。如果查询条件有and 和 or 的关系会相对复杂一些

所有查询条件都是and关系：方法1

设置恒值断言，参考 JPA :为 CriteriaBuilder 增加一个恒值断言 Predicate

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

@Service
public class PersonServiceImpl implements PersonService {

    @Autowired
    private PersonRepository personRepository;

    @Override
    public List<PersonResponse> findListAllAnd1(PersonRequest request) {

        Specification<Person> specification = new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                //设置恒真断言
                Predicate predicate = criteriaBuilder.equal(criteriaBuilder.literal(1), 1);// alway true
                //把所有查询条件predicate and到恒真predicate
                if (StringUtils.hasText(request.getFirstName())) {
                    predicate = criteriaBuilder.and(predicate, criteriaBuilder.like(root.get("firstName"), "%" + request.getFirstName() + "%"));
                }
                if (StringUtils.hasText(request.getLastName())) {
                    predicate = criteriaBuilder.and(predicate, criteriaBuilder.like(root.get("lastName"), "%" + request.getLastName() + "%"));
                }
                if (null != request.getAge()) {
                    predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(root.get("age"), request.getAge()));
                }
                //返回Predicate
                return predicate;
            }
        };

        List<Person> persons = personRepository.findAll(specification);
        List<PersonResponse> responses = new ArrayList<PersonResponse>();
        for (Person person : persons) {
            PersonResponse response = new PersonResponse();
            modelToResponse(person, response);
            responses.add(response);
        }
        return responses;
    }
    private void modelToResponse(Person person, PersonResponse response) {
        response.setId(person.getId());
        response.setFirstName(person.getFirstName());
        response.setLastName(person.getLastName());
        response.setAddress(person.getAddress());
        response.setAge(person.getAge());
        response.setPhone(person.getPhone());
    }
}

所有查询条件都是and关系：方法二（推荐）

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

@Service
public class PersonServiceImpl implements PersonService {

    @Autowired
    private PersonRepository personRepository;
    
    @Override
    public List<PersonResponse> findListAllAnd2(PersonRequest request) {

        Specification<Person> specification = new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
              
                //1.调用criteriaBuilder.conjunction()方法 返回一个默认true 的 and predicate 
                Predicate predicate = criteriaBuilder.conjunction();
                
                //把所有查询条件add到这个predicate的expression
                if (StringUtils.hasText(request.getFirstName())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("firstName"), "%" + request.getFirstName() + "%"));
                }
                if (StringUtils.hasText(request.getLastName())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("lastName"), "%" + request.getLastName() + "%"));
                }
                if (null != request.getAge()) {
                    predicate.getExpressions().add(criteriaBuilder.equal(root.get("age"), request.getAge()));
                }
                
                //返回predicate
                return predicate;
            }
        };

        List<Person> persons = personRepository.findAll(specification);
        List<PersonResponse> responses = new ArrayList<PersonResponse>();
        for (Person person : persons) {
            PersonResponse response = new PersonResponse();
            modelToResponse(person, response);
            responses.add(response);
        }
        return responses;
    }
    private void modelToResponse(Person person, PersonResponse response) {
        response.setId(person.getId());
        response.setFirstName(person.getFirstName());
        response.setLastName(person.getLastName());
        response.setAddress(person.getAddress());
        response.setAge(person.getAge());
        response.setPhone(person.getPhone());
    }
}

方法一和二的不同的就是恒真断言的获得和组合查询条件的方法

获取and恒真断言和or恒假断言，CriteriaBuilder接口都提供了方法

public interface CriteriaBuilder {

    /**
     * Create a conjunction (with zero conjuncts).
     * A conjunction with zero conjuncts is true.
     * @return and predicate
     */
    Predicate conjunction();

    /**
     * Create a disjunction (with zero disjuncts).
     * A disjunction with zero disjuncts is false.
     * @return or predicate
     */
    Predicate disjunction();

}

查询条件存在and 和 or 的关系，比如（A or B ）and C

import java.util.ArrayList;
import java.util.List;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;


@Service
public class PersonServiceImpl implements PersonService {

    @Autowired
    private PersonRepository personRepository;

    @Override
    public List<PersonResponse> findListAndOr(PersonRequest request) {

        Specification<Person> specification = new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {

                // A conjunction with zero conjuncts is true.
                Predicate predicate = criteriaBuilder.conjunction();

                // or 条件
                List<Predicate> orPredicates = new ArrayList<Predicate>();
                if (StringUtils.hasText(request.getFirstName())) {
                    orPredicates.add(criteriaBuilder.like(root.get("firstName"), "%" + request.getFirstName() + "%"));
                }
                if (StringUtils.hasText(request.getLastName())) {
                    orPredicates.add(criteriaBuilder.like(root.get("lastName"), "%" + request.getLastName() + "%"));
                }
                if (orPredicates.size() > 0) {
                    predicate.getExpressions().add(criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()])));
                }

                // and 条件
                if (null != request.getAge()) {
                    predicate.getExpressions().add(criteriaBuilder.equal(root.get("age"), request.getAge()));
                }
                return predicate;
            }
        };

        List<Person> persons = personRepository.findAll(specification);
        List<PersonResponse> responses = new ArrayList<PersonResponse>();
        for (Person person : persons) {
            PersonResponse response = new PersonResponse();
            modelToResponse(person, response);
            responses.add(response);
        }
        return responses;
    }

    private void modelToResponse(Person person, PersonResponse response) {
        response.setId(person.getId());
        response.setFirstName(person.getFirstName());
        response.setLastName(person.getLastName());
        response.setAddress(person.getAddress());
        response.setAge(person.getAge());
        response.setPhone(person.getPhone());
    }
}

它生成的sql如下 where (person0_.first_name like ? or person0_.last_name like ?) and person0_.age=12 实现了 ( A or B) and C

Hibernate: select person0_.id as id1_7_, person0_.address as address2_7_, person0_.age as age3_7_, person0_.first_name as first_name4_7_, person0_.last_name as last_name5_7_, person0_.phone as phone6_7_ from tb_person_info person0_ where (person0_.first_name like ? or person0_.last_name like ?) and person0_.age=12

Controller层

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/person")
public class PersonController {

    @Autowired
    private PersonService personService;

    @PostMapping("/list1")
    public ResponseEntity<List<PersonResponse>> findList1(@RequestBody PersonRequest request) {
        List<PersonResponse> responses = personService.findListAllAnd1(request);
        return ResponseEntity.ok(responses);
    }

    @PostMapping("/list2")
    public ResponseEntity<List<PersonResponse>> findList2(@RequestBody PersonRequest request) {
        List<PersonResponse> responses = personService.findListAllAnd2(request);
        return ResponseEntity.ok(responses);
    }

    @PostMapping("/list3")
    public ResponseEntity<List<PersonResponse>> findList3(@RequestBody PersonRequest request) {
        List<PersonResponse> responses = personService.findListAndOr(request);
        return ResponseEntity.ok(responses);
    }

}

5.Spring Data JPA 原生sql查询参考 Spring Data JPA @Query

在Repository层用@Query注解做查询 @Query 默认是按对象查询，nativeQuery = true：按sql原生语句查询

增删改操作需用要@Modifying注解和@Transactional注解，并且返回值只能是int或者Integer。

import java.util.List;

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

@Repository
public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {


	@Query(value = "SELECT * from t_student where id= :id and name like CONCAT('%',:name,'%') ", nativeQuery = true)
	List<Student> findOneStudent1(@Param("id") Long id, @Param("name") String name);

}

Spring Data JPA提供的查询很多时候不能满足业务需求，这时候就需要使用原生sql实现查询

5.1.需要按汉语拼音排序

基于oracle数据库，在oracle数据库，把查询结果按汉语拼音排序

select * from TB_STUDENT  where class_no = '' order by NLSSORT(student_name,'NLS_SORT=SCHINESE_PINYIN_M')

Spring Data JPA原生sql查询

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;


public interface UserRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {


	@Query(value = "select * from TB_STUDENT  where class_no = :classNo order by NLSSORT(student_name,'NLS_SORT=SCHINESE_PINYIN_M')", nativeQuery = true)
	List<Student> findAllByClass(@Param("classNo") String classNo);


}

5.2 只查询部分字段

import java.util.List;

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


@Repository
public interface MatrixReportRepository
		extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {

	@Query(value = "select username,age from TB_STUDENT Where class_no= :classNo ", nativeQuery = true)
	List<SubStudent> findSubStudents(@Param("classNo") String classNo);
	
	 

}

5.3 distinct 或者 group by

Spring Data JPA ：查

继续阅读

@Query nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Spring data JPARepository的一些方法&&jpa-spec插件普通的CRUD分页排序功能根据条件进行查询JpaSpecificationExecutorjpa-spec插件