mybatis联查注解+xml实现demo

1.创建mybatis-config配置文件

<?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="db.properties"></properties>
    <!--开启驼峰-->
<!--    <settings>-->
<!--        <setting name="mapUnderscoreToCamelCase" value="true"/>-->
        <!--关闭自动映射-->
<!--        <setting name="autoMappingBehavior" value="NONE"/>-->
<!--    </settings>-->
    <!--起别名-->
    <typeAliases>
        <package name="com.bean"/>
    </typeAliases>
    <!--环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--添加厂商-->
    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
    </databaseIdProvider>
    <!--映射文件-->
    <mappers>
        <package name="com.mapper"/>
    </mappers>

</configuration>

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

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

3. pom导依赖包

	<dependencies>
		<!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</scope>
        </dependency>
        <!--log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

4. 导入log4j.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

    <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
        <param name="Encoding" value="UTF-8" />
        <layout class="org.apache.log4j.PatternLayout">
            <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
        </layout>
    </appender>
    <logger name="java.sql">
        <level value="debug" />
    </logger>
    <logger name="org.apache.ibatis">
        <level value="info" />
    </logger>
    <root>
        <level value="debug" />
        <appender-ref ref="STDOUT" />
    </root>
</log4j:configuration>

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<Department> 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<Department> allDepartments = mapper.getAllDepartments();
        for (Department allDepartment : allDepartments) {
            System.out.println("allDepartment = " + allDepartment);
        }
    }

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

9. 实现结果

10. 不使用注解时，需要创建DepartmentMapper.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.mapper.DepartmentMapper">  
    <!--    List<Department> getAllDepartments();-->
    <select id="getAllDepartments" resultType="Department">
        select * from departments
    </select>
</mapper>

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 {
    /**
     * 根据部门id查找department对象
     * @param id
     * @return
     */
    Department getDepartmentById(Integer id);
}

public interface EmployeeMapper {
 /**
     * 通过id查询EmployeeAndDep
     * @param id
     * @return
     */
    EmployeeAndDep getEmployeeAndDepById(Integer id);
}

3. 创建映射文件

 <select id="getDepartmentById" resultType="Department">
        select * from departments where id=#{id}
    </select>

<resultMap id="EmployeeAndDepMap" type="EmployeeAndDep">
        <id property="id" column="id"></id>
        <result property="name" column="last_name"></result>
        <result property="gender" column="gender"></result>
        <result property="email" column="email"></result>
        <result property="salary" column="salary"></result>
        <association 
                property="department"
                javaType="Department"
                select="com.mapper.DepartmentMapper.getDepartmentById"
                column="dept_id">
        </association>
    </resultMap>
    <select id="getEmployeeAndDepById" resultMap="EmployeeAndDepMap">
        select * from employees where id = #{id}
    </select>

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=教务部))