Hibernate简单的HQL语句

查询单一的属性

格式：select  属性名 from 类名

			List<String> list =
				session.createQuery("select name from Emp").list();
			Iterator<String> iterator = list.iterator();
			while(iterator.hasNext()){
				String name = iterator.next();
				System.out.println(name);
			}

查询多个属性

格式：select 属性名1，属性名2...from 类名

返回值用List    遍历使用Object[]

		 List	  list = session.createQuery("select name,salary from Emp ").list();
		 Iterator iterator = list.iterator();
		 while(iterator.hasNext()){
			 Object[] obj = (Object[]) iterator.next();//name , salary
			 System.out.println("姓名是:"+obj[0]+",工资是:"+obj[1]);
		 }

使用别名查询

格式: select   别名.属性名   from 类名 别名

		List list =  session.createQuery(
				 "select e.id , e.name , e.salary  from Emp e").list();
		Iterator iterator = list.iterator();
		while(iterator.hasNext()){
			Object[] obj = (Object[]) iterator.next();
			System.out.println("id是:"+obj[0]+"姓名是:"+obj[1]
			                                            +"工资是:"+obj[2]);
		}

使用构造方法查询

a 在实体类当中,加入带参数的构造方法

b  格式: select new 构造方法(属性) from 类名

			List list =
			session.createQuery(
				"select new Emp(id,name,salary) from Emp ").list();
			Iterator iterator = list.iterator();
			while(iterator.hasNext()){
				Emp emp = (Emp) iterator.next();
				System.out.println("id是:"+emp.getId()+"姓名是:"
						+emp.getName()+"工资是:"+emp.getSalary());
				
			}

针对条件的查询where

模糊查询emp中,name中带有"Dept"的数据

		List list =	session.createQuery("select e.id,e.name from Emp e where e.name like '%Dept%'").list();
		Iterator iterator = list.iterator();
		while(iterator.hasNext()){
		Object[] obj = (Object[]) iterator.next();
			System.out.println("id是:"+obj[0]+",姓名是:"+obj[1]
			                                 );
		}

where 传递过来的参数,追加到where( ?)

			List list = session.createQuery(
			"select e.name,e.id  from Emp e where e.name like ? " +
			"and e.salary = ?" ).setParameter(0, "%Dept%")
			.setParameter(1, 800.0).list();
			Iterator iterator = list.iterator();
		    while(iterator.hasNext()){
		    	Object[] obj = (Object[]) iterator.next();
		    	System.out.println(obj[0]+" , "+obj[1]);
		    }

where 传递过来的参数,追加到where( :别名)

			Object[] obj = (Object[]) session.createQuery(                                                                                     
			"select e.id ,e.name from Emp e " +
			"where e.name like :myName and e.id=:myId")
			.setParameter("myName","%Dept%" )
			.setParameter("myId", 5)
			.list().get(0);
			System.out.println(obj[0]+" , "+obj[1]);

id =1 ,2 ,3,4,5-----------> where id in(1,2,3,4,5);

			List list = session.createQuery(
					"select e.id , e.name from Emp e " +
					"where e.id in(:myId)")
					.setParameterList("myId", new Object[]{1,2,3,4,5} )
					.list();
			Iterator iterator = list.iterator();
			while(iterator.hasNext()){
				Object[] obj = (Object[]) iterator.next();
				System.out.println(obj[0]+" , "+obj[1]);
			}

Sql select * from 表名

HQL :不能使用"*" 
 count(*)可以使用
HQL:也可以使用分组函数

		long count =	(Long) session.createQuery(
					"select count(*) from Emp ").uniqueResult();
		System.out.println(count);

在session使用sql语句 (deptId使用hql查询不了,hql不能使用各个数据库厂商独有的函数)

		  int id =(Integer)	 session.createSQLQuery(
					"select deptId from t_emp where id = 1")
					.uniqueResult();
			System.out.println(id);

目标导向查询

1号部门里所有的员工

			List list = session.createQuery(
			"select e.id ,e.name from Emp e where e.dept.id = 1")
			.list();
			Iterator iterator =list.iterator();
			while(iterator.hasNext()){
				Object[] obj = (Object[]) iterator.next();
				System.out.println(obj[0]+" , "+obj[1]);
			}

iterate() : 查询对象

n+1问题:
a  先发出1条sql语句,查询所有的id
select emp0_.id as col_0_0_ from t_emp emp0_
b  根据id ,发出n条查询对象的sql语句
select emp0_.id as id1_0_, emp0_.t_salary as t2_1_0_, emp0_.t_name as t3_1_0_, emp0_.deptId
as deptId1_0_ from t_emp emp0_ where emp0_.id=?

			Iterator iterator =
				session.createQuery("from Emp").iterate();
			while(iterator.hasNext()){
				Emp emp = (Emp) iterator.next();
				System.out.println(emp.getName()+" , "+emp.getId());
			}

list()  : 只发出一条查询对象的Sql语句

iterate() : 查询属性--->只发出了一条查询属性的sql语句

查询属性,没有id,所以不是n+1问题
 list():查询属性--->只发出了一条查询属性的sql语句

			Iterator iterator = session.createQuery(
					"select  e.name from Emp e")
					.iterate();
			while(iterator.hasNext()){
				String name = (String) iterator.next();
				System.out.println(name);
			}
			List list = session.createQuery(
					"select e.name from Emp e")
					.list();
			Iterator iterator2 = list.iterator();
			while(iterator2.hasNext()){
				String name =(String) iterator2.next();
				System.out.println(name);
			}