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", "[e-mail]")
));
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();
}
}