mybtis多表联查

数据库设计

company(公司)表

game(游戏)表

game_company(游戏与公司关系)表

login(用户)表

userinfo(用户信息)表

项目结构

1对1关系联查

通过用户消息的qq查用户

1.定义对象(用户类里定义用户消息对象)

package entity;

import java.util.List;

/**
 * @author hua
 * @version 1.0
 * @title User
 * @description
 * @create 2024/2/25 19:55
 */
public class User extends Base{
    private String userName;
    private String password;
    private UserInfo userInfo;
    private List<Game> games;

    public User() {
    }

    public User(String userName, String password, UserInfo userInfo, List<Game> games) {
        this.userName = userName;
        this.password = password;
        this.userInfo = userInfo;
        this.games = games;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public UserInfo getUserInfo() {
        return userInfo;
    }

    public void setUserInfo(UserInfo userInfo) {
        this.userInfo = userInfo;
    }

    public List<Game> getGames() {
        return games;
    }

    public void setGames(List<Game> games) {
        this.games = games;
    }

    @Override
    public String toString() {
        return "User{" +
                "userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", userInfo=" + userInfo +
                ", game=" + games +
                '}';
    }
}

 在原本的实体里加入用户消息对象并加入get,set函数,更新构造函数与toSring函数

2.定义resultMap

<resultMap id="UserAndUserInfoMap" type="User">
        <id column="id" property="id"></id>
        <result column="userName" property="userName"></result>
        <result column="password" property="password"></result>
        <!--one-to-one映射对象模型-->
        <association property="userInfo" resultMap="UserInfoMap">
        </association>
    </resultMap>
<resultMap id="UserInfoMap" type="UserInfo">
        <id column="id" property="id"></id>
        <result column="email" property="email"></result>
        <result column="qq" property="qq"></result>
        <result column="uid" property="uid"></result>
    </resultMap>

column就是数据库列名,property就是实体类对应的属性

3.写sql语句

<select id="queryUserInfoAndUserByQQ" parameterType="String" resultMap="UserAndUserInfoMap">
        SELECT * FROM login l JOIN userinfo u ON l.id =u.uid WHERE u.qq=#{qq};
    </select>

4.在dao层定义与id同名的函数

User queryUserInfoAndUserByQQ(String qq);

5.测试函数

SqlSessionFactory sqlSessionFactory = BuilderSessionFactory.getSqlSessionFactory();
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //产生接口的代理对象
    SelectDao selectDao =  sqlSession.getMapper(SelectDao.class);
    public void testQueryUserInfoAndUserByUserName() {
        User user = selectDao.queryUserInfoAndUserByQQ("123456");
        System.out.println(user);
    }