Analysis of key points of database query using HQL statement in Java hibernate _java

One, Entity object query

Entity object queries are the basis of HQL queries, and as an object query language, the contents of the query string are replaced with the class name and the property name of the class, unlike SQL in query operations. This kind of query method is relatively simple, as long as has the SQL Foundation, uses the HQL is very simple, but some questions need to notice, is the query obtains the data is not the goal, needs to consider is how to write the efficient query statement, this is the discussion key point.

1.n+1 problem

(1) What is the n+1 problem
when I first heard this noun, I was puzzled that I had never heard of n+1 problem before, so what does it mean? N+1 refers to a table with n pieces of data, the N+1 SQL command is generated when you get the n data, which is called n+1. 1 refers to a statement that emits a list of query IDs, and N refers to the sending of N-SQL statements based on IDs to load related objects. This query operation is inefficient and tends to occur in iterators, which means that if we translate the query results directly into iterators, this problem occurs, as shown in the following code:

public void Testquery () {session 
  Session=null; 
   
  try{ 
    session=hibernateutils.getsession (); 
    Session.begintransaction (); 
     
    /** 
     * will appear n+1 problem, the so-called n+1 worth is to send out a n+1 SQL statement * * 
     1: Send a Query ID list statement 
     * * 
     N: Send an N-SQL statement based on ID, load related 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 generates a N+1 problem because the query returns an iterator so that it emits an SQL statement at once, depending on the query mechanism of iterator, which queries the data from the cache. If the data does not exist in the cache, the data is first converted into memory, so a SQL query statement is emitted at this time, so an SQL statement is generated every iteration. This kind of writing is actually a mistake, can use other methods to optimize the solution.

(2) to avoid n+1 problems
The n+1 problem arises because iterate is not used properly, but there are other ways to avoid this n+1 problem, here is a list of methods. The biggest difference between list and iterate is that list puts data into the cache, but does not take advantage of caching, and by default the list emits SQL statements each time. You can use list to save data to a database before using iterate so that iterate can read from the cache when accessing data, avoiding the n+1 problem, the code reads as follows:

 public void Testquery () {session session=null; 
    try{session=hibernateutils.getsession (); 
     
    Session.begintransaction (); 
     
     
    List Students=session.createquery ("from Student"). List (); 
    System.out.println ("---------------------------------"); /** * Avoids the n+1 problem * * Because the list operation will put the data into the session cache (first-level cache), so when the use of iterate * will be issued a query ID list of statements, and then according to the ID to the cache add 
    Load the corresponding data, if there is a matching data in the cache * then no longer issue the SQL statement based on the ID query, directly using the data in the cache * * Iterate method if there is data in the cache, it can improve performance, otherwise there is n+1 problem. 
     
    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 the list operation will put the data into the session cache (first-level cache), so when the use of iterate will be issued a query ID list of statements, and then based on the ID to the cache to load the corresponding data, if the cache with matching data, The SQL statement queried based on the ID is no longer issued and the data in the cache is used directly. Iterate method if there is data in the cache, it can improve performance, otherwise there is n+1 problem.

2. Object Navigation Query

Object navigation is the data that obtains another object by the object's attribute navigation in one object, which simplifies the query statement and optimizes the query method. If we could rewrite the object of another class in the usual way, it would be cumbersome to get the action of another object, and the object navigation comparison statement.

@SuppressWarnings ({"Unchecked", "rawtypes"}) public 
void TestQuery1 () {session 
  Session=null; 
   
  try{ 
    session=hibernateutils.getsession (); 
    Session.begintransaction (); 
     
    Returns a list of result set properties, and the attribute types in the element type and entity class are consistent 
    list students=session.createquery ("from Student s where s.classes.name like '%2% '"). List (); 
     
    For (iterator Ite=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 in the example above uses the method of object navigation, and the query statement is the information queried from the student object, but the object property of the object is derived from the Name property of the Classes object, at which point the Query method that uses objects navigation will obviously improve query efficiency, optimize the query statement, It is complicated to make a large number of connection statements if you change the normal query method.

Second, SQL Native query

The native query value is the use of SQL statements to query fetch data, rather than the use of HQL statements, it is in fact very simple, similar to HQL, only need to use the Createsqlquery method query, it is actually similar to the HQL 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 Ite=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 method within the query string is the SQL statement, it implements the underlying string query method, the difference is hql and do a layer of packaging, in Hibernate.cfg.xml configure the corresponding dialect options to complete the mapping.

Third, the connection inquiry

A connection query is often used in SQL to get a collection of multiple objects. Often used in the inner join, left JOIN, right join and so on, respectively refers to the internal connection query, the external connection query, the right-hand outer join query, they are returned in the query of the contents of the Cartesian product between the entities, The contents of the query and the contents of the left table, query content and the contents of the right table, the query function is powerful. The HQL connection Query method and the SQL connection query are the same on the query results, but slightly different on the query statement.

1. Internal connection

An HQL query can use a INNER JOIN statement or a JOIN statement query to obtain a result set that is a Cartesian product. Similar to the inner join query of SQL, HQL join query is divided into explicit and implicit two, the query is displayed refers to the query string has a join keyword, implicit query in the string does not need to add a join.

The INNER join 
@SuppressWarnings ({"Unchecked", "rawtypes"}) public 
void Testquery () {session 
  Session=null; 
   
  try{ 
    session=hibernateutils.getsession (); 
    Session.begintransaction (); 
     
    Returns a list of result set properties, with attribute types consistent in the element type and entity class list 
    students=session.createquery ("Select S.name,c.name from Student s join S.classes C "). List (); 
     
    For (iterator Ite=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. Outer connection
The outer joins are divided into the left outer join and the right outer join query, the query method is similar, but the result set of query is different, they are in the query result and the outer connection of the SQL is same, different is the writing, the concrete use code is as follows:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public 
void Testquery () {session 
  Session=null; 
   
  try{ 
    session=hibernateutils.getsession (); 
    Session.begintransaction (); 
     
    Returns a list of result set attributes, and the attribute types in the element type and entity class are consistent list 
    students=session.createquery ("Select S.name,c.name from Student S-left Join S.classes C "). List (); 
     
    For (iterator Ite=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 the LEFT OUTER JOIN query statement, and the corresponding right outer join query is similar to the left outer join, which translates to Right-to-left. The queried data is saved to the list object, and the query content can be obtained by using the list.

Four, external named query

An external named query is to write the query statement to the mapping file, use <query> tags in the mapping file to define the HQL statement, so that the definition of the HQL statement can implement the function configuration function, if there is a problem only need to modify the configuration. If you want to use the SQL statement, you can use the Session.getnamedquery () method in your program to get the HQL query string, as shown in the following example.

1. External query statement
The following example demonstrates the application of an external query statement, adds a <query> label to the mapping file, adds a Name property to the label, and adds a string to the <! [cdata[]]>, so that you can get the corresponding query string in your program by the Name property of 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 "> 
 
 

An external named query places the query statement in the mapping file. So it can be considered a public query string that can be queried in the program file to use the string, which is its advantage so that other program files can be used, and as a common string increases the convenience of modification.

2. Program Application

When you define an external query statement to use in your program, HQL provides a getnamequery method to get an external query string that adds an external cursor name, and HQL gets the corresponding block of SQL statements based on the cursor name query, such as the following program code:

External named query 
@SuppressWarnings ({"Unchecked", "rawtypes"}) public 
void Testquery () {session 
  Session=null; 
   
  try{ 
    session=hibernateutils.getsession (); 
    Session.begintransaction (); 
     
    List Students=session.getnamedquery ("Querystudent"). Setparameter (0). List (); 
     
    For (iterator Ite=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); 
  }