一、hql
1. 什么是hql
HQL是Hibernate Query Language的缩写
查全部
2. hql和sql区别/异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?,从下标0开始计算位置(hibernate5之后不支持) ?,从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言
hqlTest
public class HqlTest { Session session=null; Transaction trans=null; BookDao bookDao=new BookDao(); // @Before // public void setUp() { // session=SessionFactoryUtils.openSession(); // trans=session.beginTransaction(); // } // // @After // public void tearDown() { // trans.commit(); // SessionFactoryUtils.closeSession(); // } @SuppressWarnings("deprecation") @Test public void test() { String hql=""; //一.HQL //1.HQL之查询返回List<T> // hql="from Book"; // Query<Book> createQuery = session.createQuery(hql, Book.class); // List<Book> list = createQuery.list(); // for (Book book : list) { // System.out.println(book); // } //2.HQL之查询返回单个属性 // hql="select b.bookName from Book b"; // Query query = session.createQuery(hql); // List list = query.list(); // for (Object object : list) { // System.out.println(object); // } //3.HQL之查询返回多个属性 // hql="select bookId,bookName from Book"; // Query createQuery = session.createQuery(hql); // List list = createQuery.list(); // for (Object object : list) { // //Object==object[] // System.out.println(Arrays.toString((Object[])object)); // } //4.HQL之new Map // hql="select new Map(b.bookId as bid, b.bookName as bname) from Book b"; // Query query = session.createQuery(hql); // List<Map<String, Object>> list = query.list(); // for (Map<String, Object> map : list) { // System.out.println(map); // } // 5.HQL之new 构造方法 // hql="select new Book(b.bookId,b.bookName,b.price) from Book b "; // Query createQuery = session.createQuery(hql); // List list = createQuery.list(); // for (Object object : list) { // System.out.println(object); // } // 6.HQL之命名参数 //hql="from Book where price>:min and price<:max"; // hql="from Book where bookId in (:ids)"; // Query<Book> query = session.createQuery(hql, Book.class); // query.setParameter("min", 40f); // query.setParameter("max", 1000f); // query.setParameterList("ids",new Integer[] {1,2,3}); // List<Book> list = query.list(); // for (Book book : list) { // System.out.println(book); // } //7.HQL之聚合函数(count/sum/avg/min/max) // hql="select sum(b.price) as total from Book b"; // Query createQuery = session.createQuery(hql); // List list = createQuery.list(); // for (Object object : list) { // System.out.println(object); // } // 8.HQL之连接查询 // hql="select b.bookId,b.bookName,b.price" // + ",c.categoryId,c.categoryName from Book b inner join " // + "b.categroies c"; // Query query = session.createQuery(hql); // List list = query.list(); // for (Object object : list) { // System.out.println(Arrays.toString((Object[])object)); // } // 9.HQL之分页查询 // int page=1; // int rows=2; // // hql="from Book"; // Query<Book> query = session.createQuery(hql, Book.class); // query.setFirstResult((page-1)* rows); // query.setMaxResults(rows); // List<Book> list = query.list(); // for (Book book : list) { // System.out.println(book); // } //二.原生sql //1.原生sql查询返回Object[] String sql=""; // sql="select book_id,book_name,price from t_book_hb"; // NativeQuery query = session.createNativeQuery(sql); // List list = query.list(); // for (Object object : list) { // System.out.println(Arrays.toString((Object[])object)); // } //2.原生sql返回list<T> // sql="select book_id,book_name,price from t_book_hb"; // NativeQuery<Book> query = session.createNativeQuery(sql, Book.class); // List<Book> list = query.list(); // for (Book book : list) { // System.out.println(book); // } //3.原生sql查询返回单个属性 // sql="select book_name from t_book_hb"; // NativeQuery query = session.createNativeQuery(sql); // List list = query.list(); // for (Object object : list) { // System.out.println(object); // } //4.原生sql查询返回List<Map>,适用于多表联查返回结果 sql="SELECT * FROM t_book_hb b,t_book_category_hb bc,t_category_hb c " + "WHERE b.`book_id`=bc.`bid` AND bc.`cid`=c.`category_id`"; NativeQuery query = session.createNativeQuery(sql); //将结果集转换为Map集合 query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP); List<Map<String,Object>> list= query.list(); for (Map<String,Object> map : list) { System.out.println(map); } }
BaseDao
public Session getSession() { return SessionFactoryUtils.openSession(); } /** * 设置Query的查询参数 * @param params * @param query * @return */ public void setParamters(Map<String,Object> params,Query query) { if(null==params||0==params.size()) return; Set<Entry<String, Object>> set = params.entrySet(); String name=null; Object value=null; for (Entry<String, Object> entry : set) { name=entry.getKey(); value=entry.getValue(); //判断参数是否是Collection,一般用于List/Set集合参数时使用 if(value instanceof Collection) query.setParameterList(name, (Collection)value); //判断参数是否是Object[] else if(value instanceof Object[]) query.setParameterList(name, (Object[])value); else query.setParameter(name, value); } } /** * 将普通hql语句转换成查询总记录数的hql语句 * @param hql * @return */ public String countSql(String hql) { //from Book //select * from Book int start=hql.toUpperCase().indexOf("FROM"); return "select count(1) "+hql.substring(start); } /** * 查询(支持分页) * @param hql 普通hql语句 * @param params 请求参数 * @param pageBean 分页对象 * @return */ @SuppressWarnings("rawtypes") public List executeQuery(String hql,Map<String,Object> params,PageBean pageBean) { Session session=getSession(); Query query=null; //1.根据满足条件查询总记录数 if(null!=pageBean&&pageBean.isPagination()) { //select count(1) from Book where String countHql=this.countSql(hql); query = session.createQuery(countHql); this.setParamters(params, query); } query=session.createQuery(hql); //2.根据满足条件查询分页记录 if(null!=pageBean&&pageBean.isPagination()) { query.setFirstResult(pageBean.getStartIndex()); query.setMaxResults(pageBean.getRows()); } this.setParamters(params, query); return query.list(); }
BookDao
public class BookDao extends BaseDao{ public void addBook(Book book) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); //保存 session.save(book); transaction.commit(); SessionFactoryUtils.closeSession(); } public Book get(Book book) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); //保存 Book b = session.get(Book.class, book.getBookId()); if(b!=null) { Hibernate.initialize(b.getCategroies()); } transaction.commit(); SessionFactoryUtils.closeSession(); return b; } //被控方 public void delBook(Book book) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); Book b = session.get(Book.class, book.getBookId()); if(null!=b) { //解除关联关系 Set<Category> categroies = b.getCategroies(); for (Category category : categroies) { b.getCategroies().remove(category); } session.delete(b); } transaction.commit(); SessionFactoryUtils.closeSession(); } /***************************通用查询***************************/ public List<Book> list1(Book book,PageBean pageBean) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); //下面代码处理的是book实体类的条件查询 String bookName= book.getBookName(); //sql语句where后面可以直接写true,而hql不能出现true String hql= "from Book where 1=1"; if(StringUtils.isNotBlank(bookName)) { hql +=" and bookName like :bookName"; } Query query=session.createQuery(hql); if(StringUtils.isNotBlank(bookName)) { query.setParameter("bookName",bookName); } //处理的是分页 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<Book> list2(Book book, PageBean pageBean) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); // 下面的代码处理的是book实体类的条件查询 String bookName = book.getBookName(); Map<String , Object> map = new HashMap<String, Object>(); // sql语句where后面可以直接写true,而hql语句不能出现true String hql = "from Book where 1=1"; if(StringUtils.isNotBlank(bookName)) { hql += " and bookName like :bookName"; map.put("bookName", bookName); } List list = super.executeQuery(hql, map, pageBean); transaction.commit(); session.close(); return list; } }
测试
@Test public void testList1() { Book book = new Book(); // book.setBookName("T226"); PageBean pageBean = new PageBean(); pageBean.setPage(2); List<Book> list1 = this.bookDao.list1(book, pageBean); for (Book b : list1) { System.out.println(b); } }
@Test public void testList2() { Book book = new Book(); book.setBookName("%西游记%"); PageBean pageBean = new PageBean(); // pageBean.setPage(2); List<Book> list1 = bookDao.list2(book, pageBean); for (Book b : list1) { System.out.println(b); } }