Hibernate之HQL

一、HQL入门 1、HQL的含义:

HQL是Hibernate Query Language（专属于hibernate框架的一个查询语言）的缩写

2. hql和sql区别/异同

HQL SQL
类名/属性表名/列名
区分大小写,关键字不区分大小写不区分大小写
别名别名
?，从下标0开始计算位置(hibernate5之后不支持) ?，从顺序1开始计算位置
:命名参数不支持:命名参数
面向对象的查询语言面向结构查询语言

例： sql：select name from t_mvc_book where name like ?；

hql：from Book where name like :bookName

3、处理返回的结果集

1.单个对象—— select没有逗号
2.Object[]—— b.bookId, b.bookName
3.Map——new Map(b.bookId as bid, b.bookName as bname)
4.new 构造方法(attr1,attr2) String/long——new Book(b.bookId, b.price) 单个列段

二.hql语法基础

hql的返回值：对象、object[]（数组）、集合、String/long（字符串）

第一步： HqlTest

package com.pjl.three.hql;

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.pjl.two.entity.Book;
import com.pjl.two.util.SessionFactoryUtil;



public class HqlTest {
	private Session session;
	private Transaction transaction;
	
	@Before
	public void before() {
		session = SessionFactoryUtil.getSession();
		transaction = session.beginTransaction();
	}
	
	@After
	public void after() {
		transaction.commit();
		session.close();
	}
	
	
	@Test
	public void testList1() {
		Query query = session.createQuery("from Book");
		List 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 list = query.list();
		for (String b : list) {
			System.out.println(b);
		}
	}
	
	
	@Test
	public void testList3() {
		Query query = session.createQuery("select b.bookId,b.bookName as ss from Book b");
		List list = query.list();
		for (Object[] b : list) {
			System.out.println(Arrays.toString(b));
		}
	}
	
	
	@Test
	public void testList4() {
		Query query = session.createQuery("select new mAp(b.bookId,b.bookName) from Book b");
		List 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 list = query.list();
		for (Book b : list) {
			System.out.println(b);
		}
	}
	
	
	@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 params = new ArrayList();
//		params.add(1);
//		params.add(2);
//		params.add(4);
//		query.setParameterList("bookIds", params);
		List list = query.list();
		for (Book b : list) {
			System.out.println(b);
		}
	}
	
	
	@Test
	public void testList7() {
		Query query = session.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order");
		List list = query.list();
		for (Object[] b : list) {
			System.out.println(Arrays.toString(b));
		}
	}
	
	
	@Test
	public void testList8() {
		Query query = session.createQuery("select count(*) from Book");
		Long singleResult = (Long) query.getSingleResult();
		System.out.println(singleResult);
	}
	
	
	@Test
	public void testList9() {
		Query query = session.createQuery("from Book");
		query.setFirstResult(2);
		query.setMaxResults(3);
		List list = query.list();
		for (Book b : list) {
			System.out.println(b);
		}
	}
}

第二步：测试

	@Test
	public void testList1() {
		Query query = session.createQuery("from Book");
		List 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 list = query.list();
       for (String b : list) {
           System.out.println(b);
       }
   }

   @Test
   public void testList3() {
       Query query = session.createQuery("select b.bookId,b.bookName as ss from Book b");
       List list = query.list();
       for (Object[] b : list) {
           System.out.println(Arrays.toString(b));
       }
   }

   @Test
   public void testList4() {
       Query query = session.createQuery("select new mAp(b.bookId,b.bookName) from Book b");
       List 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 list = query.list();
       for (Book b : list) {
           System.out.println(b);
       }
   }

   @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 params = new ArrayList();
//       params.add(1);
//       params.add(2);
//       params.add(4);
//       query.setParameterList("bookIds", params);
       List list = query.list();
       for (Book b : list) {
           System.out.println(b);
       }
   }

   @Test
   public void testList7() {
       Query query = session.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order");
       List list = query.list();
       for (Object[] b : list) {
           System.out.println(Arrays.toString(b));
       }
   }

   @Test
   public void testList8() {
       Query query = session.createQuery("select count(*) from Book");
       Long singleResult = (Long) query.getSingleResult();
       System.out.println(singleResult);
   }

   @Test
   public void testList9() {
       Query query = session.createQuery("from Book");
       query.setFirstResult(2);
       query.setMaxResults(3);
       List list = query.list();
       for (Book b : list) {
           System.out.println(b);
       }
   }

二、Hibernate的baseDao

1. baseDAO
需求：
按名字分页查询对应书籍信息

2. 原生sql
hql实现不了的功能，可以考虑使用原生sql
1、多表（5+）联查
2、未配置映射文件中关系

3. 视图映射

场景
select * from 3表联查

自定义mvc的baseDao思路：

1.完成一个基础版本的查询方法

2.分析共同之处（代码重复、思想重复）

3.解决方案，代码重复的抽取到父类，思想重复的用反射

难点：通用分页的处理；思想重复的用反射

第一步：完成一个基础版本的查询方法

public void testList10() {
        String hql="from Book where 1=1";
        Book book=new Book();
        if(book.getBookId() !=0) {
            hql+=" and bookId = :bookId";
        }
        if(book.getBookName() !=null) {
            hql+=" and bookName =: bookName";
        }
        Query query = session.createQuery(hql);
        if(book.getBookId() !=0) {
            query.setParameter("bookId", 1);
        }
        if(book.getBookName() !=null) {
            query.setParameter("bookName", "%圣墟%");
        }
        Book b = (Book) query.getSingleResult();
        System.out.println(b);
        }

第二步：分析共同之处

①、都有非空判断

②、都有query对象

③、具体值从对象/map

④、都需要分页

query.setFirstResult(2);//起始下标
query.setMaxResults(3);//偏移量

第三步：baseDao

package com.pjl.three.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.pjl.three.hql.PageBean;




public class baseDao {

	
	public void setParam(Map map, Query query) {
		if (map != null && map.size() > 0) {
			Object value = null;
			Set> entrySet = map.entrySet();
			for (Entry entry : entrySet) {
				// 例子的圣墟，但是有的时候它并不是单纯的字符串，可能是数组比如爱好，也可能是集合
				value = entry.getValue();
				if (value instanceof Object[]) {
					query.setParameterList(entry.getKey(), (Object[]) value);
				} else if (value instanceof Collection) {
					query.setParameterList(entry.getKey(), (Collection) value);
				} else {
					query.setParameter(entry.getKey(), value);
				}
			}
		}
	}

	public String getCountHql(String hql) {
		// hql = "from Book where bookName like :bookName"
		// * hql = "select * from new Book(bid,bookName) where bookName like :bookName"
		int index = hql.toUpperCase().indexOf("FROM");
		return "select count(*) " + hql.substring(index);
	}
	
	
	public List executeQuery(Session session,Map map,String hql,PageBean pageBean) {
		List list = null;
		if(pageBean != null && pageBean.isPagination()) {
			String countHql = getCountHql(hql);
			Query countQuery = session.createQuery(countHql);
			this.setParam(map, countQuery);
			pageBean.setTotal(countQuery.getSingleResult().toString());
			
			Query query = session.createQuery(hql);
//			给预定义hql语句执行对象中的参数赋值，有多少赋值多少
			this.setParam(map, query);
			query.setFirstResult(pageBean.getStartIndex());
			query.setMaxResults(pageBean.getRows());
			list = query.list();
		}else {
			Query query = session.createQuery(hql);
//			给预定义hql语句执行对象中的参数赋值，有多少赋值多少
			this.setParam(map, query);
			list = query.list();
		}
		return list;
	}

}

第一步：原生sql分页

	public List list(Book book, PageBean pageBean) {
		Session session = SessionFactoryUtil.getSession();
		Transaction transaction = session.beginTransaction();
		String hql = "from Book where 1 = 1";
 
		if (StringUtils.isNotBlank(book.getBookName())) {
			hql += " and bookName like :bookName";
		}
		Query query = session.createQuery(hql);
		
		if (StringUtils.isNotBlank(book.getBookName())) {
			query.setParameter("bookName", book.getBookName());
		}
 
		if (pageBean != null && pageBean.isPagination()) {
			query.setFirstResult(pageBean.getStartIndex());
			query.setMaxResults(pageBean.getRows());
		}
		List list = query.list();
		transaction.commit();
		session.close();
		return list;
	}

第四步：BookDao

package com.pjl.two.dao;
 
import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.pjl.two.entity.Book;
import com.pjl.two.entity.Category;
import com.pjl.two.util.SessionFactoryUtil;
 

 
 
public class BookDao {
	
	
	
	
//	public List list(Book book, PageBean pageBean) {
//		Session session = SessionFactoryUtil.getSession();
//		Transaction transaction = session.beginTransaction();
//		String hql = "from Book where 1 = 1";
//
//		if (StringUtils.isNotBlank(book.getBookName())) {
//			hql += " and bookName like :bookName";
//		}
//
//		Query query = session.createQuery(hql);
//		
//		if (StringUtils.isNotBlank(book.getBookName())) {
//			query.setParameter("bookName", book.getBookName());
//		}
//
//		if (pageBean != null && pageBean.isPagination()) {
//			query.setFirstResult(pageBean.getStartIndex());
//			query.setMaxResults(pageBean.getRows());
//		}
//		List list = query.list();
//		transaction.commit();
//		session.close();
//		return list;
//
//	}
	
	
//	public List list2(Book book, PageBean pageBean) {
//		Session session = SessionFactoryUtil.getSession();
//		Transaction transaction = session.beginTransaction();
//		String hql = "from Book where 1 = 1";
//		Map map = new HashMap();
//
//		if (StringUtils.isNotBlank(book.getBookName())) {
//			hql += " and bookName like :bookName";
//			map.put("bookName", book.getBookName());
//		}
//		List list = super.executeQuery(session, hql, map, pageBean);
//		transaction.commit();
//		session.close();
//		return list;
//	}
	
	
//	public List list3(Book book, PageBean pageBean) {
		String sql = "select b.*,o.* from t_hibernate_book b,t_hibernate_Order o";
//		String sql = "select * from t_hibernate_book";
//		Session session = SessionFactoryUtil.getSession();
//		Transaction transaction = session.beginTransaction();
//		List list = session.createSQLQuery(sql).list();
//		transaction.commit();
//		session.close();
//		return list;
//	}
}

    @Test

//    原生sql
    public void testList1() {
        PageBean pageBean=new PageBean();
        pageBean.setRows(3);
        Book book = new Book();
//        book.setBookName("%圣墟%");
        List list=this.bookDao.list3(book, pageBean);
        for (Book b : list) {
            System.out.println(b);
        }
    }

    @Test
//    使用baseDao
    public void testList2() {
        PageBean pageBean=new PageBean();
        pageBean.setRows(3);
        Book book = new Book();
//        book.setBookName("%圣墟%");
        List list=this.bookDao.list2(book, pageBean);
        for (Book b : list) {
            System.out.println(b);
        }
    }

     @Test
//    在hibernate中使用原生sql
    public void testList3() {
        PageBean pageBean=new PageBean();
        pageBean.setRows(3);
        Book book = new Book();
        List list=this.bookDao.list3(book, pageBean);
        for (Object[] object : list) {
            System.out.println(Arrays.toString(object));
        }
    }

Hibernate之HQL

Java相关栏目本月热门文章