【HIbernate框架学习】：HQL（二）

        限制子句——where子句

        where子句是一个逻辑表达式，设置查询条件，限制返回的查询结果。

        比较运算

        比较运算也就是持久化类的属性和给定的查询条件做比较。比较运算包括：=、<>、<>、>=、<=

        其中比较运算也包含null值判断，也就是is [not] null。

        (x = null) HIbernate框架会解析为： (x is null)

        (x <> null) HIbernate框架会解析为： (x is not null)

        实例类Commodity对应的Commodity数据表的数据：

        

        简单实例：

	@Test
	public void testWhere1(){
		String hql = " from Commodity c where c.price > 400";
		//String hql = " from Commodity c where c.description is null";
		//String hql = " from Commodity c where c.description = null";
		//String hql = " from Commodity c where c.description is not null";
		//String hql = " from Commodity c where c.description <> null";
		//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());
			//System.out.println("description:"+c.getDescription());
		}
	}

        查询结果：

        

        范围运算

        [not] in 列表：表示只有属性值存在才返回true，不存在则返回false

        [not] between 值1 and 值2：表示属性值在值1到值2范围之间才返回，不存在则不返回

        实例：

	@Test
	public void testWhere2(){
		String hql = " from Customer c where c.age in (20,40) ";
		//String hql = " from Customer c where c.age not in (20,40)";
		//String hql = " from Customer c where c.age between 20 and 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关键字，通配符%表示匹配任意个字符；通配符_表示匹配一个字符

        实例：

	@Test
	public void testWhere3(){
		//String hql = " from Customer c where c.name 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());
		}
	}

        查询结果：

        

        逻辑运算

        逻辑运算符包括：逻辑与（and）、逻辑或（or）、逻辑非（not）

        实例：

	@Test
	public void testWhere3(){
		String hql = " from Commodity c where c.price < 50 or c.price > 4000 ";
		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());
		}
	}

        查询结果：

        

        集合运算

        is [not] empty：表示集合[不]为空，不包含任何元素，empty解析为exists

        member of：表示元素属于集合，member of解析为in

        实体类Order对应的数据表Order数据：

        

        实体类OrderItem对应的数据表OrderItem数据：

        

        实例：

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

        查询结果：

        

        四则运算

        在HQL语句中也可以使用+-*/四则运算符，也可以在where子句和select语句中使用

        实例：

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

        查询结果：

        

        查询单个对象

        查询单个对象需要使用Query接口的uniqueResult方法，尤其注意在where子句条件的设置

        实例：

	@Test
	public void testWhere4(){
		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子句

        实例：

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

        查询结果：

        

        一个较为复杂的排序

	@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("sellerId:"+c.getSeller().getId());
			System.out.println("sellerName:"+c.getSeller().getName());
			System.out.println("price:"+c.getPrice());
		}
	}

       结果很多就不再演示。