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 条记录被删除!

目录
相关文章
|
12天前
|
关系型数据库 MySQL
MySQL查询语句大全
以上是一些常用的MySQL查询语句示例,可以根据实际需求进行适当的组合和修改。
12 0
|
3月前
|
SQL 关系型数据库 MySQL
SQL-分页查询and语句执行顺序
SQL-分页查询and语句执行顺序
|
11月前
|
SQL JSON Java
JPA的EntityManager来实现SQL或者HQL语句查询
JPA的EntityManager来实现SQL或者HQL语句查询
常用的Hql语句
本文主要讲常用的Hql语句
110 0
|
SQL XML 缓存
HQL的使用
HQL(Hibernate Query Language) 是面向对象的查询语言, 它和 SQL 查询语言有些相似. 在 Hibernate 提供的各种检索方式中, HQL 是使用最广的一种检索方式
266 0
|
SQL 数据库 索引