/**
 * 使用@Query注解，自定義JPQL語(yǔ)句
 * JPQL語(yǔ)句中，使用的不是表名，而是實(shí)體類(lèi)名，不是列名，而是屬性名，
 * 例如SQL語(yǔ)句：select * from user  JPQL語(yǔ)句是：select User from User
 * 如果是條件查詢(xún)，之前語(yǔ)句中的占位符？可以使用(:xxx)進(jìn)行表示
 * 此時(shí)xxx會(huì)根據(jù)方法中同名的形參自動(dòng)綁定值
 */
public interface UsersRepositoryByQuery extends Repository<User, Integer> {
    //如果是查詢(xún)所有列，可以省略select ....，從from開(kāi)始寫(xiě)
    @Query("from User where name=:name")
    List<User> selectByName(String name); //select * from user where name=?
    @Query("select u from User u where name=:name and age=:age")
    List<User> selectByNameAndAge(String name, Integer age); //select * from user where name=? and age=?
    @Query("from User where name=:name and age=:age")
    List<User> selectByNameLike(String name);//select * from user  where name like ？
    //除了使用JPQL語(yǔ)句，也是可以使用sql原生語(yǔ)句，在注解中設(shè)置參數(shù)nativeQuery = true，不推薦！
    @Query(value = "select * from user where name=:name",nativeQuery = true)
    List<User> selectByNameSQl(String name);//select * from user where name=?
}

 @Autowired
    private UserRepositoryPage userRepositoryPage;
    /**
     * 分頁(yè)
     */
    @Test
    void testPage(){
        //第一個(gè)參數(shù)是頁(yè)碼數(shù)，0表示第一頁(yè)；第二參數(shù)是分頁(yè)單位；
        Pageable Pageable = PageRequest.of(0, 2);
        Page<User> page = userRepositoryPage.findAll(Pageable);
        System.out.println("總數(shù)據(jù)量"+page.getTotalElements());
        System.out.println("總頁(yè)數(shù)"+page.getTotalPages());
        List<User> content = page.getContent();
        for (User user : content) {
            System.out.println(user);
        }
    }
    /**
     * 排序
     */
    @Test
    void testSort(){
        //根據(jù)id排序
        Sort sort = Sort.by(Sort.Order.desc("id"));
        Iterable<User> all = userRepositoryPage.findAll(sort);
        for (User user : all) {
            System.out.println(user);
        }
    }

//接口  
public interface UserRepositoryExJPA extends JpaRepository<User,Integer>,JpaSpecificationExecutor<User> {
}
//使用  
@Autowired
    private UserRepositoryExJPA userRepositoryExJPA;
    /**
     * 自定義規(guī)則，可以多條件查詢(xún)
     */
    @Test
    void testExJPA2() {
        Specification<User> specification = new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                //模糊查詢(xún)
                Predicate predicate = criteriaBuilder.like(root.get("name"), "張%");
                //模糊查詢(xún)+性別篩選+年齡篩選
                Predicate predicate2 = criteriaBuilder.and(criteriaBuilder.like(root.get("name"), "張%"),
                        criteriaBuilder.equal(root.get("sex"), "男"), criteriaBuilder.gt(root.get("age"), 24));
                return predicate2;
            }
        };
        //進(jìn)行排序年齡進(jìn)行倒序排列
        Sort sort = Sort.by(Sort.Order.desc("age"));
        //分頁(yè)+追加排序
        Pageable pageable = PageRequest.of(0, 2, sort);
        Page<User> page = userRepositoryExJPA.findAll(specification, pageable);
        System.out.println("根據(jù)條件查詢(xún)到" + page.getTotalElements() + "條數(shù)據(jù)");
        System.out.println("根據(jù)條件查詢(xún)到" + page.getTotalPages() + "頁(yè)數(shù)據(jù)");
        System.out.println("當(dāng)頁(yè)數(shù)據(jù)");
        for (User user : page) {
            System.out.println(user);
        }
    }