一、简单属性查询
单一属性:
//返回结果集属性列表,元素类型和实体类中的属性类型一致
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]);
}