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.命名查询+多字段排序

import java.util.List;

import org.springframework.data.domain.Sort;
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> {

    List<Fruit> findByNameNot(String name, Sort sort);

}

Service层调用

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> findAllByNameMultiSort() {

        //命名查询
        String name = "";
        
        // 根据多个条件排序 先根据name降序排列,再根据color的升序排列
        //忽略大小写，并且null值放最后
        List<Order> orders = new ArrayList<Sort.Order>();
        orders.add(new Order(Direction.DESC, "name",Sort.NullHandling.NULLS_LAST).ignoreCase());
        orders.add(new Order(Direction.ASC, "color",Sort.NullHandling.NULLS_LAST).ignoreCase());
        Sort sort = Sort.by(orders);

        //命名查询 + 多字段排序
        List<Fruit> fruitList = fruitRepository.findByNameNot(name,sort);

        return fruitList;
    }
}

 

3.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