文章目录
1、一对一查询(多对一)
用户和账户:[用一个uid管理起来]
- 一个用户可以有多个账户[一对多]
- 一个账户只能属于一个用户[一对一]
- 多个账户可以被同一个用户拥有
建立账户信息表
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,46,1000),(2,45,1000),(3,46,2000);
方式一
- 定义账户信息的实体类
package com.oceanstar.domain;
public class Account {
private Integer id;
private Integer uid;
private Double money;
.......
}
- 编写sql语句
SELECT account.*,user.username,user.address FROM account,user WHERE account.uid = user.id
- 定义AccountUser类
为了能够封装上面Sql语句的查询结果,定义AccountCustomer类中要包含账户信息同时幺幺保证用户信息
public class AccountUser extends Account implements Serializable {
private String username;
private String address;
...
@Override
public String toString() {
return super.toString() + "AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
- 定义账户的持久层Dao接口
/*
* 账户的持久层接口
* */
public interface IAccountDao {
/*
* 查询所有账户,同时获取账户的所属用户名称以及它的地址信息
* */
List<AccountUser> findAll();
}
- 定义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">
<mapper namespace="com.oceanstar.dao.IAccountDao">
<select id="findAll" resultType="com.oceanstar.domain.AccountUser">
SELECT account.*,user.username,user.address FROM account,user WHERE account.uid = user.id
</select>
</mapper>
- 修改SqlMapper.xml
<!--配置映射配置文件的位置,映射配置文件指的是每个dao的配置文件-->
<mappers>
<mapper resource="com/oceanstar/dao/IUserDao.xml"></mapper>
<mapper resource="com/oceanstar/dao/IAccountDao.xml"></mapper>
</mappers>
- 创建AccountTest测试类
@Test
public void testFindAll() {
//6、使用代理对象执行方法
List<AccountUser> accountUsers = iAccountDao.findAll();
for (Account accountUser : accountUsers){
System.out.println(accountUser);
}
}
方式二
定义resultMap,定义专门的resultMap用于映射一对一查询结果。
通过面向对象has a关系可以得知,我们可以在Account类中加入一个User类的对象来代表这个账户是哪个用户的。
- 修改Account类:Account类加入User类的对象作为Account类的一个属性
public class Account {
private Integer id;
private Integer uid;
private Double money;
private User user;
}
此时Account类中包含了一个User类对象,他可以封装账户所对应的用户信息
- 修改IAccountDao接口
public interface IAccountDao {
/*
* 查询所有账户,同时获取账户的所属用户名称以及它的地址信息
* */
List<Account> findAll();
}
- 重新定义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">
<mapper namespace="com.oceanstar.dao.IAccountDao">
<resultMap id="userAccountAmp" type="com.oceanstar.domain.Account">
<id column="aid" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
<collection property="user" ofType="com.oceanstar.domain.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userAccountAmp">
select user.*, account.id as aid, account.uid, account.money from user left join account on user.id = account.uid;
</select>
</mapper>
- 修改测试类
@Test
public void testFindAll() {
//6、使用代理对象执行方法
List<Account> accountUsers = iAccountDao.findAll();
for (Account accountUser : accountUsers){
System.out.println(accountUser);
}
}
2、一对多
- 需求:查询所有用户信息以及用户关联的账户信息
- 分析:用户信息和账户是一对多的关系,一个用户可能有多个账户,也可能没有账户。如果用户没有账户也应该显示出来— 左外链接
- sql编写:select user.*, account.id as aid, account.uid, account.money from user left join account on user.id = account.uid
代码实现:
- User类中加入List< Account >
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Account> accounts;
}
- 用户持久层Dao接口中加入查询方法
/*
* 查询用户,并且获取出每个用户下的所有账户信息
* @return
* */
List<User> findAll();
- 用户持久层Dao映射方法配置
<?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.oceanstar.dao.IUserDao">
<resultMap id="userAccountMap" type="com.oceanstar.domain.User">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
<collection property="accounts" ofType="com.oceanstar.domain.Account">
<id column="aid" property="id"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<!--配置查询所有-->
<select id="findAll" resultMap="userAccountMap">
select user.*, account.id as aid, account.uid, account.money from user left join account on user.id = account.uid;
</select>
</mapper>
- 测试方法
@Test
public void testFindAll() {
//6、使用代理对象执行方法
List<User> users = iUserDao.findAll();
for (User user : users){
System.out.println(user);
}
}