spring data jpa操作数据库的实现方式

spring data jpa接口：

 

• 继承JpaRepository

　　　　1.1.新建基础接口　　　　　

@NoRepositoryBean
 public interface BaseReposittory <T,ID extends Serializable> extends JpaRepository<T,ID>{
 }

　　　　1.2.创建要操作的类型接口，ID为数据库中主键的类型

public interface EmployeeRepository extends BaseReposittory<Employee,Long>{}

　　　　1.3.查询

　　　　　　1.3.1.按照jpa已有的方法名创建查询方法

表达式	例子	hql查询语句
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs,findByFirstnameEqual	… where x.firstname = 1?
Between	findByStartDateBetween	… where x.startDate between 1? and ?2
LessThan(lt)	findByAgeLessThan	… where x.age < ?1
LessThanEqual(le)	findByAgeLessThanEqual	… where x.age <= ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After	findByStartDateAfter	… where x.startDate > ?1
Before	findByStartDateBefore	… where x.startDate < ?1
IsNull	findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like	findByFirstnameLike	… where x.firstname like ?1
NotLike	findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection age)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)

　　　　　　1.3.2.@Query注解查询

    //根据用户名查询(这里写必需在问号后加顺序)
    @Query("select e from Employee e where e.username = ?1")
    Employee query01(String name);
    //根据用户名模糊查询(这里写必需在问号后加顺序)
    @Query("select e from Employee e where e.username like ?1")
    List<Employee> query02(String name);
    //根据用户名与邮件进行模糊查询(这里写必需在问号后加顺序)
    @Query("select e from Employee e where e.username like ?1 and e.email like ?2")
    List<Employee> query03(String name, String email);

　　　　　　如果想用原生SQL，在@Query加nativeQuery =true

//查询所有
@Query(nativeQuery =true,value = "select * from employee where username=?;")
Employee query04(String name);

　　　　1.3.3.继承JpaSpecificationExecutor

　　　　　　1.3.3.1.创建基础接口

@NoRepositoryBean 
public interface BaseReposittory <T,ID extends Serializable> extends JpaRepository<T,ID>,JpaSpecificationExecutor<T> {
}

　　　　　　1.3.3.2.查询

/**
     * 根据相应的规则(Specification) 查询对应数据
     *  Predicate: where username=? and email =?
     *      root : 根 -> 可以获取到类中的属性(username,email)
     *      criteriaQuery: 如select,from,where,group by ,order by 等
     *      criteriaBuilder:解决 username=? / username like ? / age > ?
     *                      多个条件结合 username=?  and/or  age > ?
     */
    //多条件查询+分页+排序
    public void testJpaSpecificationExecutor() throws Exception{
        //先定义规范
        Specification spec= new Specification<Employee>() {
            @Override
            public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> cq, CriteriaBuilder cd) {
                Path usernamePath = root.get("username");
                Predicate p1 = cd.like(usernamePath, "%1%");
                Path emailPath = root.get("email");
                Predicate p2 = cd.like(emailPath, "%2%");
                Path agePath = root.get("age");
                Predicate p3 = cd.ge(agePath, 18);//le 表示小于 ge表示大于
                //多个条件连接起来
                Predicate p = cd.and(p1, p2, p3);
                return p;
            }
        };
        //进行排序
        Sort sort = new Sort(Sort.Direction.ASC,"username");
        //进行分页
        Pageable page=new PageRequest(0,10,sort);
        //查询数据库
        Page p = employeeRepository.findAll(spec, page);
        p.forEach(e->{
            System.out.println(e);
        });
    }

 

　　　　1.3.4.jpa-spec插件

    // 多个条件查询 +分页+排序
    public void testSpec() throws Exception{
        Specification<Employee> spec = Specifications.<Employee>and()
                .like("username","%1%")
                .like("email","%2%")
                .ge("age", 20)
                .build();
        Sort sort = new Sort(Sort.Direction.DESC,"age");//根据年龄排序
        Pageable page = new PageRequest(0,10,sort);
        Page<Employee> list = employeeRepository.findAll(spec, page);
        list.forEach(e->{
            System.out.println(e);
        });
    }