表之间的关系分析
- 一对一( 多对一)
- 一对多
- 多对多
一对一示例:
用户和账户
- 一个账户只能属于一个用户
实现步骤:
- 建立两张表,用户表和账户表
- 建立两个实体类,用户实体类和账户实体类
- 建立两个配置文件
- 用户的配置文件
- 账户的配置文件
- 实现配置
- 当我们查询用户时,可以同时得到用户下包含的账户信息
- 当我们查询账户时,可以同时得到账户所属用户信息
建表语句:
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime default NULL COMMENT '生日',
`sex` char(1) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`ID` int(11) NOT NULL COMMENT '编号',
`UID` int(11) default NULL COMMENT '用户编号',
`MONEY` double default NULL COMMENT '金额',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `account`(`ID`,`UID`,`MONEY`) values (1,41,1000),(2,45,1000),(3,41,2000);
实体类:
User:
@Data
public class User {
private Integer id;
private String userName;
private Date userBirthday;
private char userSex;
private String userAddress;
}
Account实体类:
@Data
public class Account implements Serializable {
private int id;
private int uid;
private double money;
//一对一关系映射
private User user;
}
映射文件AccountDao.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">
<!--对应的dao接口-->
<mapper namespace="com.wjh.dao.AccountDao">
<!--定义封装account和user的resultMap-->
<resultMap id="accountuserMap" type="AccountUser">
<!--主键字段的对应-->
<id column="id" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
<!--一对一的关系映射:配置封装user的内容-->
<association property="user" column="uid">
<id column="id" property="id"></id>
<result column="username" property="userName"></result>
<result column="birthday" property="userBirthday"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
</association>
</resultMap>
<!--id的值必须是对应的方法名-->
<select id="findAll" resultMap="accountuserMap">
select * from account a,user u where a.UID = u.id
</select>
<!--查询所有账户信息,并且带有用户姓名和地址-->
<select id="findAllAccount" resultType="com.wjh.domain.AccountUser">
select a.*,u.username userName,u.address userAddress from account a,user u where a.UID = u.id
</select>
</mapper>
测试:
//查询所有账户信息,并且带有用户姓名和地址
@Test
public void testSelect(){
List<AccountUser> accounts = accountDao.findAllAccount();
for (AccountUser accountUser : accounts){
System.out.println(accountUser);
}
}
一对多示例:
- 一个用户有多个账户
user实体类:
@Data
public class User {
private Integer id;
private String userName;
private Date userBirthday;
private char userSex;
private String userAddress;
private List<Account> accounts;
}
映射文件userDao.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">
<!--对应的dao接口-->
<mapper namespace="com.wjh.dao.UserDao">
<!--定义resultMap,配置查询结果列名和实体类属性名的对应关系-->
<resultMap id="userResultMap" type="User">
<!--主键字段的对应-->
<id column="id" property="id"></id>
<result column="username" property="userName"></result>
<result column="birthday" property="userBirthday"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
<!--配置一对多关系映射,user对象中accounts集合-->
<collection property="accounts" ofType="Account">
<id column="id" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<!--id的值必须是对应的方法名-->
<select id="getAllUser" resultMap="userResultMap">
<!--select id ,username,birthday as userBirthday,sex as userSex,address as userAddress from user-->
select * from user u left outer join account a on u.id = a.uid
</select>
<!--根据用户id查询用户-->
<select id="getUserById" parameterType="int" resultMap="userResultMap">
select * from user where id = #{userId}
</select>
</mapper>
测试:
//查询用户
@Test
public void testselect(){
//5、使用代理对象执行方法
List<User> userList = userDao.getAllUser();
for (User user : userList){
System.out.println(user);
}
}
多对多示例:
用户和角色
- 一个用户可以有多个角色
- 一个角色可以有多个用户
实现步骤:
- 建立三张表,用户表和角色表,用户角色表(中间表)
- 建立两个实体类,用户实体类和角色实体类
- 建立两个配置文件
- 用户的配置文件
- 角色的配置文件
- 实现配置
- 当我们查询用户时,可以同时得到用户所属角色
- 当我们查询角色时,可以同时得到角色下的所有用户
说明:当表与表的关系是多对多是,需要建立中间表
建表语句:
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`ID` int(11) NOT NULL COMMENT '编号',
`ROLE_NAME` varchar(30) default NULL COMMENT '角色名称',
`ROLE_DESC` varchar(60) default NULL COMMENT '角色描述',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`UID` int(11) NOT NULL COMMENT '用户编号',
`RID` int(11) NOT NULL COMMENT '角色编号',
PRIMARY KEY (`UID`,`RID`),
KEY `FK_Reference_10` (`RID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);
user实体类:
@Data
public class User {
private Integer id;
private String userName;
private Date userBirthday;
private char userSex;
private String userAddress;
private List<Role> roles;
role实体类:
@Data
public class Role {
private int rId;
private String roleName;
private String roleDesc;
private List<User> users;
}
userDao接口:
//查询所有用户
List<User> getAllUser();
roleDao接口:
//查询所有角色
List<Role> findAll();
映射文件userDao.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">
<!--对应的dao接口-->
<mapper namespace="com.wjh.dao.UserDao">
<!--定义resultMap,配置查询结果列名和实体类属性名的对应关系-->
<resultMap id="userResultMap" type="User">
<!--主键字段的对应-->
<id column="id" property="id"></id>
<result column="username" property="userName"></result>
<result column="birthday" property="userBirthday"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
<!--配置一对多关系映射,user对象中accounts集合-->
<collection property="roles" ofType="Role">
<id column="rid" property="rId"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
</collection>
</resultMap>
<!--id的值必须是对应的方法名-->
<select id="getAllUser" resultMap="userResultMap">
SELECT u.*,r.id as rId ,r.ROLE_NAME,r.ROLE_DESC 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>
映射文件RoleDao.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">
<!--对应的dao接口-->
<mapper namespace="com.wjh.dao.RoleDao">
<!--定义resultMap,配置查询结果列名和实体类属性名的对应关系-->
<resultMap id="roleResultMap" type="Role">
<!--主键字段的对应-->
<id column="rid" property="rId"></id>
<result column="role_name" property="roleName"></result>
<result column="role_desc" property="roleDesc"></result>
<!--配置一对多关系映射,user对象中accounts集合-->
<collection property="users" ofType="User">
<id column="id" property="id"></id>
<result column="username" property="userName"></result>
<result column="birthday" property="userBirthday"></result>
<result column="sex" property="userSex"></result>
<result column="address" property="userAddress"></result>
</collection>
</resultMap>
<!--id的值必须是对应的方法名-->
<select id="findAll" resultMap="roleResultMap">
SELECT r.id as rId ,r.ROLE_NAME,r.ROLE_DESC,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.wjh.test;
import com.wjh.dao.UserDao;
import com.wjh.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 is = null;
private SqlSession session = null;
private UserDao userDao = null;
@Before
public void before(){
try {
//1、读取配置文件
is = Resources.getResourceAsStream("SqlMapConfig.xml");
//2、创建SqlSessionFactory工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、使用工厂生成Sqlsession对象
session = factory.openSession(true);
//4、使用Sqlsession对象创建dao接口的代理对象
userDao = session.getMapper(UserDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void after(){
//6、释放资源
try {
session.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//查询用户
@Test
public void testselect(){
//5、使用代理对象执行方法
List<User> userList = userDao.getAllUser();
for (User user : userList){
System.out.println(user);
}
}
}
测试查询角色:
package com.wjh.test;
import com.wjh.dao.RoleDao;
import com.wjh.dao.UserDao;
import com.wjh.domain.Role;
import com.wjh.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 RoleTest {
private InputStream is = null;
private SqlSession session = null;
private RoleDao roleDao = null;
@Before
public void before(){
try {
//1、读取配置文件
is = Resources.getResourceAsStream("SqlMapConfig.xml");
//2、创建SqlSessionFactory工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
//3、使用工厂生成Sqlsession对象
session = factory.openSession(true);
//4、使用Sqlsession对象创建dao接口的代理对象
roleDao = session.getMapper(RoleDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void after(){
//6、释放资源
try {
session.close();
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//查询所有角色
@Test
public void testselect(){
//5、使用代理对象执行方法
List<Role> roles = roleDao.findAll();
for(Role role : roles){
System.out.println(role);
}
}
}