package action;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import bean.User;
import util.HibernateUtil;

/**
 * 本类测试HQL的预处理指令
 * 
 * HIbernate中的预编译与Spring的预编译的处理差不多。
 * 
 * 1：使用标准的?
 * 
 * 　 2：使用命名参数
 * 
 * 　　 2.1：使用名称逐个设置。
 * 
 * 　　 2.2：使用Map(key的值等于参数的名称)
 * 
 * 　　 2.3：使用Bean(属性的名称等于参数的名称）
 * 
 * @author 半颗柠檬、
 * 
 */
public class Query_HQL_Prepared {

    public static void main(String[] args) {
        // Query_HQL_Prepared.one(); // 使用标准的 ？
        // Query_HQL_Prepared.two(); //使用名称逐个设置。
//        Query_HQL_Prepared.three(); // 使用Map(key的值等于参数的名称)
        Query_HQL_Prepared.four();   //使用Bean(属性的名称等于参数的名称）
    }



    /*
     * 使用标准的？
     */
    private static void one() {
        Session session = null;
        Transaction tran = null;
        Query query = null;
        String hql = "";
        try {

            session = HibernateUtil.getSession();
            tran = session.beginTransaction();
            hql = " select u from User u where userid<=? and userid>=? and username like ?";

            /**
             * HQL的索引是从0开始的
             */
            query = session.createQuery(hql);
            query.setParameter(0, 6);
            query.setParameter(1, 1);
            query.setParameter(2, "%user%");

            List<User> userList = query.list();

            for (User user : userList) {

                System.out.println("username=" + user.getUsername()
                        + "\t userid=" + user.getUserid());

            }

            tran.commit();
        } catch (Exception e) {
            tran.rollback();
            e.printStackTrace();

        } finally {
            HibernateUtil.closeSession();
        }

    }

    /**
     * 使用名称逐个设置
     */
    private static void two() {
        Session session = null;
        Query query = null;
        Transaction tran = null;
        String sql = "";
        try {

            session = HibernateUtil.getSession();
            tran = session.beginTransaction();
            sql = "  select u from User u where username like :username and  userid>= :userid_begin  and  userid<= :userid_end ";

            query = session.createQuery(sql);
            query.setString("username", "%user%");
            query.setInteger("userid_begin", 3);
            query.setInteger("userid_end", 5);

            List<User> userList = query.list();

            for (User user : userList) {

                System.out.println("username=" + user.getUsername()
                        + "\t userid=" + user.getUserid());

            }

            tran.commit();
        } catch (Exception e) {
            e.printStackTrace();
            tran.rollback();
        } finally {
            HibernateUtil.closeSession();
        }

    }

    /**
     * 使用Map(key的值等于参数的名称)
     */
    private static void three() {

        Session session = null;
        Transaction tran = null;
        Query query = null;
        String sql = "";

        try {
            session=HibernateUtil.getSession();
            tran=session.beginTransaction();
            
            sql=" select  u from User u where userid>= :userid_begin and userid<= :userid_end and username like :username";
            query=session.createQuery(sql);
            
            Map<String,Object> sqlMap=new HashMap<String,Object>();
            sqlMap.put("userid_begin", 3);
            sqlMap.put("userid_end",5);
            sqlMap.put("username", "%user%");
            
            query.setProperties(sqlMap);
            
            List<User> userList = query.list();

            for (User user : userList) {

                System.out.println("username=" + user.getUsername()
                        + "\t userid=" + user.getUserid());

            }
            
            tran.commit();
        } catch (Exception e) {
            e.printStackTrace();
            tran.rollback();
        } finally {
            HibernateUtil.closeSession();
        }

    }

    
    /**
     * 使用Bean(属性的名称等于参数的名称）
     */
    private static void four() {
    
        Session session = null;
        Query query = null;
        Transaction tran = null;
        String sql="";
        
        try {
            session=HibernateUtil.getSession();
            tran=session.beginTransaction();
            
            sql=" select u from User u  where username like :username and userid>= :userid_begin and  userid<= :userid_end  "; 
            
            User user=new User();
            user.setUsername("%user%");
            user.setUserid_begin(3);   //User bean中一定要有userid_begin和userid_end属性，且要有get和set方法
            user.setUserid_end(5);
            
            query=session.createQuery(sql);
            query.setProperties(user);
            
            List<User> userList = query.list();

            for (User use1r : userList) {

                System.out.println("username=" + use1r.getUsername()
                        + "\t userid=" + use1r.getUserid());

            }
            
            
            tran.commit();
        } catch (Exception e) {
            e.printStackTrace();
            tran.rollback();
        }finally{
            HibernateUtil.closeSession();
        }   
    }
}