HQL常用的查詢語句

標籤：lang   let   多個   color   hibernate   outer   nat   ...   where   

摘錄自某人，比較有用，比較全。

// HQL: Hibernate Query Language.// 特點：// >> 1，與SQL相似，SQL中的文法基本上都可以直接使用。// >> 2，SQL查詢的是表和表中的列；HQL查詢的是對象與對象中的屬性。// >> 3，HQL的關鍵字不區分大小寫，類名與屬性名稱是區分大小寫。// >> 4，SELECT可以省略.                // 1，簡單的查詢，Employee為實體名而不是資料庫中的表名(物件導向特性)hql = "FROM Employee";hql = "FROM Employee AS e"; // 使用別名hql = "FROM Employee e"; // 使用別名，as關鍵字可省略// 2，帶上過濾條件的（可以使用別名）：Wherehql = "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，帶上排序條件的：Order Byhql = "FROM 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 Employee e WHERE e.id<10 ORDER BY e.name DESC, id ASC";// 4，指定select子句（不可以使用select *）hql = "SELECT e FROM Employee e"; // 相當於"FROM Employee e"hql = "SELECT e.name FROM Employee e"; // 只查詢一個列，返回的集合的元素類型就是這個屬性的類型hql = "SELECT e.id,e.name FROM Employee e"; // 查詢多個列，返回的集合的元素類型是Object數組hql = "SELECT new Employee(e.id,e.name) FROM Employee e"; // 可以使用new文法，指定把查詢出的部分屬性封裝到對象中// 5，執行查詢，獲得結果（list、uniqueResult、分頁 ）Query query = session.createQuery("FROM Employee e WHERE id<3");query.setFirstResult(0);query.setMaxResults(10); // 等同於 limit 0,10//兩種查詢結果list、uniqueResult// List list = query.list(); // 查詢的結果是一個List集合// Employee employee = (Employee) query.uniqueResult();// 查詢的結果是唯一的一個結果，當結果有多個，就會拋異常// 6，方法鏈List list = session.createQuery(//        "FROM Employee e")//        .setFirstResult(0)//        .setMaxResults(10)//        .list();// 7，聚集合函式：count(), max(), min(), avg(), sum()hql = "SELECT COUNT(*) FROM Employee"; // 返回的結果是Long型的hql = "SELECT min(id) FROM Employee"; // 返回的結果是id屬性的類型//8，分組: Group By ... Havinghql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name";hql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name HAVING count(e.id)>1";hql = "SELECT e.name,COUNT(e.id) FROM Employee e WHERE id<9 GROUP BY e.name HAVING count(e.id)>1";hql = "SELECT e.name,COUNT(e.id) " + //     "FROM Employee e " + //     "WHERE id<9 " + //     "GROUP BY e.name " + //     "HAVING count(e.id)>1 " + //     "ORDER BY count(e.id) ASC";hql = "SELECT e.name,COUNT(e.id) AS c " + //     "FROM Employee e " + //     "WHERE id<9 " + //     "GROUP BY e.name " + //     "HAVING count(e.id)>1 " + // 在having子句中不能使用列別名     "ORDER BY c ASC"; // 在orderby子句中可以使用列別名// 9，串連查詢 / HQL是物件導向的查詢 //>> 內串連（inner關鍵字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e JOIN e.department d"; hql = "SELECT e.id,e.name,d.name FROM Employee e INNER JOIN e.department d"; //>> 左外串連（outer關鍵字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e LEFT OUTER JOIN e.department d"; //>> 右外串連（outer關鍵字可以省略） hql = "SELECT e.id,e.name,d.name FROM Employee e RIGHT JOIN e.department d"; //可以使用更方便的方法 hql = "SELECT e.id,e.name,e.department.name FROM Employee e";// 10，查詢時使用參數// >> 方式一：使用‘?‘佔位 hql = "FROM Employee e WHERE id BETWEEN ? AND ?"; List list2 = session.createQuery(hql)//     .setParameter(0, 5)// 設定參數，第1個參數的索引為0。     .setParameter(1, 15)//     .list();// >> 方式二：使用變數名 hql = "FROM Employee e WHERE id BETWEEN :idMin AND :idMax"; List list3 = session.createQuery(hql)//     .setParameter("idMax", 15)//     .setParameter("idMin", 5)//     .list();// 當參數是集合時，一定要使用setParameterList()設定參數值 hql = "FROM Employee e WHERE id IN (:ids)"; List list4 = session.createQuery(hql)//     .setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 })//     .list();// 11，update與delete，不會通知Session緩衝// >> Updateint result = session.createQuery(//        "UPDATE Employee e SET e.name=? WHERE id>15")//        .setParameter(0, "無名氏")//        .executeUpdate(); // 返回int型的結果，表示影響了多少行。// >> Deleteint result1 = session.createQuery(//        "DELETE FROM Employee e WHERE id>15")//        .executeUpdate(); // 返回int型的結果，表示影響了多少行。

 

HQL常用的查詢語句