package org.fkit.hrm.util.common;

public class HrmConstants
{
    // 数据库表常量
    public static final String USERTABLE = "user_inf";
    public static final String DEPTTABLE = "dept_inf";
    public static final String JOBTABLE = "job_inf";
    public static final String EMPLOYEETABLE = "employee_inf";
    public static final String NOTICETABLE = "notice_inf";
    public static final String DOCUMENTTABLE = "document_inf";

    // 登录
    public static final String LOGIN = "loginForm";
    // 用户的session对象
    public static final String USER_SESSION = "user_session";

    // 默认每页4条数据
    public static int PAGE_DEFAULT_SIZE = 4;
}

package domain;
import java.io.Serializable;
public class User
    implements Serializable
{
    // 使用缓存需要可序列话
    private static final long serialVersionUID = 2667279148834038605L;
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    public User()
    {}
    // 此处省略getter和setter方法,请自己补上
    @Override
    public String toString()
    {
        return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
    }
}

package mapper;

import java.util.List;
import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.cache.decorators.LruCache;
import domain.User;

@CacheNamespace(
    eviction = LruCache.class, 
    flushInterval = 60000, 
    size = 512, 
    readWrite = true
)
public interface UserMapper {
    /**
     * 根据id查询用户信息.
     * 
     * @param id
     * @return 用户记录
     */
    @Select("select * from tb_user where id=#{id}")
    @Options(useCache = true)
    User selectUserById(Integer id);

    /**
     * 查询所有的用户信息.
     * 
     * @return 用户列表
     */
    @Select("select * from tb_user")
    @Options(useCache = true)
    List<User> selectAllUsers();

    /**
     * 根据id删除用户信息.
     * 
     * @param id 主键
     */
    @Delete("delete from tb_user where id=#{id}")
    void deleteUserById(Integer id);
}

@CacheNamespace(
    eviction = LruCache.class,
    flushInterval = 60000,
    size = 512,
    readWrite = true
)

package fractory;

import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlSessionFactoryTools {
    private static SqlSessionFactory sqlSessionFactory = null;
    static
    {
        try
        {
            InputStream mybatisConfigXML = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(mybatisConfigXML);

        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession()
    {
        return sqlSessionFactory.openSession();
    }
}

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(1);
            System.out.println(user);
            // 关闭一级缓存
            sqlSession.close();
            // 重新获取会话
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 重新获取mapper接口代理对象
            userMapper = sqlSession.getMapper(UserMapper.class);
            System.out.println("-----------------------------------------------");
            user = userMapper.selectUserById(1);
            System.out.println(user);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

package test;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class DeleteUserTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.selectAllUsers();
            users.forEach(user -> System.out.println("  " + user));
            System.out.println("----------------------------------------");
            // userMapper.deleteUserById(users.get(0).getId());
            // sqlSession.commit();
            sqlSession.close();
            System.out.println("----------------------------------------");
            // 再次加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 再次获取mapper接口代理对象
            userMapper = sqlSession.getMapper(UserMapper.class);
            users = userMapper.selectAllUsers();
            users.forEach(user -> System.out.println("  " + user));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }

    }
}

package domain;
public class User{
    private Integer id;
    private String name;
    private String sex;
    private Integer age;
    public User()
    {
        super();
    }
    // 此处省略getter和setter方法,请自己补上
    @Override
    public String toString()
    {
        return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
    }
}

package mapper;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;
import domain.User;

public interface UserMapper {
    /**
     * 查询所有的用户信息.
     * 
     * @return 包含所有用户信息的List集合.
     */
    @Select("{call select_all_user()}")
    @Options(statementType = StatementType.CALLABLE)
    List<User> selectAllUser();

    /**
     * 根据id查询用户信息.
     * 
     * @param id 主键
     * @return 该主键对应的用户信息
     */
    @Select("{call select_user_by_id(#{id,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    User selectUserById(Integer id);

    /**
     * 插入一个用户信息.
     * 
     * @param user 用户对象
     * @return 插入用户时，系统分配的主键
     */
    @Insert("{call insert_user(" 
                + "#{id,mode=OUT,jdbcType=INTEGER}," 
                + "#{name,mode=IN}," 
                + "#{sex,mode=IN},"
                + "#{age,mode=IN}"
                + ")}")
    @Options(statementType = StatementType.CALLABLE)
    Integer insertUser(User user);

    @Update("{call update_user_by_id(" 
                + "#{id,mode=IN}," 
                + "#{name,mode=IN}," 
                + "#{sex,mode=IN}," 
                + "#{age,mode=IN}"
                + ")}")
    @Options(statementType = StatementType.CALLABLE)
    void updateUserById(User user);

    /**
     * 根据id删除用户.
     * 
     * @param id 用户的主键
     */
    @Delete("{call delete_user_by_id(#{id,mode=IN})}")
    @Options(statementType = StatementType.CALLABLE)
    void deleteUserById(Integer id);
}

@Select("{call select_all_user()}")
@Options(statementType=StatementType.CALLABLE)
List<User> selectAllUser();

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectAllTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.selectAllUser();
            System.out.println("________________________________");
            users.forEach(user-> System.out.println(user));
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

@Select("{call select_user_by_id(#{id,mode=IN})}")
@Options(statementType=StatementType.CALLABLE)
User selectUserById(Integer id);

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectByIdTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(1);
            System.out.println("_______________________________________________");
            System.out.println(user);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

@Update("{call update_user_by_id("
        + "#{id,mode=IN},"
        + "#{name,mode=IN},"
        + "#{sex,mode=IN},"
        + "#{age,mode=IN}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
void updateUserById(User user);

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class UpdateTest{
    public static void main(String[] args)
    {
        SqlSession sqlSession = null;
        try
        {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFratoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById(3);
            System.out.println("________________________________");
            if(user != null)
            {
                System.out.println("更新之前的数据:" + user);
                System.out.println("________________________________");
                user.setName("李四");
                user.setSex("男");
                user.setAge(22);
                userMapper.updateUserById(user);
                System.out.println("________________________________");
                user = userMapper.selectUserById(user.getId());
                System.out.println("________________________________");
                System.out.println("更新之后的数据:" + user);
            }
            // 提交事务
            sqlSession.commit();
        } catch (Exception e)
        {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally
        {
            // 关闭会话
            if(sqlSession != null)
                sqlSession.close();
        }
    }
}

@Delete("{call delete_user_by_id(#{id,mode=IN})}")
@Options(statementType=StatementType.CALLABLE)
void deleteUserById(Integer id);

package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class DeleteTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 查找要删除的记录
            User user = userMapper.selectUserById(2);
            // 如果找到要删除的记录
            if (user != null) {
                System.out.println("_________________________________________");
                System.out.println("即将删除用户:" + user);
                System.out.println("_________________________________________");
                // 删除掉该用户
                userMapper.deleteUserById(user.getId());
            }
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

@Insert("{call insert_user("
        + "#{id,mode=OUT,jdbcType=INTEGER},"
        + "#{name,mode=IN},"
        + "#{sex,mode=IN},"
        + "#{age,mode=IN}"
        + ")}")
@Options(statementType=StatementType.CALLABLE)
Integer insertUser(User user);

package test;

import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFactoryTools;
import mapper.UserMapper;

public class InsertTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 创建插入的数据
            User user = new User();
            user.setName("小张");
            user.setSex("男");
            user.setAge(22);
            // 插入数据
            userMapper.insertUser(user);
            System.out.println("______________________________________");
            System.out.println("生成的主键:" + user.getId());
            System.out.println("______________________________________");
            User user1 = userMapper.selectUserById(user.getId());
            System.out.println("插入后的记录:" + user1);
            
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }

    }
}

public String deleteEmployeeByPO(Employee parm)
{
    return new SQL(){
        {
            // 在这里写上动态SQL的生成逻辑...
            DELETE_FROM("tb_employee");
            if (parm.getId() != null)
            {
                WHERE("id=#{id}");
            }
            if (parm.getLoginname() != null)
            {
                WHERE("loginname=#{loginname}");
            }
            if (parm.getPassword() != null)
            {
                WHERE("password=#{password}");
            }
            if (parm.getName() != null)
            {
                WHERE("name=#{name}");
            }
            if (parm.getSex() != null)
            {
                WHERE("sex=#{sex}");
            }
            if (parm.getAge() != null)
            {
                WHERE("age=#{age}");
            }
            if (parm.getPhone() != null)
            {
                WHERE("phone=#{phone}");
            }
            if (parm.getSal() != null)
            {
                WHERE("sal=#{sal}");
            }
            if (parm.getState() != null)
            {
                WHERE("state=#{state}");
            }
        }
    }.toString();
}

@DeleteProvider(
    type = EmployeeDynamicSQLProvider.class,
    method = "deleteEmployeeByPO"
)
void deleteEmployeeByPO(Employee employee);

package test;

import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFactoryTools;
import mapper.EmployeeMapper;

public class DeleteTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            // 创建一个新的对象保存要删除的条件
            Employee delCondition = new Employee();
            // 添加条件
            delCondition.setId(1);
            // employee.setLoginname();
            // employee.setPassword();
            // employee.setName();
            delCondition.setSex("女");
            // employee.setAge(18);
            // employee.setPhone();
            // employee.setSal();
            // employee.setState();
            employeeMapper.deleteEmployeeByPO(delCondition);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }

    }
}

public String updateEmployeeByPO(Employee employee)
{
    return new SQL(){
        {
            // 在这里写上动态SQL的生成逻辑...
            UPDATE("tb_employee");
            if(employee.getLoginname() != null)
            {
                SET("loginname=#{loginname}");
            }
            if(employee.getPassword() != null)
            {
                SET("password=#{password}");
            }
            if(employee.getName() != null)
            {
                SET("name=#{name}");
            }
            if(employee.getSex() != null)
            {
                SET("sex=#{sex}");
            }
            if(employee.getAge() != null)
            {
                SET("age=#{age}");
            }
            if(employee.getPhone() != null)
            {
                SET("phone=#{phone}");
            }
            if(employee.getSal() != null)
            {
                SET("sal=#{sal}");
            }
            if(employee.getState() != null)
            {
                SET("state=#{state}");
            }
            WHERE("id=#{id}");
        }
    }.toString();
}

@UpdateProvider(
    type = EmployeeDynamicSQLProvider.class,
    method = "updateEmployeeByPO"
)
void updateEmployeeByPO(Employee employee);

package test;

import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFactoryTools;
import mapper.EmployeeMapper;

public class UpdateTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            // 查找要修改的行
            Employee employee = employeeMapper.selectEmployeeById(3);
            System.out.println("-------------------------------------------------");
            // 如果找到
            if (employee != null) {
                System.out.println("修改之前的员工信息");
                System.out.println(employee);
                System.out.println("-------------------------------------------------");
                // 创建一个新对项，保存更新后的信息
                Employee employeeUpdate = new Employee();
                // 主键id不更新
                employeeUpdate.setId(employee.getId());
                // 更新其他信息
                // employeeUpdate.setLoginname();
                // employeeUpdate.setPassword();
                employeeUpdate.setName("修改之后的姓名");
                employeeUpdate.setSex("男");
                // employeeUpdate.setAge(15);
                // employeeUpdate.setPhone();
                // employeeUpdate.setSal(123.1);
                // employeeUpdate.setState("员工");
                // 执行更新语句
                employeeMapper.updateEmployeeByPO(employeeUpdate);
                System.out.println("-------------------------------------------------");
                // 再吃查询
                employee = employeeMapper.selectEmployeeById(3);
                System.out.println("-------------------------------------------------");
                // 查看是否更新成功
                System.out.println(employee);
            }
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }
    }
}

public String insertEmployeeWithParamPO(Employee employee)
{
    return new SQL() {
        {
            // 在这里写上动态SQL的生成逻辑...
            INSERT_INTO("tb_employee");
            if (employee.getId() != null)
            {
                VALUES("id", "#{id}");
            }
            if (employee.getLoginname() != null)
            {
                VALUES("loginname", "#{loginname}");
            }
            if (employee.getPassword() != null)
            {
                VALUES("password", "#{password}");
            }
            if (employee.getName() != null)
            {
                VALUES("name", "#{name}");
            }
            if (employee.getSex() != null)
            {
                VALUES("sex", "#{sex}");
            }
            if (employee.getAge() != null)
            {
                VALUES("age", "#{age}");
            }
            if (employee.getPhone() != null)
            {
                VALUES("phone", "#{phone}");
            }
            if (employee.getSal() != null)
            {
                VALUES("sal", "#{sal}");
            }
            if (employee.getState() != null)
            {
                VALUES("state", "#{state}");
            }
        }
    }.toString();
}

@InsertProvider(
    type = EmployeeDynamicSQLProvider.class,
    method = "insertEmployeeWithParamPO"
)
// 设置使用自动增长主键,主键返回给po类的id属性
@Options(
    useGeneratedKeys = true,
    keyProperty = "id"
)
int insertEmployeeWithParmPO(Employee employee);

package test;

import org.apache.ibatis.session.SqlSession;
import domain.Employee;
import fractory.SqlSessionFactoryTools;
import mapper.EmployeeMapper;

public class insertTest {
    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            // 加载mybatis-config.xml,获取SqlSession实例
            sqlSession = SqlSessionFactoryTools.getSqlSession();
            // 获取mapper接口代理对象
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            // employee.setLoginname("xiaozhang");
            // employee.setPassword("xiaozhang");
            employee.setName("小张");
            employee.setSex("男");
            employee.setAge(22);
            employee.setPhone("123456789123");
            employee.setSal(6800.0);
            employee.setState("经理");
            employeeMapper.insertEmployeeWithParmPo(employee);
            System.out.println("生成的主键:" + employee.getId());
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 出错回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        } finally {
            // 关闭会话
            if (sqlSession != null)
                sqlSession.close();
        }

    }
}