<!--mybatis-config文件配置，可以為一些類(lèi)定義別名-->
<!--<typeAliases>
    <typeAlias type="com.xyxy.pojo.Emp" alias="emp"></typeAlias>
</typeAliases>-->
<!-- type屬性里面使用別名就可以不用全路徑 -->
<resultMap id="empMapper" type="emp">
    <id property="empId" column="emp_id"></id>
    <result property="empName" column="emp_name"></result>
    <result property="empSalary" column="emp_salary"></result>
</resultMap>
<select id="findAll" resultMap="empMapper">
    select * from t_emp
</select>

<!-- 公共代碼 -->
<sql id="select">
    <!-- select * from t_emp -->
    select emp_id empId,emp_name empName,emp_salary empSalary from t_emp
</sql>
    
<!--select標(biāo)簽的id與接口的方法名對(duì)應(yīng)，resultType和返回結(jié)果封裝的Bean一致-->
<select id="findById" resultType="com.xyxy.pojo.Emp">
    <!-- include:用于加載提取公共的sql語(yǔ)句，與<sql>標(biāo)簽對(duì)應(yīng)
           refid:填寫(xiě)<sql>標(biāo)簽中的id屬性-->
    <include refid="select"></include>
    where emp_id=#{empId}
</select>
    
<select id="findByIdOrName" parameterType="Emp" resultType="emp">
    <!-- 引入公共代碼 -->
    <include refid="select"></include>
    <!-- where標(biāo)簽:一個(gè)where條件語(yǔ)句，通常和<if>標(biāo)簽混合使用 -->
    <where>
        <!-- if標(biāo)簽:執(zhí)行一個(gè)判斷語(yǔ)句，成立才會(huì)執(zhí)行標(biāo)簽體內(nèi)的sql語(yǔ)句,test:寫(xiě)上條件判斷語(yǔ)句
        注意:這里每一個(gè)if前面都盡量加上and，如果你是第一個(gè)條件，框架會(huì)自動(dòng)幫你把a(bǔ)nd截取，
            如果是第二個(gè)if就不能省略and-->
        <if test="empId != null and empId != ''">
            and emp_id=#{empId}
        </if>
        <if test="empName != null and empName != ''">
            and emp_name like '%${empName}%'
        </if>
    </where>
</select>

<delete id="deleteEmployeeById" parameterType="int">
    delete from t_emp where emp_id=#{empId}
</delete>

delete from t_emp where emp_id=?

<select id="selectEmployeeByName" resultType="com.xyxy.pojo.Employee">
    select emp_id empId,emp_name empName,emp_salary empSalary from t_emp where emp_name like '%${empName}%'
</select>

Employee selectEmployee(Integer empId);

<select id="selectEmployee" resultType="com.xyxy.pojo.Employee">
    select emp_id empId,emp_name empName,emp_salary empSalary from t_emp where emp_id=#{empId}
</select>

int updateEmployee(@Param("empId") Integer empId,@Param("empSalary") Double empSalary);

<update id="updateEmployee">
    update t_emp set emp_salary=#{empSalary} where emp_id=#{empId}
</update>

int insertEmployee(Employee employee);

<insert id="insertEmployee">
    insert into t_emp(emp_name,emp_salary) values(#{empName},#{empSalary})
</insert>

int updateEmployeeByMap(Map<String, Object> paramMap);

<update id="updateEmployeeByMap">
    update t_emp set emp_salary=#{empSalaryKey} where emp_id=#{empIdKey}
</update>

/**
* 統(tǒng)計(jì)員工數(shù)量
* @return
*/
Long selectEmployeeCount();

<!--
        返回簡(jiǎn)單類(lèi)型:
        resultType表示結(jié)果類(lèi)型:結(jié)果集返回的類(lèi)型,要和Mapper接口中對(duì)應(yīng)的方法的返回值類(lèi)型保持一致
    -->
<select id="selectEmployeeCount" resultType="long">
    select count(emp_id) from t_emp
</select>

Employee selectEmployee(Integer empId);

<!-- 編寫(xiě)具體的SQL語(yǔ)句，使用id屬性唯一的標(biāo)記一條SQL語(yǔ)句 -->
<!-- resultType屬性：指定封裝查詢(xún)結(jié)果的Java實(shí)體類(lèi)的全類(lèi)名 -->
<select id="selectEmployee" resultType="com.xyxy.pojo.Employee">
    <!-- Mybatis負(fù)責(zé)把SQL語(yǔ)句中的#{}部分替換成“?”占位符 -->
    <!-- 給每一個(gè)字段設(shè)置一個(gè)別名，讓別名和Java實(shí)體類(lèi)中屬性名一致 -->
    select emp_id empId,emp_name empName,emp_salary empSalary from t_emp where emp_id=#{maomi}
</select>

<!-- 在全局范圍內(nèi)對(duì)Mybatis進(jìn)行配置 -->
<settings>
    <!-- 具體配置 -->
    <!-- 從org.apache.ibatis.session.Configuration類(lèi)中可以查看能使用的配置項(xiàng) -->
    <!-- 將mapUnderscoreToCamelCase屬性配置為true，表示開(kāi)啟自動(dòng)映射駝峰式命名規(guī)則 -->
    <!-- 規(guī)則要求數(shù)據(jù)庫(kù)表字段命名方式：?jiǎn)卧~_單詞 -->
    <!-- 規(guī)則要求Java實(shí)體類(lèi)屬性名命名方式：首字母小寫(xiě)的駝峰式命名 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

/**
* 根據(jù)empId查詢(xún)員工信息，并且將結(jié)果集封裝到Map中
* @param empId
* @return
*/
Map selectEmployeeMapByEmpId(Integer empId);

<!--
        返回Map類(lèi)型:
        resultType表示結(jié)果類(lèi)型: 就是Map的全限定名或者別名
    -->
<select id="selectEmployeeMapByEmpId" resultType="map">
    select * from t_emp where emp_id=#{empId}
</select>

List<Employee> selectAll();

<!-- List<Employee> selectAll(); -->
<select id="selectAll" resultType="com.xyxy.pojo.Employee">
    select emp_id empId,emp_name empName,emp_salary empSalary from t_emp
</select>

List<Map> selectAllMap();

<select id="selectAllMap" resultType="map">
    select emp_id empId,emp_name empName,emp_salary empSalary
    from t_emp
</select>

insert into t_order_item(item_name,item_price,item_count,order_id) values(...)

int insertEmployee(Employee employee);

<!-- int insertEmployee(Employee employee); -->
<!-- useGeneratedKeys屬性字面意思就是“使用生成的主鍵” -->
<!-- keyProperty屬性可以指定主鍵在實(shí)體類(lèi)對(duì)象中對(duì)應(yīng)的屬性名，Mybatis會(huì)將拿到的主鍵值存入這個(gè)屬性 -->
<insert id="insertEmployee" useGeneratedKeys="true" keyProperty="empId">
    insert into t_emp(emp_name,emp_salary)
    values(#{empName},#{empSalary})
</insert>

@Test
public void testSaveEmp() {

    EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
    Employee employee = new Employee();
        
    employee.setEmpName("john");
    employee.setEmpSalary(666.66);
    
    employeeMapper.insertEmployee(employee);
    //打印自動(dòng)返回存入對(duì)象的主鍵值
    System.out.println("employee.getEmpId() = " + employee.getEmpId());

}

<insert id="insertEmployee">
    insert into t_emp (emp_name,emp_salary) values (#{empName},#{empSalary})
    <!--
            keyColumn="emp_id"表示要查詢(xún)的主鍵的列名
            keyProperty="empId"表示將查詢(xún)到的主鍵值賦給JavaBean的哪個(gè)屬性
            resultType="int"表示查詢(xún)的結(jié)果類(lèi)型
            order="AFTER" 表示這個(gè)查詢(xún)是執(zhí)行在insert之前還是之后呢?如果為AFTER表示之后，BEFORE表示之前
        -->
    <selectKey keyColumn="emp_id" keyProperty="empId" resultType="int" order="AFTER">
        select last_insert_id()
    </selectKey>
</insert>

<insert id="insertEmployee"
parameterType="com.atguigu.mybatis.beans.Employee"  
databaseId="oracle">
<selectKey order="BEFORE" keyProperty="id"
resultType="integer">
select employee_seq.nextval from dual
</selectKey>    
insert into orcl_employee(id,last_name,email,gender) values(#{id},#{lastName},#{email},#{gender})
</insert>

<insert id="insertEmployee"
parameterType="com.atguigu.mybatis.beans.Employee"  
databaseId="oracle">
<selectKey order="AFTER" keyProperty="id"
resultType="integer">
select employee_seq.currval from dual
</selectKey>    
insert into orcl_employee(id,last_name,email,gender) values(employee_seq.nextval,#{lastName},#{email},#{gender})
</insert>

<!-- 使用settings對(duì)Mybatis全局進(jìn)行設(shè)置 -->
<settings>
    <!-- 將xxx_xxx這樣的列名自動(dòng)映射到xxXxx這樣駝峰式命名的屬性名 -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

<!-- Employee selectEmployee(Integer empId); -->
<select id="selectEmployee" resultType="com.atguigu.mybatis.entity.Employee">
    select emp_id,emp_name,emp_salary from t_emp where emp_id=#{empId}
</select>

<!--
        手動(dòng)映射:通過(guò)resultMap標(biāo)簽配置映射規(guī)則
            1. id屬性:表示這個(gè)手動(dòng)映射規(guī)則的唯一表示
            2. type屬性: 表示這個(gè)手動(dòng)映射規(guī)則是將結(jié)果集映射給哪個(gè)類(lèi)的對(duì)象，就是JavaBean類(lèi)的全限定名
        resultMap標(biāo)簽中的子標(biāo)簽就是一一指定映射規(guī)則:
            1. id標(biāo)簽:指定主鍵的映射規(guī)則
            2. result標(biāo)簽:指定非主鍵的映射規(guī)則
        id標(biāo)簽和result標(biāo)簽的屬性:
            1. column:要進(jìn)行映射的結(jié)果集的字段名
            2. property:要進(jìn)行映射的JavaBean的屬性名
    -->
<resultMap id="EmployeeInfoMap" type="com.xyxy.pojo.EmployeeInfo">
    <id column="emp_id" property="id"/>
    <result column="emp_name" property="name"/>
    <result column="emp_salary" property="salary"/>
</resultMap>
<!--
        在select標(biāo)簽中通過(guò)resultMap屬性來(lái)指定使用哪個(gè)手動(dòng)映射規(guī)則
    -->
<select id="selectEmployeeInfoByEmpId" resultMap="EmployeeInfoMap">
select * from t_emp where emp_id=#{empId}
</select>

CREATE TABLE `t_customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`customer_name` CHAR(100),
PRIMARY KEY (`customer_id`)
);
CREATE TABLE `t_order` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`order_name` CHAR(100),
`customer_id` INT,
PRIMARY KEY (`order_id`)
);
INSERT INTO `t_customer` (`customer_name`) VALUES ('c01');
INSERT INTO `t_customer` (`customer_name`) VALUES ('c02');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o1', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o2', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o3', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o4', '2');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o5', '2');

public class Order {
    private Integer orderId;
    private String orderName;
    //表示Order和Customer的對(duì)一關(guān)系
    private Customer customer;
}

public class Customer {
    private Integer customerId;
    private String customerName;
}

public interface OrderMapper{
    Order findByOrderId(Integer orderId);
}

<!--全局配置自動(dòng)映射駝峰命名-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

    <!--實(shí)體類(lèi)取別名-->
    <typeAliases>
        <package name="com.xyxy.pojo"/>
    </typeAliases>
        
    <!-- Mapper注冊(cè)：指定Mybatis映射文件的具體位置 -->
    <!-- mappers標(biāo)簽：配置一個(gè)具體的Mapper映射文件 -->
    <!-- resource屬性：指定Mapper映射文件的實(shí)際存儲(chǔ)位置，這里需要使用一個(gè)以類(lèi)路徑根目錄
    <mappers>
        <!-- 包下的所有Mapper配置文件將被自動(dòng)加載、注冊(cè)，比較方便。 -->
        <package name="com.xyxy.mapper"/>
    </mappers>

<?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="com.xyxy.mapper.OrderMapper">
    <resultMap id="order" type="Order" autoMapping="true">
        <!-- autoMapping屬性設(shè)置為true開(kāi)啟自動(dòng)映射 -->
        <association property="customer" javaType="Customer">
            <id column="customer_id" property="customerId"></id>
            <result column="customer_name" property="customerName"></result>
        </association>
    </resultMap>
    <select id="findByOrderId" resultMap="order">
        select * from t_order o left join t_customer c on o.customer_id=c.customer_id where order_id=#{orderId}
    </select>
</mapper>

public class Customer {
    private Integer customerId;
    private String customerName;
    //客戶(hù)和訂單的一對(duì)多關(guān)系
    private List<Order> orderList;
}

public class Order {
    private Integer orderId;
    private String orderName;
}

public interface CustomerMapper {
    Customer findCustomerAndOrders(Integer customerId);
}

<?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="com.xyxy.mapper.CustomerMapper">
    <resultMap id="customer" type="Customer" >
        <id column="customer_id" property="customerId"/>
        <result column="customer_name" property="customerName"/>
        <collection property="orderList" ofType="Order" autoMapping="true">
            <!--
            進(jìn)行一對(duì)多映射,使用collection標(biāo)簽
                ofType屬性指的是orderList的泛型
            -->
        </collection>
    </resultMap>
    <select id="findCustomerAndOrders" resultMap="customer">
        SELECT * FROM t_customer c,t_order o WHERE o.customer_id=c.customer_id AND c.customer_id=#{customerId}
    </select>
</mapper>