准备
用户和账户
- 一个用户可以由多个账户
- 一个账户只能属于一个用户,多个账户也可以属于一个用户
- 建立两张表,用户表,账户表,让用户表和账户表具备一对多的关系,需要使用外键在账户中
- 建立两个实体列,用户实体类和账户实体类,让用户和账户的实体类实现一对多的关系
- 建立连个配置文件:用户的配置文件+用户的配置文件
- 实现配置,当我们查询用户时,可以同时得到用户下所包含的账户信息,当我们查询账户时,可以同时得到账户所属用户信息
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(16) 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=7 DEFAULT CHARSET=utf8
create table account(
`id` int not null comment 'account的id',
`uid` int default null comment '用户的id',
`money` double default null comment '金额',
PRIMARY key (`id`),
CONSTRAINT FOREIGN KEY(`uid`) REFERENCES `user`(`id`)
) ENGINE= INNODB DEFAULT charset=utf8;
INSERT INTO `account` values(1,2,200)
INSERT INTO `account` values(2,2,30)
准备工作
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
private Integer id;
private Integer uid;
private Double money;
}
/**
*
* 查询所有账户
* @return
*/
List<Account> findAll();
<!--mapper绑定interface接口-->
<mapper namespace="com.kcl.dao.AccountDao">
<select id="findAll" resultType="com.kcl.pojo.Account">
select * from account
</select>
</mapper>
查询Account同时还要获取当前账户的所属用户信息(一对一查询)
分析:
select * from user u,account a where u.id = a.uid
返回实体
public class AccountUser extends Account {
//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() {
return super.toString()+ "AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
/**
* 查询所以账户,并带有用户名称和地址信息
*/
List<AccountUser> findAccountWithNameAndAddress();
<select id="findAccountWithNameAndAddress" resultType="com.kcl.pojo.AccountUser">
select a.*,u.username,u.address
from account a,user u where a.uid = u.id
</select>
这种通过写一个Account子类的方式来实现,用的不多!
常用的方式:
Account.java
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Account {
private Integer id;
private Integer uid;
private Double money;
//从表实体应该包含一个主表实体的对象引用
private User user;
}
AccountDao.java
/**
*
* 查询所有账户
* @return
*/
List<Account> findAll();
AccountDao.xml
<!--定义封装account和user的resultmap-->
<!--account实体列,包含一个user对象-->
<resultMap id="accountUserMap" type="com.kcl.pojo.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一的关系映射,配置封装user的内容-->
<!--javaType:封装到哪个对象-->
<association property="user" javaType="com.kcl.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username" ></result>
<result property="sex" column="sex" ></result>
<result property="address" column="address" ></result>
<result property="birthday" column="birthday" ></result>
</association>
</resultMap>
<select id="findAll" resultType="com.kcl.pojo.Account" resultMap="accountUserMap">
select u.*,a.id as aid,a.uid,a.money
from account a,user u where a.uid = u.id
</select>
一对多查询:查询用户,同时获取到用户下面的所以账户信息
分析:
select * from user u left join account a on u.id = a.uid
User实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一对多关系映射,主表实体包含从表的集合引用
private List<Account> accounts;
}
接口
/**
* 获取用户时,同时得到其账户下的所以账户信息
*/
List<User> findAll();
实现xml
<resultMap id="userAccountMap" type="com.kcl.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<result property="address" column="address"></result>
<collection property="accounts" ofType="com.kcl.pojo.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
<select id="findAll" resultType="com.kcl.pojo.User" resultMap="userAccountMap">
select u.*,a.id as aid,a.uid,a.money
from user u left join account a on u.id = a.uid
</select>