Hibernate HQL查询（1）

1. 查询整个映射对象所有字段

//直接from查询出来的是一个映射对象，即：查询整个映射对象所有字段

String hql ="from Users";

Query query = session.createQuery(hql);

List<Users> users = query.list();

for(Users user : users){

System.out.println(user.getName() +" : "+ user.getPasswd() +

"

 : "

+ user.getId());

}

输出结果为：

name1 : password1 :1

name2 : password2 :2

name3 : password3 :3

2.查询字段　　

//查询其中几个字段

String hql =" select name,passwd from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组

List<Object[]> list = query.list();

for(Object[] object : list){

String name = (String)object[0];

String passwd = (String)object[1];

System.out.println(name +" : "+ passwd);

}

输出结果为：

name1 : password1

name2 : password2

name3 : password3

3.修改默认查询结果(query.list())不以Object[]数组形式返回，以List形式返回

//查询其中几个字段,添加new list(),注意list里的l是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是List集合了

String hql =" select new list(name,passwd) from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是List集合了

List<List> list = query.list();

for(List user : list){

String name = (String)user.get(0);

String passwd = (String)user.get(1);

System.out.println(name +" : "+ passwd);

}

/**

输出结果为：

name1 : password1

name2 : password2

name3 : password3

*/

4.修改默认查询结果(query.list())不以Object[]数组形式返回，以Map形式返回

//查询其中几个字段,添加new map(),注意map里的m是小写的。也不需要导入包，这样通过query.list()出来的list里存放的不再是默认的Object数组了，而是map集合了

String hql =" select new map(name,passwd) from Users";

Query query = session.createQuery(hql);

//默认查询出来的list里存放的是一个Object数组，但是在这里list里存放的不再是默认的Object数组了，而是Map集合了

List<Map> list = query.list();

for(Map user : list){

//一条记录里所有的字段值都是map里的一个元素,key是字符串0,1,2,3....，value是字段值

//如果将hql改为：String hql = " select new map(name as username,passwd as password) from Users";,那么key将不是字符串0,1,2...了，而是"username","password"了

String name = (String)user.get("0");//get("0");是get(key),注意:0,1,2...是字符串，而不是整形

String passwd = (String)user.get("1");

System.out.println(name +" : "+ passwd);

}

/**

输出结果为：

name1 : password1

name2 : password2

name3 : password3

*/

5.修改默认查询结果(query.list())不以Object[]数组形式返回，以自定义类型返回

6.条件查询

//条件查询，参数索引值从0开始，索引位置。通过setString,setParameter设置参数

String hql ="from Users where name=? and passwd=?";

Query query = session.createQuery(hql);

//第1种方式

// query.setString(0, "name1");

// query.setString(1, "password1");

//第2种方式

query.setParameter(0,"name1",Hibernate.STRING);

query.setParameter(1,"password1",Hibernate.STRING);

List<Users> list = query.list();

for(Users users : list){

System.out.println(users.getId());

}

//条件查询，自定义索引名(参数名):username,:password.通过setString,setParameter设置参数

String hql ="from Users where name=:username and passwd=:password";

Query query = session.createQuery(hql);

//第1种方式

// query.setString("username", "name1");

// query.setString("password", "password1");

//第2种方式,第3个参数确定类型

query.setParameter("username","name1",Hibernate.STRING);

query.setParameter("password","password1",Hibernate.STRING);

List<Users> list = query.list();

for(Users users : list){

System.out.println(users.getId());

}

//条件查询,通过setProperties设置参数

String hql ="from Users where name=:username and passwd=:password";

Query query = session.createQuery(hql);

//MyUser类的2个属性必须和:username和:password对应

MyUser myUser =newMyUser("name1","password1");

query.setProperties(myUser);

List<Users> list = query.list();

for(Users users : list){

System.out.println(users.getId());

}

7.update 数据

执行SQL语句（为什么要用SQL语句，我想是为了执行某些复杂的SQL语句吧）　

String sql="update Table set field = 'test'"

Session session = HibernateSessionFactory.getSession();

session.createSQLQuery(sql).executeUpdate();

ts.commit();

执行HQL语句　　　

String hql="update Table set field = 'test'"

Session session = HiberanteSessionFactory.getSession();

Transaction ts = session.beginTransaction();

Query query = session.createQuery(hql);

query.executeUpdate();

ts.commit();

*************************************************************************************

/**

*

*/

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]

巴特西

Hibernate HQL查询（1）

最新文章

热门文章