hibernate查询的三种方式

Hibernate Hql

hql

案例说明

表一Commodity

| 1 | 中式童装 | 120.00 | 套 | 童装 | 中式童装 | 4 |

| 2 | 女士套装 | 200.00 | 套 | 女装 | 女士职业套装 | 1 |

| 3 | 男士西服 | 200.00 | 套 | 男装 | 男士西服套装 | 1 |

| 4 | 笔记本电脑 | 4000.00 | 台 | 电脑 | 双核笔记本电脑 | 2 |

| 5 | 移动硬盘 | 400.00 | 块 | 电脑周边 | 1t移动硬盘 | 2 |

| 6 | 液晶电视 | 5000.00 | 台 | 电视 | 4k高清液晶电视 | 3 |

| 7 | 滚筒洗衣机 | 4000.00 | 台 | 洗衣机 | 滚筒洗衣机 | 3 |

| 8 | 《hibernate编程》 | 30.00 | 本 | 实体书 | 介绍hibernate编程 | 4 |

| 9 | 《Java核心》 | 50.00 | 本 | 实体书 | 介绍Java编程核心 | 4 |

| 10 | 《海底两万里》 | 40.00 | 本 | 电子书 | 经典科幻小说 | 4 |

| 11 | 优盘 | 30.00 | 个 | 电脑周边 | 16G优盘 | 2 |

表一中最后一个字段是seller, 是外键关联seller表主键

以下是seller表

+----+------------+-------------+------------------------------+-----------+------+-----------------------------------+

+----+------------+-------------+------------------------------+-----------+------+-----------------------------------+

| 1 | A服装店 | 13000000000 | 中国北京××区 | www.a.com | 5 | 经营各式服装 |

| 2 | B数码店 | 15800000000 | 中国浙江杭州市××区 | www.b.com | 4 | 经营各类数码电子产品 |

| 3 | C电器店 | 13012341234 | 中国广东深圳市××区 | www.c.com | 4 | 经营各类家电 |

| 4 | D书店 | 18600000000 | 中国陕西西安市××区 | www.d.com | 5 | 经营各类实体书与电子书 |

+----+------------+-------------+------------------------------+-----------+------+-----------------------------------+

from 关键词

使用from关键词查询出Commodity中所有数据

// hql语句

String hql = " from Commodity ";

//创建一个Query对象

Query query = session.createQuery(hql);

//调用list方法,查询出数据,保存到commodities中

List<Commodity> commodities = query.list();

//foreach方法遍历查询出的数据

for(Commodity commodity : commodities){

System.out.println(commodity.getName());

}

可能出现的问题:

from 后跟的是需要查询的实体类的名字,而不是数据库中对应表的名字,这点需要特别注意
类名错误的trace

org.hibernate.hql.ast.QuerySyntaxException: commodity is not mapped [ from commodity ]
另外,

java.lang.IllegalArgumentException: node to traverse cannot be null!

这个错误是hql语句语法错误导致的

查询结果返回List<T>数组

select 关键词

单纯使用from语句会将所有字段都查询出来,我们可以通过select语句控制具体需要查询字段

基本用法:

String hql = "select name,price from Commodity";

Query query = session.createQuery(hql);

List<Object[]> commodities = query.list();

for(Object[] commodity : commodities){

System.out.println("商品名:"+commodity[0]);

System.out.println("价格:"+commodity[1]);

}

通过select关键词,我们将查询的字段设定为name和price, 通过query.list()方法查询后返回一个类型为Object数组的List集合

在foreach中,通过数组下标的方式取得查询的数据

别名

String hql = "select c.name as n,c.price as p from Commodity as c";

可以省略as

select 基本用法就是这些,额外补充下面的内容

基本方法中调用query.list()方法返回的是一个Object[] 类型的List集合,我们可以更改List()方法返回的数据类型

query.list()返回List<List> ,一个类型为List的List集合

用法如下

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

}

query.list()返回Set集合

通过as方法给字段添加别名后,再在外面加个new map()

具体代码如下

String hql = "select new map(name as name,price as price) from Commodity";

Query query = session.createQuery(hql);

List<Map> commodities = query.list();

for(Map map : commodities){

System.out.println("商品名:"+map.get("name"));

System.out.println("价格:"+map.get("price"));

}

通过as关键词设置字段别名后,用set集合get("key")获得到数据

注意:不使用as别名的话不能通过get(String key)方法获得数据,但是可以通过get(0)

,这中下标的方式获取数据String hql = "select new map(name,price) from Commodity";
Query query = session.createQuery(hql);
List<Map> commodities = query.list();
for(Map map : commodities){
System.out.println("商品名:"+map.get("0"));
System.out.println("价格:"+map.get("1"));
}

List,Set 集合的方法与基本方法中返回List<Object[]>没有什么区别,根据习惯选用

select 只查询一个字段的情况

如果你在select中只查询一个字段,那么query.list()返回的就是List<Object>类型,而不是List<Object[]>类型

String hql = "select name from Commodity";

Query query = session.createQuery(hql);

List<Object> commodities = query.list();

for(Object commodity : commodities){

System.out.println(commodity.toString());

}

distinct 过滤重复数据

熟悉sql语句的同学都应该认识这个,过滤掉多余的重复记录只保留一条

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

}

where 条件查询

跟sql语法一模一样

比较运算符 >大于 , < 小于 , >= 大于等于, <= 小于等于, <>不等于, = 等于, is null, is not null

查询女客户

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

}

in,between

查询年龄是20或者40的顾客

String hql = " from Customer c where c.age not in(20,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());

}

查询年龄在20到40岁的客户

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

}

like 模糊查询

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

}

limit 与 uniqueResult

hql中不支持limit 语法

String hql = " from Customer";

Query query = session.createQuery(hql);

//设置从第几条开始查询(开始检索的位置)

query.setFirstResult(0);

//设置查询出多少条(返回最大记录行)

query.setMaxResults(1);

演示

String hql = " from Customer";

Query query = session.createQuery(hql);

query.setFirstResult(0);

query.setMaxResults(1);

List<Customer> lists = query.list();

for(Customer customer : lists){

System.out.println(customer.getName());

}

例子中,只查询了一条,所以lists中只有一个Customer对象,我们可以使用uniqueResult改进下

String hql = " from Customer";

Query query = session.createQuery(hql);

query.setFirstResult(0);

query.setMaxResults(1);

Customer customer = (Customer) query.uniqueResult();

System.out.println(customer.getName());

query.uniqueResult()只能用于查询一条数据,如果查询结果是多条则报错

排序

asc 正序

desc 倒序

支持逗号分隔,二次排序

String hql = " from Customer order by age desc,id asc ";

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

}

group by

支持group by语法

String hql = "select name,address from Customer group by sex";

Query query = session.createQuery(hql);

List<Object[]> customers = query.list();

for(Object[] customer : customers){

System.out.print(customer[0]+ " ");

System.out.println(customer[1]);

}

Hibernate QBC

//查询所有

session.createCriteria(Object.class).list();

//分页查询

session.createCriteria(Object.class).setFirstResult(0).setMaxResults(4).list();

//Like查询

session.createCriteria(Object.class)

.add(Restrictions.like("ename", "a", MatchMode.ANYWHERE).ignoreCase()).list();

//between查询

String beginDateStr = "1980-07-26 00:00:00";

String endDateStr = "2016-07-28 23:59:59";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

Date beginDate = null;

Date endDate = null;

try {

beginDate = sdf.parse(beginDateStr);

endDate = sdf.parse(endDateStr);

} catch (ParseException e) {

e.printStackTrace();

}

List<Employee> empList = session.createCriteria(Object.class)

.add(Restrictions.between("hiredate", beginDate, endDate)).list();

for(Employee e : empList){

System.out.println(e.getEname());

}

//in查询

List<Object> List = session.createCriteria(Object.class)

.add(Restrictions.in("id", new Integer[]{7369, 7499})).list();

//大于查询

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.gt("id", 7500)).list();

//大于等于查询

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.ge("id", 7521)).list();

<或<=

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.lt("id", 7500)).list(); // 小于(<)

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.le("id", 7521)).list(); // 小于等于(<=)

//等于 = 查询

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.eq("id", 7521)).list(); // 等于(=)

//多条件查询

String beginDateStr = "1980-07-26 00:00:00";

String endDateStr = "2016-07-28 23:59:59";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

Date beginDate = null;

Date endDate = null;

try {

beginDate = sdf.parse(beginDateStr);

endDate = sdf.parse(endDateStr);

} catch (ParseException e) {

e.printStackTrace();

}

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.ge("id", 7521))

.add(Restrictions.between("hiredate", beginDate, endDate))

.add(Restrictions.like("ename", "a", MatchMode.ANYWHERE).ignoreCase())

.list();

for(Employee e : empList){

System.out.println(e.getEname());

}

//多条件查询(or)

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.or(Restrictions.eq("id", 7521), Restrictions.like("ename", "A%").ignoreCase()))

.list();

for(Employee e : empList){

System.out.println(e.getEname());

}

//排序

List<Employee> empList = session.createCriteria(Employee.class)

.addOrder(Order.desc("salary")).list();

//非空查询

List<Employee> list = session.createCriteria(Employee.class).add(Restrictions.isNotNull("comm")).list();

//查询单个聚合函数

Object o = session.createCriteria(Student.class)

.setProjection(Projections.rowCount()

).list().get(0);

System.out.println(o.toString());

//多条件查询and

@Test

public void multiAndQuery(){

String beginDateStr = "1980-07-26 00:00:00";

String endDateStr = "2016-07-28 23:59:59";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

Date beginDate = null;

Date endDate = null;

try {

beginDate = sdf.parse(beginDateStr);

endDate = sdf.parse(endDateStr);

} catch (ParseException e) {

e.printStackTrace();

}

List<Employee> empList = session.createCriteria(Employee.class)

.add(Restrictions.ge("id", 7521))

.add(Restrictions.between("hiredate", beginDate, endDate))

.add(Restrictions.like("ename", "a", MatchMode.ANYWHERE).ignoreCase())

.list();

for(Employee e : empList){

System.out.println(e.getEname());

}

Hibernate 原生sql

int i = session.createSQLQuery("insert into student values(SQ_STUDENT.nextval,?,?,?,?)")

.setParameter(0, 2)

.setParameter(1, "露娜”)……

..executeUpdate();

List<Student> list = session.createSQLQuery("select * from student")

.addEntity(Student.class)

.list();

List<Map<String,Object>> query = session.createSQLQuery("select * from PERSON ")

.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

分页两种方式

1:limit

2:setFirstResult……

hibernate查询的三种方式

继续阅读

Hibernate自增主键

缓存(Cache)介绍

主键生成策略---- Hibernate入门学习主键生成策略《本章完》

Hibernate之级联

Hibernate 二级缓存以及 HibernateDaoSupport 简单总结

【hibernate框架】对象的三种状态Transient-Persistent-Detached

Hibernate之检索策略

Hibernate框架学习笔记——01

Spring MVC的例子使用

异常解决：org.hibernate.QueryException: Legacy-style query parameters (`?`) are no longer supported

如果没添spring框架的情况下,Hibernate中session.connection()的替代方法

Hibernate中Many的属性mappedBy小结

HQL检索方式

【HIbernate框架学习】：Hibernate对象关系映射之双向多对多关联映射

【Hibernate框架学习】：Hibernate进阶之持久化对象状态

如何理解hibernate