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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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);}}@Testpublic 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);}}@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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")@Testpublic 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() {}}