Hibernate查询方式

1) Hibernate查询方式简介

新建项目Hibernate10

1.1) 导航对象图查询方式

1.2) OID查询方式

1.3) 本地SQL查询方式

1.4) HQL查询方式

1.5) QBC查询方式(Query By Criteria)

2) 本地SQL查询方式

hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!--数据库连接设置 -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/hibernate4</property>
        <property name="connection.username">root</property>
        <property name="connection.password">root</property>
        <!-- 方言 -->
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <!-- 控制台显示SQL -->
        <property name="show_sql">true</property>
        <!-- 自动更新表结构 -->
        <property name="hbm2ddl.auto">update</property>
        <!-- Mapper -->
        <mapping resource="com/andrew/model/Student.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

HibernateUtil.java

package com.andrew.util;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();
    private static SessionFactory buildSessionFactory() {
        // 1. 实例化配置文件
        Configuration configuration = new Configuration().configure();
        // 2. 实例化服务登记
        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties()).build();
        // 3. 获取Session工厂
        return configuration.buildSessionFactory(serviceRegistry);
    }
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}

Student.java

package com.andrew.model;
public class Student {
    private int id;
    private String name;
    private int age;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
}

Student.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.andrew.model">
    <class name="Student" table="t_student">
        <id name="id" column="stuId">
            <generator class="native"></generator>
        </id>
        <property name="name" column="stuName"></property>
        <property name="age" column="stuAge"></property>
    </class>
</hibernate-mapping>

insert into t_student values(1, '张三', 10);
insert into t_student values(2, '李四', 15);
insert into t_student values(3, '王五', 13);

StudentTest.java

package com.andrew.service;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.andrew.model.Student;
import com.andrew.util.HibernateUtil;
public class StudentTest {
    private SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
    private Session session;
    @Before
    public void setUp() throws Exception {
        session = sessionFactory.openSession(); // 1. 生成一个session
        session.beginTransaction(); // 2. 开启事务
    }
    @After
    public void tearDown() throws Exception {
        session.getTransaction().commit(); // 3. 提交事务
        session.close(); // 4. 关闭session
    }
    @Test
    public void testSQLQuery() {
        String sql = "select * from t_student";
        Query query = session.createSQLQuery(sql).addEntity(Student.class);
        List studentList = query.list();
        Iterator it = studentList.iterator();
        while (it.hasNext()) {
            Student s = (Student)it.next();
            System.out.println(s);
        }        
    }
    @Test
    public void testSQLQuery2() {
        String sql="select * from t_student where stuName like :stuName and stuAge=:stuAge";
        Query query = session.createSQLQuery(sql).addEntity(Student.class);
        query.setString("stuName", "张%");
        query.setInteger("stuAge", 10);
        List studentList = query.list();
        Iterator it=studentList.iterator();
        while (it.hasNext()) {
            Student s = (Student)it.next();
            System.out.println(s);
        }    
    }
}

运行结果：
Hibernate: select * from t_student
Student [id=1, name=张三, age=10]
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]
运行结果：
Hibernate: select * from t_student where stuName like ? and stuAge=?
Student [id=1, name=张三, age=10]

3) HQL查询方式

HQL(Hibernate Query Language)是面向对象的查询语言；是使用最广的一种查询方式；

3.1) 普通查询

@Test
public void testHQLQuery() {
    String hql = "from Student";
    Query query = session.createQuery(hql);
    List<Student> studentList = (List<Student>)query.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }        
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_
Student [id=1, name=张三, age=10]
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]

3.2) 带条件查询

@Test
public void testHQLQuery2() {
    String hql = "from Student where name like :stuName and age=:stuAge";
    Query query = session.createQuery(hql);
    query.setString("stuName", "张%");
    query.setInteger("stuAge", 10);
    List<Student> studentList = (List<Student>)query.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }        
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ where (student0_.stuName like ?) and student0_.stuAge=?
Student [id=1, name=张三, age=10]

3.3) 使用别名

@Test
public void testHQLQuery3() {
    String hql = "from Student as s where s.name like :stuName and s.age=:stuAge";
    Query query = session.createQuery(hql);
    query.setString("stuName", "张%");
    query.setInteger("stuAge", 10);
    List<Student> studentList = (List<Student>)query.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ where (student0_.stuName like ?) and student0_.stuAge=?
Student [id=1, name=张三, age=10]

3.4) 对结果排序

@Test
public void testHQLQuery4() {
    String hql = "from Student order by age desc";
    Query query = session.createQuery(hql);
    List<Student> studentList = (List<Student>)query.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }        
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ order by student0_.stuAge desc
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]
Student [id=1, name=张三, age=10]

3.5) 分页查询

@Test
public void testHQLQuery5() {
    String hql = "from Student";
    Query query = session.createQuery(hql);
    query.setFirstResult(1);
    query.setMaxResults(2);
    List<Student> studentList = (List<Student>)query.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }        
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ limit ?, ?
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]

3.6) 查询单个对象

@Test
public void testHQLQuery6() {
    String hql = "from Student";
    Query query = session.createQuery(hql);
    query.setFirstResult(1);
    query.setMaxResults(1);
    Student student = (Student)query.uniqueResult();
    System.out.println(student);    
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ limit ?, ?
Student [id=2, name=李四, age=15]

3.7) 链式写法

@Test
public void testHQLQuery7() {
    String hql = "from Student as s where s.name like :stuName and s.age=:stuAge";
    Query query = session.createQuery(hql);
    List<Student> studentList = (List<Student>)query
            .setString("stuName", "张%")
            .setInteger("stuAge", 10)
            .list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }        
}

Hibernate: select student0_.stuId as stuId1_0_, student0_.stuName as stuName2_0_, student0_.stuAge as stuAge3_0_ from t_student student0_ where (student0_.stuName like ?) and student0_.stuAge=?
Student [id=1, name=张三, age=10]

4) QBC 查询方式

QBC查询方式(Query By Criteria)是用一套接口来实现的查询方式；

4.1) 普通查询

@Test
public void testQBCQuery1() {
    Criteria criteria = session.createCriteria(Student.class);
    List<Student> studentList = criteria.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }    
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_
Student [id=1, name=张三, age=10]
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]

4.2) 带条件查询

@Test
public void testQBCQuery2(){
    Criteria criteria = session.createCriteria(Student.class);
    Criterion c1 = Restrictions.like("name", "张%");
    Criterion c2 = Restrictions.eq("age", 10);
    criteria.add(c1);
    criteria.add(c2);
    List<Student> studentList = criteria.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }    
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_ where this_.stuName like ? and this_.stuAge=?
Student [id=1, name=张三, age=10]

4.3) 对结果排序

@Test
public void testQBCQuery3() {
    Criteria criteria = session.createCriteria(Student.class);
    criteria.addOrder(Order.desc("age"));
    List<Student> studentList = criteria.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }    
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_ order by this_.stuAge desc
Student [id=2, name=李四, age=15]
Student [id=3, name=王五, age=13]
Student [id=1, name=张三, age=10]

4.4) 分页查询

@Test
public void testQBCQuery4() {
    Criteria criteria = session.createCriteria(Student.class);
    criteria.setFirstResult(2);
    criteria.setMaxResults(2);
    List<Student> studentList = criteria.list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }    
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_ limit ?, ?
Student [id=3, name=王五, age=13]

4.5) 查询单个对象

@Test
public void testQBCQuery5() {
    Criteria criteria = session.createCriteria(Student.class);
    criteria.setFirstResult(2);
    criteria.setMaxResults(1);
    Student student = (Student)criteria.uniqueResult();
    System.out.println(student);
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_ limit ?, ?
Student [id=3, name=王五, age=13]

4.6) 链式写法

@Test
public void testQBCQuery6() {
    Criteria criteria = session.createCriteria(Student.class);
    List<Student> studentList = criteria
            .setFirstResult(0)
            .setMaxResults(2)
            .list();
    Iterator it = studentList.iterator();
    while (it.hasNext()) {
        Student s = (Student)it.next();
        System.out.println(s);
    }    
}

Hibernate: select this_.stuId as stuId1_0_0_, this_.stuName as stuName2_0_0_, this_.stuAge as stuAge3_0_0_ from t_student this_ limit ?
Student [id=1, name=张三, age=10]
Student [id=2, name=李四, age=15]