目录
概述:
HQL
:
Hibernate Query Language
,是
Hibernate
框架提供的⼀种查询机制,它和
SQL
类似,不同的
是
HQL
是⾯向对象的查询语句,让开发者能够以⾯向对象的思想来编写查询语句,对
Java
编程是⼀种很友好的⽅式。
HQL
不能直接参与数据库的交互,中间层语⾔。
Java ---
》
HQL ---
〉
Hibernate ---
》
SQL ---
〉
DB
HQL
只能完成查询、修改、删除,新增是⽆法操作的。
1、查询对象
查询表中所有数据,⾃动完成对象的封装,返回
List
集合。
HQL
进⾏查询,
from
关键字后⾯不能写表名,必须写表对应的实体类名。
//1、实体类
package com.southwind.entity;
import lombok.Data;
@Data
public class People {
private Integer id;
private String name;
private Double money;
}
<!--2、People.hbm.xml文件-->
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping> <!--文件名的hbm就是hibernate-mapping 的缩写,在这里面完成类和表的映射-->
<!-- class 就是指类,name就是指类名 table 就是指对应的表名,表customer里面有id和name两个字段-->
<class name="com.southwind.entity.People" table="people">
<!-- 主键映射 name指的是实体类里面的属性:id type指的是id属性的类型-->
<id name="id" type="java.lang.Integer">
<!--column是字段,指的是对应类里面id的表里面的名称-->
<column name="id"></column>
<!--这个是配置主键自增的方式,identity的意思就是自增-->
<generator class="identity"></generator>
</id>
<!-- 下面继续配置类里面的其他信息,name type指的是name属性的类型-->
<property name="name" type="java.lang.String">
<!--column是字段,指的是对应类里面name的表里面的名称-->
<column name="name"></column>
</property>
<!-- 下面继续配置类里面的其他信息,money type指的是money属性的类型-->
<property name="money" type="java.lang.Double">
<!--column是字段,指的是对应类里面money的表里面的名称-->
<column name="money"></column>
</property>
</class>
</hibernate-mapping>
<!--3、hibernate.cfg.xml文件-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<!-- 核⼼配置:session-factory-->
<!-- SessionFactory:针对单个数据库映射经过编译的内存镜像⽂件,将数据库转换为⼀个 Java 可以识别的镜像⽂件。-->
<!-- 构建 SessionFactory ⾮常耗费资源,所以通常⼀个⼯程只需要创建⼀个 SessionFactory。-->
<session-factory>
<!-- datasource 数据源配置 -->
<property name="connection.username">root</property>
<property name="connection.password">123456</property>
<property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT</property>
<!--?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT" />-->
<!-- C3P0 连接池 -->
<property name="hibernate.c3p0.acquire_increment">10</property> <!-- 每次不够的话就会增加的数量-->
<property name="hibernate.c3p0.idle_test_period">10000</property> <!--释放资源时间的设置,s为单位-->
<property name="hibernate.c3p0.timeout">5000</property> <!-- 超时时间-->
<property name="hibernate.c3p0.max_size">30</property> <!-- 最大连接数-->
<property name="hibernate.c3p0.min_size">5</property> <!-- 最小连接数-->
<property name="hibernate.c3p0.max_statements">10</property> <!-- 最大线程数数-->
<!-- 数据库⽅⾔ oracle或mysql-->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!-- 打印SQL语句,固定写法 -->
<property name="show_sql">true</property>
<!-- 格式化SQL语句,固定写法 -->
<property name="format_sql">true</property>
<!-- 是否⾃动⽣成数据表-->
<property name="hibernate.hbm2ddl.auto">update</property>
<!-- create:每次加载hibernate时都会删除上一次的生成的表,然后根据你的model类再重新来生成新表,
哪怕两次没有任何改变也要这样执行,这就是导致数据库表数据丢失的一个重要原因。
create-drop :每次加载hibernate时根据model类生成表,但是sessionFactory一关闭,表就自动删除。
update:最常用的属性,第一次加载hibernate时根据model类会自动建立起表的结构(前提是先建立好数据库),
以后加载hibernate时根据 model类自动更新表结构,即使表结构改变了但表中的行仍然存在不会删除以前的行。
要注意的是当部署到服务器后,表结构是不会被马上建立起来的,是要等 应用第一次运行起来后才会。
validate :每次加载hibernate时,验证创建数据库表结构,只会和数据库中的表进行比较,不会创建新表,但是会插入新值。
-->
<!-- 注册实体关系映射文件 -->
<mapping resource="com/southwind/entity/People.hbm.xml"></mapping>
<mapping resource="com/southwind/entity/Customer.hbm.xml"></mapping>
<mapping resource="com/southwind/entity/Orders.hbm.xml"></mapping>
<mapping resource="com/southwind/entity/Account.hbm.xml"></mapping>
<mapping resource="com/southwind/entity/Course.hbm.xml"></mapping>
</session-factory>
</hibernate-configuration>
//4、test
package com.southwind.test;
import com.southwind.entity.Customer;
import com.southwind.entity.Orders;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
//查询对象
String hql = "from People";
Query query = session.createQuery(hql);
List<People> list = query.list();
for(People people:list){
System.out.println(people);
}
//分⻚查询
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//where 条件查询
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
//模糊查询
// String hql = "from People where name like '%四%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//order by排序查询
// String hql = "from People order by id desc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//查询实体对象的属性,姓名
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
//占位符
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
//级联查询
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
查询结果:
2、分⻚查询
HQL
分⻚查询可以通过调⽤
query
的⽅法来完成。
1、setFirstResult() 设置起始下标
2、setMaxResults() 设置截取⻓度
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
String hql = "from People";
Query query = session.createQuery(hql);
//从第一个开始,往后面3个
query.setFirstResult(1);//设置起始下标
query.setMaxResults(3);//设置截取⻓度
List<People> list = query.list();
for(People people:list){
System.out.println(people);
}
// String hql = "from People where id = 0";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
// String hql = "from People where name like '%三%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People order by id asc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
3、where 条件查询
HQL 直接追加 where 关键字作为查询条件,与 SQL 没有区别。
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
String hql = "from People where id = 4";
Query query = session.createQuery(hql);
People people = (People) query.uniqueResult();
System.out.println(people);
// String hql = "from People where name like '%三%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People order by id asc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
4、模糊查询
查询名称包含
“四
”
的所有记录
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
String hql = "from People where name like '%四%'";
Query query = session.createQuery(hql);
List<People> list = query.list();
for(People people:list){
System.out.println(people);
}
// String hql = "from People order by id asc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
5、order by
按照
id
进⾏降序排序
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
// String hql = "from People where name like '%四%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
String hql = "from People order by id desc ";
Query query = session.createQuery(hql);
List<People> list = query.list();
for(People people:list){
System.out.println(people);
}
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
6、查询实体对象的属性
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
//查询对象
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//分⻚查询
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//where 条件查询
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
//模糊查询
// String hql = "from People where name like '%四%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//order by排序查询
// String hql = "from People order by id desc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//查询实体对象的属性,姓名
String hql = "select name from People where id = 6";
Query query = session.createQuery(hql);
String name = (String) query.uniqueResult();
System.out.println(name);
//占位符
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
//级联查询
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
7、占位符
package com.southwind.test;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
//查询对象
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//分⻚查询
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//where 条件查询
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
//模糊查询
// String hql = "from People where name like '%四%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//order by排序查询
// String hql = "from People order by id desc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//查询实体对象的属性,姓名
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
//占位符
String hql = "from People where name = :name";
Query query = session.createQuery(hql);
query.setString("name","张三");
List<People> list = query.list();
for (People people:list){
System.out.println(people);
}
//级联查询
// String hql1 = "from Customer where name = :name";
// Query query1 = session.createQuery(hql1);
// query1.setString("name","张三");
// Customer customer = (Customer) query1.uniqueResult();
// String hql2 = "from Orders where customer = :customer";
// Query query2 = session.createQuery(hql2);
// query2.setEntity("customer",customer);
// List<Orders> list = query2.list();
// for(Orders orders:list){
// System.out.println(orders);
// }
session.close();
}
}
结果:
8、级联查询
package com.southwind.test;
import com.southwind.entity.Customer;
import com.southwind.entity.Orders;
import com.southwind.entity.People;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.List;
public class Test11 {
public static void main(String[] args) {
//创建 Configuration
Configuration configuration = new Configuration().configure();
//获取 SessionFactory
SessionFactory sessionFactory = configuration.buildSessionFactory();
//获取 Session
Session session = sessionFactory.openSession();
//查询对象
// String hql = "from People";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//分⻚查询
// String hql = "from People";
// Query query = session.createQuery(hql);
// query.setFirstResult(1);//设置起始下标
// query.setMaxResults(3);//设置截取⻓度
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//where 条件查询
// String hql = "from People where id = 4";
// Query query = session.createQuery(hql);
// People people = (People) query.uniqueResult();
// System.out.println(people);
//模糊查询
// String hql = "from People where name like '%四%'";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//order by排序查询
// String hql = "from People order by id desc ";
// Query query = session.createQuery(hql);
// List<People> list = query.list();
// for(People people:list){
// System.out.println(people);
// }
//查询实体对象的属性,姓名
// String hql = "select name from People where id = 6";
// Query query = session.createQuery(hql);
// String name = (String) query.uniqueResult();
// System.out.println(name);
//占位符
// String hql = "from People where name = :name";
// Query query = session.createQuery(hql);
// query.setString("name","张三");
// List<People> list = query.list();
// for (People people:list){
// System.out.println(people);
// }
//级联查询
String hql1 = "from Customer where name = :name";
Query query1 = session.createQuery(hql1);
query1.setString("name","张三");
Customer customer = (Customer) query1.uniqueResult();
String hql2 = "from Orders where customer = :customer";
Query query2 = session.createQuery(hql2);
query2.setEntity("customer",customer);
List<Orders> list = query2.list();
for(Orders orders:list){
System.out.println(orders);
}
session.close();
}
}