mybatis学习 (四) 了解映射器的配置

映射器是javaBean和数据库表沟通的桥梁， 必须掌握它的使用
它的内容如下表

select

---

这是最简单的用法了， 根据id来返回一个实体。

        <select id="getStudent" parameterType="integer" resultType="student">
            select * from student where stuId = #{stuId}
        </select>

参数解释
id 和接口中的方法定义一致
parameterType 要传给sql的参数类型, 可以是全路径或别名
resultType 结果类型，可以是全路径或别名

统计姓王的学生人数， 步骤如下

1 . 在接口中定义方法

public Student countFirstName(String name);

2 . 配置select标签
这里需要注意的是like的 %写法

<select id="countFirstName" parameterType="string" resultType="integer">
            select count(*) FROM student where stuName like concat(#{firstName} ,'%')
        </select>

3 . 测试

    @Test
    public void test() throws IOException {
        SqlSession sqlSession = null;
        try {
           sqlSession = SqlSessionFactoryUtil.openSqlSession();
           StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            System.out.println(studentMapper.countFirstName("王"));
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }

select的自动映射

假如有一个这样的字段，在数据库中是stu_id, 用下划线分割了两个词， 但是javabean中的命名为stuId， 两种不能互相对应。
解决办法是 ，用as将放回列表重命名成javabean的属性

 <select id="getStudent" parameterType="integer" resultType="student">
            SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday  FROM student WHERE stu_Id = #{stuId}
        </select>

多个值传递

上面传递一个值还是挺简单的， 当有多个值的时候 用map将列名对应的参数传入.
比如查找姓氏为王， 年龄18的学生
接口方法

 public List<Student> findByNameAndAge(Map<String, String> params);

xml

    <select id="findByNameAndAge" parameterType="map" resultType="student">
       SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
        FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
    </select>

测试

 @Test
    public void test() throws IOException {
        SqlSession sqlSession = null;
        try {
           sqlSession = SqlSessionFactoryUtil.openSqlSession();
           StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
           Map<String, String> paramsMap = new HashMap<String, String>();
           paramsMap.put("name","王");
           paramsMap.put("age","18");
           System.out.println(studentMapper.findByNameAndAge(paramsMap));
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }

多值传递 –注解方式

在接口方法参数上加上@Param(“name”) 也可以将值传入

public List<Student> findByNameAndAge(@Param("name") String name, @Param("age") Integer age);

xml, 不需要指定parameterType

 <select id="findByNameAndAge"  resultType="student">
       SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
        FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
    </select>

测试代码

studentMapper.findByNameAndAge("王",18)

使用javabean 方式传入

需要针对查询的参数来重新创建一个类，类的属性就是查询的参数，如下

public class StuParam {
    private String name;
    private Integer age;

    public String getName() {
        return name;
    }

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

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

接口创建以这个参数类为参数的方法

public List<Student> findByNameAndAge(StuParam stuParam);

xml, 指定输入类型为StuParam类

    <select id="findByNameAndAge"  parameterType="com.cyh.pojo.StuParam" resultType="student">
       SELECT stu_id AS stuId , stu_name AS stuName,stu_age AS stuAge,stu_major AS stuMajor,birthday
        FROM student WHERE stu_name LIKE concat(#{name},'%') AND stu_age = #{age};
    </select>

测试：

studentMapper.findByNameAndAge(new StuParam("王",18));

3种多值传递方法感觉还是注解的方便一些。

insert

---

插入的规则要比查询简单很多， 只要注意普通的查询是不返回主键的，如下代码

 @Test
    public void test() throws IOException {
        SqlSession sqlSession = null;
        try {
           sqlSession = SqlSessionFactoryUtil.openSqlSession();
           StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

           Student stu = new Student();
           stu.setStuName("刘si");
           stu.setStuAge(12);
           stu.setStuMajor("软件");
           stu.setBirthday(new Date());

            studentMapper.insertStudent(stu);
            sqlSession.commit();

            System.out.println(stu.getStuId());
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }

输出的id是null， 主键不回填， 后面若是需要对stu这个对象操作的话就麻烦了， 在xml中有解决的办法，如下 . 设置useGeneratedKeys为true ， keyProperty为pojo的主键属性

 <insert id="insertStudent" parameterType="student"
            useGeneratedKeys="true" keyProperty="stuId">
            insert into student(stu_name, stu_age, stu_major, birthday) values(#{stuName},#{stuAge},#{stuMajor},#{birthday})
        </insert>

update

参数传递的方式和select 一样
接口如下

public Integer updateStudent(Student stu);

xml 如下

    <update id="updateStudent" parameterType="student"  >
        UPDATE student SET stu_name=#{stuName} WHERE stu_id = #{stuId}
    </update>

测试

@Test
    public void test() throws IOException {
        SqlSession sqlSession = null;
        try {
           sqlSession = SqlSessionFactoryUtil.openSqlSession();
           StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

           Student stu = new Student();
           stu.setStuId(6);
           stu.setStuName("新名字哎");
           studentMapper.updateStudent(stu);
            sqlSession.commit();
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }

注意一点是， 这里只按id修改了学生的姓名，没有传入stuMajor参数, 但是数据库不会将stu_major字段设置为null， 而是保留上一次的内容

delete

     <delete id="deleteStudent" parameterType="integer">
         delete FROM student WHERE stu_id = #{stuid}
     </delete>