mybatis关联查询(一对多和多对一)
目录
mybatis关联查询
DBUtil mybatis工具类
public class DBUtil {
private static SqlSessionFactory factory = null;
static {
try {
String mybatis_config = "mybatis-config.xml";
InputStream in = Resources.getResourceAsStream(mybatis_config);
factory = new SqlSessionFactoryBuilder().build(in);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取SqlSession
public static SqlSession getSqlSession() {
return factory.openSession(true);
}
// 获取mapper
public static <T> T getMapper(Class<T> mapper) {
return getSqlSession().getMapper(mapper);
}
}
mybatis一对多查询
resultMap格式:
cloumn属性可以是表字段名或者别名
<resultMap id="唯一的标识" type="映射的pojo对象">
<id column="表的主键字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="映射到pojo对象的一个属性"/>
<!--多个标签<result>...-->
<collection property="pojo的集合属性名" ofType="集合中的pojo对象">
<id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" />
<!--多个标签<result>...-->
</collection>
</resultMap>
案例
一个老师对应多个学生, 查询老师的时候把老师
学生类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String name;
}
老师类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private Integer id;
private String name;
List<Student> students;
}
老师dao接口
public interface TeacherMapper {
List<Teacher> findAllTeacher();
}
方试一: 案结果嵌套处理（联表查询）
TeacherMapper.xml
<select id="findAllTeacher" resultMap="teacherStudent">
select
t.id as t_id,
t.name as t_name,
s.id as s_id,
s.name as s_name
from teacher t
left join student s on
s.t_id = t.id
</select>
<resultMap id="teacherStudent" type="teacher">
<id column="t_id" property="id"/>
<result column="t_name" property="name"/>
<collection property="students" ofType="student">
<id column="s_id" property="id"/>
<result column="s_name" property="name"/>
</collection>
</resultMap>
方式二: 按查询嵌套处理
TeacherMapper.xml
<select id="findAllTeacher" resultMap="teacherStudent">
select * from teacher;
</select>
<select id="findStudentById" resultType="student">
select * from student where t_id = #{id};
</select>
<resultMap id="teacherStudent" type="teacher">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="students" javaType="ArrayList" ofType="student" select="findStudentById" column="id"/>
</resultMap>
方式一方式二测试均如下:
@Test
public void test1() {
TeacherMapper mapper1 = DBUtil.getMapper(TeacherMapper.class);
List<Teacher> teachers = mapper1.findAllTeacher();
for (Teacher teacher : teachers) {
System.out.println(teacher.getName());
for (Student student : teacher.getStudents()) {
System.out.println("\t" + student);
}
}
}
结果:
张老师
Student(id=1, name=张三)
Student(id=2, name=李四)
Student(id=3, name=王五)
Student(id=4, name=赵六)
王老师
Student(id=5, name=李七)
Student(id=6, name=冯八)
mybatis多对一查询
resultMap格式:
<resultMap id="唯一的标识" type="映射的pojo对象">
<id column="表的主键字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="映射到pojo对象的一个属性"/>
<!--多个标签<result>...-->
<association property="pojo的集合属性名" javaType="饮用配型">
<id column="主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
<result column="表字段名或者别名" jdbcType="字段类型" property="集合中的pojo对象的属性" />
<!--多个标签<result>...-->
</association>
</resultMap>
案例
查询学生, 并查询学生的老师
学生类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String name;
private Teacher teacher;
}
老师类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private Integer id;
private String name;
}
学生dao层
public interface StudentMapper {
List<Student> findAllStudent();
}
方式一: 案结果嵌套处理（联表查询）
StudentMapper.xml
<select id="findAllStudent" resultMap="studentTeacher">
select
s.id as s_id,
s.name as s_name,
t.id as t_id,
t.name as t_name
from student s left join teacher t on
s.t_id = t.id;
</select>
<resultMap id="studentTeacher" type="student">
<id column="s_id" property="id"/>
<result column="s_name" property="name"/>
<association property="teacher" javaType="teacher">
<id column="t_id" property="id"/>
<result column="t_name" property="name"/>
</association>
</resultMap>
方式二: 按查询结果嵌套
StudentMapper.xml
<select id="findAllStudent" resultMap="studentTeacher">
select * from student;
</select>
<select id="findTeacherById" resultType="teacher">
select * from teacher where id = #{t_id};
</select>
<resultMap id="studentTeacher" type="student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<association property="teacher" column="t_id" javaType="teacher" select="findTeacherById"/>
</resultMap>
方式一方式二测试均如下:
@Test
public void test1() {
StudentMapper mapper = DBUtil.getMapper(StudentMapper.class);
List<Student> students = mapper.findAllStudent();
for (Student student : students) {
System.out.println(student);
}
}
结果:
Student(id=1, name=张三, teacher=Teacher(id=1, name=张老师))
Student(id=2, name=李四, teacher=Teacher(id=1, name=张老师))
Student(id=3, name=王五, teacher=Teacher(id=1, name=张老师))
Student(id=4, name=赵六, teacher=Teacher(id=1, name=张老师))
Student(id=5, name=李七, teacher=Teacher(id=2, name=王老师))
Student(id=6, name=冯八, teacher=Teacher(id=2, name=王老师))
