package pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer empId;
    private String empName;
    private Double empSalary;

}

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>day03-mybatis02-dynamic</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

        <!-- Mybatis核心 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!-- junit測(cè)試 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- MySQL驅(qū)動(dòng) -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.3</version>
            <scope>runtime</scope>
        </dependency>

        <!-- log4j日志 -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

</project>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--駝峰映射-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--類型別名映射-->
    <typeAliases>
        <package name="pojo"/>
    </typeAliases>
    <!--環(huán)境配置-->
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="username" value="root"/>
                <property name="password" value="888888"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/>
                <property name="driver" value="com.mysql.jdbc.Driver"/>
            </dataSource>
                
        </environment>
    </environments>
    <!--路徑映射-->
    <mappers>
        <mapper resource="mapper/sql.xml"/>
        <package name="mapper"/>
    </mappers>
</configuration>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.sql">

        <sql id="mySelectSql">
    select emp_id,emp_name,emp_salary from t_emp
</sql>


</mapper>

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

     //根據(jù)員工的empId查詢大于該empId的所有員工，如果empId為null，則查詢?nèi)w員工
    List<Employee> selectEmployeeListByEmpId(Integer empId);

    /**
     * 查詢大于傳入的empId并且工資大于傳入的empSalary的員工集合,如果傳入的empId為null，則不考慮empId條件
     * 傳入的empSalary為null則不考慮empSalary的條件
     */
    List<Employee> selectEmployeeListByEmpIdAndEmpSalary(@Param("empId") Integer empId, @Param("empSalary") Double empSalary);

    /**
     * 根據(jù)empId更新員工信息,如果某個(gè)值為null，則不更新這個(gè)字段
     */
    void updateEmployee(Employee employee);

    /**
     * 根據(jù)emp_id查詢員工信息，如果0<emp_id<6,那么就查詢所有大于該emp_id的員工，如果emp_id是大于6，那么就查詢所有小于該emp_id的員工
     * 如果是其它情況，則查詢所有員工信息
    
     */
    List<Employee> selectEmployeeList(Integer empId);

    /**
     * 添加員工信息 
     */
    void insertEmployee(Employee employee);

    /**
     * 批量添加員工集合    
     */
    void insertEmployeeList(@Param("employeeList") List<Employee> employeeList);

    /**
     * 根據(jù)員工的id集合查詢員工集
     */
    List<Employee> selectEmployeeListByEmpIdList(List<Integer> idList);
}

<select id="selectEmployeeListByEmpId" resultType="Employee">

    <include refid="mapper.sql.mySelectSql"></include> where emp_id>#{empId}

</select>

<select id="selectEmployeeListByEmpId" resultType="Employee">
     <include refid="mapper.sql.mySelectSql"></include>
     <if test="empId != null">
         where emp_id>#{empId}
     </if>
 </select>

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include> where
   <if test="empId != null">
            emp_id>#{empId}
        </if>
        <if test="empSalary != null">
           and emp_salary>#{empSalary}
        </if>

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include>
   
    <where>
        <if test="empId != null">
            emp_id>#{empId}
        </if>
        <if test="empSalary != null">
           and emp_salary>#{empSalary}
        </if>
    </where>
</select>

<select id="selectEmployeeListByEmpIdAndEmpSalary" resultType="Employee">
    <include refid="mapper.sql.mySelectSql"></include>
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        <if test="empId != null">
            emp_id>#{empId}
        </if>

        <if test="empSalary != null">
            AND emp_salary>#{empSalary}
        </if>
    </trim>
</select>

<update id="updateEmployee" >
    <trim prefix="set" prefixOverrides=",">
        <if test="empName!=null">
            emp_name=#{empName}
        </if>
        <if test="empSalary!=null">
            , emp_salary=#{empSalary}
        </if>
    </trim>
    where emp_id=#{empId}
</update>

<update id="updateEmployee" >
    update t_emp
     <set >
         <if test="empName!=null">
             emp_name=#{empName}
         </if>
         <if test="empSalary!=null">
            , emp_salary=#{empSalary}
         </if>
     </set>

<select id="selectEmployeeList" resultType="employee">
  
    <include refid="mapper.sql.mySelectSql"></include> where
    <choose>
    <!--&lt;是<號(hào)的轉(zhuǎn)義字符-->
        <when test="empId>0 and empId&lt;6">
            emp_id>#{empId}
        </when>
        <when test="empId>6">
            emp_id&lt;#{empId}
        </when>
        <otherwise>
            1==1
        </otherwise>
    </choose>

</select>

<insert id="insertEmployeeList">
    insert into t_emp(emp_name,emp_salary)values
    <!--collection標(biāo)簽可以寫list,collection,
    或者自己自己定義參數(shù)名@Param("employeeList") List<Employee> employeeList-->
    <foreach collection="employeeList" separator="," item="emp">
        (#{emp.empName},#{emp.empSalary})
    </foreach>
</insert>

<select id="selectEmployeeListByEmpIdList" resultType="employee">
    <include refid="mapper.sql.mySelectSql"></include>
     <foreach collection="collection" item="id" separator="," open="where emp_id in (" close=")">
         #{id}
     </foreach>
</select>

import mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import pojo.Employee;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Test {
    private EmployeeMapper employeeMapper;
    private InputStream is;
    private SqlSession sqlSession;
    @Before
    public void init() throws Exception{
        //目標(biāo):獲取EmployeeMapper接口的代理對(duì)象，并且使用該對(duì)象調(diào)用selectEmployee(1)方法,然后返回Employee對(duì)象
        //1. 將全局配置文件轉(zhuǎn)成字節(jié)輸入流
        is = Resources.getResourceAsStream("mybatisConfig.xml");
        //2. 創(chuàng)建SqlSessionFactoryBuilder對(duì)象
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        //3. 使用構(gòu)建者模式創(chuàng)建SqlSessionFactory對(duì)象
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
        //4. 使用工廠模式創(chuàng)建一個(gè)SqlSession對(duì)象
        sqlSession = sqlSessionFactory.openSession();
        //5. 使用動(dòng)態(tài)代理模式，創(chuàng)建EmployeeMapper接口的代理對(duì)象
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }


    @After
    public void after() throws Exception{
        //提交事務(wù)!!!
        sqlSession.commit();
        //7. 關(guān)閉資源
        is.close();
        sqlSession.close();
    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpId(){
        System.out.println(employeeMapper.selectEmployeeListByEmpId(null));
    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpIdAndEmpSalary(){
        System.out.println(employeeMapper.selectEmployeeListByEmpIdAndEmpSalary(2, 300d));
    }

    @org.junit.Test
    public void testUpdateEmployee(){
        Employee employee = new Employee(3,"celia", 9000d);

        employeeMapper.updateEmployee(employee);
    }

    @org.junit.Test
    public void testSelectEmployeeList(){
    System.out.println(employeeMapper.selectEmployeeList(7));
}

   @org.junit.Test
   public void testInsertEmployee(){
        employeeMapper.insertEmployee(new Employee(null,"tom",300d));
    }

    @org.junit.Test
    public void testInsertEmployeeList(){
        List<Employee> employeeList = new ArrayList<>();
        for (int i = 11; i <=20 ; i++) {
            employeeList.add(new Employee(null,"aobama"+i,2000d));
        }

        employeeMapper.insertEmployeeList(employeeList);

    }

    @org.junit.Test
    public void testSelectEmployeeListByEmpIdList(){
        List<Integer> idList = new ArrayList<>();
        idList.add(23);
        idList.add(33);
        idList.add(32);
        idList.add(21);
        idList.add(22);
        System.out.println(employeeMapper.selectEmployeeListByEmpIdList(idList));
    }


}