mybatis增删改查

基于XML方式映射SQL

结果集映射

resultMap通常用于比较复杂的结果集映射(如：多表关联查询)的情况，使用步骤如下：

显示定义标签映射结果集

<resultMap id="userResultMap" type="User">
  <id property="id" column="user_id" />
  <result property="username" column="user_name"/>
  <result property="password" column="hashed_password"/>
</resultMap>

在查询语句中引用我们定义的resultMap：

<select id="selectUsers" resultMap="userResultMap">
  select user_id, user_name, hashed_password
  from some_table
  where id = #{id}
</select>

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


<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
    <!-- 此处的id是查询语句的名称，对应接口中的方法名 -->
    <select id="queryAll" resultType="Teacher">
        select * from teacher;
      </select>

    <!-- 带一个简单类型的参数， 这种情况下parameterType属性可以省略，mybatis可以自动推断出类型 -->
    <select id="queryById" parameterType="int" resultType="Teacher">
        select * from teacher where id = #{id};
    </select>

    <!-- 带两个参数，需要在接口中通过@Param注解指定名称（因为编译时参数名不会保留） -->
    <select id="queryByIdAndAge" resultType="Teacher">
        select * from teacher where id = #{id} and age &lt;= #{age};
    </select>

    <!-- insert、update、delete的返回值都是int（影响行数） -->
    <!-- 自定义类型参数，通过#{属性名}可以直接获取引入类型参数的属性值 -->
    <insert id="insertTeacher" parameterType="Teacher">
       insert into teacher(tname) values (#{tname});
    </insert>

    <insert id="insertTeacherByParam">
       insert into teacher(tname, age) values (#{tname}, #{age});
    </insert>

    <update id="updateTeacherById" parameterType="Teacher">
        update teacher set tname = #{tname}, age = #{age} where id = #{id}
    </update>

    <delete id="deleteTeacherById">
        delete from teacher where id = #{id};
    </delete>
</mapper>

package com.lanou3g.mybatis.bean;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Teacher {
    private Integer id;
    private String tname;
    private Integer age;

    public Teacher (){}

    public Teacher(String tname) {
        this.tname = tname;
    }

    @Override
    public String toString() {
        return "{" +
                "id:" + id +
                ", tname:'" + tname + '\'' +
                ", age:'" + age + '\'' +
                "}\n";
    }
   
}

对应接口

package com.lanou3g.mybatis.dao;

import com.lanou3g.mybatis.bean.Teacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TeacherDao {

    List<Teacher> queryAll();

    /**
     * 多参数查询
     * @param id
     * @param age
     * @return
     */
    List<Teacher> queryByIdAndAge(@Param("tid") int id, @Param("age") int age);

    int insertTeacher(Teacher teacher);

    int insertTeacherByParam(@Param("tname") String tname, @Param("age") int age);

    Teacher queryById(int id);

    int updateTeacherById(Teacher teacher);

    int deleteTeacherById(int id);
}

测试类

package com.lanou3g.spring;

import com.lanou3g.mybatis.MyBatisTools;
import com.lanou3g.mybatis.bean.Student;
import com.lanou3g.mybatis.bean.Teacher;
import com.lanou3g.mybatis.dao.CategoryDao;
import com.lanou3g.mybatis.dao.StudentDao;
import com.lanou3g.mybatis.dao.TeacherDao;
import lombok.extern.slf4j.Slf4j;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import java.util.List;

/**
 * Unit test for simple App.
 */
@Slf4j
public class AppTest {

    TeacherDao teacherDao = null;
    StudentDao studentDao = null;
    CategoryDao categoryDao = null;

    @Before
    public void setUp() {
        categoryDao = MyBatisTools.getInstance().getSessionFactory("taotao").openSession().getMapper(CategoryDao.class);
        studentDao = MyBatisTools.getInstance().openSession().getMapper(StudentDao.class);
        teacherDao =  MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
    }


    /**
     * 练习查询多个库(用到了多环境配置)
     */
    @Test
    public void testMultiDatabase() {
        // 查询其他数据库中的数据
        System.out.println(categoryDao.queryAll());
    }

    @Test
    public void testQueryAll() {
        // 查询学生表
        List<Student> studentList = studentDao.queryAll();
//        List<Student> studentList = studentDao.queryResultMap();
        log.info("studentList: " + studentList);

        // 查询教师表
//        TeacherDao teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
//        List<Teacher> teacherList = teacherDao.queryAll();
//        log.info("teacherList: " + teacherList);
    }

    @Test
    public void testInsert() {
        // 新增Teacher表
        System.out.println("--------------插入前：");
        List<Teacher> teacherList = teacherDao.queryAll();
        System.out.println(teacherList);

        int ret = teacherDao.insertTeacher(new Teacher("铜赛赛"));
        log.info("影响的行数: " + ret);

        // 比较low的写法（不推荐）
        //int ret = teacherDao.insertTeacherByParam("哈哈哥", 99);
        //log.info("影响的行数: " + ret);

        System.out.println("--------------插入后：");
        teacherList = teacherDao.queryAll();
        System.out.println(teacherList);
    }

    @Test
    public void testQueryById() {
        Teacher teacher = teacherDao.queryById(6);
        System.out.println(teacher);
    }

    /**
     * 多个参数查询语句
     */
    @Test
    public void testQueryByIdAndAge() {
        List<Teacher> teacherList = teacherDao.queryByIdAndAge(6, 99);
        log.info("查询结果："  + teacherList);
    }

    @Test
    public void testUpdate() {
        Teacher teacher = new Teacher();
        teacher.setId(6);
        teacher.setAge(99);
        teacher.setTname("托尼托尼乔巴老师");
        int rows = teacherDao.updateTeacherById(teacher);
        log.info("更新行数：" + rows);
    }
    @Test
    public void testDelete() {
        int rows = teacherDao.deleteTeacherById(30);
        log.info("删除行数：" + rows);
    }