hibernate HQL
1. hsql介绍:
1.查询所有客户
代码:
@Test public void TestDemo1(){ Session session= HibernateUtils.openSession(); Transaction transaction= session.beginTransaction(); Query query=session.createQuery("from Customer ");//Hsql查询 List<Customer> list= query.list(); for (Customer c:list ) { System.out.println(c.getCname()); } transaction.commit(); session.close(); }
结果:
Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_
ssml
ssml
ssml001
ssml002
ssml002
ssml003
2.1 选择查询
public void demo02(){
//2 简单条件查询
Session session = factory.openSession();
session.beginTransaction();
//1 指定数据,cid OID名称
//Query query = session.createQuery("from Customer where cid = 1");
//2 如果使用id,也可以(了解)
//Query query = session.createQuery("from Customer where id = 1");
//3 对象别名 ,格式: 类 [as] 别名
//Query query = session.createQuery("from Customer as c where c.cid = 1");
//4 查询所有项,mysql--> select * from...
Query query = session.createQuery("select c from Customer as c where c.cid = 1");
Customer customer = (Customer) query.uniqueResult();
System.out.println(customer);
session.getTransaction().commit();
session.close();
}
3.1 投影查询(部分)
public void demo04(){ //4 投影 Session session = HibernateUtils.openSession(); session.beginTransaction(); //1 默认 //如果单列 ,select c.cname from,需要List<Object> //如果多列,select c.cid,c.cname from ,需要List<Object[]> ,list存放每行,Object[]多列 // Query query = session.createQuery("select c.cid,c.cname from Customer c"); //2 将查询部分数据,设置Customer对象中 // * 格式:new Customer(c.cid,c.cname) // * 注意:Customer必须提供相应的构造方法。 // * 如果投影使用oid,结果脱管态对象。 Query query = session.createQuery("select new Customer(c.cid,c.cname) from Customer c"); List<Customer> allCustomer = query.list(); for (Customer customer : allCustomer) { System.out.println(customer.getCname() + " : " + customer.getOrderSet().size()); } session.getTransaction().commit(); session.close(); }结果:
Hibernate: select customer0_.id as col_0_0_, customer0_.name as col_1_0_ from t_customer customer0_
ssml : 0
ssml : 0
ssml001 : 0
ssml002 : 0
4.1 排序
代码:
@Test public void demo03(){ //3排序 ,mysql--> select... order by 字段 [asc]|desc ,.... Session session = HibernateUtils.openSession(); session.beginTransaction(); Query query = session.createQuery("from Customer order by cid desc"); List<Customer> allCustomer = query.list(); for (Customer customer : allCustomer) { System.out.println(customer.getCid()+"---------"+customer.getCname()); } session.getTransaction().commit(); session.close(); }结果:
6---------ssml003
5---------ssml002
4---------ssml002
3---------ssml001
2---------ssml
1---------ssml
5.1 分页
//分页 @Test public void demo05(){ //分页 Session session = HibernateUtils.openSession(); session.beginTransaction(); Query query = session.createQuery("from Customer"); // * 开始索引 , startIndex 算法: startIndex = (pageNum - 1) * pageSize; // *** pageNum 当前页(之前的 pageCode) query.setFirstResult(0); // * 每页显示个数 , pageSize query.setMaxResults(2); List<Customer> allCustomer = query.list(); for (Customer customer : allCustomer) { System.out.println(customer.getCid()+"---------"+customer.getCname()); } session.getTransaction().commit(); session.close(); }结果:
Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_ limit ?
1---------ssml
2---------ssml
6.1 绑定参数
/* 绑定参数
* 方式1:占位符,使用? 在hql语句替换具体参数
* 设置参数 query.setXxx(int , object)
* 参数1:?位置,从0开始。
* 参数2:实际参数
* 例如:String --> query.setString(int,String)
* 方式2:别名 , 格式 “属性= :别名 ”
* 设置参数 query.setXxx(String,object)
* 参数1:别名
* 参数2:实际参数
* 例如:Integer --> query.setInteger(String,Integer)
* 提供公共设置方法
* setParameter(int|string , Object)
*/
代码:
@Test public void demo06(){ Session session = HibernateUtils.openSession(); session.beginTransaction(); Integer cid = 1; //方式1占位符, // Query query = session.createQuery("from Customer where cid = ?"); // query.setInteger(0, cid);//从0开始 //方式2 别名 , 格式 “属性= :别名 ” Query query = session.createQuery("from Customer where cid = :id"); // query.setInteger("id", cid); query.setParameter("id", cid); Customer customer = (Customer) query.uniqueResult(); System.out.println(customer); session.getTransaction().commit(); session.close(); }结果:
Hibernate: select customer0_.id as id2_, customer0_.name as name2_ from t_customer customer0_ where customer0_.id=?
Hibernate: select orderset0_.cid as cid2_1_, orderset0_.id as id1_, orderset0_.id as id1_0_, orderset0_.price as price1_0_, orderset0_.cid as cid1_0_ from t_order orderset0_ where orderset0_.cid=?
Customer{cid=1, cname='ssml', orderSet=[[email protected]]}
7.1 聚合函数和分组
@Test public void demo07(){ /* 7 聚合函数 */ Session session = HibernateUtils.openSession(); session.beginTransaction(); //1 // Query query = session.createQuery("select count(*) from Customer"); //2 别名 // Query query = session.createQuery("select count(c) from Customer c"); //3 oid Query query = session.createQuery("select count(cid) from Customer"); Long numLong = (Long) query.uniqueResult(); int num = numLong.intValue(); System.out.println(num); session.getTransaction().commit(); session.close(); }
结果:
Hibernate: select count(customer0_.id) as col_0_0_ from t_customer customer0_ 6
8.1 连接查询
1.交叉连接 ,等效 sql 笛卡尔积
2.隐式内连接,等效 sql 隐式内连接
3.内连接,等效sql内连接
4.迫切内连接,hibernate底层使用 内连接。
5.左外连接,等效sql左外连接
6.迫切左外连接,hibernate底层使用 左外连接
7.右外连接,等效sql右外连接
/* 8 链接查询 : 左外连接和迫切左外连接?
* * 左外连接 , left outer join
* 底层使用sql的左外连接,hibernate进行数据自动封装,将一条记录,封装给两个对象(Customer,Order)
* 将两个对象添加到一个对象数组中Object[Customer,Order]
* * 迫切左外链接 left outer join fetch
* 底层使用sql的左外连接,hibernate将一条记录封装给Customer,讲order数据封装Order,并将order关联到Customer
* customer.getOrderSet().add(order)
* 默认查询的数据重复
*/
@Test public void demo08(){ /* 8 链接查询 : 左外连接和迫切左外连接? * * 左外连接 , left outer join * 底层使用sql的左外连接,hibernate进行数据自动封装,将一条记录,封装给两个对象(Customer,Order) * 将两个对象添加到一个对象数组中Object[Customer,Order] * * 迫切左外链接 left outer join fetch * 底层使用sql的左外连接,hibernate将一条记录封装给Customer,讲order数据封装Order,并将order关联到Customer * customer.getOrderSet().add(order) * 默认查询的数据重复 */ Session session = HibernateUtils.openSession(); session.beginTransaction(); //左外连接 List list = session.createQuery("from Customer c left outer join c.orderSet ").list();//数据重复!!!!!!!!!!!!!!! for ( Object o:list ) { System.out.println(o.toString()+"======"+list.size()); } session.getTransaction().commit(); session.close(); }
//数据去重复
代码:
@Test public void demo08(){ Session session = HibernateUtils.openSession(); session.beginTransaction(); //左外连接 List list = session.createQuery("select distinct c from Customer c left outer join c.orderSet ").list(); for ( Object o:list ) { System.out.println(o.toString()+"======"+list.size()); } session.getTransaction().commit(); session.close(); }结果:Hibernate: select orderset0_.cid as cid2_1_, orderset0_.id as id1_, orderset0_.id as id1_0_, orderset0_.price as price1_0_, orderset0_.cid as cid1_0_ from t_order orderset0_ where orderset0_.cid in (select customer0_.id from t_customer customer0_ left outer join t_order orderset1_ on customer0_.id=orderset1_.cid)
Customer{cid=1, cname='ssml', orderSet=[[email protected]]}======6
Customer{cid=2, cname='ssml', orderSet=[]}======6
Customer{cid=3, cname='ssml001', orderSet=[[email protected]]}======6
Customer{cid=4, cname='ssml002', orderSet=[[email protected]]}======6
Customer{cid=5, cname='ssml002', orderSet=[[email protected], [email protected], [email protected]]}======6
Customer{cid=6, cname='ssml003', orderSet=[]}======6