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; }}
