package com.data.oper;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;

import com.data.model.User;
import com.utils.HibernateUtil;

public class UserOper {

	private Logger log = Logger.getLogger(UserOper.class);
	private int pageSize = 3;
	
	public void add(User u){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return;
		}
		//只有放在事務(wù)中才能反映到數(shù)據(jù)庫(kù)中
		System.out.print("UserOper"+  u);
		Transaction tx = session.beginTransaction();
		session.save(u);
		tx.commit();
		
		HibernateUtil.closeSession();
	}
	
	//該對(duì)象至少需要包含關(guān)鍵字值
	public void delete(User u){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return;
		}
		Transaction tx = session.beginTransaction();
		session.delete(u);
		tx.commit();
		
		HibernateUtil.closeSession();
	}
	
	public void delete(int id){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return;
		}
		//1）類(lèi)名要寫(xiě)完整包路徑？不一定，寫(xiě)不寫(xiě)都可以的，但大小寫(xiě)是敏感的
		//2）這個(gè)一個(gè)HQL查詢（Hibernate Query Language）
		//3）在select語(yǔ)句時(shí)，可以不要select子句
		
		Transaction tx = session.beginTransaction();
		Query q = session.createQuery("delete from com.data.model.User where id="+id);
//		Query q = session.createQuery("delete from User where id="+id);
		q.executeUpdate();
		
		tx.commit();
		
		HibernateUtil.closeSession();
	}
	
	//要求u中必需包含關(guān)鍵字值
	public void update(User u){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return;
		}

		Transaction tx = session.beginTransaction();
		session.update(u);
		tx.commit();
		
		HibernateUtil.closeSession();
	}
	
	//兩種查詢方法1）HQL；2）Criteria Query
	public List queryAll(){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return null;
		}

		List list = new ArrayList();

		Query q = session.createQuery("from com.data.model.User");//com.data.model.User均可
		list = q.list();
		
		Criteria c = session.createCriteria(User.class);
//		c.addOrder(Order.asc("id"));// 按ID排序
		list = c.list();
		
		Iterator i = list.iterator();
		while(i.hasNext()){
			User s = (User) i.next();
			log.debug(s.getName()+" " + s.getSex());
		}

		return list;
	}
	
	//分頁(yè)查詢
	public List queryPage(int pageNo){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return null;
		}

		List list = new ArrayList();

		int begin = pageSize * (pageNo - 1);
		int end = pageSize * pageNo;
		
		// 不能實(shí)現(xiàn)
//		Query q = session.createQuery("from hibernate.model.Stu limit " + begin + "," + end);
//		l = q.list();
		
		Criteria c = session.createCriteria(User.class);
		c.setMaxResults(pageSize);
		c.setFirstResult(begin);
		c.addOrder(Order.asc("id"));//指定排序字段
		list = c.list();

		return list;
	}
	
	//帶有查詢條件:按姓名查詢，順帶解釋一下復(fù)合查詢
	public List query(User u){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return null;
		}

//		Query query = session.createQuery("from User where name='" + u.getName() +"'");
//		List list = query.list();
		
//		Criteria criteria = session.createCriteria(User.class);
////		criteria.add(Expression.eq("name",u.getName())); 
//		criteria.add(Expression.like("name","lin%"));
//		List list = criteria.list();
		
		List list = session.createCriteria(User.class)
			.add(Expression.eq("name",u.getName())).list();

//		//可以構(gòu)造復(fù)雜條件
//		List list = session.createCriteria(User.class)
//			.add(Expression.eq("name",u.getName()))
//			.add(Expression.or(Expression.eq("name",u.getName()),Expression.ne("sex","1")))
//			.list();
		
		HibernateUtil.closeSession();
		return list;
	}
	

	// 主鍵加載get和load
	// get和load的區(qū)別：加載一個(gè)不存在的對(duì)象時(shí)，load會(huì)拋出異常，而get會(huì)返回null
	// 用load加載的數(shù)據(jù)，在會(huì)話關(guān)閉后，不再能夠訪問(wèn)
	public User query(int id){
		Session session = HibernateUtil.currentSession();
		if (session == null){
			log.error("Error when get HibernateSession");
			return null;
		}
		User user = null;
//		user = (User)session.load(User.class,new Integer(id));
//		// 外部調(diào)用時(shí)報(bào)錯(cuò),原因：用load加載的對(duì)象只有在session范圍內(nèi)才有效
		user = (User)session.get(User.class,new Integer(id));
		
		HibernateUtil.closeSession();
		return user;
	}
	
	public int getPageCount() {
		int ret = 0;
		Session session = HibernateUtil.currentSession();
		if (session == null) {
			System.out.println("null");
			return 0;
		}

		Query query = session.createQuery("select count(*) from User");
		List list = query.list();
		ret = ((Integer) list.get(0)).intValue();

		ret = (int) Math.ceil(ret / (double) pageSize);

		HibernateUtil.closeSession();
		return ret;
	}
		
	
	// ...其他查詢方法
	
	public static void main(String[] args){
		UserOper uo = new UserOper();
//		User u = new User();
//		u.setName("LIN111");
//		u.setSex("1");
//		uo.add(u);
////		uo.delete(u);
////		uo.delete(177);
////		u.setId(180);
////		u.setName("LIN:LIN");
////		uo.update(u);
//		
//		uo.queryAll();
//		List l = uo.queryPage(1);
//		System.out.println(l);
		
//		List l = uo.query(u);
//		System.out.println(l);
		
		User u = uo.query(180);
		System.out.print(u);
	}
}