java pagehelper不走sql分页失效 jpa原生sql分页

转载

智能创新梦想家 2023-08-02 12:58:53

文章标签 Customer List System 文章分类 Java 后端开发

一、使用方法

1、在dao中定义开一个方法，使用方法的参数设置jpql，并且使用方法的返回值接受查询结果，在方法上添加@query注解，在注解中写jpql语句进行增删改查，测试

2、使用原生的sql语句：dao中定义一个方法，在方法中添加@query注解，在注解中添加原生sql语句，并且添加一个属性：nativeQuery=true，测试

3、方法命名规则查询：

　　通过以肯定的规则，定义一个方法，框架本身就可以根据方法名生成一块个sql语句进行查询，规则：

　1、必须以findBy开头

　　　　2、查询某个字段，findBy后跟实体类的属性名称

　　　　3、如果有多个条件，就在方法后加And后跟实体类的属性名

　　　　4、方法的参数对应查询的定义

　　　　5、返回值根据返回值的数据类型定义；如果是分页查询，在方法中添加一个参数Pageable即可

4、使用Specification方式进行查询：最强大的查询方式，除了原生的SQL 语句以外还有最复杂的查询方式

1、要在dao 继承JpaSeciFicationExection 接口

　　　　2、使用JpaSeciFicationExection 接口中提供的方法进行查询并且每个方法都需要使用Specification对象作为参数

二、、编写实现类

package cn.zrf.jpa.entity;

import javax.persistence.*;

@Entity
@Table(name = "cust_customer")
public class Customer {
    // 配置主键自增的策略
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    @Column(name="cust_id")
    private long custId;
    @Column(name="cust_name")
    private String custName;
    @Column(name="cust_source")
    private String custSource;
    @Column(name="cust_indutry")
    private String custIndutry;
    @Column(name="cust_level")
    private String custLevel;
    @Column(name="cust_address")
    private String custAddress;
    @Column(name="cust_phone")
    private String custPhone;

    @Override
    public String toString() {
        return "Customer{" +
                "custId=" + custId +
                ", custName='" + custName + '\'' +
                ", custSource='" + custSource + '\'' +
                ", custIndutry='" + custIndutry + '\'' +
                ", custLevel='" + custLevel + '\'' +
                ", custAddress='" + custAddress + '\'' +
                ", custPhone='" + custPhone + '\'' +
                '}';
    }

    public long getCustId() {
        return custId;
    }

    public void setCustId(long custId) {
        this.custId = custId;
    }

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getCustSource() {
        return custSource;
    }

    public void setCustSource(String custSource) {
        this.custSource = custSource;
    }

    public String getCustIndutry() {
        return custIndutry;
    }

    public void setCustIndutry(String custIndutry) {
        this.custIndutry = custIndutry;
    }

    public String getCustLevel() {
        return custLevel;
    }

    public void setCustLevel(String custLevel) {
        this.custLevel = custLevel;
    }

    public String getCustAddress() {
        return custAddress;
    }

    public void setCustAddress(String custAddress) {
        this.custAddress = custAddress;
    }

    public String getCustPhone() {
        return custPhone;
    }

    public void setCustPhone(String custPhone) {
        this.custPhone = custPhone;
    }
}

三、编写dao

package cn.zrf.jpa.dao;

import cn.zrf.jpa.entity.Customer;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CustomerDao extends JpaRepository<Customer,Long>,JpaSpecificationExecutor<Customer> {
    //查询全部
    @Query("from Customer")
    List<Customer> getAllCustomer();
    //查询全部并分页
    @Query("from Customer ")
    List<Customer> getAllCustomerByPage(Pageable pageable);
    //条件查询(根据ID查询)
    @Query("from Customer where cust_id = ?")
    Customer getCustomerById(Long id);
    //根据地址，姓名模糊查询
    @Query("from Customer where custAddress like ? and custName like ?")
    List<Customer> getCustList(String address,String custName);
    //根据ID进行局部更新操作
    @Query("update Customer set cust_name=? where custId=?")
    @Modifying
    void getUpdateById(String name,Long id);
    //原生sql语句模糊查询操作
    @Query(value = "select * from cust_customer where cust_name like ?",nativeQuery = true)
    List<Customer> getCustomerListByNative(String name);
    //方法命名规则查询
    /**1 应该使用findBy开头
     * 2 查询某个字段  findBy后跟实体类的属性的名称
     * 3 如果有多个条件   就在方法后加And+实体类的属性名
     * 4  方法的参数 对应查询的定义
     * 5 返回值根据返回的数据类型定义
     * 6 如果需要分页查询   在方法中添加一个参数Pageable 即可
     */
    //根据ID查询
    Customer findByCustId(Long id);
    //根据姓名，地址进行模糊查询
    List<Customer> findByCustNameLikeAndCustAddressLike(String name,String address);
    //分页查询
    List<Customer> findByCustAddressLike(String address, Pageable pageable);
}

四、测试类

package cn.jpa.test;

import cn.zrf.jpa.dao.CustomerDao;
import cn.zrf.jpa.entity.Customer;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.test.annotation.Commit;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import java.awt.print.Pageable;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestJpql {
    @Autowired
    CustomerDao customerDao;
    //查询全部
    @Test
    public void getAllCustomerTest(){
        List<Customer> list = customerDao.getAllCustomer();
        for (Customer customer:list){
            System.out.println(customer);
        }
    }
    //查询全部并分页
    @Test
    public void getAllCustomerByPageTest(){
        List<Customer> list = customerDao.getAllCustomerByPage(new PageRequest(0, 3));
        for (Customer customer:list){
            System.out.println(customer);
        }
    }
    //根据ID查询
    @Test
    public void getCustomerByIdTest(){
        Customer customer = customerDao.getCustomerById(1l);
        System.out.println(customer);
    }
    //根据地址，姓名进行模糊查询
    @Test
    public void getCuseListTest(){
        List<Customer> custList = customerDao.getCustList("%京%", "%长%");
        for (Customer customer:custList){
            System.out.println(customer);
        }
    }
    //根据ID进行局部更新操作
    @Test
    @Transactional
    @Commit
    public void getUpdateByIdTest(){
        customerDao.getUpdateById("张无忌",3l);
    }
    //原生sql语句模糊查询
    @Test
    public void getCustomerByNativeTest(){
        List<Customer> list = customerDao.getCustomerListByNative("%长%");
        for (Customer customer:list){
            System.out.println(customer);
        }
    }
    //方法命名规则查询测试
    //根据ID查询
    @Test
    public void findByIdTest(){
        Customer customer = customerDao.findByCustId(1L);
        System.out.println(customer);
    }
    //根据地址，姓名进行模糊查询
    @Test
    public void findByCustNameLikeAndAddressLike(){
        List<Customer> list = customerDao.findByCustNameLikeAndCustAddressLike("%张%","%京%");
        for (Customer customer:list){
            System.out.println(customer);
        }
    }
    //根据地址进行分页查询
    @Test
    public void findByCustAddressLike(){
        List<Customer> list = customerDao.findByCustAddressLike("%京%", new PageRequest(0, 3));
        for (Customer customer:list){
            System.out.println(customer);
        }
    }

　　 //使用Specification方式进行查询
    //根据ID进行查询
    @Test
    public void findByIdSpecification(){
        Customer customer = customerDao.findOne(new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                //参数1、字段名   参数2、字段值
                Predicate predicate = criteriaBuilder.equal(root.get("custId"), 1l);
                return predicate;
            }
        });
        System.out.println(customer);
    }
    //根据姓名、地址进行模糊查询
    @Test
    public void findByNameAndAddress(){
        customerDao.findAll(new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                //创建根据模糊查询的条件
                Predicate custName = criteriaBuilder.like(root.get("custName"), "%长%");
                Predicate custAddress = criteriaBuilder.like(root.get("custAddress"), "%京%");
                //把两个条件进行组合
                Predicate predicate = criteriaBuilder.and(custName, custAddress);
                return predicate;
            }
        }).forEach(c-> System.out.println(c));//forEach(System.out::println);
//        for(Customer customer:list){
//            System.out.println(customer);
//        }
    }
    //根据姓名、地址进行分页查询
    @Test
    public void findByCustNameAndCustAddressPage(){
        Page page = customerDao.findAll(new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                //创建根据模糊查询条件
                Predicate custName = criteriaBuilder.like(root.get("custName"), "%长%");
                Predicate custAddress = criteriaBuilder.like(root.get("custAddress"), "%京%");
                //组合两个条件
                Predicate predicate = criteriaBuilder.and(custName, custAddress);
                return predicate;
            }
        },new PageRequest(0,3));
        System.out.println("总条数"+page.getTotalElements());
        System.out.println("总页数"+page.getTotalPages());
        List list = page.getContent();
        for (Customer customer:list){
            System.out.println(customer);
        }
    }
}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。