问:查询完成后,结果列只能展示单表内的字段,比如用户表表中只记录角色编号,没有角色名称。结果列要如何展示在另一个表内的字段——角色名称呢?
答:通过resultMap映射自定义结果显示该字段。
1.resultMap的节点与属性值
id属性:唯一标识,次id值用于select元素resultMap属性的引用
type属性:表示该resultMap的映射结果类型,一般来说resultMap的type与select节点中的parameterType类型一致
result子节点:用于表示一些简单的属性
(1)column属性:表示从数据库中查询的字段名
(2)property:表示查询出来的字段对应的值赋给实体对象的哪个属性
2.使用方法
(1)SQL映射文件
select u.*,r.roleName
from smbms_user u,smbms_role r
where u.userRole = r.id
and userName like concat('%',#{userName},'%')
and userRole = #{userRole}
(2)UserMapper接口
public List getUserListByUser(User user);
(3)User.java
public classUser {private Integer id; //用户编号
private String userCode; //用户编码
private String userName; //用户名
private String userPassword; //密码
private Integer gender; //性别
private Date birthday; //生日
private String phone; //电话
private String address; //地址
private String userRole; //角色id//配合resultMap显示另一个表内的字段:角色名称//添加getter与setter方法
private String userRoleName; //
private Integer createdBy; //创建人
private Date creationDate; //创建日期
private Integer modifyBy; //修改人
private Date modifyDate; //修改日期//用户对应角色,可以通过用户访问到角色的所有信息,复杂映射
privateRole role;publicInteger getId() {returnid;
}public voidsetId(Integer id) {this.id =id;
}publicString getUserCode() {returnuserCode;
}public voidsetUserCode(String userCode) {this.userCode =userCode;
}publicString getUserName() {returnuserName;
}public voidsetUserName(String userName) {this.userName =userName;
}publicString getUserPassword() {returnuserPassword;
}public voidsetUserPassword(String userPassword) {this.userPassword =userPassword;
}publicInteger getGender() {returngender;
}public voidsetGender(Integer gender) {this.gender =gender;
}publicDate getBirthday() {returnbirthday;
}public voidsetBirthday(Date birthday) {this.birthday =birthday;
}publicString getPhone() {returnphone;
}public voidsetPhone(String phone) {this.phone =phone;
}publicString getAddress() {returnaddress;
}public voidsetAddress(String address) {this.address =address;
}publicString getUserRole() {returnuserRole;
}public voidsetUserRole(String userRole) {this.userRole =userRole;
}publicInteger getCreatedBy() {returncreatedBy;
}public voidsetCreatedBy(Integer createdBy) {this.createdBy =createdBy;
}publicDate getCreationDate() {returncreationDate;
}public voidsetCreationDate(Date creationDate) {this.creationDate =creationDate;
}publicInteger getModifyBy() {returnmodifyBy;
}public voidsetModifyBy(Integer modifyBy) {this.modifyBy =modifyBy;
}publicDate getModifyDate() {returnmodifyDate;
}public voidsetModifyDate(Date modifyDate) {this.modifyDate =modifyDate;
}publicString getUserRoleName() {returnuserRoleName;
}public voidsetUserRoleName(String userRoleName) {this.userRoleName =userRoleName;
}publicRole getRole() {returnrole;
}public voidsetRole(Role role) {this.role =role;
}
}
(4)UserService.java
@Testpublic voidtestGetUserListByUser(){
SqlSession session= null;
List userList = null;try{
session=MybatisUtil.createSqlSession();
User user1= newUser();
user1.setUserName("用户");
user1.setUserRole("1");
userList=session.getMapper(UserMapper.class).getUserListByUser(user1);
mit();
}catch(Exception e) {
e.printStackTrace();if (session != null) {
session.rollback();
}
}finally{if (session != null) {
session.close();
}
}for(User user : userList){
log.debug("user =====> "+user.getUserName()+"\t"+user.getUserRoleName());
}
}
3.步骤总结
(1)首先在User类加入userRoleName属性,加入该属性的getter与setter方法
(2)修改UserMapper接口中的对应查询方法
(3)修改UserMapper.xml中中的resultType属性为resultMap,注意其值为resultMap节点的id属性值
(4)注意resultMap中的property属性值与column属性值
(5)若要使用自动映射,则实体对象的属性名要与数据库字段名一致,否则就自定义结果映射(如本例)
mybatis 显示自定义多个统计_myBatis查询结果显示多表内的字段——通过resultMap映射自定义结果...