Criteria 基本查詢

參考出處: https://openhome.cc/Gossip/HibernateGossip/CriteriaBasic.html

使用Hibernate時，即使您不了解SQL的使用與撰寫，也可以使用它所提供的API來進行SQL語句查詢， org.hibernate.Criteria對SQL進行封裝，您可以從Java物件的觀點來組合各種查詢條件，由Hibernate自動為您產生 SQL語句，而不用特別管理SQL與資料庫相依的問題，就某個程度的意涵來看，這就像是在編譯時期也可以得到對SQL語法的檢查與驗證。

以最基本的查詢來說，如果您想要查詢某個物件所對應的資料表中所有的內容，您可以如下進行查詢：

Criteria criteria = session.createCriteria(User.class);

List users = criteria.list();        

for(Iterator it = users.iterator(); it.hasNext(); ) {

      User user = (User) it.next();

     System.out.println(user.getId() +    " \t " + user.getName() +  "/" + user.getAge());    

} 

Restrictions的幾個常用限定查詢方法如下表所示：

方 法	說 明
Restrictions.eq	等於
Restrictions.allEq	使用Map，使用key/value進行多個等於的比對
Restrictions.gt	大於 >
Restrictions.ge	大於等於 >=
Restrictions.lt	小於 <
Restrictions.le	小於等於 <=
Restrictions.between	對應SQL的BETWEEN子句
Restrictions.like	對應SQL的LIKE子句
Restrictions.in	對應SQL的in子句 Criteria criteria = session.createCriteria(EmpInfo.class).add( Restrictions.in("fname", new String[] { "bharat", "ankit","Vinay" }) );
Restrictions.and	and關係
Restrictions.or	or關係
Restrictions.sqlRestriction	SQL限定查詢

Hibernate Query 用法

Hibernate Query examples (HQL)
出處: mkyong.com
         https://www.mkyong.com/hibernate/hibernate-query-examples-hql/

 HQL Select Query Example

Retrieve a stock data where stock code is “7277”.

Query query = session.createQuery("from Stock where stockCode = :code ");
query.setParameter("code", "7277");
List list = query.list();

Query query = session.createQuery("from Stock where stockCode = '7277' ");
List list = query.list();

2. HQL Update Query Example

Update a stock name to “DIALOG1” where stock code is “7277”.

Query query = session.createQuery("update Stock set stockName = :stockName" +
        " where stockCode = :stockCode");
query.setParameter("stockName", "DIALOG1");
query.setParameter("stockCode", "7277");
int result = query.executeUpdate();

Query query = session.createQuery("update Stock set stockName = 'DIALOG2'" +
        " where stockCode = '7277'");
int result = query.executeUpdate();

3. HQL Delete Query Example

Delete a stock where stock code is “7277”.

Query query = session.createQuery("delete Stock where stockCode = :stockCode");
query.setParameter("stockCode", "7277");
int result = query.executeUpdate();

Query query = session.createQuery("delete Stock where stockCode = '7277'");
int result = query.executeUpdate();

4. HQL Insert Query Example

In HQL, only the INSERT INTO … SELECT … is supported; there is no INSERT INTO … VALUES. HQL only support insert from another table. For example

"insert into Object (id, name) select oo.id, oo.name from OtherObject oo";

Insert a stock record from another backup_stock table. This can also called bulk-insert statement.

Query query = session.createQuery("insert into Stock(stock_code, stock_name)" +
       "select stock_code, stock_name from backup_stock");
int result = query.executeUpdate();

The query.executeUpdate() will return how many number of record has been inserted, updated or deleted.

Reference

1. Hibernate 3.3.2 query documentation