Hibernate_HQL的各种查询_Demo

package org.com.test.test;



import java.util.List;

import org.com.test.model.Special;
import org.com.test.model.Student;
import org.com.test.model.StudentDto;
import org.com.test.util.HibernateUtil;
import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;

public class TestHQL {

	@SuppressWarnings("unchecked")
	@Test
	public void test01(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 对于HQL而言，都是基于对象进行查询的。
			 */
			Query query = session.createQuery("from Special");
			List<Special> specails = query.list();
			for (Special s : specails) {
				System.out.println(s.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test02(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 对于HQL而言，都是基于对象进行查询的。
			 * 不是使用select * 进行查询
			 */
			//Query query = session.createQuery("select * from Special");
			/**
			 * 可是使用链式查询的方式。
			 */
			List<Special> specails = session.createQuery("select spe from Special spe").list();
			for (Special s : specails) {
				System.out.println(s.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test03(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 使用这种查询存在注入攻击的危险，我们习惯使用的%?%;
			 */
			List<Student> students = session.createQuery("from Student where name like '%李%' ").list();
			for (Student s : students) {
				System.out.println(s.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test04(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 使用这种查询存在注入攻击的危险，我们习惯使用的%?%;
			 * 这里注意，我们在JDBC的时候使用PrepareStatment预处理的时候，参数是从第1位开始的（既是设置的最小下标）；这里是从第0位开始。
			 */
			List<Student> students = session.createQuery("from Student where name like ? ")
										.setParameter(0,"%张%").list();
			for (Student s : students) {
				System.out.println(s.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test05(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 还可以基于别名进行查询；使用    ：xxx来说明别名的名称
			 */
			List<Student> students = session.createQuery("from Student where name like :name and sex=:sex ")
					.setParameter("name", "%刘%").setParameter("sex", "男").list();
			for (Student s : students) {
				System.out.println(s.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test06(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 还可以基于别名进行查询；使用    ：xxx来说明别名的名称
			 */
			List<Long> students = session.createQuery("select count(*) from Student where name like :name and sex=:sex ")
					.setParameter("name", "%刘%").setParameter("sex", "男").list();
			for (Long s : students) {
				System.out.println(s);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}

	
	@Test
	public void test07(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 使用uniqueResult可以返回一个唯一的值。
			 */
			Long students = (Long)session.createQuery("select count(*) from Student where name like :name and sex=:sex ")
					.setParameter("name", "%刘%").setParameter("sex", "男").uniqueResult();
				System.out.println(students);
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@Test
	public void test08(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 * 使用uniqueResult可以返回一个唯一的值。
			 */
			Student student = (Student)session.createQuery("select stu from Student stu where id=:id ")
					.setParameter("id", 20).uniqueResult();
			System.out.println(student.getName());
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
/**
 * **********************************************以上都是基于对象的查询，下面是查询对象中字段*********************
 */
	
	@SuppressWarnings("unchecked")
	@Test
	public void test09(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *基于投影过的查询，通过在列表中存储一个对象的数组；
			 */
			List<Object[]> student = session.createQuery("select stu.sex,count(*) from Student stu group by stu.sex")
										.list();
			for (Object[] objects : student) {
				System.out.println(objects[0]+":"+objects[1]);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test10(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *如果对象中存在相应的导航对象，可以直接导航完成查询
			 */
			List<Student> stus = session.createQuery("select stu from Student stu where stu.classroom.name = ? and"
						+ " stu.name like ? and stu.sex = ?")
					.setParameter(0, "数学教育1班").setParameter(1, "%李%").setParameter(2, "女").list();
			for (Student stu : stus) {
				System.out.println(stu.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test11(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *可是设置in来设置基于列表的查询，此处的查询需要使用别名进行查询；
			 *特别注意，使用in的查询必须放到其他查询之后，否者报错。
			 */
			List<Student> stus = session.createQuery("select stu from Student stu where stu.name like ? and"
					+ " stu.sex = ? and stu.classroom.id in (:cla)")
					.setParameterList("cla", new Integer[]{1,2}).setParameter(0, "%李%").setParameter(1, "女").list();
			for (Student stu : stus) {
				System.out.println(stu.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test12(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *使用setFirstResult和setMaxResult可以完成分页的offset和pageSize的设置
			 */
			List<Student> stus = session.createQuery("select stu from Student stu where stu.classroom.id in (:cla)")
					.setParameterList("cla", new Integer[]{1,2,3,4,5})
					.setFirstResult(0).setMaxResults(15).list();
			for (Student stu : stus) {
				System.out.println(stu.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test13(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *可以使用is null来查询为空的对象，和sql一样不能使用=号来查询对象(视频里说不可以，但是我测试时可以的)。
			 */
			List<Student> stus = session.createQuery("select stu from Student stu where stu.classroom.id is null")
					.setFirstResult(0).setMaxResults(15).list();
		//	List<Student> stus = session.createQuery("select stu from Student stu where stu.classroom.id = null")
		//			.setFirstResult(0).setMaxResults(15).list();
			for (Student stu : stus) {
				System.out.println(stu.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test14(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *使用对象的导航链接可以完成链接，但是是基于Cross Join ,效率不高，可以直接使用Join来完成链接
			 */
			List<Student> stus = session
					.createQuery("select stu from Student stu left join stu.classroom cla where cla.id = 2")
					.setFirstResult(0).setMaxResults(15).list();
			for (Student stu : stus) {
				System.out.println(stu.getName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test15(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *查询每个班级的人数。
			 */
			List<Object[]> stus = session
					.createQuery("select cla.name,count(stu.classroom.id) from Student stu right join stu.classroom cla group by cla.id")
					.list();
			for (Object[] stu : stus) {
				System.out.println(stu[0]+":"+stu[1]);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test16(){
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *查询每个学生所在的班级和所在的专业
			 */
			List<Object[]> stus = session
					.createQuery("select stu.id,stu.name,stu.sex,cla.name,spe.name from Student stu left join stu.classroom cla "
							+ " left join cla.special spe ")
					.list();
			for (Object[] stu : stus) {
				System.out.println(stu[0]+":"+stu[1]+":"+stu[2]+":"+stu[3]+":"+stu[4]);
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Test
	public void test17(){
		/**
		 * 基于DTO的实现
		 * 特别注意，这里必须使用  as
		 */
		Session session = null;
		try {
			session = HibernateUtil.getSession();
			session.beginTransaction();
			/**
			 *查询每个学生所在的班级和所在的专业
			 */
			List<StudentDto> stus = session
					.createQuery("select new org.com.test.model.StudentDto"
							+ "(stu.id as stuId,stu.name as stuName,stu.sex as sex,cla.name as claName,spe.name as speName) "
							+ "from Student stu left join stu.classroom cla "
							+ " left join cla.special spe ")
					.list();
			for (StudentDto stu : stus) {
				System.out.println(stu.getStuId()+";"+stu.getStuName()+
						";"+stu.getSex()+";"+stu.getClaName()+";"+stu.getSpeName());
			}
			session.getTransaction().commit();
		} catch (Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			HibernateUtil.closeSession(session);
		}
	}
	
}

package org.com.test.model;
/**
 * DTO对象没有任何存储的意义，仅仅是用来进行数据的传输的。
 * 特别注意：这样的对象，必须有全部字段的构造方法和不带参数的构造方法。
 * @author asus_n56
 *
 */
public class StudentDto {
	private int stuId;
	private String stuName;
	private String sex;
	private String claName;
	private String speName;
	
	
	public int getStuId() {
		return stuId;
	}
	public void setStuId(int stuId) {
		this.stuId = stuId;
	}
	public String getStuName() {
		return stuName;
	}
	public void setStuName(String stuName) {
		this.stuName = stuName;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getClaName() {
		return claName;
	}
	public void setClaName(String claName) {
		this.claName = claName;
	}
	public String getSpeName() {
		return speName;
	}
	public void setSpeName(String speName) {
		this.speName = speName;
	}
	
	
	public StudentDto(int stuId, String stuName, String sex, String claName, String speName) {
		super();
		this.stuId = stuId;
		this.stuName = stuName;
		this.sex = sex;
		this.claName = claName;
		this.speName = speName;
	}
	public StudentDto() {
	}
	
	
}