一对一
一对多
一对一映射是指一个对象的成员变量中含有另一个对象的引用。这两个对象为关联关系。
映射的方式:通过自定义resultMap来映射
数据库中的数据表:
student表
mysql> select * from student;
+--------+--------+----------------+-------------+--------+
| stu_id | name | email | phone | tea_id |
+--------+--------+----------------+-------------+--------+
| 1 | 张三 | asdu@163.com | 1354641 | 3 |
| 2 | 李四 | askdja@163.com | 15286344 | 1 |
| 4 | lisi | yz_ahha@qq.com | 15229219206 | 2 |
+--------+--------+----------------+-------------+--------+
3 rows in set (0.00 sec)
teacher表
mysql> select * from teacher;
+--------+-----------+-------------+
| tea_id | name | tel |
+--------+-----------+-------------+
| 1 | 杨老师 | 15229219206 |
| 2 | 宋老师 | 13201656250 |
| 3 | 李老师 | 13468667153 |
+--------+-----------+-------------+
3 rows in set (0.00 sec)
自定义的实体类中的属性:
Student的属性
private int stuId;
private String name;
private String email;
private String phone;
private Teacher tea;//Teacher类的引用
Teacher类的属性
private int teaId;
private String name;
private String tel;
1.通过引用.属性名
的方式来映射关联对象的属性:
<resultMap id="studentWithTeacher" type="Student">
<id property="stuId" column="stu_id"/>
<result property="name" column="stu_name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="tea.teaId" column="tea_id"/>
<result property="tea.name" column="tea_name"/>
<result property="tea.tel" column="tel"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentWithTeacher">
select stu_id,student.name as stu_name,email,phone,teacher.tea_id,teacher.name as tea_name,tel
from student
left join teacher
on student.tea_id=teacher.tea_id
where stu_id=#{id}
</select>
在接口中定义方法后,在main中进行调用:
public class Main {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
System.out.println(studentMapper.selectStudentById(1));
}
}
运行结果:
Student{stuId=1, name='张三', email='asdu@163.com', phone='1354641', tea=Teacher{tea_id=3, name='李老师', tel='13468667153'}}
2.定义两个resultMap,通过associate来关联:
<resultMap id="studentinfo" type="Student">
<id property="stuId" column="stu_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<association property="tea" resultMap="teacherinfo"/><!--在此进行关联-->
</resultMap>
<resultMap id="teacherinfo" type="teacher">
<id property="teaId" column="tea_id"/>
<result property="name" column="name"/>
<result property="tel" column="tel"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentinfo">
select stu_id,student.name,email,phone,teacher.tea_id,teacher.name,tel
from student inner join teacher
on student.tea_id=teacher.tea_id
where stu_id=#{id}
</select>
同样,在接口中定义方法,在main中运行,运行结果同上。
3.第二种方式属于外联,在一个resultMap的外部定义另外一个resultMap。第三种则是属于内联,在一个resultMap的内部定义另一个resultMap:
<resultMap id="studentbyinner" type="Student">
<id property="stuId" column="stu_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<association property="tea" javaType="Teacher"> <!--定义关联的对象的映射-->
<id property="teaId" column="tea_id"/>
<result property="name" column="name"/>
<result property="tel" column="tel"/>
</association>
</resultMap>
<select id="selectStudentById" parameterType="int" resultMap="studentbyinner">
select stu_id,student.name,email,phone,teacher.tea_id,teacher.name,tel
from student,teacher
where student.tea_id=teacher.tea_id and stu_id=#{id}
</select>
<resultMap id="teacherinfo" type="teacher">
<id property="teaId" column="tea_id"/>
<result property="name" column="name"/>
<result property="tel" column="tel"/>
</resultMap>
<select id="selectTeaById" parameterType="int" resultMap="teacherinfo">
select * from teacher where tea_id=#{id}
</select>
<resultMap id="studentinfo" type="Student">
<id property="stuId" column="stu_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<association property="tea" column="tea_id" select="selectTeaById"/> <!--嵌套查询-->
</resultMap>
<select id="selectStudent" parameterType="int" resultMap="studentinfo">
select * from student where stu_id=#{id}
</select>
根据tea_id进行查询。嵌套查询会先执行内层查询,然后将查询到的结果放在临时表中,然后通过临时表在进行外层查询。查询结束后会销毁临时表。因此效率相对较低,可以用连接查询来代替嵌套查询(子查询),也就是第二种方法,第二种相对第一种和第三种来说较为灵活。
补充
在mybatis_config.xml配置文件中遇到的坑:配置settings和typeAliases放的位置不对时,会报错。如果出现错误,尝试调整其位置。
<!--开启驼峰命名的映射,即从tea_id到teaId的映射-->
<settings >
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--定义类型别名,用可用Student来代替com.pojo.Studnet,可用teacher代替com.pojo.Teacher-->
<typeAliases>
<typeAlias type="com.pojo.Student" alias="Student"/>
<package name="com.pojo"/>
</typeAliases>
以上是一对一映射,接下来是一对多映射:一对多的写法类似于一对一的 第二种和 第四种。 首先,在原有的数据表中添加数据。一个老师可以带领多名学生。实体类老师中含有一个集合,集合中是学生实体类对象。老师类和学生类的这种关系被称为聚合。 接下来修改实体类:
teacher:
private int teaId;
private String name;
private String tel;
private List<Student> list;
student:
private int stuId;
private String name;
private String email;
private String phone;
private int teaId;
映射文件第一种写法:
<sql id="teaAndStu">
teacher.tea_id,teacher.name as tname,tel,stu_id,student.name as sname,email,phone
</sql>
<resultMap id="teaWithStus" type="Teacher">
<id property="teaId" column="tea_id"/>
<result property="name" column="tname"/>
<result property="tel" column="tel"/>
<collection property="list" resultMap="stu"/>
</resultMap>
<resultMap id="stu" type="student">
<id property="stuId" column="stu_id"/>
<result property="name" column="sname"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="teaId" column="tea_id"/>
</resultMap>
<select id="selectTea" parameterType="int" resultMap="teaWithStus">
select <include refid="teaAndStu"/>
from teacher left join student
on student.tea_id=teacher.tea_id
where teacher.tea_id=#{id}
</select>
通过内嵌结果来映射。
在数据库中查询的结果:
mysql> select teacher.tea_id,teacher.name,tel,stu_id,student.name,email,phone
-> from student right join teacher
-> on teacher.tea_id=student.tea_id
-> where teacher.tea_id=2;
+--------+-----------+-------------+--------+------+----------------+-------------+
| tea_id | name | tel | stu_id | name | email | phone |
+--------+-----------+-------------+--------+------+----------------+-------------+
| 2 | 宋老师 | 13201656250 | 4 | lisi | yz_ahha@qq.com | 15229219206 |
| 2 | 宋老师 | 13201656250 | 5 | swj | swj@163.com | 132016562xx |
| 2 | 宋老师 | 13201656250 | 6 | mmq | 123@qq.com | 136987456 |
| 2 | 宋老师 | 13201656250 | 7 | lfl | 456@163.com | 178888880 |
+--------+-----------+-------------+--------+------+----------------+-------------+
4 rows in set (0.01 sec)
main方法:
TeacherMapper teacherMapper=sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=teacherMapper.selectTea(2);
List<Student> list=teacher.getList();
for(Student stu:list)
{
System.out.println(stu);
}
运行结果:
Student{stuId=4, name='lisi', email='yz_ahha@qq.com', phone='15229219206', teaId=2}
Student{stuId=5, name='swj', email='swj@163.com', phone='132016562xx', teaId=2}
Student{stuId=6, name='mmq', email='123@qq.com', phone='136987456', teaId=2}
Student{stuId=7, name='lfl', email='456@163.com', phone='178888880', teaId=2}
同理,也可以通过嵌套查询来过的:
映射文件:
<resultMap id="teaWithStus" type="Teacher">
<id property="teaId" column="tea_id"/>
<result property="name" column="tname"/>
<result property="tel" column="tel"/>
<collection property="list" column="tea_id" select="selectStu"/><!--进行内层查询,tea_id是参数-->
</resultMap>
<!--内层查询-->
<resultMap id="stu" type="student">
<id property="stuId" column="stu_id"/>
<result property="name" column="sname"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="teaId" column="tea_id"/>
</resultMap>
<select id="selectStu" parameterType="int" resultMap="stu">
select stu_id,name as sname,email,phone,tea_id from student where tea_id=#{id}
</select>
<!--外层查询-->
<select id="selectTea" parameterType="int" resultMap="teaWithStus">
select tea_id,name as tname,tel from teacher where tea_id=#{id}
</select>
总结:嵌套查询虽然效率低,但是其sql语句简单易懂;连接查询虽然效率较高,但是其sql语句较长。
上一篇 |
---The End---
| 下一篇 |