实验步骤
背景描述:一个用户可以有多个角色,一个角色也可以包含多个用户,两者形成多对多的联系。
项目结构:
1. 创建实体类
1.1 用户实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
private int userId;
private String username;
private String password;
private String email;
private Set<Role> roles;
}
1.2 角色实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Role {
private Integer rid;
private String rname;
private List<User> users;
}
2. 创建mapper接口
2.1 用户接口
public interface UserMapper {
//根据用户找角色,用户表做主表
@Select("select * from user where userId = #{userId}")
@Results({
@Result(id = true, column = "userId", property = "userId"),
@Result(column = "username", property = "username"),
@Result(column = "address", property = "address"),
@Result(column = "email", property = "email"),
@Result(column = "userId", property = "roles",
many = @Many(select = "com.rg2.mapper.RoleMapper.getRoleById", fetchType = FetchType.EAGER)),
})
User getUserById(Integer userId) throws Exception;
//根据角色找用户
@Select("select * from user where userId in\n" +
"(select user_id from role_user where role_id=#{role_id}")
List<User> getUserByRId(Integer role_id) throws Exception;
}
2.2 角色接口
public interface RoleMapper {
//根据用户查询角色,角色表做从表
@Select("SELECT * FROM role where rid in( SELECT role_id FROM role_user WHERE user_id=#{user_id})")
List<Role> getRoleById(Integer user_id) throws Exception;
//根据角色查询用户,角色做主表
@Results({
@Result(id = true, column = "rid", property = "rid"),
@Result(column = "rname", property = "rname"),
@Result(column = "rid", property = "users",
many = @Many(select = "com.rg2.mapper.UserMapper.getUserByRId",fetchType = FetchType.EAGER))
})
Role getRoleByRid(Integer rid) throws Exception;
}
3. config.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
<properties resource="jdbc.properties"></properties>
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.rg2.mapper.RoleMapper"></mapper>
<mapper class="com.rg2.mapper.UserMapper"></mapper>
</mappers>
</configuration>
4. 测试类
public class Test2 {
public static void main(String[] args) throws Exception {
InputStream inputstream = Resources.getResourceAsStream("config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputstream);
SqlSession session = sqlSessionFactory.openSession(true);
System.out.println(session);
//根据用户找角色
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.getUserById(1);
System.out.println(user.getUsername());
Set<Role> roles = user.getRoles();
for(Role r:roles){
System.out.println("---"+r.getRname());
}
//根据角色找用户
RoleMapper roleMapper = session.getMapper(RoleMapper.class);
Role role = roleMapper.getRoleByRId(2);
System.out.println(role.getRname());
List<User> users = role.getUsers();
for(User u:users){
System.out.println("==="+u.getUsername());
}
}
}
运行截图: