目录
4.2)完成account的一对一操作,通过写account的子类方式查询(不常用的方式)
4.3)完成account的一对一操作,建立实体类关系的方式(常用的方式)
mybatis表之间关系分析
- 表之间的关系有几种:
- 一对多,如:用户和订单,1个用户有多个订单;
- 多对一,如:订单和用户,多个订单属于1个用户;
- 一对一,如:人和身份证号,一个人只有一个身份证号,一个身份证号只属于一个人;
- 多对多,如:老师和学生之间,一个学生被多个老师教过,一个老师可以教多个学生
特例:如果拿出每一个订单,他都只能属于一个用户,所以mybatis把多对一看成了一对一。
mybatis中的多表查询
示例:用户和账户
- 一个用户可以有多个账户;
- 一个账户只能属于一个用户(多个账户也可以属于同一个用户);
步骤:
1)建立两张表:用户表,账户表
- 让用户表和账户表之间具备一对多的关系,需要使用外键在账户表中添加;
2)建立两个实体类:用户实体类和账户实体类
- 让用户和账户的实体类,能体现出一对多的关系;
3)建立两个配置文件:用户的配置文件,账户的配置文件
4)实现配置
- 当查询用户时,可以同时得到用户下所包含的账户信息;
- 当查询账户时,可以同时得到账户的所属用户信息;
具体操作步骤:
1.1)创建用户表:user
drop table if exists `user`;
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 AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;
INSERT INTO `mybatis`.`user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES ('41', '大王', '2020-03-01 19:58:51', '女', '我家在上海市01区');
INSERT INTO `mybatis`.`user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES ('42', '中王', '2020-03-02 19:58:51', '男', '我家在上海市02区');
INSERT INTO `mybatis`.`user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES ('43', '小王', '2020-03-02 19:58:51', '男', '我家在上海市03区');
INSERT INTO `mybatis`.`user` (`id`, `username`, `birthday`, `sex`, `address`) VALUES ('44', '小李', '2020-03-11 19:58:51', '男', '我家在上海市01区');
1.2)创建账户表:account
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`),
KEY `fk_reference_8` (`uid`),
CONSTRAINT `fk_reference_8` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `account`(`id`,`uid`,`money`) values (1,41,1000),(2,42,1000),(3,41,2000);
2.1)建立两个实体类:用户实体类User和账户实体类Account
com.itheima.domain.User
package com.itheima.domain;
import java.io.Serializable;
import java.util.Date;
/**
* @author nikey
* @date 2020/3/19
*/
public class User implements Serializable {
// User实例类属性要与数据库user表中的列名一样
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer 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 + '\'' +
'}';
}
}
com.itheima.domain.Account
package com.itheima.domain;
import java.io.Serializable;
/**
* @author nikey
* @date 2020/4/3
*/
public class Account implements Serializable {
private Integer id;
private Integer uid;
private double money;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
2.2)创建两个接口:用户接口IUserDao和账户接口IAccountDao
com.itheima.dao.IUserDao
package com.itheima.dao;
import com.itheima.domain.User;
import java.util.List;
/**
* @author nikey
* @date 2020/3/19
* 用户的持久层接口
*/
public interface IUserDao {
// 查询所有用户操作
List<User> findAll();
// 依据用户id查询用户信息
User findById(Integer id);
}
com.itheima.dao.IAccountDao
package com.itheima.dao;
import com.itheima.domain.Account;
import java.util.List;
/**
* @author nikey
* @date 2020/4/3
* 账户的持久层接口
*/
public interface IAccountDao {
// 查询所有账户
List<Account> findAll();
}
3)建立两个配置文件:用户的配置文件IUserDao.xml,账户的配置文件IAccountDao.xml
com/itheima/dao/IUserDao.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">
<!-- 命名空间,xml文件和dao接口对接起来 -->
<!--dao的全限定类名-->
<mapper namespace="com.itheima.dao.IUserDao">
<sql id="defaultUser">
select * from user
</sql>
<!--查询所有用户操作-->
<select id="findAll" resultType="user">
<include refid="defaultUser"/>
</select>
<!--依据用户id查询用户信息-->
<select id="findById" parameterType="integer" resultType="user" >
<include refid="defaultUser"/> where id = #{id}
</select>
</mapper>
com/itheima/dao/IAccountDao.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">
<!-- 命名空间,xml文件和dao接口对接起来 -->
<!--dao的全限定类名-->
<mapper namespace="com.itheima.dao.IAccountDao">
<sql id="defaultAccount">
select * from account
</sql>
<!--查询所有账户操作-->
<select id="findAll" resultType="account">
<include refid="defaultAccount"/>
</select>
</mapper>
4.1)测试查询所有账户AccountTest
com.itheima.test.AccountTest
package com.itheima.test;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
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;
/**
* @author nikey
* @date 2020/4/3
*/
public class AccountTest {
private InputStream in;
private SqlSessionFactory factory;
private SqlSession session;
private IAccountDao accountDao;
@Before // 用于在测试方法执行之前执行
public void init() throws IOException {
// 1、读取配置文件,生成字节输入流
in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2、获取SqlSessionFactory
factory = new SqlSessionFactoryBuilder().build(in);
// 3、获取SqlSession对象
session = factory.openSession();
// 4、获取Dao的代理对象
accountDao = session.getMapper(IAccountDao.class);
}
@After // 用于在测试方法执行之后执行
public void destroy() throws IOException {
// 提交事务
session.commit();
// 释放资源
session.close();
in.close();
}
@Test
// 测试查询所有账户
public void testFindAll(){
List<Account> accounts = accountDao.findAll();
for(Account acct:accounts){
System.out.println(acct);
}
}
}
运行结果:
2020-04-03 18:02:31,396 566 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 18:02:31,665 835 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2353b3e6]
2020-04-03 18:02:31,678 848 [ main] DEBUG theima.dao.IAccountDao.findAll - ==> Preparing: select * from account
2020-04-03 18:02:31,773 943 [ main] DEBUG theima.dao.IAccountDao.findAll - ==> Parameters:
2020-04-03 18:02:31,796 966 [ main] DEBUG theima.dao.IAccountDao.findAll - <== Total: 3
Account{id=1, uid=41, money=1000.0}
Account{id=2, uid=42, money=1000.0}
Account{id=3, uid=41, money=2000.0}
2020-04-03 18:02:31,796 966 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2353b3e6]
2020-04-03 18:02:31,797 967 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2353b3e6]
4.2)完成account的一对一操作,通过写account的子类方式查询(不常用的方式)
com.itheima.domain.AccountUser
创建AccountUser类,继承于Account类
package com.itheima.domain;
/**
* @author nikey
* @date 2020/4/3
*/
// 从父类继承信息
public class AccountUser extends Account {
private String username;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
// 调用父类的toString,拼接AccountUser的toString
return super.toString() + " AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
com.itheima.dao.IAccountDao
查询所有账户,并且带有用户名和地址信息,List<AccountUser> findAllAccount();
package com.itheima.dao;
import com.itheima.domain.Account;
import com.itheima.domain.AccountUser;
import java.util.List;
/**
* @author nikey
* @date 2020/4/3
* 账户的持久层接口
*/
public interface IAccountDao {
// 查询所有账户,同时还要获取到当前账户的所属用户信息
List<Account> findAll();
// 查询所有账户,并且带有用户名和地址信息
List<AccountUser> findAllAccount();
}
com/itheima/dao/IAccountDao.xml
<!--查询所有账户,并且带有用户名和地址信息-->
<select id="findAllAccount" resultType="accountuser">
select a.*,u.username,u.address from account a,user u where u.id=a.uid;
</select>
com.itheima.test.AccountTest
@Test
// 查询所有账户,并且带有用户名和地址信息
public void testFindAllAccount(){
List<AccountUser> accountUsers = accountDao.findAllAccount();
for(AccountUser acctuser:accountUsers){
System.out.println(acctuser);
}
}
运行结果:
2020-04-03 18:26:42,022 365 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 18:26:42,224 567 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
2020-04-03 18:26:42,233 576 [ main] DEBUG dao.IAccountDao.findAllAccount - ==> Preparing: select a.*,u.username,u.address from account a,user u where u.id=a.uid;
2020-04-03 18:26:42,277 620 [ main] DEBUG dao.IAccountDao.findAllAccount - ==> Parameters:
2020-04-03 18:26:42,299 642 [ main] DEBUG dao.IAccountDao.findAllAccount - <== Total: 3
Account{id=1, uid=41, money=1000.0} AccountUser{username='大王', address='我家在上海市01区'}
Account{id=3, uid=41, money=2000.0} AccountUser{username='大王', address='我家在上海市01区'}
Account{id=2, uid=42, money=1000.0} AccountUser{username='中王', address='我家在上海市02区'}
2020-04-03 18:26:42,301 644 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
2020-04-03 18:26:42,302 645 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
4.3)完成account的一对一操作,建立实体类关系的方式(常用的方式)
com.itheima.domain.Account
从表(Account)实体中,应该包含一个主表(User)实体的对象引用;
package com.itheima.domain;
import java.io.Serializable;
/**
* @author nikey
* @date 2020/4/3
*/
public class Account implements Serializable {
private Integer id;
private Integer uid;
private double money;
// 从表实体应该包含一个主表实体的对象引用
private User user;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
com/itheima/dao/IAccountDao.xml
定义封装account和user的resultMap:定义java Bean的属性与数据库的列之间的映射
- 对Account的数据封装;Account表的主键id的column是id列,别名aid;
- 一对一的关系映射:配置封装user的内容,使用<association>标签,javaType指定封装的是哪个对象信息,这里写的别名user;
<association>标签的各个属性的含义:
- property:映射数据库列的实体类对象的属性。Account实体类中定义的属性user,通过javaType指定为com.itheima.domain.User类型;
- colum:数据库的列名或者列标签别名;用哪个列的值作为条件,去查询关联的对象;
- javaType:完整java类名或别名;
- jdbcType:支持的JDBC类型列表,列出的JDBC类型。这个属性只在insert,update或delete的时候,针对允许空的列有用;
- resultMap:一个可以映射联合嵌套结果集到一个适合的对象视图上的ResultMap。这是一个替代的方式去调用另一个select语句。
<!--定义封装account和user的resultMap-->
<resultMap id="accountUserMap" type="account">
<!--Account的数据封装-->
<id property="id" column="aid"/>
<result property="uid" column="uid"/>
<result property="money" column="money"/>
<!--一对一的关系映射:配置封装user的内容,javaType指定封装的是哪个对象信息-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
</resultMap>
<!--查询所有账户操作-->
<select id="findAll" resultMap="accountUserMap">
select u.*,a.id as aid,a.uid,a.money from user u,account a where u.id=a.uid;
</select>
com.itheima.test.AccountTest
@Test
// 测试查询所有账户
public void testFindAll(){
List<Account> accounts = accountDao.findAll();
for(Account acct:accounts){
System.out.println("----------每个account的信息----------");
System.out.println(acct);
System.out.println(acct.getUser());
}
}
运行结果:
2020-04-03 19:03:48,375 318 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-03 19:03:48,562 505 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@37d31475]
2020-04-03 19:03:48,568 511 [ main] DEBUG theima.dao.IAccountDao.findAll - ==> Preparing: select u.*,a.id as aid,a.uid,a.money from user u,account a where u.id=a.uid;
2020-04-03 19:03:48,612 555 [ main] DEBUG theima.dao.IAccountDao.findAll - ==> Parameters:
2020-04-03 19:03:48,643 586 [ main] DEBUG theima.dao.IAccountDao.findAll - <== Total: 3
----------每个account的信息----------
Account{id=1, uid=41, money=1000.0}
User{id=41, username='大王', birthday='Sun Mar 01 19:58:51 CST 2020', sex='女', address='我家在上海市01区'}
----------每个account的信息----------
Account{id=3, uid=41, money=2000.0}
User{id=41, username='大王', birthday='Sun Mar 01 19:58:51 CST 2020', sex='女', address='我家在上海市01区'}
----------每个account的信息----------
Account{id=2, uid=42, money=1000.0}
User{id=42, username='中王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市02区'}
2020-04-03 19:03:48,645 588 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@37d31475]
2020-04-03 19:03:48,646 589 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@37d31475]
4.4)完成user的一对多查询操作
com.itheima.domain.User
一对多关系映射:主表实体应该包含,从表实体的集合引用,private List<Account> accounts;
package com.itheima.domain;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* @author nikey
* @date 2020/3/19
*/
public class User implements Serializable {
// User实例类属性要与数据库user表中的列名一样
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
// 一对多关系映射:主表实体应该包含从表实体的集合引用
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public Integer getId() {
return id;
}
public void setId(Integer 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 + '\'' +
'}';
}
}
com.itheima.dao.IUserDao
package com.itheima.dao;
import com.itheima.domain.User;
import java.util.List;
/**
* @author nikey
* @date 2020/3/19
* 用户的持久层接口
*/
public interface IUserDao {
// 查询所有用户,同时获取到用户下所有账户的信息
List<User> findAll();
}
com/itheima/dao/IUserDao.xml
定义User的 resultMap:定义java Bean的属性与数据库的列之间的映射
- 对User的数据封装
- 一对多的关系映射:配置User对象中accounts集合的映射,使用<collection>标签;
- <collection property="accounts" ofType="Account">
<?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">
<!-- 命名空间,xml文件和dao接口对接起来 -->
<!--dao的全限定类名-->
<mapper namespace="com.itheima.dao.IUserDao">
<!--定义User的 resultMap-->
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="sex" column="sex"/>
<result property="birthday" column="birthday"/>
<!--配置User对象中accounts集合的映射-->
<collection property="accounts" ofType="Account">
<id property="id" column="aid"/>
<result property="uid" column="uid"/>
<result property="money" column="money"/>
</collection>
</resultMap>
<!--查询所有用户,同时获取到用户下所有账户的信息-->
<select id="findAll" resultMap="userAccountMap">
select u.*,a.id as aid,a.uid,a.money from user u LEFT OUTER JOIN account a on u.id=a.uid;
</select>
</mapper>
com.itheima.test.UserTest
@Test
// 测试查询所有用户,同时获取到用户下所有账户的信息
public void testFindAll(){
List<User> users = userDao.findAll();
for(User u:users){
System.out.println("----------每个user的信息----------");
System.out.println(u);
System.out.println(u.getAccounts());
}
}
运行结果:
- mybatis会自动把用户中的多个账户,合并在一起显示;如User(id=41)的用户,有2个Account账户;
2020-04-08 00:37:26,799 540 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Opening JDBC Connection
2020-04-08 00:37:27,345 1086 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
2020-04-08 00:37:27,361 1102 [ main] DEBUG m.itheima.dao.IUserDao.findAll - ==> Preparing: select u.*,a.id as aid,a.uid,a.money from user u LEFT OUTER JOIN account a on u.id=a.uid;
2020-04-08 00:37:27,460 1201 [ main] DEBUG m.itheima.dao.IUserDao.findAll - ==> Parameters:
2020-04-08 00:37:27,522 1263 [ main] DEBUG m.itheima.dao.IUserDao.findAll - <== Total: 5
----------每个user的信息----------
User{id=41, username='大王', birthday='Sun Mar 01 19:58:51 CST 2020', sex='女', address='我家在上海市01区'}
[Account{id=1, uid=41, money=1000.0}, Account{id=3, uid=41, money=2000.0}]
----------每个user的信息----------
User{id=42, username='中王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市02区'}
[Account{id=2, uid=42, money=1000.0}]
----------每个user的信息----------
User{id=43, username='小王', birthday='Mon Mar 02 19:58:51 CST 2020', sex='男', address='我家在上海市03区'}
[]
----------每个user的信息----------
User{id=44, username='小李', birthday='Wed Mar 11 19:58:51 CST 2020', sex='男', address='我家在上海市01区'}
[]
2020-04-08 00:37:27,524 1265 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
2020-04-08 00:37:27,525 1266 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@631330c]
数据库表查询结果:select u.*,a.id as aid,a.uid,a.money from user u LEFT OUTER JOIN account a on u.id=a.uid;
源码:day03_eesy_03one2many
4.5)多对多操作:查询角色获取角色下所属用户信息
4.6)多对多操作:查询用户获取用户所包含的角色信息
源码:
源码: