Criteria查询及命名查询

实例House代码

package cn.bdqn.entity;

public class House {
	private int hid;       //旅店编号
	private String title;  //标题
	private Double price;  //单价
	private Double floorage;//房屋面积
	public int getHid() {
		return hid;
	}
	public void setHid(int hid) {
		this.hid = hid;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public Double getPrice() {
		return price;
	}
	public void setPrice(Double price) {
		this.price = price;
	}
	public Double getFloorage() {
		return floorage;
	}
	public void setFloorage(Double floorage) {
		this.floorage = floorage;
	}


}

House.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="cn.bdqn.entity">

	<class name="House" table="house">
		<id name="hid" column="hid" type="java.lang.Integer">
			<generator class="assigned" />
		</id>
		<property name="title" type="java.lang.String" column="title" />
		<property name="price" type="java.lang.Double" column="price" />
		<property name="floorage" type="java.lang.Double" column="floorage" />
	</class>
</hibernate-mapping>

1.Restrictions限制结果集内容、结果集排序

关键代码如下

Criteria criteria = session.createCriteria(House.class);
		<span style="color:#ff0000;">Criterion c1 = Restrictions.eq("title", "单人间");//对应SQL的等于
		Criterion c2 = Restrictions.between("price", 30.00, 90.00);<span style="font-family: Arial, Helvetica, sans-serif;">//对应SQL的between子句</span>
		Criterion c3 = Restrictions.between("floorage", 35.00, 60.00);
		criteria.add(Restrictions.and(c1, Restrictions.and(c2, c3)));<span style="font-family: Arial, Helvetica, sans-serif;">//对应SQL的and</span>
		criteria.addOrder(Order.desc("price"));//结果集排序</span>
		result = criteria.list();

2.Example示例查询

关键代码如下

Criteria criteria = session.createCriteria(House.class);
		House h1 = new House();
		h1.setTitle("单人间1");
		criteria.add(Example.create(h1));
		result = criteria.list();

其中实例对象的属性最好用包装类，这样默认值为null

3.统计、分组、分页

关键代码如下

Criteria criteria = session.createCriteria(House.class);
		<span style="color:#ff0000;">criteria.setProjection(Projections.projectionList()
				.add(Projections.groupProperty("title"))
				.add(Projections.rowCount()).add(Projections.avg("price"))
				.add(Projections.max("floorage")));</span>
		result = criteria.list();
		Iterator it = result.iterator();
		while (it.hasNext()) {
			Object[] obj = (Object[]) it.next();
			for (Object o : obj) {
				System.out.println(o);
			}
		}

取值的时候是object数组，分页使用

criteria.setFirstResult(0);//设置第一条记录的位置
criteria.setMaxResults(3);//设置最大返回的记录数量

4.命名Hql查询

修改映射文件，和class标签同一级

<query name="selectfloorage">
    <![CDATA[
       from House where floorage>=:floorage
    ]]>
	</query>

测试代码

Query query=session.getNamedQuery("selectfloorage");
query.setDouble("floorage", 60);

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

5.命名SQL查询

修改映射文件，和class标签同一级

<sql-query name="selectfloorage1">
    <![CDATA[
       select * from house l where l.floorage>=:floorage
    ]]>
    <return alias="l" class="cn.bdqn.entity.House" />
	</sql-query>

测试代码

String sql = "select * from house l where l.floorage>=:floorage";
			SQLQuery query = session.createSQLQuery(sql).addEntity("l",House.class);
			query.setDouble("floorage", 60);
			List<House> list = query.list();

6.定制SQL

修改映射文件，在class标签

<sql-insert>
	insert into house (title,price,floorage,hid) values(?,?,?,?)
</sql-insert>

测试代码

tran = session.beginTransaction();
			House h=new House();
			h.setHid(8);
			h.setTitle("豪华单人间");
			h.setPrice(188.00);
			h.setFloorage(150.00);
			session.save(h);
			tran.commit();

7.hibernate调用存储过程

存储过程

create or replace procedure houseselect(houseref out sys_refcursor,
                                        intitle  in varchar2) is
begin
  open houseref for
    select * from house where title = intitle;
end houseselect;

存储过程out游标要放在第一位，不然会报错

配置文件

<sql-query name="selecthouse" callable="true">
	   {call houseselect(?,?)}
	<return alias="l" class="cn.bdqn.entity.House" />
	</sql-query>

测试文件

Query query=session.getNamedQuery("selecthouse");
			query.setString(0, "单人间");
			List list = query.list();

8.使用CallableStatement调用存储过程

CallableStatement cst=session.connection().prepareCall("{call houseselect1(?)}");
				 cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
				 cst.execute();
				 ResultSet re=(ResultSet) cst.getObject(1);
				 while (re.next()) {
					System.out.print(re.getString(1)+",");
					System.out.print(re.getString(2)+",");			
					System.out.print(re.getString(3)+",");			
					System.out.println(re.getString(4));			

				 }