017——hibernate条件查询

实体类以及映射文件参考015篇

测试类：

package test;

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

import junit.framework.TestCase;

import org.hibernate.Session;

import com.java.hibernate.HibernateUtils;

public class SimpleConditionQueryTest extends TestCase {

	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);
		}
	}
	
	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);
		}
	}		
	
	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 :itachi")
					.setParameter("itachi",  "%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);
		}
	}	
	
	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);
		}
	}		
	
	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);
		}
	}
	
	public void testQuery6() {
		Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			//查询2016-07的学生，可以调用mysql的日期格式化函数
			List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")
					.setParameter(0,  "2016-07")
					.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);
		}
	}	

	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("2016-07-01 00:00:00"))
					.setParameter(1, sdf.parse("2016-07-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);
		}
	}
}