MyBatis框架 JDBC增删改查（v2.1 一对多、多对一关系）

项目下配置文件

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.hzx</groupId>
    <artifactId>day20200813</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!--MyBatis核心依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

        <!--MySql驱动依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <!--日志依赖-->
        <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>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.10</version>
        </dependency>
    </dependencies>
</project>

---

src/main/resource

mybatis-config.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">

<!--MyBatis配置-->
<configuration>
    <!--添加properties配置文件路径(外部配置、动态替换)-->
    <properties resource="db.properties"/>
    <!-- 实体类的别名 -->
    <typeAliases>
        <!--        <typeAlias type="com.entity.User" alias="User"/>-->
        <!-- com.entity包下的所有实体定义别名，默认别名：实体类名 -->
        <package name="entity"/>
    </typeAliases>
    <!--JDBC环境配置、选中默认环境-->
    <environments default="MySqlDB">
        <!--MySql数据库环境配置-->
        <environment id="MySqlDB">
            <!--事务管理-->
            <transactionManager type="JDBC"/>
            <!--连接池-->
            <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--Mapper注册-->
    <mappers>
        <!--注册Mapper文件的所在位置-->
<!--        <mapper resource="mapper/userdao-mapper.xml"/>-->
        <mapper resource="mapper/employeedao-mapper.xml"/>
        <mapper resource="mapper/departmentdao-mapper.xml"/>
    </mappers>
</configuration>

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb3?useSSL=false&characterEncoding=utf-8
username=root
password=123456
initialSize=10
maxActive=50
minIdle=5
maxWait=5000

/mapper

employeedao-mapper.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">

<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="dao.EmployeeDAO">

    <!--一对一关系-->
    <resultMap id="empMap" type="Employee">
        <id column="empid" property="id"/>
        <result column="empname" property="name"/>
        <result column="salary" property="salary"/>
        <association property="dept" javaType="Department">
            <id column="deptid" property="id"/>
            <result column="deptname" property="name"/>
            <result column="location" property="location"/>
        </association>
    </resultMap>
    <select id="queryEmployeeById" resultMap="empMap">
        select department.id deptid,department.name deptname,location,
               employee.id empid,employee.name empname,salary
        from department join employee on department.id = employee.dept_id
        where employee.id=#{id}
    </select>

</mapper>

departmentdao-mapper.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">

<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="dao.DepartmentDAO">

    <!--一对多关系-->
    <resultMap id="deptMap" type="Department">
        <id column="deptid" property="id"/>
        <result column="deptname" property="name"/>
        <result column="location" property="location"/>
        <collection property="employees" ofType="Employee">
            <id column="empid" property="id"/>
            <result column="empname" property="name"/>
            <result column="salary" property="salary"/>
        </collection>
    </resultMap>

    <select id="queryDeptById" resultMap="deptMap">
        select department.id deptid,department.name deptname,location,
               employee.id empid,employee.name empname,salary
        from department join employee on department.id = employee.dept_id
        where department.id=#{id}
    </select>

</mapper>

---

src/main/java

entity/Employee.java

// lombok注解
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
    private Integer id;
    private String name;
    private Double salary;
    // 关系属性
    private Department dept;
}

entity/Depatrment.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Department {
    private Integer id;
    private String name;
    private String location;
    // 关系属性
    //@ToString.Exclude 加此注解则toString中不包含此属性
    private List<Employee> employees;
}

dao/EmployeeDAO.java

import entity.Employee;
import org.apache.ibatis.annotations.Param;

public interface EmployeeDAO {
    Employee queryEmployeeById(@Param("id") Integer id);
}

dao/DepatrmentDAO.java

import entity.Department;
import org.apache.ibatis.annotations.Param;

public interface DepartmentDAO {
    Department queryDeptById(@Param("id") Integer id);
}

util/MyBatisUtil.java

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 java.io.InputStream;

public class MyBatisUtils {
    //获得SqlSession工厂
    private static SqlSessionFactory factory;

    //创建ThreadLocal绑定当前线程中的SqlSession对象
    private static final ThreadLocal<SqlSession> tl = new ThreadLocal<SqlSession>();

    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获得连接（从tl中获得当前线程SqlSession）
    private static SqlSession openSession(){
        SqlSession session = tl.get();
        if(session == null){
            session = factory.openSession();
            tl.set(session);
        }
        return session;
    }
    public static SqlSession openSession2(){
        return factory.openSession();
    }

    //释放连接（释放当前线程中的SqlSession）
    public static void closeSession(){
        SqlSession session = tl.get();
        session.close();
        tl.remove();
    }

    //提交事务（提交当前线程中的SqlSession所管理的事务）
    public static void commit(){
        SqlSession session = openSession();
        session.commit();
        closeSession();
    }

    //回滚事务（回滚当前线程中的SqlSession所管理的事务）
    public static void rollback(){
        SqlSession session = openSession();
        session.rollback();
        closeSession();
    }

    //获得接口实现类对象
    public static <T extends Object> T getMapper(Class<T> clazz){
        SqlSession session = openSession();
        return session.getMapper(clazz);
    }
}

---

测试

import dao.DepartmentDAO;
import dao.EmployeeDAO;
import entity.Department;
import entity.Employee;
import org.junit.Test;
import util.MyBatisUtils;

public class MyBatisTest {

    @Test
    public void testOne2Many(){
        DepartmentDAO mapper = MyBatisUtils.getMapper(DepartmentDAO.class);
        Department department = mapper.queryDeptById(1);
        System.out.println("===========");
    }

    @Test
    public void testMany2One(){
        EmployeeDAO mapper = MyBatisUtils.getMapper(EmployeeDAO.class);
        Employee employee = mapper.queryEmployeeById(1);
        System.out.println("==============");
    }

}