"Hibernate Step by Step"--HQL query filter and related aggregate function query

The previous article discussed the connection query in the HQL query, its query syntax in the function and SQL connection query is the same, the inner join query obtains the Cartesian product between the relations, the outer join query is to obtain a relational table and with another relational table of the collection part, the specific use method see the previous article, Finally, the method of the external named query is discussed. This article will discuss the HQL parameter query, function query and query filter in detail.

First, parameter query

The parameter query is actually using the equivalent substitution method, using the value of the set to replace the symbol specified in the string, or by using the parameter name, the string to replace the value of the parameter name, so as to avoid the problem of SQL injection, stitching the string will appear SQL injection problem. HQL provides two ways to query a parameter by using the symbol "?" and then using the Setparameter method to set the replacement string, and the other is to use the parameter name, define the parameter name, and use the Setparameter method to replace the string that specifies the parameter name.

1.1 parameter Symbols--?

By using symbols? To set the parameter content to be added, that is, the symbol here is actually the HQL parameter. If you want to assign a value to a parameter, you can use the Hql method Setparameter, which overloads the parameter, provides two parameters, one that can pass the argument where the parameter appears, and the other assigns a value to the parameter. The following code is specific:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//method Chain Programming List Students=session.createquery ("Select S from Student s where s.name like? "). Setparameter (0, "%0%"). List (); for (Iterator Iter=students.iterator (); 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);}}

Where do you want to use the string where parameters are used? Instead, then use the Setparameter method to replace the contents of the parameter. The first parameter in the method can be a string or an index, and if you use it as a parameter, you must specify the index of the.

1.2 Parameter name

The use of symbolic parameters is discussed earlier, and the method of parameter name query is briefly introduced, that is, you can define parameters by using the method of parameter names, which requires specifying the name of the parameter, and then using Setparameter to set the name substitution parameter, the HQL parameter definition is the colon + parameter name.

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//Can be used: parameter name of the way to pass the parameter//method chain Programming list students= Session.createquery ("Select S from Student s where S.name like:myname"). Setparameter ("MyName", "%0%"). List (); for ( Iterator iter=students.iterator (); 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);}}

In the above example, a like fuzzy query is used, and the query has 0 of all student information appearing in the student's name, a parameter named MyName is defined in the query string, and the parameter is assigned a value using Setparameter.

1.3 Parameter Collection

The above simple introduction to the use of hql single parameter, very simple, if the string needs to configure the parameters of multiple connections to do it, then use Setparameter one after the assignment? HQL provides setparameterlist to comfortably assign values to the parameters, such as the following example:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void TestQuery5 () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//Can be used: parameter name of the way to pass the parameter//method chain Programming list students= Session.createquery ("Select S from Student s where S.id in (: IDs)"). Setparameterlist ("IDs", New object[]{1,2,3,4}). List ( ); for (Iterator Iter=students.iterator (); 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);}}

In the example above, a collection query in, which queries the collection for content that meets the requirements of the collection, defines a parameter named IDs, and the specific content of the parameter is an object collection, which is assigned using Setparameterlist.

Second, function query

HQL's specific query provides the basic SQL query method, also supports the corresponding database function Query method, you can use the database function in the query statement to set the format of the query content and the content you want to query, such as the date format function Date_format:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//Date Function List Students=session.createquery ("Select S from Student s where Date_format (S.createtime, '%y-%m ') =? "). Setparameter (0, "2009-07"). List (); for (Iterator Iter=students.iterator (); 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);}}

In the example above, the student information is queried according to the time of creation, and the query content is obtained by using the Date_format () method to convert the string into the format of the corresponding query content.

Alternatively, you can use the Between...and of the database ... To query the data in a range, the following example queries the student information for a time period:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//Query time period list Students=session.createquery ("Select S from Student s where Date_format (S.createtime, '%y-%m ') between? and? "). Setparameter (0, "2009-07"). Setparameter (1, "2012-07"). List (); for (Iterator Iter=students.iterator (); 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);}}

Aggregate queries, HQL does not support * queries, but you can use the count (*) query. To use a GROUP BY grouping statement, you must use an aggregate function in the statement. The following example shows how the HQL aggregate function and the grouping query are used:

@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); session.begintransaction ();//Returns the result set attribute list, the element type and the attribute type in the entity class are consistent list students= Session.createquery ("Select C.name,count (s) from Classes C joins c.students s GROUP by c.name Order by C.name"). 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);}}

Getting the first row, HQL provides a way to get the first row of data in the result set, which is Uniqueresult (), gets the result set by using the SQL statement, and then uses the Uniqueresult () method in the method chain to get the data for the first row by default, as in the following code:

<pre name= "code" class= "Java" > @SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session Session=null;try{session=hibernateutils.getsession (); session.begintransaction ();//return result set attribute list, element type and attribute type in entity class consistent// List Students=session.createquery ("SELECT count (*) from Student"). List ();//long count= (Long) students.get (0);// The above query method, similar to the Uniqueresult () method used below, is a long count= (long) session.createquery ("SELECT count (*) from Student"). Uniqueresult ( ); System.out.println ("count=" +count); Session.gettransaction (). commit ();} catch (Exception e) {e.printstacktrace (); Session.gettransaction (). rollback (); Finally{hibernateutils.closesession (session);}}

The preceding code example provides two ways to get the first row of data, one by using the Get () method, to get the contents of the row in the result set, and the other using the Uniqueresult () method to get the contents of the new row.

third, query filter

A query filter provides the process of filtering the contents of a query, defining a filter in the mapping file, enabling the filter in the program, and assigning values to the filter parameters. The steps are: First use the <filter-def> tag to configure the filter in the mapping of the entity object to be queried, and add the corresponding <filter> filter in the relative <class> tag And then use the Enablefilter () method in the corresponding program file to start the filter, assigning values to the parameters defined by the filter, and automatically corresponding to the filter at execution time.

Listing one: The mapping file, the corresponding filter is configured in the mapping file, a filter named Testfilter is defined in the mapping file, an execution condition is added to the filter in class, and the execution parameter myID is added to the condition.

<?xml version= "1.0"? ><! DOCTYPE hibernate-mapping Public "-//hibernate/hibernate mapping DTD 3.0//en" "http://hibernate.sourceforge.net/ Hibernate-mapping-3.0.dtd ">

Listing II: Program files, use Enablefilter to enable filters in program files, and assign values to filter parameters.
@SuppressWarnings ({"Unchecked", "rawtypes"}) public void Testquery () {Session session=null;try{session= Hibernateutils.getsession (); Session.begintransaction (); Session.enablefilter ("Testfilter"). SetParameter ("myID", 10);//Returns the result set attribute list, the element type and the attribute type in the entity class are consistent list Students=session.createquery ("from Student"). 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);}}

Conclusion
HQL Basic Query method has been discussed, mainly for the use of HQL to pay attention to the content of the detailed discussion, in each query method has added a corresponding example, through the example to deepen the use of HQL query method, SQL based on the use of HQL will be very simple.