Hibernate HQL示例四:条件查询

在

条件查询                  
  * 可以拼字符串的形式传递参数
  * 可以用过？来传递参数（注意索引值是从0开始的，跟jdbc不同，jdbc是从1开始的）
  * 可以通过 ：参数名 来传递参数（即命名参数方式）
  * 如果对应的参数值是多个，可以调用setParamterList()传方法开递
  * 在HQL中可以使用数据库的函数，如date_format()

 示例代码如下:

package com.bjsxt.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;

public class SimpleConditionQueryTest extends TestCase {

	public void testQuery1() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询，拼字符串
			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE '%1%'")
			.list();
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}
	
	public void testQuery2() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询，使用 ？ 的方式传递参数
			Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE ?");
			
			//传递参数
			//参数的索引是从0开始的
			//传递的字符串，无需用''单引号括起来
			query.setParameter(0, "%1%");
			
			
			List students = query.list(); 
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}

	public void testQuery3() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询，使用 ：参数名称 的方式传递参数
			Query query = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname");
			
			//传递参数
			query.setParameter("myname", "%1%");
			
			List students = query.list(); 
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}
	
	public void testQuery4() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询,因为setParameter方法返回Query接口，所以可以用省略的方式来查询
			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.name LIKE :myname and s.id = :myid")
			.setParameter("myname", "%1%")
			.setParameter("myid", 15)
			.list();
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}

	public void testQuery5() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询,支持in，需要用setParameterList()进行参数传递
			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE s.id in(:myids)")
			.setParameterList("myids", new Object[]{1, 3, 5})
			.list();
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}

	public void testQuery6() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询,支持in，需要用setParameter进行参数传递
//			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +
//					"date_format(s.createTime, '%Y-%m') =?")
//			.setParameter(0, "2007-01")
//			.list();

			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +
			"substring(s.createTime, 1, 7) =?")
         	.setParameter(0, "2007-01")
	        .list();
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}

	public void testQuery7() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			
			//条件查询,支持in，需要用setParameter进行参数传递

			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			List students = session.createQuery("SELECT s.id, s.name FROM Student s WHERE " +
			"s.createTime between ? and ? ")
         	.setParameter(0, format.parseObject("2007-01-01"))
         	.setParameter(1, format.parseObject("2007-03-01"))
	        .list();
			
			for (Iterator iter = students.iterator();iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			HibernateUtils.closeSession(session);
		}				
	}
	
}