HQL
定义
hql与sql的区别
| hql | sql |
|---|---|
| 面向对象 | 面向结构 |
| 操作的是类 ，类属性 | 表，表列段 |
| 命名参数占位 | 占位符 |
hql的几种返回类型
- 返回对象 from 类名
- 返回String 查询单个属性
- object [] 查询两个及两个以上的属性
- Map 利用了Map函数，查询两个及两个以上的类属性
- 返回对象 利用了构造函数的形式
案例
Book
package com.wxm.four.entity;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
public class Book implements Serializable{
private Integer bookId;
private String bookName;
private Float price;
private Set<Category> categories = new HashSet<Category>();
private Integer initCategories = 0;
public Integer getInitCategories() {
return initCategories;
}
public void setInitCategories(Integer initCategories) {
this.initCategories = initCategories;
}
public Integer getBookId() {
return bookId;
}
public void setBookId(Integer bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public Set<Category> getCategories() {
return categories;
}
public void setCategories(Set<Category> categories) {
this.categories = categories;
}
@Override
public String toString() {
return "Book [bookId=" + bookId + ", bookName=" + bookName + ", price=" + price + "]";
}
public Book(Integer bookId, String bookName) {
super();
this.bookId = bookId;
this.bookName = bookName;
}
public Book() {
super();
}
}
BookDao
package com.wxm.four.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import com.wxm.five.dao.BaseDao;
import com.wxm.five.dao.PageBean;
import com.wxm.five.dao.StringUtils;
import com.wxm.four.entity.Book;
import com.wxm.four.entity.Category;
import com.wxm.two.util.SessionFactoryUtils;
public class BookDao extends BaseDao {
public Integer addBook(Book book) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
Integer bid = (Integer) session.save(book);
transaction.commit();
session.close();
return bid;
}
public Integer addCategory(Category category) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
Integer cid = (Integer) session.save(category);
transaction.commit();
session.close();
return cid;
}
public Category getCategory(Category category) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
Category c = session.get(Category.class, category.getCategoryId());
transaction.commit();
session.close();
return c;
}
public Book getBook(Book book) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
Book b = session.get(Book.class, book.getBookId());
if (b != null && new Integer(1).equals(book.getInitCategories())) {
Hibernate.initialize(b.getCategories());
}
transaction.commit();
session.close();
return b;
}
public void delBook(Book book) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
session.delete(book);
transaction.commit();
session.close();
}
public void delCategory(Category category) {
Session session = SessionFactoryUtils.openSession();
Transaction transaction = session.beginTransaction();
Category c = session.get(Category.class, category.getCategoryId());
if (c != null) {
for (Book b : c.getBooks()) {
// 通过在被控方通过主控方来解除关联关系，最后被控方再做删除
b.getCategories().remove(c);
}
}
session.delete(c);
transaction.commit();
session.close();
}
/**
* 书籍的分页查询
*
* 1.动态的拼接hql语句 2.需要给拼接的hql语句的命名参数赋值(可以简化) 3.分页
*
* @param book
* @param pageBean
* @return
*/
public List<Book> list(Book book, PageBean pageBean) {
Session session = SessionFactoryUtils.openSession();
// Transaction beginTransaction = session.beginTransaction();
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;
}
HqlTest
package com.wxm.five.test;
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.wxm.five.dao.PageBean;
import com.wxm.four.dao.BookDao;
import com.wxm.four.entity.Book;
import com.wxm.two.util.SessionFactoryUtils;
public class HqlTest {
private Session session;
private Transaction transaction;
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,4});
// 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(2);
query.setMaxResults(3);
List<Book> list = query.list();
for (Book b : list) {
System.out.println(b);
}
}
@Test
public void testList10() {
Book book=new Book();
PageBean pageBean =new PageBean();
// pageBean.setPagination(true);
book.setBookName("%圣墟%");
List<Book> list=this.bookDao.list(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
}
/**
*继承了basedao后
*/
@Test
public void testList11() {
Book book=new Book();
PageBean pageBean =new PageBean();
// pageBean.setPagination(false);
book.setBookName("%圣墟%");
List<Book> list=this.bookDao.listPlus(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
}
}
问题：每增加一个查询就需要写一个这种语句，如果数量过多的话，导致重复代码过多。因此我们封装了baseDao类
BaseDao
package com.wxm.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;
/**
* 基于hql语句的查询工具类
*
* 1.动态的拼接hql语句 2.需要给拼接的hql语句的命名参数赋值(可以简化) 3.分页
*
* 基于sql的分页的查询的做法 1.首先要查询出符合条件的总记录数 2.返回符合条件的记录
*
*
* 参数:sql,pagebean,Class clz sql=select * from t_hibernate_book where bookName
* like "%圣墟%"
*
*
* 1.countSql=select count(*)form (sql)t 2.pageSql=sql+" limit "+start+","+rows
*
* 基于hql的分页的查询的做法 hql=from Book where bookName like "%圣墟%" hql=select * from
* Book where bookName like "%圣墟%"
*
* 1.countHql=select count(*) hql;
*
*/
public class BaseDao {
/**
*
*
* @param query
* 里面的包含了带有命名参数hql语句,需要等待赋值 from Book where 1=1 ";
* @param parameMap
* =request.getParameterMap();
*/
public void setParam(Query query, Map<String, Object> paramMap) {
if (paramMap != null && paramMap.size() > 0) {
Set<Entry<String, Object>> entrySet = paramMap.entrySet();
for (Entry<String, Object> entry : entrySet) {
// 给query赋值
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) {
//indexOf() 取 FROM 的下标
int fromIndex = hql.toUpperCase().indexOf("FROM");
return "select count(*) " + hql.substring(fromIndex);
}
/**
* 基于hql的通用查询方法
*
* @param hql
* @param pageBean
* @param paMap
* @param session
* @return
*/
public List excuteQuery(String hql, PageBean pageBean, Map<String, Object> paMap, Session session) {
List list = null;
Query query = null;
if (pageBean != null && pageBean.isPagination()) {
// 1.首先要查询出符合条件的总记录数
String countHql = getCountHql(hql);
Query countQuery = session.createQuery(countHql);
this.setParam(countQuery, paMap);
//将总行数放入pageBean对象
pageBean.setTotal(countQuery.getSingleResult().toString());
// 2.返回符合条件的记录
query = session.createQuery(hql);
//给预定于的hql语句的命名参数赋值。有多少赋多少
this.setParam(query, paMap);
//设置开始位置
query.setFirstResult(pageBean.getStartIndex());
//这是偏移量，就是一页展示几条数据
query.setMaxResults(pageBean.getRows());
list = query.list();
} else {
query = session.createQuery(hql);
this.setParam(query, paMap);
list = query.list();
}
return list;
}
}
BookDao
/**
* 基于hql 继承了basedao的BookDao
* @param book
* @param pageBean
* @return
*/
public List<Book> listPlus(Book book, PageBean pageBean) {
Session session = SessionFactoryUtils.openSession();
String hql = "from Book where 1=1 ";
String bname = book.getBookName();
// 这是jsp自动传递过来的,不需要手写,这里模拟一下
Map<String, Object> map = new HashMap<String, Object>();
if (StringUtils.isNotBlank(bname)) {
hql += " and bookName like :bookName";
map.put("bookName", bname);
}
Query query = session.createQuery(hql);
List list = super.excuteQuery(hql, pageBean, map, session);
session.close();
return list;
}
HqlTest
/**
*继承了basedao后
*/
@Test
public void testList11() {
Book book=new Book();
PageBean pageBean =new PageBean();
// pageBean.setPagination(false);
book.setBookName("%圣墟%");
List<Book> list=this.bookDao.listPlus(book, pageBean);
for (Book b : list) {
System.out.println(b);
}
}
效果一样，省了很多代码
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
