package myHibernate; /* * 测试简单的HQL语句 * 2010年4月9日 23:36:54 * */ import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Set; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; import junit.framework.TestCase; public class HQLTest extends TestCase { public void intiData() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); Classes classes = new Classes(); classes.setName("713"); session.save(classes); Student student1 = new Student(); Student student2 = new Student(); student1.setCreateTime(new Date()); student2.setCreateTime(new Date()); student1.setName("小妹"); student2.setName("小桂"); student1.setClasses(classes); student2.setClasses(classes); session.save(student1); session.save(student2); tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } public void testHQL_1() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); //小%要加单引号哦 //注意返回的结果,此处查询的是属性而不是对象,故返回的是与该属性同类型的属性列表 List studentName = session.createQuery("select name from Student where name like '小%'").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ String name = (String) iter.next(); System.out.println(name); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } //查询多个属性 public void testHQL_2() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); //如果查询多个属性则返回的是由Object数组组成的list——每个数组代表一组属性。 //数组的长度取决于select语句中属性的个数 List studentName = session.createQuery("select id,name from Student").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ Object objects[]= (Object[]) iter.next(); System.out.print("id: "+objects[0]+" "); /* int id =(Integer)objects[0]; System.out.println("id+10:"+(id+10));*/ System.out.println("name: "+objects[1]); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } //查询多个属性,并且返回的是一个具体的类型(如过觉得Object不够对象化的话),而不是Object //注意必须写构造方法,包括无参的(原则上要求) public void testHQL_3() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); //此时list中为Student对象 List studentName = session.createQuery("select new Student(id,name) from Student").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ Student student = (Student) iter.next(); System.out.println(student.getId()+" "+student.getName()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } //使用别名进行查询,可以使用as关键子也可以不用 public void testHQL_4() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); // List studentName = session.createQuery("select s.id,s.name from Student s").list(); //使用了as关键字 List studentName = session.createQuery("select s.id,s.name from Student as s").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ Object objects[]= (Object[]) iter.next(); System.out.print("id: "+objects[0]+" "); System.out.println("name: "+objects[1]); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* *查询实体对象 *可以采用别名也可以不用 * */ public void testHQL_5() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); // List studentName = session.createQuery("from Student").list(); // List studentName = session.createQuery("from Student s").list(); List studentName = session.createQuery("from Student as s").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ Student s =(Student)iter.next(); System.out.println(s.getName()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 采用select查询实体的话一定要用别名 * */ public void testHQL_6() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); // List studentName = session.createQuery("from Student").list(); // List studentName = session.createQuery("from Student s").list(); List studentName = session.createQuery("select s from Student as s").list(); Iterator iter = studentName.iterator(); while(iter.hasNext()){ Student s =(Student)iter.next(); System.out.println(s.getName()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 采用迭代接口(Iterator)进行查询 * 会有"N+1"问题出现: * N:发出N条语句到数据库进行查询(每个记录一条语句) * 1:发出查询id列表的语句 * 原因:迭代接口使用缓存(session),通过查询出来的id到缓存中去找,找不到就会到数据库中去找 * */ public void testHQL_7() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); // List studentName = session.createQuery("from Student").list(); Iterator iter = session.createQuery("select s from Student as s").iterate(); while(iter.hasNext()){ Student s =(Student)iter.next(); System.out.println(s.getName()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 先执行用list接口进行的查询再执行iterator接口的查询 * */ public void testHQL_8() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); // List studentName = session.createQuery("from Student").list(); List students = session.createQuery("from Student").list(); Iterator iter1 = students.iterator(); while(iter1.hasNext()){ Student student = (Student) iter1.next(); System.out.println(student.getId()+" "+student.getName()); } //由于上面先用list进行了一次查询,故缓存中存在了这些对象,下面用iterate进行 //查询时到缓存中进行查找,找得到这些对象,故只发出了一条语句:查询id的语句 //总结:在有缓存的前提下使用iterate会提高效率 Iterator iter = session.createQuery("select s from Student as s").iterate(); while(iter.hasNext()){ Student s =(Student)iter.next(); System.out.println(s.getName()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 简单条件查询 * 可以采用?方式进行查询,代表一个参数 * 参数的索引从0开始 * */ public void testHQL_9() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); /* Query query = session.createQuery("select name from Student where name like ?"); query.setParameter(0, "%王"); Iterator iter = query.list().iterator();*/ //采用方法链的方式: Iterator iter = session.createQuery("select name from Student where name like ?") .setParameter(0, "小%") .list() .iterator(); while(iter.hasNext()){ System.out.println(iter.next()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 采用:参数名称的方式 * */ public void testHQL_10() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); /* Query query = session.createQuery("select name from Student where name like ?"); query.setParameter(0, "%王"); Iterator iter = query.list().iterator();*/ //采用方法链的方式: Iterator iter = session.createQuery("select name from Student where name like :name") .setParameter("name", "小%") .list() .iterator(); while(iter.hasNext()){ System.out.println(iter.next()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 采用:参数名称的方式2:in方法 * */ public void testHQL_11() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); Query query = session.createQuery("select name from Student where id in(:myids)"); Object ids[] = new Object[]{1,2,3}; query.setParameterList("myids",ids); Iterator iter = query.list().iterator(); while(iter.hasNext()){ System.out.println(iter.next()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 附加日期条件1 * */ public void testHQL_12() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); Query query = session.createQuery("select name from Student where date_format(createTime,'%Y-%m-%d')=?"); query.setParameter(0,"2010-04-10"); Iterator iter = query.list().iterator(); while(iter.hasNext()){ System.out.println(iter.next()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 附加日期条件2 * 【注意】写的MM,而不是小写的mm m是小时中的分钟数 M是年中的月份 * 【重要】掌握定义日期的方法 * */ public void testHQL_13() { Session session = null; Transaction tx = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); Query query = session.createQuery("select name from Student where createTime between ? and ?"); //要求参数必须是一个日期 Date beginTime=new Date(),endTime=new Date(); try { beginTime = sdf.parse("2010-02-10 00:00:00"); endTime = sdf.parse("2010-04-10 23:59:59"); } catch (ParseException e) { e.printStackTrace(); } query.setParameter(0,beginTime); query.setParameter(1,endTime); Iterator iter = query.list().iterator(); while(iter.hasNext()){ System.out.println(iter.next()); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } /* * 使用SQL语句 * */ public void testHQL_14() { Session session = null; Transaction tx = null; try { session = HibernateUtils.getSession(); tx = session.beginTransaction(); Query query = session.createSQLQuery("select * from student"); Iterator iter = query.list().iterator(); while(iter.hasNext()){ Object obj[] = (Object[]) iter.next(); System.out.println(obj[0]+" "+obj[1]); } tx.commit(); } catch (HibernateException e) { e.printStackTrace(); if (tx != null) tx.rollback(); } finally { HibernateUtils.closeSession(session); } } }