package com.hibernate.demo.test;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;

public class SQLTest {

    public static void main(String[] args) {
        //加载配置文件创建会话工厂对象
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        //创建会话对象
        Session session = sessionFactory.openSession();
        
        //调用方法
        getById(session,2);
        
        //关闭会话对象
        session.close();
        //关闭会话工厂对象
        sessionFactory.close();
    }
    
    /**
     * 根据id查询
     * @param session
     * @param id
     */
    private static void getById(Session session,int id){
        //编写sql
        String sql="select * from t_employee where emp_id=?";
        //根据sql创建SQLQuery对象
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        //为占位符设置值
        sqlQuery.setInteger(0, id);
        //获取结果集,集合中的元素是Object类型的数组
        List<Object[]> list = sqlQuery.list();
        //遍历结果集
        for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }
    }
    
    /**
     * 根据name模糊查询
     * @param session
     * @param name
     */
    private static void getByName(Session session,String name){
        //编写sql语句
        String sql="select * from t_employee where emp_name like ?";
        //根据sql语句创建SQLQuery对象
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        //为占位符设置值
        sqlQuery.setString(0, "%"+name+"%");
        //获取结果集
        List<Object[]> list = sqlQuery.list();
        //遍历结果集
        for (Object[] objects : list) {
            for (Object object : objects) {
                System.out.print(object+"\t");
            }
            System.out.println();
        }
    }
    
    /**
     * 新增信息
     * @param session
     * @param employee
     */
    private static void add(Session session){
        //开启事务
        Transaction transaction = session.beginTransaction();
        //编写sql语句
        String sql="insert into t_employee(emp_name) values(?)";
        //根据sql创建SQLQuery对象
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        //设置指定索引的占位符的值
        sqlQuery.setString(0, "emp"+Math.random());
        //执行更新操作---新增/修改/删除都属于数据库的更新操作
        sqlQuery.executeUpdate();
        //提交事务
        transaction.commit();
    }
    
    /**
     * 更新操作
     * @param session
     * @param id
     */
    private static void update(Session session,int id){
        //开启事务
        Transaction transaction = session.beginTransaction();
        //编写sql语句
        String sql="update t_employee set emp_name=? where emp_id=?";
        //根据sql语句创建SQLQuery对象
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        //设置指定索引的占位符的值
        sqlQuery.setString(0, "emp"+Math.random());
        sqlQuery.setInteger(1, id);
        //执行更新操作
        sqlQuery.executeUpdate();
        //提交事务
        transaction.commit();
    }
    
    /**
     * 删除操作
     * @param session
     * @param id
     */
    private static void delete(Session session,int id){
        //开启事务
        Transaction transaction = session.beginTransaction();
        //编写sql语句
        String sql="delete from t_employee where emp_id=?";
        //根据sql创建SQLQuery对象
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        //设置占位符的值
        sqlQuery.setInteger(0, id);
        //执行更新操作
        sqlQuery.executeUpdate();
        //提交事务
        transaction.commit();
    } 

}

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: select * from t_employee where emp_id=?
2    李四    1    

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: select * from t_employee where emp_name like ?
6    小明    2    
7    小红    3    
8    小兰    3    
9    小绿    3    

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: insert into t_employee(emp_name) values(?)

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: update t_employee set emp_name=? where emp_id=?

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Hibernate: delete from t_employee where emp_id=?