QBC (Query by Criteria)由hibernate提供的更具面向对象的查询方式,
能够支持运行时动态查询语句
在Hibernate使用QBC的步骤
1.通过session.createCriteria()获得Criteria实例
2.使用工具类Restrictions(Expression早起版本)追加查询条件
3.调用uniqueResult()或者list()获得结果
排序使用Order类
Projection 提供聚合函数的方法
1.提供实体类Dept.java和Employee.java
package com.jsu.hb.pojo;
import java.util.HashSet;
import java.util.Set;
public class Dept {
private Integer id;
private String dname;
private String dno;
private Set<Employee> emps = new HashSet<Employee>();
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getDno() {
return dno;
}
public void setDno(String dno) {
this.dno = dno;
}
public Set<Employee> getEmps() {
return emps;
}
public void setEmps(Set<Employee> emps) {
this.emps = emps;
}
}
Employee.java
package com.jsu.hb.pojo;
import java.util.Date;
public class Employee {
private Integer id;
private String name;
private Date birthday;
private String email;
private double salary;
private Dept dept;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
}
2.建表
create table g_dept(
t_id integer primary key,
t_dname varchar2(20),
t_dno varchar2(20)
)
create table g_employee(
t_id integer primary key,
t_name varchar2(25),
t_birthday date,
t_salary number(12,2),
t_email varchar2(50),
d_id integer references g_dept(t_id)
)
insert into g_dept(t_id,t_dname,t_dno) values (1,'dev','001');
insert into g_dept(t_id,t_dname,t_dno) values (2,'train','002');
insert into g_dept(t_id,t_dname,t_dno) values (3,'market','003');
insert into g_dept(t_id,t_dname,t_dno) values (4,'game','004');
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (1,'bobs',to_date('2012-1-11','yyyy-mm-dd'),'bobs@b',100.0,1);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (2,'scott',to_date('2010-10-11','yyyy-mm-dd'),'scott@b',300.0,1);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (3,'tiger',to_date('2008-2-11','yyyy-mm-dd'),'tiger@b',400.0,2);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (4,'lucy',to_date('2009-3-11','yyyy-mm-dd'),'lucy@b',600.0,2);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (5,'lily',to_date('2012-4-11','yyyy-mm-dd'),'lily@b',1000.0,3);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (6,'tom',to_date('2001-10-11','yyyy-mm-dd'),'tom@b',700.0,3);
insert into g_employee(t_id,t_name,t_birthday,t_email,t_salary,d_id)
values (7,'black',to_date('2001-10-11','yyyy-mm-dd'),'black@b',200.0,null);
commit
3.提供映射文件query.hbm.xml
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping package="com.jsu.hb.pojo"> <class name="Dept" table="g_dept"> <id name="id" column="t_id"> <generator class="increment"></generator> </id> <property name="dname" column="t_dname"></property> <property name="dno" column="t_dno"></property> <!-- 关系属性 --> <set name="emps" cascade="all" inverse="true"> <key column="d_id"></key> <one-to-many class="Employee"></one-to-many> </set> </class> <class name="Employee" table="g_employee"> <id name="id" column="t_id"> <generator class="increment"></generator> </id> <property name="name" column="t_name"></property> <property name="birthday" column="t_birthday" type="java.util.Date"></property> <property name="email" column="t_email"></property> <property name="salary" column="t_salary"></property> <!-- 双向关系是添加关系属性 --> <many-to-one name="dept" class="Dept" cascade="save-update" column="d_id"></many-to-one> </class> </hibernate-mapping>
4.在hibernate.cfg.xml中对映射文件进行注册
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- show_sql:是否显示hibernate执行的SQL语句,默认为false --> <property name="show_sql">true</property> <!-- show_sql:是否显示hibernate执行格式化输出的SQL语句,默认为false --> <property name="format_sql">true</property> <!-- 配置与数据库连接的参数 --> <property name="connection.driver_class">oracle.jdbc.OracleDriver</property> <property name="connection.url">jdbc:oracle:thin:@127.0.0.1:1521:oracle</property> <property name="connection.username">scott</property> <property name="connection.password">tiger</property> <!-- 2.自身属性相关的配置 dialect:方言 hibernate根据dialect的配置进行特定数据性能方面的调优 --> <property name="dialect">org.hibernate.dialect.Oracle9iDialect</property> <mapping resource="com/jsu/hb/pojo/query.hbm.xml"></mapping> </session-factory> </hibernate-configuration>
5.提供工具类HibernateUtil.java
package com.jsu.hb.util;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class HibernateUtil {
private static SessionFactory sf;
private static ThreadLocal<Session> tl= new ThreadLocal<Session>();
static{
try{
Configuration cfg = new Configuration();
cfg.configure();
sf=cfg.buildSessionFactory();
}catch(Exception e){
e.printStackTrace();
}
}
public static Session openSession(){
return sf.openSession();
}
public static Session getCurrentSession(){
Session session = tl.get();//先从储存的线程中查找
if(session==null){
session=openSession();
tl.set(session);
return session;
}
return session;
}
}
6.提供测试类TestQBC.java
public class TestQBC {
public void testQbc() {
Session session = HibernateUtil.getCurrentSession();
Transaction tx = session.getTransaction();
tx.begin();
// hql:from Employee
Criteria cir = session.createCriteria(Employee.class);
cir.add(Restrictions.eq("name", "haoren"));// 加入 name='haoren ' 的条件
Employee e = (Employee) cir.uniqueResult();// 获得单个结果
System.out.println(e.getName() + " " + e.getSalary());
tx.commit();
}
// 为员工的工资排序
public void testOrder() {
Session session = HibernateUtil.getCurrentSession();
Transaction tx = session.getTransaction();
tx.begin();
// hql:from Employee
Criteria cir = session.createCriteria(Employee.class);
cir.add(Expression.between("salary", 100.0, 400.0));
cir.addOrder(Order.asc("salary"));
List<Employee> emps = cir.list();
for (Employee e : emps) {
System.out.println(e.getName() + " : " + e.getSalary());
}
tx.commit();
}
// 员工表的平均工资
public void testAvg() {
Session session = HibernateUtil.getCurrentSession();
Transaction tx = session.getTransaction();
tx.begin();
// hql:from Employee
Criteria cir = session.createCriteria(Employee.class);
cir.setProjection(Projections.avg("salary"));
Double d = (Double) cir.uniqueResult();
System.out.println(d);
tx.commit();
}
// 部门号为1的所有员工
@Test
public void testDept() {
Session session = HibernateUtil.getCurrentSession();
Transaction tx = session.getTransaction();
tx.begin();
// hql:from Employee
Criteria cir = session.createCriteria(Employee.class);
Criteria ciradd = cir.createCriteria("dept");// employee对象的关系属性的名称
ciradd.add(Expression.eq("id", 1));
List<Employee> emps = cir.list();
for (Employee e : emps) {
System.out.println(e.getName() + " " + e.getDept().getId());
}
tx.commit();
}
}