HQL深入

1.1. 模糊查询

from Book where author like:author
query.setParameter (“author”,”%”+author+”%”);

代码：
public void findByHQL(){
Session session = HibernateUtils.openSession();
String hql = "from BookTypeEntity where typeName like :tn";
Query<BookTypeEntity> query = session.createQuery(hql, BookTypeEntity.class);
query.setParameter("tn","%人工%");
List<BookTypeEntity> resultList = query.getResultList();
for (BookTypeEntity bookTypeEntity : resultList) {
System.out.println(bookTypeEntity.getTypeName()+"\t"+bookTypeEntity.getTypeDesc());
}
session.close();
}

1.2. 条件查询
from Order where id > ?
session.createQuery(hql, Order.class).setParameter(0, 3);

代码：
public void findByHQL(){
Session session = HibernateUtils.openSession();
// String hql = "from BookTypeEntity where typeName like :tn";
String hql = "from BookTypeEntity where typeId > ?";//条件是ＰＯＪＯ中的属性名称，不是字段名称
Query<BookTypeEntity> query = session.createQuery(hql, BookTypeEntity.class);
// query.setParameter("tn","%人工%");
query.setParameter(0,29L);
List<BookTypeEntity> resultList = query.getResultList();
for (BookTypeEntity bookTypeEntity : resultList) {
System.out.println(bookTypeEntity.getTypeName()+"\t"+bookTypeEntity.getTypeDesc());
}
session.close();
}

1.3. HQL更新
update Order set createTime = ? where id = ?
query.executeUpdate();

代码：
public void testUpdateHql(){
Session session = HibernateUtils.openSession();
Transaction transaction = session.beginTransaction();
String hql = "update BookTypeEntity set typeDesc=? where typeId=?";
Query query = session.createQuery(hql);
query.setParameter(0,"提升自己");
query.setParameter(1,30L);
query.executeUpdate();

transaction.commit();
session.close();
}

1.4. HQL删除
delete from Order where id in (:idList) //括号可加可不加
session.createQuery(hql).setParameter("idList", list);

代码：
public void testUpdateHql(){
Session session = HibernateUtils.openSession();
Transaction transaction = session.beginTransaction();
// String hql = "update BookTypeEntity set typeDesc=? where typeId=?";
String hql = "delete from BookTypeEntity where typeId=?";
Query query = session.createQuery(hql);
// query.setParameter(0,"提升自己");
// query.setParameter(1,30L);
query.setParameter(0,29L);
query.executeUpdate();

  transaction.commit();
  session.close();

}

1.5. 获取某列的值
select orderId from Order
session.createQuery(hql, String.class);
代码：
public void find1(){
Session session = HibernateUtils.openSession();
//如果是一个字段，可以指定这个字段的类型
Query<String> query = session.createQuery("select typeName from BookTypeEntity", String.class);
List<String> resultList = query.getResultList();
for (String s : resultList) {
System.out.println(s);
}
session.close();
}

重点：

1.6. 获取部分字段的值:投影查询(查询局部字段)
select c.name, c.phoneNum, o.orderId, o.createTime from Order o join o.customer c
Query<Object[]> query = session.createQuery(hql, Object[].class);
方式二(不用泛型)：Object[] obj = (Object[])list.get(i)

传统希望方式：

image.png

bug:

image.png

调试观察可以用什么解决方案？

image.png

代码：

public void find2(){
Session session = HibernateUtils.openSession();
//如果查询部分字段
Query<Object[]> query = session.createQuery("select typeName,typeDesc from BookTypeEntity", Object[].class);
List<Object[]> resultList = query.getResultList();
for (Object[] objects : resultList) {
System.out.println(objects[0]+"\t"+objects[1]);
}
session.close();
}
注意上面的泛型

调试：
查询返回的不是对象，而是数组

image.png

总结：查询部分字段，返回的是Object[]数组。

1.7. 函数查询
select max(id), count(*) from Order
session.createQuery(hql, Object[].class);
Object[] obj = query.getSingleResult();

代码：
public void findCount(){
Session session = HibernateUtils.openSession();
Query<Long> query = session.createQuery("select count(*) from BookTypeEntity", Long.class);
Long aLong = query.uniqueResult();
System.out.println(aLong);
session.close();
}

1.8. 联合查询得到关联对象

多表查询:连接查询
左连接查询:左边的数据全部显示
//迫切外链接,返回一个对象(包含关联表对象)
from BookInfo b left join fetch b.bookType//别名.关联对象属性
//普通外连接得到是数组对象object[] ,解析结果比较麻烦
右连接查询：右边的数据全部显示
from BookInfo b right join fetch b.bookType
内连接查询:只会显示满足条件的数据
from BookInfo b inner join fetch b.bookType
(等值连接:只返回两个表中联结字段相等的行)
传统查询的方案：

image.png

bug:

image.png
分析问题：
image.png

解决方案一：使用数组作为泛型

image.png

解决方案二：
HQL中使用fetch，可以使用具体的对象作为泛型

public void find5(){
Session session = HibernateUtils.openSession();
//迫切外链接
String hql = "from BookInfoEntity book left join fetch book.bookTypeByTypeId ";
Query<BookInfoEntity> query = session.createQuery(hql, BookInfoEntity.class);
List<BookInfoEntity> resultList = query.getResultList();
for (BookInfoEntity bookInfoEntity : resultList) {
//主表
System.out.println(bookInfoEntity.getBookName());
//关联表
BookTypeEntity bookTypeByTypeId = bookInfoEntity.getBookTypeByTypeId();
System.out.println("关联分类:"+bookTypeByTypeId.getTypeName());
}

session.close();
}

调试：

image.png

1.9. 排序 一般排序都是和分页一起用的新加入的数据在最上面显示出来
from Order order by id desc

倒序：

image.png

同时可以和分页配合：
//设置分页
query.setFirstResult(0);
query.setMaxResults(5);

本地SQL（扩展了解） 允许在java代码中使用最基本的sql语句操作

image.png

NativeQuery<Order> sqlQuery = session.createNativeQuery("select * from t_order", Order.class);
List<Order> list = sqlQuery.list();

代码：

public void findSql(){
Session session = HibernateUtils.openSession();
String sql = "select * from book_type";
NativeQuery<BookTypeEntity> nativeQuery = session.createNativeQuery(sql, BookTypeEntity.class);
List<BookTypeEntity> list = nativeQuery.list();
for (BookTypeEntity bookTypeEntity : list) {
System.out.println(bookTypeEntity.getTypeName()+"\t"+bookTypeEntity.getTypeDesc());
}
session.close();
}