package com.baidu.test;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.baidu.leftJoin.Department;
import com.baidu.leftJoin.Employee;
public class TestHQL_LeftJoin {
	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;
	@Before
	  public void init(){
		Configuration configuration = new Configuration().configure();
		ServiceRegistry serviceRegistry = new ServiceRegistryBuilder()
		                    .applySettings(configuration.getProperties())
		                    .buildServiceRegistry();
		sessionFactory = configuration.buildSessionFactory(serviceRegistry);
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}
	@After
	  public void destroy(){
		transaction.commit();
		session.close();
		sessionFactory.close();
	}
	// ~~~~~~~~~~~~~~~~~~~~~~~~~~下面的例子是 從 1 對(duì) 多  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	/**
   *
   * 迫切左外連接: 特點(diǎn)是：如果左表有不滿足條件的，也返回左表不滿足條件
   *    1. LEFT JOIN FETCH 關(guān)鍵字表示迫切左外連接檢索策略.
   *    2. list() 方法返回的集合中存放實(shí)體對(duì)象的引用, 每個(gè) Department 對(duì)象關(guān)聯(lián)的 Employee 集合都被初始化,
   *       存放所有關(guān)聯(lián)的 Employee 的實(shí)體對(duì)象.
   *    3. 查詢(xún)結(jié)果中可能會(huì)包含重復(fù)元素, 可以通過(guò)一個(gè) HashSet 來(lái)過(guò)濾重復(fù)元素
   *
   *     去重：
   *       方法一:使用 distinct
   *         String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.emps ";
   *        Query query = session.createQuery(hql);
   *
   *        List<Department> depts = query.list();
   *        System.out.println(depts.size());
   *       
   *       方法二
   *         String hql = "FROM Department d LEFT JOIN FETCH d.emps ";
   *        Query query = session.createQuery(hql);
   *
   *        List<Department> depts = query.list();
   *
   *        depts = new ArrayList<>(new LinkedHashSet(depts));
   *        System.out.println(depts.size());
   *        
   *        for(Department dept:depts){
   *          System.out.println(dept.getName() + "--" + dept.getEmps().size() );
   *        }
   *
   *
   */
	@Test
	  public void testLeftJoinFetch(){
		//    String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.emps ";
		//    Query query = session.createQuery(hql);
		//    
		//    List<Department> depts = query.list();
		//    System.out.println(depts.size());
		//    
		String hql = "FROM Department d LEFT JOIN FETCH d.emps ";
		Query query = session.createQuery(hql);
		List<Department> depts = query.list();
		System.out.println(depts.size());
		depts = new ArrayList<>(new LinkedHashSet(depts));
		System.out.println(depts.size());
		for (Department dept:depts){
			System.out.println(dept.getName() + "--" + dept.getEmps().size() );
		}
	}
	/**
   * 左外連接:
   *    1. LEFT JOIN 關(guān)鍵字表示左外連接查詢(xún).
   *    2. list() 方法返回的集合中存放的是對(duì)象數(shù)組類(lèi)型
   *    3. 根據(jù)配置文件來(lái)決定 Employee 集合的檢索策略.
   *    4. 如果希望 list() 方法返回的集合中僅包含 Department 對(duì)象,
   *      可以在HQL 查詢(xún)語(yǔ)句中使用 SELECT 關(guān)鍵字
   *    
   *    這樣的語(yǔ)句查詢(xún)的結(jié)果有重復(fù)：
   *      String hql = "FROM Department d LEFT JOIN d.emps";
   *      Query query = session.createQuery(hql);
   *    
   *      List<Object[]> results = query.list();
   *      System.out.println(results.size());
   *  
   *     去重：
   *       僅能使用 distinct 的方法去除重復(fù)
   *   
   *       String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";
   *       Query query = session.createQuery(hql);
   *
   *       List<Department> depts = query.list();
   *       System.out.println(depts.size());
   *         
   *       for(Department dept:depts){
   *         System.out.println(dept.getName() + dept.getEmps().size());
   *       }
   *
   */
	@Test
	  public void testLeftJoin(){
		String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";
		Query query = session.createQuery(hql);
		List<Department> depts = query.list();
		System.out.println(depts.size());
		for (Department dept:depts){
			System.out.println(dept.getName() + dept.getEmps().size());
		}
	}
	/**
   * 迫切內(nèi)連接: 特點(diǎn)是：不返回左表不滿足條件
   *    INNER JOIN FETCH 關(guān)鍵字表示迫切內(nèi)連接, 也可以省略 INNER 關(guān)鍵字
   *    list() 方法返回的集合中存放 Department 對(duì)象的引用, 每個(gè) Department
   *        對(duì)象的 Employee 集合都被初始化, 存放所有關(guān)聯(lián)的 Employee 對(duì)象
   *
   * 內(nèi)連接:
   *    INNER JOIN 關(guān)鍵字表示內(nèi)連接, 也可以省略 INNER 關(guān)鍵字
   *    list() 方法的集合中存放的每個(gè)元素對(duì)應(yīng)查詢(xún)結(jié)果的一條記錄, 每個(gè)元素都是對(duì)象數(shù)組類(lèi)型
   *    如果希望 list() 方法的返回的集合僅包含 Department 對(duì)象, 可以在 HQL 查詢(xún)語(yǔ)句中使用 SELECT 關(guān)鍵字
   *
   *
   *
   */
	@Test
	  public void testInnerJoinFetch(){
		//String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.emps ";
		String hql = "FROM Department d INNER JOIN FETCH d.emps ";
		Query query = session.createQuery(hql);
		List<Department> depts = query.list();
		depts = new ArrayList<>(new LinkedHashSet(depts));
		System.out.println(depts.size());
		for (Department dept:depts){
			System.out.println(dept.getName() + "--" + dept.getEmps().size() );
		}
	}
	// ~~~~~~~~~~~~~~~~~~~~~~~~~~下面的例子是 從多 對(duì) 1  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	@Test
	  public void testLeftJoinFetch2(){
		String hql = "FROM Employee e LEFT JOIN FETCH e.dept";
		Query query = session.createQuery(hql);
		List<Employee> emps = query.list();
		System.out.println(emps.size());
		for (Employee emp:emps){
			System.out.println(emp + " -- " + emp.getDept());
		}
	}
}