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里面的<foreach>标签不陌生，这个注解的作用其实与之相同，甚至在实现上更加的便捷！
@JoinColumn(name = "schoolId")
保存表与表之间关系的字段
其余的不做赘述！

JPA配置

#配置jpa
#帮我们自动生成表结构
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql= true
spring.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);
}

示例：

/*条件构造器查询*/
@Test
void 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获取实体
我这里只举了一个模糊查询的例子，因为模糊查询需要写%，不然查不到，这个容易出错，请知悉！

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

/*多条件构造*/
@Test
void 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
 */
@MappedSuperclass
public 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;

@NoRepositoryBean
public 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条件了！

关注我的公众号