一、MyBatis环境搭建
1.1、数据的准备
CREATE DATABASE IF NOT EXISTS db_test;
USE db_test;
CREATE TABLE IF NOT EXISTS t_student(
id INT PRIMARY KEY auto_increment,
name VARCHAR(30) NOT NULL,
age INT
);
INSERT INTO t_student
VALUES(null,'Sakura',10),
(null,'Sakura',12),
(null,'Mikoto',14),
(null,'Shana',15),
(null,'Shana',16);
1.2、导入jar包
通过 maven 的方式导入 jar包，打包方式设置为 jar 包即可。
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<!-- junit 测试程序 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
1.3、创建核心配置文件
创建 MyBatis 的核心配置文件，习惯上命名为 mybatis-config.xml，这个文件名仅仅只是建议，并非强制要求。配置 MyBatis 的核心配置文件中的标签必须按照固定的顺序：
properties --> settings --> typeAliases --> typeHandlers --> objectFactory --> objectWrapperFactory --> reflectorFactory --> plugins --> environments --> databaseIdProvider --> mappers
<?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="jdbc.properties"/>
<settings>
<!-- 将下划线映射为驼峰 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 按需加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<!-- 设置类型别名 -->
<typeAliases>
<!-- 以包为单位，将包下所有的类型设置默认的类型别名，即类名且不区分大小写 -->
<package name="star.light.pojo"/>
</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>
</environments>
<!--引入映射文件-->
<mappers>
<!--
以包为单位引入映射文件，要求：
1、mapper接口所在的包与映射文件所在的包一致
2、mapper接口要和映射文件一致
-->
<package name="star.light.mapper"/>
</mappers>
</configuration>
数据库连接信息的配置文件：jdbc.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_test
jdbc.username=root
jdbc.password=abc123
1.4、创建实体类
package star.light.pojo;
public class Student {
private Integer id;
private String name;
private Integer age;
public Student() {
}
public Student(Integer id, String name, Integer age) {
this.id = id;
this.name = name;
this.age = age;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
1.5、创建mapper接口
MyBatis 中的 mapper 接口相当于以前的 dao。但是区别在于，mapper 仅仅是接口，我们不需要提供实现类。
package star.light.mapper;
public interface StudentMapper {
/**
* MyBatis 面向接口编程的两个一致：
* 1、映射文件的 namespace 要和 mapper 接口的全类名保持一致
* 2、映射文件中 SQL 语句的 id 要和 mapper 接口中方法名一致
*/
}
1.6、创建MyBatis的映射文件
映射文件的命名应该为 表所对应的实体类的类名+Mapper.xml。一个映射文件对应一个实体类，对应一张表的操作。MyBatis映射文件用于编写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="star.light.mapper.StudentMapper">
</mapper>
1.7、封装SqlSessionUtil工具类
package star.light.util;
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 java.io.IOException;
import java.io.InputStream;
public class SqlSessionUtil {
public static SqlSession getSqlSession(){
SqlSession sqlSession = null;
try {
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
sqlSession = sqlSessionFactory.openSession(true);
} catch (IOException e) {
e.printStackTrace();
}
return sqlSession;
}
}
二、动态SQL
测试程序：
package star.light.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import star.light.mapper.StudentMapper;
import star.light.pojo.Student;
import star.light.util.SqlSessionUtil;
import java.util.ArrayList;
import java.util.List;
public class DynamicMapperTest {
@Test
public void testGetStudentByConditionOne(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student(null,null,14);
List<Student> list = mapper.getStudentByConditionOne(student);
list.forEach(System.out::println);
}
@Test
public void testGetStudentByConditionTwo(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = new Student(null,"Sakura",null);
List<Student> list = mapper.getStudentByConditionTwo(student);
list.forEach(System.out::println);
}
@Test
public void testinsertStrudents(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = new ArrayList<>();
students.add(new Student(null,"Kikyō",18));
students.add(new Student(null,"Kagome",15));
students.add(new Student(null,"Kagome",18));
int affectedRowCount = mapper.insertStrudents(students);
System.out.println("affectedRowCount = " + affectedRowCount);
}
@Test
public void testDeleteStudentsOne(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Integer[] ids = new Integer[]{10,11};
int affectedRowCount = mapper.deleteStudentsOne(ids);
System.out.println("affectedRowCount = " + affectedRowCount);
}
@Test
public void testDeleteStudentsTwo(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Integer[] ids = new Integer[]{10,11};
int affectedRowCount = mapper.deleteStudentsTwo(ids);
System.out.println("affectedRowCount = " + affectedRowCount);
}
}
StudentMapper 接口：
package star.light.mapper;
import org.apache.ibatis.annotations.Param;
import star.light.pojo.Student;
import java.util.List;
public interface StudentMapper {
/**
* MyBatis 面向接口编程的两个一致：
* 1、映射文件的 namespace 要和 mapper 接口的全类名保持一致
* 2、映射文件中 SQL 语句的 id 要和 mapper 接口中方法名一致
*/
List<Student> getStudentByConditionOne(Student student); // 根据条件查询学生信息
List<Student> getStudentByConditionTwo(Student student); // 根据条件查询学生信息
List<Student> getStudentByChoose(Student student); // 使用choose查询学生信息
int insertStrudents(@Param("students") List<Student> students); // 批量添加学生信息
int deleteStudentsOne(@Param("ids")Integer[] ids); // 批量删除学生信息
int deleteStudentsTwo(@Param("ids")Integer[] ids); // 批量删除学生信息
}
StudentMapper.xml 配置文件：
<?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="star.light.mapper.StudentMapper">
<!--
sql片段，可以记录一段公共sql片段，在需要用的地方通过include标签进行引入
-->
<sql id="studentColumns">
id,name,age
</sql>
<!-- List<Student> getStudentByConditionOne(Student student); -->
<select id="getStudentByConditionOne" resultType="Student">
<!--
include标签引入sql片段，其中refid属性为要要引入sql片段的id
-->
select <include refid="studentColumns"></include> from t_student
<!--
where标签 一般和 if标签 结合使用
a).若 where 标签中有条件成立，会自动生成 where 关键字
b).会自动将 where 标签中内容前多余的 and 去掉，但是其中其中内容后多余的 and 无法去掉
c).若 where 标签中没有任何一个成立成立，则 where 标签中没有任何功能
-->
<where>
<!--
if 标签：通过 test 属性中的表达式判断标签中的内容是否有效（是否会拼接到SQL中）
-->
<if test="name != null and name != ''">
name = #{name}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
</where>
</select>
<!-- List<Student> getStudentByConditionTwo(Student student); -->
<select id="getStudentByConditionTwo" resultType="Student">
select <include refid="studentColumns"></include> from t_student
<!--
trim标签 用于去掉或添加标签中的内容
常用属性：
prefix：在trim标签中的内容的前面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffix：在trim标签中的内容的后面添加某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容
-->
<trim prefix="where" suffixOverrides="and">
<if test="name != null and name != ''">
name = #{name} and
</if>
<if test="age != null and age != ''">
age = #{age}
</if>
</trim>
</select>
<!-- List<Student> getStudentByChoose(Student student); -->
<select id="getStudentByChoose" resultType="Student">
select <include refid="studentColumns"></include> from t_student
<where>
<!--
choose、when、 otherwise 相当于 if...else if..else
when 至少设置一个，otherwise 至多设置一个
-->
<choose>
<when test="name != null and name != ''">
name = #{name}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
</choose>
</where>
</select>
<!-- int insertStrudents(@Param("students") List<Student> students); -->
<insert id="insertStrudents">
insert into t_student values
<!--
foreach标签 用于循环遍历
collection：设置要循环遍历的集合、数组等
item：用一个字符串表示数组或集合中的每一个数据
separator：设置每次循环的数据之间的分隔符
-->
<foreach collection="students" item="student" separator=",">
(#{student.id},#{student.name},#{student.age})
</foreach>
</insert>
<!-- int deleteStudentsOne(@Param("ids")Integer[] ids); -->
<delete id="deleteStudentsOne">
delete from t_student where id in
<!--
foreach标签 用于循环遍历
collection：设置要循环遍历的集合、数组等
item：用一个字符串表示数组或集合中的每一个数据
separator：设置每次循环的数据之间的分隔符
open：循环的所有内容以什么开始
close：循环的所有内容以什么结束
-->
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
<!-- int deleteStudentsTwo(@Param("ids")Integer[] ids); -->
<delete id="deleteStudentsTwo">
delete from t_student where
<!--
foreach标签 用于循环遍历
collection：设置要循环遍历的集合、数组等
item：用一个字符串表示数组或集合中的每一个数据
separator：设置每次循环的数据之间的分隔符，会自动在前面和后面添加空格
-->
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
</mapper>