mybatis的多表查询（一对多）

初学mybatis框架，关于其多表查询的方法很多，下面简单写几种，帮助自己理解和加强记忆。

一、环境

1、数据库（表一：tb_user、表二 ：tb_role（role_id为外键））

方法一、添加业务字段

User.java实体类

package com.hfxt.domain;
import java.util.Date;
public class User {
	private Integer id;
	private String username;
	private String password;
	private Integer status;
	private Date createTime;
	private Integer roleId;
	private Role role;//添加业务字段
        //...get/set方法...
}

UserMapper.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.hfxt.dao.UserDao">
        <select id="getAllUsers" resultMap="rm_user"> 
           select tu.*,tr.rolename
           from tb_user as tu inner join tb_role as tr on tu.role_id=tr.id
        </select>
        <resultMap type="user" id="rm_user">
        	<collection property="role" column="role_id" select="com.hfxt.dao.RoleDao.getRoleById"></collection> 
                <--关键的配置-->
        </resultMap>
    </mapper>

Role.java实体类

package com.hfxt.domain;
public class Role {
	private Integer id;
	private String roleName;        
        //...get/set方法...
}

RoleMapper.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.hfxt.dao.RoleDao">
        <select id="getRoleById" resultType="role" >
            select * from tb_role where id=#{id}
        </select>
    </mapper>

mybatis-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<--别名-->
        <typeAliases>
	    <typeAlias type="com.hfxt.domain.User" alias="user"/>
	    <typeAlias type="com.hfxt.domain.Role" alias="role"/>
	</typeAliases>
         <--环境-->
         <environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/users?characterEncoding=UTF-8"/>
				<property name="username" value="root"/>
				<property name="password" value="root"/>
			</dataSource>
		</environment>
	</environments>
	
	<!-- 映射Xxxx.xml -->
	<mappers>
	    <mapper resource="com/hfxt/domain/UserMapper.xml"/>
	    <mapper resource="com/hfxt/domain/RoleMapper.xml"/>
	</mappers>
</configuration>

UserDao.java代码

package com.hfxt.dao;
import java.util.List;
import com.hfxt.domain.User;
public interface UserDao {
	public List<User> getAllUsers();
}

RoleDao.java代码

package com.hfxt.dao;
import com.hfxt.domain.Role;
public interface RoleDao {
	public Role getRoleById(Integer id);
}

Test.java代码

package com.hfxt.test;

import java.io.IOException;
import java.io.Reader;
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 com.hfxt.dao.UserDao;
import com.hfxt.domain.User;
public class Test {

	public static void main(String[] args) {
		SqlSession session=null;
		try {
			Reader reader=Resources.getResourceAsReader("mybatis-config.xml");
			SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
			SqlSessionFactory ssf=ssfb.build(reader);
			session=ssf.openSession();UserDao userDao=session.getMapper(UserDao.class);
			List<User> users=userDao.getAllUsers();
			for(User user:users){
				System.out.println(user.getUsername()+"，"+user.getPassword()+"，"+user.getRole().getId()+"，"+user.getRole().getRoleName());
			}
			System.out.println(userDao.getUserCount());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

（未完待续）