mybatis 显示自定义多个统计_myBatis查询结果显示多表内的字段——通过resultMap映射自定义结果...

问：查询完成后，结果列只能展示单表内的字段，比如用户表表中只记录角色编号，没有角色名称。结果列要如何展示在另一个表内的字段——角色名称呢？

答：通过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);

session.commit();

}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)若要使用自动映射，则实体对象的属性名要与数据库字段名一致，否则就自定义结果映射(如本例)