Hql简单使用

一、简单属性查询

单一属性：

                       //返回结果集属性列表，元素类型和实体类中的属性类型一致
			List students = session.createQuery("select name from Student").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				String name = (String)iter.next();
				System.out.println(name);
			}

多个属性，返回对象数组集合

                        //查询多个属性，返回对象数组集合
			//数组元素的类型与查询的属性类型一致
			//数组的长度与select中查询的属性个数一致
			List students = session.createQuery("select id, name from Student").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}

多个属性，返回实体对象

                        //可以使用hql返回Student对象
			//需要提供构造函数
			List students = session.createQuery("select new Student(id, name) from Student").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Student student = (Student)iter.next();
				System.out.println(student.getId() + ", " + student.getName());
			}

普通别名使用

                        // 可以使用别名
			List students = session.createQuery("select s.id, s.name from Student s").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Object[] obj = (Object[])iter.next();
				System.out.println(obj[0] + ", " + obj[1]);
			}

as命名别名

                Session session = null;
		try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			// 可以采用as命名别名
			List students = session.createQuery("select s.id, s.name from Student as s").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);
		}

二、实体对象

采用list查询：可忽略select（也可以加入别名 from Student s或者as作为别名 from Student as s）

                        //返回Student对象的集合
			//可以忽select关键字
			List students = session.createQuery("from Student").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}

list查询：使用select（另外不支持select * from 。。。）

                         //返回Student对象的集合
			//如果使用select查询实体对象，必须使用别名
			List students = session.createQuery("select s from Student s").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}

iterate查询：会出现N+1问题，即发出N+1条sql语句（发出一条查询id列表语句，根据id发出N条sql，加载相关对象），list查询可以避免，Iterate方法如果缓存中存在数据，它可以提高性能，否则出现N+1问题

   Iterator iter = session.createQuery("from Student").iterate();
			while (iter.hasNext()) {
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}

三、条件查询

拼串

	 List students = session.createQuery("select s.id, s.name from Student s where s.name like '%0%'").list();

使用？传递参数：索引从0开始，不同jdbc从1开始，值能使用单引号引起来（建议）

                         //例一、方法链编程，建议采用此种方式
			List students = session.createQuery("select s.id, s.name from Student s where s.name like ?")
					.setParameter(0, "%0%")
					.list();</span>

                       //例二、采用 ？方式，查询学号为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();

可以采用 :参数名 的方式传递参数

                       //例一、可以采用 :参数名 的方式传递参数
			List students = session.createQuery("select s.id, s.name from Student s where s.name like :myname")
					.setParameter("myname",  "%0%")
					.list();</span>

                       //例二、采用 :参数名 方式，查询学号为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();

查询2009-08的学生，可以调用mysql的日期格式化函数

        List students = session.createQuery("select s.id, s.name from Student s where date_format(s.createTime, '%Y-%m')=?")
			.setParameter(0,  "2009-08")
			.list();

查询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("2009-08-01 00:00:00"))
					.setParameter(1, sdf.parse("2009-08-20 23:59:59"))
					.list();

四、原生sql

List students = session.createSQLQuery("select * from t_student").list();

五、外置命名

     List students = session.getNamedQuery("queryStudent")
				.setParameter(0, 10)
				.list();

student.hbm.xml

<hibernate-mapping>
	<class name="com.bjpowernode.hibernate.Student" table="t_student">
		<id name="id">
			<generator class="native"/>
		</id>
		<property name="name"/>
		<property name="createTime"/>
		<many-to-one name="classes" column="classesid"/>
		<filter name="testFilter" condition="id < :myid"></filter>
	</class>
	
	<query name="queryStudent">
		<![CDATA[
			select s from Student s where s.id <?
		]]>
	</query>	
	
	<filter-def name="testFilter">
		<filter-param type="integer" name="myid"/>
	</filter-def>
</hibernate-mapping>

六、查询过滤器(参考五中hbm.xml中testFilter)

try {
			session = HibernateUtils.getSession();
			session.beginTransaction();
			session.enableFilter("testFilter")
					.setParameter("myid", 10);
			List students = session.createQuery("from Student").list();
			for (Iterator iter=students.iterator(); iter.hasNext();) {
				Student student = (Student)iter.next();
				System.out.println(student.getName());
			}
			session.getTransaction().commit();
		}

七、分页查询（从第一条结果开始，每页2条显示）

	List students = session.createQuery("from Student")
			.setFirstResult(1)
			.setMaxResults(2)						
			.list();

八、连接查询

完整连接：

List students = session.createQuery("select c.name, s.name from Student s join s.classes c")
						.list();

左连接：

List students = session.createQuery("select c.name, s.name from Student s left join s.classes c")
						.list();

右连接：

List students = session.createQuery("select c.name, s.name from Student s right join s.classes c")
						.list();

九、统计查询

一、Long count = (Long)session.createQuery("select count(*) from Student").uniqueResult();
			System.out.println("count=" + count);

二、String hql = "select c.name, count(s) from Classes c join c.students s group by c.name order by c.name";
			List students = session.createQuery(hql).list();
			for (int i=0; i<students.size(); i++) {
				Object[] obj = (Object[])students.get(i);
				System.out.println(obj[0] + ", " + obj[1]);
			}