模擬業務關系：

一個用戶user有對應的一個公司company，每個用戶有多個賬戶account。

spring boot 2的環境搭建見上文：spring boot 2整合mybatis

一、mysql創表和模擬數據sql

CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `company` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `user`VALUES (1, ’aa’, 1), (2, ’bb’, 2);INSERT INTO `company`VALUES (1, ’xx公司’), (2, ’yy公司’);INSERT INTO `account`VALUES (1, ’中行’, 1), (2, ’工行’, 1), (3, ’中行’, 2);

二、創建實體

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 這里省略...}

三、開發Mapper

方法一：使用注解

1、AccountMapper.java

package com.example.demo.mapper;import java.util.List;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import com.example.demo.entity.Account;public interface AccountMapper { /* * 根據用戶id查詢賬戶信息 */ @Select('SELECT * FROM `account` WHERE user_id = #{userId}') @Results({ @Result(property = 'accountName', column = 'name') }) List<Account> getAccountByUserId(Long userId);}

2、CompanyMapper.java

package com.example.demo.mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import com.example.demo.entity.Company;public interface CompanyMapper { /* * 根據公司id查詢公司信息 */ @Select('SELECT * FROM company WHERE id = #{id}') @Results({ @Result(property = 'companyName', column = 'name') }) Company getCompanyById(Long id);}

3、UserMapper.java

package com.example.demo.mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.One;import org.apache.ibatis.annotations.Many;import com.example.demo.entity.User;public interface UserMapper { /* * 一對一查詢 * property：查詢結果賦值給此實體屬性 * column：對應數據庫的表字段，做為下面@One(select方法的查詢參數 * one：一對一的查詢 * @One(select = 方法全路徑) ：調用的方法 */ @Select('SELECT * FROM user WHERE id = #{id}') @Results({ @Result(property = 'company', column = 'company_id', one = @One(select = 'com.example.demo.mapper.CompanyMapper.getCompanyById')) }) User getUserWithCompany(Long id); /* * 一對多查詢 * property：查詢結果賦值給此實體屬性 * column：對應數據庫的表字段，可做為下面@One(select方法)的查詢參數 * many：一對多的查詢 * @Many(select = 方法全路徑) ：調用的方法 */ @Select('SELECT * FROM user WHERE id = #{id}') @Results({ @Result(property = 'id', column = 'id'),//加此行，否則id值為空 @Result(property = 'accounts', column = 'id', many = @Many(select = 'com.example.demo.mapper.AccountMapper.getAccountByUserId')) }) User getUserWithAccount(Long id); /* * 同時用一對一、一對多查詢 */ @Select('SELECT * FROM user') @Results({ @Result(property = 'id', column = 'id'), @Result(property = 'company', column = 'company_id', one = @One(select = 'com.example.demo.mapper.CompanyMapper.getCompanyById')), @Result(property = 'accounts', column = 'id', many = @Many(select = 'com.example.demo.mapper.AccountMapper.getAccountByUserId')) }) List<User> getAll(); }

方法二：使用XML

參考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后，以上面的getAll()方法為例，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.example.demo.mapper.UserMapper' > <resultMap 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 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>

四、控制層

package com.example.demo.web;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.example.demo.entity.User;import com.example.demo.mapper.UserMapper;@RestControllerpublic class UserController { @Autowired private UserMapper userMapper; //請求例子：http://localhost:9001/getUserWithCompany/1 /*請求結果：{'id':1,'name':'aa','company':{'id':1,'companyName':'xx公司'},'accounts':null}*/ @RequestMapping('/getUserWithCompany/{id}') public User getUserWithCompany(@PathVariable('id') Long id) { User user = userMapper.getUserWithCompany(id); return user; } //請求例子：http://localhost:9001/getUserWithAccount/1 /*請求結果：{'id':1,'name':'aa','company':null,'accounts':[{'id':1,'accountName':'中行'},{'id':2,'accountName':'工行'}]}*/ @RequestMapping('/getUserWithAccount/{id}') public User getUserWithAccount(@PathVariable('id') Long id) { User user = userMapper.getUserWithAccount(id); return user; } //請求例子：http://localhost:9001/getUserWithAccount/1 /*請求結果：[{'id':1,'name':'aa','company':{'id':1,'companyName':'xx公司'},'accounts':[{'id':1,'accountName':'中行'}, {'id':2,'accountName':'工行'}]},{'id':2,'name':'bb','company':{'id':2,'companyName':'yy公司'},'accounts':[{'id':3,'accountName':'中行'}]}]*/ @RequestMapping('/getUsers') public List<User> getUsers() { List<User> users=userMapper.getAll(); return users; } }

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持好吧啦網。