HQL数据查询基础

来源：互联网发布：天津二手房成交量数据编辑：程序博客网时间：2024/05/21 07:08

HQL,Hibernate Query Language，Hibernate查询语言。
HQL是面向对象的查询语言，它查询的主体是映射配置的持久化类及其属性。
HQL提供了丰富灵活的查询特性，Hibernate官方推荐查询方式
注意：

HQL是面向对象的查询语言，对Java类与属性大小写敏感
HQL对关键字不区分大小写，习惯上小写

Query接口

org.hibernate.Query接口，定义有执行查询的方法
Query接口支持方法链编程风格，使得程序代码更为简洁

Query实例的创建

1. Session.createQuery()方法创建Query实例2. creatQuery方法包含一个HQL语句参数，createQuery(hql)

Query执行查询

Query接口的list()方法执行HQL查询
list()方法返回结果数据类型为java.util.List，List集合中存放符合查询条件的持久化对象

代码实例

数据库表关系：
商家商品订单明细订单客户
一项商品属于某一个商家，一个商家有多个商品
一张订单属于某个客户，一个客户有多个订单
一个订单有多个订单明细
每项订单明细记录购买的商品信息，以及该商品的购买数量

商家表（Seller）

字段名字段描述数据类型 ID 主键 int name 商家名称 varchar tel 电话 varchar address 地址 varchar website 商家网站 varchar star 商家星级 varchar business 经营范围 varchar

客户表（Customer）

字段名字段描述数据类型 ID 主键 int name 名称 varchar tel 电话 varchar address 地址 varchar email 电子邮箱 varchar sex 性别 varchar description 简介 varchar age 年龄 int birthday 生日 date

商品表（Commodity）

字段名字段描述数据类型 ID 主键 int name 商品名称 varchar price 价格 double util 计量单位 varchar category 商品分类 varchar description 商品简介 varchar seller 所属商家 int

订单表（OrderForm）

字段名字段描述数据类型 ID 主键 int customer 所属客户 int tradedate 交易日 date status 订单状态 varchar amount 订单金额 double

订单明细表（OrderItem）

字段名字段描述数据类型 ID 主键 int orderid 所属订单 int commodity 订单商品 int discount 订单折扣 double actprice 成交价格 double amount 订单数量 double

# MySQL-Front 5.1  (Build 3.80)/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;/*!40103 SET SQL_NOTES='ON' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;/*!40014 SET UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;/*!40014 SET FOREIGN_KEY_CHECKS=0 */;# Host: 127.0.0.1    Database: webshop# ------------------------------------------------------# Server version 5.1.36-communityDROP DATABASE IF EXISTS `webshop`;CREATE DATABASE `webshop` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `webshop`;## Source for table commodity#DROP TABLE IF EXISTS `commodity`;CREATE TABLE `commodity` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) DEFAULT NULL,  `price` double(11,2) DEFAULT NULL,  `unit` varchar(50) DEFAULT NULL,  `category` varchar(100) DEFAULT NULL,  `description` varchar(1000) DEFAULT NULL,  `seller` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;## Dumping data for table commodity#LOCK TABLES `commodity` WRITE;/*!40000 ALTER TABLE `commodity` DISABLE KEYS */;INSERT INTO `commodity` VALUES (1,'中式童装',120,'套','童装','中式童装',1);INSERT INTO `commodity` VALUES (2,'女士套装',200,'套','女装','女士职业套装',1);INSERT INTO `commodity` VALUES (3,'男士西服',200,'套','男装','男士西服套装',1);INSERT INTO `commodity` VALUES (4,'笔记本电脑',4000,'台','电脑','双核笔记本电脑',2);INSERT INTO `commodity` VALUES (5,'移动硬盘',400,'块','电脑周边','1t移动硬盘',2);INSERT INTO `commodity` VALUES (6,'液晶电视',5000,'台','电视','4k高清液晶电视',3);INSERT INTO `commodity` VALUES (7,'滚筒洗衣机',4000,'台','洗衣机','滚筒洗衣机',3);INSERT INTO `commodity` VALUES (8,'《hibernate编程》',30,'本','实体书','介绍hibernate编程',4);INSERT INTO `commodity` VALUES (9,'《Java核心》',50,'本','实体书','介绍Java编程核心',4);INSERT INTO `commodity` VALUES (10,'《海底两万里》',40,'本','电子书','经典科幻小说',4);INSERT INTO `commodity` VALUES (11,'优盘',30,'个','电脑周边','16G优盘',2);/*!40000 ALTER TABLE `commodity` ENABLE KEYS */;UNLOCK TABLES;## Source for table customer#DROP TABLE IF EXISTS `customer`;CREATE TABLE `customer` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) DEFAULT NULL,  `tel` varchar(50) DEFAULT NULL,  `address` varchar(1000) DEFAULT NULL,  `email` varchar(300) DEFAULT NULL,  `sex` varchar(30) DEFAULT NULL,  `description` varchar(4000) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `birthday` date DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;## Dumping data for table customer#LOCK TABLES `customer` WRITE;/*!40000 ALTER TABLE `customer` DISABLE KEYS */;INSERT INTO `customer` VALUES (1,'张三','13800000000','中国上海××区××路','13800000000@138.com','男','热爱编程的程序员',25,'1990-01-01');INSERT INTO `customer` VALUES (2,'李四','13888888888','中国北京××区××路','13888888888@138.com','女','酷爱网购的白领',20,'1995-02-21');INSERT INTO `customer` VALUES (3,'王五','15888888888','中国深圳××区××路','15888888888@158.com','男','这个家伙很懒，什么也没有留下',35,'1980-04-14');INSERT INTO `customer` VALUES (4,'赵六','13900000000',NULL,NULL,'男',NULL,40,'1975-01-01');/*!40000 ALTER TABLE `customer` ENABLE KEYS */;UNLOCK TABLES;## Source for table orderform#DROP TABLE IF EXISTS `orderform`;CREATE TABLE `orderform` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `customer` int(11) DEFAULT NULL,  `tradedate` date DEFAULT NULL,  `status` varchar(10) DEFAULT NULL,  `amount` double(11,2) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;## Dumping data for table orderform#LOCK TABLES `orderform` WRITE;/*!40000 ALTER TABLE `orderform` DISABLE KEYS */;INSERT INTO `orderform` VALUES (1,1,'2015-04-30','已收货',4400);INSERT INTO `orderform` VALUES (2,2,'2015-05-11','已发货',520);INSERT INTO `orderform` VALUES (3,3,'2015-05-13','已付款',9120);/*!40000 ALTER TABLE `orderform` ENABLE KEYS */;UNLOCK TABLES;## Source for table orderitem#DROP TABLE IF EXISTS `orderitem`;CREATE TABLE `orderitem` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `ORDERID` int(11) DEFAULT NULL,  `COMMODITY` int(11) DEFAULT NULL,  `DISCOUNT` double(11,2) DEFAULT NULL,  `ACTPRICE` double(11,2) DEFAULT NULL,  `AMOUNT` double(11,2) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;## Dumping data for table orderitem#LOCK TABLES `orderitem` WRITE;/*!40000 ALTER TABLE `orderitem` DISABLE KEYS */;INSERT INTO `orderitem` VALUES (1,1,4,1,4000,1);INSERT INTO `orderitem` VALUES (2,1,5,1,400,1);INSERT INTO `orderitem` VALUES (3,2,1,1,120,1);INSERT INTO `orderitem` VALUES (4,2,2,1,200,1);INSERT INTO `orderitem` VALUES (5,2,3,1,200,1);INSERT INTO `orderitem` VALUES (6,3,6,1,5000,1);INSERT INTO `orderitem` VALUES (7,3,7,1,4000,1);INSERT INTO `orderitem` VALUES (8,3,8,1,30,1);INSERT INTO `orderitem` VALUES (9,3,9,1,50,1);INSERT INTO `orderitem` VALUES (10,3,10,1,40,1);/*!40000 ALTER TABLE `orderitem` ENABLE KEYS */;UNLOCK TABLES;## Source for table seller#DROP TABLE IF EXISTS `seller`;CREATE TABLE `seller` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) DEFAULT NULL,  `tel` varchar(1000) DEFAULT NULL,  `address` varchar(2000) DEFAULT NULL,  `website` varchar(500) DEFAULT NULL,  `star` int(11) DEFAULT NULL,  `business` varchar(2000) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;## Dumping data for table seller#LOCK TABLES `seller` WRITE;/*!40000 ALTER TABLE `seller` DISABLE KEYS */;INSERT INTO `seller` VALUES (1,'A服装店','13000000000','中国北京××区','www.a.com',5,'经营各式服装');INSERT INTO `seller` VALUES (2,'B数码店','15800000000','中国浙江杭州市××区','www.b.com',4,'经营各类数码电子产品');INSERT INTO `seller` VALUES (3,'C电器店','13012341234','中国广东深圳市××区','www.c.com',4,'经营各类家电');INSERT INTO `seller` VALUES (4,'D书店','18600000000','中国陕西西安市××区','www.d.com',5,'经营各类实体书与电子书');/*!40000 ALTER TABLE `seller` ENABLE KEYS */;UNLOCK TABLES;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

SellerTest.java

package com.imooc.model;import java.util.List;import java.util.Map;import org.hibernate.Query;import org.hibernate.Session;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.imooc.util.HibernateSessionFactory;public class SellerTest {    private Session session = null;    //通过自定义类型返回结果    @Test    public void testSelectClauseSelf(){        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());        }    }    //可以为每个字段设置别名，获取时:map.get("name");    @Test    public void testSelectClauseMap(){        String hql = " select new map(s.name,s.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("0"));            System.out.println("tel:"+map.get("1"));            System.out.println("address:"+map.get("2"));        }    }    @Test    public void testSelectClauseList(){        String hql = " select new list(s.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));        }    }    /*     * 1.name 2.tel 3.address 4.star     * 查询多个字段，Object[]     */    @Test    public void testSelectClauseObjectArray(){        String hql = " select s.name from Seller s ";        Query query = session.createQuery(hql);        List<Object> list = query.list();        for(Object obj : list){            System.out.println("name:"+obj);        }    }    @Test    public void testFromClause(){        String hql = " from Seller  s ";        Query query = session.createQuery(hql);        List<Seller> sellers = query.list();        for(Seller seller : sellers){            System.out.println("name:"+seller.getName());        }    }    @Before    public void setUp() throws Exception {        session = HibernateSessionFactory.getCurrentSession();    }    @After    public void tearDown() throws Exception {        session.close();    }}

CommodityTest.java

package com.imooc.model;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.imooc.util.HibernateSessionFactory;public class CommodityTest {    private Session session = null;    @Test    public void testOrderby(){        String hql = " from Commodity order by seller.id asc,price desc,name asc  ";        Query query = session.createQuery(hql);        List<Commodity> commodities = query.list();        for(Commodity c : commodities){            System.out.println("name:"+c.getName());            System.out.println("price:"+c.getPrice());        }    }    //四则元素    @Test    public void testWhere4(){        String hql = " from Commodity c where c.price*5>3000 ";        Query query = session.createQuery(hql);        List<Commodity> commodities = query.list();        for(Commodity c : commodities){            System.out.println("name:"+c.getName());            System.out.println("price:"+c.getPrice()*5);        }    }    @Test    public void testWhere3(){        String hql = " from Commodity c where c.price<200 or c.price>3000 ";        Query query = session.createQuery(hql);        List<Commodity> commodities = query.list();        for(Commodity c : commodities){            System.out.println("name:"+c.getName());            System.out.println("price:"+c.getPrice());        }    }    //字符串模糊匹配  一个_匹配一个字符  %匹配任意个字符    @Test    public void testWhere2(){        String hql  = " from Commodity c where c.price between 1000 and 5000 and c.category like '%电脑%' or name like '%电脑%' ";        Query query = session.createQuery(hql);        List<Commodity> commodities = query.list();        for(Commodity c : commodities){            System.out.println("name:"+c.getName());            System.out.println("category:"+c.getCategory());            System.out.println("price:"+c.getPrice());        }    }    @Test    public void testWhere1(){        String hql = " from Commodity c where c.price<=200 ";        Query query = session.createQuery(hql);        List<Commodity> commodities = query.list();        for(Commodity c : commodities){            System.out.println("name:"+c.getName());            System.out.println("price:"+c.getPrice());        }    }    @Test    public void testFromClause(){        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());        }    }    @Before    public void setUp() throws Exception {        session = HibernateSessionFactory.getCurrentSession();    }    @After    public void tearDown() throws Exception {        session.close();    }}

OrderTest.java

package com.imooc.model;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.imooc.util.HibernateSessionFactory;public class OrderTest {    private Session session = null;    /**     * is empty ---> exist     * member of ---> in     */    @Test    public void testWhere1(){        String hql = " from Order o where o.orderItems is not empty ";        Query query = session.createQuery(hql);        List<Order> orders = query.list();        for(Order order : orders){            System.out.println(order.getCustomer().getName());            System.out.println(order.getAmount());            System.out.println(order.getTradeDate());        }    }    @Before    public void setUp() throws Exception {        session = HibernateSessionFactory.getCurrentSession();    }    @After    public void tearDown() throws Exception {        session.close();    }}

CustomerTest.java

package com.imooc.model;import java.util.List;import java.util.Map;import org.hibernate.Query;import org.hibernate.Session;import org.junit.After;import org.junit.Before;import org.junit.Test;import com.imooc.util.HibernateSessionFactory;public class CustomerTest {    private Session session = null;    //排序    @Test    public void testOrderby(){        String hql = " from Customer order by age desc ";        Query query = session.createQuery(hql);        List<Customer> customers = query.list();        for(Customer c: customers){            System.out.println("name:"+c.getName());            System.out.println("age:"+c.getAge());        }    }    //查询单个对象，要保证where中条件，确保只有一个结果或没有结果    @Test    public void testWhere4(){        String hql = " from Customer c where c.age>20 ";        Query query = session.createQuery(hql);        Customer c = (Customer)query.uniqueResult();        System.out.println(c.getName());    }    @Test    public void testWhere3(){        String hql = " from Customer c where c.address like '%北京%'";        Query query = session.createQuery(hql);        List<Customer> customers = query.list();        for(Customer c : customers){            System.out.println("name:"+c.getName());            System.out.println("address :"+ c.getAddress());        }    }    @Test    public void testWhere2(){        String hql = " from Customer c where c.age not between 20 and 40 ";        Query query = session.createQuery(hql);        List<Customer> customers = query.list();        for(Customer c: customers){            System.out.println("name:"+c.getName());            System.out.println("age:"+c.getAge());        }    }    @Test    public void testWhere1(){        String hql = "  from Customer c where c.sex<>'男'";        Query query = session .createQuery(hql);        List<Customer> customers = query.list();        for(Customer c : customers){            System.out.println("name:"+c.getName());            System.out.println("sex:"+c.getSex());        }    }    //distinct关键字去除重复元素    @Test    public void testDistinct(){        String hql = "select distinct c.sex from Customer c ";        Query query = session.createQuery(hql);        List<Object> list = query.list();        for(Object obj : list){            System.out.println(obj);        }    }    @Test    public void testSelectClauseMap(){        String hql = "select new map(c.name,c.sex,c.age,c.tel ) from Customer c ";        Query query = session.createQuery(hql);        List<Map> maps = query.list();        for(Map map : maps){            System.out.println("name:"+map.get("0"));            System.out.println("sex:"+map.get("1"));            System.out.println("age:"+map.get("2"));            System.out.println("tel:"+map.get("3"));        }    }    @Test    public void testSelectClauseList(){        String hql = "select new list(c.name,c.sex,c.age,c.tel) from Customer c ";        Query query = session.createQuery(hql);        List<List> lists = query.list();        for(List list : lists){            System.out.println("name:"+list.get(0));            System.out.println("sex:"+list.get(1));            System.out.println("age"+list.get(2));            System.out.println("tel:"+list.get(3));        }    }    @Test    public void testSelectClauseObjectArray(){        String hql = " select c.name,c.tel,c.age from Customer c ";        Query query = session.createQuery(hql);        List<Object[]> list = query.list();        for(Object[] objs : list){            System.out.println("name:"+objs[0]);            System.out.println("tel:"+objs[1]);            System.out.println("age:"+objs[2]);        }    }    @Test    public void testFromClause(){        String hql = " from Customer ";        Query query = session.createQuery(hql);        List<Customer> customers = query.list();        for(Customer customer : customers){            System.out.println("name:"+customer.getName());        }    }    @Before    public void setUp() throws Exception {        session = HibernateSessionFactory.getCurrentSession();    }    @After    public void tearDown() throws Exception {        session.close();    }}

阅读全文

0 0