mybatis一对一查询（多对一）
mybatis一对一查询（多对一）
需求：查询所有账户信息，关联查询用户信息。
注意：因为一个账户信息只能供某个用户使用，所以从查询账户信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的账户信息则为一对多查询，因为一个用户可以有多个账户。
1、方式一
1.1、账户信息实体类
package com.yl.bean;
import java.io.Serializable;
/**
* 账户实体类
*/
public class Account implements Serializable {
private Integer id;//账户id
private Integer uid;//用户id
private Double money;//余额
public Account() {
}
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 +
'}';
}
}
1.2、用户实体类
package com.yl.bean;
import java.io.Serializable;
import java.sql.Date;
/**
* 用户实体类
*/
public class User implements Serializable {
private Integer id;//id
private String username;//用户名
private Date birthday;//生日
private String sex;//性别
private String address;//地址
public User() {
}
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 + '\'' +
'}';
}
}
1.3、编写sql语句
//实现查询账户信息时，也要查询账户所对应的用户信息。
SELECT
account.*,
user.username,
user.address
FROM
account,
user
WHERE account.uid = user.id
1.4、定义AccountUser类
为了能够封装上面 SQL 语句的查询结果，定义 AccountCustomer 类中要包含账户信息同时还要包含用户信息，所以我们要在定义 AccountUser 类时可以继承 User 类。
package com.yl.bean;
public class AccountUser extends User{
private String username;//用户名
private String address;//地址
@Override
public String getUsername() {
return username;
}
@Override
public void setUsername(String username) {
this.username = username;
}
@Override
public String getAddress() {
return address;
}
@Override
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return super.toString()+"AccountUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
1.5、定义账户持久层接口
package com.yl.dao;
import com.yl.bean.Account;
import com.yl.bean.User;
import java.util.List;
/**
* 账户持久层接口
*/
public interface IAccountDao {
/**
* 查询所有账户
*/
List<AccountUser> queryAll();
}
1.6、配置文件
<?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.itheima.dao.IAccountDao">
<!-- 配置查询所有操作，resultType使用了别名-->
<select id="findAll" resultType="accountuser">
select a.*,u.username,u.address from account a,user u where a.uid =u.id;
</select>
</mapper>
注意：因为上面查询的结果中包含了账户信息同时还包含了用户信息，所以我们的返回值类型 returnType的值设置为 AccountUser 类型，这样就可以接收账户信息和用户信息了。
1.7、测试
@Test
public void testFindAll() {
List<AccountUser> accountusers = accountDao.findAll();
for(AccountUser au : accountusers) {
System.out.println(au);
}
}
1.8、小结
定义专门的 po 类作为输出类型，其中定义了 sql 查询结果集所有的字段。此方法较为简单，企业中使用普遍。
2、方式二
使用 resultMap，定义专门的 resultMap 用于映射一对一查询结果。通过面向对象的(has a)关系可以得知，我们可以在 Account 类中加入一个 User 类的对象来代表这个账户是哪个用户的。
2.1、修改账户信息实体类
package com.yl.bean;
import java.io.Serializable;
/**
* 账户实体类
*/
public class Account implements Serializable {
private Integer id;//账户id
private Integer uid;//用户id
private Double money;//余额
private User user;//账户所属用户
public Account() {
}
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 +
'}';
}
}
2.2、用户实体类
package com.yl.bean;
import java.io.Serializable;
import java.sql.Date;
/**
* 用户实体类
*/
public class User implements Serializable {
private Integer id;//id
private String username;//用户名
private Date birthday;//生日
private String sex;//性别
private String address;//地址
public User() {
}
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 + '\'' +
'}';
}
}
2.3、账户持久层接口
package com.yl.dao;
import com.yl.bean.Account;
import com.yl.bean.User;
import java.util.List;
/**
* 账户持久层接口
*/
public interface IAccountDao {
/**
* 查询所有账户
*/
List<Account> queryAll();
}
注意：第二种方式，将返回值改 为了 Account 类型。因为 Account 类中包含了一个 User 类的对象，它可以封装账户所对应的用户信息。
2.4、配置文件
<?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.yl.dao.IAccountDao">
<!--配置Account属性对应数据库信息-->
<resultMap id="accountUserMap" type="account">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!--一对一关系映射，javaType使用了别名-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
</association>
</resultMap>
<!--查询所有账户-->
<select id="queryAll" resultMap="accountUserMap">
SELECT * FROM USER u,account a WHERE u.`id`=a.`UID`
</select>
</mapper>
2.5、测试
package com.yl;
import com.yl.bean.Account;
import com.yl.bean.User;
import com.yl.dao.IAccountDao;
import com.yl.dao.IUserDao;
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.sql.Date;
import java.util.List;
public class AccountTest {
private InputStream inputStream;
private IAccountDao accountDao;
private SqlSession sqlSession;
@Before
public void init() throws IOException {
//读取配置文件
inputStream= Resources.getResourceAsStream("SqlMapperConfig.xml");
//创建工厂
SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sessionFactory= factoryBuilder.build(inputStream);
//创建SqlSession对象
sqlSession=sessionFactory.openSession();
//创建IUserDao接口代理对象
accountDao=sqlSession.getMapper(IAccountDao.class);
}
@After
public void after() throws IOException {
//提交事务
sqlSession.commit();;
//关闭资源
sqlSession.close();
inputStream.close();
}
/**
* 查询所有
*/
@Test
public void test01() throws IOException {
//查询所有账户
List<Account> accountList=accountDao.queryAll();
//遍历输出
for (Account account:accountList){
System.out.println(account);
System.out.println(account.getUser().toString());
}
}
}
记得快乐
浙公网安备 33010602011771号