HQL Query Statements

1. The result set of a multi-table query is an array of objects, that is, the column properties of the two-dimensional array are composed of the properties of the two objects. Each row of records is an array of objects, and the elements in the array correspond to specific objects.

String hql = "from Tbookinfo book, bookselection sel where book.id = Sel.bookid"; ==from Order as O inner join o.products as product;
Collection result = new ArrayList ();
Transaction tx = NULL;
try {
Session session = Hibernateutil.currentsession ();
tx = Session.begintransaction ();
Query query = session.createquery (SQL);
result = Query.list ();
Tx.commit ();

} catch (Exception e) {
Throw e;
} finally {
Hibernateutil.closesession ();
}
ArrayList sList = (ArrayList) result;
Iterator Iterator1 = Slist.iterator ();
while (Iterator1.hasnext ()) {
object[] o = (object[]) iterator1.next ();

Here you can add each object to the list collection for the foreground JSP to call
Tbookinfo BookInfo = (tbookinfo) o[0];
Bookselection Bookselect = (bookselection) o[1];
System.out.println ("Bookinfo-title:" + bookinfo.gettitle ());
System.out.println ("Bookselection-bookselectionid:" + Bookselect.getid ());
}

Difference: SQL and HQL

SQL is for data table and column queries, HQL is for object and property queries, HQL keywords are case insensitive, but class names and attributes are case-sensitive. If the query is an entire class object, you can omit select.

1. Simple query

HQL = "from Employee";

HQL = "from Employee as E"; Using aliases

"From Employee E"; Using aliases, the AS keyword can be omitted

2, with the filter conditions (can use aliases): Where

HQL = "from Employee where id<10";

HQL = "from Employee e WHERE e.id<10";

HQL = "from Employee e WHERE e.id<10 and e.id>5";

3, with the sorting conditions: ORDER By default ascending: ASC; Descending: DESC

HQL = "From the Employee e WHERE e.id<10 ORDER by E.name";

HQL = "from Employee e WHERE e.id<10 ORDER by E.name DESC";

HQL = "From the Employee e WHERE e.id<10 ORDER BY e.name Desc, id ASC";//asc Ascending, desc descending: Sort by First name descending order, with the same name in ascending order by ID.

The result set found in all three cases is a collection of list objects.

4. Specify the SELECT clause (You cannot use SELECT *)

HQL = "Select E.name from Employee e"; querying only one column, the element type of the returned collection is the type of this property. The resulting set is a list, type is the type of the property, and the returned object is not an array of objects, because it has only one column.

HQL = "Select E.id,e.name from Employee e"; querying multiple columns, the element type of the returned collection is an object array. The type of the returned collection element is exactly the array of objects in the collection, and the object array holds the value of the property you are querying.

HQL = "Select New Employee (e.id,e.name) from employee E"; You can use the new syntax to specify that some of the properties of a query are encapsulated into an object. This type of query requires a corresponding construction method in the object.

5. Aggregation function: Count (), Max (), Min (), AVG (), SUM ()

HQL = "Select min (id) from Employee"; The returned result is the type of the id attribute

6, group: GROUP By ... Having

HQL = "Select E.name,count (e.id) from the Employee e GROUP by E.name";//The collection element returned is an array of objects. Note: The condition after GROUP by must be the one previously selected.

HQL = "Select E.name,count (e.id) from the Employee e GROUP by E.name have COUNT (e.id) >1";//The Returned collection element is an array of objects. Note: The condition after GROUP by must be the one previously selected. Having is the choice within the group, that is, after the grouping is completed.

HQL = "Select E.name,count (e.id) from the Employee e WHERE id<9 GROUP by E.name have COUNT (e.id) >1";

HQL = "Select E.name,count (e.id)" +//query name and number
"From Employee E" +//query from this table
"WHERE id<50" +//id<50
"GROUP BY E.name" +//Group by name (same name as a group)
"Having count (e.id) >=1" +//filter criteria, which are filtered for the group (when the group is completed and selected)
"Order by Count (e.id) ASC";//Sort

Such as:

7, Connection query/HQL is an object-oriented query

>> Internal connection (inner keyword can be omitted)

HQL = "Select E.id,e.name,d.name from Employee e INNER JOIN e.department d";

HQL = "Select E.id,e.name,d.name from Employee e JOIN e.department D"; For many-to-one, that is, both sides have the same attribute field to query out. Here the staff and department is a many-to-one connection, the employee has the department's attribute (department primary key as foreign key). The result here is the name of the department to which the employee belongs.

>> LEFT OUTER join (outer keyword can be omitted)

The left outer connection is the name of the table on the left, the data are all listed, the right table has a corresponding display, no display is empty.

hql = "Select E.id,e.name,d.name from the Employee e left JOIN e.department D where e.id>40";

：

>> right outer join (outer keyword can be omitted)

The right outer connection as the name implies is the right table data are all listed, the left side of the table has a corresponding display, no display is empty.

HQL = "Select E.id,e.name,d.name from Employee e right JOIN e.department D where id>20";

：

You can use a more simple method

HQL = "Select E.id,e.name,e.department.name from Employee e";

The result of this query is the same as the internal connection, the corresponding records will be displayed

such as: [1, WKL, XXX department]

8. >> Update

int result = Session.createquery (//
"UPDATE Employee e SET e.name=?" WHERE id>15 ")//
. Setparameter (0, "anonymous")//
. executeupdate (); Returns the result of the int type, indicating how many rows were affected.

9. >> Delete

int result = Session.createquery (//
"DELETE from Employee e WHERE id>15")//
. executeupdate (); Returns the result of the int type, indicating how many rows were affected.

HQL Query Statements