条件查询&SQL查询

条件查询是更具面向对象特色的数据查询方式，通过如下3个类完成：
Criteria：代表一次查询
Criterion：代表一个查询条件
Restrictions：产生查询条件的工具类
执行条件查询的步骤如下：
( 1 )获得Hibernate的Session对象
( 2 )以Session对象创建Criteria对象
( 3 )增加Criterion查询条件
( 4 )执行Criteria的list等方法返回结果集
请看示例：
private   void  test()
... {
    Session session = HibernateUtil.currentSession();
    Transaction tx = session.beginTransaction();
    //创建Criteria和添加查询条件同步完成
    //最后调用list方法，返回查询到的结果集
    List l = session.createCriteria(Student.class)
        //此处增加到限制条件必须是Student已经存在的属性
        .add(Restritions.gt("studentNumber", new Long(20071127)))
        //如果要增加对Student关联类的属性限制，则必须重新创建
        //如果此关联属性是集合，则只要集合里任意一个对象的属性满足下面条件即可
        .createCriteria("enrolments")
        .add(Restritions.gt("semester", new Short("2")))
        .list();
        Iterator it = l.iterator();
    //遍历查询到的记录
    while (it.hasNext())
    ...{
        Student s = (Student)it.next();
        System.out.println(s.getName());
        Set enrolments = s.getEnrolments();
        Iterator iter = enrolments.iterator();
        while (iter.hasNext())
        ...{
            Enrolment e = (Enrolment)iter.next();
            System.out.println(e.getCourse().getName());
        }
    }
    tx.commit();
    HibernateUtil.closeSession();
}

session.createCriteria(Person. class )
    .add(Restrictions.like( " name " ,  " dd% " ))
    .createCriteria( " address " )
    .add(Restrictions.like( " addressdetail " ,  " 福州% " ))
    .list();

 

SQL查询
比Query多了两个重载的构造方法
addEntity:将查询到的记录与特定的实体关联
addScalar:将查询到的记录关联成标量值
执行SQL查询的步骤如下：
( 1 )获取Hibernate Session对象
( 2 )编写SQL语句
( 3 )以SQL语句作为参数，调用Session的createSQLQuery方法创建查询对象
( 4 )如果SQL语句包含参数，则调用Query的setXxx方法为参数赋值
( 5 )调用SQLQuery对象的addEntiy或addScalar方法，将选出的结果与实体或标量值关联
( 6 )调用Query的list方法返回查询的结果集
请看SQL的查询示例：
private   void  test()  ... {
    Session session = HibernateUtil.currentSession();
    Transaction tx = session.beginTransaction();
    //编写SQL语句
    String sqlString = "select {s.*} from student s where s.name like '李四'";
    //以SQL语句创建SQLQuery对象
    List l = session.createSQLQuery(sqlString)
                    //将查询到的记录与特定实体关联起来
                    .addEntity("s", Student.class)
                    //返回全部的记录集
                    .list();
    Iterator it = l.iterator();
    while (it.hasNext())
    ...{
        //因为将查询的结果与Student类关联，因此返回时Student的集合
        Student s = (Student)it.next();
        Set enrolments = s.getEnrolmens();
        Iterator iter = enrolments.iterator();
        while (iter.hasNext())
        ...{
            Enrolment e = (Enrolment)iter.next();
            System.out.println("=====================================");
            System.out.println(e.getCourse().getName());
            System.out.println("=====================================");
        }
    }
    tx.commit();
    HibernateUtil.closeSession();
}

Double max  =  (Double)session.createSQLQuery( " select max(cat.weight) as
maxWeight from cats cat " )
        .addScalar( " maxWeight " , Hibernate.DOUBLE);
        .uniqueResult();
实例名.属性名
// 依次将多个选出的字段命名别名，命名别名时都以ss作为前缀，ss是关联实体的别名
String sqlStr  =   " select stu.studentId as {ss.studentNumber},  "
         +   " stu.name as {ss.name} from "  
         +   " student as stu where stu.name like '张大勇' " ;
List l  =  session.createSQLQuery(sqlStr)
                 // 将查询出的ss实例，关联到Student类
                .addEntity( " ss " , Student. class )
                .list();

1 .命名SQL查询
配置片段：
<!-- 每个sql - query元素定义一个命名SQL查询 -->
< sql - query name = " mySQLQuery " >
     <!-- 关联返回的结果与实体类 -->
     < return  alias = " s "   class = " Student " />
         <!-- 定义命名SQL查询的SQL语句 -->
        SELECT  ... {s.*}
        FROM student s WHERE s.name like  ' 张大勇 '
</ sql - query >
使用该命名SQL查询的示例代码：
private   void  testNamedSQL()  ... {
    Session session = HibernateUtil.currentSession();
    Transaction tx = session.beginTransaction();
    //调用命名查询，直接返回结果
    List l = session.getNamedQuery("mySQLQuery").list();
    while (it.hasNext())
    ...{
        //在定义SQL查询时，已经将结果集与Student类关联起来
        //因此，集合里的每个元素都是Student实例
        Student s = (Student)it.next();
        Set enrolments = s.getEnrolmens();
        Iterator iter = enrolments.iterator();
        while (iter.hasNext())
        ...{
            Enrolment e = (Enrolment)iter.next();
            System.out.println("=====================================");
            System.out.println(e.getCourse().getName());
            System.out.println("=====================================");
        }
    }
    tx.commit();
    HibernateUtil.closeSession();
}

2 .调用存储过程
Oracle9i存储过程示例：
CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
SELECT EMPLOYEE, EMPLOYER,
STARTDATE, ENDDATE,
REGIONCODE, EID, VALUE, CURRENCY
FROM EMPLOYMENT;
    RETURN st_cursor;
END;
如果需要调用该存储过程，可以先将其定义成命名SQL查询，例如：
<!-- 定义命名SQL查询，name属性指定命名SQL查询名 -->
< sql - query name = " selectAllEmployees_SP "  callabe = " true " >
     <!-- 定义返回列与关联实体类属性之间的映射 -->
     < return  alias = " emp "   class = " Employment " >
         <!-- 依次定义每列与实体类属性的对应 -->
         < return - property name = " employee "  column = " EMPLOYEE " />
         < return - property name = " employer "  column = " EMPLOYER " />
         < return - property name = " startDate "  column = " STARTDATE " />
         < return - property name = " endDate "  column = " ENDDATE " />
         < return - property name = " regionCode "  column = " REGIONCODE " />
         < return - property name = " id "  column = " EID " />
         <!-- 将两列值映射到一个关联类的引用属性 -->
         < return - property name = " salary " >
             <!-- 映射列与引用属性之间的关联 -->
             < return - column name = " VALUE " />
             < return - column name = " CURRENCY " />
         </ retrun - property >
     </ return >
</ sql - query >

$数据过滤
过滤器的使用分三步：
( 1 )定义过滤器，使用filter - def元素定义过滤器
( 2 )使用过滤器，使用filter元素使用过滤器
( 3 )在代码中启用过滤器
请看下面的映射文件示例：
<? 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 >
     < class  name = " Category "  table = " category " >
         < id name = " id "  column = " category_id " >
             < generator  class = " native " />
         </ id >
         < property name = " name "  type = " string " />
         < property name = " effectiveStartDate "  column = " eff_start_date "
            type = " java.util.Date " />
         < property name = " effectiveEndDate "  column = " eff_end_date "
            type = " java.util.Date " />
         <!-- 映射N - N关联属性 -->
         < set cascade = " none "  inverse = " true "  name = " products "  table = "
            product_category >
             <!-- 定义关联属性的key，对应连接表中的外键列 -->
             < key column = " category_id " />
             <!-- 定义关联属性 -->
             < many - to - many column = " product_id "   class = " Product " />
         </ set >
         <!-- 使用过滤器，并设置过滤条件 -->
         < filter name = " effectiveDate "  condition = " :asOfDate BETWEEN eff_start
        _date and eff_end_date " />
     </ class >
     < class  name = " Product "  table = " product " >
         < id name = " id "  column = " product_id " >
             < generator  class = " native " />
         </ id >
         < property name = " name "  type = " string " />
         < property name = " stockNumber "  column = " stock_number "
            type = " int " />
         < property name = " effectiveStartDate "  column = " eff_start_date "
            type = " java.util.Date " />
         < property name = " effectiveEndDate "  column = " eff_end_date "
            type = " java.util.Date " />
         <!-- 映射N - N关联属性 -->
         < set cascade = " all "  name = " catagories "  fetch = " join "  table = "
            product_category >
             <!-- 定义关联属性的key，对应连接表中的外键列 -->
             < key column = " product_id " />
             <!-- 定义关联属性 -->
             < many - to - many column = " category_id "   class = " Category "
                fetch = " join " >
                 <!-- 对关联属性使用第一个过滤器 -->
                 < filter name = " effectiveDate "
                    condition = " :asOfDate BETWEEN eff_start_date
                    and eff_end_date " />
                 <!-- 对关联属性使用第二个过滤器 -->
                 < filter name = " category "  condition = " category_id = : catId " />
             </ many - to - many >
         </ set >
         < filter name = " effectiveDate "
            condition = " :asOfDate BETWEEN eff_start_date
        and eff_end_date " />
     </ class >
     <!-- 定义第一个过滤器，该过滤器包含一个date类型的参数 -->
     < filter - def name = " effectiveDate " >
         < filter - param name = " asOfDate "  type = " date " />
     </ flter - def >
     <!-- 定义第二个过滤器，该过滤器包含一个long类型的参数 -->
     < filter - def name = " category " >
         < filter - param name = " catId "  type = " long " />
     </ flter - def >
</ hibernate - mapping >
下面是使用过滤器的示例代码：
private   void  test()  ... {
    Session session = HibernateUtil.currentSession();
    Transaction tx = session.beginTransaction();
    //启用第一个过滤器
    session.enableFilter("effectiveDate")
            //为过滤器设置参数
            .setParameter("asOfDate", new Date());
    //启用第二个过滤器
    session.enableFilter("categor")
            //为过滤器设置参数
            .setParameter("catId", new Long(2));
    //执行查询，该查询没有任何的查询条件
    Iterator results = session.createQuery("form Product as p").iterate();
    while (results.hasNext())
    ...{
        Product p = (Product)results.next();
        System.out.println(p.getName());
        //此处获取Product关联的种类，过滤器也将自动应用过滤
        Iterator it = p.getCategories().iterator();
        System.out.println(p.getCategories().size());
        while (it.hasNext())
        ...{
            Category c = (Category)it.next();
            System.out.println(c.getName());
        }
    }
    tx.commit();
    HibernateUtil.closeSession();
}