HQL 语句 / iVAN

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

时间： 2024-02-21 07:49:45

HQL 语句

HQL 语句的相关文章

hql语句查询实体类News的属性category为“生活类新闻”的LIST,HQL语句怎么写？

Castle学习笔记----使用HQL语句实现复杂查询

hql语句只能查询出数据库里面的第一条数据

关于hql语句查询的问题

hql-关于Hql语句，这条语句为什么查不出数据

原生的sql语句怎么写成HQL语句SELECT * FROM tmms_bookout WHERE c_id IN (SELECT cs_id FROM tm

查询关键词-hql语句如何查询字段包含关键词

Hql语句这样写怎么会报错误呢

Hibernate HQL 语句