Hibernate——笔记04

Hibernate的查询方式

1. 对象导航查询
2. OID查询
3. HQL单表查询
   1）查询所有
   2）条件查询
   3）排序查询
   4）分页查询
   5）投影查询
   6）聚合函数查询
4. QBC查询
   1）查询所有
   2）条件查询
   3）排序查询
   4）分页查询
   5）统计查询（实质是聚合函数查询，封装成了具体的方法实现）
   6）离线查询
5. HQL多表查询
   1）内连接查询
   2）迫切内连接查询
   3）左（右）外连接查询
   4）迫切左（右）外连接查询

对象导航查询

/** * 对象导航查询 */
	@Test
	public void fun1(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Customer customer = session.get(Customer.class, 1);
			Set<LinkMan> setLinkMan = customer.getSetLinkMan();
			System.out.println(setLinkMan.size());
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

OID查询

/** * OID查询 */
	@Test
	public void fun2(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Customer customer = session.get(Customer.class, 1);
			System.out.println(customer);
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

HQL单表查询

查询所有

/** * hql查询（所有） */
	@Test
	public void fun1(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("from Customer");
			List<Customer> list = query.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" +  customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

条件查询

/** * hql查询（条件查询） */
	@Test
	public void fun2(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("from Customer where cid=?");
			// 给参数设值(起始位置从0开始)
			query.setParameter(0, 1);
			List<Customer> list = query.list();
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

模糊查询

/** * hql查询（模糊查询） */
	@Test
	public void fun3(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("from Customer where custName like ?");
			// 给参数设值(起始位置从0开始)
			query.setParameter(0, "%D%");
			List<Customer> list = query.list();
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

排序查询

/** * hql查询（排序查询） */
	@Test
	public void fun4(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			// 默认的排序方法（升序）
// Query query = session.createQuery("from Customer order by cid asc");
			// 降序
			Query query = session.createQuery("from Customer order by cid desc");
			List<Customer> list = query.list();
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

分页查询

/** * hql查询（分页查询） */
	@Test
	public void fun5(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("from Customer");
			//设置分页的起始位置
			query.setFirstResult(0);
			//设置分页每页的最大数
			query.setMaxResults(2);
			List<Customer> list = query.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

投影查询

/** * hql查询（投影查询） * 就是查询具体的值，不是全部数据，如：select custName from Customer */
	@Test
	public void fun6(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("select custName from Customer");
			
			// 注意：返回的是Object对象，更正确的是返回的是String类型
			List<Object> list = query.list();
			
			for (Object object : list) {
				System.out.println(object);
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

聚集函数查询

/** * hql查询（聚集函数的查询） * 常用的聚集函数：count、max、min、avg、sum */
	@Test
	public void fun7(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Query query = session.createQuery("select count(*) from Customer");
			Object object = query.uniqueResult();
			
			System.out.println(object);
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

QBC查询

查询所有

/** * 查询所有 */
	@Test
	public void fun1(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			List<Customer> list = criteria.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

条件查询

/** * 按条件查询 */
	@Test
	public void fun2(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			criteria.add(Restrictions.eq("cid", 1));
			
			List<Customer> list = criteria.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

模糊查询

/** * 按条件查询(模糊查询) */
	@Test
	public void fun3(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			criteria.add(Restrictions.like("custName", "%D%"));
			
			List<Customer> list = criteria.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

排序查询

/** * 排序查询 */
	@Test
	public void fun4(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			//默认升序
// criteria.addOrder(Order.asc("cid"));
			//降序
			criteria.addOrder(Order.desc("cid"));
			List<Customer> list = criteria.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

分页查询

/** * 分页查询 */
	@Test
	public void fun5(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			
			criteria.setFirstResult(0);
			criteria.setMaxResults(2);
			
			List<Customer> list = criteria.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCid() + "::" + customer.getCustName());
			}
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

统计

/** * 统计 */
	@Test
	public void fun6(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
			Criteria criteria = session.createCriteria(Customer.class);
			
			criteria.setProjection(Projections.rowCount());
// criteria.setProjection(Projections.max(""));
// criteria.setProjection(Projections.min(""));
// criteria.setProjection(Projections.sum(""));
			Object object = criteria.uniqueResult();
			System.out.println(object);
			

			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

离线查询

/** * 离线查询 */
	@Test
	public void fun7(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
		
// Criteria criteria = session.createCriteria(Customer.class);
			DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class);
			
			//执行时调用session
			Criteria criteria = detachedCriteria.getExecutableCriteria(session);
			List<Customer> list = criteria.list();
			for (Customer customer : list) {
				System.out.println(customer.getCustName());
			}
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

HQL多表查询

内连接查询

	/** * 内连接查询 */
	@Test
	public void fun1(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
			
			/* * 等同于sql语句 * select * from t_customer c inner join t_linkman l on c.cid = l.clid; * select * from t_customer c,t_linkman l where c.cid = l.clid; */
			Query query = session.createQuery("from Customer c inner join c.setLinkMan");
			
			//返回的其实是Object[]数组，下面的迫切内连接就是让他返回的是Customer
			List<Object[]> list = query.list();
// for (Object[] object : list) {
// System.out.println(Arrays.toString(object));
// }
			
			System.out.println(list.size());
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

迫切内连接查询

	/** * 迫切内连接查询(与上面唯一的不同就是得到的list集合内的数据类型不同) */
	@Test
	public void fun2(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
			
			/* * 等同于sql语句 * select * from t_customer c inner join t_linkman l on c.cid = l.clid; * select * from t_customer c,t_linkman l where c.cid = l.clid; */
			Query query = session.createQuery("from Customer c inner join fetch c.setLinkMan");
			
			List<Customer> list = query.list();
			
			for (Customer customer : list) {
				System.out.println(customer.getCustName());
			}
			
			System.out.println(list.size());
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

左（右）外连接查询

/** * 左外连接查询 */
	@Test
	public void fun3(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
			
			/* * select * from t_customer c left outer join t_linkmna l on c.cid = l.clid; */
			Query query = session.createQuery("from Customer c left outer join c.setLinkMan");
			List list = query.list();
			
			System.out.println(list.size());
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}

迫切左（右）外连接查询

/** * 迫切左外连接查询 */
	@Test
	public void fun4(){
		Session session = null;
		SessionFactory sessionFactory = null;
		Transaction transaction = null;
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.openSession();
			transaction = session.beginTransaction();
			
			/* * select * from t_customer c left outer join t_linkmna l on c.cid = l.clid; */
			Query query = session.createQuery("from Customer c left outer join fetch c.setLinkMan");
			List list = query.list();
			
			System.out.println(list.size());
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		}finally{
			session.close();
			sessionFactory.close();
		}
	}