目录
MyBatis映射文件
映射文件指导着MyBatis如何进行数据库增删改查,有着非常重要的意义,有以下标签:cache
:命名空间的二级缓存配置cache-ref
:其他命名空间缓存配置的引用resultMap
:自定义结果集映射sql
:抽取可重用语句块insert
:映射插入语句update
:映射更新语句delete
:映射删除语句select
:映射查询语句
简单的增删改查示例
注意的两点:
- myBatis允许增删改查直接定义以下类型返回值:
Integer
、Long
、Boolean
、void
。直接在接口的方法声明上修改返回值类型即可。 - 如果在创建
SqlSession
时是使用sqlSessionFactory.openSession()
的话我们需要手动提交数据,即sqlSession.commit()
;如果使用的是sqlSessionFactory.openSession(true);
则不需要手动提交,因为他会自动帮我们提交。
增删改查前的准备
我们有如下数据表:
![13424350-2c3c7ffa1218fe58.png](https://i-blog.csdnimg.cn/blog_migrate/65b19aca31a1bf82c5977ded95c93e12.png)
对应的实体bean:
package com.cerr.mybatis;
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 + '\'' +
'}';
}
public Employee(Integer id, String lastName, String email, String gender) {
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Employee() {
}
}
定义接口的方法
我们需要在接口中定义增删改查的方法:
package com.cerr.mybatis.dao;
import com.cerr.mybatis.Employee;
public interface EmployeeMapper {
//查
public Employee getEmpById(Integer id);
//增
public boolean addEmp(Employee employee);
//改
public boolean updateEmp(Employee employee);
//删
public boolean deleteEmpById(Integer id);
}
配置sql映射文件
我们需要在sql映射文件中编写sql语句(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.cerr.mybatis.dao.EmployeeMapper">
<!-- 查 -->
<select id="getEmpById" resultType="com.cerr.mybatis.Employee">
select * from tb1_employee where id = #{id}
</select>
<!-- 增 -->
<insert id="addEmp" parameterType="com.cerr.mybatis.Employee">
insert into tb1_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>
<!-- 改 -->
<update id="updateEmp">
update tb1_employee set last_name = #{lastName},email=#{email},gender=#{gender}
where id=#{id}
</update>
<!-- 删 -->
<delete id="deleteEmpById">
delete from tb1_employee where id = #{id}
</delete>
</mapper>
将sql映射文件在全局配置文件中注册
<?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>
<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>
</environments>
<!-- 将我们写好的sql映射文件注册到全局配置文件中 -->
<mappers>
<!-- 批量注册 -->
<package name="com.cerr.mybatis.dao"/>
</mappers>
</configuration>
1.测试新增数据
package com.cerr.mybatis;
import com.cerr.mybatis.dao.EmployeeMapper;
import com.cerr.mybatis.dao.EmployeeMapperAnnotation;
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 java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class MyBatisTest {
//获取SQLSessionFactory
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 测试添加
*/
@Test
public void test3() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取到的sql不会自动提交数据
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null,"jerry","jerry@qq.com","1");
Boolean isOk = mapper.addEmp(employee);
//提交
sqlSession.commit();
System.out.println(isOk);
}finally {
sqlSession.close();
}
}
}
执行后结果如下:
![13424350-0c4246d670c8ac7e.png](https://i-blog.csdnimg.cn/blog_migrate/67ec0a057ac2ac2a7535f1432ccc0820.png)
数据库信息:
![13424350-009604217e35910c.png](https://i-blog.csdnimg.cn/blog_migrate/f244d3b5201d384b92ee0588bfcf9955.png)
2.测试修改
我们将id=1的信息修改为last_name=aaa,email=aaa@qq.com,gender=1。
方法:
package com.cerr.mybatis;
import com.cerr.mybatis.dao.EmployeeMapper;
import com.cerr.mybatis.dao.EmployeeMapperAnnotation;
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 java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class MyBatisTest {
//获取SQLSessionFactory
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 测试修改
*/
@Test
public void test4() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取到的sql不会自动提交数据
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(1,"aaa","aaa@qq.com","1");
Boolean isOK = mapper.updateEmp(employee);
//提交
sqlSession.commit();
if (isOK){
System.out.println("修改成功,信息为:"+employee);
}else{
System.out.println("修改失败");
}
}finally {
sqlSession.close();
}
}
}
控制台信息如下:
![13424350-0f57e37c0a459d72.png](https://i-blog.csdnimg.cn/blog_migrate/f16ac3d5d6d958ec5fde4c397bc28d4f.png)
表:
![13424350-3e28cd34a2c3c9cc.png](https://i-blog.csdnimg.cn/blog_migrate/f29e58a3eb915021f5c5eee4644084e8.png)
3.测试删除
把id=3的记录给删除
package com.cerr.mybatis;
import com.cerr.mybatis.dao.EmployeeMapper;
import com.cerr.mybatis.dao.EmployeeMapperAnnotation;
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 java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class MyBatisTest {
//获取SQLSessionFactory
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 测试删除
*/
@Test
public void test5() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取到的sql不会自动提交数据
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Boolean isOK = mapper.deleteEmpById(3);
//提交
sqlSession.commit();
if (isOK){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}finally {
sqlSession.close();
}
}
}
4.测试查询
package com.cerr.mybatis;
import com.cerr.mybatis.dao.EmployeeMapper;
import com.cerr.mybatis.dao.EmployeeMapperAnnotation;
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 java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class MyBatisTest {
//获取SQLSessionFactory
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test1() throws IOException {
//获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//获取接口的实现类对象:会为接口自动的创建一个代理对象,代理对象去执行增删改查
EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
//调用方法
Employee employee = employeeMapper.getEmpById(1);
//打印
System.out.println(employee);
}finally {
//关闭
sqlSession.close();
}
}
}
insert获取自增主键的值
mysql支持自增主键的获取,而MyBatis也支持,利用statament.genGenreatedKeys()
进行获取。需要在sql映射文件的<insert>
中配置属性useGeneratekKeys
的值为true
,表示使用自增主键获取主键值策略。
还需要使用keyProperty
属性来指定对应的主键属性,也就是mybatis获取到主键值以后,将这个值封装给javaBean的哪个属性。
<?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.cerr.mybatis.dao.EmployeeMapper">
<!-- 增 -->
<insert id="addEmp" parameterType="com.cerr.mybatis.Employee"
useGeneratedKeys="true" keyProperty="id">
insert into tb1_employee(last_name,email,gender) values(#{lastName},#{email},#{gender})
</insert>
</mapper>
package com.cerr.mybatis;
import com.cerr.mybatis.dao.EmployeeMapper;
import com.cerr.mybatis.dao.EmployeeMapperAnnotation;
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 java.io.IOException;
import java.io.InputStream;
import static org.junit.Assert.*;
public class MyBatisTest {
//获取SQLSessionFactory
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 测试添加
*/
@Test
public void test3() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
//获取到的sql不会自动提交数据
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null,"jerry","jerry@qq.com","1");
Boolean isOk = mapper.addEmp(employee);
//提交
sqlSession.commit();
System.out.println(isOk);
System.out.println(employee.getId());
}finally {
sqlSession.close();
}
}
}
在添加方法中加上打印id的值,成功打印出来,之前实例化的employee对象的id值是空的,因此可以证明是获取自增主键的功能起了作用。
![13424350-e5172dce6e385c0f.png](https://i-blog.csdnimg.cn/blog_migrate/7b25fabc46eaf1c7ef8031d4aba0edb4.png)
MyBatis的参数处理
可查看:MyBatis笔记 | 详解参数处理(多种类型的参数处理、源码分析、读取参数的两种格式的区别)
select元素
Select元素来定义查询操作
id
:唯一标识符
用来引用这条语句,需要和接口的方法名一致parameterType
:参数类型
可以不传,MyBatis会根据TypeHandler
自动推断resultType
:返回值类型
别名或全类名,如果返回的是集合,定义集合中元素的类型。不能和resultMap
同时使用。resultMap
:用来实现高级结果映射集,自定义封装规则等等
对于resultType
和resultMap
可以进入以下链接参考此文章:MyBatis笔记 | 详解resultType和resultMap