idea实现关联表查询

最新推荐文章于 2024-08-15 12:14:14 发布

DragonGod__

最新推荐文章于 2024-08-15 12:14:14 发布

阅读量3.4k

点赞数 1

CC 4.0 BY-SA版权

文章标签： java 开发语言 sql 数据库

本文链接：https://blog.csdn.net/DragonGod__/article/details/122142432

本文档介绍了在IDEA中进行关联表查询的步骤，包括创建实体类、定义功能接口、编写接口的XML配置文件以及在资源文件中配置映射文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.创建实体类

package com.bdqn.pojo;

import java.util.Date;
import java.util.List;

/**
 * @author Guo
 * @create 2021-12-23 14:28
 * 用户表单实体类
 */
public class Smbms_User {
    //私有化创建数据库属性
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private Date birthday;
    private String phone;
    private String address;
    private Integer userRole;
    private Integer createdBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;
    private String idPicPath;
    private String workPicPath;

   /* //第一种方式,实体类里面添加一个字段角色名称
    private String roleName;

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }*/


    //第二种方式,添加用户角色的类型属性
    private Smbms_Role role;

    public Smbms_Role getRole() {
        return role;
    }

    public void setRole(Smbms_Role role) {
        this.role = role;
    }

    //添加供应商地址属性
    private List<Smbms_Address> addressList;

    public List<Smbms_Address> getAddressList() {
        return addressList;
    }

    public void setAddressList(List<Smbms_Address> addressList) {
        this.addressList = addressList;
    }

    @Override
    public String toString() {
        return "Smbms_User{" +
                "id=" + id +
                ", userCode='" + userCode + '\'' +
                ", userName='" + userName + '\'' +
                ", userPassword='" + userPassword + '\'' +
                ", gender=" + gender +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", userRole=" + userRole +
                ", createdBy=" + createdBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                ", idPicPath='" + idPicPath + '\'' +
                ", workPicPath='" + workPicPath + '\'' +
//                ", role=" + role.toString() +
                ", addressList=" + addressList.toString() +
                '}';
    }
//封装方法

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

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

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Integer getUserRole() {
        return userRole;
    }

    public void setUserRole(Integer userRole) {
        this.userRole = userRole;
    }

    public Integer getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(Integer createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    public String getIdPicPath() {
        return idPicPath;
    }

    public void setIdPicPath(String idPicPath) {
        this.idPicPath = idPicPath;
    }

    public String getWorkPicPath() {
        return workPicPath;
    }

    public void setWorkPicPath(String workPicPath) {
        this.workPicPath = workPicPath;
    }

    //构造方法

    public Smbms_User() {
    }

    public Smbms_User(Integer id, String userCode, String userName, String userPassword, Integer gender, Date birthday, String phone, String address, Integer userRole, Integer createdBy, Date creationDate, Integer modifyBy, Date modifyDate, String idPicPath, String workPicPath) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.phone = phone;
        this.address = address;
        this.userRole = userRole;
        this.createdBy = createdBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
        this.idPicPath = idPicPath;
        this.workPicPath = workPicPath;
    }


}

package com.bdqn.pojo;

import java.util.Date;

/**
 * @author Guo
 * @create 2021-12-23 14:29
 * 角色表单实体类
 */
public class Smbms_Role {
    //私有化创建表格属性
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;

    //封装方法
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public Integer getCreateBy() {
        return createBy;
    }

    public void setCreateBy(Integer createBy) {
        this.createBy = createBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }

    //构造方法
    public Smbms_Role() {
    }

    public Smbms_Role(Integer id, String roleCode, String roleName, Integer createBy, Date creationDate, Integer modifyBy, Date modifyDate) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createBy = createBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }

    //重写tostring
    @Override
    public String toString() {
        return "Smbms_Role{" +
                "id=" + id +
                ", roleCode='" + roleCode + '\'' +
                ", roleName='" + roleName + '\'' +
                ", createBy=" + createBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                '}';
    }
}

2.创建实体类功能接口

package com.bdqn.dao;

import com.bdqn.pojo.Smbms_User;
import org.apache.ibatis.annotations.Param;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * @author Guo
 * @create 2021-12-23 18:38
 */
public interface UserMapper {
    //使用关联映射执行多表连接查询
    List<Smbms_User> selectAllJoinByMap();
}

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.bdqn.dao.UserMapper">

    <!--编写用户表和角色表的  map映射管理-->
    <resultMap id="userRoleMap" type="user">
        <!--column列名  对应数据库字段名  property属性  对应实体类中的属性名-->
        <!--如果主配置文件中的设置项设置了FULL属性并且实体类中创建的属性名和数据库中
        字段名一致则可以省略map映射中字段和属性的映射-->
        <!--id指的是主键-->
        <id property="id" column="id"></id>
        <!--result 对应的是数据库中除主键以外的字段-->
        <result property="userName" column="userName"></result>
        <!--关联映射    association-->
        <association property="role" javaType="com.bdqn.pojo.Smbms_Role">
            <id property="id" column="id"></id>
            <result property="roleName" column="roleName"></result>
        </association>
    </resultMap>
    <!--配置查询的sql语句-->
    
    <!--使用关联映射执行多表查询-->
    <select id="selectAllJoinByMap" resultMap="userRoleMap">
        SELECT r.roleName, u.*
        FROM smbms_user AS u
                 INNER JOIN smbms_role AS r
                            ON u.userRole = r.id;
    </select>
</mapper>

4.在资源文件中配置映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--根元素-->
<configuration>
    <!--引入database.properties配置文件-->
    <properties resource="database.properties"></properties>

    <!--设置项-->
    <settings>
        <!--配置打印日志的方式-->
        <setting name="logImpl" value="log4j"/>
        <!--设置关联映射的级别   FULL全部关联-->
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>
    <!--起别名-->
    <typeAliases>
        <!--第一种方法设置包名后面映射的命名就为实体类的类名-->
        <!--<package name="com.bdqn.pojo"/>-->
        <!--第二种方法给对应的实体类起名  type"需要起名的实体类" alias"起的名字"-->
        <typeAlias type="com.bdqn.pojo.Smbms_User" alias="user"></typeAlias>
    </typeAliases>
    <!--开发环境  讲白了就是一套连接数据库的配置   里面可以配置多套开发环境-->
    <environments default="development">
        <!--开发板环境-->
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--获取所有需要映射的映射文件  对象关系映射的配置文件-->
    <mappers>
        <mapper resource="com/bdqn/dao/Smbms_UserMapper.xml"/>
        <mapper resource="com/bdqn/dao/Smbms_BillMapper.xml"/>
        <mapper resource="com/bdqn/dao/Smbms_Address.xml"/>
    </mappers>
</configuration>