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]);
}
}
}