Hibernate HQL 语句

/**  *  */ package com.b510.example;

import java.util.Iterator; import java.util.List; import java.util.Map;

import org.hibernate.Query; import org.hibernate.Session;

/**  *  * @author XHW  *  * @date 2011-6-18  *  */ public class HibernateTest {

 /**   * @param args   */  public static void main(String[] args) {   HibernateTest test=new HibernateTest();   test.list();   test.oneResult();   test.iterate();   test.iterateHuancun();   test.jiChenChaXun();   test.touYinChaXun();   test.touYinChaXunMap();   test.touYinChaXunObject();   test.checkDistinct();   test.where(); }  public void list(){   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from User");   query.setMaxResults(2);//返回2条记录   query.setFirstResult(2);//从第2条记录开始   List<User> list=query.list();   session.getTransaction().commit();   for(User user:list){    System.out.println(user.getId()+", "+user.getUsername()+",  "+user.getPassword());   }  }

 public void oneResult(){   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from User");   query.setMaxResults(1);//返回1条记录   query.setFirstResult(2);//从第2条记录开始   User user=(User)query.uniqueResult();   System.out.println(user.getId()+", "+user.getUsername());   session.getTransaction().commit();  }  public void iterate(){//此方法的SQL语句格式为  n+1   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from User");   Iterator it=query.iterate();   while(it.hasNext()){    User user=(User)it.next();    System.out.println(user.getId()+", "+user.getUsername()+",  "+user.getPassword());   }   session.getTransaction().commit();  }  public void iterateHuancun(){   // 充分利用Hibernate中的缓存数据 如果Hibernate缓存中不存在数据，则使用list方法执行效率更高   //List<User> list=query.list();   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from User");   Iterator it=query.iterate();   while(it.hasNext()){    User user=(User)it.next();    System.out.println(user.getId()+", "+user.getUsername()+",  "+user.getPassword());   }   System.out.println("-----------------------------------------------");   it=query.iterate();   while(it.hasNext()){    User user=(User)it.next();    System.out.println(user.getId()+", "+user.getUsername()+",  "+user.getPassword());   }     session.getTransaction().commit();  }

 public void jiChenChaXun(){   /**    *  HQL中持久化类的继承查询    hibernate.cfg.xml 中的配置文件    <mapping resource="com/b510/example/User.hbm.xml" />    <mapping resource="com/b510/example/Profile.hbm.xml" />    */   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from java.lang.Object");   List<Object> list=query.list();   for(Object o:list){    System.out.println(o.getClass().getName());   }     session.getTransaction().commit();  }  public void touYinChaXun(){//投影查询  返回的是一个Object数组   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("select id,username,password from User");     List<Object[]> list=query.list();    session.getTransaction().commit();

   for(Object[] o:list){     System.out.println(o[0]+",  "+o[1]+",  "+o[2]);    }  }  public void touYinChaXunObject(){   //投影查询 返回的是一个对象   /**    * 在User.java持久化类中添加构造方法才能执行，否则就会报错    * public User(Integer id,String username,String password){    * this.id=id;    * this.username=username;    * this.password=password;    * }    */   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("select new User(id,username,password) from User");     List<User> list=query.list();    session.getTransaction().commit();

   for(User user:list){     System.out.println(user.getId()+",  "+user.getUsername()+",  "+user.getPassword());    }  }

 public void touYinChaXunMap(){   //使用Map查询   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("select new Map(u.id as id,u.username as name) from User as u");   //可以把as省略   //Query query=session.createQuery("select new Map(u.id as id,u.username as name) from User u");     List<Map> list=query.list();

  for(Map map:list){     System.out.println(map.get("id")+",   "+map.get("name"));    }    session.getTransaction().commit();  }  public void checkDistinct(){   //使用distinct查询   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("select distinct username from User");   List<String> list=query.list();

  for(String name:list){     System.out.println(name);    }    session.getTransaction().commit();  }  public void where(){   //使用where查询   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.createQuery("from User where id not between 200 and 2000");   List<User> list=query.list();

  for(User user:list){     System.out.println(user.getId()+user.getUsername());    }   //投影查询 中使用where子句   query=session.createQuery("select username from User where id=2");   List<String> listname=query.list();

  for(String name:listname){     System.out.println(name);    }   //in查询   query=session.createQuery("from User where username in ('Hongten','Hanyuan','dfgd')");   List<User> listin=query.list();

  for(User user:listin){     System.out.println(user.getId()+user.getUsername());    }   //like查询   query=session.createQuery("from User where username not like 'Hon%'");   List<User> listlike=query.list();

  for(User user:listlike){     System.out.println(user.getId()+user.getUsername());    }   //null查询   query=session.createQuery("from User where password is null");   List<User> listnull=query.list();

  for(User user:listnull){     System.out.println(user.getId()+user.getUsername());    }   //and查询   query=session.createQuery("from User where password is not null and id<5");   List<User> listand=query.list();   for(User user:listand){     System.out.println(user.getId()+user.getUsername()+user.getPassword());    }   //order by   query=session.createQuery("from User order by username,id desc");   List<User> listorderby=query.list();

  for(User user:listorderby){     System.out.println(user.getId()+user.getUsername());    }   //使用"?"号 作为参数占位符，一条HQL语句中可以使用多个？   //query.setInteger(0,2)   //query.setString(0,"Hongten")   query=session.createQuery("select username from User where username=?");   query.setString(0,"Hongten");   List<String> listwenhao=query.list();   for(String name:listwenhao){     System.out.println(name);    }    session.getTransaction().commit();  } }

结果：

Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_ limit ?,         ? 3, Hanyuan,  5645645 4, dfgd,  5645645 Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_ limit ?,         ? 3, Hanyuan Hibernate:     select         user0_.id as col_0_0_     from         users.user user0_ Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 1, Hongten,  123 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 2, Hanyuan,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 3, Hanyuan,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 4, dfgd,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 5, dfgd,  5645645 Hibernate:     select         user0_.id as col_0_0_     from         users.user user0_ Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 1, Hongten,  123 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 2, Hanyuan,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 3, Hanyuan,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 4, dfgd,  5645645 Hibernate:     select         user0_.id as id0_0_,         user0_.username as username0_0_,         user0_.password as password0_0_     from         users.user user0_     where         user0_.id=? 5, dfgd,  5645645 ----------------------------------------------- Hibernate:     select         user0_.id as col_0_0_     from         users.user user0_ 1, Hongten,  123 2, Hanyuan,  5645645 3, Hanyuan,  5645645 4, dfgd,  5645645 5, dfgd,  5645645 Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_ Hibernate:     select         profile0_.id as id1_,         profile0_.user_id as user2_1_,         profile0_.email as email1_,         profile0_.phone as phone1_,         profile0_.mobile as mobile1_,         profile0_.address as address1_,         profile0_.postcode as postcode1_     from         users.profile profile0_ com.b510.example.User com.b510.example.User com.b510.example.User com.b510.example.User com.b510.example.User com.b510.example.Profile com.b510.example.Profile com.b510.example.Profile com.b510.example.Profile com.b510.example.Profile Hibernate:     select         user0_.id as col_0_0_,         user0_.username as col_1_0_,         user0_.password as col_2_0_     from         users.user user0_ 1,  Hongten,  123 2,  Hanyuan,  5645645 3,  Hanyuan,  5645645 4,  dfgd,  5645645 5,  dfgd,  5645645 Hibernate:     select         user0_.id as col_0_0_,         user0_.username as col_1_0_     from         users.user user0_ 1,   Hongten 2,   Hanyuan 3,   Hanyuan 4,   dfgd 5,   dfgd Hibernate:     select         user0_.id as col_0_0_,         user0_.username as col_1_0_,         user0_.password as col_2_0_     from         users.user user0_ 1,  Hongten,  123 2,  Hanyuan,  5645645 3,  Hanyuan,  5645645 4,  dfgd,  5645645 5,  dfgd,  5645645 Hibernate:     select         distinct user0_.username as col_0_0_     from         users.user user0_ Hongten Hanyuan dfgd Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         user0_.id not between 200 and 2000 1Hongten 2Hanyuan 3Hanyuan 4dfgd 5dfgd Hibernate:     select         user0_.username as col_0_0_     from         users.user user0_     where         user0_.id=2 Hanyuan Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         user0_.username in (             'Hongten' , 'Hanyuan' , 'dfgd'         ) 1Hongten 2Hanyuan 3Hanyuan 4dfgd 5dfgd Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         user0_.username not like 'Hon%' 2Hanyuan 3Hanyuan 4dfgd 5dfgd Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         user0_.password is null Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         (             user0_.password is not null         )         and user0_.id<5 1Hongten123 2Hanyuan5645645 3Hanyuan5645645 4dfgd5645645 Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     order by         user0_.username,         user0_.id desc 5dfgd 4dfgd 3Hanyuan 2Hanyuan 1Hongten Hibernate:     select         user0_.username as col_0_0_     from         users.user user0_     where         user0_.username=? Hongten