五)hibernate专用查询
(0)hibernate中的查询方式
>>session.get(字节码,1)
>>session.load(字节码,1)
>>query.list()
>>query.uniqueResult()
>>HQL查询【复杂,灵活,用途最广】
>>对象导航查询
(1)SQL和HQL的区别
HQL:
>>HQL是hibernate专用查询方式
>>hibernate用专用的技术将HQL转成SQL,此时会时时间的开销
>>HQL是完全面向对象的语法形式,即在HQL中只能出现类,属性,和一些操作符和关健字,例如:sum(),order by
SQL:
>>SQL是任何关系型数据库通用的查询方式
>>SQL是直接面向数据库的,无需转换
>>SQL是完全面向关系的语法形式,即在SQL中只能出现表,字段,和一些操作符和关健字,例如:sum(),order by
下面是熟悉和练习hql语句的题目:
(2)需求
(A)查询ID为1的客户订单信息
from Customer where id = 1
(B)查询姓名为“XX”的客户订单信息
from Customer where name = '牛人'
(C)使用别名,查询姓名为“XX”的客户订单信息
from Customer as c where c.name = '牛人'
(D)共有几个对象,共有几个客户,共有几个订单,多态查询(是指查询出当前类或所有子类的实例)
from java.lang.Object
(E)查询所有订单,按价格降序排列
from Order o order by o.price DESC
(F)分页查询所有订单,每页显示3条记录
//从第几条记录-1开始显示
query.setFirstResult(6);
//最多显示几条记录
query.setMaxResults(3);
(G)查询价格最贵的一个订单
from Order o order by o.price desc
query.setFirstResult(0);
query.setMaxResults(1);
(H)查询姓名为“XX”的客户订单信息,动态绑定参数,方式一:通过名字绑定,名字以:开头
(I)查询姓名为“XX”的客户订单信息,动态绑定参数,方式二:通过占位符?绑定,下标多0开始(个人提倡)
(J)查询姓名为“XX”的客户订单信息,动态绑定参数,方式三:在映射文件中定义命名HQL查询语句
<query name="findCustomerByAge">
<![CDATA[
from Customer c where c.age < ?
]]>
</query>
(K)查询姓"X"的,且年龄不介于10-20岁之间的客户
from Customer c where c.name like ? and c.age not between ? and ?
(L)使用投影查询,查询“XX”客户姓名,年龄,描述
select c.name,c.age from Customer c where c.name = '牛人'
注意:返回值,是一个数组的数组
(M)查询订单总数,订单总价格,最便宜订单,最昂贵订单,订单平均价格
select count(o),sum(o.price),min(o.price),max(o.price),avg(o.price) from Order o
(N)按客户分组,查询订单总价
SQL:
select customers_id,sum(price)
from orders
group by customers_id;
HQL:
select o.customer.id,sum(o.price) from Order o group by o.customer.id;
(3)HQL常用API
>>Query query = session.createQuery();
>>Query query = session.getNamedQuery();
>>query.setString("","");
>>query.setInt("","");
>>Object obj = query.uniqueResult();
>>List list = query.list();
以下代码是对A-N题目的实现:
package example.hql;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;
import example.utils.HibernateUtils;
public class CustomerOrderDao {
/**
* 查询ID为1的客户订单信息
*/
@Test
public void test01(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Customer where id=1";
Query query=session.createQuery(hql);
Customer c= (Customer) query.uniqueResult();
System.out.println("顾客的姓名:"+c.getName()+"顾客的年龄是:"+c.getAge());
//对象导航查询
for(Order o:c.getOrderSet()){
System.out.println("订单号:"+o.getOrderNo());
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询姓名为“XX”的客户订单信息(如:牛人2号)和使用别名,查询姓名为“XX”的客户订单信息
*/
@Test
public void test02(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Customer where name='牛人2号'";
Query query1=session.createQuery(hql);
Customer c1= (Customer) query1.uniqueResult();
for(Order o:c1.getOrderSet()){
System.out.println("牛人2号顾客的订单编号:"+o.getOrderNo()+",价格:"+o.getPrice());
}
hql="from Customer c where c.name='牛人'";
Query query2=session.createQuery(hql);
Customer c2= (Customer) query2.uniqueResult();
for(Order o:c2.getOrderSet()){
System.out.println("牛人顾客的订单编号:"+o.getOrderNo()+",价格:"+o.getPrice());
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 共有几个对象,共有几个客户,共有几个订单,多态查询(是指查询出当前类或所有子类的实例)
*/
@Test
public void test03(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from java.lang.Object";
Query query=session.createQuery(hql);
List list = query.list();
System.out.println(list.size());
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询所有订单,按价格降序排列
*/
@Test
public void test04(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Order order by price ";
Query query=session.createQuery(hql);
List<Order> list = query.list();
for(Order o:list){
System.out.println("订单编号是:"+o.getOrderNo());
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 分页查询所有订单,每页显示3条记录
*/
@Test
public void test05(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Order";
Query query=session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(3);
List<Order> list = query.list();
System.out.println("每一页的订单数目是:"+list.size());
for(Order o:list){
System.out.println("订单编号是:"+o.getOrderNo());
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询价格最贵的一个订单
*/
@Test
public void test06(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Order order by price desc";
Query query=session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(1);
Order o= (Order) query.uniqueResult();
System.out.println("订单编号是::"+o.getOrderNo());
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询姓名为“XX”的客户订单信息,动态绑定参数,方式一:通过名字绑定,名字以:开头
*/
@Test
public void test07(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Customer c where c.name=:cname";
Query query=session.createQuery(hql);
query.setString("cname", "牛人");
Customer c= (Customer) query.uniqueResult();
System.out.println(c.getOrderSet().size());
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询姓名为“XX”的客户订单信息,动态绑定参数,方式二:通过占位符?绑定,下标多0开始(个人提倡)
*/
@Test
public void test08(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Customer c where c.name=?";
Query query=session.createQuery(hql);
query.setString(0, "牛人");
Customer c= (Customer) query.uniqueResult();
System.out.println(c.getOrderSet().size());
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询姓名为“XX”的客户订单信息,动态绑定参数,方式三:在映射文件中定义命名HQL查询语句
* <![CDATA[里面转移为字符串]]>
*/
@Test
public void test09(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
Query query=session.getNamedQuery("findCustomerByAge");
query.setInteger(0, 12);
List list=query.list();
System.out.println("人数是:"+list.size());
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
* 查询姓"X"的,且年龄不介于10-20岁之间的客户
*/
@Test
public void test10(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="from Customer c where c.name like ? and c.age not between ? and ?";
Query query=session.createQuery(hql);
query.setString(0, "%牛%");
query.setInteger(1, 10);
query.setInteger(2, 20);
List<Customer> list = query.list();
for(Customer c : list){
System.out.println(c.getName()+":"+c.getAge());
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
*使用投影查询,查询“XX”客户姓名,年龄
**/
@Test
public void test11(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="select c.name,c.age from Customer c where c.name like ?";
Query query=session.createQuery(hql);
query.setString(0, "%牛%");
List<Object[]> list = query.list();
for(Object[] o : list){
System.out.println(o[0]+":"+o[1]);
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
*查询订单总数,订单总价格,最便宜订单,最昂贵订单,订单平均价格
*/
@Test
public void test12(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="select count(o),sum(o.price),min(o.price),max(o.price),avg(o.price) from Order o";
Query query=session.createQuery(hql);
List<Object[]> list = query.list();
for(Object[] obj : list){
System.out.println("订单总数:"+obj[0]);
System.out.println("订单总价格:"+obj[1]);
System.out.println("最便宜订单:"+obj[2]);
System.out.println("最昂贵的订单:"+obj[3]);
System.out.println("订单平均价格:"+obj[4]);
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
/**
*按客户分组,查询订单总价
**/
@Test
public void test13(){
Session session=HibernateUtils.getSession();
Transaction t=session.getTransaction();
try{
t.begin();
String hql="select o.customers.id,sum(o.price) from Order o group by o.customers.id";
Query query=session.createQuery(hql);
List<Object[]> list = query.list();
for(Object[] o : list){
System.out.println(o[0]+":"+o[1]);
}
t.commit();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
t.rollback();
}finally{
HibernateUtils.closeSession();
}
}
}