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();

//

如果有多个值则抛错
如果有值且只有一个,返回一个object
如果没值,返回null

经试验，如果是返回一个值的时候，返回类型：：

如果hql为：select count（*）from 类名------------------------》结果是数字

如果hql为：select 字段 from 类名 where 条件------------------------------》字段值


   } 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;
}
}
一方：

package com.mengya.dao;

import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Transaction;

import com.mengya.entity.TType;
import com.mengya.util.MySessionUilt;

public class TypeDAO extends MySessionUilt {
 private Transaction tran;
 
 /**
  * 一对多中的一方inner join fetch多方
  * 这里不能用inner join on
  * 查询结果是它把一方对多方的那个集合里面的对象都查询出来放到一方的集合中
  */
 public List getallByinnerjoinTShop(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TType as t inner join fetch t.TShops");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 }
 
 /**
  * left join fetch 左外连接
  * 
  */
 public List getallLeftjoinfetchTShop(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TType as t left join fetch t.TShops");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;  
 }
 
 /**
  * left join  左外连接
  * 
  */
 public List getallLeftjoinTShop(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TType as t left join  t.TShops");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;  
 }
 
 /**
  * 隐式内连接
  */
 public List getall(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TType as t,TShop as s where s.TType=t");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 }
 
 /**
  * 集合过滤
  */
 public List getallfilter(int price){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TType");
   list=query.list();
   Iterator iter=list.iterator();
   while(iter.hasNext()){
    TType type=(TType) iter.next();
    Query que=this.GetSession().createFilter(type.getTShops(), "where SPrice>=?");
    que.setInteger(0, price);
    List arr=que.list();
    type.setTShops(new HashSet(arr));
   }
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 }
 
}
多方：

package com.mengya.dao;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Transaction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;

import com.mengya.entity.TShop;
import com.mengya.util.MySessionUilt;

public class ShopDAO extends MySessionUilt {
 private Transaction tran;
 
 /**
  * 多条件组合查询
  */
 public List getallByMoreWhere(String name,double price){
  List list=null;
  try {
   Criteria cri=this.GetSession().createCriteria(TShop.class);
   if(name!=null && name.length()>1){
    cri.add(Restrictions.like("SName", name,MatchMode.ANYWHERE));
   }
   if(price>0){
    cri.add(Restrictions.lt("SPrice", price));
   }
   list=cri.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 }
 
 /**
  * 多对一中的多方的inner join on（主对象可以查出从对象，即使主对象中关与从对象的lazy="true"也是可以把从对象查询出来）
  * 以TShop为主，若TShop的TType为空，则查询后没有这条记录。即查询结果都是非空的
  * 这种方式List中存放的是Object的数组，Object[]中的元素才是TShop和TType
  */
 public List getallByinnerjoinTtype(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TShop as s inner join s.TType");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 }
 
 /**
  * 多对一中的多方的inner join fetch（主对象可以查出从对象，即使主对象中关与从对象的lazy="true"也是可以把从对象查询出来）
  * 以TShop为主，若TShop的TType为空，则查询后没有这条记录。即查询结果都是非空的
  * 这种方式List中存放的是TShop对象,TShop.TType对象是非空的
  */
 public List getallByinnerjoinTtype2(){
  List list=null;
  try {
   Query query=this.GetSession().createQuery("from TShop as s inner join fetch s.TType");
   list=query.list();
  } catch (HibernateException e) {
   throw e;
  }finally{
   this.ColseSession();
  }
  return list;
 } 
}

测试：

package com.mengya.test;

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

import com.mengya.dao.ShopDAO;
import com.mengya.dao.TypeDAO;
import com.mengya.entity.TShop;
import com.mengya.entity.TType;

public class Test {
 
 public static void main(String[] args) {
  ShopDAO shopdao=new ShopDAO();
  TypeDAO typedao=new TypeDAO();
  /*List list=shopdao.getallByMoreWhere("", 50);
  Iterator cri=list.iterator();
  while(cri.hasNext()){
   TShop shop=(TShop) cri.next();
   System.out.println(shop);
  }*/
  
  /* 多对一中的多方的inner join on（主对象可以查出从对象，即使主对象中关于从对象的lazy="true"也是可以把从对象查询出来）
   * 以TShop为主，若TShop的TType为空，则查询后没有这条记录。即查询结果都是非空的
   */
  /*List list=shopdao.getallByinnerjoinTtype();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   Object[] obj=(Object[]) iter.next();
   TShop shop=(TShop) obj[0];
   TType type=(TType) obj[1];
   System.out.println(shop.getSName()+"  "+shop.getSPrice()+"  所属类别"+shop.getTType().getTName());
   System.out.println(type.toString());
   
  }*/
  
  /**
   * 用的from TShop as s inner join fetch s.TType
   * 这样list中存放的就是TShop对象，但它会把一方也查出来，
   * 同样如查TShop对象对应的TType为null，这个TShop也不会查询出来，即查询结果都是非空的。
   */
  /*List list=shopdao.getallByinnerjoinTtype2();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   //这样就是错的
   //Object[] obj=(Object[]) iter.next();
   //TShop shop=(TShop) obj[0];
   //TType type=(TType) obj[1];
   //System.out.println(shop.getSName()+"  "+shop.getSPrice()+"  所属类别"+shop.getTType().getTName());
   //System.out.println(type.toString());
   TShop shop=(TShop) iter.next();
   System.out.println(shop.getSName()+"  "+shop.getSPrice()+"  所属类别"+shop.getTType().getTName());   
  }*/
  
  /*List list=typedao.getallByinnerjoinTShop();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   TType type=(TType) iter.next();
   System.out.println(type.toString());
   Iterator<TShop> it=type.getTShops().iterator();
   while(it.hasNext()){
    TShop sh=it.next();
    System.out.println(sh.toString());
   }
  }*/
  
  /*List list=typedao.getallLeftjoinfetchTShop();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   TType type=(TType) iter.next();
   System.out.println(type.toString());
   Iterator<TShop> it=type.getTShops().iterator();
   while(it.hasNext()){
    TShop sh=it.next();
    System.out.println(sh.toString());
   }
  }*/
  
  /*List list=typedao.getallLeftjoinTShop();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   Object[] obj=(Object[]) iter.next();
   TType type=(TType) obj[0];
   TShop shop=(TShop) obj[1];
   System.out.println(type.toString());
   //访问type对象里面的TShops集合里的元素,有懒加载问题
   //Iterator<TShop> it=type.getTShops().iterator();
   //while(it.hasNext()){
   //TShop sh=it.next();
   //System.out.println(sh.toString());
   //}
   //from TType as t left join  t.TShops shop可能为空
   if(shop!=null){
    System.out.println(shop.toString());
   }   
  }*/
  
  /**
   * 隐式内联连
   */
  /*List list=typedao.getall();
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   Object[] obj=(Object[]) iter.next();
   TType type=(TType) obj[0];
   TShop shop=(TShop) obj[1];
   System.out.println(type);
   System.out.println(shop);
  }*/
  
  List list=typedao.getallfilter(10);
  Iterator iter=list.iterator();
  while(iter.hasNext()){
   TType type=(TType) iter.next();
   System.out.println(type.toString());
   Iterator<TShop> it=type.getTShops().iterator();
   while(it.hasNext()){
    TShop sh=it.next();
    System.out.println(sh.toString());
   }
  }
  
 }

}

 

评论

1 楼 hlbng 2009-06-04  

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