HQL 语句

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

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

import org.hibernate.Criteria; import org.hibernate.FetchMode; import org.hibernate.Hibernate; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.transform.Transformers;

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

 /**   * @param args   */  public static void main(String[] args) {   HibernateTest test = new HibernateTest();   test.nameHQL();   test.userSQL();  }

 public void nameHQL(){//命名HQL语句   //这种方法是要在对应的POJO类的映射文件中添加代码，如：在User.hbm.xml文件中添加以下代码   //</class>      //<!-- 命名的    HQL  -->      //<query name="com.b510.examples.User">                //<![CDATA[select u from User u where u.id between :begin and :end]]>      //</query>      //</hibernate-mapping>   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Query query=session.getNamedQuery("com.b510.examples.User");   query.setInteger("begin", new Integer(1));   query.setInteger("end", new Integer(5));   List<User> list=query.list();   for(User user:list){    System.out.println(user.getId()+user.getUsername());   }   session.getTransaction().commit();  }

 public void userSQL(){//使用sql语句      //sql语句中用的对象如：user不是POJO 类，而是数据库表名，但是可以转化为 程序中的POJO类，这样我们食用的时候就会很方便，   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   String sql="select * from user";   SQLQuery sqlQuery=session.createSQLQuery(sql);   //转化为pojo类，如果不做转化，则是一个Object[]数组类型，使用起来很不方便   sqlQuery.addEntity(User.class);   List<User> list=sqlQuery.list();   for(User user:list){    System.out.println("ID:  "+user.getId()+"   username:  "+user.getUsername()+"   password:  "+user.getPassword());   }   System.out.println("----------------------------------------------");   //另一种形式的sql语句   String sqlOther="select p.id,p.email from profile p,user u where p.user_id=u.id";   sqlQuery=session.createSQLQuery(sqlOther);   sqlQuery.addScalar("id",Hibernate.INTEGER).addScalar("email", Hibernate.STRING);   sqlQuery.setResultTransformer(Transformers.aliasToBean(Profile.class));   List<Profile> listOther=sqlQuery.list();   for(Profile profile:listOther){    System.out.println("    ID:  "+profile.getId()+"  Email：　"+profile.getEmail());   }   System.out.println("----------------------------------------------");   //另一种形式的sql语句,sql语句中使用"?"作为参数的占位符   String sqlwenhao="select p.id,p.email from profile p,user u where p.user_id=u.id and u.id=?";   sqlQuery=session.createSQLQuery(sqlwenhao);   sqlQuery.setInteger(0, new Integer(3));   sqlQuery.addScalar("id",Hibernate.INTEGER).addScalar("email", Hibernate.STRING);   sqlQuery.setResultTransformer(Transformers.aliasToBean(Profile.class));   List<Profile> listwenhao=sqlQuery.list();   for(Profile profile:listwenhao){    System.out.println("    ID:  "+profile.getId()+"  Email：　"+profile.getEmail());   }   System.out.println("----------------------------------------------");   //另一种形式的sql语句,sql语句中使用"一个字符名称如：':Hongten'"作为参数的占位符   String sqlzifu="select p.id,p.email from profile p,user u where p.user_id=u.id and u.id=:Hongten";   sqlQuery=session.createSQLQuery(sqlzifu);   sqlQuery.setInteger("Hongten", new Integer(3));   sqlQuery.addScalar("id",Hibernate.INTEGER).addScalar("email", Hibernate.STRING);   sqlQuery.setResultTransformer(Transformers.aliasToBean(Profile.class));   List<Profile> listzifu=sqlQuery.list();   for(Profile profile:listzifu){    System.out.println("    ID:  "+profile.getId()+"  Email：　"+profile.getEmail());   }   System.out.println("----------------------------------------------");   //另一种形式的sql语句,这是查询符合条件的全部的数据     String sqlquanbu="select {p.*} from profile p,user u where p.user_id=u.id";   sqlQuery=session.createSQLQuery(sqlquanbu);   sqlQuery.addEntity("p", Profile.class);   List<Profile> listquanbu=sqlQuery.list();   for(Profile profile:listquanbu){    System.out.println("  ID:  "+profile.getUser().getId()+"  Username :"+profile.getUser().getUsername()+"  Email:  "+profile.getEmail()+"  Address:  "+profile.getAddress());   }   System.out.println("----------------------------------------------");   //更新的SQL语句   String sqlUpdate="update user set username=? where id=?";   sqlQuery=session.createSQLQuery(sqlUpdate);   sqlQuery.setString(0, "功夫熊猫2");   sqlQuery.setInteger(1, new Integer(1));   int rowCount=sqlQuery.executeUpdate();   System.out.println("有 "+rowCount+" 条记录被更新！");   System.out.println("----------------------------------------------");   //删除的SQL语句   String sqlDelete="delete from profile where id=?";   sqlQuery=session.createSQLQuery(sqlDelete);   sqlQuery.setInteger(0, new Integer(1));   int rowCountDelete=sqlQuery.executeUpdate();   System.out.println("有 "+rowCountDelete+" 条记录被删除！");    session.getTransaction().commit();    }

}

结果：

log4j:WARN No appenders could be found for logger (org.hibernate.cfg.Environment). log4j:WARN Please initialize the log4j system properly. Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     where         user0_.id between ? and ? 1功夫熊猫2 2hanyuan 3hongwei123 4mingliu 5shouzhang Hibernate:     select         *     from         user ID:  1   username:  功夫熊猫2   password:  123 ID:  2   username:  hanyuan   password:  5645645 ID:  3   username:  hongwei123   password:  5645645 ID:  4   username:  mingliu   password:  5645645 ID:  5   username:  shouzhang   password:  5645645 ---------------------------------------------- Hibernate:     select         p.id,         p.email     from         profile p,         user u     where         p.user_id=u.id     ID:  1  Email：　hongtenzone@foxmail.com     ID:  2  Email：　hanyuan@foxmail.com ---------------------------------------------- Hibernate:     select         p.id,         p.email     from         profile p,         user u     where         p.user_id=u.id         and u.id=?     ID:  2  Email：　hanyuan@foxmail.com ---------------------------------------------- Hibernate:     select         p.id,         p.email     from         profile p,         user u     where         p.user_id=u.id         and u.id=?     ID:  2  Email：　hanyuan@foxmail.com ---------------------------------------------- Hibernate:     select         p.id as id1_0_,         p.user_id as user2_1_0_,         p.email as email1_0_,         p.phone as phone1_0_,         p.mobile as mobile1_0_,         p.address as address1_0_,         p.postcode as postcode1_0_     from         profile p,         user u     where         p.user_id=u.id   ID:  1  Username :功夫熊猫2  Email:  hongtenzone@foxmail.com  Address:  Guangzhoushi   ID:  3  Username :hongwei123  Email:  hanyuan@foxmail.com  Address:  GuangzhoushiDianbian ---------------------------------------------- Hibernate:     update         user     set         username=?     where         id=? 有 1 条记录被更新！ ---------------------------------------------- Hibernate:     delete     from         profile     where         id=? 有 1 条记录被删除！