mybatis表关联（一对一）

最新推荐文章于 2024-10-21 17:03:02 发布

原创最新推荐文章于 2024-10-21 17:03:02 发布 · 199 阅读

0 ·

CC 4.0 BY-SA版权

springboot 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了在MyBatis中如何实现表的一对一查询，包括创建t_account和t_user两张表，定义对应的Bean类，编写Mapper映射文件，并提供了测试案例。

建表 t_account

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_account
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account` (
  `ID` int NOT NULL COMMENT '编号',
  `UID` int DEFAULT NULL COMMENT '用户编号',
  `MONEY` double DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`ID`),
  KEY `FK_Reference_8` (`UID`),
  CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表t_user

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名',
  `telephone` varchar(11) NOT NULL COMMENT '手机',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `gender` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

一，一对一查询

T-accout Bean

public class T_account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    private T_user t_user;

---set,get方法----
}

T_user Bean

public class T_user {
    private Integer id;
    private String username;
    private String telephone;
    private Date birthday;
----set  get方法
}

Mapper映射

public interface AccountMapper {
    List<T_account> findAll();

    List<UserAccount> findUserAll();
}

<?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.yjg.mybatis.mapper.AccountMapper">
   <!-- <select id="findAll" resultType="com.yjg.mybatis.bean.T_account">
        select * from t_account
    </select>-->

    <select id="findUserAll" resultType="com.yjg.mybatis.bean.UserAccount">
        select a.*,u.username,u.address from t_account a , t_user u where u.id = a.uid;
    </select>

    <!-- 定义封装 Account和User 的resultMap -->
    <resultMap id="userAccountMap" type="com.yjg.mybatis.bean.T_account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- 配置封装 User 的内容 -->
        <association property="t_user" column="uid" javaType="com.yjg.mybatis.bean.T_user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="telephone" column="telephone"></result>
            <result property="birthday" column="birthday"></result>
            <result property="gender" column="gender"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>

    <!-- 根据查询所有用户 -->
    <select id="findAll" resultMap="userAccountMap">
        SELECT u.*,a.id AS aid,a.uid,a.money FROM t_account a,t_user u WHERE u.id = a.uid;
    </select>
</mapper>

测试

@RestController
public class AccountController {
    @Autowired(required = false)
    AccountMapper accountMapper;

    @GetMapping("/account")
    public List<T_account> t_accounts(){
        return accountMapper.findAll();
    }

    @GetMapping("/useraccount")
    public List<UserAccount> tu_accounts(){
        return accountMapper.findUserAll();
    }

}