Hibernate-HQL

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);
}