MyBatis关联查询：一对多

数据表

项目框架搭建 第一步：创建Maven项目

• 添加Maven依赖：

        junit
        junit
        4.12
        test
    

    
        log4j
        log4j
        1.2.17
    

    
        org.projectlombok
        lombok
        1.18.10
    

    
        mysql
        mysql-connector-java
        8.0.18
    
    
        org.mybatis
        mybatis
        3.5.4
    

• 指定Mapper XML文件在mybatis-config.xml中的配置有效，强制部署resources目录到classes

    
        src/main/resources
        
            **/*.xml
        
    
    
        ${basedir}/src/main/resources
    

第二步：配置文件

• log4j.properties

log4j.rootLogger=DEBUG, Console  

#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender  
log4j.appender.Console.layout=org.apache.log4j.PatternLayout  
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n  

log4j.logger.java.sql.ResultSet=INFO  
log4j.logger.org.apache=INFO  
log4j.logger.java.sql.Connection=DEBUG  
log4j.logger.java.sql.Statement=DEBUG  
log4j.logger.java.sql.PreparedStatement=DEBUG  

• mysql.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1/db_test?useSSL=false&serverTimezone=CST
username=root
password=root

第三步：工具类MyBatisUtil

public class MyBatisUtil {
    private static String CONFIG_FILE_LOCATION = "mybatis-config.xml";
    private static final ThreadLocal threadLocal = new ThreadLocal();
    private static SqlSessionFactory sessionFactory;

    static {
        try {
            Reader reader = Resources.getResourceAsReader(CONFIG_FILE_LOCATION);
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            sessionFactory = builder.build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession() {
        SqlSession session = threadLocal.get();
        if (session == null) {
            session = (sessionFactory != null) ? sessionFactory.openSession() : null;
            threadLocal.set(session);
        }
        return session;
    }

    public static void closeSession() {
        SqlSession session = threadLocal.get();
        if (session != null) {
            session.close();
            threadLocal.set(null);
        }
    }
}

第四步：实体类

• Emp

@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class Emp implements Serializable {

	private static final long serialVersionUID = 4976291764719306570L;

	private Integer empno;  //封装数据类型
	private String ename;
	private String job;
	private Integer mgr;  //封装数据类型
	private LocalDate hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

}

• Dept

@Data
@ToString(exclude = "emps") //设置toString输出字段中不包含emps字段
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {

    private static final long serialVersionUID = -4186854135138751084L;

    private Integer deptno;
    private String dname;
    private String loc;

    /
    private List emps;
}

XML方式 第一步：创建mybatis-config.xml

DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

    
    

    
        
        
    

    
        
            
              
                
                
                
                
            
        
    

    
        
    

注意：mappers标签中使用的是resouce

第二步：DeptDao

public interface DeptDao {
      Dept selectDeptWithEmpsByDname(String dname);
}

第三步：DeptMapper.xml

    
        
        
        
        
            
            
            
            
            
            
            
        
    

    
		select * from tb_dept
		inner join tb_emp
		on tb_emp.deptno = tb_dept.deptno
		where dname like "%"#{dname}"%";
	

测试代码

public class DeptDaoTest {

    @Test
    public void getDeptWithEmpsByDname() {
        SqlSession session = MyBatisUtil.getSession();
        DeptDao deptDao = session.getMapper(DeptDao.class);

        Dept dept = deptDao.selectDeptWithEmpsByDname("sales");
        System.out.println(dept);
        dept.getEmps().forEach(System.out::println);
    }

}

注解方式 第一步：创建mybatis-config.xml

DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">


    
    

    
        
        
    

    
        
            
              
                
                
                
                
            
        
    
    
    
        
        
    

第二步：创建Dao接口

• EmpDao

public interface EmpDao {
      @Select("select * from tb_emp where deptno = #{deptno}")
      List selectEmpByDeptno(Integer deptno);
}

• DeptDao

public interface DeptDao {
      @Select("select * from tb_dept where dname = #{dname}")
      @Results({
              @Result(id=true,column="deptno",property="deptno"),
              @Result(column="dname",property="dname"),
              @Result(column="loc",property="loc"),
              @Result(
                      column="deptno",property="emps",
                      many=@Many( select="com.hc.one2many.dao.EmpDao.selectEmpByDeptno", fetchType= FetchType.LAZY )
              )
      })
      Dept selectDeptWithEmpsByDname(String dname);
}

测试代码

public class DeptDaoTest {

    @Test
    public void getDeptWithEmpsByDname() {
        SqlSession session = MyBatisUtil.getSession();
        DeptDao deptDao = session.getMapper(DeptDao.class);
        Dept dept = deptDao.selectDeptWithEmpsByDname("sales");
        System.out.println(dept);
        dept.getEmps().forEach(System.out::println);
    }

}