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

2024-12-17 技术教程

本次环境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.Driverjdbc.url=jdbc:mysql://10.0.0.40:3306/mybatisjdbc.username=chendanjdbc.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>  <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>  <mappers>  <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">  <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>  <insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee"> insert into tbl_employee1(last_name,email,gender) values(#{lastName},#{email},#{gender}) </insert>  <update id="updateEmp"> update tbl_employee1 set last_name=#{lastName},email=#{email},gender=#{gender} where id=#{id} </update>  <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语句分离出来，非常好！