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.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.where();   test.function();   test.update();   test.jiaoChaCheck();   test.innerJoin();   test.QBC();   test.leftOuterJoin();   test.rightOuterJoin();  }

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();

 }

public void function() {// 把大写字母转化为小写字母   // 作用可以用在:比如在一个用户注册的程序中,大小写不容易区分,但是全部转化为小写后就可以很容易进行比较   Session session = HibernateSessionFactoryUtil.getSessionFactory()     .openSession();   session.beginTransaction();   // 输出原始的数据   Query query = session.createQuery("select username from User");   List<String> list = query.list();

  for (String name : list) {    System.out.println(name);   }   System.out.println("-------------------------------------------");   // 输出的数据全部转化为小写   query = session.createQuery("select lower(username) from User");   List<String> listChange = query.list();

  for (String name : listChange) {    System.out.println(name);   }   session.getTransaction().commit();  }

public void update() {   Session session = HibernateSessionFactoryUtil.getSessionFactory()     .openSession();   session.beginTransaction();   Query query = session     .createQuery("update User set username='洪伟1231' where id=?");   query.setInteger(0, 3);   int rowCount = query.executeUpdate();   System.out.println(rowCount);   session.getTransaction().commit();  }

public void operateProfile() {// 对profile这个类执行HQL语句操作   Session session = HibernateSessionFactoryUtil.getSessionFactory()     .openSession();   session.beginTransaction();   // 执行查询操作   Query query = session.createQuery("from Profile");   List<Profile> list = query.list();   for (Profile profile : list) {    System.out.println(profile.getId() + profile.getEmail()      + profile.getAddress() + profile.getMobile()      + profile.getPostcode());   }   // 执行删除操作   query = session.createQuery("delete from Profile where id=?");   query.setInteger(0, 3);   int rowCount = query.executeUpdate();   System.out.println(rowCount);   session.getTransaction().commit();  }

 public void jiaoChaCheck() {//交叉查询   //这种方法查询出来的结果是笛卡尔积,对于我们开发中没有多大用处   Session session = HibernateSessionFactoryUtil.getSessionFactory()     .openSession();   session.beginTransaction();   Query query=session.createQuery("from User,Profile");   List<Object[]> list=query.list();   for(Object[] values:list){    User user=(User)values[0];    System.out.print("ID :"+user.getId()+",UserName:"+user.getUsername()+",Password:"+user.getPassword());    Profile profile=(Profile)values[1];    System.out.println(profile.getEmail()+profile.getMobile()+profile.getAddress()+profile.getPostcode());   }   session.getTransaction().commit();  }

public void innerJoin(){//内连接查询   /**    * 下面三种hql语句都是可以得到相同的结果    * String hql="select p from Profile as p inner join p.user";    * 在下面的hql语句中加入"fetch"后,此hql语句变为了"迫切HQL"语句,这样的查询效率要比上面的hql语句要高    * String hql="select p from Profile as p inner join fetch p.user";    *    * String hql="select p from Profile p,User u where p.user=u";    * String hql="select p from Profile p,User u where p.user.id=u.id";    *     */     Session session = HibernateSessionFactoryUtil.getSessionFactory()     .openSession();   session.beginTransaction();   String hql="select p from Profile as p inner join fetch p.user";   //String hql="select p from Profile p,User u where p.user=u";   //String hql="select p from Profile p,User u where p.user.id=u.id";   Query query=session.createQuery(hql);   List<Profile> list=query.list();   for(Profile p:list){    System.out.println("ID:"+p.getUser().getId()+"   Username: "+p.getUser().getUsername()+"   Email: "+p.getEmail()+",   Address: "+p.getAddress());   }   session.getTransaction().commit();   }

 public void QBC(){//QBC中实现内连接查询   Session session=HibernateSessionFactoryUtil.getSessionFactory().openSession();   session.beginTransaction();   Criteria criteria=session.createCriteria(Profile.class).createCriteria("user");   List<Profile> list=criteria.list();   for(Profile p:list){    System.out.println("ID:"+p.getUser().getId()+"   Username: "+p.getUser().getUsername()+"   Email: "+p.getEmail()+",   Address: "+p.getAddress());   }   //QBC中实现外连接   System.out.println("##################################################");   criteria=session.createCriteria(Profile.class).setFetchMode("user", FetchMode.JOIN);   List<Profile> listp=criteria.list();   for(Profile p:list){    System.out.println("ID:"+p.getUser().getId()+"   Username: "+p.getUser().getUsername()+"   Email: "+p.getEmail()+",   Address: "+p.getAddress());       }     session.getTransaction().commit();  }

 public void leftOuterJoin(){//左外连接   /**    * String hql="select p from Profile p left outer join p.user order by p.user.id";    * 在下面的hql语句中加入"fetch"后,此hql语句变为了"迫切HQL"语句,这样的查询效率要比上面的hql语句要高    * String hql="select p from Profile p left outer join fetch p.user order by p.user.id";    *    * String hqlu="select u from User u left outer join u.profiles";    *  在下面的hql语句中加入"fetch"后,此hql语句变为了"迫切HQL"语句,这样的查询效率要比上面的hql语句要高    * String hqlu="select u from User u left outer join fetch u.profiles";    */   Session session=HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();   session.beginTransaction();   String hql="select p from Profile p left outer join fetch p.user order by p.user.id";   Query query=session.createQuery(hql);   List<Profile> list=query.list();   for(Profile p:list){    System.out.println("ID:"+p.getUser().getId()+"   Username: "+p.getUser().getUsername()+"   Email: "+p.getEmail()+",   Address: "+p.getAddress());   }   System.out.println("-------------------------------------");   String hqlu="select u from User u left outer join fetch u.profiles";   query=session.createQuery(hqlu);   List<User> listu=query.list();   for(User u:listu){    System.out.println(u.getId()+u.getUsername()+u.getProfiles());   }   session.getTransaction().commit();  }  public void rightOuterJoin(){//右外连接   Session session=HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();   session.beginTransaction();   String hql="select u from User u right outer join u.profiles order by u.id";   Query query=session.createQuery(hql);   List<User> listu=query.list();   for(User user:listu){    System.out.println(user.getId()+user.getUsername()+user.getProfiles());   }   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 not between 200 and 2000 1hongten 2hanyuan 3hongwei 4mingliu 5shouzhang 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 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 4mingliu 5shouzhang 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 3hongwei5645645 4mingliu5645645 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 2hanyuan 1hongten 3hongwei 4mingliu 5shouzhang Hibernate:     select         user0_.username as col_0_0_     from         users.user user0_     where         user0_.username=? hongten Hibernate:     select         user0_.username as col_0_0_     from         users.user user0_ hongten hanyuan hongwei mingliu shouzhang ------------------------------------------- Hibernate:     select         lower(user0_.username) as col_0_0_     from         users.user user0_ hongten hanyuan hongwei mingliu shouzhang Hibernate:     update         users.user     set         username='Hongwei1231'     where         id=? 1 Hibernate:     select         user0_.id as id0_0_,         profile1_.id as id1_1_,         user0_.username as username0_0_,         user0_.password as password0_0_,         profile1_.user_id as user2_1_1_,         profile1_.email as email1_1_,         profile1_.phone as phone1_1_,         profile1_.mobile as mobile1_1_,         profile1_.address as address1_1_,         profile1_.postcode as postcode1_1_     from         users.user user0_,         users.profile profile1_ ID :1,UserName:hongten,Password:123hongtenzone@foxmail.com45464Guangzhoushi65465 ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :1,UserName:hongten,Password:123hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :2,UserName:hanyuan,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465 ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :2,UserName:hanyuan,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :3,UserName:Hongwei1231,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465 ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :3,UserName:Hongwei1231,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :4,UserName:mingliu,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465 ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :4,UserName:mingliu,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :5,UserName:shouzhang,Password:5645645hongtenzone@foxmail.com45464Guangzhoushi65465 ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 ID :5,UserName:shouzhang,Password:5645645hanyuan@foxmail.com45648255GuangzhoushiDianbian65465 Hibernate:     select         profile0_.id as id1_0_,         user1_.id as id0_1_,         profile0_.user_id as user2_1_0_,         profile0_.email as email1_0_,         profile0_.phone as phone1_0_,         profile0_.mobile as mobile1_0_,         profile0_.address as address1_0_,         profile0_.postcode as postcode1_0_,         user1_.username as username0_1_,         user1_.password as password0_1_     from         users.profile profile0_     inner join         users.user user1_             on profile0_.user_id=user1_.id ID:1   Username: hongten   Email: hongtenzone@foxmail.com,   Address: Guangzhoushi ID:2   Username: hanyuan   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ID:3   Username:Hongwei1231   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian Hibernate:     select         this_.id as id1_1_,         this_.user_id as user2_1_1_,         this_.email as email1_1_,         this_.phone as phone1_1_,         this_.mobile as mobile1_1_,         this_.address as address1_1_,         this_.postcode as postcode1_1_,         user1_.id as id0_0_,         user1_.username as username0_0_,         user1_.password as password0_0_     from         users.profile this_     inner join         users.user user1_             on this_.user_id=user1_.id ID:1   Username: hongten   Email: hongtenzone@foxmail.com,   Address: Guangzhoushi ID:2   Username: hanyuan   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ID:3   Username: Hongwei1231   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ################################################## Hibernate:     select         this_.id as id1_1_,         this_.user_id as user2_1_1_,         this_.email as email1_1_,         this_.phone as phone1_1_,         this_.mobile as mobile1_1_,         this_.address as address1_1_,         this_.postcode as postcode1_1_,         user2_.id as id0_0_,         user2_.username as username0_0_,         user2_.password as password0_0_     from         users.profile this_     left outer join         users.user user2_             on this_.user_id=user2_.id ID:1   Username: hongten   Email: hongtenzone@foxmail.com,   Address: Guangzhoushi ID:2   Username: hanyuan   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ID:3   Username: 洪伟1231   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian Hibernate:     select         profile0_.id as id1_0_,         user1_.id as id0_1_,         profile0_.user_id as user2_1_0_,         profile0_.email as email1_0_,         profile0_.phone as phone1_0_,         profile0_.mobile as mobile1_0_,         profile0_.address as address1_0_,         profile0_.postcode as postcode1_0_,         user1_.username as username0_1_,         user1_.password as password0_1_     from         users.profile profile0_     left outer join         users.user user1_             on profile0_.user_id=user1_.id     order by         profile0_.user_id ID:1   Username: hongten   Email: hongtenzone@foxmail.com,   Address: Guangzhoushi ID:2   Username: hanyuan   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ID:3   Username: 洪伟1231   Email: hanyuan@foxmail.com,   Address: GuangzhoushiDianbian ------------------------------------- Hibernate:     select         user0_.id as id0_0_,         profiles1_.id as id1_1_,         user0_.username as username0_0_,         user0_.password as password0_0_,         profiles1_.user_id as user2_1_1_,         profiles1_.email as email1_1_,         profiles1_.phone as phone1_1_,         profiles1_.mobile as mobile1_1_,         profiles1_.address as address1_1_,         profiles1_.postcode as postcode1_1_,         profiles1_.user_id as user2_0__,         profiles1_.id as id0__     from         users.user user0_     left outer join         users.profile profiles1_             on user0_.id=profiles1_.user_id 1hongten[com.b510.example.Profile@14eaec9] 2hanyuan[com.b510.example.Profile@569c60] 3Hongwei1231[com.b510.example.Profile@d67067] 4mingliu[] 5shouzhang[] Hibernate:     select         user0_.id as id0_,         user0_.username as username0_,         user0_.password as password0_     from         users.user user0_     right outer join         users.profile profiles1_             on user0_.id=profiles1_.user_id     order by         user0_.id Hibernate:     select         profiles0_.user_id as user2_1_,         profiles0_.id as id1_,         profiles0_.id as id1_0_,         profiles0_.user_id as user2_1_0_,         profiles0_.email as email1_0_,         profiles0_.phone as phone1_0_,         profiles0_.mobile as mobile1_0_,         profiles0_.address as address1_0_,         profiles0_.postcode as postcode1_0_     from         users.profile profiles0_     where         profiles0_.user_id=? 1hongten[com.b510.example.Profile@10c0f66] Hibernate:     select         profiles0_.user_id as user2_1_,         profiles0_.id as id1_,         profiles0_.id as id1_0_,         profiles0_.user_id as user2_1_0_,         profiles0_.email as email1_0_,         profiles0_.phone as phone1_0_,         profiles0_.mobile as mobile1_0_,         profiles0_.address as address1_0_,         profiles0_.postcode as postcode1_0_     from         users.profile profiles0_     where         profiles0_.user_id=? 2hanyuan[com.b510.example.Profile@e265d0] Hibernate:     select         profiles0_.user_id as user2_1_,         profiles0_.id as id1_,         profiles0_.id as id1_0_,         profiles0_.user_id as user2_1_0_,         profiles0_.email as email1_0_,         profiles0_.phone as phone1_0_,         profiles0_.mobile as mobile1_0_,         profiles0_.address as address1_0_,         profiles0_.postcode as postcode1_0_     from         users.profile profiles0_     where         profiles0_.user_id=? 3Hongwei1231[com.b510.example.Profile@8997d1]

E | hongtenzone@foxmail.com  B | http://www.cnblogs.com/hongten

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏zhisheng

渣渣菜鸡的 ElasticSearch 源码解析 —— 启动流程(上)

上篇文章写了 ElasticSearch 源码解析 —— 环境搭建 ,其中里面说了启动 打开 server 模块下的 Elasticsearch 类:org.e...

14110
来自专栏MasiMaro 的技术博文

Windows内核函数

在驱动中一般使用的是ANSI字符串和宽字节字符串,在驱动中我们仍然可以使用C中提供的字符串操作函数,但是在DDK中不提倡这样做,由于C函数容易导致缓冲区溢出漏洞...

17740
来自专栏zhisheng

看透 Spring MVC 源代码分析与实践 —— 俯视 Spring MVC

Spring MVC 之初体验 环境搭建 在 IDEA 中新建一个 web 项目,用 Maven 管理项目的话,在 pom.xml 中加入 Spring MVC...

42060
来自专栏数据之美

MapReduce 计数器简介

1、计数器简介 在许多情况下,一个用户需要了解待分析的数据,尽管这并非所要执行的分析任务 的核心内容。以统计数据集中无效记录数目的任务为例,如果发现无效...

32190
来自专栏pangguoming

Hibernate详细教程

一、搭建Hibernate环境 1.在src目录下创建hibernate.cfg.xml配置文件 PS:文件的名字不能改! <?xml version="1.0...

44650
来自专栏Java3y

图书管理系统【用户、购买、订单模块、添加权限】

用户模块 要登陆后才能购买,因此我们先写购买模块. 设计实体 private String id; private String usernam...

66680
来自专栏一名合格java开发的自我修养

jdbc操作根据bean类自动组装sql,天啦,我感觉我实现了hibernate

场景:需要将从ODPS数仓中计算得到的大额可疑交易信息导入到业务系统的mysql中供业务系统审核。但是本系统是开放是为了产品化,要保证不同环境的可移植性,同时同...

20320
来自专栏码匠的流水账

spring mvc中的几类拦截器对比

是servlet规范中的Filter,spring中有一个基本的实现叫做org/springframework/web/filter/GenericFilter...

23610
来自专栏爱撒谎的男孩

地址管理之省市区三级联动菜单

1.1K30
来自专栏潇涧技术专栏

Lint Tool Analysis (2)

本系列的几篇源码分析文档意义不大,如果你正好也在研究lint源码,或者你想知道前面自定义lint规则中提出的那几个问题,抑或你只是想大致了解下lint的源码都有...

18810

扫码关注云+社区

领取腾讯云代金券