Hibernate-HQL
文章目录
HQL(Hibernate Query Language)是面向对象的查询语言,它和SQL查询语言有些相似。
在 Hibernate 提供的各种检索方式中,HQL 是使用最广的一种检索方式。Hibernate负责解析HQL查询语句,然后根据对象-关系映射文件中的映射信息,把HQL查询语句根据方言翻译成相应的SQL语句。HQL查询语句中的主体是域模型(真实世界的实体的软件抽象)中的类及类的属性。
由于面向对象的原因, HQL区分大小写,但是对应SQL关键字(select, from, where)不区分
数据准备
@Test
public void testSave() {
Session session = null;
Transaction tx = null;
try {
session = HibernateUtil.getSession();
tx = session.beginTransaction();
Category category1 = new Category();
category1.setName("文学");
Category category2 = new Category();
category2.setName("历史");
Category category3 = new Category();
category3.setName("言情");
Category category4 = new Category();
category4.setName("科幻");
Category category5 = new Category();
category5.setName("恐怖");
Book book1 = new Book();
book1.setName("红楼梦");
book1.setPrice(50.5);
book1.setAuthor("曹雪芹&高鹗");
book1.setCategory(category1);
Book book2 = new Book();
book2.setName("史记");
book2.setPrice(30.0);
book2.setAuthor("司马迁");
book2.setCategory(category2);
Book book3 = new Book();
book3.setName("那些年我们追过的女孩");
book3.setPrice(35.0);
book3.setAuthor("九把刀(柯景腾)");
book3.setCategory(category3);
Book book4 = new Book();
book4.setName("盗墓笔记");
book4.setPrice(65.0);
book4.setAuthor("南派三叔(徐磊)");
book4.setCategory(category4);
Book book5 = new Book();
book5.setName("楼下的房客");
book5.setPrice(36.0);
book5.setAuthor("九把刀(柯景腾)");
book5.setCategory(category5);
session.save(book1);
session.save(book2);
session.save(book3);
session.save(book4);
session.save(book5);
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
} finally {
HibernateUtil.closeSession();
}
}
基本查询
查询单个属性
//编写hql
String hql = "select name from Book";
//创建Query对象
Query query = session.createQuery(hql);
//list()方法返回查询结果,返回结果的类型是根据查询的列决定的
List<String> list = query.list();
for(String bookName : list)}{
System.out.println(bookName);
}
查询多个属性
//编写hql
String hql = "select name, price from Book";
// 创建Query对象
Query query = session.createQuery(hql);
// 查询
List<Object[]> list = query.list();
for (Object[] obj: list) {
System.out.println(obj[0] + "---" + obj[1]);
}
将多个查询列封装成为对象
需要在类中定义带参构造
//编写hql
String hql = "select new Book(name, price) from Book";
// 创建Query对象
Query query = session.createQuery(hql);
// 查询
List<Book> list = query.list();
for (Book book: list) {
System.out.println(book);
}
使用别名
//编写hql
String hql = "select new Book(b.name, b.price) from Book as b";
// 创建Query对象
Query query = session.createQuery(hql);
// 查询
List<Book> list = query.list();
for (Book book: list) {
System.out.println(book);
}
查询所有列
不使用select
// 查询所有列
String hql = "from Book";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql).list();
for (Book book: list) {
System.out.println(book);
}
使用select,但是不能使用(不支持)*
// 查询所有列
String hql = "select b from Book b";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql).list();
for (Book book: list) {
System.out.println(book);
}
条件查询
使用占位符“?” (从0开始,JPA方式使用)
// 查询所有列
String hql = "from Book where id < ?0 and name = ?1";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql)
.setParameter(0, 3)
.setParameter(1, "史记")
.list();
for (Book book: list) {
System.out.println(book);
}
命名查询
// 查询所有列
String hql = "from Book where id < :idx";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql)
.setParameter("idx", 3)
.list();
for (Book book: list) {
System.out.println(book);
}
分页查询
// 查询所有列
String hql = "from Book";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql)
.setFirstResult(0) // 每页显示的起始下标 (pageNum - 1) * pageSize
.setMaxResults(3) // 每页显示多少条 pageSize
.list();
for (Book book: list) {
System.out.println(book);
}
统计查询
// 查询所有列
// count(1) count(*)都可以
String hql = "select count(b.id) from Book b";
// 创建Query对象并查询
Object count = session.createQuery(hql).uniqueResult();
System.out.println("图书的总数为:" + count);
分组查询
// 查询所有列
String hql = "select b.category.name, count(b.id) from Book b group by
b.category.name";
// 创建Query对象并查询
List<Object[]> list = session.createQuery(hql).list();结果
for (Object[] obj: list) {
System.out.println(obj[0] + "--" + obj[1]);
}
排序
// 查询所有列,降序
String hql = "from Book order by price desc";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql).list();结果
for (Book b: list) {
System.out.println(b);
}
内连接查询
// 查询所有列
String hql = "select b from Book b inner join b.category c where c.name =:name";
// 创建Query对象并查询
List<Book> list = session.createQuery(hql).setParameter("name", "言情")
.list();
for (Book b: list) {
System.out.println(b);
}
外连接查询
// 查询所有列
String hql = "select c.name, b.name from Category c left join c.books b";
// 创建Query对象并查询
List<Object[]> list = session.createQuery(hql).list();
for (Object[] obj: list) {
System.out.println(obj[0] + "---" + obj[1]);
}
过滤器
过滤器的作用是让每一次的查询都添加指定的条件。
1、注解方式
Book.java
@Entity // 表示一个需要持久化的类
@Table(name = "book") // Table表示生成表的表名,可以不写,默认值类名
@FilterDef(name="bookFilter", parameters={@ParamDef(name="id",type="integer")}) // 声明
拦截器
@Filter(name="bookFilter",condition="id < :id") // 选择使用的过滤器
public class Book implements Serializable {
private Integer id;
private String name;
private String author;
private Double price;
private String copyright;
private Category category;
public Book() {
}
public Book(String name, Double price) {
this.name = name;
this.price = price;
}
@Id // 主键
@GeneratedValue(strategy = GenerationType.IDENTITY) // 自增
@Column(name = "id") // 数据库表列名,可以不写,默认属性名
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@ManyToOne(cascade = CascadeType.ALL) // 级联操作
@JoinColumn(name = "category_id") // 外键列名
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
//测试类
public void setAuthor(String author) {
this.author = author;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Transient // 表示不生成数据库表对应的列
public String getCopyright() {
return copyright;
}
public void setCopyright(String copyright) {
this.copyright = copyright;
}
}
2、配置文件方式
Book.hbm.xml
<hibernate-mapping>
<class name="com.bjsxt.pojo.Book" table="book">
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="identity" />
</id>
<many-to-one name="category" class="com.bjsxt.pojo.Category" fetch="select">
<column name="category_id" />
</many-to-one>
<property name="author" type="java.lang.String">
<column name="author" />
</property>
<property name="name" type="java.lang.String">
<column name="name" />
</property>
<property name="price" type="java.lang.Double">
<column name="price" precision="22" scale="0" not-null="true" />
</property>
<!-- 使用过滤器 -->
<filter name="bookFilter" condition="id=:id"></filter>
</class>
<!-- 过滤器定义:定义参数 -->
<filter-def name="bookFilter">
<filter-param name="id" type="integer"/>
</filter-def>
</hibernate-mapping>
测试类不变
原生SQL查询
// 使用原生SQL查询
String sql = "select * from book"; // 不推荐使用*,索引会失效
List<Book> list = session.createSQLQuery(sql).addEntity(Book.class).list();
for (Book b: list) {
System.out.println(b);
}