1 Mybatis映射文件

Mybatis映射文件指导着Mybatis如何进行数据库的增加、删除、查询、修改，有着非常重要的意义。

cache：命名看空间的二级缓存配置。
cache-ref：其他命名空间缓存配置的引用。
resultMap：自定义结果集映射。
sql：抽取科重用的语句块。
insert：映射插入语句。
update：映射更新语句。
delete：映射删除语句。
select：映射查询语句。

2 增加、删除、修改、查询示例

Mybatis可以根据不同的数据库，使用对应的不同的SQL。但是需要在全局配置文件中配置databaseIdProvider节点，并且在映射文件的语句中标明所使用的数据库，示例如下（以MySQL为例）
全局配置文件mybatis-config.xml

   <databaseIdProvider type="DB_VENDOR">
        <property name="Oracle" value="oracle"/>
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>

<?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>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <databaseIdProvider type="DB_VENDOR">
        <property name="Oracle" value="oracle"/>
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>
    <mappers>
        <mapper resource="com/xuweiwei/mybatis/mapper/EmployeeMapper.xml"/>
    </mappers>

</configuration>

映射文件

<insert id="insertEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee" databaseId="mysql">
        INSERT INTO employee (last_name,gender,email) VALUE (#{lastName},#{gender},#{email})
 </insert>

Employee.java

package com.xuweiwei.mybatis.pojo;

public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender='" + gender + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

全局配置文件mybatis-config.xml

<?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>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <databaseIdProvider type="DB_VENDOR">
        <property name="Oracle" value="oracle"/>
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>
    <mappers>
        <mapper resource="com/xuweiwei/mybatis/mapper/EmployeeMapper.xml"/>
    </mappers>

</configuration>

EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {
    public Employee getEmployeeById(Integer id);

    public boolean insertEmployee(Employee employee);

    public Long updateEmployee(Employee employee);

    public Integer deleteEmployeeById(Integer id);

}

EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">
    <!-- 查询 -->
    <select id="getEmployeeById" resultType="com.xuweiwei.mybatis.pojo.Employee">
        select id,last_name ,gender,email  from employee where id = #{id}
    </select>
    <!-- 增加 -->
    <insert id="insertEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee"  databaseId="mysql">
        INSERT INTO employee (last_name,gender,email) VALUE (#{lastName},#{gender},#{email})
    </insert>

    <!-- 修改 -->
    <update id="updateEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee">
        UPDATE employee SET last_name = #{lastName},gender=#{gender},email=#{email} WHERE id = #{id}
    </update>

    <!-- 删除 -->
    <delete id="deleteEmployeeById" parameterType="java.lang.Integer">
        DELETE from employee WHERE id = #{id}
    </delete>

</mapper>

测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            System.out.println(employeeMapper.getEmployeeById(1));
        } finally {
            session.close();
        }
    }

    /**
     * 增加
     * @throws IOException
     */
    @Test
    public void testInsert() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            employee.setLastName("许威");
            employee.setGender("男");
            employee.setEmail("1975356467@qq.com");
            System.out.println(employeeMapper.insertEmployee(employee));
            session.commit();
        } finally {
            session.close();
        }
    }

    /**
     * 修改
     */
    @Test
    public void testUpdate() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            employee.setId(1);
            employee.setLastName("许威");
            employee.setGender("女");
            employee.setEmail("1975356467@qq.com");
            System.out.println(employeeMapper.updateEmployee(employee));
            session.commit();
        } finally {
            session.close();
        }
    }
    /**
     * 删除
     */
    @Test
    public void testDelete() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            System.out.println(employeeMapper.deleteEmployeeById(1));
            session.commit();
        } finally {
            session.close();
        }
    }





}

3 主键生成方式（以MySQL为例）

我们知道MySQL是支持主键自动生成的，而Oracle是不支持的。
那么对于MySQL我们可以在insert标签上设置useGeneratedKeys="truue"，然后再把keyProperty设置到目标属性上。

 <!-- 增加 -->
    <insert id="insertEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee"  useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
        INSERT INTO employee (last_name,gender,email) VALUE (#{lastName},#{gender},#{email})
    </insert>

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {
  

    public boolean insertEmployee(Employee employee);
    

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">
  
    <!-- 增加 -->
    <insert id="insertEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee"  useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
        INSERT INTO employee (last_name,gender,email) VALUE (#{lastName},#{gender},#{email})
    </insert>

</mapper>

- 测试

/**
     * 增加
     * @throws IOException
     */
    @Test
    public void testInsert() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee = new Employee();
            employee.setLastName("许威");
            employee.setGender("男");
            employee.setEmail("1975356467@qq.com");
            System.out.println(employeeMapper.insertEmployee(employee));
            System.out.println(employee);
            session.commit();
        } finally {
            session.close();
        }
    }

4 参数传递

4.1 单个参数

可以接受基本类型，对象类型，集合类型的之，这种情况下，Mybatis可以直接使用这个参数，不需要经过任何的处理。

示例：
- 基本类型
  - EmployeeSingleParamMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeSingleParamMapper {
    public Employee getEmployeeById(Integer id);

}

- - EmployeeSingleParamMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper">
    <!-- 查询 -->
    <select id="getEmployeeById" resultType="com.xuweiwei.mybatis.pojo.Employee" >
        select id,last_name ,gender,email  from employee where id = #{id}
    </select>

</mapper>

- - mybatis-config.xml

<?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>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <databaseIdProvider type="DB_VENDOR">
        <property name="Oracle" value="oracle"/>
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>
    <mappers>
        <mapper resource="com/xuweiwei/mybatis/mapper/EmployeeMapper.xml"/>
        <mapper resource="com/xuweiwei/mybatis/mapper/EmployeeSingleParamMapper.xml"/>
    </mappers>

</configuration>

- - 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest2 {
    /**
     * 查询
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeSingleParamMapper employeeSingleParamMapper = session.getMapper(EmployeeSingleParamMapper.class);
            System.out.println(employeeSingleParamMapper.getEmployeeById(3));
        } finally {
            session.close();
        }
    }

}

- 对象类型
  - EmployeeSingleParamMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeSingleParamMapper {
    public Long updateEmployee(Employee employee);

}

- - EmpoyeeSingleParamMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper">
    <!-- 修改 -->
    <update id="updateEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee">
        UPDATE employee SET last_name = #{lastName},gender=#{gender},email=#{email} WHERE id = #{id}
    </update>

</mapper>

- - 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest2 {
    /**
     * 修改
     */
    @Test
    public void testUpdate() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeSingleParamMapper employeeSingleParamMapper = session.getMapper(EmployeeSingleParamMapper.class);
            Employee employee = new Employee();
            employee.setId(3);
            employee.setLastName("许威");
            employee.setGender("女");
            employee.setEmail("1975356467@qq.com");
            employeeSingleParamMapper.updateEmployee(employee);
            session.commit();
        } finally {
            session.close();
        }
    }

}

- 集合类型（如果是List、Set、Collection集合或者数组的话，Mybatis会对做特殊处理，key分别是collection、list、array）
  - EmployeeSingleParamMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

import java.util.List;

public interface EmployeeSingleParamMapper {

   public List<Employee> getEmployeeByIds(List<Integer> ids);


}

- - EmployeeSingleParamMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper">

    <select id="getEmployeeByIds" resultType="com.xuweiwei.mybatis.pojo.Employee" parameterType="java.lang.Integer">
        SELECT * FROM employee where id IN
        <foreach collection="list" item="id" index="index"
                 open="(" close=")" separator=",">
            #{id}
        </foreach>

    </select>


</mapper>

- - 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeSingleParamMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

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

public class MybatisTest2 {
    /**
     * 修改
     */
    @Test
    public void testSelect() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeSingleParamMapper employeeSingleParamMapper = session.getMapper(EmployeeSingleParamMapper.class);
            List<Integer> ids = new ArrayList<>();
            ids.add(3);
            ids.add(4);
            List<Employee> employees = employeeSingleParamMapper.getEmployeeByIds(ids);
            for(Employee employee:employees){
                System.out.println(employee);
            }
            session.commit();
        } finally {
            session.close();
        }
    }

}

- 参数是List集合
  - EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

    public void insertEmployee(List<Employee> employees);

}

- - EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <insert id="insertEmployee" parameterType="java.util.Map">
        INSERT INTO employee(last_name,gender,email)
        VALUES (#{list[0].lastName},#{list[0].gender},#{list[0].email}),(#{list[1].lastName},#{list[1].gender},#{list[1].email})
    </insert>

</mapper>

- - 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

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

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            List<Employee> employees = new ArrayList<>();

            Employee emp1 = new Employee();
            emp1.setLastName("哈哈");
            emp1.setGender("男");
            emp1.setEmail("1232163.com");
            employees.add(emp1);


            Employee emp2 = new Employee();
            emp2.setLastName("呵呵");
            emp2.setGender("男");
            emp2.setEmail("1232163.com");
            employees.add(emp2);


            employeeMapper.insertEmployee(employees);

            session.commit();
        } finally {
            session.close();
        }
    }


}

- 参数是数组
  - EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

    public List<Employee> getEmpsByIds(Integer[] ids);

}

- - EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <select id="getEmpsByIds" resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT * FROM employee
        WHERE id IN
        (#{array[0]},#{array[1]})
    </select>

</mapper>

- - 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

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

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Integer[] ids = new Integer[2];
            ids[0] = 3;
            ids[1] = 4;

            List<Employee> emps = employeeMapper.getEmpsByIds(ids);
            for(Employee employee : emps){
                System.out.println(employee);
            }

            session.commit();
        } finally {
            session.close();
        }
    }


}

4.2 多个参数

如果是多个参数，Mybatis会做特殊的处理，会将参数封装成一个Map，key是param1，param2……或者索引也可以。

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public Employee getEmployeeByIdAndLastName(Integer id ,String lastName);


}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <select id="getEmployeeByIdAndLastName" resultType="com.xuweiwei.mybatis.pojo.Employee">
        select id,last_name ,gender,email  from employee where id = #{0} AND last_name = #{1}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee = employeeMapper.getEmployeeByIdAndLastName(3, "许威");
            System.out.println(employee);
        } finally {
            session.close();
        }
    }





}

4.3 命名参数

为参数使用@Param起一个名字，Mybatis就会将这些参数封装进Map中，key就是我们自己指定的名字

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

        import com.xuweiwei.mybatis.pojo.Employee;
        import org.apache.ibatis.annotations.Param;

public interface EmployeeMapper {

    public Employee getEmployeeByIdAndLastName(@Param("id") Integer id ,@Param("lastName") String lastName);


}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

<select id="getEmployeeByIdAndLastName" resultType="com.xuweiwei.mybatis.pojo.Employee">
    select id,last_name ,gender,email  from employee where id = #{id} AND last_name = #{lastName}
</select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee = employeeMapper.getEmployeeByIdAndLastName(3, "许威");
            System.out.println(employee);
        } finally {
            session.close();
        }
    }





}

4.4 POJO

当这些参数属于业务的POJO的时候，直接传递POJO

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public void insertEmployee(Employee employee);

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <insert id="insertEmployee" parameterType="com.xuweiwei.mybatis.pojo.Employee">
        INSERT INTO employee(last_name,gender,email) VALUES (#{lastName},#{gender},#{email})
    </insert>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee emp = new Employee();
            emp.setEmail("123@163.com");
            emp.setGender("女");
            emp.setLastName("凤姐");
            employeeMapper.insertEmployee(emp);
            session.commit();
        } finally {
            session.close();
        }
    }


}

4.5 Map

我们也可以将参数封装到一个Map对象中，直接传递

、

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import java.util.Map;

public interface EmployeeMapper {

    public void insertEmployee(Map<String, Object> param);

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <insert id="insertEmployee" parameterType="java.util.Map">
        INSERT INTO employee(last_name,gender,email) VALUES (#{lastName},#{gender},#{email})
    </insert>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.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.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Map<String,Object> map = new HashMap<>();
            map.put("lastName", "哈哈");
            map.put("email", "123@163.com");
            map.put("gender", "女");
            employeeMapper.insertEmployee(map);


            session.commit();
        } finally {
            session.close();
        }
    }


}

5 #{}和${}的区别

相同点：都可以获取map中的值或者POJO对象属性的值。
不同点：
- #{}是以预编译的形式，将参数设置到sql语句中
- ${}将取出的值直接拼接到sql中。会有SQL注入问题。一般用在原生JDBC不支持占位符的地方，就可以使用${}

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;
import org.apache.ibatis.annotations.Param;

public interface EmployeeMapper {

    public Employee getEmpByIdAndListName(@Param("id") Integer id, @Param("lastName") String lastName);

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <select id="getEmpByIdAndListName" resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT * FROM employee WHERE id = ${id} AND last_name = #{lastName}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee emp = employeeMapper.getEmpByIdAndListName(3,"许威");
            System.out.println(emp);
            session.commit();
        } finally {
            session.close();
        }
    }


}

示例：表名使用${}
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;
import org.apache.ibatis.annotations.Param;

public interface EmployeeMapper {

    public Employee getEmpByIdAndListName(@Param("id") Integer id, @Param("lastName") String lastName,@Param("tabName") String tabName);

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <select id="getEmpByIdAndListName" resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT * FROM ${tabName} WHERE id = ${id} AND last_name = #{lastName}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee emp = employeeMapper.getEmpByIdAndListName(3,"许威","employee");
            System.out.println(emp);
            session.commit();
        } finally {
            session.close();
        }
    }


}

6 select标签

select元素用来定义查询操作。
- 属性
  - id：唯一标识符。用来引用这条语句，需要和接口的方法名一致。
  - parameterType：参数类型。可以不传，Mybatis会根据TypeHandler自动推断。
  - resultType：返回值类型。别名或者全类名，如果返回的是集合，定义集合中元素的类型。不能和resultMap同时使用。

6.1 resultType

6.1.1 返回List集合

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

    public List<Employee> getEmpsByName(String name);

}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">


    <select id="getEmpsByName" resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT *
        FROM employee
        WHERE last_name like #{lastName}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

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

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            List<Employee> emps = employeeMapper.getEmpsByName("%许%");
            for(Employee employee:emps){
                System.out.println(employee);
            }
        } finally {
            session.close();
        }
    }


}

6.1.2 返回Map集合

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import java.util.Map;

public interface EmployeeMapper {


    public Map<String,Object> getEmpById(Integer id);


}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">


    <select id="getEmpById" resultType="map">
        SELECT *
        FROM employee
        WHERE id = #{id}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.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.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Map<String,Object> map = employeeMapper.getEmpById(3);
            System.out.println(map);
        } finally {
            session.close();
        }
    }


}

6.1.3 返回Map<主键，记录所对应的集合对象类型>

示例：
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;
import org.apache.ibatis.annotations.MapKey;

import java.util.Map;

public interface EmployeeMapper {

    @MapKey("id")
    public  Map<Integer,Employee> getEmpsByName(String name);


}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">


    <select id="getEmpsByName" resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT *
        FROM employee
        WHERE last_name like #{name}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

        import com.xuweiwei.mybatis.mapper.EmployeeMapper;
        import com.xuweiwei.mybatis.pojo.Employee;
        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.Test;

        import java.io.IOException;
        import java.io.InputStream;
        import java.util.Map;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Map<Integer,Employee> map = employeeMapper.getEmpsByName("%许%");
            System.out.println(map);
        } finally {
            session.close();
        }
    }


}

6.2 resultMap

6.2.1 自定义结果集

我们需要知道的是数据库中的字段是last_name，而实体类中的属性是lastName，如果要实现双方之间的映射，有如下三种方法。

①在全局配置文件中，设置如下属性

<settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

②就是在查询的时候使用别名

 SELECT id,last_name lastName,gender,email WHERE id = #{id}

③自定义结果集
- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public Employee getEmployeeById(Integer id);



}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <resultMap id="empMap" type="com.xuweiwei.mybatis.pojo.Employee">
        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="gender" column="gender"/>
        <result property="email" column="email"/>
    </resultMap>

    <select id="getEmployeeById" resultMap="empMap" >
        SELECT * FROM employee WHERE id = #{id}
    </select>


</mapper>

- 测试

package com.xuweiwei.mybatis.test;

        import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee  = employeeMapper.getEmployeeById(3);
            System.out.println(employee);
        } finally {
            session.close();
        }
    }


}

6.2.2 关联查询

脚本

CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255)    
)

ALTER TABLE employee ADD COLUMN department_id INT;
ALTER TABLE employee ADD CONSTRAINT fk_emp_department FOREIGN KEY (department_id) REFERENCES department(id)

6.2.2.1 关联查询之级联属性封装结果集

示例：
- Employee.java

package com.xuweiwei.mybatis.pojo;

public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;


    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender='" + gender + '\'' +
                ", email='" + email + '\'' +
                ", department=" + department +
                '}';
    }
}

- Department.java

package com.xuweiwei.mybatis.pojo;

public class Department {
    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public Employee getEmployeeAndDepartmentById(Integer id);



}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <resultMap id="empMap" type="com.xuweiwei.mybatis.pojo.Employee">
        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="gender" column="gender"/>
        <result property="email" column="email"/>
        <result property="department.id" column="did"/>
        <result property="department.name" column="dname"/>
    </resultMap>

    <select id="getEmployeeAndDepartmentById" resultMap="empMap">
        SELECT e.id id,e.last_name last_name,e.gender gender ,e.email email ,d.id did,d.name dname
        FROM `employee` e INNER  JOIN `department` d
        ON e.department_id = d.id
        WHERE e.id = #{id};
    </select>


</mapper>

- 测试

package com.xuweiwei.mybatis.test;

        import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee  = employeeMapper.getEmployeeAndDepartmentById(3);
            System.out.println(employee);
        } finally {
            session.close();
        }
    }


}

6.2.2.2 关联查询之association定义关联对象封装结果集

示例：
- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <resultMap id="empMap" type="com.xuweiwei.mybatis.pojo.Employee">
        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="gender" column="gender"/>
        <result property="email" column="email"/>
        <!--
            可以指定关联的JavaBean对象
            property:指定那个属性是联合对象
            javaType：指定这个属性对应的类型（不能少）
        -->
        <association property="department" javaType="com.xuweiwei.mybatis.pojo.Department" >
            <id property="id" column="did"/>
            <result property="name" column="dname"/>
        </association>

    </resultMap>

    <select id="getEmployeeAndDepartmentById" resultMap="empMap">
        SELECT e.id id,e.last_name last_name,e.gender gender ,e.email email ,d.id did,d.name dname
        FROM `employee` e INNER  JOIN `department` d
        ON e.department_id = d.id
        WHERE e.id = #{id};
    </select>


</mapper>

其它代码和6.2.2.1相同。

6.2.2.3 关联查询之association分步查询

示例：
- Employee.java

package com.xuweiwei.mybatis.pojo;

public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;


    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender='" + gender + '\'' +
                ", email='" + email + '\'' +
                ", department=" + department +
                '}';
    }
}

- Department.java

package com.xuweiwei.mybatis.pojo;

public class Department {
    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

- DepartmentMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Department;

public interface DepartmenMapper {

    public Department getDepartmentById(Integer id);
}

- DepartmentMapper.xml

<?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.xuweiwei.mybatis.mapper.DepartmenMapper">

    <select id="getDepartmentById" resultType="com.xuweiwei.mybatis.pojo.Department">
        SELECT id,name FROM department WHERE id = #{id}
    </select>

</mapper>

- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public Employee getEmployeeAndDepartmentById(Integer id);



}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">

    <resultMap id="empMap" type="com.xuweiwei.mybatis.pojo.Employee">
        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="gender" column="gender"/>
        <result property="email" column="email"/>

        <!--
            分步查询
        -->
        <association property="department"  select="com.xuweiwei.mybatis.mapper.DepartmenMapper.getDepartmentById" column="department_id">
        </association>

    </resultMap>

    <select id="getEmployeeAndDepartmentById" resultMap="empMap">
        SELECT  e.`id` id ,e.`last_name` last_name,e.`gender` gender,e.`email` email,e.`department_id` department_id
        FROM employee e
        WHERE id = #{id}
    </select>


</mapper>

- 测试

package com.xuweiwei.mybatis.test;

        import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee  = employeeMapper.getEmployeeAndDepartmentById(3);
            System.out.println(employee);
        } finally {
            session.close();
        }
    }


}

6.2.2.4 分步查询&延迟加载

分步查询才有延迟加载。

在全局配置文件中配置如下信息

 <setting name="lazyLoadingEnabled" value="true"/>
 <setting name="aggressiveLazyLoading" value="false"/>

测试（其余代码和6.2.2.3相同）

package com.xuweiwei.mybatis.test;

        import com.xuweiwei.mybatis.mapper.EmployeeMapper;
import com.xuweiwei.mybatis.pojo.Employee;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            Employee employee  = employeeMapper.getEmployeeAndDepartmentById(3);
            System.out.println(employee.getLastName());
        } finally {
            session.close();
        }
    }


}

6.2.2.5 关联查询之collection定义关联集合封装结果集

示例：查询部门的时候，将部门对应的员工信息查询出来
- Employee.java

package com.xuweiwei.mybatis.pojo;

public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;


    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender='" + gender + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

- Department.java

package com.xuweiwei.mybatis.pojo;

import java.util.List;

public class Department {
    private Integer id;
    private String name;

    private List<Employee> employees;

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", employees=" + employees +
                '}';
    }
}

DepartmentMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Department;

public interface DepartmenMapper {

    public Department getDepartmentById(Integer id);
}

DepartmentMapper.xml

<?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.xuweiwei.mybatis.mapper.DepartmenMapper">

    <resultMap id="departmentMap" type="com.xuweiwei.mybatis.pojo.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--
            collection 定义关联集合类型的属性的封装规则
            ofType：集合里面的元素的类型
        -->
        <collection property="employees" ofType="com.xuweiwei.mybatis.pojo.Employee">
            <id property="id" column="eid"/>
            <result property="lastName" column="last_name"/>
            <result property="gender" column="gender"/>
            <result property="email" column="email"/>
        </collection>

    </resultMap>

    <select id="getDepartmentById" resultMap="departmentMap">
        SELECT d.`id` id, d.`name` NAME ,e.`id` eid,e.`last_name` last_name,e.`gender` gender,e.`email` email
        FROM `department` d LEFT OUTER JOIN `employee` e
        ON d.`id` = e.`department_id`
        WHERE d.`id` = #{id}
    </select>

</mapper>

测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.DepartmenMapper;
import com.xuweiwei.mybatis.pojo.Department;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            DepartmenMapper departmenMapper = session.getMapper(DepartmenMapper.class);
            Department department = departmenMapper.getDepartmentById(1);
            System.out.println(department);

        } finally {
            session.close();
        }
    }


}

6.2.2.5 关联查询之collection分步查询

示例：
- Employee.java

package com.xuweiwei.mybatis.pojo;

public class Employee {
    private Integer id;
    private String lastName;
    private String gender;
    private String email;


    private Department department;

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", gender='" + gender + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

- Department.java

package com.xuweiwei.mybatis.pojo;

import java.util.List;

public class Department {
    private Integer id;
    private String name;

    private List<Employee> employees;

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", employees=" + employees +
                '}';
    }
}

- EmployeeMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Employee;

public interface EmployeeMapper {

    public Employee getEmployeeById(Integer departmentId);



}

- EmployeeMapper.xml

<?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.xuweiwei.mybatis.mapper.EmployeeMapper">


    <select id="getEmployeeById"  resultType="com.xuweiwei.mybatis.pojo.Employee">
        SELECT  e.`id` id ,e.`last_name` last_name,e.`gender` gender,e.`email` email
        FROM employee e
        WHERE e.`department_id` = #{departmentId}
    </select>


</mapper>

- DepartmentMapper.java

package com.xuweiwei.mybatis.mapper;

import com.xuweiwei.mybatis.pojo.Department;

public interface DepartmenMapper {

    public Department getDepartmentById(Integer id);
}

- DepartmentMapper.xml

<?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.xuweiwei.mybatis.mapper.DepartmenMapper">

    <resultMap id="departmentMap" type="com.xuweiwei.mybatis.pojo.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--
            collection 定义关联集合类型的属性的封装规则
            ofType：集合里面的元素的类型
        -->
        <collection property="employees"  select="com.xuweiwei.mybatis.mapper.EmployeeMapper.getEmployeeById" column="id">
        </collection>

    </resultMap>

    <select id="getDepartmentById" resultMap="departmentMap">
        SELECT d.`id` id, d.`name` `name`
        FROM `department` d
        WHERE d.`id` = #{id}
    </select>

</mapper>

- 测试

package com.xuweiwei.mybatis.test;

import com.xuweiwei.mybatis.mapper.DepartmenMapper;
import com.xuweiwei.mybatis.pojo.Department;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {
    /**
     * 查询
     *
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        try {
            DepartmenMapper departmenMapper = session.getMapper(DepartmenMapper.class);
            Department department = departmenMapper.getDepartmentById(1);
            System.out.println(department);
            System.out.println(department.getEmployees());

        } finally {
            session.close();
        }
    }


}

JAVA学习网

分类

Mybatis（二）

1 Mybatis映射文件

2 增加、删除、修改、查询示例

3 主键生成方式（以MySQL为例）

4 参数传递

4.1 单个参数

4.2 多个参数

4.3 命名参数

4.4 POJO

4.5 Map

5 #{}和${}的区别

6 select标签

6.1 resultType

6.1.1 返回List集合

6.1.2 返回Map集合

6.1.3 返回Map<主键，记录所对应的集合对象类型>

6.2 resultMap

6.2.1 自定义结果集

6.2.2 关联查询

6.2.2.1 关联查询之级联属性封装结果集

6.2.2.2 关联查询之association定义关联对象封装结果集

6.2.2.3 关联查询之association分步查询

6.2.2.4 分步查询&延迟加载

6.2.2.5 关联查询之collection定义关联集合封装结果集

6.2.2.5 关联查询之collection分步查询