Hibernate hql 续

来源：互联网发布：买电动车上淘宝编辑：程序博客网时间：2024/06/16 22:54

4、hibernate直接使用sql语句查询

Hibernate原生SQL语句查询@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();
//返回结果集属性列表,使用session的createSQLQuery方法
List list = session.createSQLQuery("select * from student").list();
for (Iterator iter=list.iterator(); iter.hasNext();) {
Object[] obj = (Object[])iter.next();
System.out.println(obj[0] + ", " + obj[1]);
}

tx.commit();
session.close();
}

5.分页查询【重要】
* setFirstResult(),从0开始
* setMaxResults(),每页显示的记录数

分页查询@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("from Student")
.setFirstResult(1) //从数据库第二条记录开始
.setMaxResults(5) //查询的记录数
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Student stu = (Student) iterator.next();
System.out.println(stu.getStuname());
}

tx.commit();
session.close();
}

6.对象导航查询【重要】

对象导航查询//查询某个班级的所有学生
@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("from Student s wheres.classes.classname like '%2%' ")
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Student student = (Student) iterator.next();
System.out.println(student.getStuid() + "------" + student.getStuname());
}

tx.commit();
session.close();
}

7.统计查询【重要】

统计查询//统计班级的人数
@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

// List list = session.createQuery("select count(*) from Student")
// .list();

//System.out.println(list.get(0));
Long count= (Long)session.createQuery("select count(*) from Student").uniqueResult();

System.out.println("总学生数："+ count);

tx.commit();
session.close();
}

//按班级名称分组并统计班级的人数
@Test
public void test2(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list= session.createQuery("select c.classname, count(s) from Classes c join c.students s group by c.classname order byc.classname").list();

for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Object [] obj = (Object []) iterator.next();
System.out.println("班级名称：" + obj[0] + "-------班级人数：" + obj[1]);
}

tx.commit();
session.close();
}

8.连接查询【重要】

*隐式连接
* 内连接
* 外连接（左连接/右连接）

连接查询//隐式连接
@Test
public void queryMyStudents() {

Session session = HibernateUitl.getSession();
Transaction tx = session.beginTransaction();
Team team = (Team) session.get(Team.class, 1);
// String hql = select stu from Student stu wherestu.team.teamname=:teamname";
String hql = "select stu from Student stu where stu.team=:team";
Query query = session.createQuery(hql);
query.setEntity("team", team);
List<Student> list = query.list();

for (Student stu : list) {
System.out.println("编号：" + stu.getStuid() + "--姓名："
+ stu.getStuname() + "--班级：" + stu.getTeam().getTeamname());

}

tx.commit();
session.close();
}
//内连接
@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("select c.classname, s.stuname from Student s join s.classes c")
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Object [] obj = (Object []) iterator.next();
System.out.println(obj[0] + "------" + obj[1]);
}

tx.commit();
session.close();
}

//左外连接
@Test
public void test2(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("select c.classname, s.stuname from Student s left join s.classes c")
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Object [] obj = (Object []) iterator.next();
System.out.println(obj[0] + "------" + obj[1]);
}

tx.commit();
session.close();
}

//左外连接
@Test
public void test3(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("select c.classname, s.stuname from Student s right join s.classes c")
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Object [] obj = (Object []) iterator.next();
System.out.println(obj[0] + "------" + obj[1]);
}

tx.commit();
session.close();
}
/****************************************
* 预先抓取获得学生
*/
@Test
public void queryMyStudents4() {

Session session = HibernateUitl.getSession();
Transaction tx = session.beginTransaction();
// String hql =
// "select stu from Student stu where stu.team.teamname=:teamname";
String hql = "select stu from Student stu inner join fetch stu.team";
Query query = session.createQuery(hql);
List<Student> list = query.list();

tx.commit();
session.close();
for (Student stu : list) {

System.out.println("编号：" + stu.getStuid() + "--姓名："
+ stu.getStuname() + "--班级：" + stu.getTeam().getTeamname());

}
}

9、外置命名查询
* 在映射文件中使用<query>标签来定义hql
* 在程序中使用session.getNamedQuery()方法得到hql查询串

Student.hbm.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="org.xiaolong.entity">
   <class name="Student" table="STUDENT" schema="SVSE">
   <id name="stuid" type="integer">
   <column name="STUID" precision="5" scale="0" />
   <generator class="native" />
   </id>
   <many-to-one name="classes" class="Classes" fetch="select">
   <column name="CLASSID" precision="5" scale="0" />
   </many-to-one>
   <property name="stuname" type="string">
   <column name="STUNAME" length="50" />
   </property>
   <property name="createtime" type="date">
   <column name="CREATETIME" length="7" />
   </property>

   
   <filter name="testFilter" condition="stuid < :myid"/>
   </class>

   
   <query name="queryStudent">
   <![CDATA[
   select s from Student s where s.stuid < ?
   ]]>
   </query>


   
   <filter-def name="testFilter">
   <filter-param name="myid" type="integer"/>
   </filter-def>
</hibernate-mapping>

外置命名查询 //使用外置命名查询，在映射文件中配置sql语句,放在Classes或者Student的映射文件里都可以
@Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.getNamedQuery("queryStudent")
.setParameter(0, 150)
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Student student = (Student) iterator.next();
System.out.println(student.getStuid() + "------" + student.getStuname());
}

tx.commit();
session.close();
}

//select s from Student s where s.stuid < ?
@Test
public void test2(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();

List list = session.createQuery("select s from Student s where s.stuid < ?")
.setParameter(0, 150)
.list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Student student = (Student) iterator.next();
System.out.println(student.getStuid() + "------" + student.getStuname());
}

tx.commit();
session.close();
}

10.查询过滤器
* 在映射文件中定义过滤器参数
* 在类的映射中使用过滤器参数
* 在程序中必须显示的启用过滤器，并且为过滤器参数赋值

查询过滤器 @Test
public void test1(){
Session session = HibernateUtil.getSession();
Transaction tx = session.beginTransaction();
//启用过滤器，并为参数赋值
session.enableFilter("testFilter").setParameter("myid", 150);

List list = session.createQuery("from Student").list();
for (Iterator iterator = list.iterator(); iterator.hasNext();) {
Student student = (Student) iterator.next();
System.out.println(student.getStuid() + "------" + student.getStuname());
}

tx.commit();
session.close();
}

到此结束！