Mybatis多表查询

1.创建实体对象

public class User {		
	    private Integer id;
	    private String name;
	    private Company company;
	    private List<Account> accounts;	
	    //getter/setter 这里省略...
    }
 
    public class Company {
	    private Integer id;
	    private String companyName;
    	//getter/setter 这里省略...
    }
 
    public class Account {
	    private Integer id;
	    private String accountName;
	    //getter/setter 这里省略...
    }

2.创建mapper

   public interface UserMapper {
	   User getUserWithCompany(Long id);
	   
	   User getUserWithAccount(Long id);
	   
	   List<User> getAll();	
    }

3.创建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.example.demo.mapper.UserMapper" >
    <resultMap id="UserMap" type="com.example.demo.entity.User">
	    <id column="id" jdbcType="INTEGER" property="id" />
	    <result property="name" column="name" jdbcType="VARCHAR" />	
	    <!--封装映射company表数据，user表与company表1对1关系，配置1对1的映射
	        association:用于配置1对1的映射
	        属性property：company对象在user对象中的属性名
	        属性javaType：company属性的java对象 类型
	        属性column：user表中的外键引用company表
	    -->
	    <association property="company" javaType="com.example.demo.entity.Company" column="company_id">
	        <id property="id" column="companyid"></id>
	        <result property="companyName" column="companyname"></result>            
	    </association>
	    <!--配置1对多关系映射
	        property：在user里面的List<Account>的属性名            
	        ofType:当前account表的java类型
	        column:外键
	    -->
	    <collection property="accounts" ofType="com.example.demo.entity.Account" column="user_id">
	        <id property="id" column="accountid"></id>
	        <result property="accountName" column="accountname"></result>           
	    </collection>        
	  </resultMap>
    <select id="getAll" resultMap="UserMap"  >
    SELECT 
        u.id,u.name,c.id companyid, c.name companyname, a.id accountid,a.name accountname 
        FROM user u 
        LEFT JOIN company c on u.company_id=c.id
        LEFT JOIN account a on u.id=a.user_id
    </select>
</mapper>

4.控制层

@RestController
public class UserController {
	@Autowired
	private UserMapper userMapper;
	
	@RequestMapping("/getUserWithCompany/{id}")
	public User getUserWithCompany(@PathVariable("id") Long id) {
		User user = userMapper.getUserWithCompany(id);
		return user;
	}
	
	@RequestMapping("/getUserWithAccount/{id}")
	public User getUserWithAccount(@PathVariable("id") Long id) {
		User user = userMapper.getUserWithAccount(id);
		return user;
	}
	
	@RequestMapping("/getUsers")
	public List<User> getUsers() {
		List<User> users=userMapper.getAll();
		return users;
	}	
}