package com.test;

import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

import com.pojo.Address;
import com.pojo.User;
import com.util.HibernateUtil;

public class Test {
	public static void main(String[] args){

		Session session = HibernateUtil.getSesion();
		Transaction tran = session.beginTransaction();


		//添加一条记录
		User user = new User();
		user.setName("jerry");
		user.setPassword("100");
		session.save(user);
		user.setPassword("***");
		tran.commit();


		/**
		 * 使用hql查询
		 */

		//查询一条记录
		//方法一：
		User user = (User) session.get(User.class, 1);
		//方法二：
		String hql = "select name,password from User where id = ?";
		Query query = session.createQuery(hql);
		query.setParameter(0, 1);
		Object[] str =  (Object[]) query.uniqueResult();
		for(Object s:str){
			System.out.println(s);
		}

		/*//查询所有记录
		String hql = "from User";
		Query query = session.createQuery(hql);
		List<User> list = query.list();
		for(User u:list){
			System.out.println(u.getName());
		}*/

		 //分页显示
		String hql = "from User";
		Query query = session.createQuery(hql);
		query.setFirstResult(0);
		query.setMaxResults(2);

		List<User> list = query.list();
		for(User u:list){
			System.out.println(u.getName());
		}

		/**
		 * Criteria查询
		 */
		Criteria c = session.createCriteria(User.class);

		//返回所有
		List<User> list = c.list();
		for(User u:list){
			System.out.println(u.getName());
		}


		//where
		//查询name为tom且id为1的
		c.add(Restrictions.eq("name", "tom"));
		c.add(Restrictions.eq("id", 1));
		User user = (User) c.uniqueResult();
		System.out.println(user.getName());

		//查询name为tom或jerry的
		c.add(Restrictions.or(Restrictions.eq("name", "tom"), Restrictions.eq("name", "jerry")));
		List<User> list = c.list();
		for(User u:list){
			System.out.println(u.getName());
		}

		//分页显示
		c.setFirstResult(0);
		c.setMaxResults(2);
		List<User> list = c.list();
		for(User u:list){
			System.out.println(u.getName());
		}


	   //分组与统计
		//统计id列的和
	    c.setProjection(Projections.sum("id"));
	    Object obj = c.uniqueResult();
	    System.out.println(obj);

		//分组查询
		c.setProjection(Projections.groupProperty("name"));
		//Hibernate: select this_.name as y0_ from user this_ group by this_.name
		List<String> list = c.list();
		for(String u:list){
			System.out.println(u);
		}

		ProjectionList list = Projections.projectionList();
		list.add(Projections.max("id"));
		list.add(Projections.min("id"));
		//Hibernate: select max(this_.id) as y0_, min(this_.id) as y1_ from user this_
		c.setProjection(list);
		Object[] obj = (Object[]) c.uniqueResult();
		for(Object s:obj){
			System.out.println(s);
		}


		//排序
		c.addOrder(Order.desc("id"));
		List<User> list = c.list();
		for(User u:list){
			System.out.println(u.getName());
		}


		/**
		 * 使用原生sql查询
		 */
		String sql = "select id,name,password from user";
		//方法一：
		SQLQuery query = session.createSQLQuery(sql);
		List list = query.list();
		for(Object obj:list){
			Object[] rows = (Object[]) obj;
			System.out.println("id:"+rows[0]+"name:"+rows[1]+"password:"+rows[2]);
		}
		//方法二：
		SQLQuery query = session.createSQLQuery(sql).addEntity(User.class);
		List<User> list = query.list();


		tran.commit();
	}

}