hql : HQL是Hibernate Query Language的缩写
2. hql和sql区别/异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?,从下标0开始计算位置(hibernate5之后不支持) ?(站位符),从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言
注1:QuerySyntaxException:book is not mapped
代码演示:
/**
* 结果处理1:查询所有,结果返回对象
*/
public void getBookList1() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "from Book";
Query query = session.createQuery(hql);
List<Book> list = query.list();
transaction.commit();
for (Book book : list) {
System.out.println(book);
}
HibernateUtil.closeSession();
}
/**
* 结果处理二:查询数据库一列
* 数据库:是不区分大小写的
* hql:由于你查的的是实体类的属性,那就是区分大小写
*
* 2018年10月29日上午11:16:24
*/
public void getBookList2() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "select book_name as naw from Book";
Query query = session.createQuery(hql);
List<String> list = query.list();
transaction.commit();
for (String book : list) {
System.out.println(book);
}
HibernateUtil.closeSession();
}
/**
* 结果处理三:Object[]
* 在自定义mvc中entitybasedao
* 数据库 8列 ,实体类中 也是8列
*
* 如果查询的数据不是完整的,最好使用map集合
*
* 2018年10月29日上午11:35:25
*/
public void getBookList3() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "select book_name, book_id from Book";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
transaction.commit();
for (Object[] book : list) {
System.out.println(Arrays.toString(book));
}
HibernateUtil.closeSession();
}
/**
* 结果处理四:通过hibernate函数map去处理
* 函数这个词源于数据库
* 函数不区分大小写
*
* 2018年10月29日上午11:44:10
*/
public void getBookList4() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "select new map(book_name, book_id) from Book";
Query query = session.createQuery(hql);
List<Map<Object, Object>> list = query.list();
transaction.commit();
for (Map<Object, Object> book : list) {
System.out.println(book);
}
HibernateUtil.closeSession();
}
/**
* 结果处理五:通过构造方法进行查询
* 在实体类需要构造相对应的构造方法
*
* 2018年10月29日上午11:48:46
*/
public void getBookList5() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "select new Book(book_id, book_name) from Book";
Query query = session.createQuery(hql);
List<Book> list = query.list();
transaction.commit();
for (Book book : list) {
System.out.println(book);
}
HibernateUtil.closeSession();
}
/**
* hibernate的占位符问题
* 在hibernate 5 之后就已经弃用了 “?”,在五之前的使用率也不大,太过麻烦和不安全
*
* 2018年10月29日上午11:57:45
*/
public void getBookList6() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
// String hql = "from Book where book_id = :book_id";
String hql = "from Book where book_id in (:book_id)";
Query query = session.createQuery(hql);
// query.setParameter("book_id", 1);
query.setParameterList("book_id", new Integer[] {2,3});
transaction.commit();
// Book result = (Book) query.getSingleResult();
// System.out.println(result);
List list = query.list();
for (Object object : list) {
System.out.println(object);
}
HibernateUtil.closeSession();
}
/**
* 联表查询
*
* 2018年10月29日下午1:29:47
*/
public void getBookList7() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
// String hql = "select o.order_no,oi.product_id from Order o, OrderItem oi where o.order_id = oi.order.order_id";
String hql = "select o.order_no,oi.product_id from Order o, OrderItem oi where o = oi.order";
Query query = session.createQuery(hql);
transaction.commit();
List<Object[]> list = query.list();
for (Object[] object : list) {
System.out.println(Arrays.toString(object));
}
HibernateUtil.closeSession();
}
/**
* 聚合函数
*sum
*avg
*max
*min
*count
* 2018年10月29日下午1:30:49
*/
public void getBookList8() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "select count(*) from Book";
Query query = session.createQuery(hql);
transaction.commit();
Long result = (Long) query.getSingleResult();
System.out.println(result);
HibernateUtil.closeSession();
}
/**
* 分页
*
* 2018年10月29日下午1:35:54
*/
public void getBookList9() {
Session session = HibernateUtil.getCurrentSession();
Transaction transaction = session.beginTransaction();
String hql = "from Book";
Query query = session.createQuery(hql);
int page = 1;
int ofset = 3;
query.setFirstResult((page - 1) * ofset);
query.setMaxResults(ofset);
transaction.commit();
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
HibernateUtil.closeSession();
}