Hibernate完成常用的JDBC操作上

package com.mengya.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;

import com.mengya.entity.Stu;
import com.mengya.util.SessionFactoryUitl;

/**
 * Query query=this.GetSession().createQuery(hql); query.iterate()与query.list()
 * 效率是说query.iterate()比query.list()好，但做了N+1次查询 它们都是先从数据库的查询出主键值，然后根据主键值查询出对象，
 * 只是query.iterate()会根据主键值先从缓存当中找,若没有就从数据库查询 query.list()会根据主键值在数据库查询(N+1)
 */

public class StuDAO extends SessionFactoryUitl {
 private Transaction tran;

 /**
  * 添加
  */
 public void save(Stu stu) {
  try {
   tran = this.GetSession().beginTransaction();
   this.GetSession().save(stu);
   tran.commit();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
 }

 /**
  * 使用HQL全查询
  */
 public List getallbyHQL() {
  List arr = null;
  try {
   String hql = "from Stu";
   Query query = this.GetSession().createQuery(hql);
   arr = query.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 使用Criteria全查询
  */
 public List getallbyCriteria() {
  List arr = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   arr = cri.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 根据主键查询
  */
 public Stu getbyID(int id) {
  Stu stu = null;
  try {
   stu = (Stu) this.GetSession().get(Stu.class, id);
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return stu;
 }

 /**
  * 根据对象属性查询（使用Query）
  */
 public List getbyPropertyQuery(String name) {
  List arr = null;
  try {
   // 这里不能像SQL语一样select * from Stu where SName=:name,这是不对的。
   // Query query=this.GetSession().createQuery("from Stu where
   // SName=:name");
   // query.setString("name", name);
   // 或者
   Query query = this.GetSession().createQuery(
     "from Stu where SName=?");
   query.setString(0, name);
   arr = query.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 根据对象属性查询(使用Criteria)
  */
 public List getbyPropertyCriteria(String name) {
  List arr = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   Criterion c1 = Expression.eq("SName", name);
   cri.add(c1);
   arr = cri.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 查询部分属性
  */
 public List getProperty() {
  List arr = new ArrayList();
  try {
   String hql = "select s.SName,s.SSex from Stu as s";
   Query query = this.GetSession().createQuery(hql);
   List list = query.list();
   Iterator iter = list.iterator();
   while (iter.hasNext()) {
    Object[] obj = (Object[]) iter.next();
    Stu s = new Stu();
    s.setSName(obj[0].toString());
    s.setSSex(obj[1].toString());
    arr.add(s);
   }
  } catch (HibernateException e) {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 查询一个属性
  */
 public List getoneProperty() {
  List arr = new ArrayList();
  try {
   String hql = "select s.SName from Stu as s";
   Query query = this.GetSession().createQuery(hql);
   Iterator iter = query.iterate();
   while (iter.hasNext()) {
    Object obj = (Object) iter.next();
    Stu s = new Stu();
    s.setSName(obj.toString());
    arr.add(s);
   }
  } catch (HibernateException e) {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 查询一个对象一个属性值
  */
 public Object getonlyProprotyValue(int s_id) {
  Object obj = null;
  try {
   String hql = "select s.SName from Stu as s where s.SId=?";
   Query query = this.GetSession().createQuery(hql);
   query.setInteger(0, s_id);
   obj = query.uniqueResult();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return obj;
 }

 /**
  * Criteria多条件查询
  */
 public List getPropertyByCriteria(String name, int age) {
  List list = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   Criterion c1 = Expression.eq("SName", name);
   Criterion c2 = Expression.eq("SAge", age);
   cri.add(c1);
   cri.add(c2);
   list = cri.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return list;
 }

 /**
  * SQL查询
  */
 public List getallBYSQL() {
  List arr = null;
  try {
   String sql = "select {c.*} from stu as c";
   SQLQuery sqlquery = this.GetSession().createSQLQuery(sql);
   sqlquery.addEntity("c", Stu.class);
   arr = sqlquery.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 根据对象查询
  */
 public List getallByObject(Stu s) {
  List arr = null;
  try {
   String hql = "from Stu as s where s=:stuentity";
   // 或者
   // String hql="from Stu as s where s.SId=:stuentity";
   Query query = this.GetSession().createQuery(hql);
   query.setEntity("stuentity", s);
   arr = query.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 模糊查询
  */
 public List getallQueryLike(String name) {
  List arr = null;
  try {
   String hql = "from Stu as s where s.SName like :name";
   Query query = this.GetSession().createQuery(hql);
   query.setString("name", "%" + name + "%");
   // 不能
   // query.setString("name", "'%"+name+"%'");
   arr = query.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * Criteria模糊查询
  */
 public List getallCriteriaLike(String name) {
  List arr = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   Criterion c1 = Expression.like("SName", "%" + name + "%");
   cri.add(c1);
   arr = cri.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * Criteria范围查询
  */
 public List getallCriteriabetween(int age1, int age2) {
  List arr = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   Criterion c1 = Expression.between("SAge", age1, age2);
   cri.add(c1);
   arr = cri.list();
  } catch (HibernateException e) {
   this.CloseSession();
  } finally {
   this.CloseSession();
  }
  return arr;
 }

 /**
  * 统计函数
  */
 public int CountStu() {
  int count = 0;
  try {
   String hql = "select count(*) from Stu";
   Query query = this.GetSession().createQuery(hql);
   count = (Integer) query.uniqueResult();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return count;
 }

 /**
  * Criteria条件统计
  */
 public int CountStuByWhereInCriteria(int num1, int num2) {
  int count = 0;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   Criterion c1 = Expression.between("SAge", num1, num2);
   cri.add(c1);
   count = cri.list().size();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return count;
 }

 /**
  * 条件统计
  */
 public int CountByWhere(String sex) {
  int count = 0;
  try {
   Query query = this.GetSession().createQuery(
     "select count(*) from Stu where SSex=:sex");
   query.setString("sex", sex);
   count = (Integer) query.uniqueResult();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return count;
 }

 /**
  * 统计平均值
  */
 public float VagAge() {
  float vag = 0;
  try {
   Query query = this.GetSession().createQuery(
     "select avg(SAge) from Stu");
   vag = (Float) query.uniqueResult();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return vag;
 }

 /**
  * 求和函数
  */
 public int sumage() {
  int sum = 0;
  try {
   Query query = this.GetSession().createQuery(
     "select sum(SAge) from Stu");
   sum = (Integer) query.uniqueResult();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return sum;
 }

 /**
  * Criteria排序
  */
 public List getallCriteriaOrder() {
  List arr = null;
  try {
   Criteria cri = this.GetSession().createCriteria(Stu.class);
   cri.addOrder(Order.desc("SAge"));
   arr = cri.list();
  } catch (HibernateException e) {
   throw e;
  } finally {
   this.CloseSession();
  }
  return arr;
 }
}