搭建 Mybatis 工作环境

一、数据库准备

1、创建 tbl_employee 表

create database `test`;

create table tbl_employee (
 id int(11) PRIMARY key AUTO_INCREMENT,
 last_name varchar(255),
 gender char(1),
 email varchar(255)
);

INSERT INTO `test`.`tbl_employee` (`id`, `last_name`, `gender`, `email`) VALUES (1, 'tom', '0', 'tom@123.com');
INSERT INTO `test`.`tbl_employee` (`id`, `last_name`, `gender`, `email`) VALUES (2, 'jerry', '0', 'jerry@123.com');
INSERT INTO `test`.`tbl_employee` (`id`, `last_name`, `gender`, `email`) VALUES (3, 'jack', '0', 'jack@123.com');
INSERT INTO `test`.`tbl_employee` (`id`, `last_name`, `gender`, `email`) VALUES (4, 'rose', '1', 'rose@123.com');

二、maven环境准备

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>org.example</groupId>
  <artifactId>mybatis_demo1</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>mybatis_demo1 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!--Mybatis-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.5</version>
    </dependency>
    <!--MySQL驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.26</version>
    </dependency>

    <!-- 添加日志支持 -->
    <!-- log4j日志系统 -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <!-- simple logg facade for java（slf4-api.jar）日志接口 和 log4j具体日志系统之间的适配器 -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.12</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.18</version>
    </dependency>
    <dependency>
      <groupId>org.testng</groupId>
      <artifactId>testng</artifactId>
      <version>RELEASE</version>
      <scope>compile</scope>
    </dependency>
  </dependencies>

	<!--IDEA是不会编译src的java目录的xml文件，
	通过以下配置可以将src下的xml文件纳入到编译范围-->
  <build>
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
        </includes>
      </resource>
    </resources>
  </build>

</project>

三、数据库连接信息

jdbc.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT&useSSL=false&useUnicode=true&characterEncoding=utf8
username=***
password=***

四、日志

log4j.properties

log4j.rootLogger=DEBUG,A1

log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=[%t] [%c]-[%p] %m%n

五、代码

1、实体类

package com.example.pojo;

public class Employee {

    private Integer id;
    private String lastName;
    private String email;
    private String gender;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

	@Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

2、mapper 接口

package com.example.mapper;

import com.example.pojo.Employee;

import java.util.List;

public interface EmployeeMapper {

    public List<Employee> findAllEmp();
}

3、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">
<mapper namespace="com.example.mapper.EmployeeMapper">
    <select id="findAllEmp" resultType="com.example.pojo.Employee">
        select * from tbl_employee
    </select>
</mapper>

4、mybatis 全局配置文件

<?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: 引入外部properties文件 必须放在最前面,否则会报错
           resource: 类路径下
           url: 磁盘路径或网络路径
    -->
    <properties resource="jdbc.properties"></properties>

    <!-- 设置日志输出, 方便观察sql语句和参数 -->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

    <!--
        environments 标签主要用于配置 Mybatis 的环境信息。
        可以有多个 environment 标签，每一个单独的 environment 标签对代表一个单独的数据库配置环境。
        在 environment 标签对中，transactionManager 标签配置的是 Mybaits 的事务控制类型，
        而 dataSource 标签中配置的数据库连接信息，其中包含多个 property 标签，用于配置数据库驱动信息driver、
        数据库连接地址 url、数据库用户名 username、数据库密码 password
    -->
    <environments default="dev_mysql">
        <environment id="dev_mysql">
            <transactionManager type="JDBC"></transactionManager>
            <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>


    <mappers>
        <mapper resource="com/example/mapper/EmployeeMapper.xml"></mapper>
    </mappers>

</configuration>

六、测试

package com.example;

import com.example.mapper.EmployeeMapper;
import com.example.pojo.Employee;
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.testng.annotations.Test;

import java.util.*;
import java.io.IOException;
import java.io.InputStream;

public class MybatisTest {

	// // 获取SqlSessionFactory
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory;
    }


    @Test
    public void test01() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

        SqlSession sqlSession = sqlSessionFactory.openSession();

        try{
            EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
            List<Employee> list = employeeMapper.findAllEmp();
            for (Employee employee: list) {
                System.out.println(employee);
            }
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }

    }

}

七、运行结果

[main] [com.example.mapper.EmployeeMapper.findAllEmp]-[DEBUG] ==>  Preparing: select * from tbl_employee 
[main] [com.example.mapper.EmployeeMapper.findAllEmp]-[DEBUG] ==> Parameters: 
[main] [com.example.mapper.EmployeeMapper.findAllEmp]-[DEBUG] <==      Total: 4
Employee{id=1, lastName='null', email='tom@123.com', gender='0'}
Employee{id=2, lastName='null', email='jerry@123.com', gender='0'}
Employee{id=3, lastName='null', email='jack@123.com', gender='0'}
Employee{id=4, lastName='null', email='rose@123.com', gender='1'}