hibernate_查询简介: 分页, 数据总数, 结果集的包装, 位置参数的设置

//使用原生sql 查询 employee 表中以"T"或"A"开头的数据@Testpublic void testSql(){Session session = HibernateUtil.getInstance().getSession(); NativeQuery query = session.createNativeQuery("SELECT * FROM EMPLOYEE WHERE name LIKE ? OR name LIKE ?"); query.setParameter(0, "T%"); query.setParameter(1, "A%"); List<Object> l = query.list(); for(Object o : l){ System.out.println(Arrays.toString((Object[])o)); }session.close();}//使用hql 查询 employee 表中以"T"或"A"开头的数据@Testpublic void testHql(){Session session = HibernateUtil.getInstance().getSession();List<Employee> l = session.createQuery("SELECT e FROM Employee e WHERE e.name LIKE ? OR e.name LIKE ?").setParameter(0, "T%").setParameter(1, "A%").list();for(Employee e : l){System.out.println(e);}session.close();}@Testpublic void testCri(){Session session = HibernateUtil.getInstance().getSession();List<Employee> l = session.createCriteria(Employee.class).add(Restrictions.or(Restrictions.like("name", "T", MatchMode.START), Restrictions.like("name", "A", MatchMode.START))).list();for(Employee e : l){System.out.println(e);}session.close();}//分页@Testpublic void testPage(){int currentPage = 2;//第几页数据int pageSize = 3;//每页多少条数据Session session = HibernateUtil.getInstance().getSession();//limit ?, ?List<Employee> l = session.createQuery("FROM Employee").setFirstResult((currentPage-1)*pageSize).setMaxResults(pageSize).list();for(Employee e : l){System.out.println(e);}session.close();}//查询总数@Testpublic void testTotalCount(){Session session = HibernateUtil.getInstance().getSession();//select count(*) from employee//List l = session.createQuery("SELECT COUNT(e) FROM Employee e").list();//System.out.println(l.get(0));Long total = (Long) session.createQuery("SELECT COUNT(e) FROM Employee e").uniqueResult();System.out.println(total);session.close();}//查询所有员工的名字@Testpublic void testResult(){Session session = HibernateUtil.getInstance().getSession();List<String> l = session.createQuery("SELECT e.name FROM Employee e").list();System.out.println(l);session.close();}//查询所有员工的id, 名字(返回Object[]集合)@Testpublic void testResult2(){Session session = HibernateUtil.getInstance().getSession();List<Object[]> l = session.createQuery("SELECT e.id,e.name FROM Employee e").list();for(Object[] o : l){System.out.println(Arrays.toString(o));}session.close();}//查询员工的所属部门(Department)@Testpublic void testResult3(){Session session = HibernateUtil.getInstance().getSession();List<Department> l = session.createQuery("SELECT e.dept FROM Employee e").list();for(Department d : l){System.out.println(d);}session.close();}//查询员工所在部门的地址的街道@Testpublic void testResult4(){Session session = HibernateUtil.getInstance().getSession();List<String> l = session.createQuery("SELECT e.dept.address.street FROM Employee e").list();for(String s : l){System.out.println(s);}session.close();}/** * 结果集的包装 */@Testpublic void testResult5(){Session session = HibernateUtil.getInstance().getSession();List<Object[]> l = session.createQuery("SELECT e.id,e.name,e.dept.id,e.dept.name FROM Employee e").list();List<Object[]> l2 = session.createQuery("SELECT NEW LIST(e.id,e.name,e.dept.id,e.dept.name) FROM Employee e").list();List<Object[]> l3 = session.createQuery("SELECT NEW MAP(e.id,e.name,e.dept.id,e.dept.name) FROM Employee e").list();List<Object[]> l4 = session.createQuery("SELECT NEW MAP(e.id as eid,e.name as ename,e.dept.id as did,e.dept.name as dname) FROM Employee e").list();//可以在xxx.hbm.xml 文件中为 cd.itcast.day4.extend.ResultValue 取别名,如下://<import class="cd.itcast.day4.extend.ResultValue" rename="ResultValue"/>List<ResultValue> l5 = session.createQuery("SELECT NEW cd.itcast.day4.extend.ResultValue(e.id,e.name,e.dept.id,e.dept.name)").list();session.close();}/** * =============================== HQL 中的位置参数================ *///使用位置参数查询名字首字母是"T"或"A"的员工@Testpublic void testResult6(){Session session = HibernateUtil.getInstance().getSession();List<Employee> l = session.createQuery("SELECT e FROM Employee e WHERE e.name LIKE :param1 OR e.name LIKE :param2").setParameter("param1", "T%").setParameter("param2", "A%").list();session.close();}//查询所属部门为市场部和技术部的所有员工@Testpublic void testResult7(){Session session = HibernateUtil.getInstance().getSession();List<Employee> l = session.createQuery("SELECT e FROM Employee e WHERE e.dept.name in(:params)").setParameterList("params", new String[]{"市场部", "技术部"}).list();session.close();}//需求: 查询和某员工同一个部门的所有员工(不确定部门的主键是不是复合主键)//解决方案: 直接比较部门实体, 让hibernate 去拼接主键.@Testpublic void testResult8(){Session session = HibernateUtil.getInstance().getSession();Department d = session.get(Department.class, 1L);List<Employee> l = session.createQuery("SELECT e FROM Employee e WHERE e.dept = :param").setParameter("param", d).list();session.close();}