晋南讲堂之持久层框架Mybatis—(四)SQL映射文件(数据库CRUD操作)

  首先按照mybatis文章(三)中开头指出的方法复制一个项目出来。新项目如下图：

  在EmployeeMapper.java接口中增加如下的方法：

package com.mybatis.Dao;
import com.mybatis.beans.Employee;

public interface EmployeeMapper {
	
	public Employee getEmpById(Integer id);//查
	
	public void addEmp(Employee employee);//增
	
	public void updateEmp(Employee employee);//改
	
	public void deleteEmpById(Integer id);//删
}

  同样在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.mybatis.Dao.EmployeeMapper">
	<select id="getEmpById" resultType="emp" databaseId="mysql">
		select id,last_name lastName,gender,email from tbl_employee where id = #{id}
	</select>
	<select id="getEmpById" resultType="emp" databaseId="oracle">
		select EMPLOYEE_ID id,LAST_NAME last_name lastName,gender,EMAIL email from employees where EMPLOYEE_ID = #{id}
	</select>
	
	<insert id="addEmp" parameterType="com.mybatis.beans.Employee">
		insert into tbl_employee(last_name,email,gender)
		values(#{lastName},#{email},#{gender})
	</insert>
	
	<update id="updateEmp">
		update tbl_employee
			set last_name=#{lastName},email=#{email},gender=#{gender}
			where id=#{id}
	</update>
	
	<delete id="deleteEmpById">
		delete from tbl_employee where id=#{id}
	</delete>
</mapper>

  insert、update、delete三个标签都具有parameterType和databaseId的属性，parameterType的值可以不写，也可以写成Employee的全类名，也可以写成在全局配置文件中设置的别名“emp”，databaseId可以加上“mysql”，也可以省略，当全局配置文件中采用MySQL数据库时，mybatis会加载sql映射文件中所有的带mysql标识和不带数据库标识的sql语句，所以可以省略。三个标签中的id值都是接口中对应的方法。

  Employee.java中要新增有参和无参构造方法，注意无参构造方法是一定要增加的(要么有参无参都不写，要么都写，因为mybatis框架会调用这个默认的无参构造方法来构造实例对象)，如下所示：

package com.mybatis.beans;
import org.apache.ibatis.type.Alias;

public class Employee {
	private Integer id;
	private String lastName;
    private String gender;
    private String email;
    
	public Employee() {
		super();
	}
	public Employee(Integer id, String lastName, String gender, String email) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.gender = gender;
		this.email = email;
	}
	public int 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 getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", email=" + email + "]";
	}
}

  紧接着测试一下往数据库增加一条记录的addEmp()方法，在MybatisTest.java中新增测试方法test2()，如下所示：

package com.mybatis.test;

import java.io.IOException;
import java.io.InputStream;

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.mybatis.Dao.EmployeeMapper;
import com.mybatis.Dao.EmployeeMapperAnnotation;
import com.mybatis.beans.Employee;

public class MybatisTest {

	@Test
	public void test() throws IOException {
		String resource = "Mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			Employee employee = openSession.selectOne("com.mybatis.EmployeeMapper.selectEmp", 1);
			System.out.println(employee);
		} 
		finally {
			openSession.close();
		}
		
	}
	
	@Test
	public void test1() throws IOException{
		
		String resource = "Mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			System.out.println(mapper.getClass());
			Employee employee = mapper.getEmpById(2);
			System.out.println(employee);
		} 
		finally {
			openSession.close();
		}
		
	}
	
	@Test
	public void test2() throws IOException{//测试增删改
		
		String resource = "Mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee1 = new Employee(null,"jerry2","0", "jerry2@163.com");
			mapper.addEmp(employee1);
			openSession.commit();//增删改最后必须有提交这一项，否则不生效
		} 
		finally {
			openSession.close();
		}
		
	}
}

  调用Employee的有参构造方法传递参数，有参构造方法中id设为null,因为数据库中对id设置的自增主键，然后调用接口中的addEmp()方法将employee1实例传入。因为我们在调用openSession()方法时没有传true，所以最后提交这一项一定不能少。

  当然如果给openSession()方法传值为true，则不需要手动提交，如下：

  如下为运行结果：

  测试修改，MybatisTest.java中test2()方法变为如下，画红线部分，把1号员工的信息更新为Jerry2，运行结果如下：


  测试删除，MybatisTest.java中test2()方法变为如下，画红线部分，删除2号员工arya，结果如下，2号员工已删除。


  mybatis允许增删改返回以下类型的返回值：Integer、Long、Boolean、void这三个的基本类和包装类都可以，尽管增删改三个标签中并没有定义resultType属性。只需在接口方法定义时写上返回值类型即可，Long和Integer会返回受影响的行数。