Recently, I encountered several slow query events during the data statistics query, and finally found that the problem occurred in Hibernate’s query operation. The default value of the FetchType on the @manytoone annotation is fetchtype.eager. Hibernate will automatically launch the join query of the associated table when the query is done. Once too many tables are associated, query efficiency will be greatly affected.

In a simple data query, there is nothing wrong with this query mechanism: the ability to automatically correlate an entity as it is queried makes program development extremely simple. But it is because of this method will be associated with too much information query, make a large number of data operations to the database brought too much pressure, the database can not bear the burden, resulting in slow query. There are several ways to solve the problem of slow queries caused by associative queries. For example, by adding the fetch = fetchType. LAZY attribute to the @manytoone annotation at the expense of some convenience; Another example is to create a view specifically with a composite query and call the data in the view in the composite query. Another example is to create a return value type specifically for a synthetic query.

This article presents a way to automatically remove unwanted association queries by defining the returned fields in code.

Scene Settings

Suppose there are four tables as follows: students, classes, teachers and schools. Each table has two fields, id and name. ER diagram is as follows:

The relationships between data tables are all N :1, and the example entities are as follows:

@entity public class Student {@id @GeneratedValue(strategy = GenerationType.IDENTITY ➊); private String name; @manyToOne (Cascade = CascadeType.persist bananas) private Clazz Clazz; // Omit empty constructor ★ and setter/getter}

• The ➊ form is set to increment
• The bananas were set to cascading
• An empty constructor is a must

[Success] The code of the class, teacher and school shall be completed by referring to the above code.

public interface StudentRepository extends CrudRepository<Student, Long>, JpaSpecificationExecutor {  
}

test

Query tests:

@SpringBootTest class StudentRepositoryTest { @Autowired StudentRepository studentRepository; @Autowired private EntityManager entityManager; ➊ Student Student. @beforeeach bananas public void BeforeEach () {School School = new School(); School.setName (" test school "); Teacher teacher = new Teacher(); Teacher.setName (" test teacher "); Clazz clazz = new Clazz(); Clazz. SetName (" test class "); this.student = new Student(); Student. setName(" test student "); teacher.setSchool(school); clazz.setTeacher(teacher); student.setClazz(clazz); this.studentRepository.save(student); } @Test public void find() { this.studentRepository.findById(student.getId()).get(); }}

• ➊ standby
• The old version by Bluebeard used @Before, please refer to the GitHub link from this article

The generated SQL statement is as follows:

select 
student0_.id as id1_2_0_, student0_.clazz_id as clazz_id3_2_0_, student0_.name as name2_2_0_, 
  clazz1_.id as id1_0_1_, clazz1_.name as name2_0_1_, clazz1_.teacher_id as teacher_3_0_1_, 
    teacher2_.id as id1_3_2_, teacher2_.name as name2_3_2_, teacher2_.school_id as school_i3_3_2_, 
      school3_.id as id1_1_3_, school3_.name as name2_1_3_ 
from student student0_ 
  left outer join clazz clazz1_ on student0_.clazz_id=clazz1_.id 
    left outer join teacher teacher2_ on clazz1_.teacher_id=teacher2_.id 
      left outer join school school3_ on teacher2_.school_id=school3_.id 
where student0_.id=1

When Hibernate queries a student, as shown above, it associates the fields annotated by @ManyToOne in the student entity, and it cleverly iterates the teacher fields in the class entity and the corresponding school fields in the teacher entity in turn.

Selection<Tuple>

Hiberante provides Selection in the synthesis to solve the problem of redundant field and redundant association in the query. When using Selection to query, it is necessary to first establish the corresponding constructor in the entity class, assuming that the current only need to query the ID and name information of students. First we need to create the following constructor:

    public Student(Long id, String name) {
        this.id = id;
        this.name = name;
        System.out.println("student construct");
    }

The sample code is as follows:

@Test public void findByColumn() { CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder(); ➊ CriteriaQuery < Student > CriteriaQuery = criteriaBuilder. CreateQuery (Student. Class); ➊ Root<Student> Root = CriteriaQuery. From (student.class); ➊ criteriaQuery. Multiselect (root) get (" id "), ➋ root. Get (" name ")), where (criteriaBuilder. Equal (root) get (" id ")) as (Long. Class), student. The getId (), toString ())); ➌ TypedQuery < Student > query = this. EntityManager. CreateQuery (criteriaQuery); ➍ List<Student> students = query.getResultList(); ➎}}

• The expression Criteriabuilder and the expression CriteriaQuery and root use in composite queries like this.
• The UpperCase created the output fields of this query as the ID and name fields of the Student entity.
• ➌ Set the query criteria
• ➍ generates a pre-query
• ➎ Execute the query

The information about the execution test console is as follows

select student0_.id as col_0_0_, student0_.name as col_1_0_ 
from student student0_ 
where student0_.id=1

student construct

As shown above, when multiselect is used to specify output fields in a comprehensive query, Hibernate only selects specified fields student.id and student.name during the select process, and no other tables are associated in the query. After the data is queried, the constructor in the Student entity is called.

Associated query

When associative query is needed, the above steps can still be followed: first, the corresponding constructor is established, and then the corresponding selection conditions are set. For example, the class ID and teacher ID need to be queried. The code is as follows:

    public Student(Long id, String name, Long clazzId, Long teacherId) {
        this.id = id;
        this.name = name;
        this.clazz = new Clazz();
        this.clazz.setId(clazzId);
        this.clazz.setTeacher(new Teacher());
        this.clazz.getTeacher().setId(teacherId);
        System.out.println("student construct invoked");
    }

The query code is as follows:

    @Test
    public void findByColumnWithJoin() {
        CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
        CriteriaQuery<Student> criteriaQuery = criteriaBuilder.createQuery(Student.class);
        Root<Student> root = criteriaQuery.from(Student.class);

        criteriaQuery
                .multiselect(root.get("id"),
                        root.get("name"),
                        root.get("clazz").get("id"),
                        root.get("clazz").get("teacher").get("id"))
                .where(criteriaBuilder.equal(root.get("id").as(Long.class), student.getId().toString()));

        TypedQuery<Student> query = this.entityManager.createQuery(criteriaQuery);

        List<Student> students = query.getResultList();
    }

The execution log is as follows:

select student0_.id as col_0_0_, student0_.name as col_1_0_, student0_.clazz_id as col_2_0_, 
  clazz1_.teacher_id as col_3_0_ 
from student student0_ 
  cross join clazz clazz1_ 
where student0_.clazz_id=clazz1_.id and student0_.id=1

student construct invoked

As shown above, Hibrenate automatically builds cascading SQL statements where necessary.

Selection< Tuple >

If you don’t want to add constructors to the query, you can also use Selection

. Again, the same query as above: The code for the query using Selection

is as follows:

@Test public void findByColumnWithJoinAndTuple() { CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class); ➊ Root<Student> Root = CriteriaQuery. From (student.class); criteriaQuery .multiselect(root.get("id"), root.get("name"), root.get("clazz").get("id"), root.get("clazz").get("teacher").get("id")) .where(criteriaBuilder.equal(root.get("id").as(Long.class), student.getId().toString())); TypedQuery<Tuple> query = this.entityManager.createQuery(criteriaQuery); ➋ List < Tuple > tuples = query. GetResultList (); List<Student> students = new ArrayList<>(); tuples.forEach(tuple -> { Student student = new Student(); student.setId((Long) tuple.get(0)); ➌ student. Elegantly-named setName ((String) the tuple. The get (1)); ➌ student. SetClazz (new Clazz ()); student.getClazz().setId((Long) tuple.get(2)); ➌ student. GetClazz (.) setTeacher (new the Teacher ()); student.getClazz().getTeacher().setId((Long) tuple.get(3)); ➌ students. The add (student); }); }

• The ➊ CriteriaQuery generic uses tuples
• The bananas also used a Tuple for the generic lookup
• ➌ converts the returned data of type Tuple to Student using the tuple. Get (index) method and type casting

The main information of the console is as follows:

select student0_.id as col_0_0_, student0_.name as col_1_0_, student0_.clazz_id as col_2_0_, 
  clazz1_.teacher_id as col_3_0_ 
from student student0_ 
  cross join clazz clazz1_ 
where student0_.clazz_id=clazz1_.id and student0_.id=1

The generated SQL code is still concise.

Matters needing attention

Because this query method uses strings in hardCode format (such as root.get(“id”)) during the query, the string depends on the entity structure. Spring JPA does not generate any errors at system startup when the entity structure is changed, but once the relevant query method is called it will cause system 500 errors because the string does not correspond to the entity class. So when you use this query method, you must use it in conjunction with unit testing!

conclusion

Hibernate is a great ORM framework and is the default choice for Spring JPA. The team has been following the principle of standing on the shoulders of giants and believing that the choices of giants are all right. In the selection of production projects, Hibernate is chosen without any hesitation. However, the performance of some statistical queries in recent production projects is not satisfactory, and there is a big gap compared with handwritten SQL. Therefore, I began to doubt Hibernate at the same time, I further deepened my in-depth study of Hibernate. During this period, I also learned a little knowledge about MyBatis. Conclusion: When using Hibernate correctly and reasonably, no matter in adding, updating and deleting data, or in batch deleting and comprehensive query data, Hibernate can achieve the characteristics of fast, convenient and object-oriented development on the premise of sacrificing a small amount of controllable performance, so it should be the first choice for small and medium-sized projects.

Reference documentation

Author: Pan Jie, Mengyunzhi Development Team of Hebei University of Technology