晋南讲堂之持久层框架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", "[email protected]");
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会返回受影响的行数。