mybatis的多表查询（一对多）

谁践踏了优雅 2022-05-31 13:57 353阅读 0赞

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

一、环境

1、数据库（表一：tb\_user、表二 ：tb\_role（role\_id为外键））

![70][]

![70 1][]

方法一、添加业务字段

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映射文件

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>
    	
    	
    	<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();
    		}
    	}
    }

（未完待续）

[70]: /images/20220531/90da2e0fe3834206916e16ceb4d23b16.png
[70 1]: /images/20220531/a3dcbcc3e693424ba6530a7dd119fa08.png