MyBatis的关联查询

发布时间:2023年12月25日

MyBatis的关联查询

  • (1)项目不可能只有一个表,一般是多表
  • (2)多表关系为,一对一,一对多,多对多
  • (3)查询的数据来自多个表,要使用多表查询
    笛卡尔集,显示内连接inner join,左外连接left outer join,右外连接right outer join,子查询select 嵌套select
  • (4)查询的结果要封装成javaBean对象 ,在Mybatis中重点掌握resultMap

关联查询三种方法:

  1. 一对一查询

(1)关联查询的中的一对一就是指 一个账号存在一个用户
(2)数据来自两个表,使用连接查询,需要输出所有信息,使用左外连接

  • association 标签 用于 成员变量的类型为自定义实体类型
 <resultMap id="" type="">
            ....
            <association property="user" javaType="com.by.pojo.User">
                ....
            </association>
        </resultMap>

案例:一个账号对应一个用户

  • (1) 先写一个User对象和Account对象:写在pojo包
public class Account {
    private Integer id;
    private Integer uid;
    private Double money;

    //对应一个 用户
    private User user;

    public Integer getId() {
        return id;
    }

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

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}
public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}
  • (2) 在创建接口是实现方法 和sql结果映射 并使用association标签
public interface AccountDao {
   /**
    * 一对一
    * @param id
    * @return
    */
   Account getAccountById(Integer id);
}
<?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.by.dao.AccountDao">
    <resultMap id="getAccountByIdResultMap" type="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
        <!--
               一对一查询使用association标签
               property="user" :Account的属性
               javaType="user" :等价于resultType
               -->
        <association property="user" javaType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>
<select id="getAccountById" parameterType="int" resultMap="getAccountByIdResultMap" >
    select a.id as aid,a.uid,a.money,u.* from account a left join user u on a.uid=u.id where a.id=#{id}
</select>
  • (3)最后测试
public class MyBatisTest {

    private InputStream inputStream;
    private SqlSession sqlSession;

    @Before
    public void info() throws IOException {
        // 加载配置文件
        String resource = "mybatis-config.xml";
        inputStream = Resources.getResourceAsStream(resource);

        // 创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);
        //获得数据库会话实例
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void testFindAll() {
        AccountDao accountDao=sqlSession.getMapper(AccountDao.class);
        Account account=accountDao.getAccountById(1);
        System.out.println(account);
    }
     @After
    public void close() throws IOException {
    	//关闭资源
        sqlSession.close();
        inputStream.close();
    }

}
  1. 一对多查询:
  • (1)关联查询的中的一对多是指,一个用户可以拥有多个账号
  • (2)数据来自两个表,使用连接查询,需要输出每一个用户有多少个账号
  • (3)查询结果使用collection标签 映射List<元素>
<resultMap id="" type="">
            ....
            <collection property="user" ofType="com.by.pojo.Account">
                ....
            </collection>
        </resultMap>
  1. 多对多查询
        <resultMap id="" type="">
            ....
            <collection property="user" ofType="com.by.pojo.Role">
                ....
            </collection>
        </resultMap>
<?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">
<!--namespace:隔离sql,一般是接口名称的全类名-->
<mapper namespace="com.by.dao.RoleDao">
    <resultMap id="getRoleByIdResult" type="role">
        <id column="rid" property="id"></id>

        <result column="role_name" property="roleName"></result>
        <result column="role_desc" property="roleDesc"></result>
        <collection property="users" ofType="user">
            <id column="id" property="id"></id>

            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </collection>
    </resultMap>

    <select id="getRoleById" resultMap="getRoleByIdResult">
        SELECT
            r.id as rid,r.role_name,r.role_desc,
            u.*
        FROM
            user_role ur
                JOIN role r ON ur.rid=r.id
                JOIN user u ON ur.uid=u.id
        WHERE
            r.id=#{id}
    </select>
</mapper>
文章来源:https://blog.csdn.net/w2144217940/article/details/135160187
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。