Mybatis-CRUD

1、 namespace

namespace中的包名要和Dao/mapper接口包名一致

后面用到了Student这个表，所以pojo

package com.kwok.pojo;

public class Student {
    private int sid;
    private String sname;
    private String gender;
    private int class_id;
    private String className;

    public Student() {
    }

    public Student(int sid, String sname, String gender, int class_id, String className) {
        this.sid = sid;
        this.sname = sname;
        this.gender = gender;
        this.class_id = class_id;
        this.className = className;
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getGender() {
        return gender;
    }

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

    public int getClass_id() {
        return class_id;
    }

    public void setClass_id(int class_id) {
        this.class_id = class_id;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", gender='" + gender + '\'' +
                ", class_id=" + class_id +
                ", className='" + className + '\'' +
                '}';
    }
}

2、 select

选择，查询语句；

• id：就是对应的namespace中接口的方法名；

• resultType：Sql语句执行的返回值！

• parameterType：参数类型

编写接口

// 根据tid查询学生
    Student getStudentById(int id);

编写对应的mapper中的sql语句

<!--select查询语句-->
    <select id="getStudentById" parameterType="int" resultType="com.kwok.pojo.Student">
        select stu.*,cla.caption as className from practice.student stu, practice.class cla where stu.class_id = cla.cid and stu.sid = #{id}
    </select>

注册配置文件

测试

@Test
    public void getStudentById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.getStudentById(1);
        System.out.println(student);
        sqlSession.close();
    }

3、insert

编写接口

// 插入一个学生
    int addStudent(Student student);

编写对应的mapper中的sql语句

<!--对象中的属性可以直接取出来-->
    <insert id="addStudent" parameterType="com.kwok.pojo.Student">
        insert into practice.student (sid,sname,gender,class_id) values (#{sid},#{sname},#{gender},#{class_id});
    </insert>

测试

// 增删改需要提交事务
    @Test
    public void addStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        int res = mapper.addStudent(new Student(5, "翠花", "女", 1, "三年二班"));
        if (res>0){
            System.out.println("添加学生成功");
        }

        // 提交事务
        sqlSession.commit();
        sqlSession.close();
    }

4、 update

编写接口

// 修改学生
    int updateStudent(Student student);

编写对应的mapper中的sql语句

<update id="updateStudent" parameterType="com.kwok.pojo.Student">
        update practice.student set sname = #{sname},gender = #{gender},class_id=#{class_id}  where sid = #{sid};
    </update>

测试

// 增删改需要提交事务
    @Test
    public void updateStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        int res = mapper.updateStudent(new Student(5, "翠花", "男", 2, "三年一班"));
        if (res>0){
            System.out.println("修改学生成功");
        }
        // 提交事务
        sqlSession.commit();
        sqlSession.close();
    }

5、 delete

编写接口

// 删除一个学生
    int deleteStudent(int id);

编写对应的mapper中的sql语句

<delete id="deleteStudent" parameterType="int">
        delete from practice.student where sid = #{id};
</delete>

测试

// 增删改需要提交事务
    @Test
    public void deleteStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        int res = mapper.deleteStudent(5);
        if (res>0){
            System.out.println("删除学生成功");
        }
        // 提交事务
        sqlSession.commit();
        sqlSession.close();
    }

注意点：

增删改必须提交事务

6、 分析错误

• 标签不要匹配错

• resource绑定mapper，需要使用路径

• 程序配置文件必须符合规范

• NullPointerException，没有注册到资源

• 输出的xml文件中存在中文乱码问题

• maven资源没有导出问题

7、 万能Map

假设实体类或数据库中的表字段过多，我们应当考虑使用Map！

编写接口

// 万能的Map
    int addStudent2(Map<String,Object> map);

编写对应的mapper中的sql语句

<!--传递map的key-->
    <insert id="addStudent2" parameterType="map">
        insert into practice.student (sid,sname,gender,class_id) values (#{id},#{name},#{gender},#{cid})
    </insert>

测试

// 增删改需要提交事务
    @Test
    public void addStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Map<String, Object> map = new HashMap<String,Object>();
        map.put("id",5);
        map.put("name","test");
        map.put("gender","男");
        map.put("cid",2);
        int res = mapper.addStudent2(map);
        if (res>0){
            System.out.println("添加学生成功");
        }
        // 提交事务
        sqlSession.commit();
        sqlSession.close();
    }

Map传递参数，直接在sql中取出key即可！【parameterType=”map”】

对象传递参数，直接在sql中取对象的属性即可【parameterType=”Object”】

只有一个基本数据类型参数的情况下，可以直接在sql中取到，不用写parameterType

多个参数用Map，或者***注解!***

8、 模糊查询

编写接口

    List<Student> getStudentLike(String value);

编写对应的mapper中的sql语句

<select id="getStudentLike" resultType="com.kwok.pojo.Student">
        select stu.*,cla.caption as className from practice.student stu, practice.class cla where stu.class_id = cla.cid and stu.sname like #{value}
</select>

测试

@Test
    public void getStudentLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        // java代码在执行的时候，传递通配符%%
        List<Student> stulist = mapper.getStudentLike("%铁%");
        for (Student student : stulist) {
            System.out.println(student);
        }
        sqlSession.close();
    }

下面这种也行（在sql拼接中使用通配符），但不建议使用，避免sql注入

<select id="getStudentLike" resultType="com.kwok.pojo.Student">
        select stu.*,cla.caption as className from practice.student stu, practice.class cla where stu.class_id = cla.cid and stu.sname like "%"#{value}"%"
</select>
@Test
    public void getStudentLike(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> stulist = mapper.getStudentLike("铁");
        for (Student student : stulist) {
            System.out.println(student);
        }
        sqlSession.close();
    }