Shulou(Shulou.com)06/01 Report--

HQL (Hibernate QueryLanguage) is an object-oriented query language, which is somewhat similar to SQL query language.

1. Select/update/delete. From... Where... Group by... Having... Order by... Asc/desc

2. Select statement

1. Query the whole mapping object

String hql = "from Users"

Query query = session.createQuery (hql)

List users = query.list ()

2. Query the corresponding fields

/ / query some of the fields

String hql = "select name,passwd from Users"

Query query = session.createQuery (hql)

/ / An Object array is stored in the list queried by default.

List list = query.list ()

3. Modify the default query result (query.list ()) not in the form of Object [] array, but in the form of List.

/ / query some of these fields, add new list (), and notice that the l in list is lowercase. There is no need to import the package, so the list coming out through query.list () is no longer the default Object array, but the List collection.

String hql = "select new list (name,passwd) from Users"

Query query = session.createQuery (hql)

/ / An Object array is stored in the list queried by default, but the default Object array is no longer stored in the list, but the List collection

List list = query.list ()

4. HQL statement executes the written SQL statement

String sql= "update Table set field = 'test'"

Session session = HibernateSessionFactory.getSession ()

Session.createSQLQuery (sql) .executeUpdate ()

Ts.commit ()

3. Association query

I. connection

(the premise is that the relationship between the tables has been established, and the association starts with one to many parties.)

1. Implicit connection:

From Student s where s.room.id=1 (the room here is actually an one-to-many association in the entity class Student)

2. Show connections

From Student s left join fetch s.room r where r.id=1

HQL connection type inner join inner connection, can be abbreviated as joinleft outer join left outer connection, can be abbreviated as left joinright outer join right outer connection, can be abbreviated as right joinfull join full connection, and is not commonly used.

When using explicit connections, you can also provide additional connection conditions through the HQL keyword with, such as:

Hibernate converts this explicit join to the syntax of SQL99 multi-table joins, so the with keyword in the HQL statement is basically the same as the on keyword in SQL99: both are used to specify join conditions. By using the with keyword in the HQL statement, you can have the HQL statement execute a non-equivalent join query.

There are two other differences between implicit and explicit connections:

The underlying layer of the ① implicit connection will be converted into the cross connection of SQL99, and the explicit connection layer will be converted into the inner join, left join, right join and other connections of SQL99.

The results returned by ② implicit join and explicit join queries are different.

4. From clause

The from keyword is followed by the class name of the persistent class. For example: from Person indicates that all instances are selected from the Person persistence class.

Multiple persistent classes can also appear at the same time after from, resulting in a Cartesian product or cross-table join, but in practice this usage is rarely used, because usually when we may need to use cross-table joins, we can consider using implicit or explicit joins, and

Not directly followed by multiple table names after the from.

5. Aggregate function

HQL also supports the use of aggregation functions on selected attributes. The aggregate functions supported by HQL are exactly the same as those of SQL:

HQL's aggregation function avg calculates the average value of attributes count statistics the number of selected objects the maximum value of max statistical attribute values the minimum value of min statistical attribute values sum calculates the sum of attribute values

6. Batch function

UPDATE | DELETE FROM? [WHERE WHERE_CONDITIONS]

There are four points to note about the above grammatical format.

① in the FORM clause, the FROM keyword is optional, that is, it is entirely possible not to write the FROM keyword.

② can have only one class name in the FORM clause, and the class name cannot have an alias.

③ cannot use joins in batch HQL statements, either explicitly or implicitly. However, you can use subqueries in the WHERE clause.

The entire WHERE clause of ④ is optional. The syntax of the WHERE clause is exactly the same as that of the HQL clause.

12345Transaction txt=session.beginTransaction (); String hqlUpdate= "update User set name=:newName"; int updatedEntities=session.createQuery (hqlUpdate) .setString ("newName", "new name"). ExecuteUpdate (); txt.commit (); session.close ()

The Query.executeUpdate () method returns an integer value that is the number of records affected by this operation. We know that the underlying operation of Hibernate is actually done by JDBC, so if a batch of update or delete operations are converted into multiple update or delete statements, this method will only return the number of rows of records affected by the last SQL statement.

7. Binding parameters

A. Bind by parameter name:

Defining named parameters in a HQL statement starts with ":" in the following form:

Query query=session.createQuery ("from User user where user.name=:customername anduser:customerage=:age")

Query.setString ("customername", name)

Query.setInteger ("customerage", age)

In the above code, the named parameters customername and customerage are defined with: customername and: customerage, respectively, and then the setXXX () method of the Query interface is used to set the value of the named parameter. The setXXX () method contains two parameters, namely, the named parameter name and the named parameter actual value.

B. Bind by parameter position:

Use "?" in HQL query statements To define the parameter location, as follows:

Query query=session.createQuery ("from User user where user.name=? and user.age =?")

Query.setString (0dint name)

Query.setInteger (1jinage)

The binding parameter is also set using the setXXX () method, except that the first parameter of the setXXX () method represents the position number (numbered from 0) that the binding parameter appears in the HQL statement, and the second parameter still represents the actual value of the parameter.

Note: in actual development, it is advocated to bind named parameters by name, because it not only provides very good program readability, but also improves the maintainability of the program, because when the location of query parameters changes, there is no need to adjust the program code in the way of naming parameters by name.

C, setParameter () method:

Any type of parameter can be bound through the setParameter () method in Hibernate's HQL query, as shown in the following code:

String hql= "from User user where user.name=:customername"

Query query=session.createQuery (hql)

Query.setParameter ("customername", name,Hibernate.STRING)

As shown in the code above, the setParameter () method contains three parameters, namely, the named parameter name, the named parameter actual value, and the named parameter mapping type. For some parameter types, the setParameter () method can guess the corresponding mapping type according to the Java type of the parameter value, so there is no need to show and write out the mapping type. Like the example above, you can write this directly:

Query.setParameter ("customername", name); but for some types, you must specify the mapping type, such as the java.util.Date type, because it corresponds to multiple mapping types of Hibernate, such as Hibernate.DATA or Hibernate.TIMESTAMP.

D, setProperties () method: (setEntity ())

In Hibernate, you can use the setProperties () method to bind named parameters to the property values of an object, as shown in the program code:

Customer customer=new Customer ()

Customer.setName ("pansl")

Customer.setAge (80)

Query query=session.createQuery ("from Customer c where c.name=:name and c.age=:age")

Query.setProperties (customer)

The setProperties () method automatically matches the property value of the customer object instance to the named parameter, but requires that the named parameter name must have the same name as the corresponding attribute of the entity object.

There is also a special setEntity () method that associates named parameters with a persistent object, as shown in the following code:

Customer customer= (Customer) session.load (Customer.class, "1")

Query query=session.createQuery ("from Order order where order.customer=:customer")

Query. SetEntity ("customer", customer)

List list=query.list ()

The above code generates a SQL statement similar to the following:

Select * from order where customer_ID='1'

E. advantages of using binding parameters:

Why do we use binding named parameters? The existence of anything has its value. When it comes to binding parameters, there are two main advantages for HQL queries:

①, you can use the database to optimize the performance, because PrepareStatement is used to complete the query at the bottom of Hibernate, so for SQL statements with the same syntax and different parameters, you can make full use of the cache of precompiled SQL statements to improve query efficiency.

②, which can prevent SQL Injection security vulnerabilities:

SQL Injection is a * method specially assembled for SQL statements. For example, for our common user login, the user enters a user name and password on the login interface, and the login verification program may generate the following HQL statement:

"from User user where user.name='" + name+ "'and user.password='" + password+ "'"

Logically speaking, there is no problem with this HQL statement, and the login verification function will be completed correctly under normal circumstances, but if you enter "zhaoxin or'x verify verify x" in the user name when logging in, if you use the string assembly of the simple HQL statement, you will generate the following HQL statement:

"from User user where user.name='zhaoxin' or'x 'and user.password='admin'."

Obviously, the where sentence of this HQL statement will always be true, and the function of using a password will be meaningless. This is the basic principle of SQL Injection***.

Using binding parameters, you can handle this problem properly. When you use binding parameters, you will get the following HQL statement:

From User user where user.name=''zhaoxin'' or 'xbind quote' and user.password='admin'; 'shows that using binding parameters will parse the single quotation marks entered in the user name into strings (if you want to include single quotes in the string, you should use duplicate single quotation marks), so parameter binding can effectively prevent SQL Injection security vulnerabilities.

8. Other

The subquery / Where/order by/groud by is basically the same as SQL.

The having clause is used to filter packets, so the having clause can only be used when there is a group by clause. You cannot use having without a group by clause.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.