I don’t like JPA, it makes simple things so confusing, that is why I spent some hours to come up with these examples in the hope that I only need to copy/paste in the future without giving it a second thought.

The entities used in these examples are:

A Grade can have many Clazz.
A Clazz has one Room and one head Teacher.
A Clazz can have many Course and many Teacher.
A Course can have many TextBook.

The association between Grade and Clazz is through gradeCode field, associations among other entities are through id field.

The persistence-unit used in these examples is Hibernate, database is mysql. I haven’t tested others.

@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;    
    @Column
    private String name;

…}

@Entity
@NamedEntityGraphs({
        @NamedEntityGraph(name = "graph.Clazz.teacherAndCourse",
                attributeNodes = @NamedAttributeNode(value = "headTeacher", subgraph = "courses"),
                subgraphs = @NamedSubgraph(name = "courses", attributeNodes = @NamedAttributeNode("courses"))),
        @NamedEntityGraph(name = "graph.Clazz.teacher",
                attributeNodes = @NamedAttributeNode(value = "headTeacher"))
})
public class Clazz extends AbstractEntity {  
    @Column
    private String gradeCode;

    @OneToOne //(fetch = FetchType.LAZY)
    private Room room;

    @OneToOne(fetch = FetchType.LAZY)
    private Teacher headTeacher;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
            @JoinColumn(name="gradeCode", referencedColumnName="gradeCode", insertable=false, updatable=false)
    })
    private Grade grade;

…}

@Entity
@NamedEntityGraphs({
        @NamedEntityGraph(name = "graph.Course.textBooks",
                attributeNodes = @NamedAttributeNode(value = "textBooks"))

})
public class Course extends AbstractEntity { 
    @ManyToMany(mappedBy = "courses")
    private Set<Teacher> teachers = new HashSet<>();

    @OneToMany(mappedBy = "course",fetch = FetchType.LAZY)
    private Set<TextBook> textBooks=new HashSet<>();

…}

@Entity
public class Grade extends  AbstractEntity {

    @Column
    private String gradeCode;

…}

@Entity
public class Teacher extends AbstractEntity {  

    @ManyToMany
    @JoinTable(name = "teach_course",
            joinColumns = @JoinColumn(name="teach_id", referencedColumnName="id"),
            inverseJoinColumns = @JoinColumn(name="course_id", referencedColumnName="id"))
    private Set<Course> courses = new HashSet<>();

…}

@Entity

public class TextBook extends AbstractEntity {
    @ManyToOne(fetch = FetchType.LAZY)
    private Course course;

…}

ToOne fetch

By default, JPA uses eager fetch to fetch ToOne associations (including OneToOne, ManyToOne), but note, it doesn’t use a single select with JOIN clause to fetch associations, instead it issues separate select sqls to fetch them.

Clazz has 3 ToOne associations:

@OneToOne //(fetch = FetchType.LAZY)
private Room room;
@OneToOne(fetch = FetchType.LAZY)
private Teacher headTeacher;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({
@JoinColumn(name="gradeCode", referencedColumnName="gradeCode", insertable=false, updatable=false)
})

private Grade grade;

If we query Clazz:

List<Clazz> ret = em.createQuery( "from Clazz", Clazz.class ).getResultList();

JPA issues these sqls:

select
    clazz0_.id as id1_1_,
    clazz0_.name as name2_1_,
    clazz0_.grade_id as grade_id3_1_,
    clazz0_.room_id as room_id4_1_
from
   Clazz clazz0_




select
    room0_.id as id1_8_0_,
    room0_.name as name2_8_0_
from
   Room room0_
where
   room0_.id=?

select
    department0_.id as id1_3_0_,
    department0_.name as name2_3_0_,
    department0_.code as code3_3_0_,
    department0_.head_id as head_id4_3_0_
from
   Department department0_
where
   department0_.code=?

What is interesting here is even though Clazz-Grade association is defined FetchType.LAZY, JPA still eager fetches Grade. Clazz-Grade association is not done through id, it seems for such associations, JPA will do eager fetch.

Fetch VS Join

The difference between Fetch and Join is Fetch will fetch associates while Join only fetch the main entity:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery();
Root root = query.from(Teacher.class);
root.fetch("courses", JoinType.INNER);
query.select(root);
query.where(cb.equal(root.get("id"), teacherId));


List<Teacher> ret = em.createQuery(query).getResultList();
em.close();

This will issue this sql (notice the selected fields include associates):

select
    teacher0_.id as id1_12_0_,
    course2_.id as id1_2_1_,
    teacher0_.name as name2_12_0_,
    course2_.name as name2_2_1_,
    courses1_.teach_id as teach_id1_11_0__,
    courses1_.course_id as course_i2_11_0__
from
   Teacher teacher0_
inner join
   teach_course courses1_
on teacher0_.id=courses1_.teach_id
inner join
   Course course2_
on courses1_.course_id=course2_.id
where
   teacher0_.id=1

Use Join:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery();
Root root = query.from(Teacher.class);
Join join = root.join("courses", JoinType.INNER);
query.select(root);
query.where(cb.equal(join.get("id"), courseId));

List<Teacher> ret = em.createQuery(query).getResultList();
em.close();

This will issue the following sql (notice the selected fields doesn’t have associates):

select
    teacher0_.id as id1_12_,
    teacher0_.name as name2_12_
from
   Teacher teacher0_
inner join
   teach_course courses1_
on teacher0_.id=courses1_.teach_id
inner join
    Course course2_
on courses1_.course_id=course2_.id
where
   course2_.id=1

Another difference is, with Join it is easy to express where conditions on joined tables, as highlighted above. We can do similar things with Fetch, but first we need cast it:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery();
Root root = query.from(Teacher.class);
query.select(root);
query.distinct(true);

/*
by casting Fetch to JoinImplementor, you can express conditions on joined tables
* */

JoinImplementor fetch = (JoinImplementor) root.fetch("courses", JoinType.INNER);
query.where(cb.equal(fetch.get("id"), courseId));

List<Teacher> ret = em.createQuery(query).getResultList();
em.close();

This will produce the following sql:

select
    teacher0_.id as id1_12_,
    teacher0_.name as name2_12_
from
   Teacher teacher0_
inner join
   teach_course courses1_
on teacher0_.id=courses1_.teach_id
inner join
    Course course2_
on courses1_.course_id=course2_.id
where
   course2_.id=1

Interestingly, if you combine Fetch and Join, you will cause duplicated joins:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Clazz.class);
Root root = query.from(Clazz.class);
Join join = root.join("headTeacher", JoinType.INNER);
Fetch fetch = root.fetch("headTeacher", JoinType.INNER);
query.select(root);
query.where(cb.equal(join.get("id"), 1));

List<Clazz> ret = em.createQuery(query).getResultList();
em.close();

select
    clazz0_.id as id1_1_0_,
    teacher2_.id as id1_12_1_,
    clazz0_.name as name2_1_0_,
    clazz0_.gradeCode as gradeCod3_1_0_,
    clazz0_.headTeacher_id as headTeac4_1_0_,
    clazz0_.room_id as room_id5_1_0_,
    teacher2_.name as name2_12_1_
from
   Clazz clazz0_
inner join
   Teacher teacher1_
on clazz0_.headTeacher_id=teacher1_.id
inner join
   Teacher teacher2_
on clazz0_.headTeacher_id=teacher2_.id
where
   teacher1_.id=1

Another thing to look out for is, with ToMany associates, there might be duplicated entities returned, to remove duplicates, use query.distinct(true);

NamedEntityGraph

You can defined static NamedEntityGraph on Entities:

@Entity
@NamedEntityGraphs({
        @NamedEntityGraph(name = "graph.Clazz.teacherAndCourse",
                attributeNodes = @NamedAttributeNode(value = "headTeacher", subgraph = "courses"),
                subgraphs = @NamedSubgraph(name = "courses", attributeNodes = @NamedAttributeNode("courses"))),

        @NamedEntityGraph(name = "graph.Clazz.teacher",
                attributeNodes = @NamedAttributeNode(value = "headTeacher"))
})
public class Clazz extends AbstractEntity {

To invoke it:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery();
Root root = query.from(Clazz.class);
query.select(root);
query.distinct(true);

EntityGraph graph = this.em.getEntityGraph("graph.Clazz.teacherAndCourse");
List<Clazz> ret = em.createQuery(query).setHint("javax.persistence.loadgraph",graph).getResultList();

Note, JPA issues left joins for NamedEntityGraph:

select
    distinct clazz0_.id as id1_1_0_,
    teacher1_.id as id1_12_1_,
    course3_.id as id1_2_2_,
    clazz0_.name as name2_1_0_,
    clazz0_.gradeCode as gradeCod3_1_0_,
    clazz0_.headTeacher_id as headTeac4_1_0_,
    clazz0_.room_id as room_id5_1_0_,
    teacher1_.name as name2_12_1_,
    course3_.name as name2_2_2_,
    courses2_.teach_id as teach_id1_11_0__,
    courses2_.course_id as course_i2_11_0__
from
   Clazz clazz0_
left outer join
   Teacher teacher1_
on clazz0_.headTeacher_id=teacher1_.id
left outer join
   teach_course courses2_
on teacher1_.id=courses2_.teach_id
left outer join
   Course course3_
on courses2_.course_id=course3_.id

You can also build dynamic NamedEntityGraph. I can’t find out how to defined sub-sub graph with static NamedEntityGraph, but you can build dynamic sub-sub NamedEntityGraph:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Clazz.class);
Root root = query.from(Clazz.class);
query.distinct(true);

EntityGraph<Clazz> graph = this.em.createEntityGraph(Clazz.class);
graph.addAttributeNodes("headTeacher");

Subgraph<Object> subgraph = graph.addSubgraph("headTeacher");
subgraph.addAttributeNodes("courses");

Subgraph<Object> subsubGraph = subgraph.addSubgraph("courses");
subsubGraph.addAttributeNodes("textBooks");

List<Clazz> ret = em.createQuery(query).setHint("javax.persistence.loadgraph",graph).getResultList();em.close();

This will generate the following sql (strangely, sub-sub entity fields are selected twice):

select
    distinct clazz0_.id as id1_1_0_,
    teacher1_.id as id1_12_1_,
    course3_.id as id1_2_2_,
    textbooks4_.id as id1_14_3_,
    clazz0_.name as name2_1_0_,
    clazz0_.gradeCode as gradeCod3_1_0_,
    clazz0_.headTeacher_id as headTeac4_1_0_,
    clazz0_.room_id as room_id5_1_0_,
    teacher1_.name as name2_12_1_,
    course3_.name as name2_2_2_,
    courses2_.teach_id as teach_id1_11_0__,
    courses2_.course_id as course_i2_11_0__,
    textbooks4_.name as name2_14_3_,
    textbooks4_.course_id as course_i3_14_3_,
    textbooks4_.course_id as course_i3_14_1__,
    textbooks4_.id as id1_14_1__
from
    Clazz clazz0_
left outer join
    Teacher teacher1_
on clazz0_.headTeacher_id=teacher1_.id
left outer join
    teach_course courses2_
on teacher1_.id=courses2_.teach_id
left outer join
   Course course3_
on courses2_.course_id=course3_.id
left outer join
    TextBook textbooks4_
on course3_.id=textbooks4_.course_id

I can’t find out how to express where conditions on NamedEntityGraph, to do so, I need to use JPQL:

Query query = em.createQuery("select c, t, co, te from Clazz c join c.headTeacher t left join t.courses co left join co.textBooks te where co.id=:id");

query.setParameter("id", courseId);
List ret = query.getResultList();

em.close();

for (int i = 0; i <ret.size() ; i++) {
    Object[] rowObject=(Object[])ret.get(i);
    for(Object o: rowObject){
        System.out.println(o);
    }
    System.out.println("\n");
}

This will generate the following sql:

select
    clazz0_.id as id1_1_0_,
    teacher1_.id as id1_12_1_,
    course3_.id as id1_2_2_,
    textbooks4_.id as id1_14_3_,
    clazz0_.name as name2_1_0_,
    clazz0_.gradeCode as gradeCod3_1_0_,
    clazz0_.headTeacher_id as headTeac4_1_0_,
    clazz0_.room_id as room_id5_1_0_,
    teacher1_.name as name2_12_1_,
    course3_.name as name2_2_2_,
    textbooks4_.name as name2_14_3_,
    textbooks4_.course_id as course_i3_14_3_
from
    Clazz clazz0_
inner join
    Teacher teacher1_
        on clazz0_.headTeacher_id=teacher1_.id
left outer join
    teach_course courses2_
        on teacher1_.id=courses2_.teach_id
left outer join
    Course course3_
        on courses2_.course_id=course3_.id
left outer join
    TextBook textbooks4_
        on course3_.id=textbooks4_.course_id
where
    course3_.id=?

Note, JPA assembles each resulting row into an Object array, which contains entities in the join order. The above code generates the following output:

test.Clazz:1,clazz1
test.Teacher:1,teacher1
test.Course:1,course1
test.TextBook:1,textBook1


test.Clazz:1,clazz1
test.Teacher:1,teacher1
test.Course:1,course1
test.TextBook:2,textBook2

Pagination

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Clazz.class);
Root root = query.from(Clazz.class);
query.orderBy(cb.asc(root.get("id")));
query.where(cb.like(root.get("name"), "%zz%"));

EntityGraph graph = this.em.getEntityGraph("graph.Clazz.teacher");

TypedQuery typedQuery = em.createQuery(query);
typedQuery.setFirstResult(1);
typedQuery.setMaxResults(2);


List<Clazz> ret = typedQuery.setHint("javax.persistence.loadgraph", graph).getResultList();em.close();

For ToOne associations, Hibernate appends limit clause:

select
    clazz0_.id as id1_1_0_,
    teacher1_.id as id1_12_1_,
    clazz0_.name as name2_1_0_,
    clazz0_.gradeCode as gradeCod3_1_0_,
    clazz0_.headTeacher_id as headTeac4_1_0_,
    clazz0_.room_id as room_id5_1_0_,
    teacher1_.name as name2_12_1_
from
   Clazz clazz0_
left outer join
   Teacher teacher1_
on clazz0_.headTeacher_id=teacher1_.id
where
   clazz0_.name like ?
order by
   clazz0_.id asc limit ?,?

For ToMany associates, because the result may contain duplications, Hibernate doesn’t append limit clause, it first gets all data, and does pagination in memory:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Course.class);
Root root = query.from(Course.class);

query.orderBy(cb.asc(root.get("id")));
query.where(cb.ge(root.get("id"), 0));

EntityGraph graph = this.em.getEntityGraph("graph.Course.textBooks");

TypedQuery typedQuery = em.createQuery(query);
typedQuery.setFirstResult(0);
typedQuery.setMaxResults(50000);

List<Course> ret = typedQuery.setHint("javax.persistence.fetchgraph",graph).getResultList();em.close();

select
    course0_.id as id1_2_0_,
    textbooks1_.id as id1_14_1_,
    course0_.name as name2_2_0_,
    textbooks1_.name as name2_14_1_,
    textbooks1_.course_id as course_i3_14_1_,
    textbooks1_.course_id as course_i3_14_0__,
    textbooks1_.id as id1_14_0__
from
    Course course0_
left outer join
    TextBook textbooks1_
        on course0_.id=textbooks1_.course_id
where
    course0_.id=1
order by
    course0_.id asc

PerfSpy

Sunday, December 24, 2017

JPA examples

ToOne fetch

Fetch VS Join

NamedEntityGraph

Pagination

No comments:

Post a Comment