Hibernate_多种查询模式介绍

package com.sun.test;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Projections;
import org.junit.After;
import org.junit.Before;

import com.sun.dao.impl.HibernateSessionFactory;
import com.sun.entity.District;
import com.sun.entity.House;
import com.sun.entity.Street;
@SuppressWarnings("unchecked")
public class TestHib {

	private Session session;

	@Before
	public void before() {
		session = HibernateSessionFactory.getSession();
	}

	@After
	public void after() {
		session.close();
	}

	@org.junit.Test
	public void queryBase() {
		List<House> list = session.createQuery(
				"select h from House h where price>2000").list();
		for (House h : list) {
			System.out.println(h.getTitle());
		}
	}

	// 查询部分属性
	@org.junit.Test
	public void queryPro() {
		List<Object[]> list = session.createQuery(
				"select h.title,h.price from House h where price>2000").list();
		for (Object[] h : list) {
			System.out.println(h[0] + "=========" + h[1]);
		}
	}

	@org.junit.Test
	public void queryPro1() {
		// 要求：一定要有对应的构造方法
		List<House> list = session
				.createQuery(
						"select new House(h.title,h.price) from House h where price>2000")
				.list();
		for (House h : list) {
			System.out.println(h.getTitle());
		}
	}

	@org.junit.Test
	public void queryPro2() {

		List<Map> list = session
				.createQuery(
						"select new Map(h.title as t,h.price as p) from House h where price>2000")
				.list();
		for (Map h : list) {
			System.out.println(h.get("t") + "--------" + h.get("p"));
		}
	}

	@org.junit.Test
	public void queryFun() {
		// 包装类
		int count = (Integer) session.createQuery(
				"select count(*) from House h where price>2000").uniqueResult();
		System.out.println(count);

	}

	@SuppressWarnings("unchecked")
	@org.junit.Test
	public void queryParam() {

		List<House> list = session.createQuery(
				"select h from House h where price>:price1 and price <:price2")
				.setLong("price1", 2000l).setLong("price2", 10000l).list();
		for (House h : list) {
			System.out.println(h.getTitle());
		}

	}

	@SuppressWarnings("unchecked")
	@org.junit.Test
	public void queryParam1() {
		// 参数名和属性名相同
		Query query = session
				.createQuery("select h from House h where h.title like :title");
		House house = new House();
		house.setTitle("%江%");
		query.setProperties(house);
		List<House> list = query.list();
		for (House h : list) {
			System.out.println(h.getTitle());
		}

	}

	/**
	 * 1.如果没有对应的数据,load会异常 2.load支持延时加载
	 * 
	 */
	@org.junit.Test
	public void load() {
		House house = (House) session.load(House.class, 1L);
		System.out.println(house.getTitle());
	}

	@org.junit.Test
	public void get() {
		House house = (House) session.get(House.class, 1L);
		System.out.println(house.getTitle());
	}

	// 默认是select(生成两条sql)
	// fetch="join"生成一条outer left join语句
	@org.junit.Test
	public void fetch() {
		House house = (House) session.get(House.class, 1L);
		System.out.println(house.getTitle());
		System.out.println(house.getUsers().getName());
	}

	@org.junit.Test
	public void lazySet() {
		District district = (District) session.get(District.class, 1L);
		System.out.println(district.getName());

		Set<Street> set = district.getStreets();
		Iterator<Street> it = set.iterator();// 迭代器
		while (it.hasNext()) {
			Street s = it.next();
			System.out.println(s.getName());
		}
		// for(Street s:set){
		// System.out.println(s.getName());
		// }

	}

	/**
	 * list不从缓存取数据，iterate根据id先找缓存中是否存在数据，数据没有再查数据库
	 * 
	 */
	@SuppressWarnings("unchecked")
	@org.junit.Test
	public void iterateAndList() {
		Query query = session.createQuery("select h from  House h");

		// List<House> list=query.list();
		// for(House s:list){
		// System.out.println("title==="+s.getTitle());
		// System.out.println("name==="+s.getUsers().getName());
		// }

		System.out.println("=======================");
		Iterator<House> it = query.iterate();

		while (it.hasNext()) {
			House s = it.next();
			System.out.println(s.getTitle());
			System.out.println("name===" + s.getUsers().getName());
		}

	}

	@org.junit.Test
	public void iterate() {
		Query query = session.createQuery("select h from  House h");

		Iterator<House> it = query.iterate();

		while (it.hasNext()) {
			House s = it.next();
			System.out.println(s.getTitle());
		}
		System.out.println("=======================");
		it = query.iterate();

		while (it.hasNext()) {
			House s = it.next();
			System.out.println(s.getTitle());
		}
	}
	@org.junit.Test
	public void manyTableJoin() {
		Session session = HibernateSessionFactory.getSession();
		Query query = session
				.createQuery("select h from  House h inner join fetch h.street s inner join fetch  s.district");
		List<House> list = query.list();
		House house = list.get(0);
		System.out.println("=======================" + house.getTitle());

		session.close();
		System.out.println(house.getStreet().getName() + "==="
				+ house.getStreet().getDistrict().getName());
	}

	@org.junit.Test
	public void innerJoin() {
		// select d,s from District d inner join d.streets s
		Query query = session
				.createQuery("from District d inner join d.streets");
		List<Object[]> list = query.list();
		for (Object[] o : list) {
			District district = (District) o[0];
			Street street = (Street) o[1];
			System.out.println(district.getName() + "===" + street.getName());
		}

	}

	@org.junit.Test
	public void innerFetchJoin() {

		Query query = session
				.createQuery("from District d inner join fetch d.streets");
		List<District> list = query.list();
		for (District d : list) {
			System.out.println(d.getName());
		}

	}

	@org.junit.Test
	public void leftJoin() {
		// select d,s from District d inner join d.streets s
		Query query = session
				.createQuery("from District d right join fetch d.streets");
		List list = query.list();
		System.out.println(list);
//		for (Object[] o : list) {
//			District district = (District) o[0];
//			Street street = (Street) o[1];
//			System.out.println(district.getName() + "===" + street.getName());
//		}

	}
   
	@org.junit.Test
	public void queryByName() {
		Query query=session.getNamedQuery("queryStreet");
		List<Street> list=query.setString("name", "%北%").list();
		for(Street s:list){
			System.out.println(s.getName()+"==="+s.getDistrict().getName());
		}
	}
	
	//直接加addEntity就可以转换类型了

	@org.junit.Test
	public void queryBySQL() {
		//返回类型addEntity
		List<Street> list=session.createSQLQuery("select * from street").addEntity(Street.class).list();
		System.out.println(list);
		for(Street s:list){
			System.out.println(s.getName()+"==="+s.getDistrict().getName());
		}
	}
	
	//在类得映射文件中配置

	<sql-query name="querySql">
     <![CDATA[
         select {s.*} from street s where name like :name
       ]]>
       <return alias="s" class="com.accp.entity.Street"></return>
    </sql-query>
    <sql-query name="querySqlByName">
     <![CDATA[
         select s.name from street s where name like :name
       ]]>
	<!--       <return alias="s" class="com.accp.entity.Street"></return>-->
      <return-scalar column="name" type="java.lang.String"/>
    </sql-query>

	@org.junit.Test
	public void queryBySQLXML() {
		Query query=session.getNamedQuery("querySql");
		List<Street> list=query.setString("name", "%北%").list();
		for(Street s:list){
			System.out.println(s.getName()+"==="+s.getDistrict().getName());
		}
	}

	@org.junit.Test
	public void queryBySQLXMLByName() {
		Query query=session.getNamedQuery("querySqlByName");
		List<String> list=query.setString("name", "%北%").list();
		for(String s:list){
			System.out.println(s);
		}
	}
	

	@org.junit.Test
	public void projections() {
		Criteria c=session.createCriteria(House.class);
		int count=(Integer)c.setProjection(Projections.rowCount()).uniqueResult();
		System.out.println(count);
		
	}
	@org.junit.Test
	public void projections1() {
		Criteria c=session.createCriteria(House.class);
		List<Object[]> list=c.setProjection(Projections.projectionList().add(Projections.avg("price"))
	    .add(Projections.groupProperty("title")).add(Projections.rowCount())).list();
		for(Object[] obj:list){
			System.out.println(obj[0]+"==="+obj[1]);
		}
	}
}