高端网站建设郑州,橄榄树网站建设,前端网站开发总结,吐槽做网站这里先总结一下 association 存在的问题。
一、内嵌查询时存在报错Id找不到及内存溢出隐患 二、一对多关系数据重复问题 三、多层嵌套内层 association 查询结果为null 或 非预期的值
一、内嵌查询时存在报错Id找不到及内存溢出隐患
参考#xff1a; https://www.lmlphp.co…这里先总结一下 association 存在的问题。
一、内嵌查询时存在报错Id找不到及内存溢出隐患 二、一对多关系数据重复问题 三、多层嵌套内层 association 查询结果为null 或 非预期的值
一、内嵌查询时存在报错Id找不到及内存溢出隐患
参考 https://www.lmlphp.com/user/57996/article/item/1356741/ 错误示例
?xml version1.0 encodingUTF-8 ?!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.lizhou.dao.StudentDaoresultMap typeStudent idstudentResultid propertyid columnid/association propertygrade columngradeId selectcom.lizhou.dao.GradeDao.getGrade/association/resultMapselect idgetStudent resultMapstudentResultSELECT * FROM studentwhereif testid ! nullAND id#{id}/if/where/select/mapper 报错Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named ‘id’ in ‘class java.lang.Integer’ 正确示例
mapper namespacecom.lizhou.dao.GradeDaoresultMap typeGrade idgradeResultid propertyid columnid//resultMapselect idgetGrade parameterTypeGrade resultMapgradeResultSELECT * FROM grade WHERE id#{id}/select/mapper 内存溢出隐患示例
错误示例 StudentMapper.xml
mapper namespacecom.lizhou.dao.StudentDaoresultMap typeStudent idstudentResultid propertyid columnid/association propertygrade columngradeId selectcom.lizhou.dao.GradeDao.getGrade/association/resultMapselect idgetStudent resultMapstudentResultSELECT * FROM student WHERE gradeId#{gradeId}/select/mapper GradeMapper.xml
mapper namespacecom.lizhou.dao.GradeDaoresultMap typeGrade idgradeResultid propertyid columnid/collection propertystudentList columnid ofTypeStudent selectcom.lizhou.dao.StudentDao.getStudent/collection/resultMapselect idgetGrade parameterTypeGrade resultMapgradeResultSELECT * FROM grade WHERE id#{id}/select/mapper 解决方式 分离写分段查询
二、一对多关系数据重复问题
参考https://blog.csdn.net/wuyezhiyu/article/details/81364974 resultMap idcommissionRec typeCommissionRec result propertysourceName columncmr_sourceName/result propertytotalMoney columncmr_totalMoney/result propertytotalCard columncmr_totalCard/result propertytotalCommission columncmr_totalCommission/result propertycommissionTimes columncmr_commissionTimes/result propertyisProvided columncmr_isProvided/result propertyuserInfo.userId columnus_userId/result propertyuserInfo.userName columnus_userName/association propertymemberCard resultMapmemberCardTemplate / !-- association propertyuserInfo resultMapuserInfo / --/resultMapresultMap iduserInfo typecn.sstech.member.marketing.model.base.BaseUserInfo id propertyuserId columnus_userId/result propertyuserName columnus_userName//resultMap 如上图所示查询结果中第一条和第二条只有us_userId是不重复的而剩下的字段都被注入到CommissionRec对象中。这时
1.用association注入userInfo mybatis会因为数据一样而将前两条数据合为一个CommissionRec对象us_userId则随便调一个注入到userInfo对象中
2.用 result property“userInfo.userId” column“us_userId”/ 这种制定具体字段名的方式注入mybatis会因为记录数据有所不同而将前两条数据设为2个CommissionRec对象.
三、多层嵌套内层 association 查询结果为null 或 非预期
非预期
本人遇到的就是这个问题 以组织机构树的单张表为例一般存的有ID及pre_id父级ID当内嵌查询时由于父级ID存在多条记录导致查询结果并非预期
多层嵌套查询结果为null
参考https://www.zhuxianfei.com/java/57394.html 错误示例
resultMap idBaseResultMap typea.b.c.d.eid columnid propertyid /result propertyworkTime columnwork_time /result propertymodel columnmodel /result propertystatus columnstatus /association propertyinterfaceUpstream javaTypeinterfaceUpstream columnPrefixui_id columnid propertyid /result propertyinterfaceName columninterface_name /result propertyinterfaceType columninterface_type /result propertyfrequency columnfrequency /result propertyaddress columnaddress /result propertytemplateOrSql columntemplate_or_sql /result propertystatus columnstatus /association propertysystemInfo javaTypeSystemInfo columnPrefixsys_id columnid propertyid/result propertysystemName columnsystem_name/result propertysystemNameEN columnsystem_name_en/result propertybelong columnbelong/result propertystatus columnstatus//associationassociation propertyserverInfo javaTypeServerInfo columnPrefixser_id columnid propertyid/result propertyftpIp columnftp_ip/result propertyftpPort columnftp_port/result propertyftpAccount columnftp_account/result propertyftpPassword columnftp_password//association/association/resultMapsql idbase_selectSELECTii.Id,ii.model,ii.status,ii.work_time,ui.id AS ui_id,ui.interface_name AS ui_interface_name,ui.interface_type AS ui_interface_type,ui.frequency AS ui_frequency,ui.address AS ui_address,ui.template_or_sql AS ui_template_or_sql,ui.status AS ui_status,sys.id AS sys_id,sys.system_name AS sys_system_name,sys.system_name_en AS sys_system_name_en,sys.belong AS sys_belong,sys.status AS sys_status,ser.id AS ser_id,ser.ftp_ip AS ser_ftp_ip,ser.ftp_port AS ser_ftp_port,ser.ftp_account AS ser_ftp_account,ser.ftp_password AS ser_ftp_password/sql结果 内层的association查询的结果一直为null
原因是association在进行多层嵌套时mybatis会将外层association的columnPrefix值与内层的进行并合
如外层columnPrefix值位ui_, 内层为sys_ 那么在SQL中就不能这样 sys.id AS sys_id 了需要将ui_前缀加上变成 sys.id AS ui_sys_id ,这样mybatis在匹配的时候才会将数据映射到对应association上
正确示例
SELECTii.Id,ii.model,ii.status,ii.work_time,ui.id AS ui_id,ui.interface_name AS ui_interface_name,ui.interface_type AS ui_interface_type,ui.frequency AS ui_frequency,ui.address AS ui_address,ui.template_or_sql AS ui_template_or_sql,ui.status AS ui_status,sys.id AS ui_sys_id,sys.system_name AS ui_sys_system_name,sys.system_name_en AS ui_sys_system_name_en,sys.belong AS ui_sys_belong,sys.status AS ui_sys_status,ser.id AS ui_ser_id,ser.ftp_ip AS ui_ser_ftp_ip,ser.ftp_port AS ui_ser_ftp_port,ser.ftp_account AS ui_ser_ftp_account,ser.ftp_password AS ui_ser_ftp_password总结
1、能不用就别用 2、如果用请务必指定对应的字段或id 3、多层嵌套时请注意前缀的使用 4、内嵌sql避免递归
