MyBatis框架3

最新推荐文章于 2023-06-28 22:32:36 发布

winkooxx

最新推荐文章于 2023-06-28 22:32:36 发布

阅读量77

点赞数

分类专栏：笔记文章标签： mybatis

本文链接：https://blog.csdn.net/Dhxy1030/article/details/115609469

版权

笔记专栏收录该内容

44 篇文章 1 订阅

订阅专栏

MyBatis框架3

MyBatis多表查询之一对多与一对一

需求：
查询所有账户信息，关联查询下的用户信息。

方式一：定义账户信息的实体类Account

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

定义接收查询结果的实体类AccountUser，因为关联用户信息所以继承Account类

public class AccountUser extends Account {
    private String username;
    private String address;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return super.toString() + " AccountUser [username=" + username + "address=" + address + "]";
    }
}

定义账户的持久层 Dao接口，返回AccountUser对象

public interface IAccountDao {
    List<AccountUser> findAllAccount();
}

配置账户Dao接口的映射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="cn.xiong.dao.IAccountDao">
        <!-- 配置查询所有操作-->
        <select id="findAllAccount" resultType="cn.xiong.domain.AccountUser">
            select a.*,u.username,u.address from account a,user u where a.uid =u.id;
        </select>
</mapper>

创建账户测试类

private InputStream in;
private SqlSession sqlSession;
private IAccountDao iAccountDao;

@Before//用于在测试方法执行之前执行
public void init()throws Exception{
    //1.读取配置文件，生成字节输入流
    in = Resources.getResourceAsStream("SqlMapConfig.xml");
    //2.获取SqlSessionFactory
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
    //3.获取SqlSession对象
    sqlSession = factory.openSession(true);
    //4.获取dao的代理对象
    iAccountDao = sqlSession.getMapper(IAccountDao.class);
}

@After//用于在测试方法执行之后执行
public void destroy()throws Exception{
    //提交事务
    // sqlSession.commit();
    //6.释放资源
    sqlSession.close();
    in.close();
}
@Test
public void testFindAllAccount(){
    List<AccountUser> accounts =  iAccountDao.findAllAccount();
    for (AccountUser account : accounts) {
        System.out.println(account);
    }

}

小结：定义专门的 po 类作为输出类型，其中定义了 sql 查询结果集所有的字段。此方法较为简单，企业中使用普遍。

方式二：使用 resultMap，定义专门的 resultMap 用于映射一对一查询结果

定义账户信息的实体类在其中加入User类对象

public class Account implements Serializable {

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

    //从表实体应该包含一个主表实体的对象引用
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.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;
    }

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

定义AccountDao接口，返回Account对象

public interface IAccountDao {
    List<Account> findAll();
}

配置账户Dao接口的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="cn.xiong.dao.IAccountDao">
    <resultMap id="accountUserMap" type="cn.xiong.domain.Account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- 一对一的关系映射：配置封装user的内容-->
        <association property="user" column="uid" javaType="cn.xiong.domain.User">
            <id property="id" column="id"></id>
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>
    <!-- 配置查询所有操作-->
    <select id="findAll" resultMap="accountUserMap">
          select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
    </select>

</mapper>

创建测试类

@Test
public void testFindAll(){
    List<Account> accounts =  iAccountDao.findAll();
    for (Account account : accounts) {
        System.out.println(account);
        System.out.println(account.getUser().toString());
    }
}

MyBatis多表查询之多对多

需求：
实现查询所有角色对象并且加载它所分配的用户信息。

分析：
查询角色我们需要用到Role表，但角色分配的用户的信息我们并不能直接找到用户信息，而是要通过中间表(USER_ROLE 表)才能关联到用户信息。

1.编写角色实体类，并关联用户集合

public class Role implements Serializable {

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }

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

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

    private List<User> users;
    private Integer roleId;

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

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


    private String roleName;

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    private String roleDesc;
}

2.定义IRoleDao持久层接口

public interface IRoleDao {
    List<Role> findAll();
}

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="cn.xiong.dao.IRoleDao">
    <resultMap id="roleMap" type="cn.xiong.domain.Role">
        <id property="roleId" column="RID"></id>
        <result property="roleName" column="ROLE_NAME"></result>
        <result property="roleDesc" column="ROLE_DESC"></result>
        <collection property="users" ofType="cn.xiong.domain.User">
           <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="address" property="address"></result>
            <result column="sex" property="sex"></result>
            <result column="birthday" property="birthday"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="roleMap">
        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 user u on u.id = ur.uid
    </select>
</mapper>

}
<!--        比如同时有A.java和B.java两个类，A.java如下:-->
<!--        public class A{-->
<!--        private B b1;-->
<!--        private List<B> b2;-->
<!--在映射b1属性时用association标签, 映射b2时用collection标签，分别是一对一，一对多的关系-->

4.实现类

private InputStream in;
private SqlSession sqlSession;
private IRoleDao roleDao;
//多对多的关系映射
private List<User> users;

@Before//用于在测试方法执行之前执行
public void init() throws Exception {
    //1.读取配置文件，生成字节输入流
    in = Resources.getResourceAsStream("SqlMapConfig.xml");
    //2.获取SqlSessionFactory
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
    //3.获取SqlSession对象
    sqlSession = factory.openSession(true);
    //4.获取dao的代理对象
    roleDao = sqlSession.getMapper(IRoleDao.class);
}

@After//用于在测试方法执行之后执行
public void destroy() throws Exception {
    //提交事务
    // sqlSession.commit();
    //6.释放资源
    sqlSession.close();
    in.close();
}

/**
 * 测试查询所有
 */
@Test
public void testFindAll() {
    List<Role> roles = roleDao.findAll();
    for (Role role : roles) {
        System.out.println(role);
        System.out.println(role.getUsers());
    }
}

winkooxx

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis框架3

MyBatis框架3MyBatis多表查询之一对多与一对一需求：查询所有账户信息，关联查询下的用户信息。方式一：定义账户信息的实体类Accountpublic class Account implements Serializable { private Integer id; private Integer uid; private Double money; public Integer getId() { return id;
复制链接

扫一扫