首先数据库表展示
User表
Role角色表
关联User表与Role表关系的user_role表
因为是要串联关系所以需要设置外键,以下是外键展示
工程目录展示
pom.xml依赖配置
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
顺便附上Maven阿里云仓库
这样比较快 我之前创建一个项目都要10来分钟,现在只需要几秒
<mirrors>
<mirror>
<id>alimaven</id>
<name>aliyun maven</name>
<url>http://maven.aliyun.com/nexus/content/groups/public/</url>
<mirrorOf>central</mirrorOf>
</mirror>
</mirrors>
SqlMapConfig.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>
<!--typeAliases 得放在properties下面 不然会报错-->
<typeAliases>
<package name="com.domain" />
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.DAO"/>
</mappers>
</configuration>
实体类
user
package com.domain;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
/*多对多关系映射*/
private List<Role> roles;
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
public int getId() {
return id;
}
public void setId(int 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;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
Role
package com.domain;
import com.sun.xml.internal.bind.v2.model.core.ID;
import java.io.Serializable;
import java.util.List;
public class Role implements Serializable {
private int ID;
private String ROLE_NAME;
private String ROLE_DESC;
/*多对多关系映射*/
private List<User> users;
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public int getID() {
return ID;
}
public void setID(int ID) {
this.ID = ID;
}
public String getROLE_NAME() {
return ROLE_NAME;
}
public void setROLE_NAME(String ROLE_NAME) {
this.ROLE_NAME = ROLE_NAME;
}
public String getROLE_DESC() {
return ROLE_DESC;
}
public void setROLE_DESC(String ROLE_DESC) {
this.ROLE_DESC = ROLE_DESC;
}
@Override
public String toString() {
return "Role{" +
"ID=" + ID +
", ROLE_NAME='" + ROLE_NAME + '\'' +
", ROLE_DESC='" + ROLE_DESC + '\'' +
'}';
}
}
dao
package com.DAO;
import com.domain.User;
import java.util.List;
public interface IUser {
List<User> findAll();
}
package com.DAO;
import com.domain.Role;
import java.util.List;
public interface IRole {
List<Role> findAll();
}
对应的Mapper映射配置文件及测试
IUser.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.DAO.IUser">
<resultMap id="UserRole" type="User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<collection property="roles" ofType="Role">
<result property="ID" column="ID"></result>
<result property="ROLE_NAME" column="ROLE_NAME"></result>
<result property="ROLE_DESC" column="ROLE_DESC"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="UserRole" >
select u.*,r.* from user u
left outer join user_role ur on u.id=ur.uid
left outer join role r on r.id=ur.rid
</select>
</mapper>
附上测试类代码
package com.test;
import com.DAO.IUser;
import com.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class UserTest {
private InputStream in=null;
private SqlSessionFactory factory=null;
private SqlSession sqlSession=null;
private IUser iUser=null;
@Before
public void Init() throws IOException {
in= Resources.getResourceAsStream("SqlMapConfig.xml");
factory=new SqlSessionFactoryBuilder().build(in);
sqlSession=factory.openSession();
iUser=sqlSession.getMapper(IUser.class);
}
@After
public void Close() throws IOException {
sqlSession.commit();//提交事务
if(sqlSession!=null){
sqlSession.close();
}
if(in!=null){
in.close();
}
}
@Test
public void TestfindAll(){
List<User> user=iUser.findAll();
for(User user1:user){
System.out.println(user1);
System.out.println( user1.getRoles());
}
}
}
查询所有用户的信息及其身份的结果集展示
IRole.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.DAO.IRole">
<resultMap id="RoleUser" type="Role">
<id property="ID" column="ID"></id>
<result property="ROLE_NAME" column="ROLE_NAME"></result>
<result property="ROLE_DESC" column="ROLE_DESC"></result>
<collection property="users" ofType="User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="RoleUser">
select r.*,u.* from Role r
left outer join user_role ur on r.id=ur.rid
left outer join User u on u.id=ur.uid
</select>
</mapper>
附上测试类代码
package com.test;
import com.DAO.IRole;
import com.DAO.IUser;
import com.domain.Role;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class RoleTest {
private InputStream in=null;
private SqlSessionFactory factory=null;
private SqlSession sqlSession=null;
private IRole iRole=null;
@Before
public void Init() throws IOException {
in= Resources.getResourceAsStream("SqlMapConfig.xml");
factory=new SqlSessionFactoryBuilder().build(in);
sqlSession=factory.openSession();
iRole=sqlSession.getMapper(IRole.class);
}
@After
public void Close() throws IOException {
sqlSession.commit();//提交事务
if(sqlSession!=null){
sqlSession.close();
}
if(in!=null){
in.close();
}
}
@Test
public void TestfindAll(){
List<Role> roles=iRole.findAll();
for(Role role:roles){
System.out.println(role);
System.out.println(role.getUsers());
}
}
}
查询所有身份信息及对应的用户信息的结果集
其实在这多对多中也映射了一对多的关系
查询这边运用了多表查询中的左外连接
具体可以参考这篇大佬的博客 mysql多表查询