- 什么是hql
HQL是Hibernate Query Language的缩写
HQL | SQL |
---|---|
类名/属性 | 表名/列名 |
区分大小写,关键字不区分大小写 | 不区分大小写 |
别名 | 别名 |
?,从下标0开始计算位置(hibernate5之后不支持) | 从顺序1开始计算位置 |
:命名参数 | 不支持:命名参数 |
面向对象的查询语言 | 面向结构查询 |
通用分页的演示
package com.hjc.five.test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.hjc.five.util.PageBean;
import com.hjc.four.dao.BookDao;
import com.hjc.four.entity.Book;
import com.zking.two.util.SessionFactoryUtils;
public class HqlTest {
private Session session;
private Transaction transaction;
private BookDao bookdao=new BookDao();
@Before
public void before() {
session = SessionFactoryUtils.openSession();
transaction = session.beginTransaction();
}
@After
public void after() {
transaction.commit();
session.close();
}
/**
* 返回对象(多个)
*/
@Test
public void testList1() {
Query query = session.createQuery("from Book");
List<Book> list = query.list();
for (Book b : list) {
System.out.println(b);
}
}
/**
* 返回单个列段 用字符串就可以接收
*/
@Test
public void testList2() {
Query query = session.createQuery("select b.bookName as ss from Book b");
List<String> list = query.list();
for (String b : list) {
System.out.println(b);
}
}
/**
* 查两个列段以上 默认返回object【】
*/
@Test
public void testList3() {
Query query = session.createQuery("select b.bookId,b.bookName as ss from Book b");
List<Object[]> list = query.list();
for (Object[] b : list) {
System.out.println(Arrays.toString(b));
}
}
/**
* 注意map是函数 所以不区分大小写 返回的是map集合
*/
@Test
public void testList4() {
Query query = session.createQuery("select new mAp(b.bookId,b.bookName) from Book b");
List<Map> list = query.list();
for (Map b : list) {
System.out.println(b);
}
}
/**
* 查两个列段以上 可返回对象 提前是有对应的函数
*/
@Test
public void testList5() {
Query query = session.createQuery("select new Book(b.bookId,b.bookName) from Book b");
List<Book> list = query.list();
for (Book b : list) {
System.out.println(b);
}
}
/**
* HQL支持占位符
*/
@Test
public void testList6() {
// Query query = session.createQuery("from Book where bookId = :bookId");
// query.setParameter("bookId", 1);
// Book b = (Book) query.getSingleResult();
// System.out.println(b);
Query query = session.createQuery("from Book where bookId in (:bookIds)");
// query.setParameterList("bookIds", new Integer[] {1,2,7});
List<Integer> params = new ArrayList<Integer>();
params.add(1);
params.add(2);
params.add(4);
query.setParameterList("bookIds", params);
List<Book> list = query.list();
for (Book b : list) {
System.out.println(b);
}
}
/**
* HQl支持连接查询
*/
@Test
public void testList7() {
Query query = session.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order");
List<Object[]> list = query.list();
for (Object[] b : list) {
System.out.println(Arrays.toString(b));
}
}
/**
* HQL支持聚合函数
*/
@Test
public void testList8() {
Query query = session.createQuery("select count(*) from Book");
Long singleResult = (Long) query.getSingleResult();
System.out.println(singleResult);
}
/**
* HQL分页
*/
@Test
public void testList9() {
Query query = session.createQuery("from Book");
query.setFirstResult(5);
query.setMaxResults(5);
List<Book> list = query.list();
for (Book b : list) {
System.out.println(b);
}
}
public void testList10() {
Book book=new Book();
PageBean pageBean=new PageBean();
List<Book> list=this.bookdao.list(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
}
}
* hql通用分页写法1.0
* 步骤:
* 1、动态拼接hql语句
* 2、需要给拼接的hql语句的命名参数赋值(可以简化)
* 3、分页(可以简化)
*/
public List<Book> list(Book book,PageBean pageBean){
Session session = SessionFactoryUtils.openSession();
String hql = "from Book where 1=1";
String bname = book.getBookName();
if(StringUtils.isNotBlank(bname)) {
hql +=" and bookName like :bookName";//命名参数
}
Query query = session.createQuery(hql);
if(StringUtils.isNotBlank(bname)) {
query.setParameter("bookName", bname);
}
if(pageBean !=null && pageBean.isPagination()) {
query.setFirstResult(pageBean.getStartIndex());//设置起始下标
query.setMaxResults(pageBean.getRows());//设置查询数量
}
List list = query.list();
session.close();
return list;
}
测试:
/**
* 测试hql通用分页 1.0
*/
@Test
public void testList10() {
Book book = new Book();
PageBean pageBean = new PageBean();
// pageBean.setRows(10);//设置每页显示数据行数
book.setBookName("%圣墟%");//带条件查询
List<Book> list = this.bookDao.list(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
}
升级版
package com.xwt.five.dao;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.hibernate.Session;
import org.hibernate.query.Query;
import com.xwt.five.uity.PageBean;
/**
* 基于hql语句的通用查询工具类
* 1、需要给拼接的hql语句的命名参数赋值
* 2、 分页
*
* 基于hql分页的查询
* 1、首先要查询出符合条件的总记录数
* 2、返回符合条件的记录
* @author wt
*
*/
public class BaseDao {
/**
* @param query 里面包含了带有命名参数的hq语句 需要等待赋值
* @param paMap
*/
public void setParam(Query query,Map<String, Object> paMap) {
if(paMap!=null && paMap.size()>0) {
Set<Entry<String, Object>> entrySet=paMap.entrySet();
for (Entry<String, Object> entry : entrySet) {
String key=entry.getKey();
Object value=entry.getValue();
if(value instanceof Object[]) {
//value可能是数组
query.setParameterList("bookIds", (Object[])value);
}else if(value instanceof Collection) {
//value可能是集合
query.setParameterList("bookIds", (Collection)value);
}else {
query.setParameter(key, value);
}
}
}
}
/**
* 拼装符合条件的总记录数
* @param hql
* @return
*/
public String getCountHql(String hql) {
int fromIndex=hql.toUpperCase().indexOf("FROM");
return "select count(*) "+hql.substring(fromIndex);
}
/**
* 基于hql的通用查询方法
* @param hql
* @param pageBean
* @param pMap
* @param session
* @return
*/
public List executeQuery(String hql,PageBean pageBean,Map<String, Object> pMap,Session session) {
List list=null;
Query query=null;
if(pMap!=null && pageBean.isPagination()) {
//首先要查询出符合条件的总记录数
String countHql=getCountHql(hql);
Query countQuery=session.createQuery(countHql);
this.setParam(countQuery, pMap);
pageBean.setTotal(countQuery.getSingleResult().toString());
//返回符合条件的记录
query=session.createQuery(hql);
this.setParam(countQuery, pMap);
query.setFirstResult(pageBean.getStartIndex());
query.setMaxResults(pageBean.getRows());
list=query.list();
}else {
query=session.createQuery(hql);
this.setParam(query, pMap);
list=query.list();
}
return list;
}
}
bookdao.java继承basedao.java
package com.xwt.five.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.hibernate.Session;
import org.hibernate.query.Query;
import com.xwt.five.uity.PageBean;
import com.xwt.five.uity.StringUtils;
import com.xwt.four.entity.Book;
import com.xwt.two.uity.SessionFactoryUtils;
public class BookDao extends BaseDao {
/**
* 基于hql的basedao的升级版
* @param book
* @param pageBean
* @return
*/
public List<Book> listPlus(Book book,PageBean pageBean){
Session session=SessionFactoryUtils.openSession();
/**
* 拼接hql语句
*/
String hql="from Book where 1=1";
String bname=book.getBookName();
//jsp自动传递过来的 不需要手写
Map<String, Object> map=new HashMap<>();
if(StringUtils.isNotBlank(bname)) {
hql+=" and bookName like :bookName";
}
Query query=session.createQuery(hql);
List list = super.executeQuery(hql, pageBean, map, session);
session.close();
return list;
}
}
测试:
@Test
public void testHql2() {
Book book=new Book();
PageBean pageBean=new PageBean();
pageBean.setPage(1);
//book.setBookName("%斗%");
List<Book> list=this.bookDao.list(book, pageBean);
for(Book b:list) {
System.out.println(b);
}
}
结果:
Book [bookId=8, bookName=我不, price=39.0]