MyBatus学习-3

再经过MyBatis的Helloworld程序学习，我们知道Mapper的配置文件就是MyBatis的核心，也是他的魅力所在，魔法之源，至尊奥义。接下来我们先去补全我们的增删改查操作，在EmployeeMappper接口中添加剩下的三个方法：

    public boolean addEmp(Employee employee);
    public void updateEmp(Employee employee);
    public void deleteEmp(Integer id);

这里我把addEmp的返回值类型设为了boolean，因为我想知道我在运行成功后，能不能给我一个提示，在MyBatis允许增删改查直接定义以下的类型：

Integer
Long
Boolean

mysql支持自增逐渐，自增主键值的获取，MyBatis也是利用JDK的statement.getGenreateKeys（）；useGenerateKeys=“true”；使用自增主键获取主键值策略，keyProerty：指定对应主键值，也就是MyBatis获得到主键值后，封装给java的那个属性。接下来我们还是回到Mapper文件当中，添加相应的sql映射：

  <insert id="addEmp" useGeneratedKeys="true" keyProperty="id">
      insert into tbl_employee(last_name,gender,email)
      values (#{Lastname},#{gender},#{email})
  </insert>
    
  <update id="updateEmp">
      update tbl_employee
      set last_name=#{Lastname},email=#{email},gender=#{gender}
      where id = #{id}
  </update>

  <delete id="deleteEmp">
      delete from tbl_employee where id=#{id}
  </delete>

最后回到我们的测试类写一个新的测试，试试这些新的功能有没有实现：

  @Test
  public void test3() throws IOException
  {
      SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
      //1.获得的SqlSession不会自动提交
      SqlSession openSession = sqlSessionFactory.openSession();


      //MyBatis允许增上改查直接定义以下类型返回值
      //Integer，Long，Boolean，void
      try {
          EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);

          Employee employee = new Employee(null,"huwenhao","huwenhao@lookout.com","1");
          //添加员工
          Boolean bool = mapper.addEmp(employee);
          System.out.println(employee.getId());
//            System.out.println(bool);

          //更改员工
//            mapper.updateEmp(employee);

          //删除
//            mapper.deleteEmp(2);
          //2.手动提交数据
          openSession.commit();
      }finally {
          openSession.close();
      }
  }

把上面这些操作都完成了，我们对数据库的基本操作就算是告一段落了，开始我们正式的对Mapper配置文件的学习吧！

1.参数处理

单个参数：#{参数名}：取出参数
多个参数：会被封装成一个Map#{}就是从map中获取指定的key的值，但是value才是我们要的,所以我们在参数的{}当中要写入arg0，arg1…，或者是param1，param2…这样子的，当然你也可以选择在接口方法的参数中用@Param（“”）为你的param1，param2…绑定。

好，我先去接口写一个一个多个参数的方法，并用@param对其进行绑定

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

接下来在Mapper文件当中添加我们的sql语句,我用了两种方法去写sql，用的时候用一个就好了：

  <select id="getEmpByIdAndLastName" resultType="com.snake_lvyonghao.MyBatis.bean.Employee">
      select * from tbl_employee where id = #{id} and last_name = #{lastName}
  </select>

  <select id="getEmpByIdAndLastName" resultType="com.snake_lvyonghao.MyBatis.bean.Employee">
      select * from tbl_employee where id = #{param1} and last_name = #{param2}
  </select>

最后放上一个测试test04:

  @Test
  public void test04() throws IOException{
      SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
      //1.获得的SqlSession不会自动提交
      SqlSession openSession = sqlSessionFactory.openSession();


      try {
          EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);

          Employee employee = mapper.getEmpByIdAndLastName(1,"huwenhao");

          System.out.println(employee);

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

如果多个参数正好是我们业务逻辑的模型，我们就可以传入pojo，#{属性名}，取出传入的pojo属性，如果多个参数不是业务模型中的数据，没有对应的pojo，为了方便，我们也可以传入map,#{key}:value,甚至你可以传入List，但是实际上还是封装在Map当中，如果多个参数不是业务模型的数据，但是要经常使用，推荐编写一个TO（Transfer Object）数据传输对象

我们这里做一个map的方式，at first我们给接口在写一个方法参数为map

public Employee getEmpByMap(Map<String,Object> map);

在配置文件中添加一条sql：

select * from tbl_employee where id = #{id} and last_name = #{lastName}
    </select>

来实践一下吧：

    @Test
    public void test04() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获得的SqlSession不会自动提交
        SqlSession openSession = sqlSessionFactory.openSession();


        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);

//            Employee employee = mapper.getEmpByIdAndLastName(1,"huwenhao");

            Map<String,Object> map = new HashMap<>();
            map.put("id",1);
            map.put("lastName","huwenhao");

            Employee employee = mapper.getEmpByMap(map);

            System.out.println(employee);

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

(#{}):更丰富的用法(规定参数的一些规则)

javaType,jdbcType,mode(岑出过程)，numericScale,resultMap,typeHandler,jdbcTypeName,expression
jdbcType通常需要在某种特定条件下被设置：在我们数据为null的时候，有些数据库可能不能识别MyBatis对null的默认处理，比如Orcale（报错）JdbcType OTHER 无效类型，因为MyBatis对null默认对应都是原生JDBC的OTHER类型，所以我们在使用Oracle数据库通常会在为null的#{}里面设置jdbcType#{email,jdbcType=NULL}

2.select

下面我们来仔细看一下Mapper中的select标签

2.1 resultType

在之前我们返回值都是void或者Object，如果我们想要返回一个List,或者是Map该怎么办呢？我们的resultType莫非要写成List或者是Map类型吗？不是的List我们写的还是集合里的元素类型,Map就直接写map就好了MyaBatis已经为这些基本的类型起了别名,当然还可以返回的Map，key为你指定的类型，value为JavaBean，但你的返回值类行为JavaBean，key值该怎么办呢？这时候我们需要在接口的方法前面加上一个@MapKey(“JavaBean的成员”)：

首先我们还是去写接口的方法：

    public Employee getEmpByMap(Map<String,Object> map);

    public List<Employee> getEmpByLastNameLike(String lastnNme);

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

    @MapKey("id")
    public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastname);

Mapper文件当中加上我们的sql语句：

    <select id="getEmpByLastNameLike" resultType="com.snake_lvyonghao.MyBatis.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

    <select id="getEmpByIdMap" resultType="map">
        select * from tbl_employee where id = #{id}
    </select>

    <select id="getEmpByLastNameLikeReturnMap" resultType="com.snake_lvyonghao.MyBatis.bean.Employee">
        select * from tbl_employee where last_name like #{lastName}
    </select>

然后就写几个测试试一试，因为我是一个一个测试的，所以只留下了最后一个，之前的测试都被我注释了

    @Test
    public void test04() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //1.获得的SqlSession不会自动提交
        SqlSession openSession = sqlSessionFactory.openSession();


        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);

//            Employee employee = mapper.getEmpByIdAndLastName(1,"huwenhao");
//            Map<String,Object> map = new HashMap<>();
//            map.put("id",1);
//            map.put("lastName","huwenhao");

//            Employee employee = mapper.getEmpByMap(map);
//            System.out.println(employee);

//            List<Employee> employeeList = mapper.getEmpByLastNameLike("huwenhao");
//            for(Employee employee : employeeList){
//                System.out.println(employee);
//            }

//            Map<String,Object> map = mapper.getEmpByIdMap(3);
//            System.out.println(map );

            Map<Integer,Employee> employeeMap = mapper.getEmpByLastNameLikeReturnMap("huwenhao");
            System.out.println(employeeMap);

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

2.2 resultMap

刚才我们使用了返回类型为Map<key,values>,value设定为JavaBean的属性，但如果我们想设置的Key值不是JavaBean属性的该怎么办？我们使用resultMap，自定义resultMap，实现高级结果映射，通过resultMap我们自定义某个JavaBean的封装规则。resultMap和resultType只能使用一个.因为内容太多我把所有的讲解放在注释当中。

我们需要新建一张表tbl_dept来存放部门，他有两个conlumn，id以及dept_name，同时我们再tbl_employee这张表中添加一个新的comlumn：d_id，把d_id和tbl_dept中的id设置成外健约束。

我们先新建一个部门类，并在原本的雇员类中添加：

package com.snake_lvyonghao.MyBatis.bean;

import java.util.List;

public class Department {
    private Integer id;
    private String departmentName;
    private List<Employee> employees;

    public Integer getId() {
        return id;
    }

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

    public String getDepartmentName() {
        return departmentName;
    }

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

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

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

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

package com.snake_lvyonghao.MyBatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("EMP")
public class Employee {
    private Integer id;
    private String Lastname;
    private String email;
    private String gender;
    private Department department;

    public Employee(Integer id, String lastname, String email, String gender, Department department) {
        this.id = id;
        Lastname = lastname;
        this.email = email;
        this.gender = gender;
        this.department = department;
    }

    public Employee() {
        super();
    }

    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) {
        Lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

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

    public String getGender() {
        return gender;
    }

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

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

下面我们重新写一个EmployeeMapperPuls的Mapper文件：

package com.snake_lvyonghao.MyBatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("EMP")
public class Employee {
    private Integer id;
    private String Lastname;
    private String email;
    private String gender;
    private Department department;

    public Employee(Integer id, String lastname, String email, String gender, Department department) {
        this.id = id;
        Lastname = lastname;
        this.email = email;
        this.gender = gender;
        this.department = department;
    }

    public Employee() {
        super();
    }

    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) {
        Lastname = lastname;
    }

    public String getEmail() {
        return email;
    }

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

    public String getGender() {
        return gender;
    }

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

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

同样我们写一个DepartmentMapper的接口：

package com.snake_lvyonghao.MyBatis.Dao;

import com.snake_lvyonghao.MyBatis.bean.Department;

public interface DepartmentMapper {

    public Department getDeptById(Integer id);

    public Department getDepByIdPlus(Integer id);

    public Department getDepByIdstep(Integer id);

}

下面我们再写一个Departmentmapper文件

<?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.snake_lvyonghao.MyBatis.Dao.DepartmentMapper">
    <select id="getDeptById" resultType="com.snake_lvyonghao.MyBatis.bean.Department">
        select id,dept_name departmentName from tbl_dept where id = #{id}
    </select>

    <resultMap id="MyDept" type="com.snake_lvyonghao.MyBatis.bean.Department">
        <id column="did" property="id"/>
        <result column="dept_name" property="departmentName"/>

        <!--
        collection定义关联集合类型的属性的封装规则
        ofType指定集合里面的元素类型
        -->
        <collection property="employees" ofType="com.snake_lvyonghao.MyBatis.bean.Employee">
            <id column="eid" property="id"/>
            <result column="last_name" property="Lastname"/>
            <result column="email" property="email"/>
            <result column="gender" property="gender"/>
            <result column="d_id" property="d_id"/>
        </collection>
    </resultMap>

    <select id="getDepByIdPlus" resultMap="MyDept">
        select d.id did,d.dept_name dept_name,
        e.id eid,e.last_name last_name,e.email email,e.gender gender
        FROM tbl_dept d
        LEFT JOIN tbl_employee e
        ON d.id=e.d_id
        WHERE d.id = 2
    </select>

    <resultMap id="MyDeptstep" type="com.snake_lvyonghao.MyBatis.bean.Department">
        <id column="id" property="id"/>
        <result column="dept_name" property="departmentName"/>

        <!--
        collection定义关联集合类型的属性的封装规则
        ofType指定集合里面的元素类型
        select 指定类的指定方法
        -->
        <collection property="employees" select="com.snake_lvyonghao.MyBatis.Dao.EmployeeMapperPlus.getEmpsByDeptId"
        column="id">
        </collection>
    </resultMap>


    <select id="getDepByIdstep" resultMap="MyDeptstep">
        select id,dept_name from tbl_dept where id = #{id}
    </select>

    <!--扩展:多列的值传到cloumn中
    column="{key1=column1,key2=column2}"-->
</mapper>

最后再放上我们的两个测试：

    @Test
    public void test05() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

        SqlSession openSession = sqlSessionFactory.openSession();


        try {
            EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);

//            Employee employee = mapper.getEmpById(3);
//            System.out.println(employee);

//            Employee employeeAndDept = mapper.getEmpAndDept(1);
//            System.out.println(employeeAndDept);
//            System.out.println(employeeAndDept.getDepartment());

            Employee employee = mapper.getEmpByIdstep(1);
            System.out.println(employee);
            System.out.println(employee.getDepartment());

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

    @Test
    public void test06() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

        SqlSession openSession = sqlSessionFactory.openSession();


        try {
            DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);

//            Department department = mapper.getDepByIdPlus(1);
//            System.out.println(department.getDepartmentName());
//            System.out.println(department.getEmployees());

            Department department = mapper.getDepByIdstep(1);
            System.out.println(department.getDepartmentName());
            System.out.println(department.getEmployees());

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

3.总结

通过以上的学习我们应该掌握了MyBatis的select语句具体的使用方法，下面放上我们源代码的地址，方便大家查阅： SSM框架源码

MyBatis学习-3

配置文件

MyBatus学习-3

1.参数处理

2.select

2.1 resultType

2.2 resultMap

3.总结

CATALOG

FEATURED TAGS

FRIENDS