第一章 了解HQL
1. HQL定义
Hibernate Query Language
- HQL是面向对象的查询语言
- HQL 映射配置的持久化类及其属性
- SQL 数据库表
2. HQL语句形式
select ... from ... where ... group by ... having ... order by ...
3. 初学HQL注意的问题
- HQL是面向对象的查询语言,对java类与属性大小写敏感
- HQL对关键字不区分大小写
第二章 准备查询
1. org.hibernate.Query接口
query接口支持方法链编程风格,使得程序代码更为简洁
2. Query实例的创建
session.createQuery()//创建Query实例
createQuery(hql)//方法2
3. 执行查询
query.list();//执行查询
//list()方法返回结果数据类型为java.util.List,List集合存放符合查询条件的持久化类对象。
4. 实例
4.1 配置好heibernate(见hibernate学习笔记)
4.2 新建junit test case
private Session session = null;
@Before
public void setUp() throws Exception {
session = HibernateSessionFactory.getSession();
}
@After
public void tearDown() throws Exception {
session.close();
}
@Test
public void testSeller() {
// junit测试 1.导包然后buildpath 2.在项目根目录下新建source folder(test)
// 3.新建junit test case
// HQL语法,用session对象来得到query对象,然后query对象直接执行就可以了,就是这么简单
String hql = "from Seller as seller";// 设置别名,对查询完全不影响
// String hql = "from com.imooc.model.Seller";//这样也可以
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for (Seller seller : sellers) {
System.out.println(seller);
}
}
第三章 检索对象from子句
- HQL语句最简形式
- from指定了HQL语句查询主体-持久化类及其属性
- HQL——->SQL(通过hebernate)
- 不需要引入持久化类的全限定名
- auto-import(自动引入)缺省的情况
- 可以为被查询的类指明别名,在HQL语句其他部分通过别名引用该类;别名命名习惯:from Seller s;
//测试关联表
@Test
public void test() {
String hql = "from Commodity";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for (Commodity c : commodities) {
System.out.println("name:"+c.getName());
//可以直接查询其对应外键表的信息(先在数据库定义好外键,然后再反向生成持久化对象和映射文件)
System.out.println("seller's name:"+c.getSeller().getName());
}
}
第四章 选择select子句
1. 以Object[]形式返回选择的属性
select子句中未指定返回数据类型,默认为Object[]
// 查询商家的名称、电话、地址、星级 ***当只查询一个属性信息时,返回的是object而不是数组,用数组接收会报错
@Test
// 默认返回object[]
public void testSelectReturnObjectArray() {
String hql = "select s.name,s.tel,s.address,s.star from Seller s";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println("name:" + objects[0]);
System.out.println("tel:" + objects[1]);
System.out.println("address:" + objects[2]);
System.out.println("star:" + objects[3]);
}
}
2. 以List形式返回选择的属性
// 返回的一条信息就是一个list集合,返回的是多条消息,所以用集合的集合接收
@Test
public void testSelectReturnList() {
String hql = "select new list(s.name as name,s.tel,s.address)from Seller s";
Query query = session.createQuery(hql);
List<List> lists = query.list();
for (List list : lists) {
System.out.println("name:" + list.get(0));
System.out.println("tel:" + list.get(1));
System.out.println("address:" + list.get(2));
}
}
3. 以map形式返回选择的属性
select子句中使用new map指定
key值为索引值,字符串类型
@Test//返回map ***Map中key值是string类型 可以通过别名来获取属性
public void testSelectReturnMap() {
String hql = "select new map(s.name as name,s.tel as tel,s.address)from Seller s";
Query query = session.createQuery(hql);
List<Map> maps = query.list();
for (Map map : maps) {
System.out.println("name:"+map.get("name"));
System.out.println("tel:"+map.get("tel"));
System.out.println("address:"+map.get("2"));
}
}
4. 以自定义类型返回选择的属性
持久化类中定义对应的构造器
select子句中调用定义的构造器
//返回自定义类型,先在持久化类中创建相应的构造方法
@Test
public void testSelectReturnSelf(){
String hql = "select new Seller(s.name,s.tel,s.address) from Seller s";
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for (Seller seller : sellers) {
System.out.println("name:"+seller.getName());
System.out.println("tel:"+seller.getTel());
System.out.println("address:"+seller.getAddress());
}
}
5. 获取独特的结果-distinct关键字
去除查询结果中的重复元素
@Test
public void testDistinct(){
String hql ="select distinct c.sex from Customer c";
Query query= session.createQuery(hql);
List<Object> objects = query.list();
for (Object object : objects) {
System.out.println("sex:"+object.toString());
}
}
第五章 限制where子句
where子句是一个逻辑表达式,设置查询条件,从而限制查询的返回结果
1. 比较运算:=、<>、<、>、>=、<=
@Test
public void testWhere1(){
String hql = "from Commodity c where c.price>400";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for (Commodity commodity : commodities) {
System.out.println("name:"+commodity.getName());
System.out.println("price:"+commodity.getPrice());
}
}
2. null值判断:x=null/x<>null; x is (not) null;
@Test
public void testWhereNull(){
String hql = "from Commodity c where c.description is null";
//可以的
//String hql = "from Commodity c where c.description = null";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for (Commodity commodity : commodities) {
System.out.println("name:"+commodity.getName());
System.out.println("description:"+commodity.getDescription());
}
}
3. 范围运算:[not] in (列表);[not] between 值1 and 值2;
4. 字符串模式匹配:like 关键字;通配符%、_
5. 逻辑运算:and、or、not
6. 集合运算:is [not] empty 集合(不)为空,不包含任何元素;member of 元素属于集合 empty ——>exists,member of ——>in
7. HQL语句中也可以使用+-*/四则运算;四则运算可以在where子句和select子句中使用
8. Query接口的uniqueResult方法,where子句条件的设置
@Test
public void testWhereUnique(){
String hql = "from Customer c where c.name = '张三'";
Query query = session.createQuery(hql);
//如果结果不是一个单独的结果或者没有结果,就会报错
Customer c = (Customer) query.uniqueResult();
System.out.println(c.getName());
}
第六章 排序order by子句,对查询结果排序
1.asc
2. desc
3. 练习一
@Test
public void practice1(){
String hql = "select c.name,c.price,s.name,c.category" +
" from Commodity c,Seller s " +
"where c.seller=s.id and c.category like '%书%' and c.price>=40" +
"order by c.seller desc,c.name asc";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println("商品名称:"+objects[0]);
System.out.println("商品价格:"+objects[1]);
System.out.println("商品所属的商家:"+objects[2]);
System.out.println("商品类别:"+ objects[3]);
}
}
4. 练习二
@Test
public void practice2(){
String hql = "select new Map(o.customer.name,o.tradedate,o.status,o.amount) from Orderform o" +
" where o.tradedate between '2015-05-01' and '2015-06-01' and o.status in ('已发货' ,'已付款') and " +
"o.amount>1000 order by o.status asc,o.tradedate desc,o.amount desc";
Query query = session.createQuery(hql);
List<Map> list =query.list();
for (Map map : list) {
System.out.println("客户姓名:"+map.get("0"));
System.out.println("交易日期:"+map.get("1"));
System.out.println("订单状态:"+map.get("2"));
System.out.println("订单金额:"+map.get("3"));
}
}