Mybatis(多表联合，动态语句）

最新推荐文章于 2024-06-04 10:54:49 发布

weixin_73667562

最新推荐文章于 2024-06-04 10:54:49 发布

阅读量453

点赞数 1

文章标签： mybatis java intellij-idea

本文链接：https://blog.csdn.net/weixin_73667562/article/details/129673520

版权

文章详细介绍了如何在MyBatis框架下处理一对多和一对一的关系映射。通过实体类、接口、Mapper.xml配置文件以及测试类的代码示例，展示了如何在Dept和Emp类中实现部门与员工的一对多关系，以及Emp类中员工与部门的一对一关系。同时，提到了动态SQL的if、foreach、where和set标签的用法。

摘要由CSDN通过智能技术生成

一、一对多处理

测试环境搭建：（一名部门有多个员工）
1.建实体类

Dept类

package com.zhy.bean;

import java.util.List;

public class Dept {
    private Integer did;
    private String dname;
    private String dinfo;

    private List<Emp> emps;

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDinfo() {
        return dinfo;
    }

    public void setDinfo(String dinfo) {
        this.dinfo = dinfo;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", dinfo='" + dinfo + '\'' +
                ", emps=" + emps +
                '}';
    }
}

Emp 类

package com.zhy.bean;

public class Emp {
    private Integer eid;
    private String ename;
    private String job;
    private Integer sal;
    private String phone;
    private String address;
    private Integer did;

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getSal() {
        return sal;
    }

    public void setSal(Integer sal) {
        this.sal = sal;
    }

    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 getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", sal=" + sal +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", did=" + did +
                '}';
    }
}

2.建立接口

package com.zhy.dao;

import com.zhy.bean.Dept;

public interface DeptDao {

    Dept selectbyid(int did);
}

package com.zhy.dao;

import com.zhy.bean.Emp;

import java.util.List;

public interface EmpDao {
    Emp select(int eid);

    List<Emp> selectbyid(int did);
}

3. 建Mapper.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.zhy.dao.DeptDao">
    <!-- 定义手动映射关系：数据库中字段 和 类的属性 的映射-->
    <resultMap id="detpMap" type="com.zhy.bean.Dept">
        <id column="did" property="did"/>
        <result column="dname" property="dname"/>
        <result column="dinfo" property="dinfo"/>
        <!-- 一对多的映射配置collection：自动映射配置dept中 emp对象中的属性和表字段的对应关系-->
        <collection column="did" select="com.zhy.dao.EmpDao.selectbyid" property="emps">
        </collection>

    </resultMap>
    <select id="selectbyid" parameterType="int" resultMap="detpMap">
         select * from dept where did=#{did}
    </select>

</mapper>

<?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.zhy.dao.EmpDao">

<select id="select" parameterType="int" resultType="emp">
 select  * from emp  where eid=#{eid}
</select>

    <select id="selectbyid" parameterType="int" resultType="emp">
 select  * from emp  where did=#{did}
</select>
</mapper>

4.在核心配置文件中绑定注册Mapper文件或者Mapper.xml文件

5.创建测试类

package com.zhy.test;

import com.zhy.bean.Dept;
import com.zhy.dao.DeptDao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class DeptTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    DeptDao deptDao =null;

    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory= builder.build(stream);
        sqlSession=factory.openSession();
        deptDao=sqlSession.getMapper(DeptDao.class);
    }

    @Test
    public void findtest(){
        Dept dept = deptDao.selectbyid(102);
        System.out.println(dept);
    }

    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

6.运行结果

二、一对一处理

建实体类

package com.zhy.bean;

public class Dept {
    private Integer did;
    private String dname;
    private String dinfo;

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }


    @Override
    public String toString() {
        return "Dept{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", dinfo='" + dinfo + '\'' +
                '}';
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDinfo() {
        return dinfo;
    }

    public void setDinfo(String dinfo) {
        this.dinfo = dinfo;
    }
}

package com.zhy.bean;

public class Emp {
    private Integer eid;
    private String ename;
    private String job;
    private Integer sal;
    private String phone;
    private String address;
    private Dept dept;

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getSal() {
        return sal;
    }

    public void setSal(Integer sal) {
        this.sal = sal;
    }

    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 Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", sal=" + sal +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", dept=" + dept +
                '}';
    }
}

创建接口

package com.zhy.dao;

import com.zhy.bean.Dept;

public interface DeptDao {

    Dept selectbyid(int did);
}

package com.zhy.dao;

import com.zhy.bean.Emp;

import java.util.List;

public interface EmpDao {
    Emp select(int eid);
}

建Mapper.xml文件

JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是映射到list集合属性中pojo的类型。

collection、association标签是用于体现关联的。如一个实体与另一个实体之间是一对多的关系，那么在一的一方使用 collection 标签，对应多的一方的一个集合，在多的一方使用association标签对应一的一方的一个实体

<?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.zhy.dao.EmpDao">
 <!-- 定义手动映射关系：数据库中字段 和 类的属性 的映射-->
    <resultMap id="EmpMap" type="com.zhy.bean.Emp">
        <id column="eid" property="eid"/>
        <result column="ename" property="ename"/>
        <result column="job" property="job"/>
        <result column="sal" property="sal"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
    <!-- 一对一的映射配置association：自动映射配置emp中 dept对象中的属性和表字段的对应关系-->
        <association column="did" select="com.zhy.dao.DeptDao.selectbyid" property="dept">
        </association>

    </resultMap>
<select id="select" parameterType="int" resultMap="EmpMap">
 select  * from emp  where eid=#{eid}
</select>
</mapper>

<?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.zhy.dao.DeptDao">
    <select id="selectbyid" parameterType="int" resultType="dept">
         select * from dept where did=#{did}
    </select>
</mapper>

创建测试类

package com.zhy.test;


import com.zhy.bean.Emp;
import com.zhy.dao.EmpDao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;


public class EmpTest {
    InputStream stream=null;
    SqlSessionFactoryBuilder builder=null;
    SqlSessionFactory factory=null;
    SqlSession sqlSession=null;
    EmpDao empDao =null;

    @Before
    public void init() throws IOException {
        stream= Resources.getResourceAsStream("mybatis.xml");
        builder=new SqlSessionFactoryBuilder();
        factory= builder.build(stream);
        sqlSession=factory.openSession();
        empDao=sqlSession.getMapper(EmpDao.class);
    }

    @Test
    public void findtest(){
        Emp emp = empDao.select(3);
        System.out.println(emp);
    }

    @After
    public void distroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }

}

运行结果

总结：
关联 association 【一对一】
集合 collection 【一对多】
JavaType ofType 的区别
JavaType 用于指定实体类中属性的类型
ofType 用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点：

保证SQL的可读性，尽量保证通俗易懂
注意一对多和多对一，属性名和字段的问题
如果问题不好排查错误，可以使用日志，建议使用Log4j

三、动态sql

1.if 标签
if标签通常用于WHERE语句、UPDATE语句、INSERT语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

<if test="name != null and name != ''">
and NAME = #{name}
</if>

2.foreach 标签

foreach标签主要用于构建in条件，可在sql中对集合进行迭代。也常用到批量删除、添加等操作中。

<delete id="del" parameterType="delov">
        delete from emp where eid in
<!--foreach标签可以对一个集合变量进行迭代   常用于批量删除
    collection：集合变量  item：临时变量  open：以什么符号开始   close：以什么符号结束  separator：遍历出来的数据的分隔符
     #{id} 放入标签中，实现了迭代元素并连接上了字符-->
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

3. where标签
这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。
此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

  <select id="seach" parameterType="user" resultType="user">
        select * from emp
        <where>
            <if test="ename !=null and ename!=''">
                and  ename like concat('%',#{ename},'%')
            </if>
            <if test="address!=null and address!=''">
                and address like concat('%',#{address},'%')
            </if>
        </where>

    </select>

4. set 标签
当在update语句中使用if标签时,使用set标签可以将动态的配置set关键字，和剔除追
加到条件末尾的任何不相关的逗号。

  <update id="update" parameterType="user">
        update  emp
        <set>
            <if test="ename !=null and ename!=''">
                ename=#{ename},
            </if>
            <if test="dept !=null and dept!=''">
                dept=#{dept},
            </if>
            <if test="job !=null and job!=''">
                job=#{job},
            </if>
            <if test="sal !=null and sal!=''">
                sal=#{sal},
            </if>
            <if test="phone !=null and phone!=''">
                phone=#{phone},
            </if>
            <if test="address!=null and address!=''">
                address=#{address},
            </if>
        </set>
          where eid=#{eid}
    </update>