解析Mybatis对sql表的一对多查询问题代码示例

本篇文章小编给大家分享一下解析Mybatis对sql表的一对多查询问题代码示例，文章代码介绍的很详细，小编觉得挺不错的，现在分享给大家供大家参考，有需要的小伙伴们可以来看看。

Mybatisd对sql表的一对多查询

select * from
projectrecord pr
left join 
projects po
on po.pid=pr.pid
left join 
emp e
on e.empno = pr.empno
where pr.pid=1

    
    
        
        
        
        
            
            
            
                
                
                
                
                
                
                
                
            
        
    
    
    
        select *from
            emp e
                inner join
            projectrecord pr
            on pr.empno=e.empno
                inner join
            projects po
            on po.pid= pr.pid
        where pr.pid=#{pid}
    

//mapper表
package com.msb.mapper;
import com.msb.pojo.Dept;
import com.msb.pojo.Emp;
import com.msb.pojo.Projects;
import java.util.List;
public interface EmpMapper {
    /**
     * 实现根据项目编号查看所有参与项目的人员信息
     * @param pid 传入项目号
     * @return 返回一个project的对象
     */
    Projects findPiddle(int pid);
}

package com.msb.pojo;
//一对一关系引入
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Projectrecord implements Serializable {
    private int empno;
    private int pid;
    //pr和e是一对一的关系
    private Emp emp;
}

package com.msb.pojo;
//一对多关系实体类设计
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.List;

@NoArgsConstructor
@AllArgsConstructor
@Data
public class Projects implements Serializable {
    private int pid;
    private String pname;
    private int money;
    //po和pr是一对多的关系
    List projectrecordList;
}

package com.msb.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.sql.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private  Integer sal;
    private Integer comm;
    private  Integer deptno;
    private Dept dept;
}

jdbc_driver=com.mysql.cj.jdbc.Driver
jdbc_url=jdbc:mysql://127.0.0.1:3306/testtable?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
jdbc_username=root
jdbc_password=root

log4j.rootLogger=debug,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=d:/msb.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l %F %p %m%n

import com.msb.mapper.EmpMapper;
import com.msb.pojo.Dept;
import com.msb.pojo.Emp;
import com.msb.pojo.Projectrecord;
import com.msb.pojo.Projects;
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 Test1 {
    SqlSession sqlSession = null;
    EmpMapper mapper =null;

   @Before
   public void test1(){
       //首先做一个对象SqlSessionFactoryBuilder建立一个绘话
       SqlSessionFactoryBuilder ssfb = new SqlSessionFactoryBuilder();
       //有一个文本输入的io流进行读取操作
       InputStream stream = null;
       try {
           //这里的路径直接会定位到配置文件classes下面；所以这个文件在次目录下--编译和
           //-图纸；对数据库文件进行读取，获取一个io流，由于配置文件在classes下面，直接写文件名即可
           stream = Resources.getResourceAsStream("sqlMapConfig.xml");
       } catch (IOException e) {
           e.printStackTrace();
       }
       //build需要指向一个文件进行读取出来--工厂
       SqlSessionFactory factory = ssfb.build(stream);
        //需要用sqlSession去调用增删改查--工人去获取数据，打开这个绘话
       sqlSession = factory.openSession();
   }
    
    /*多对多的查询*/
    @Test
    public void test7(){
        mapper = sqlSession.getMapper(EmpMapper.class);
        Projects piddle = mapper.findPiddle(1);
        System.out.println("Pid");
        System.out.println(piddle.getPid());
        System.out.println("Pname");
        System.out.println(piddle.getPname());
        System.out.println("Money");
        System.out.println(piddle.getMoney());
        System.out.println("ProjectrecordList");
        List projectrecordList = piddle.getProjectrecordList();
        projectrecordList.forEach(System.out::println);

    }
    
    @After
    public void test3(){
       if (sqlSession!=null){
           sqlSession.close();
       }
    }
}