java jpa自身关联查询_Spring Hibernate JPA 联表查询 复杂查询

关系类型Owning-SideInverse-Side

one-to-one

@OneToOne

@OneToOne(mappedBy="othersideName")

one-to-many / many-to-one

@ManyToOne

@OneToMany(mappedBy="xxx")

many-to-many

@ManyToMany

@ManyToMany(mappedBy ="xxx")

其中 many-to-many关系的owning-side可以使用@JoinTable声明自定义关联表，比如Book和Author之间的关联表：

@JoinTable(name = "BOOKAUTHOR", joinColumns = { @JoinColumn(name = "BOOKID", referencedColumnName = "id") }, inverseJoinColumns = { @JoinColumn(name = "AUTHORID", referencedColumnName = "id") })

关联关系还可以定制延迟加载和级联操作的行为(owning-side和inverse-side可以分别设置)：

通过设置fetch=FetchType.LAZY 或 fetch=FetchType.EAGER来决定关联对象是延迟加载或立即加载。

通过设置cascade={options}可以设置级联操作的行为，其中options可以是以下组合：

CascadeType.MERGE 级联更新

CascadeType.PERSIST 级联保存

CascadeType.REFRESH 级联刷新

CascadeType.REMOVE 级联删除

CascadeType.ALL 级联上述4种操作

4、事件及监听

通过在实体的方法上标注@PrePersist，@PostPersist等声明即可在事件发生时触发这些方法。

四、JPA应用

1、Dependencies

org.springframework.data

spring-data-jpa

2、JPA提供的接口

主要来看看Spring Data JPA提供的接口，也是Spring Data JPA的核心概念：

1)：Repository：最顶层的接口，是一个空的接口，目的是为了统一所有Repository的类型，且能让组件扫描的时候自动识别。

2)：CrudRepository ：是Repository的子接口，提供CRUD的功能

public interface CrudRepositoryextends Repository {

S save(S entity);

T findOne(ID primaryKey);

Iterable findAll();

Long count();

void delete(T entity);

boolean exists(ID primaryKey);

// … more functionality omitted

}

3)：PagingAndSortingRepository：是CrudRepository的子接口，添加分页和排序的功能

public interface PagingAndSortingRepository extends CrudRepository {

Iterable findAll(Sort sort);

Page findAll(Pageable pageable);

}

4)：JpaRepository：是PagingAndSortingRepository的子接口，增加了一些实用的功能，比如：批量操作等。

5)：JpaSpecificationExecutor：用来做负责查询的接口

public interface JpaSpecificationExecutor{

T findOne(Specification spec);

List findAll(Specification spec);

Page findAll(Specification spec, Pageable pageable);

List findAll(Specification spec, Sort sort);

long count(Specification spec);

}

6)：Specification：是Spring Data JPA提供的一个查询规范，要做复杂的查询，只需围绕这个规范来设置查询条件即可

3、查询语言

3.1 根据名称判别

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

Is,Equals

findByFirstname,findByFirstnameIs,findByFirstnameEquals

… where x.firstname = ?1

Between

findByStartDateBetween

… where x.startDate between ?1 and ?2

LessThan

findByAgeLessThan

… where x.age < ?1

LessThanEqual

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)

KeywordSampleJPQL snippet

3.2 @Query

public interface UserRepository extends JpaRepository {

//Declare query at the query method using @Query

@Query("select u from User u where u.emailAddress = ?1")

User findByEmailAddress(String emailAddress);

//Advanced like-expressions in @Query

@Query("select u from User u where u.firstname like %?1")

List findByFirstnameEndsWith(String firstname);

//Declare a native query at the query method using @Query

@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)

User findByEmailAddress(String emailAddress);

//Declare native count queries for pagination at the query method using @Query

@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",nativeQuery = true)

Page findByLastname(String lastname, Pageable pageable);

//Declaring manipulating queries

@Modifying

@Query("update User u set u.firstname = ?1 where u.lastname = ?2")

int setFixedFirstnameFor(String firstname, String lastname);

}

3.3 复杂查询 JpaSpecificationExecutor

Criteria 查询：是一种类型安全和更面向对象的查询

这个接口基本是围绕着Specification接口来定义的， Specification接口中只定义了如下一个方法：

Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb);

Criteria查询

基本对象的构建

1：通过EntityManager的getCriteriaBuilder或EntityManagerFactory的getCriteriaBuilder方法可以得到CriteriaBuilder对象

2：通过调用CriteriaBuilder的createQuery或createTupleQuery方法可以获得CriteriaQuery的实例

3：通过调用CriteriaQuery的from方法可以获得Root实例

过滤条件

1：过滤条件会被应用到SQL语句的FROM子句中。在criteria 查询中，查询条件通过Predicate或Expression实例应用到CriteriaQuery对象上。

2：这些条件使用 CriteriaQuery .where 方法应用到CriteriaQuery 对象上

3：CriteriaBuilder也作为Predicate实例的工厂，通过调用CriteriaBuilder 的条件方法( equal，notEqual， gt， ge，lt， le，between，like等)创建Predicate对象。

4：复合的Predicate 语句可以使用CriteriaBuilder的and, or andnot 方法构建

实例：

ImTeacher.java

@Entity

@Table(name = "im_teacher")

public class ImTeacher implements Serializable{

private static final long serialVersionUID = 1L;

@Id

@GeneratedValue

@Column(name = "id")

private int id;

@Column(name = "teacher_id")

private int teacherId;

@Column(name = "name")

private String name = "";

@Column(name = "age")

private int age;

@Column(name = "sex")

private String sex = "";

...

}

ImTeacherDao.java

public interface ImTeacherDao extends PagingAndSortingRepository,JpaSpecificationExecutor{

...

}

@Service

public class ImTeacherDaoService {

@Autowired

ImTeacherDao imTeacherDao;

/**

* 复杂查询测试

* @param page

*/

public Page findBySepc(int page, int size){

PageRequest pageReq = this.buildPageRequest(page, size);

Page imTeachers = this.imTeacherDao.findAll(new MySpec(), pageReq);

return imTeachers;

}

/**

* 建立分页排序请求

*/

private PageRequest buildPageRequest(int page, int size) {

Sort sort = new Sort(Direction.DESC,"age");

return new PageRequest(page,size, sort);

}

private class MySpec implements Specification{

@Override

public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {

//1.混合条件查询

Path exp1 = root.get("name");

Path exp2 = root.get("age");

query.where(cb.like(exp1, "%王%"),cb.equal(exp2, "45"));

//2.多表查询

/*Join join = root.join("imStudent", JoinType.INNER);

Path exp3 = join.get("name");

return cb.like(exp3, "%jy%");*/

return null;

}

}

}

3.4 分页

上个实例的发杂查询已经带有分页，若实例的DAO接口有继承PagingAndSortingRepository接口，则可以直接调用

Page impeacher = imTeacherDao.findAll(new PageRequest(1,20));

3.5 联表查询

方法：

法一：直接用Query语句或者上节复杂的连接查询，查出两张或多张表的数据。

法二：映射，接下来将详细介绍。

1)ImStudent.java

@Entity

@Table(name = "im_student")

public class ImStudent {

@Id

@GeneratedValue

@Column(name = "id")

private int id;

@Column(name = "student_id")

private int studentId;

@Column(name = "name")

private String name = "";

@Column(name = "age")

private int age;

@Column(name = "sex")

private String sex = "";

@Column(name = "teacher_id")

private int  teacherId;

@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH})

@JoinColumn(name="teacher_id", referencedColumnName="id", insertable=false, updatable=false)

private ImTeacher imTeacher;

...

}

2)在ImTeacher.java中添加

@OneToMany(mappedBy="imTeacher",cascade=CascadeType.ALL,fetch=FetchType.LAZY)

private Set imStudent = new HashSet();

...

3)根据学生名字查出其老师信息

@Query("SELECT teacher FROM ImTeacher teacher JOIN teacher.imStudent student WHERE student.name=:name")

ImTeacher findByStuName(@Param("name") String name);

根据老师名字查出其学生列表

@Query("SELECT student FROM ImStudent student JOIN student.imTeacher teacher WHERE teacher.name = :name")

Set findByStudByTeaName(@Param("name") String name);

四、总结

1、Hibernate的DAO层开发比较简单，对于刚接触ORM的人来说，能够简化开发工程，提高开发速度。

2、Hibernate对对象的维护和缓存做的很好，对增删改查的对象的维护要方便。

3、Hibernate数据库移植性比较好。

4、Hibernate功能强大，如果对其熟悉，对其进行一定的封装，那么项目的整个持久层代码会比较简单。