Hibernate提供以下几种检索对象的方式。
运算类型
|
HQL运算符
|
QBC运算符
|
含义
|
比较运算
|
=
|
Expression.eq()
|
等于
|
<> |
Expression.not(Expression.eq())
|
不等于
| |
> |
Expression.gt()
|
大于
| |
>=
|
Expression.ge()
|
大于等于
| |
< |
Expression.lt()
|
小于
| |
<=
|
Expression.le()
|
小于等于
| |
is null
|
Expression.isNull()
|
等于空值
| |
is not null
|
Expression.isNotNull()
|
非空值
| |
范围运算
|
in (列表)
|
Expression.in()
|
等于列表中的某一个值
|
not in (列表)
|
Expression.not(Expression.in())
|
不等于列表中的任意一个值
| |
between 值1 and 值2
|
Expression.between()
|
大于等于值1并且小于等于值2
| |
not between 值1 and 值2
|
Expression.not(Expression.between())
|
小于值1或者大于值2
| |
字符串模式匹配
|
like
| Expression.like() |
字符串模式匹配
|
逻辑运算
|
and
| Expression.add()或者Expression.conjunction() |
逻辑与
|
or
| Expression.or()或者Expression.disjunction() |
逻辑或
| |
not
| Expression.not() |
逻辑非
|
通配符名称
|
通配符
|
作用
|
百分号
|
%
|
匹配任意类型且任意长度(长度可以为0)的字符串,如果是中文,需要两个百分号,即“
%%”
|
下划线
|
_
|
匹配单个任意字符,常用来限制字符串表达式的长度
|
匹配模式
|
举例
|
MatchMode.START
|
Expression.like(“name”,”y”, MatchMode.START)
姓名以y开头
|
MatchMode.END
|
Expression.like(“name”,”y”, MatchMode. END)
姓名以y结尾
|
MatchMode.ANYWHERE
|
Expression.like(“name”,”y”, MatchMode. ANYWHERE)
姓名中包含y
|
MatchMode.EXACT
|
Expression.like(“name”,”y”, MatchMode. EXACT)
精确匹配,姓名必须为y
|
在程序中指定的链接查询类型
|
HQL语法
|
QBC语法
|
使用范围
|
内连接
|
inner join 或者 join
|
Criteria.createAlias()
|
适用于有关联的持久化类,并且在映射文件中对这种关联关系作了映射。
|
迫切内连接
|
inner join fetch 或者 join fetch
|
不支持
| |
隐式内连接
|
不支持
| ||
左外连接
|
left outer join 或者 left join
|
不支持
| |
迫切左外连接
|
left outer join fetch 或者 left join fetch
|
FetchMode.EAGER
| |
右外连接
|
right outer join 或者 right join
|
不支持
| |
交叉连接
|
ClassA,ClassB
|
不支持
|
适用于不存在关联关系的持久化类
|
"from Customer c left join fetch c.orders o where c.name like 't%'"
List reslut=session.createCriteria(Customer.class) .setFetchMode("orders",FetchMode.EAGER) .add(Expression.like("name","t",MatchMode.START))
List reslut=session.createCriteria(A.class) .setFetchMode("this.b",FetchMode.EAGER) .setFetchMode("this.c",FetchMode.EAGER) .add(Expression.isNotNull("this.b")) .add(Expression.isNotNull("this.c"))
List reslut=session.createCriteria(Customer.class) .add(Expression.like("name","t",MatchMode.SRART)) .createCriteria("orders") .add(Expression.like("orderNumber","t",MatchMode.SRART))
"select c from Customer c join c.orders o where c.name like 't%'"
List reslut=session.createCriteria(Customer.class) .createAlias("orders","o") .add(Expression.like("this.name","t",MatchMode.SRART)) .add(Expression.like("o.orderNumber","t",MatchMode.SRART)) .returnMaps()
List reslut=session.createCriteria(Customer.class) .add(Expression.like("homeAddress.provice","t",MatchMode.SRART))
List reslut=session.createCriteria(Order.class) .add(Expression.like("customer.name","t",MatchMode.SRART))
List reslut=session.createCriteria(Order.class) .createAlias("customer","c") .add(Expression.like("c.name","t",MatchMode.SRART))
四、 报表查询
Iterator it = session.createQurey( "select c.name,count(c) from Customer c group by c.name") .list().iterator(); while (it.hasNext()) { Object[] pair = (Object[]) it.next(); String name = (String) pair[0]; Integer count = (Integer) pair[1];
Customer customer=.... Example exampleCustomer=Example.create(customer); exampleCustomer.ignoreCase().enableLike(MatchMode.ANYWHERE); exampleCustomer.excludeZeroes(); Criteria criteria=session.createCriteria(Customer.class).add(exampleCustomer);
Customer customer=.... Example exampleCustomer=Example.create(customer); exampleCustomer.ignoreCase().enableLike(MatchMode.ANYWHERE); exampleCustomer.excludeZeroes(); Order order=... Example exampleOrder=Example.create(order); exampleOrder.ignoreCase().enableLike(MatchMode.ANYWHERE); exampleOrder.excludeZeroes(); Criteria criteria=session.createCriteria(Customer.class) .add(exampleCustomer) .createCriteria("orders") .add(exampleOrder);
List result=session.createFilter(customer.getOrders(), "where this.price>200 order by this.price").list(); Iterator it=result.iterator(); while(it.hasNext()){ Order order =(Order)it.next(); .....
List result=session.createFilter(customer.getOrders(), " order by this.price asc") .setFirstResult(10) .setMaxResults(5) .list(); List result=session.createFilter(customer.getOrders(), "select this.orderNumber") .list(); List result=session.createFilter(customer.getOrders(), "select other from Order other where other.price=this.price") .list(); List result=session.createFilter(order.getLineItems(), "select this.item")
Iterator it=session.createQurey( "from Customer c where :order in element(c.orders)") .setEntity("order",order) .list()
String sql1 = "select cs.ID as {c.id},cs.name as {c.name} " + "from CUSTOMERS cs where cs.ID=1";
String sql1 = "select {c.*} " + "from CUSTOMERS c where c.ID=1";
String sql1 = "select {c.*},{o.*} from CUSTOMERS c inner join ORDERS o" + " where c.ID=o.CUSTOMER_ID"; Query query = session.createSQLQurey(sql1, new String[] { "c", "o" },
<sql-query name="findCustomersAndOrders"><![CDATA[ select {c.*},{o.*} from CUSTOMERS c inner join ORDERS o where c.ID=o.CUSTOMER_ID ]]> <return alias="c"class="Customer"> <return alias="o"class="Order">
Query query = session .createQuery("from Customer as c where " +"c.name=:customerName and c.age=:customerAge"); // 动态绑定参数 query.setString("customerName", "Test"); query.setInteger("customerAge", 21); // 执行检索 List result = query.list(); // 方法链编程风格 List result1 = session.createQuery( "from Customer as c where c.name=:customerName" + " and c.age=:customerAge").setString( "customerName", "Test").setInteger("customerAge", 21)
Criteria criteria = session.createCriteria(Customer.class); Criterion criterion1 = Expression.like("namr", "T%"); Criterion criterion2 = Expression.eq("age", new Integer(21)); criteria = criteria.add(criterion1); criteria = criteria.add(criterion2); // 执行检索 List result = criteria.list(); // 方法链编程风格 List result1 = session.createCriteria(Customer.class).add( Expression.like("namr", "T%")).add(
Customer exampleCustomer=new Customer(); exampleCustomer.setAge(21); List result1 = session.createCriteria(Customer.class).add(
Query query = session.createSQLQuery("select {c.*} from CUSTOMER as c where c.NAME like :customerName and c.AGE=:customerAge"); // 动态绑定参数 query.setString("customerName", "Test"); query.setInteger("customerAge", 21); // 执行检索
//排序 criteria.addOrder(Order.asc("name"));
criteria = criteria.add(criterion1); criteria = criteria.add(criterion2); criteria.setFirstResult(0); criteria.setMaxResults(10); // 执行检索
query.setString("customerName", "Test"); query.setInteger("customerAge", 21); query.setFirstResult(0); query.setMaxResults(10); // 执行检索
// 单个检索 Customer customer = (Customer) session.createQuery( "from Customer as c order by c.name").setMaxResults(1)
// 单个检索 Customer customer = (Customer) session.createCriteria(
Query query = session.createQuery("from Customer as c where " + "c.name=? and c.age=?"); // 动态绑定参数 query.setString(0, "Test");