现在最好的企业网站管理系统,铁威马怎样做网站服务器,情侣主题 wordpress,郑州东区网站建设8.MyBatis的关联查询
8.3.一对多查询
需求#xff1a;查询所有用户信息及用户关联的账户信息。
分析#xff1a;用户信息和他的账户信息为一对多关系#xff0c;并且查询过程中如果用户没有账户信息#xff0c;此时也要将用户信息查询出来#xff0c;此时左外连接查询比…8.MyBatis的关联查询
8.3.一对多查询
需求查询所有用户信息及用户关联的账户信息。
分析用户信息和他的账户信息为一对多关系并且查询过程中如果用户没有账户信息此时也要将用户信息查询出来此时左外连接查询比较合适。
8.3.1.pojo
package com.by.pojo;import java.io.Serializable;
import java.util.Date;
import java.util.List;public class User implements Serializable {private Integer id;private String username;private Date birthday;private String sex;private String address;private ListAccount account;Overridepublic String toString() {return User{ id id , username username \ , birthday birthday , sex sex \ , address address \ , account account };}public ListAccount getAccount() {return account;}public void setAccount(ListAccount account) {this.account account;}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getUsername() {return username;}public void setUsername(String username) {this.username username;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday birthday;}public String getSex() {return sex;}public void setSex(String sex) {this.sex sex;}public String getAddress() {return address;}public void setAddress(String address) {this.address address;}}
8.3.2.mapper
package com.by.dao;import com.by.pojo.User;import java.util.List;public interface UserDao {User getUserById(Integer id);
}?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
resultMap idgetUserByIdResultMap typeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnbirthday propertybirthday/resultresult columnsex propertysex/resultresult columnaddress propertyaddress/result!--一对多使用collection标签指定数据的封装规则--collection propertyaccount ofTypeaccountid columnaid propertyid/idresult columnuid propertyuid/resultresult columnmoney propertymoney/result/collection/resultMapselect idgetUserById parameterTypeint resultMapgetUserByIdResultMapSELECTu.*,a.id aid,a.money money,a.uid uidFROMuser uLEFT JOINaccount aONu.ida.uidWHEREu.id#{id}/select8.3.3.测试 /*** 一对多一个user 对 多个Account*/Testpublic void testGetUsertById() throws IOException {UserDao userDao sqlSession.getMapper(UserDao.class);User user userDao.getUserById(41);System.out.println(user);}
8.4.多对多查询
需求查询角色及角色赋予的用户信息。
分析一个用户可以拥有多个角色一个角色也可以赋予多个用户用户和角色为双向的一对多关系多对多关系其实我们看成是双向的一对多关系。 user(uid, username)王贺、万通
user_role(uid, rid)
role(rid, )校长、老师、学生
8.3.1.pojo
package com.by.pojo;import java.util.List;
// 一方
public class Role {private Integer id;private String roleName;private String roleDesc;// 多方private ListUser user;Overridepublic String toString() {return Role{ id id , roleName roleName \ , roleDesc roleDesc \ , user user };}public Integer getId() {return id;}public void setId(Integer id) {this.id id;}public String getRoleName() {return roleName;}public void setRoleName(String roleName) {this.roleName roleName;}public String getRoleDesc() {return roleDesc;}public void setRoleDesc(String roleDesc) {this.roleDesc roleDesc;}public ListUser getUser() {return user;}public void setUser(ListUser user) {this.user user;}
}
8.3.2.mapper
package com.by.dao;import com.by.pojo.Role;public interface RoleMapper {Role getRoleById(Integer id);
}
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.by.dao.RoleMapperresultMap idgetRoleByIdResultMap typeroleid columnrid propertyid/idresult columnrole_name propertyroleName/resultresult columnrole_desc propertyroleDesc/result!--一对多使用collection标签指定数据的封装规则propertyuserListpojo的属性ofTypecom.by.pojo.User集合的泛型等价于resultType--collection propertyuser ofTypeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnbirthday propertybirthday/resultresult columnsex propertysex/resultresult columnaddress propertyaddress/result/collection/resultMapselect idgetRoleById parameterTypeint resultMapgetRoleByIdResultMapSELECTr.id rid,role_name,role_desc,u.*FROMuser_role urLEFT JOINrole rONur.ridr.idLEFT JOINuser uONur.uidu.idWHEREr.id#{id}/select
/mapper8.3.3.测试 /*** 多对多一个user 对 多个role 一个role 对 多个user*/Testpublic void testGetRoletById() throws IOException {RoleMapper roleMapper sqlSession.getMapper(RoleMapper.class);Role role roleMapper.getRoleById(1);System.out.println(role);}9.MyBatis的延迟加载
创建工程
9.1.什么是延迟加载
开启延迟加载后在真正使用数据的时候才发起级联查询不用的时候不查询。
9.2.mapper
package com.by.dao;import com.by.pojo.User;import java.util.List;public interface UserDao {User getUserById2(Integer id);
}
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecom.by.dao.UserDao!--id:和接口方法名保持一致resultType:和接口返回类型保持一致--select idfindAll resultTypecom.by.pojo.Userselect * from user/selectselect idgetUserById parameterTypeint resultTypeuserselect * from user where id#{id}/selectresultMap idgetUserById2ResultMap typeuserid columnid propertyid/idresult columnusername propertyusername/resultresult columnbirthday propertybirthday/resultresult columnsex propertysex/resultresult columnaddress propertyaddress/result!--propertyaccountListpojo的属性ofTypeaccount集合的泛型selectcom.by.mapper.AccountMapper.selectAccountByUid要调用的select标签的idcolumnid传递的参数fetchTypelazy局部开启延迟加载--collection propertyaccountList ofTypeaccount selectcom.by.dao.AccountMapper.selectAccountById columnid fetchTypelazy/collection/resultMapselect idgetUserById2 parameterTypeint resultMapgetUserById2ResultMapSELECT * FROM user WHERE id#{id}/select
/mapper9.3.全局开启懒加载 !-- 全局配置延迟加载策略 --settings!-- 打开延迟加载的开关 --setting namelazyLoadingEnabled valuetrue//settings9.4.测试 Testpublic void testGetUserById2() throws IOException {UserDao userDao sqlSession.getMapper(UserDao.class);User user userDao.getUserById2(41);System.out.println(user.getUsername());ListAccount accountList user.getAccountList();for (Account account : accountList) {System.out.println(account);}}10.MyBatis的动态SQL
创建工程
10.1.什么是动态SQL?
MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作并动态拼接成完整的SQL之后再执行以达到SQL复用、简化编程的效果。
10.2.if标签
我们根据实体类的不同取值使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据 id查询如果username不同空时还要加入用户名作为条件。这种情况在我们的多条件组合查询中经常会碰到。 mapper接口
package com.by.dao;import com.by.pojo.User;import java.util.List;public interface UserDao {User findAll2(User user);
}
mapper映射文件
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdselect idfindAll2 parameterTypeuser resultTypeuserSELECT * FROM userwhereif testid ! nullAND id#{id}/ifif testsex ! null and sex ! AND sex#{sex}/if/where/select
测试 public void testFindAll2(){UserDao userDao sqlSession.getMapper(UserDao.class);User user new User();user.setId(41);user.setSex(男);System.out.println(userDao.findAll2(user));}10.3.where标签
为了简化上面where 11的条件拼装我们可以使用where标签将if标签代码块包起来将11条件去掉。
若查询条件的开头为 “AND” 或 “OR”where 标签会将它们去除。 mapper映射文件
?xml version1.0 encodingUTF-8?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
!--namespace隔离sql一般是接口名称的全类名--
mapper namespacecom.by.dao.UserDao
select idfindByUser resultTypeUserselect * from user!--where标签将if标签代码块包起来去掉开头 “AND” 或 “OR”--whereif testusername!null and username ! and username#{username}/ifif testbirthday!nulland birthday#{birthday}/ifif testsex!null and sex ! and sex#{sex}/ifif testaddress!null and address ! and address#{address}/if/where/select/mapper10.4.set标签
set标签用于动态包含需要更新的列并会删掉额外的逗号 mapper
package com.by.dao;import com.by.pojo.User;import java.util.List;public interface UserDao {void updateUserById(User user);
} update idupdateUserById parameterTypeuserUPDATE usersetif testusername ! null and username ! username#{username},/if/setWHERE id#{id}/update测试 Testpublic void testUpdateUserById(){UserDao userDao sqlSession.getMapper(UserDao.class);User user new User();user.setId(41);user.setUsername(杨过);userDao.updateUserById(user);}
