hibernate中hql查詢

               

package com.tudou.hibernates.t1;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.Transaction;import org.hibernate.cfg.Configuration;public class TestGetHql { private static Configuration cfg = new Configuration().configure(); private static SessionFactory fac = cfg.buildSessionFactory(); private static Session son = fac.openSession(); // hql普通查詢 Card為類名，不是表名，可以寫全路徑 public static void from() {  String hql = "from Card";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // 條件查詢 where public static void where() {  String hql = "from Card where cardName='三國無雙'";  Query query = son.createQuery(hql);  List<Card> cardss = query.list();  for (Card c : cardss) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // 模糊查詢 like public static void like() {  String hql = "from Card where cardName like '%世%'";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // 邏輯條件查詢 > public static void gt() {  String hql = "from Card c where c.createDate >'2011-08-08'";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // 邏輯條件查詢 between and 此處用了別名，省略了as關鍵字 public static void between() {  String hql = "from Card c where c.createDate between '2011-08-08' and '2022-11-11'";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // 邏輯多條件查詢and public static void and() {  String hql = "from Card c where c.createDate between '2011-01-08' and '2022-11-11' and c.cardName like '%世%'";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName());   System.out.println(c.getCreateDate());  } } // update 更新 public static void update() {  String hql = "update Card as c set c.createDate='2011-03-03' where c.cardType.cardTypeId=3";  Query query = son.createQuery(hql);  int num = query.executeUpdate();  System.out.println(num + "行被更新。。。"); } // delete刪除 public static void delete() {  String hql = "delete from  Card as c where c.createDate='2011-03-04'";  Query query = son.createQuery(hql);  int num = query.executeUpdate();  System.out.println(num + "行被刪除。。。"); } // 單個屬性查詢 public static void simpleProperty() {  String hql = "select c.cardName from  Card as c where c.cardType.cardTypeId=1";  Query query = son.createQuery(hql);  List<String> name = query.list();  for (String s : name) {   System.out.println(s);  } } // 多個屬性查詢 其中cardTypeName直接通過card物件的cardType物件獲得，省去了使用普通的sql語句必須多表連線查詢的麻煩 public static void mulProperty() {  String hql = "select c.cardName,c.cardType.cardTypeName,c.createDate from  Card as c where c.cardType.cardTypeId=1";  Query query = son.createQuery(hql);  List<Object[]> obj = query.list();  for (Object[] o : obj) {   System.out.println(o[0] + "\t" + o[1] + "\t" + o[2]);  } } // 多個屬性查詢 物件導向方式 public static void orientedObject() {  String hql = "select new Card(c.cardName,c.createDate) from  Card as c";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName() + "\t" + c.getCreateDate());  } } // 函式查詢 public static void function() {  String hql = "select count(*),max(c.createDate) from  Card as c";  Query query = son.createQuery(hql);  List<Object[]> oo = query.list();  for (Object[] o : oo) {   System.out.println("總記錄數：" + o[0] + "\t最新日期為：" + o[1]);  } } // 排序 public static void orderBy() {  String hql = "from  Card as c order by c.createDate desc";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName() + "\t" + c.getCreateDate());  } } // 分組 public static void groupBy() {  String hql = "from  Card as c group by c.cardType.cardTypeId";  Query query = son.createQuery(hql);  List<Card> cards = query.list();  for (Card c : cards) {   System.out.println(c.getCardName() + "\t" + c.getCreateDate());  } } // 單個物件查詢 呵呵，奇怪吧，物件可以查詢出來 public static void simpleObject() {  String hql = "select c.cardType from  Card as c";  Query query = son.createQuery(hql);  query.setMaxResults(1);// 必須在查詢之前指定，使其返回單個物件  CardType cardType1 = (CardType) query.uniqueResult();  System.out.println(cardType1.getCardTypeName() + "\t"    + cardType1.getCreateDate()); } // 按照命令列引數 格式為： :引數名 public static void parameter() {  String hql = "select c.cardType from  Card as c where c.cardType.cardTypeId=:id";  Query query = son.createQuery(hql);  query.setParameter("id", 1);  query.setMaxResults(1);// 必須在查詢之前指定，使其返回單個物件  CardType cardType = (CardType) query.uniqueResult();  System.out.println(cardType.getCardTypeName() + "\t"    + cardType.getCreateDate()); } // 按照引數位置 從0開始 public static void parameterPosition() {  String hql = "select c.cardType from  Card as c where c.cardType.cardTypeId=?";  Query query = son.createQuery(hql);  query.setParameter(0, 1);  query.setMaxResults(1);// 必須在查詢之前指定，使其返回單個物件  CardType cardType = (CardType) query.uniqueResult();  System.out.println(cardType.getCardTypeName() + "\t"    + cardType.getCreateDate()); } // 多個引數 public static void mulParameter() {  String hql = "from  Card as c where c.cardType.cardTypeId in (3,2)";  Query query = son.createQuery(hql);  // query.setParameterList("id", new Object[]{1,2});  List<Card> cards = query.list();  for (Card o : cards) {   System.out.println(o.getCardName());  } } // inner join 查詢結果為多個物件的集合 public static void innerJoin() {  String hql = "from  Card as c inner join c.cardType";  Query query = son.createQuery(hql);  List<Object[]> cards = query.list();  for (Object[] o : cards) {   System.out.println(((Card) o[0]).getCardName() + "\t"     + ((CardType) o[1]).getCreateDate());  } } // leftJoin 查詢結果為多個物件的集合 public static void leftJoin() {  String hql = "from  CardType as c left join c.cards";  Query query = son.createQuery(hql);  List<Object[]> cards = query.list();  for (Object[] o : cards) {   // 由於儲存卡片時在多的一方card進行操作，使用了級聯。但手動插入的cardType可能沒有相應的卡片   if (o[1] != null) {// 當卡片不為空時    System.out.println(((CardType) o[0]).getCardTypeName() + "\t"      + ((Card) o[1]).getCardName());   } else {    System.out.println(((CardType) o[0]).getCardTypeName()      + "\t沒有相應的卡片");   }  } } // rightJoin 查詢結果為多個物件的集合 public static void rightJoin() {  String hql = "from  CardType as c right join c.cards";  Query query = son.createQuery(hql);  List<Object[]> cards = query.list();  // 插入時保證了每張卡片的型別，所以此處不用判斷卡片型別是否為空  for (Object[] o : cards) {   System.out.println(((CardType) o[0]).getCardTypeName() + "\t"     + ((Card) o[1]).getCardName());  } } // 使用子查詢 public static void childSelect() {  String hql = "from  CardType as c where (select count(*) from c.cards)>0";  Query query = son.createQuery(hql);  List<CardType> cards = query.list();  for (CardType c : cards) {   System.out.println(c.getCardTypeName() + "\t" + c.getCreateDate());  } } // 程式入口 public static void main(String[] args) {  // 測試方法  Transaction tr = son.beginTransaction();  // update();  mulParameter();  tr.commit();  son.close();  fac.close(); }}

hibernate投影查詢：

String hql="select u.id,u.name from user as u"  //1方法

String hql="select new com.mypack.User2(u.id,u.name) from User as u "  //2方法

Iterator userHQL= session.createQuery(hql).iterate();

 

while (userHql.hasNext()) {
   User user=(User)userHql.next();
   

   Object[] row=(Object[]) userHql.next();
   int id=(Integer)row[0];
   String nameString =(String)row[1];
   System.out.print(id+"\t"+nameString);
   
   //User2 user2=(User2) userHql.next();
   //System.out.println(user2.getId()+"\t\t"+user2.getName());
  } 

--------------------------------------------------

package com.mypack;

public class User2 {
 private int id;
 private String name;
 public User2(int id,String nameString){
  this.id=id;
  this.name=nameString;
 }

    ......省略了屬性的get,set方法

}