美文网首页
myabtis多表查询

myabtis多表查询

作者: dillqq | 来源:发表于2019-11-04 23:06 被阅读0次

SQL语句查询一个数据表所有字段的类型：desc [table name]

表之间的关系

一对多
一对一
多对一
多对多

mybatis多表查询

步骤：
1.建立两个表：用户表，和账户表（一个用户对应多个账户）

2.对应两个表的实体类，体现多对一和一对一

public class account implements Serializable {

private Integer id;
private Integer uid;
private Double money;

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;
}

@Override
public String toString() {
    return "account{" +
            "id=" + id +
            ", uid=" + uid +
            ", money=" + money +
            '}';
}
}

用户实体类

public class User  implements Serializable {

private Integer id;
private String username;
private Integer age;

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 Integer getAge() {
    return age;
}

public void setAge(Integer age) {
    this.age = age;
}

@Override
public String toString() {
    return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", age=" + age +
            '}';
}

}

将用户对象封装到类中

public class AccountAndUser implements Serializable {

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 "AccountAndUser{" +
            "id=" + id +
            ", uid=" + uid +
            ", money=" + money +
            ", user=" + user +
            '}';
}
}

使用的方法结合用户和账号信息

  public class AccountUser extends account  implements Serializable {
private String username;
private Integer age;

public String getUsername() {
    return username;
}

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

public Integer getAge() {
    return age;
}

public void setAge(Integer age) {
    this.age = age;
}

@Override
public String toString() {
    return super.toString()+"AccountUser{" +
            "username='" + username + '\'' +
            ", age=" + age +
            '}';
}
}

3.建立两个对应的配置文件

<?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>

<properties resource="db.properties">
</properties>
<environments default="mysql">
    <environment id="mysql">
        <transactionManager type="jdbc"></transactionManager>
        <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/dao/IAcount.xml"></mapper>
</mappers>
</configuration>

4.实现配置文件：查询用户时得到账户信息，查询账户时得到用户信息

<?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.Dao.IAccount">
<!--定义将user封装到对象中-->
<resultMap id="accountMapUser" type="com.User.AccountAndUser">

        <id property="id" column="id"></id>
    <  result property="uid" column="uid"/>
    <result property="money" column="money"/>
    <!--建立一对一的关系映射 ，配置封装user-->
    <association property="user" column="uid" javaType="com.User.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="age" column="age"/>
    </association>
</resultMap>
  <!--    SELECT u.*, a.money FROM user1 u , account a where a.uid =           u.id-->
    <!--   使用上面创建的rsultMap建立封装的对象这个语句和下面的语句都能实现一样的效果accountMapU serselect * from account-->
<select id="findUserAndAccount" resultMap="accountMapUser">
    SELECT u.*, a.money FROM user1 u , account a where a.uid = u.id
</select>



   <select id="findAll" resultType="com.User.account">
   select * from account
 </select>
<!--查询所用用户名称及账户-->
<select id="findAllAccount" resultType="com.User.AccountUser">
   SELECT u.*, a.money FROM user1 u , account a where a.uid = u.id
 </select>
</mapper>

5测试类

/**
 * @Author: zheng
 * @Data :2019/11/4  19:51
 */
public class AccountTest {
IAccount iDao;
SqlSession sqlSession;
InputStream in;

@Before
public void init() throws IOException {
    in = Resources.getResourceAsStream("SqlConfigMap.xml");
    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
    sqlSession = sqlSessionFactory.openSession();
    iDao = sqlSession.getMapper(IAccount.class);
}

@After
public void destroy() throws IOException {
    sqlSession.close();
    in.close();


}

@Test
public void testAccount() {
    List<account> list = iDao.findAll();
    for (account a : list) {
        System.out.println(a);
    }

}


/**
 * 查询所用用户名称及账户
 */

@Test
public void testAccountUser() {
    List<AccountUser> list = iDao.findAllAccount();
    for (AccountUser a : list) {
        System.out.println(a);
    }

}


@Test
public void testAccountAndUser() {
    List<AccountAndUser> list = iDao.findUserAndAccount();
    for (AccountAndUser a : list) {
        System.out.println(a);
    }

}
}

多对多

SELECT u.*, r.id as rid ,r.role_name,r.role_desc
from role r 
LEFT OUTER JOIN user_role ur ON r.id = ur.rid
LEFT OUTER JOIN user1 u ON u.id = ur.uid

实例 ： 用户和角色
1.建立两个表：账户表，角色表（一个用户对应多个账户） 需要使用中间表包含各自的主键
中间表

CREATE TABLE user_role
(
uid int NOT NULL,
rid int NOT NULL,
PRIMARY KEY (uid, rid),
FOREIGN KEY (uid) REFERENCES role(id),
FOREIGN KEY (uid) REFERENCES user1(id)

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

2.对应两个表的实体类，体现多对多，各自包含对方的对象引用

public class User implements Serializable {

private Integer id;
private String username;
private Integer age;

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 Integer getAge() {
    return age;
}

public void setAge(Integer age) {
    this.age = age;
}

@Override
public String toString() {
    return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", age=" + age +
            '}';
}

}
角色类

public class Role implements Serializable {
//    一个角色可以赋予多个用户
private Integer id;
private String role_name;
private String role_desc;



public Integer getId() {
    return id;
}

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

public String getRole_name() {
    return role_name;
}

public void setRole_name(String role_name) {
    this.role_name = role_name;
}

public String getRole_desc() {
    return role_desc;
}

public void setRole_desc(String role_desc) {
    this.role_desc = role_desc;
}

@Override
public String toString() {
    return "Role{" +
            "id=" + id +
            ", role_name='" + role_name + '\'' +
            ", role_desc='" + role_desc + '\'' +
            '}';
}

}

结果集查看

public class UserAndRole {

//    一个角色可以赋予多个用户
private Integer id;
private String role_name;
private String role_desc;
private List<User> users;

public List<User> getUsers() {
    return users;
}

public void setUsers(List<User> users) {
    this.users = users;
}

public Integer getId() {
    return id;
}

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

public String getRole_name() {
    return role_name;
}

public void setRole_name(String role_name) {
    this.role_name = role_name;
}

public String getRole_desc() {
    return role_desc;
}

public void setRole_desc(String role_desc) {
    this.role_desc = role_desc;
}

@Override
public String toString() {
    return "Role{" +
            "id=" + id +
            ", role_name='" + role_name + '\'' +
            ", role_desc='" + role_desc + '\'' +
            ", users=" + users +
            '}';
}

}

3.建立两个对应的配置文件

映射文件

  <?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.Dao.IRoleDao">


<resultMap id="roleMap" type="com.User.Role">
    <id property="id" column="id" />
    <result property="role_name" column="role_name"/>
    <result property="role_desc" column="role_desc"/>
</resultMap>



<resultMap id="roleMap1" type="com.User.UserAndRole">
    <id property="id" column="rid" />
    <result property="role_name" column="role_name"/>
    <result property="role_desc" column="role_desc"/>
    <collection property="users" ofType="com.User.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="age" column="age"/>
    </collection>
</resultMap>



<select id="findRole" resultMap="roleMap">
    select  * from role
</select>

<select id="findRoleAndUser" resultMap="roleMap1">
    SELECT u.*, r.id as rid ,r.role_name,r.role_desc 
    from role r 
    LEFT OUTER JOIN user_role ur ON r.id = ur.rid 
    LEFT OUTER JOIN user1 u ON u.id = ur.uid 
</select>
</mapper>

4.实现配置文件：查询用户时得到角色信息，
查询角色时得到用户信息

  public interface IRoleDao {
/**
 * 查询所有角色
 */
List<Role> findRole();
/**
 * 查询所有角色和关联的用户
 */
List<UserAndRole> findRoleAndUser();
}

5.测试类

public class RoleTest {

IRoleDao iDao;
SqlSession sqlSession;
InputStream in;


@Before
public void init() throws IOException {
    in = Resources.getResourceAsStream("SqlConfigMap.xml");
    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
    sqlSession = sqlSessionFactory.openSession();
    iDao = sqlSession.getMapper(IRoleDao.class);
}

@After
public void destroy() throws IOException {
    sqlSession.close();
    in.close();
}

@Test
public  void testRole()
{
    List<Role> list = iDao.findRole();
    for (Role a : list) {
        System.out.println(a);
}
}

@Test
public  void testRoleAndUser()
{
    List<UserAndRole> list = iDao.findRoleAndUser();
    for (UserAndRole a : list) {
        System.out.println(a);
    }
}

从用户查询角色

首先需要将
SQL语句改成

SELECT u.*, r.id as rid ,r.role_name,r.role_desc
from user1 u
LEFT OUTER JOIN user_role ur ON u.id = ur.rid
LEFT OUTER JOIN role r ON r.id = ur.uid

反过来查询

然后新建一个类继承User 封装角色也就是Role

public class RoleAndUser extends User {
private List<Role> list;
public List<Role> getList() {
    return list;
}

public void setList(List<Role> list) {
    this.list = list;
}

@Override
public String toString() {
    return super.toString()+"RoleAndUser{" +
            "list=" + list +
            '}';
}
}

修改映射文件

<resultMap id="roleMap2" type="com.User.RoleAndUser">
    <id property="id" column="id" />
    <result property="username" column="username"/>
    <result property="age" column="age"/>
    <collection property="list" ofType="com.User.Role">
        <id property="id" column="rid"/>
        <result property="role_name" column="role_name"/>
        <result property="role_desc" column="role_desc"/>
    </collection>
</resultMap>





  <select id="findRoleAnd" resultMap="roleMap2">
  SELECT u.*, r.id as rid ,r.role_name,r.role_desc
from user1 u
  LEFT OUTER JOIN user_role ur ON u.id = ur.rid
LEFT OUTER JOIN role r ON r.id = ur.uid



</select>

测试方法

  @Test
public  void testRoleAnd()
{
    List<RoleAndUser> list = iDao.findRoleAnd();
    for (RoleAndUser a : list) {
        System.out.println(a);
    }
}

相关文章

  • myabtis多表查询

    SQL语句查询一个数据表所有字段的类型：desc [table name] 表之间的关系 一对多一对一多对一多对多...

  • SQLAlchemy（四）

    知识要点： 1.多表查询 2.原生SQL的查询 多表查询 在MySQL中我们讲了多表查询，在SQLAlchemy中...

  • python面试题01

    1、什么是多表关联查询，有几种多表关联的查询方式，分别是什么？ 多表关联查询概念： 多表关联查询分类：1.1内连接...

  • 数据库基本操作3.0

    今日内容 多表查询 \\ 事务DCL 多表查询： 事务 DCL：

  • MySql : 三、 多表查询和事务

    前言 本篇主要介绍了数据库中多表查询以及事务相关的知识。 目录 一、多表查询二、子查询三、事务 一、多表查询 1....

  • Oracle详解（Ⅱ）:世界上目前已知最好的关系型数据库

    多表查询 多表连接基本查询 使用一张以上的表做查询就是多表查询 这样会出现的结果就是：笛卡儿积连接查询的时候一般在...

  • spring-data-jpa 复杂查询：使用

    单表查询 多表查询

  • 4.MySQL多表&事务

    主要内容 1 . 多表查询2 . 事务3 . DCL 多表查询： 事务 DCL：

  • SQL语句常用命令整理---多表查询

    多表查詢之关连查询 多表数据连接查询，简称连接查询。本篇我们来一同学习多表连接查询的相关用法，主要內容有： 内连接...

  • sql多表查询

    普通多表查询 嵌套多表查询 链接多表查询 左链接（会将左表的内容全部输出，没有需要补NULL） 右链接（会将右表的...

网友评论

      本文标题：myabtis多表查询

      本文链接：https://www.haomeiwen.com/subject/tfadbctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|myabtis多表查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！