1、引入
需求:
查询所有用户信息及用户关联的账户信息。
分析:
用户信息和他的账户信息为一对多关系,并且查询过程中如果用户没有账户信息,此时也要将用户信息查询出来,我们想到了左外连接查询比较合适。(一个用户对应多个账户信息
)
2、编写实体类
public class Account implements Serializable{
private Integer id;
private Integer uid;
private double money;
···//
}
public class User implements Serializable{
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一对多关系映射:主要实体应该包含从表实体的集合引用
private List<Account> accounts;
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public List<Account> getAccounts() {
return accounts;
}
...//
}
3、编写持久层接口
/**
* @author
* 用户的持久层接口
* mybatis的映射配置文件位置必须和dao接口的包结构相同
*/
public interface IUserDao {
/**
* 查询所有操作:同时获取 用户
*/
List<User> findAll();
}
4、编写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">
<mapper namespace="com.song.dao.IUserDao">
<!-- 配置查询所有 -->
<!-- 定义一个User的resultMap -->
<resultMap id="userAccountMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<!-- 配置user对象中accounts集合的映射 -->
<collection property="accounts" ofType="account">
<id property="id" column="aid"></id>
<result property="uid" column ="uid"></result>
<result property="money" column ="money"></result>
</collection>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="userAccountMap">
select user.*,account.money,account.uid,account.id as aid from user left outer join account on user.id=account.uid
</select>
</mapper>
5、编写测试类
package com.song.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.song.dao.IUserDao;
import com.song.domain.User;
public class UserTest {
private InputStream in;
private SqlSessionFactory factory;
private SqlSession sqlSession;
private IUserDao userDao;
@Before
public void init() throws IOException {
//1、读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2、创建SqlSessionFactory工厂(工厂模式)
factory= new SqlSessionFactoryBuilder().build(in);
//3、使用工厂生产SqlSesson对象
sqlSession = factory.openSession(true);//手动将事务提交方式改为自动提交
//4、使用SqlSession创建Dao接口的代理对象
userDao = sqlSession.getMapper(IUserDao.class);
}
@After//用于在测试方法执行之后执行
public void destory() throws Exception {
//提交事务
//sqlSession.commit();
//6、释放资源
sqlSession.close();
in.close();
}
/**
* 查询账户所有
*/
@Test
public void testfindAllcount() {
List<User> users = userDao.findAll();
for(User user:users) {
System.out.print("这是一条记录:");
System.out.println(user);
System.out.println(user.getAccounts());
}
}
}