Mybatis接口结合xml、字段属性匹配以及联表查询

1.映射文件下<mapper>标签属性namespace要和接口所在的包匹配
2.各操作标签id要与接口中对应方法名一致==>作为接口的实现类
    public interface RoleDao {
        public void addRole(Role role);
        public List<Role> selectById(@Param("min")int min,@Param("max")int max);   
    }    //@param注解参数命名,默认为[0, 1, param1, param2],见补充五

<?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.zhiyou100.yj.dao.OrderDao">    
    <insert id="addRole" parameterType="com.zhiyou100.yj.bean.Role">
        insert into role(uname,description) values(#{uname},#{description})
    </insert>
    <select id="selectById" resultType="com.zhiyou100.yj.bean.Role">
        select * from role where id>#{min} and id&lt;=#{max};
    </select>
</mapper>    

class RoleMapperTest {
    static SqlSession session=null;
    static RoleDao roleDao=null;

    @BeforeAll
    static void setUpBeforeClass() throws Exception {
        Reader reader = Resources.getResourceAsReader("config.xml");
        SqlSessionFactory SessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session = SessionFactory.openSession();
        roleDao = session.getMapper(RoleDao.class);
    }

    @Test
    void testAddRole() {
        Role role=new Role("李大炳","助理");
        roleDao.addRole(role);
    }
    @Test
    void testSelectById() {
        List<Role> roles = roleDao.selectById(3, 4);
        System.out.println(roles);
    }

    @AfterAll
    static void tearDownAfterClass() throws Exception {
        session.commit();
    }
}

直接传参会报告以下异常:
Error querying database. 
Cause:org.apache.ibatis.binding.BindingException: Parameter ‘uname‘ not found. Available parameters are [0, 1, param1, param2]
这是由于mabatis把参数默认封装到map中,它的键有两种形式[0, 1, param1, param2]
解决这个问题有3种方法:
1、把参数改为Mybatis默认的参数[arg0, argX, param1, paramX]（X对应参数个数）;
2、把参数封装为一个JavaBean,然后把接口参数改为这个JavaBean;
3、方法传入map参数;
4、接口参数上使用@Param注解;（推荐）

public class Role{
    private int id;
    private String uname;
    private String description;
}
<update id="updateRole">
    update role set role_uname=#{uname},role_description=#{description} where role_id=#{id}
</update>
报错:(查询时不匹配属性结果心显示为null)
org.apache.ibatis.binding.BindingException:
Type interface com.zhiyou100.yj.dao.RoleDao is not known to the MapperRegistry.

方法一:为字段起别名以匹配实体类属性
<select id="selectById" parameterType="int" resultType="com.zhiyou100.yj.bean.Role">
    select role_uname name,role_description description,role_id id from role where role_id=#{id}   
</select>
方法二:resultMap属性替换resultType属性,并在resultMap中写字段属性对照关系
<select id="selectById" parameterType="int" resultMap="roleMap">
    select * from role where role_id=#{id}   
</select>
<resultMap type="com.zhiyou100.yj.bean.Role" id="roleMap">    <!-- type:表示那个实体类与表的对应关系,类比返回类型-->
    <id column="role_id" property="id"/>
    <result column="role_description" property="description"/>
    <result column="role_uname" property="name"/>
</resultMap>

1、多对一查询;一对一查询(一的一方作为另一方的属性,查询结果封装到对象)    //如每个班级对应一个班主任
2、一对多查询(多的一方构成集合作为一的属性,查询结果封装到集合)    //如每个班级包含多个学生

1、多对一查询;一对一查询
public class Clazz {
      private int cId;
      private String cName;
      private int teacheId;
      private Teacher teacher;    //关联的是老师对象
      private List<Student> students;    //关联的是学生对象集合
}
public class Teacher {
      private int id;
      private String tName;
}
public class Student {
      private int  sId;
      private String sName;
      private int classId;
}

<!-- 一对一联表 -->
<select id="selectClazzById" resultMap="ClazzMap">
    select * from class c,teacher t where c.teacher_id=t.t_id  and c_id=#{id}
</select>
      
<resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap">
    <id column="c_id" property="cId"/>
    <result column="c_name" property="cName"/>
    <result column="teacher_id" property="teacheId"/>
<!--
    property:Teacher对象属性
    javaType:Teacher对象类型
-->
<association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher">
    <id column="t_id" property="id"/>
    <result column="t_name" property="tName"/>
</association>
</resultMap>

<!-- 一对一嵌套 -->
<select id="selectClazzById2" resultMap="ClazzMap2">
    select * from class where c_id=#{cid}    <!--查询班级id,根据查询结果中获得的班主任id进一步查询-->
</select>

<resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap2">
    <id column="c_id" property="cId"/>
    <result column="c_name" property="cName"/>
    <result column="teacher_id" property="teacheId"/>
<!--
    property:Teacher对象属性
    javaType:Teacher对象类型
    select:对应进一步查询语句的id（另一张表查询）
    column:进一步查询对应的条件字段（外键字段）
-->
    <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher" select="selectTeacher"  column="teacher_id">
    </association>
</resultMap>

<!--写法一:属性字段不匹配,起别名-->
<select id="selectTeacher"  resultType="com.zhiyou100.yj.bean.Teacher">
    select t_id id,t_name tName from teacher where  t_id=#{teacher_id}
</select>
<!--写法二:属性字段不匹配,resultMap-->
<select id="selectTeacher" resultMap="TeacherMap">
    select * from teacher where t_id=#{teacher_id}
</select>
<resultMap type="com.zhiyou100.yj.bean.Teacher" id="TeacherMap" >
    <id column="t_id" property="id"/>
    <result column="t_name" property="tName"/>
</resultMap>

<!-- 一对多联表 -->
<select id="selectClazzById3" resultMap="ClazzMap3">
    select * from class c,student s,teacher t where  c.c_id=s.class_id  and  t.t_id=c.teacher_id  and  c.c_id=#{id};
</select>
      
<resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap3">
    <id column="c_id" property="cId"/>
    <result column="c_name" property="cName"/>
    <result column="teacher_id" property="teacheId"/>
    <association property="teacher"  javaType="com.zhiyou100.yj.bean.Teacher">    <!--对象类型:teacher-->
        <id column="t_id" property="id"/>
        <result column="t_name" property="tName"/>
    </association>
    <collection property="students"  ofType="com.zhiyou100.yj.bean.Student">   <!-- 集合类型:students;ofType:集合泛型的数据类型-->
        <id column="s_id" property="sId"/>
        <result column="s_name" property="sName"/>
        <result column="class_id" property="classId"/>
    </collection>
</resultMap>

<!-- 一对多嵌套 -->
<select id="selectClazz4" resultMap="ClazzMap4">
    select * from class where c_id=#{cid}
</select>

<resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap4">
    <id column="c_id" property="cId" />
    <result column="c_name" property="cName" />
    <result column="teacher_id" property="teacheId" />
    <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher" select="selectTeacher" column="teacher_id">
    </association>
    <collection property="students" ofType="com.zhiyou100.yj.bean.Student" select="selectStudent" column="c_id">
    </collection>
</resultMap>

<select id="selectTeacher" resultType="com.zhiyou100.yj.bean.Teacher">
    select t_id id,t_name tName from teacher where  t_id=#{teacher_id}
</select>

<select id="selectStudent" resultType="com.zhiyou100.yj.bean.Student">
    select s_id sId,s_name sName,class_id classId from student  where class_id=#{c_id}
</select>

$:解析时不会为内容添加"",是sql语句的拼接,存在sql注入危害
  传入的是列名或表名时,可以使用$
  eg:如果value的值是anything‘ OR ‘x‘=‘x,那么sql语句就会是select * from user where name=‘anything‘ or ‘x‘=‘x‘
#:解析时会为内容添加"",采用占位符,传递过来的#{xxx}的内容会被转义,然后替换掉"?"的内容
  eg:select * from student where name=‘anything\‘ or \‘x\‘=\‘x‘

<insert id="addRole" useGeneratedKeys="true" keyProperty="id">
    insert into role(uname,description) values(#{uname},#{description})
</insert>