vue+node多条件查询 分页_SpringBoot+JPA框架分页、带条件查询等操作

前言

最近研究JPA框架，初学SpringBoot时也简单学过，但是不是很深入，所以这次主要是说一些进阶且常用、实用的操作！

前置准备

创建两张表或者让JPA自动建表，任意选择！学生表

package com.example.jpa.entity;import javax.persistence.*;import java.io.Serializable;@Entity@Table(name = "t_student")public class Student implements Serializable{    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private int id;    private String name;    private String sex;    private int age;    private int schoolId;    private String username;    private String password;    public int getId()    {        return id;    }    public void setId(int id)    {        this.id = id;    }    public String getName()    {        return name;    }    public void setName(String name)    {        this.name = name;    }    public String getSex()    {        return sex;    }    public void setSex(String sex)    {        this.sex = sex;    }    public int getAge()    {        return age;    }    public void setAge(int age)    {        this.age = age;    }    public String getUsername()    {        return username;    }    public void setUsername(String username)    {        this.username = username;    }    public String getPassword()    {        return password;    }    public void setPassword(String password)    {        this.password = password;    }    public int getSchoolId()    {        return schoolId;    }    public void setSchoolId(int schoolId)    {        this.schoolId = schoolId;    }    public Student(int id, String name, String sex, int age, int schoolId, String username, String password)    {        this.id = id;        this.name = name;        this.sex = sex;        this.age = age;        this.schoolId = schoolId;        this.username = username;        this.password = password;    }    public Student()    {    }    @Override    public String toString()    {        return "Student{" +                "id=" + id +                ", name='" + name + '\'' +                ", sex='" + sex + '\'' +                ", age=" + age +                ", schoolId=" + schoolId +                ", username='" + username + '\'' +                ", password='" + password + '\'' +                '}';    }}

学校表

package com.example.jpa.entity;import javax.persistence.*;import java.util.List;@Entity@Table(name = "t_school")public class School{    @Id    //@GeneratedValue(strategy = GenerationType.IDENTITY)    private int id;    private String name;    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)    @JoinColumn(name = "schoolId")    private List<Student> list;    public int getId()    {        return id;    }    public void setId(int id)    {        this.id = id;    }    public String getName()    {        return name;    }    public void setName(String name)    {        this.name = name;    }    public List<Student> getList()    {        return list;    }    public void setList(List<Student> list)    {        this.list = list;    }    @Override    public String toString()    {        return "School{" +                "id=" + id +                ", name='" + name + '\'' +                ", list=" + list +                '}';    }}

简要说明

@OneToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)一对多注解，玩mybatis的人一定对mybatis里面的标签不陌生，这个注解的作用其实与之相同，甚至在实现上更加的便捷！@JoinColumn(name = "schoolId")保存表与表之间关系的字段其余的不做赘述！

JPA配置

#配置jpa#帮我们自动生成表结构spring.jpa.properties.hibernate.hbm2ddl.auto=updatespring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialectspring.jpa.show-sql= truespring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

我项目使用的是达梦数据库，略微麻烦！MySQL方言是自带的！

我在测试类中先插入些测试数据：这是连表插入

    @Autowired    SchoolDao schoolDao;    @Test    void contextLoads()    {        School school = new School();        school.setId(1);        school.setName("淮北中学");        List<Student> students = new ArrayList<>();        Student student = null;        for (int i = 0; i < 30; i++)        {            student = new Student();            student.setName("朝花不迟暮" + i);            student.setAge(i);            student.setUsername("朝花不迟暮" + i);            student.setPassword(UUID.randomUUID().toString().substring(0, 6));            student.setSex("男");            student.setSchoolId(1);            students.add(student);        }        school.setList(students);        schoolDao.save(school);    }

JPA分页操作

核心：PageRequest

这个可以说是十分的常用，JPA与Mybatis不同之处在于Mybatis属于orm思想的框架，需要自己写SQL，而JPA基于hibernate，封装性更强。因此，分页查询操作也已经被封装好了，只需要调用执行就可以了！我们可以看到，findAll方法里有个重载的方法，参数是Pageable，这就是分页的核心！

因为JPA分页的page是从0开始的，所有传入的page需要-1，我的注释里写了我的方法！getContent是获取分页里面的内容，返回类型是list！

    @Test    void findByPage()    {        //if (page <= 0) page = 1;        //else page -= 1;        PageRequest request = PageRequest.of(1, 5);        List<Student> students = studentDao.findAll(request).getContent();        System.out.println(students);    }

结果：

[Student{id=6, name='朝花不迟暮5', sex='男', age=5, schoolId=1, username='朝花不迟暮5', password='d37ff5'}, Student{id=7, name='朝花不迟暮6', sex='男', age=6, schoolId=1, username='朝花不迟暮6', password='b396b1'}, Student{id=8, name='朝花不迟暮7', sex='男', age=7, schoolId=1, username='朝花不迟暮7', password='0b4ceb'}, Student{id=9, name='朝花不迟暮8', sex='男', age=8, schoolId=1, username='朝花不迟暮8', password='e425ee'}, Student{id=10, name='朝花不迟暮9', sex='男', age=9, schoolId=1, username='朝花不迟暮9', password='6e1055'}]

条件查询

条件查询的话，JPA自带一些查询的原语，比如By什么的，我这里想介绍另一个更加灵活且强大的方法！首先需要在dao层继承条件执行器JpaSpecificationExecutor

public interface StudentDao extends JpaRepository<Student,Integer>, JpaSpecificationExecutor<Student>{    List<Student> findByUsernameAndPassword(String username, String password);}

示例：

/*条件构造器查询*/@Testvoid findBySpecification() {    Specification spe = new Specification()    {        @Override        public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder)        {            Predicate predicate = criteriaBuilder.like(root.get("password"), "%6%");            return predicate;        }    };    List list = studentDao.findAll(spe);    System.out.println(list);}

CriteriaBuilder构建条件Root获取实体我这里只举了一个模糊查询的例子，因为模糊查询需要写%，不然查不到，这个容易出错，请知悉！

复合条件查询案例，这是与传统的命名查询最大的区别

/*多条件构造*/@Testvoid findBySpecifications(){    Specification specs = new Specification()    {        @Override        public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder)        {            List<Predicate> predicates = new ArrayList<Predicate>();            Predicate p1 = criteriaBuilder.like(root.get("password"), "%6%");            Predicate p2 = criteriaBuilder.lessThan(root.get("id"), "20");            predicates.add(criteriaBuilder.and(p1,p2));            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));        }    };    List list = studentDao.findAll(specs);    System.out.println(list);}

Hibernate: select student0_.id as id1_2_, student0_.age as age2_2_, student0_.name as name3_2_, student0_.password as password4_2_, student0_.school_id as school_i5_2_, student0_.sex as sex6_2_, student0_.username as username7_2_ from t_student student0_ where (student0_.password like ?) and student0_.id<20

我们可以通过sql语句来分析，已经实现了我们的目的了，密码里有6且id<20

逻辑删除

照着网上的改了一下，略有弊端，简单使用可以！BaseEntry这是基础实体，如果想要实现逻辑删除必须继承此类，里面的字段也必须在表里建出来！

package com.example.jpa.entity;import javax.persistence.Id;import javax.persistence.MappedSuperclass;import javax.persistence.Temporal;import javax.persistence.TemporalType;import java.io.Serializable;import java.util.Date;/** * @author 朝花不迟暮 * @version 1.0 * @date 2020/12/8 21:25 */@MappedSuperclasspublic class BaseEntry implements Serializable{    private static final long serialVersionUID = 5966306766659220492L;    @Id    protected String id;    @Temporal(TemporalType.TIMESTAMP)    protected Date createdDate;    protected String createdBy;    @Temporal(TemporalType.TIMESTAMP)    protected Date updatedDate;    protected String updatedBy;    protected Boolean deleted = false;    public BaseEntry()    {    }    public String getId()    {        return id;    }    public void setId(String id)    {        this.id = id;    }    public Date getCreatedDate()    {        return createdDate;    }    public void setCreatedDate(Date createdDate)    {        this.createdDate = createdDate;    }    public String getCreatedBy()    {        return createdBy;    }    public void setCreatedBy(String createdBy)    {        this.createdBy = createdBy;    }    public Date getUpdatedDate()    {        return updatedDate;    }    public void setUpdatedDate(Date updatedDate)    {        this.updatedDate = updatedDate;    }    public String getUpdatedBy()    {        return updatedBy;    }    public void setUpdatedBy(String updatedBy)    {        this.updatedBy = updatedBy;    }    public Boolean getDeleted()    {        return deleted;    }    public void setDeleted(Boolean deleted)    {        this.deleted = deleted;    }}

BaseDao

package com.example.jpa.dao;import com.example.jpa.entity.BaseEntry;import org.springframework.data.jpa.repository.JpaSpecificationExecutor;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.NoRepositoryBean;import org.springframework.data.repository.PagingAndSortingRepository;import org.springframework.transaction.annotation.Transactional;import java.io.Serializable;import java.util.List;@NoRepositoryBeanpublic interface BaseDao<T extends BaseEntry, ID extends Serializable> extends PagingAndSortingRepository<T, ID>, JpaSpecificationExecutor<T>{    /*逻辑查找*/    @Transactional(readOnly = true)    @Query("select e from #{#entityName} e where e.deleted = false")    List<T> findAll2Logic();    /*根据id数组逻辑查找*/    @Transactional(readOnly = true)    @Query("select e from #{#entityName} e where e.id in ?1 and e.deleted = false")    Iterable<T> findAll2Logic(Iterable<ID> ids);    /*根据id查找实体*/    @Transactional(readOnly = true)    @Query("select e from #{#entityName} e where e.id = ?1 and e.deleted = false")    T findOne2Logic(ID id);    /*逻辑返回总数据量*/    @Transactional(readOnly = true)    @Query("select count(e) from #{#entityName} e where e.deleted = false")    long count2Logic();    /*逻辑返回是否存在*/    @Transactional(readOnly = true)    default boolean exists2Logic(ID id)    {        return findOne2Logic(id) != null;    }    /*根据id逻辑删除*/    @Query("update #{#entityName} e set e.deleted = true where e.id = ?1")    @Transactional    @Modifying    void logic2Delete(ID id);    /*根据实体逻辑删除*/    @Transactional    default void logic2Delete(T entity)    {        logic2Delete((ID) entity.getId());    }    /*根据实体数组逻辑删除*/    @Transactional    default void logic2Delete(Iterable extends T> entities)    {        entities.forEach(entity -> logic2Delete((ID) entity.getId()));    }    /*全量逻辑删除*/    @Query("update #{#entityName} e set e.deleted = true ")    @Transactional    @Modifying    void logic2DeleteAll();}

使用示例：

    @Test    void findTest()    {        List<MyTest> all = myTestDao.findAll2Logic();        System.out.println(all.toString());    }

Hibernate: select mytest0_.id as id1_0_, mytest0_.created_by as created_2_0_, mytest0_.created_date as created_3_0_, mytest0_.deleted as deleted4_0_, mytest0_.updated_by as updated_5_0_, mytest0_.updated_date as updated_6_0_, mytest0_.name as name7_0_ from my_test mytest0_ where mytest0_.deleted=0

注意看SQL，已经带上了where条件了！