4.hql条件查询:测试

package com.bjpowernode.hibernate;

import java.text.SimpleDateFormat;
import java.util.Date;

import org.hibernate.Session;

public class InitData {

	public static void main(String[] args) {
			Session session = HibernateUtils.getSession();

			try {
				session.beginTransaction();

				for(int i=0; i<10; i++){
				
					Classes classes = new Classes();
					classes.setName("班级"+i);
					session.save(classes);
					
					for(int j=0; j<10; j++){
						Student student = new Student();
						student.setName("班级"+i+"的学生"+j);
						student.setCreateTime(randomDate("2009-07-01","2009-09-01"));
						
						//在内存中建立由student指向classes的引用
						student.setClasses(classes);
						session.save(student);
					}
				}
				
				for(int i=0; i<5; i++){
					Classes classes = new Classes();
					classes.setName("无学生班级"+i);
					session.save(classes);
				}
				
				for(int i=0; i<10; i++){
					Student student = new Student();
					student.setName("无业游民"+i);
					session.save(student);
				}
				
				session.getTransaction().commit();
			} catch (Exception e) {
				e.printStackTrace();
				session.getTransaction().rollback();
			} finally{
				HibernateUtils.closeSession(session);
			}
		}	
		
		/**
		 * 获取随机日期
		 * @param beginDate 起始日期，格式为：yyyy-MM-dd
		 * @param endDate 结束日期，格式为：yyyy-MM-dd
		 * @return
		 */
		private static Date randomDate(String beginDate,String endDate){
			try {
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				Date start = format.parse(beginDate);
				Date end = format.parse(endDate);
				
				if(start.getTime() >= end.getTime()){
					return null;
				}
				
				long date = random(start.getTime(),end.getTime());
				
				return new Date(date);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
		
		private static long random(long begin,long end){
			long rtn = begin + (long)(Math.random() * (end - begin));
			if(rtn == begin || rtn == end){
				return random(begin,end);
			}
			return rtn;
		}
}

package com.bjpowernode.hibernate;

import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import junit.framework.TestCase;

/**
 * 条件查询
 * @author Administrator
 *
 */
public class SimpleConditionQueryTest extends TestCase {

	@SuppressWarnings("unchecked")
	public void testQuery1() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//可以拼串
			List students = session.createQuery("select s.id, s.name from Student s where s.name like '%0%'").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	@SuppressWarnings("unchecked")
	public void testQuery2() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//可以使用？方式传递参数
			//索引从0开始，不同于jdbc从1开始
			//值能使用单引号引起来
			//List students = session.createQuery("select s.id, s.name from Student s where s.name like ?").list();
//			Query query  = session.createQuery("select s.id, s.name from Student s where s.name like ?");
//			query.setParameter(0, "%0%");
//			List students = query.list();
			
			//方法链编程，建议采用此种方式
			List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
					.setParameter(0, "%0%")
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}		
	
	@SuppressWarnings("unchecked")
	public void testQuery3() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//可以采用 :参数名 的方式传递参数
			List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname")
					.setParameter("myname",  "%0%")
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	@SuppressWarnings("unchecked")
	public void testQuery4() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//采用 ？方式，查询学号为1,2,3,4,5的学生
			List students = session.createQuery("select s.id, s.name from Student s where s.id in(?, ?, ?, ?, ?)")
					.setParameter(0, 1)
					.setParameter(1, 2)
					.setParameter(2, 3)
					.setParameter(3, 4)
					.setParameter(4, 5)
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}		
	
	@SuppressWarnings("unchecked")
	public void testQuery5() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//采用 :参数名 方式，查询学号为1,2,3,4,5的学生
			List students = session.createQuery("select s.id, s.name from Student s where s.id in(:ids)")
					.setParameterList("ids", new Object[]{1, 2, 3, 4, 5})
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}
	
	/**
	 * 把时间限定在某年某月
	 */
	@SuppressWarnings("unchecked")
	public void testQuery6() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//查询2009-08的学生，可以调用mysql的日期格式化函数
			List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")
					.setParameter(0,  "2009-08")
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	

	/**
	 * 把时间限定在某段时间内
	 */
	@SuppressWarnings("unchecked")
	public void testQuery7() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			//查询2009-08-01 到2009-08-20的学生，可以调用mysql的日期格式化函数
			List students = session.createQuery("select s.id, s.name from Student s where s.createTime between ? and ?")
					.setParameter(0, sdf.parse("2009-08-01 00:00:00"))
					.setParameter(1, sdf.parse("2009-08-20 23:59:59"))
					.list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}		
}