mybatis学习04-多对一和一对多查询-动态sql

mybatis 学习04

多对一 查询

• 实体类

//teacher
@Data
public class Teacher {
    private int id;
    private String name;
}
//student
@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;  //注意此处，需要查出一个对象
}

• StudentMapper.interface

public interface StudentMapper {
    //查询所有学生所关联的老师 association
    public List<Student> getStudent();
}

• StudentMapper.xml

<mapper namespace="com.xu.dao.StudentMapper">
    
    <resultMap id="StudentMap" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>

        <association property="teacher" javaType="teacher">  //此处的javatype对应的就是																				Teacher类型
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>

    </resultMap>

    <select id="getStudent" resultMap="StudentMap">
          SELECT s.id sid,s.name sname,t.id tid,t.name tname from
          student s,teacher t WHERE s.tid =t.id
    </select>
    
</mapper>

• 测试

public void getStudent() {
        SqlSession sqlSession= myBatisUtil.getSession();

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> list = mapper.getStudent();
        for (Student student : list) {
            System.out.println(student);
        }
        sqlSession.close();
}

• 结果

Student(id=1, name=小明, teacher=Teacher(id=1, name=xpc))
Student(id=2, name=小红, teacher=Teacher(id=1, name=xpc))
Student(id=3, name=小张, teacher=Teacher(id=1, name=xpc))
Student(id=4, name=小李, teacher=Teacher(id=1, name=xpc))
Student(id=5, name=小王, teacher=Teacher(id=1, name=xpc))

一对多查询

• 实体类

//student
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}
//teacher
@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;  //此处是一个student list集合
}

• 接口TeacherMapper.interface

public interface TeacherMapper {
    //查询一个指定的老师带的所有学生 collection
    public Teacher getTeacher(@Param("tid") int id);
}

• TeacherMapper.xml

<mapper namespace="com.xu.dao.TeacherMapper">

    <select id="getTeacher" parameterType="int" resultMap="TeacherMap">
      SELECT t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
      FROM teacher t,student s where s.tid=t.id and tid=#{tid}
    </select>
    
    <resultMap id="TeacherMap" type="Teacher">
        <result property="id" column="tid"></result>
        <result property="name" column="tname"/>

        <collection property="students" ofType="Student">  // 此处用的是ofType
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>
</mapper>

• 测试

@Test
public void getTeacher() {
        SqlSession sqlSession= myBatisUtil.getSession();

        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);

}

• 结果

Teacher(
id=1, 
name=xpc, 
students=[
Student(id=1, name=小明, tid=1), 
Student(id=2, name=小红, tid=1), 
Student(id=3, name=小张, tid=1), 
Student(id=4, name=小李, tid=1), 
Student(id=5, name=小王, tid=1)])

动态sql

1. 搭建环境

   • 使用UId工具类

   public class UidUtils {
       public static String getUid() {
           return UUID.randomUUID().toString().replaceAll("-","");
       }
   }
   

IF where

• 接口BlogMapper

public interface BlogMapper {

    List<Blog> getBlogs(Map<String,String> map);
}

• Blogmapper.xml配置文件

<select id="getBlogs" parameterType="map" resultType="Blog">
        SELECT * from mybatis.blog  //注意此处的where标签是为了不用where 1=1
        <where>
            <if test="title != null">
                and title=#{title}
            </if>
            <if test="author !=null">
                and author=#{author}
            </if>
        </where>
    </select>

• 测试

@org.junit.Test
    public void test() {
        SqlSession sqlSession=myBatisUtil.getSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap<String, String> map = new HashMap<String, String>();
        //map.put("title","Mybatis"); //
        map.put("author", "狂神说");

    	//情况1：当map为空的时候查询出全部博客
    	//情况2：当title不为空的时候，查询出对应title的博客
    	//情况3：当title为空author不为空的时候，查询出对应author的全部博客
        List<Blog> list=mapper.getBlogs(map);
        for (Blog blog : list) {
            System.out.println(blog);
        }

        sqlSession.close();
    }