Mybatis培训第三课

Mybatis培训第三课一对一映射如下两个数据库表：Students表：Addresses表：每一个学生都有一个住址，即每一条student记录关联一条address记录，students表中的ADDR_ID为外键。如何让获取student的信息中包含address信息呢？1、创建Addressjavabean；2、在Studentjavabean中增加属性privateAddressaddress;及getter、setter方法；3、在studentMapper.xml文件中增加resultMap：resultMaptype=Studentid=StudentWithAddressResultidproperty=idcolumn=id/resultproperty=namecolumn=name/resultproperty=emailcolumn=email/resultproperty=address.addrIdcolumn=addr_id/resultproperty=address.streetcolumn=street/resultproperty=address.citycolumn=city/resultproperty=address.countrycolumn=country//resultMap4、增加select语句：selectid=selectStudentWithAddressparameterType=intresultMap=StudentWithAddressResultSELECTID,NAME,EMAIL,A.ADDR_ID,STREET,CITY,COUNTRYFROMSTUDENTSSLEFTOUTERJOINADDRESSESAONS.ADDR_ID=A.ADDR_IDWHEREID=#{studId}/select5、在Mapper接口StudentMapper类中增加方法：StudentselectStudentWithAddress(intid);6、在StudentService类中完善新增方法，然后测试。通过上述步骤，我们演示了一种关联查询的方法。但是这种方法有缺点：如果有一个select语句的结果集是address，那么我们需要重复映射一次address对应的resultMap。Mybatis提供了嵌套ResultMap和嵌套Select，现在利用这种方式来改善上述功能的缺点:嵌套ResultMap：resultMaptype=Addressid=AddressResultidproperty=addrIdcolumn=addr_id/resultproperty=streetcolumn=street/resultproperty=citycolumn=city/resultproperty=countrycolumn=country//resultMapresultMaptype=Studentid=StudentWithAddressResultidproperty=idcolumn=id/resultproperty=namecolumn=name/resultproperty=emailcolumn=email/associationproperty=addressresultMap=AddressResult//resultMap对应的select语句不变，测试之。association元素用于处理有一个的关联。内联resultMap也可以使用association元素和内联的resultMap进行resultMap映射：resultMaptype=Studentid=StudentWithAddressResultidproperty=idcolumn=id/resultproperty=namecolumn=name/resultproperty=emailcolumn=email/associationproperty=addressjavaType=Addressidproperty=addrIdcolumn=addr_id/resultproperty=streetcolumn=street/resultproperty=citycolumn=city/resultproperty=countrycolumn=country//association/resultMap对应的select语句不变，测试之。嵌套Select:addressresultMap映射：resultMaptype=Addressid=AddressResultidproperty=addrIdcolumn=addr_id/resultproperty=streetcolumn=street/resultproperty=citycolumn=city/resultproperty=countrycolumn=country//resultMap使用嵌套select的StudentresultMap映射：resultMaptype=Studentid=StudentWithAddressResultidproperty=idcolumn=id/resultproperty=namecolumn=name/resultproperty=emailcolumn=email/associationproperty=addresscolumn=addr_idselect=findAddressById//resultMap嵌套select对应的select语句：selectid=findAddressByIdparameterType=intresultMap=AddressResultSELECT*FROMADDRESSESWHEREADDR_ID=#{id}/select获取包含地址信息的学生信息的select语句改为：selectid=findStudentWithAddressparameterType=intresultMap=StudentWithAddressResultSELECT*FROMSTUDENTSWHEREID=#{Id}/select这种情况下将会执行两条Sql语句，findStudentWithAddress语句首先执行，然后findAddressById语句执行;column=addr_id的值将作为输入参数传入findAddressById语句。可通过下面的代码调用findStudentWithAddress语句：StudentMappermapper=sqlSession.getMapper(StudentMapper.class);Studentstudent=mapper.findStudentWithAddress(studId);System.out.println(student);System.out.println(student.getAddress());一对多映射如下两个数据库表：TUTORS表：COURSES表：老师John教一个课程，老师Ying教了两个课程。老师和课程之间为一对多的关系。如何获取一个老师的信息和其教的课程的信息？1、创建Course、Tutor两个javabean，注意Tutor类中的privateListCoursecourses;属性；2、使用嵌套ResultMap方式映射resultMap：resultMaptype=Courseid=CourseResultidcolumn=course_idproperty=courseId/resultcolumn=nameproperty=name/resultcolumn=descriptionproperty=description/resultcolumn=start_dateproperty=startDate/resultcolumn=end_dateproperty=endDate//resultMapresultMaptype=Tutorid=TutorResultidcolumn=tutor_idproperty=tutorId/resultcolumn=tutor_nameproperty=name/resultcolumn=emailproperty=email/collectionproperty=coursesresultMap=CourseResult//resultMapcollection元素用来映射有多条记录返回的情况。3、select语句：selectid=findTutorByIdparameterType=intresultMap=TutorResultSELECTT.TUTOR_ID,T.NAMEASTUTOR_NAME,EMAIL,C.COURSE_ID,C.NAME,DESCRIPTION,START_DATE,END_DATEFROMTUTORSTLEFTOUTERJOINADDRESSESAONT.ADDR_ID=A.ADDR_IDLEFTOUTERJOINCOURSESCONT.TUTOR_ID=C.TUTOR_IDWHERET.TUTOR_ID=#{tutorId}/select4、测试；第二种方式：嵌套selectresultMaptype=Courseid=CourseResultidcolumn=course_idproperty=courseId/resultcolumn=nameproperty=name/resultcolumn=descriptionproperty=description/resultcolumn=start_dateproperty=startDate/resultcolumn=end_dateproperty=endDate//resultMapresultMaptype=Tutorid=TutorResultidcolumn=tutor_idproperty=tutorId/resultcolumn=tutor_nameproperty=name/resultcolumn=emailproperty=email/associationproperty=addressresultMap=AddressResult/collectionproperty=coursescolumn=tutor_idselect=findCoursesByTutor//resultMapselectid=findTutorByIdparameterType=intresultMap=TutorResultSELECTT.TUTOR_ID,T.NAMEASTUTOR_NAME,EMAILFROMTUTORSTWHERET.TUTOR_ID=#{tutorId}/selectselectid=findCoursesByTutorparameterType=intresultMap=CourseResultSELECT*FROMCOURSESWHERETUTOR_ID=#{tutorId}/select测试代码参考：publicinterfaceTutorMapper{TutorfindTutorById(inttutorId);}TutorMappermapper=sqlSession.getMapper(TutorMapper.class);Tutortutor=mapper.findTutorById(tutorId);System.