1. Entity object query
Entity object query is the basis of hql query. As an object query language, it is different from SQL during query operations. The content in the query string should be replaced by the class name and the class attribute name. This query method is relatively simple. As long as you have SQL skills, it is very simple to use hql. However, there are some problems that need to be paid attention to, that is, querying and obtaining data is not the purpose, and what needs to be considered is how to write efficient query statements, which is the focus of the discussion.
1.N+1 problem
(1) What is the N+1 problem
When I first heard this noun, I may have doubts. I have never heard of the N+1 problem before. So what does it mean? N+1 refers to N data in a table, so when obtaining these N data, N+1 SQL command will be generated. This operation is called N+1. Here, 1 refers to issuing a statement that searches for id list, and N refers to issuing N SQL statements based on id to load related objects. This kind of query operation is very inefficient and is often generated in iterators. That is to say, if we directly convert the query results into iterators, this problem will occur, as follows:
public void testQuery(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); /** * There will be an N+1 problem. The so-called N+1 worth is to issue N+1 sql statements* * 1: Issue a statement that querys the id list* * N: Issue N sql statements based on the id and load the relevant object*/ Iterator iter=session.createQuery("from Student").iterate(); while(iter.hasNext()){ Student student=(Student)iter.next(); System.out.println(student.getName()); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }The above query code will cause N+1 problem, because the query is returned as an iterator, so that an SQL statement will be issued if it is not generated once. This mainly depends on the Iterator's query mechanism, which querys data from the cache. If the data does not exist in the cache, the data will be converted into memory first, so an SQL query statement will be issued at this time, so a SQL statement will be generated every iteration. This writing method is actually a mistake and can be solved using other methods to optimize.
(2) Avoid N+1 problem
The problem of N+1 occurs because of the improper use of Iterate. Of course, other methods can be used to avoid this problem of N+1. Here is a List method. The biggest difference between List and Iterate is that List puts data into the cache, but does not use the cache. By default, list will issue SQL statements every time. Before using Iterate, you can use list to save the data to the database, so that iterate can read from the cache when accessing the data, avoiding the occurrence of N+1 problems. The code is as follows:
public void testQuery(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); List students=session.createQuery("from Student").list(); System.out.println("--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Then no SQL statements query based on id are issued, and the data in the cache are directly used * * Iterate method If data exists in the cache, it can improve performance, otherwise N+1 problems will occur */ // You can use the as alias Iterator iter=session.createQuery("from Student").iterate(); while(iter.hasNext()){ Student student=(Student)iter.next(); System.out.println(student.getName()); } session.getTransaction().commit(); } catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }The above example avoids the N+1 problem, because after performing the list operation, the data will be placed in the session cache (first-level cache). Therefore, when using iterate, a statement will be issued to query the id list, and then the corresponding data will be loaded in the cache according to the id. If there is data matching it in the cache, the SQL statement query based on the id will no longer be issued, and the data in the cache will be directly used. Iterate method can improve performance if data exists in the cache, otherwise N+1 problems will occur.
2. Object navigation query
Object navigation refers to obtaining data from another object according to the object's attribute navigation in one object. This can simplify query statements and optimize query methods. If we follow our usual ideas, we may rewrite and write an object of another class to obtain the operation of another object, which is more cumbersome to compare the object navigation statement.
@SuppressWarnings({ "unchecked", "rawtypes" }) public void testQuery1(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); //Return the result set attribute list, the element type and attribute types in the entity class are the same List students=session.createQuery("from Student s where s.classes.name like '%2%'").list(); for(Iterator item=students.iterator();ite.hasNext();){ Student obj=(Student)ite.next(); System.out.println(obj.getName()); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }The query statement in the above example uses the object navigation method. The query statement is the information query from the Student object, but the object attributes to be compared come from the name attribute of the Classes object. At this time, using the object navigation query method will significantly improve the query efficiency and optimize the query statement. If it were an ordinary query method, a large number of connection statements may be generated, which is very complicated.
2. SQL native query
The native query value is to use SQL statements to query and obtain data, rather than use hql statements. Its usage method is actually very simple, similar to hql. You only need to use createSQLQuery method to query. It is actually similar to hql's createQuery method. The code is as follows:
public void testQeury(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); List list=session.createSQLQuery("select * from t_student").list(); for(Iterator item=list.iterator();ite.hasNext();){ Object[] obj=(Object[])ite.next(); System.out.println(obj[0]+","+obj[1]); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }The above code uses the createSQLQuery method. The query string in the method is an SQL statement. It implements the underlying string query method. The difference is that HQL has another layer of packaging, and configures the corresponding dialect options in Hibernate.cfg.xml to complete the mapping.
3. Connection query
Connection queries are often used in SQL to obtain a collection of multiple objects. Among them, the most commonly used are inner join, left join, right join, etc., which refer to inner join query, left outer join query, and right outer join query. The content they return during query is the Cartesian product between entities, the content of the query and some content of the left table, the query content and some content of the right table, and the query function is powerful. The connection query method of hql and the connection query of sql are the same in query results, but there are slight differences in query statements.
1. Inner connection
The intrajoin query of hql can be queried using inner join statement or join statement, and the result set obtained is Cartesian product. Similar to SQL intra-connection query, hql's join query is divided into two types: explicit and implicit. The displayed query refers to the join keyword in the query string. Implicit query does not need to add join in the string.
//Inner connection @SuppressWarnings({ "unchecked", "rawtypes" }) public void testQuery(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); //Return the result set attribute list, the element type and attribute types in the entity class are the same List students=session.createQuery("select s.name,c.name from Student s join s.classes c").list(); for(Iterator item=students.iterator();ite.hasNext();){ Object[] obj=(Object[])ite.next(); System.out.println(obj[0]); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }
2. External connection
External connections are divided into left outer connection and right outer connection query. The query methods are similar, but the query result is different. They are the same as the external connection of SQL in the query results. The difference is the writing method. The specific code is as follows:
@SuppressWarnings({ "unchecked", "rawtypes" }) public void testQuery(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); //Return the result set attribute list, the element type and attribute types in the entity class are the same List students=session.createQuery("select s.name,c.name from Student s left join s.classes c").list(); for(Iterator item=students.iterator();ite.hasNext();){ Object[] obj=(Object[])ite.next(); System.out.println(obj[0]); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }The above code uses a left outer join query statement. The corresponding right outer join query method is similar to the left outer join. Just convert left to right. The query data is saved in the list object, and the query content can be obtained through the list.
4. External naming query
External named query refers to writing query statements into a mapping file and using the <query> tag to define hql statements in the mapping file. In this way, the defined hql statements can realize the function configuration function. If there is a problem, just need to modify the configuration. If you want to use this sql statement, you can use the session.getNamedQuery() method in the program to get the hql query string, as shown in the following example.
1. External query statement
The following example demonstrates the application of external query statements, add the <query> tag to the mapping file, add the name attribute to the tag, and add the string to <![CDATA[]]>, so that the corresponding query string can be obtained in the program according to the name attribute of the query.
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="com.src.hibernate.Student" table="t_student"> <id name="id"> <generator//id> <property name="name"/> <property name="createTime"></property> <!-- Add a new Classes column to the Student on one side, and the column name should be the same as the List of Classes.hbm.xml --> <many-to-one name="classes" column="classesid"></many-to-one> </class> <query name="queryStudent"> <![CDATA[ select s from Student s where s.id<? ]]> </query> </hibernate-mapping>
The external named query puts the query statement in the mapping file, so it can be considered a public query string. This is its advantage, so that other program files can be obtained and used. In addition, it increases the convenience of modification as a public string.
2. Program application
After defining external query statements, it is necessary to use them in the program. hql provides the getNameQuery method to get the external query statement string. An external cursor name needs to be added to this method. hql will query the corresponding sql statement block based on the cursor name, as follows:
//External naming query @SuppressWarnings({ "unchecked", "rawtypes" }) public void testQuery(){ Session session=null; try{ session=HibernateUtils.getSession(); session.beginTransaction(); List students=session.getNamedQuery("queryStudent").setParameter(0, 10).list(); for(Iterator item=students.ite.hasNext();){ Student obj=(Student)ite.next(); System.out.println(obj.getName()); } session.getTransaction().commit(); }catch(Exception e){ e.printStackTrace(); session.getTransaction().rollback(); } finally{ HibernateUtils.closeSession(session); } }