hibernate进阶查询--criteria查询

package org.ijob.db.hibernate.test;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.hibernate.Criteria;

import org.hibernate.FetchMode;

import org.hibernate.Session;

import org.hibernate.criterion.DetachedCriteria;

import org.hibernate.criterion.Order;

import org.hibernate.criterion.Projections;

import org.hibernate.criterion.Restrictions;

import org.hibernate.type.IntegerType;

import org.ijob.bean.Company;

import org.ijob.bean.Position;

import org.ijob.bean.Seeker;

import org.ijob.db.hibernate.util.HibernateUtil;

public class HibernateTest {

//Criteria查询

public static void queryAll(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Seeker.class);

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//DetachedCriteria查询

public static void queryDetchedCriteria(){

DetachedCriteria dc = DetachedCriteria.forClass(Seeker.class);

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = dc.getExecutableCriteria(session);

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//限制条件

public static void query1(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Seeker.class);

// query.add(Restrictions.gt("bodyHigh", 160));

// query.add(Restrictions.lt("bodyHigh", 170));

// query.add(Restrictions.isNotNull("email"));

// query.add(Restrictions.isNotEmpty("resumes"));

// query.add(Restrictions.eqProperty("username", "name"));

// query.add(Restrictions.eq("name", "张三"));

query.add(Restrictions.or(

Restrictions.and(

Restrictions.le("name", "张%"),

Restrictions.gt("bodyHigh", 165)

)

,

Restrictions.eq("email", "[email protected]")

));

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//潜入SQL表达式

public static void queryExpress(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Seeker.class);

query.add(Restrictions.sqlRestriction("length({alias}.name)<?", 5, IntegerType.INSTANCE));

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//排序

public static void queryOrder(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Seeker.class);

query.addOrder(Order.asc("birth"));

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//分页

public static void queryPage(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Seeker.class);

query.setFirstResult(0);

query.setMaxResults(2);

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

for(Seeker s : list){

System.out.println(s.getName());

}

session.close();

}

//连接

public static void queryJoin(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Company.class);

query.add(Restrictions.eq("name", "蓝桥计划"));

query.createCriteria("positions").add(Restrictions.gt("salary", "4"));

List<Company> list = query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

for(Company c : list){

System.out.println(c.getName());

}

session.close();

}

//连接(别名)

public static void queryJoin1(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Company.class)

.createAlias("positions", "p")//加别名

.add(Restrictions.eq("name", "蓝桥计划"))

.add(Restrictions.gt("p.salary", "4"));

List<Company> list = query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

for(Company c : list){

System.out.println(c.getName());

// System.out.println("===============");

// System.out.println(c.getPositions());

}

session.close();

}

//立即加载

public static void queryFetch(){

Session session = HibernateUtil.getSessionFactory().openSession();

Criteria query = session.createCriteria(Company.class)

.setFetchMode("positions", FetchMode.JOIN)//关联和立即加载

.add(Restrictions.eq("name", "蓝桥计划"));

List<Company> list = query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();

for(Company c : list){

System.out.println(c.getName());

System.out.println("===============");

System.out.println(c.getPositions());

}

session.close();

}

//结果转换

public static void queryResultChange(){

Session session = HibernateUtil.getSessionFactory().openSession();

List result = session.createCriteria(Company.class)

.createAlias("positions", "p")

.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP)

.list();

Iterator iter = result.iterator();

while(iter.hasNext()){

Map m = (Map)iter.next();

Company c = (Company)m.get(Criteria.ROOT_ALIAS);//获取根对象

Position p = (Position)m.get("p");

System.out.println(c.getName()+" "+p.getName());

}

session.close();

}

//投影查询

public static void queryProjection(){

Session session = HibernateUtil.getSessionFactory().openSession();

List list = session.createCriteria(Company.class)

.setProjection(Projections.projectionList()

.add(Projections.id())

.add(Projections.property("name"))

).list();

session.close();

}

//分组和组函数

public static void queryGroup(){

Session session = HibernateUtil.getSessionFactory().openSession();

List list = session.createCriteria(Company.class)

.createAlias("positions", "p")

.setProjection(Projections.projectionList()

.add(Projections.groupProperty("id"))

.add(Projections.groupProperty("name"))

.add(Projections.avg("p.salary"))

).list();

session.close();

}

public static void main(String[] args){

queryGroup();

}

}