Mybatis一对一关联关系-案例

1、SQL语句

USE mybatis;

-- 创建用户表
CREATE TABLE `users`(
`userid` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) DEFAULT NULL,
`usersex` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY(`userid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建角色表
CREATE TABLE `roles`(
`roleid` INT(11) NOT NULL AUTO_INCREMENT,
`rolename` VARCHAR(30) DEFAULT NULL,
`user_id` INT(11) DEFAULT NULL,
PRIMARY KEY(`roleid`),
UNIQUE KEY `roles_fk`(`user_id`) USING BTREE,
CONSTRAINT `roles_fk` FOREIGN KEY(`user_id`) REFERENCES `users`(`userid`) 
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO users VALUES(NULL,'zhangsan','M'),
(NULL,'lisi','M'),
(NULL,'wangwu','F'),
(NULL,'赵六','M');

INSERT INTO roles VALUES(NULL, 'DBA', 1),
(NULL, 'Normal', 2),
(NULL, 'Normal', 3),
(NULL, 'Guest', 4);

SELECT * FROM roles;
SELECT * FROM users;

2、POJO（Roles.java和Users.java）

2.1 Roles.java

public class Roles {
    private Integer roleid;
    private String rolename;
    //private Integer userId; // userId是属于Users这个类的属性
	// get/set/toString/constructor方法执行补充
}

特别注意：userId是属于Users这个类的属性，故在此类中不需要添加，虽然roles表中有这一列，只是用于查询是关联的。

2.2 Users.java

public class Users {
    private Integer userid;
    private String username;
    private String usersex;
    private Roles roles;        // 用户角色
	// get/set/toString/constructor方法执行补充
}

二、DAO层

1、UserMapper.java

public interface UserMapper {
    List<Users> queryAll();
}

2、UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
		PUBLIC "-//mybatis.org//DTDMapper3.0//EN"
		"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dyh.dao.UserMapper">
	<resultMap id="usersMap" type="com.dyh.pojo.Users">
		<id property="userid" column="userid" />
		<result property="username" column="username" />
		<result property="usersex" column="usersex" />
		<!-- 配置一对一关联关系，查询users表的时候，会自动查询roles表 -->
		<association property="roles" javaType="com.dyh.pojo.Roles">
			<id property="roleid" column="roleid" />
			<result property="rolename" column="rolename" />
		</association>
	</resultMap>
	<select id="queryAll"  resultMap="usersMap">
		select u.userid, u.username, u.usersex,r.roleid,rolename
		from users u , roles r where u.userid=r.user_id
	</select>
</mapper>

三、测试UserMapperTest.java

package com.dyh.test;

import com.dyh.dao.UserMapper;
import com.dyh.pojo.Users;
import com.dyh.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class UserMapperTest {
    SqlSession session;
    UserMapper mapper;
    @Before
    public void setUp(){
        session = MybatisUtil.getConnection();
        mapper = session.getMapper(UserMapper.class);
    }
    @Test
    public void testQueryUsers(){
        List<Users> list = mapper.queryAll();
        list.forEach(System.out::println);
    }

    @After
    public void setAfter(){
        MybatisUtil.closeConnection();
    }
}

运行结果如下：