mybatis多表联查

已于 2023-03-20 18:57:02 修改
阅读量153 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： mybatis mysql java
于 2023-03-17 20:00:17 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/VickyGod/article/details/129627329

多表联查一对一

mysql代码

create table dept(
    did int primary key,
    dname varchar(20),
    dinfo varchar(50)
);

insert into dept values(101,'市场部','负责公司市场的开拓');
insert into dept values(102,'产品部','负责公司产品的营销');
insert into dept values(103,'研发部','负责公司产品的研发');
insert into dept values(104,'测试部','负责公司产品的测试');

select * from dept;


#员工表
drop table if exists emp;
create table emp(
    eid int primary key auto_increment,
    ename varchar(20),
    job varchar(20),
    sal double,
    phone varchar(11),
    address varchar(50),
    did int
);


insert into emp(ename,job,sal,phone,address,did) values('赵德保','市场专员',4600,'15785642139','信阳',101);
insert into emp(ename,job,sal,phone,address,did) values('刘瑞琳','市场经理',7600,'17956248563','商丘',101);
insert into emp(ename,job,sal,phone,address,did) values('张翠萍','产品设计师',5800,'15821563548','郑州',102);
insert into emp(ename,job,sal,phone,address,did) values('刘长河','产品经理',9800,'15821563548','洛阳',102);
insert into emp(ename,job,sal,phone,address,did) values('李耀菲','开发工程师',8800,'13658942168','许昌',103);
insert into emp(ename,job,sal,phone,address,did) values('王长林','项目经理',15800,'13954865721','南阳',103);
insert into emp(ename,job,sal,phone,address,did) values('陈清泰','测试工程师',5300,'13785463249','周口',104);
insert into emp(ename,job,sal,phone,address,did) values('杨国华','测试经理',9800,'13785463249','驻马店',104);

select * from emp;

结构

Dept实体类

package com.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;
    }

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

    public String getDinfo() {
        return dinfo;
    }

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

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

Emp实体类

package com.bean;

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

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.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 Double getSal() {
        return sal;
    }

    public void setSal(Double 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;
    }

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

DeptDao包

package com.dao;

import com.bean.Dept;



public interface DeptDao {
Dept selectByid(int did);
}

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

EmpDao包

package com.dao;

import com.bean.Emp;

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

EmpDao.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.dao.EmpDao">
    <resultMap id="empMap" type="com.bean.Emp">
<id property="eid" column="eid"/>
        <result property="ename" column="ename"/>
        <result property="job" column="job"/>
        <result property="sal" column="sal"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <association property="dept" select="com.dao.DeptDao.selectByid" column="did"/>


    </resultMap>


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

TestDept

package com.test;

import com.bean.Dept;
import com.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 TestDept {
    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 selectByid(){
        Dept dept=deptDao.selectByid(102);
        System.out.println(dept);
    }
    @After
    public void tisfory() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        stream.close();
    }
}

TestEmp

package com.test;




import com.bean.Dept;
import com.bean.Emp;
import com.dao.DeptDao;
import com.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 TestEmp {

        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 textselectByeid(){
            Emp emp = empDao.selectByeid(1);
            System.out.println(emp);
        }
        @After
        public void tisfory() throws IOException {
            sqlSession.commit();
            sqlSession.close();
            stream.close();
        }
    }

mybatis.xml

<?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="jdbc.properties">

    </properties>

<!--项目配置信息-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <!--        加载com.bean.Student包中的所有类，给类默认起名别为类的名字但首字母小写-->
<typeAliases>
    <package name="com.bean"/>
</typeAliases>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">

               <property name="driver" value="${jdbc.driver}"/>
               <property name="url" value="${jdbc.url}"/>
               <property name="username" value="${jdbc.user}"/>
               <property name="password" value="${jdbc.password}"/>

            </dataSource>
        </environment>
    </environments>
<mappers>
    <package name="com.dao"/>
</mappers>
</configuration>

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mydb
jdbc.user=root
jdbc.password=root

log4j.properties

log4j.rootLogger=TRACE,stdout  

log4j.appender.stdout=org.apache.log4j.ConsoleAppender   
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout   


log4j.appender.logfile=org.apache.log4j.FileAppender   
log4j.appender.logfile.File=wocao.log   
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout   
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n   
log4j.logger.mapperNS =TRACE

log4j.logger.com.mybatis=DEBUG  
log4j.logger.com.mybatis.common.jdbc.SimpleDataSource=DEBUG   
log4j.logger.com.mybatis.common.jdbc.ScriptRunner=DEBUG   
log4j.logger.com.mybatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG   
log4j.logger.java.sql.Connection=DEBUG  
log4j.logger.java.sql.Statement=DEBUG  
log4j.logger.java.sql.PreparedStatement=DEBUG  
log4j.logger.java.sql.ResultSet=DEBUG  

log4j.logger.org.springframework=error 
log4j.logger.org.apache=ERROR  
log4j.logger.org.mybatis=DEBUG

多表联查一对多

Dept实体类

package com.bean;

import java.util.List;

public class Dept {
    private Integer did;
    private String dname;
    private String dinfo;
    //一个部门包含多个员工，一对多关系就是定义集合
    List<Emp> emps;

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

    public void setEmps(List<Emp> emps) {
        this.emps = 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;
    }

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

Emp实体类

package com.bean;

public class Emp {
    private Integer eid;
    private String ename;
    private String job;
    private Double 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 Double getSal() {
        return sal;
    }

    public void setSal(Double 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 +
                '}';
    }
}

DeptDao.java

package com.dao;

import com.bean.Dept;

public interface DeptDao {
    Dept selectByDid(int did);
}

DeptDao.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.chen.dao.DeptDao">
    <resultMap id="deptMap" type="com.chen.bean.Dept">
        <id property="did" column="did" />
        <result property="dname" column="dname"/>
        <result property="dinfo" column="dinfo"/>
        <!--配置一对多关系：自动根据did去关联查询emp表中该did的信息-->
        <collection column="did" select="com.chen.dao.EmpDao.selectByDid" property="emps">
        </collection>
    </resultMap>

    <select id="selectByDid" parameterType="int" resultMap="deptMap">
        select * from dept where did=#{did};
    </select>
</mapper>

EmpDao.java

package com.dao;

import com.bean.Emp;

import java.util.List;

public interface EmpDao {
    Emp selectByEid(int eid);

    List<Emp> selectByDid(int did);
}

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

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

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

DeptTest.java

测试类

package com.test;

import com.chen.bean.Dept;
import com.chen.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 testSelectByDid(){
        Dept dept = deptDao.selectByDid(101);
        System.out.println(dept);
    }



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

EmpTest.java

测试类

package com.test;

import com.chen.bean.Emp;
import com.chen.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;
import java.util.List;

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 testSelectByEid(){
        Emp emp = empDao.selectByEid(2);
        System.out.println(emp);
    }

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

mybatis.xml

<?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="jdbc.properties">

    </properties>

<!--项目配置信息-->
    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <!--        加载com.bean.Student包中的所有类，给类默认起名别为类的名字但首字母小写-->
<typeAliases>
    <package name="com.bean"/>
</typeAliases>

    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">

               <property name="driver" value="${jdbc.driver}"/>
               <property name="url" value="${jdbc.url}"/>
               <property name="username" value="${jdbc.user}"/>
               <property name="password" value="${jdbc.password}"/>

            </dataSource>
        </environment>
    </environments>
<mappers>
    <package name="com.dao"/>
</mappers>
</configuration>

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mydb
jdbc.user=root
jdbc.password=root

log4j.properties

log4j.rootLogger=TRACE,stdout  

log4j.appender.stdout=org.apache.log4j.ConsoleAppender   
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout   


log4j.appender.logfile=org.apache.log4j.FileAppender   
log4j.appender.logfile.File=wocao.log   
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout   
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n   
log4j.logger.mapperNS =TRACE

log4j.logger.com.mybatis=DEBUG  
log4j.logger.com.mybatis.common.jdbc.SimpleDataSource=DEBUG   
log4j.logger.com.mybatis.common.jdbc.ScriptRunner=DEBUG   
log4j.logger.com.mybatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG   
log4j.logger.java.sql.Connection=DEBUG  
log4j.logger.java.sql.Statement=DEBUG  
log4j.logger.java.sql.PreparedStatement=DEBUG  
log4j.logger.java.sql.ResultSet=DEBUG  

log4j.logger.org.springframework=error 
log4j.logger.org.apache=ERROR  
log4j.logger.org.mybatis=DEBUG

VickyGod
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值