Hibernate Data Query

• HQL: Hibernate query language, which is an Object-Oriented Query Language provided by Hibernate. (1) set various query conditions in query statements (2) support dynamic binding of parameters (3) support projection query, paging query, connection query, group query, and subquery (4) built-in Aggregate functions 2. the Query interface in HQL Hibernate is the Query interface specifically used to execute HQL statements. 1. create Query object query = session. createQuery ("fromDept"); 2. execute the query to List results 1) List all results <Dept> depts = query. list (); for (Dept dept: depts ){...} • 2) List a single result Query = session. createQuery ("from Deptwhere id = 1"); query. setMaxResults (1); Dept dept = (Dept) query. uniqueResult (); 3) iterative access result Iterator <Dept> it = query. iterate (); While (it. hasnext () {} basic HQL Syntax: persistence class names are case sensitive. 1) Select the persistence class to be queried. String hql = "from Dept" Query query = session. createQuery (hql); String hql = "from Dept as d"; 2) Projection Query String hql = "select id, name fromDept"; query Query = session. createQuery (hql); List list = query. list (); intsize = list = null? 0: list. size (); for (inti = 0; I <size; I ++) {Object [] obj = (Object []) list. get (I); System. out. println (obj [1] + "" + obj [2]);} • Creates a class for the selected Attribute and provides a construction method with the two attributes as parameters, this class is used to encapsulate query results. 3) where Condition Clause • String hqlString = "from Dept d whered. createdTime> = '2017-01-01 '"; • Query query = session. createQuery (hqlString); • String hqlString = "from Dept d where d. name like 'java % 'andd. employees is not empty and d. createdTime <current_date () "; • bind query parameters: • bind by parameter name • String hql =" from Dept whereid>: id and name like: likeName "• List <Dept> depts = session. createQuery (hql ). setLong ("id", newLong (ingputID) ). SetString ("likeName", "%" + inputName + "% "). list (); • bind by parameter position • 5) distinct filter duplicate values • Select distinct createTime from Dept • 6) Aggregate Function • select count (d) from Dept d • 7) order by sorting • From Dept d order by d. createdTime asc, d. name desc • 8) group by grouping Records • Select count (e) from Employee e group by e. dept; • 9) having performs conditional filtering on grouped data • Select e. dept. name from Eployee e group by e. dept havingcount (e)> 1 Query by PAGE The setFirstResult (intfirstResult) interface provides two methods for batch display of query results: the number of records in the record set starts to fetch data. The default value is 0. SetMaxResults (intmaxResults): sets the maximum number of objects returned by each query. Session. beginTransaction (); String hql = "from Employee"; List <Employee> empls = session. createQuery (hql ). setMaxResults (pageSize ). setFirstResult (pageNo-1) * pageSize ). list (); session. getTransaction (). commit (); batch modification and deletion: The database is operated directly using SQL statements. After execution, the data in the current session cache is not synchronized with the data in the database. Stringhql = "delete Dept as d where d. name like: likeName "; Queryquery = session. createQuery (hql ). setString ("likeName", "% 3%"); int count#query.exe cuteUpdate (); connection query • Inner join left outer join • Right outer join fulljoin String hql = "FROM Dept d JOIN d. employees e "+" WHERE d. name LIKE: likeName "; capture connection query fetch Capture: when an object data is loaded from a database, it is loaded together with the data in the collection, this reduces the data of SQL statements and improves query efficiency. From Dept d leftjoin Fetch d. employees e where d. name like "% web %" subquery From Dept dwhere (Select count (e) from d. employees e)> 1 • name query: Write an SQL query statement in a ing file and obtain the query statement through the getNameQuery () method of the Session in the program. <Queryname = "findDeptsByCondition"> <! [CDATA [from Dept d where d. name like: likeName]> </query> session. getNamedQuery ("findDeptsByCondition "). setString ("likeName", "% a % "). list ();