前提条件: 已知两个实体类user和role, user实体类的属性中声明了role对象,并且user表字段中含有 roleId字段,role表的id字段与之对应。
如何采用mybatis的相关知识进行两表间多对一的查询?
思路: 两个查询语句,查询user表的所有信息,查询role表的信息,并将role的id映射到user表的roleId属性字段上,最后通过ResultMap中的关联标签association将两者绑定,最后通过ResultMap将两表的数据绑定到user和对应的role实体类中。
本文采用两种查询方式: 查询嵌套方式和结果嵌套方式。
目录
实体类:
Role.java
User.java
接口:
RoleMapper.java
UserMapper.java
数据库表字段:
接口对应的xml文件:
RoleMapper.xml
UserMapper.xml
测试类:
MyTest.java
测试结果:
实体类
Role.java
package com.kuang.pojo;
import lombok.Data;
@Data
public class Role {
private int id;
private String roleName;
}
User.java
package com.kuang.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
public class User {
private int id;
private String userName;
//多个user对象关联一个role对象
private Role role;
}
接口
RoleMapper.java
package com.kuang.dao;
import com.kuang.pojo.Role;
import java.util.List;
public interface RoleMapper {
//查询所有role对象的信息
List getRoleList();
}
UserMapper.java
package com.kuang.dao;
import com.kuang.pojo.User;
import java.util.List;
public interface UserMapper {
//查询嵌套
public List getUserList();
//结果嵌套
public List getUserList2();
}
数据库表字段
package com.kuang.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
@Data
public class User {
private int id;
private String userName;
//多个user对象关联一个role对象
private Role role;
}
接口
RoleMapper.java
package com.kuang.dao;
import com.kuang.pojo.Role;
import java.util.List;
public interface RoleMapper {
//查询所有role对象的信息
List getRoleList();
}
UserMapper.java
package com.kuang.dao;
import com.kuang.pojo.User;
import java.util.List;
public interface UserMapper {
//查询嵌套
public List getUserList();
//结果嵌套
public List getUserList2();
}
数据库表字段
package com.kuang.dao;
import com.kuang.pojo.Role;
import java.util.List;
public interface RoleMapper {
//查询所有role对象的信息
List getRoleList();
}
UserMapper.java
package com.kuang.dao;
import com.kuang.pojo.User;
import java.util.List;
public interface UserMapper {
//查询嵌套
public List getUserList();
//结果嵌套
public List getUserList2();
}
数据库表字段
user(id ,userName,urid) , role(id,roleName)
接口对应的xml文件
RoleMapper.xml
select *
from smbms.`test-role`
where id = #{id}
UserMapper.xml
测试类
MyTest.java
import com.kuang.dao.RoleMapper;
import com.kuang.dao.UserMapper;
import com.kuang.pojo.Role;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MyTest {
@Test
//查看所有role列表信息
public void getRoleList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
List roleList = mapper.getRoleList();
roleList.forEach(role -> System.out.println(role));
sqlSession.close();
}
@Test
//按照查询嵌套方式
public void getUserList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
@Test
//按照结果嵌套方式
public void getUserList2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList2();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
}
测试结果
select * from smbms.`test-role` where id = #{id}
UserMapper.xml
测试类
MyTest.java
import com.kuang.dao.RoleMapper;
import com.kuang.dao.UserMapper;
import com.kuang.pojo.Role;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MyTest {
@Test
//查看所有role列表信息
public void getRoleList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
List roleList = mapper.getRoleList();
roleList.forEach(role -> System.out.println(role));
sqlSession.close();
}
@Test
//按照查询嵌套方式
public void getUserList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
@Test
//按照结果嵌套方式
public void getUserList2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList2();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
}
测试结果
MyTest.java
import com.kuang.dao.RoleMapper;
import com.kuang.dao.UserMapper;
import com.kuang.pojo.Role;
import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MyTest {
@Test
//查看所有role列表信息
public void getRoleList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
RoleMapper mapper = sqlSession.getMapper(RoleMapper.class);
List roleList = mapper.getRoleList();
roleList.forEach(role -> System.out.println(role));
sqlSession.close();
}
@Test
//按照查询嵌套方式
public void getUserList() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
@Test
//按照结果嵌套方式
public void getUserList2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List users = mapper.getUserList2();
users.forEach(user -> System.out.println(user));
sqlSession.close();
}
}
测试结果



