MyBatis操作数据库（XML文件方式区别于JDBC方式）

本次环境MySQL5.7.20+MyBatis3.4.1+JDK9.0，用到的插件有：

1.创建一个数据库mybatis和表格tbl_employee：

CREATE DATABASE mybatis DEFAULT CHARSET utf8; 

USE mybatis;
CREATE TABLE tbl_employee1(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
last_name VARCHAR(255),
gender CHAR(1),
email VARCHAR(255)
);

2.创建一个MyBatis java工程，编写与数据库中对应字段的Employee类：

package com.atguigu.mybatis.bean;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;

    public Employee() {
        super();
    }

    public Employee(Integer id, String lastName, String email, String gender) {
        super();
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = 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 + "]";
    }

}

3.创建EmployeeMapper接口,添加增删改查方法，此接口中的方法可以与映射到xml文件

package com.atguigu.mybatis.dao;

import com.atguigu.mybatis.bean.Employee;

public interface EmployeeMapper {
    //查询
    public Employee getEmpById(Integer id);
    //插入
    public Long addEmp(Employee employee);
    //修改
    public boolean updateEmp(Employee employee);
    //删除
    public void deleteEmpById(Integer id);

}

4.创建dbconfig.properties文件,存放的是JDBC信息

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://10.0.0.40:3306/mybatis
jdbc.username=chendan
jdbc.password=123456

5.创建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">
<configuration>
    <properties  resource="dbconfig.properties"></properties>   

    <settings>

    <setting name="mapUnderscoreToCamelCase" value="true"/>

    </settings>

    <typeAliases> 
            <!--  typeAlias type="com.atguigu.mybatis.bean.Employee"  alias="emp"/>-->
            <package name="com.atguigu.mybatis.bean"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>

        <environment id="test">
            <transactionManager type="test" />
            <dataSource type="POOLED">
                <property name="driver" value="${orcl.driver}" />
                <property name="url" value="${orcl.url}" />
                <property name="username" value="${orcl.username}" />
                <property name="password" value="${orcl.password}" />
            </dataSource>
        </environment>
    </environments>

    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="SQL Server" value="sql server"/>
    </databaseIdProvider>

    <!-- 将我们写好的sql映射文件（EmployeeMapper.xml）一定要注册到全局配置文件（mybatis-config.xml）中 -->
    <mappers>
        <!-- <mapper resource="EmployeeMapper2.xml" /> -->
        <package name="com.atguigu.mybatis.dao"/>
    </mappers>
</configuration>

6.创建EmployeeMapper.xml映射文件，存放的主要是SQL语句：

<?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.atguigu.mybatis.dao.EmployeeMapper">

    <!--public Employee getEmpById(Integer id); 查询操作 -->
    <select id="getEmpById"
        resultType="com.atguigu.mybatis.bean.Employee" databaseId="mysql">
        select * from tbl_employee1 where id = #{id}
    </select>
    <select id="getEmpById"
        resultType="com.atguigu.mybatis.bean.Employee" databaseId="oracle">
        select
        EMPLOYEE_ID id,LAST_NAME lastName,EMAIL email from employees where
        EMPLOYEE_ID = #{id}
    </select>
    <!-- 插入操作 public void addEmp(Employee employee);-->
    <insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee">
        insert into tbl_employee1(last_name,email,gender) 
        values(#{lastName},#{email},#{gender})
    </insert>
    <!-- 更新操作 public void updateEmp(Employee employee); -->
    <update id="updateEmp">
        update tbl_employee1
        set last_name=#{lastName},email=#{email},gender=#{gender}
        where id=#{id}
    </update> 
    <!-- 删除操作   public void deleteEmpById(Integer id);-->
    <delete id="deleteEmpById">
       delete from tbl_employee1 where id=#{id}
    </delete> 
</mapper>

7.创建测试类，主要用于JUnit测试使用

package com.atguigu.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.lang.reflect.Field;

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.Test;

import com.atguigu.mybatis.bean.Employee;
import com.atguigu.mybatis.dao.EmployeeMapper;

public class MyBatisTest {

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

    @Test
    public void test01() throws IOException {
        // 1、获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        // 2、获取sqlSession对象
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            // 3、获取接口的实现类对象
            // 会为接口自动的创建一个代理对象，代理对象去执行增删改查方法
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = mapper.getEmpById(1);

            System.out.println(mapper.getClass());
            System.out.println(employee);
        } finally {
            openSession.close();
        }

    }

    @Test
    public void test02() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
            Employee employee = new Employee(null,"Jack","Jack@atguigu.com","0");
            mapper.addEmp(employee);
//          Employee employee = new Employee(5,"Jack","Jack@atguigu.com","1");
//          boolean isupdate = mapper.updateEmp(employee);
//          System.out.println(isupdate);
//          mapper.deleteEmpById(4);
            openSession.commit(); 

        } finally {
            openSession.close();
        }

    }

}

运行结果如下：
1、执行查询操作,可以看到数据库中只有一条数据：

Employee employee = mapper.getEmpById(1);
System.out.println(employee);

Employee [id=1, lastName=Jack, email=Jack@atguigu.com, gender=0]

查询成功

2、执行增加一条数据，执行查询结果如下：

            Employee employee = new Employee(null,"Peter","Peter@atguigu.com","1");
            mapper.addEmp(employee);
            openSession.commit(); 
            Employee employee = mapper.getEmpById(2);
            System.out.println(employee);

DEBUG 08-01 15:11:13,512 <==    Updates: 1  (BaseJdbcLogger.java:145) 
Employee [id=2, lastName=Peter, email=Peter@atguigu.com, gender=1]

添加成功

3.修改数据库中的数据，执行结果如下：

            Employee employee = new Employee(2,"Jack","Jack@atguigu.com","1");
            boolean isupdate = mapper.updateEmp(employee);
            openSession.commit(); 

            Employee employee = mapper.getEmpById(2);
            System.out.println(employee);

DEBUG 08-01 15:18:02,625 <==    Updates: 1  (BaseJdbcLogger.java:145) 
Employee [id=2, lastName=Jack, email=Jack@atguigu.com, gender=1]

修改成功

4.删除数据库中的数据，执行结果如下：

            mapper.deleteEmpById(2);
            openSession.commit(); 

            Employee employee = mapper.getEmpById(2);
            System.out.println(employee);

DEBUG 08-01 15:23:09,434 <==    Updates: 1  (BaseJdbcLogger.java:145) 
null
查询数据删除成功

总结：MyBatis操作数据库简单快捷，将代码与sql语句分离出来，非常好！