HQL (三) 条件查询

条件查询（重要）
* 可以采用拼字符串的方式传递参数
* 可以采用 ？号的方式传递参数
* 可以采用 :参数名的方式传递参数
* 如果传递多个参数，使用setParameterList来传递
* 在hql中可以使用数据库的函数，如：date_format
* 参见：SimpleConditionQueryTest.java

"like" 和 "=" 都可以通过 ": 参数名" 或者 "?" 接参数

  如下：  like :参数名  ,like ? , =:参数名 , =?

package com.wlh.hibernate;

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

import junit.framework.TestCase;

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

public class SimpleConditionQueryTest extends TestCase{

	public void testQuery1() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//可以拼字符串

			List students=session.createQuery("select id,name from Student where name like '%1%'").list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	public void testQuery2() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b]//可以使用 ？号方式传递参数
			//参数的索引从0开始
			//传递的参数值不用单引号引起来[/b]

			 /*Query query=session.createQuery("select id,name from Student where name like ?");
			 query.setParameter(0, "%1%");
			 List students=query.list();*/
			
			List students=session.createQuery("select id,name from Student where name like ? ").setParameter(0, "%1%").list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	public void testQuery3() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b]//like :参数名[/b]	
		 Query query=session.createQuery("select id,name from Student where name like :myname");
			 query.setParameter("myname", "%1%");
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	public void testQuery4() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[color=red]//=？[/color]	

	Query query=session.createQuery("select id,name from Student where  id=?");
			query.setParameter(0,10);
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	public void testQuery5() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b][color=red]//=:参数名[/color][/b]	

		Query query=session.createQuery("select id,name from Student where  id=:myid");
			query.setParameter("myid",10);
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	public void testQuery6() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b]// like :参数名   
			// = :参数名   [/b]

			 Query query=session.createQuery("select id,name from Student where name like :myname and id =:myid");
			 query.setParameter("myname", "%1%");
			 query.setParameter("myid", 11);
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	public void testQuery7() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
	[b][color=red]//支持in，需要使用setPramaterList进行参数传递
                     //传入的参数必须是对象数组[/color][/b]

			 Query query=session.createQuery("select id,name from Student where id in(:myids)");
              query.setParameterList("myids", new Object[]{1,2,3,4,5});
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	
	public void testQuery8() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b]//查询2007年2月创建的学生(模糊查询)[/b]

			
			/*Query query=session.createQuery("select id,name from Student where date_format(createTime,'%Y-%m')=?");
              query.setParameter(0, "2007-02");*/
			Query query=session.createQuery("select id,name from Student where substring(createTime,1,7)=?");
			query.setParameter(0, "2007-02");
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
	
	
	public void testQuery9() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			[b]//找出某个（时间段）入学的学生
			////查询2007-01-01到2007-01-20创建的学生[/b]

			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Query query=session.createQuery("select id,name from Student where createTime between ? and ? order by id");
			query.setParameter(0, sdf.parse( "2007-01-10 00:00:00"));
			query.setParameter(1, sdf.parse( "2007-01-20 23:59:59"));
			 List students=query.list();
			for(Iterator iter=students.iterator();iter.hasNext();){
				Object[] o=(Object[])iter.next();
				System.out.println(o[0]+","+o[1]);
			}
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtils.closeSession(session);
		}
	}	
	
}