mybatis联查注解+xml实现demo

文章目录

• 1.创建mybatis-config配置文件
• 2. 导入数据库连接配置文件
• 3. pom导依赖包
• 4. 导入log4j.xml文件
• 5. 创建表
• 6. 创建实体类
• 7. 创建mapper映射接口
• 8.创建测试类
• 9. 实现结果
• 10. 不使用注解时，需要创建DepartmentMapper.xml映射文件
• 11. 多个查询条件时
• • 1. 创建表、实体类
  • 2. 创建映射接口
  • 3. 创建映射文件
  • 4.创建测试类

1.创建mybatis-config配置文件

2. 导入数据库连接配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/table?serverTimezone=UTC
username=root
password=root

3. pom导依赖包

	
		
        
            mysql
            mysql-connector-java
            8.0.28
        
        
        
            org.mybatis
            mybatis
            3.5.9
        
        
        
            org.projectlombok
            lombok
            1.18.22
            provided
        
        
        
            log4j
            log4j
            1.2.17
        
        
        
            junit
            junit
            4.12
            test
        
    

4. 导入log4j.xml文件

5. 创建表

CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
insert  into `departments`(`id`,`name`) values 
(1,'教学部'),
(2,'教务部'),
(3,'运营部'),
(4,'咨询部'),
(5,'就业部')
;

6. 创建实体类

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

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Department {
    private Integer id;
    private String name;
}

7. 创建mapper映射接口

  public interface DepartmentMapper {
  @Select("select * from departments")
//    @ResultMap(value = "a")
  @Results(
  @Result(id = true,property = "name" ,column = "name")
 )
    List getAllDepartments();
 }

8.创建测试类

public class DepartmentTest {
    SqlSession sqlSession = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void test() {
        DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
        List allDepartments = mapper.getAllDepartments();
        for (Department allDepartment : allDepartments) {
            System.out.println("allDepartment = " + allDepartment);
        }
    }

    @After
    public void after() {
        sqlSession.close();
    }
}

9. 实现结果

10. 不使用注解时，需要创建DepartmentMapper.xml映射文件

  
    
    
        select * from departments
    

11. 多个查询条件时 1. 创建表、实体类

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `salary` double(11,2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`id`)
)

注解@Data包含@Getter、@Setter、@ToString

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

@AllArgsConstructor
@NoArgsConstructor
@Data
//@Getter
//@Setter
//@ToString

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Double salary;
    private Integer deptId;
    
}

import lombok.*;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class EmployeeAndDep {
    private Integer id;
    private String name;
    private String email;
    private Integer gender;
    private Double salary;
    private Department department;
}

2. 创建映射接口

public interface DepartmentMapper {
    
    Department getDepartmentById(Integer id);
}

public interface EmployeeMapper {
 
    EmployeeAndDep getEmployeeAndDepById(Integer id);
}

3. 创建映射文件

 
        select * from departments where id=#{id}
    
 

        
        
        
        
        
        
        
    
    
        select * from employees where id = #{id}
    

4.创建测试类

package com;

import com.bean.Employee;
import com.bean.EmployeeAndDep;
import com.mapper.EmployeeMapper;
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.Map;
import java.util.Set;

public class EmployeeTest {
    SqlSession sqlSession = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void getEmployeeAndDepById() {
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        EmployeeAndDep employeeAndDepById = mapper.getEmployeeAndDepById(2);
        System.out.println("employeeAndDepById = " + employeeAndDepById);
    }

    @After
    public void after() {
        sqlSession.close();
    }
}

运行结果

DEBUG 05-21 01:31:43,278 ==>  Preparing: select * from employees where id = ?  (BaseJdbcLogger.java:137) 
DEBUG 05-21 01:31:43,328 ==> Parameters: 2(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 05-21 01:31:43,497 ====>  Preparing: select * from departments where id=?  (BaseJdbcLogger.java:137) 
DEBUG 05-21 01:31:43,498 ====> Parameters: 2(Integer)  (BaseJdbcLogger.java:137) 
DEBUG 05-21 01:31:43,503 <====      Total: 1  (BaseJdbcLogger.java:137) 
DEBUG 05-21 01:31:43,504 <==      Total: 1  (BaseJdbcLogger.java:137) 
employeeAndDepById = EmployeeAndDep(id=2, name=Mayun, email=mayun@alibaba.com, gender=0, salary=20000.0, department=Department(id=2, name=教务部))