①查询所有的课程对象 hql=" from Course";
②查询所有课程对象,并按课程号进行升降序排序 hql=" from Course as c order by c.courseId desc";
③检索课程的部分属性 hql=" select c.coursename from Course as c";
HQL中的聚集函数(count )
①检索课程中的所有记录数 hql=" select count(b) from Books b";
②检索课程表中价格平均价格 hql=" select avg(b.price )from Books b ";
③检索课程表中价格的最大值和最小值 hql=" select min(b.price),max(b.price) from Books b";
④分组查询以出版社为分组为依据,获取图书对象 hql=" select b.publishers.name,count ( *) from Books b group by b.publishers";
⑤分页查询 hql=" from Books b ordey by b.booksId asc ";
Query query = session.createQuery(hql);
// 从第一个对象开始查询
query.setFirstResult(0);
// 从查询结果中一次返回3个对象
query.setMaxResults(3);
// 执行查询
List list = query.list();
// 遍历返回结果
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
Books books = (Books) iterator.next();
System.out.println(books.getTitle() + " " + books.getAuthor());
}
}
⑥条件查询 hql1 = "from Books books where books.title like ?";
Query query = session.createQuery(hql1);
// 给HQL语句中的“?”代表的参数赋值
query.setString(0, "%计算机%");
List list = query.list();
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
Books books = (Books) iterator.next();
System.out.println(books.getTitle() + " " + books.getAuthor());
}
}
总代码如下
package com.hibernate.hql;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.junit.Test;
import com.hibtes2.HibernateSessionFactory;
import com.hibtest2.entitymanytoone.Books;
import com.hibtest2.entitymanytoone.Publishers;
import com.hibtest2.entityonetooneunique.Course;
public class TestHql {
// 查询所有的course对象
@SuppressWarnings("rawtypes")
@Test
public void test() {
// 获取session
Session session = HibernateSessionFactory.getSession();
// 编写hql语句
String hql = "from Course";
// 创建Query对象
Query query = session.createQuery(hql);
// 执行查询,获得结果
List list = query.list();
// 遍历查找结果
Iterator itor = list.iterator();
while (itor.hasNext()) {
Course course = (Course) itor.next();
System.out.println(course.getCourseId() + ""
+ course.getCoursename());
}
}
// 对上述对象进行一个排序,desc降序,asc是升序,默认是升序
@SuppressWarnings("rawtypes")
@Test
public void testdatadesc() {
// 获取session
Session session = HibernateSessionFactory.getSession();
// 创建HQL
String hql = "from Course as b order by b.courseId desc";
// 创建Query
Query query = session.createQuery(hql);
// 执行查询,获得结果
List list = query.list();
// 遍历查询结果,
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
Course course = (Course) iterator.next();
System.out.println(course.getCourseId() + ":"
+ course.getCoursename());
}
}
// 检索课程的部分属性
@SuppressWarnings("rawtypes")
@Test
public void testmanydate() {
Session session = HibernateSessionFactory.getSession();
String hql = "select c.coursename from Course as c ";
Query query = session.createQuery(hql);
List list = query.list();
Iterator iterator = list.iterator();
// 每条记录封装成一个Object对象
while (iterator.hasNext()) {
Object[] object = (Object[]) iterator.next();
System.out.println(object[0]);
}
}
// 测试HQL中的聚集函数,统计记录数,计算最小值,最大值,计算和,计算平均值
@Test
public void testfunction() {
Session session = HibernateSessionFactory.getSession();
// 统计记录总数
String hql1 = "select count(b) from Books b";
Query query = session.createQuery(hql1);
Long count = (Long) query.uniqueResult();
// 统计书的平均价格
String hql2 = "select avg(b.price) from Books b";
Query query2 = session.createQuery(hql2);
Double monery = (Double) query2.uniqueResult();
// 统计最贵的和最便宜的书
String hql3 = "select min(b.price),max(b.price) from Books b";
Query query3 = session.createQuery(hql3);
Object[] objects = (Object[]) query3.uniqueResult();
System.out.println("记录总数" + " " + count.toString() + " " + "平均价格"
+ monery.toString() + " " + "书价格最低为" + objects[0].toString()
+ " " + "书价格最高为" + objects[1].toString());
}
// 测试分组查询,以出版社为依据分组,将图书进行分类
@SuppressWarnings("rawtypes")
@Test
public void testgroup() {
Session session = HibernateSessionFactory.getSession();
// 统计总的记录数
String hql1 = "select b.publishers.name,count(*) from Books b group by b.publishers";
Query query = session.createQuery(hql1);
List list = query.list();
Iterator iterator = list.iterator();
// 每条记录封装成一个object数组
while (iterator.hasNext()) {
Object[] object = (Object[]) iterator.next();
System.out.println("出版社:" + object[0] + ",图书总数为:" + object[1]);
}
}
// 测试分页查询
@SuppressWarnings("rawtypes")
@Test
public void testfenye() {
Session session = HibernateSessionFactory.getSession();
String hql = "from Books b order by b.booksId asc";
Query query = session.createQuery(hql);
// 从第一个对象开始查询
query.setFirstResult(0);
// 从查询结果中一次返回3个对象
query.setMaxResults(3);
// 执行查询
List list = query.list();
// 遍历返回结果
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
Books books = (Books) iterator.next();
System.out.println(books.getTitle() + " " + books.getAuthor());
}
}
// 测试条件查询
@SuppressWarnings("rawtypes")
@Test
public void testwhere() {
Session session = HibernateSessionFactory.getSession();
String hql1 = "from Books books where books.title like ?";
Query query = session.createQuery(hql1);
// 给HQL语句中的“?”代表的参数赋值
query.setString(0, "%计算机%");
List list = query.list();
Iterator iterator = list.iterator();
while (iterator.hasNext()) {
Books books = (Books) iterator.next();
System.out.println(books.getTitle() + " " + books.getAuthor());
}
}
//测试按参数名字进行条件查询
@SuppressWarnings("rawtypes")
@Test
public void testparament(){
Session session=HibernateSessionFactory.getSession();
//通过“:booktitle”定义命名参数
String hql1="from Books books where books.title=:booktitle";
Query query=session.createQuery(hql1);
//给参数设置值
query.setString("booktitle", "数据结构");
List list=query.list();
Iterator iterator=list.iterator();
while (iterator.hasNext()){
Books books=(Books) iterator.next();
System.out.println(books.getTitle()+" "+books.getAuthor()+" "+books.getPublishers().getName());
}
}
//测试HQL连接查询中的内连接
@SuppressWarnings("rawtypes")
@Test
public void testneiconnect(){
Session session=HibernateSessionFactory.getSession();
//编写HQL语句,使用内连接查询
String hql1="from Books b inner join b.publishers p where p.name='清华大学出版社'";
Query query=session.createQuery(hql1);
List list=query.list();
Iterator iterator=list.iterator();
Object [] object=null;
Books books=null;
Publishers publishers=null;
while(iterator.hasNext()){
object=(Object[]) iterator.next();
books=(Books) object[0];
publishers=(Publishers) object[1];
System.out.println("书名:"+books.getTitle()+" 作者:"+books.getAuthor()+" 出版社:"+publishers.getName());
}
}
}