JPQL查询等查询方法

package zhang.jpa.test;

import java.util.Arrays;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import org.hibernate.ejb.QueryHints;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import zhang.jpa.helloword.Department;
import zhang.jpa.helloword.Employee;
import zhang.jpa.helloword.Item;
import zhang.jpa.helloword.Manager;
import zhang.jpa.helloword.Type;
import zhang.jpa.helloword.User;

public class Testonetoone {
	private EntityManagerFactory entityManagerFactory = null;
	private EntityManager entityManager = null;
	private EntityTransaction transaction = null;

	@Before
	public void init() {
		String persistenceUnitName = "jpa-1";
		entityManagerFactory = Persistence.createEntityManagerFactory(persistenceUnitName);
		entityManager = entityManagerFactory.createEntityManager();
		transaction = entityManager.getTransaction();
		transaction.begin();
	}

	@After
	public void after() {
		transaction.commit();
		entityManager.close();
		entityManagerFactory.close();
	}
	//更新
	@Test
	public void testjpqlupdate(){
		String jpql="update Employee set name=? where id=?";
		entityManager.createQuery(jpql).setParameter(1, "java").setParameter(2, 2).executeUpdate();
	}
	//删除
	@Test
	public  void testjpqldelete(){
		String jpql="delete from Employee e where e.id=?";
		entityManager.createQuery(jpql).setParameter(1, 1).executeUpdate();
	}
	@Test
	public void testjpqlfunction(){
		String jpql="select upper(e.name),upper(e.email),email from Employee e";
		List<Object[]> list = entityManager.createQuery(jpql).getResultList();
		for(Object[] objs: list){
			System.out.println(Arrays.asList(objs));
		}
	}
	// jpql查询还可以支持子查询
	@Test
	public void jpqlSubquery(){
		//查询name是AA的所在的部门
		String sql="select e from Employee e where department="
				+ " (select d from Department d where d.departmentId=?)";
		Query query= entityManager.createQuery(sql).setParameter(1, 1);
		List list = query.getResultList();
		System.out.println(list);
		
	}

	// 关联查询,使用left outer join fetch,返回的是一个对象的集合,并且将每一个对象的集合中的进行了初始胡
	// 如果没有fetch的话,返回的是List<Object[]>
	@Test
	public void testleftjoinfetch() {
		String jpql = "from Department d left outer join fetch d.employees where d.departmentId=? ";
		Department department = (Department) entityManager.createQuery(jpql).setParameter(1, 1).getSingleResult();
		System.out.println(department.getDepartmentName());
		System.out.println(department.getEmployees().size());

		// String jpql2="from Department d left outer join d.employees where
		// d.departmentId=? ";
		// List<Object[]> list=entityManager.createQuery(jpql2).setParameter(1,
		// 1).getResultList();
		// for(Object[] objs:list){
		// System.out.println((Employee)(Arrays.asList(objs).get(1)));
		// }

	}

	// 同样支持group by,count,avg等集聚函数
	@Test
	public void testgroupby() {
		String jpql = "select count(*) from User group by id ";
		List list = entityManager.createQuery(jpql).getResultList();
		System.out.println(list);
	}

	// 在jpql语句中支持order by
	@Test
	public void testorderby() {
		String jpql = "from User where id=1 order by id desc";
		User user = (User) entityManager.createQuery(jpql).getSingleResult();
		System.out.println(user);
	}

	// 设置查询缓存
	@Test
	public void querycache() {
		String jpql = "from User u where u.id=?";
		Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true).setParameter(1, 1);
		User user = (User) query.getSingleResult();

		query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true).setParameter(1, 1);
		user = (User) query.getSingleResult();

	}

	// 本地sql
	@Test
	public void testnativesql() {
		String jpql = "select last_name from jpa_user where id=1";
		List list = entityManager.createNativeQuery(jpql).getResultList();
		System.out.println(list);
	}

	@Test
	public void testnamedquery() {
		Query query = entityManager.createNamedQuery("testnamedquery").setParameter("id", 3);
		List emails = query.getResultList();
		System.out.println(emails);
	}

	// 获取部分属性,也可以获取部分属性组成的对象
	@Test
	public void testgetfields() {
		String jpql = "select new User(username,email) from User u where u.id>:id";
		Query query = entityManager.createQuery(jpql).setParameter("id", 5);
		// List<Object[]> list = query.getResultList();
		List<User> users = query.getResultList();
		for (User user : users) {
			System.out.println(user);
		}
	}

	// 注意获取的索引是从一开始
	@Test
	public void testhellojpql() {
		String jpql = "from User u where u.id=?";
		Query query = entityManager.createQuery(jpql).setParameter(1, 1);
		User user = (User) query.getSingleResult();
		System.out.println(user);
	}

	// 二级缓存
	@Test
	public void testsecondlevelcache() {
		Employee employee1 = entityManager.find(Employee.class, 1);

		transaction.commit();
		entityManager.close();

		entityManager = entityManagerFactory.createEntityManager();
		transaction = entityManager.getTransaction();
		transaction.begin();

		Employee employee2 = entityManager.find(Employee.class, 1);
	}

	// 删除操作
	@Test
	public void testmanytomanydelete() {
		// Item item=entityManager.find(Item.class, 2);
		// entityManager.remove(item);
		Type type = entityManager.find(Type.class, 1);
		entityManager.remove(type);
	}

	// 更新
	@Test
	public void testupdateonetoone() {
		Item item = entityManager.find(Item.class, 1);
		System.out.println(item);
		item.setItemName("item_shoe");
		item.getTypes().iterator().next().setTypeName("mytype");
	}

	// 默认使用懒加载的查询策略(查询维护关联关系的一方)(查询不维护关联关系的一方)
	@Test
	public void testmanytomanyget2() {
		Type type = entityManager.find(Type.class, 1);
		System.out.println(type.getTypeName());
		System.out.println(type.getItems().size());
	}

	// 默认使用懒加载的查询策略(查询维护关联关系的一方)
	@Test
	public void testmanytomanyget() {
		Item item = entityManager.find(Item.class, 1);
		System.out.println(item.getItemName());

		System.out.println(item.getTypes().size());
	}

	// 多对对的关联关系关系,.在进行查询操作的时候,默认是使用懒加载的策略,无论是查询多的一方还是查询多的一方
	@Test
	public void testmanytomanysave() {
		Item item = new Item();
		item.setItemName("item1");

		Item item2 = new Item();
		item2.setItemName("item2");

		Type type1 = new Type();
		type1.setTypeName("typeAA");

		Type type2 = new Type();
		type2.setTypeName("TypeBB");
		// 设置关联关系
		item.getTypes().add(type1);
		item.getTypes().add(type2);
		item2.getTypes().add(type1);
		item2.getTypes().add(type2);

		type1.getItems().add(item);
		type1.getItems().add(item2);
		type2.getItems().add(item2);
		type1.getItems().add(item);
		// 保存操作
		entityManager.persist(item);
		entityManager.persist(item2);
		entityManager.persist(type1);
		entityManager.persist(type2);

	}

	// 双向一对一默认查询维护关联关系的一方时,会使用左外连接,将另外一方的记录一起获取
	// 可以使用@OneToOne(fetch=FetchType.LAZY),来修改查询策略,一般不进行修改
	@Test
	public void testonetoonebothget2() {
		Department department = entityManager.find(Department.class, 1);
		System.out.println(department.getDepartmentName());
		System.out.println(department.getManager().getClass());
	}

	// 双向一对一默认查询不维护关联关系的一方时,会使用左外连接,将另外一方的记录一起获取
	@Test
	public void testonetoonebothget() {
		Manager manager = entityManager.find(Manager.class, 1);
		System.out.println(manager.getManagerName());
		System.out.println(manager.getDepartment().getDepartmentName());
	}

	@Test
	public void testonetooesave() {
		Department department = new Department();
		department.setDepartmentName("IT");

		Manager manager = new Manager();
		manager.setManagerName("zhangyukangmanager");

		department.setManager(manager);
		manager.setDepartment(department);

		entityManager.persist(manager);
		entityManager.persist(department);
	}

	@Test
	public void testsaveemployee() {
		Employee employee = new Employee("AA", 45, "aa@qq.com", "13525", entityManager.find(Department.class, 1));
		entityManager.persist(employee);
	}

}