hibernate常用表查詢操作（HQL）

代碼如下，其中Customer為實體類

@Test
/**
 * HQL的簡單查詢
 */
public void demo2() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	// 簡單的查詢
	Query query = session.createQuery("from Customer");
	List<Customer> list = query.list();

	// sql中支援*号的寫法：select * from cst_customer; 但是在HQL中不支援*号的寫法。
	/*
	 * Query query = session.createQuery("select * from Customer");// 報錯
	 * List<Customer> list = query.list();
	 */

	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}

@Test
/**
 * 别名查詢
 */
public void demo3() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	// 别名的查詢
	/*
	 * Query query = session.createQuery("from Customer c"); List<Customer>
	 * list = query.list();
	 */

	Query query = session.createQuery("select c from Customer c");
	List<Customer> list = query.list();

	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}

@Test
/**
 * 排序查詢
 */
public void demo4() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	// 排序的查詢
	// 預設情況
	// List<Customer> list = session.createQuery("from Customer order by
	// cust_id").list();
	// 設定降序排序 升序使用asc 降序使用desc
	List<Customer> list = session.createQuery("from Customer order by cust_id desc").list();

	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}

@Test
/**
 * 條件查詢
 */
public void demo5() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	// 條件的查詢
	// 一、按位置綁定：根據參數的位置進行綁定。
	// 一個條件
	/*
	 * Query query = session.createQuery("from Customer where cust_name = ?"
	 * ); query.setParameter(0, "李兵"); List<Customer> list = query.list();
	 */

	// 多個條件
	/*
	 * Query query = session.createQuery(
	 * "from Customer where cust_source = ? and cust_name like ?");
	 * query.setParameter(0, "小廣告"); query.setParameter(1, "李%");
	 * List<Customer> list = query.list();
	 */

	// 二、按名稱綁定
	Query query = session.createQuery("from Customer where cust_source = :aaa and cust_name like :bbb");
	// 設定參數:
	query.setParameter("aaa", "朋友推薦");
	query.setParameter("bbb", "李%");
	List<Customer> list = query.list();

	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}

@Test
/**
 * 投影查詢
 */
public void demo6() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();

	// 投影查詢
	// 單個屬性
	/*
	 * List<Object> list = session.createQuery(
	 * "select c.cust_name from Customer c").list(); for (Object object :
	 * list) { System.out.println(object); }
	 */

	// 多個屬性:
	/*
	 * List<Object[]> list = session.createQuery(
	 * "select c.cust_name,c.cust_source from Customer c").list(); for
	 * (Object[] objects : list) {
	 * System.out.println(Arrays.toString(objects)); }
	 */

	// 查詢多個屬性，但是我想封裝到對象中。
	List<Customer> list = session.createQuery("select new Customer(cust_name,cust_source) from Customer").list();
	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}

@Test
/**
 * 分頁查詢
 */
public void demo7() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();

	// 分頁查詢
	Query query = session.createQuery("from LinkMan");
	query.setFirstResult(20);
	query.setMaxResults(10);
	List<LinkMan> list = query.list();

	for (LinkMan linkMan : list) {
		System.out.println(linkMan);
	}
	tx.commit();
}

@Test
/**
 * 分組統計查詢
 */
public void demo8() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();

	// 聚合函數的使用：count(),max(),min(),avg(),sum()
	Object object = session.createQuery("select count(*) from Customer").uniqueResult();
	System.out.println(object);
	// 分組統計：
	List<Object[]> list = session.createQuery("select cust_source,count(*) from Customer group by cust_source")
			.list();
	for (Object[] objects : list) {
		System.out.println(Arrays.toString(objects));
	}
	tx.commit();
}

@Test
/**
 * HQL的多表查詢
 */
public void demo9() {
	Session session = HibernateUtils.getCurrentSession();
	Transaction tx = session.beginTransaction();
	// SQL:SELECT * FROM cst_customer c INNER JOIN cst_linkman l ON
	// c.cust_id = l.lkm_cust_id;
	// HQL:内連接配接 from Customer c inner join c.linkMans
	/*
	 * List<Object[]> list = session.createQuery(
	 * "from Customer c inner join c.linkMans").list(); for (Object[]
	 * objects : list) { System.out.println(Arrays.toString(objects)); }
	 */

	// HQL:迫切内連接配接 其實就在普通的内連接配接inner join後添加一個關鍵字fetch. from Customer c inner
	// join fetch c.linkMans
	List<Customer> list = session.createQuery("select distinct c from Customer c inner join fetch c.linkMans")
			.list();// 通知hibernate，将另一個對象的資料封裝到該對象中

	for (Customer customer : list) {
		System.out.println(customer);
	}
	tx.commit();
}