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

• 实体类

//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 getStudent();
}

• StudentMapper.xml

    
    
        
        

          //此处的javatype对应的就是																				Teacher类型
            
            
        

    

    
          SELECT s.id sid,s.name sname,t.id tid,t.name tname from
          student s,teacher t WHERe s.tid =t.id
    
    

• 测试

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

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

        List 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 students;  //此处是一个student list集合
}

• 接口TeacherMapper.interface

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

• TeacherMapper.xml

    
      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}
    
    
    
        
        

          // 此处用的是ofType
            
            
            
        
    

 
测试
 
@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 
 
搭建环境
 
  
使用UId工具类
 
public class UidUtils {
    public static String getUid() {
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}
 
 
IF where 
接口BlogMapper
 
public interface BlogMapper {

    List getBlogs(Map map);
}
 
Blogmapper.xml配置文件
 

        SELECT * from mybatis.blog  //注意此处的where标签是为了不用where 1=1
        
            
                and title=#{title}
            
            
                and author=#{author}
            
        
    

• 测试

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

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

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

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

        sqlSession.close();
    }