MyBatis:3_增删查改
一、准备
1.1 新建一个student_info表
CREATE TABLE `db`.`student_info`( `id` INT NOT NULL, `name` CHAR(20), `age` INT, `sex` CHAR(20), PRIMARY KEY (`id`) );
1.2 插入一些测试数据
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('1', 'Tom', '20', 'male');
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('2', 'Lily', '21', 'female');
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('3', 'Lucy', '19', 'female');
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('4', 'Bruce', '22', 'male');
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('5', 'David', '20', 'male');
INSERT INTO `db`.`student_info` (`id`, `name`, `age`, `sex`) VALUES ('6', 'James', '23', 'male');
1.3 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>
<typeAliases>
<package name="features"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value=""/>
<property name="url" value=""/>
<property name="username" value=""/>
<property name="password" value=""/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/StudentMapper.xml"/>
</mappers>
</configuration>
1.4 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="mapper.StudentMapper">
</mapper>
1.5 StudentMapper
package mapper;
import features.Student;
import java.util.List;
public interface StudentMapper {
}
1.6 Student
package features;
public class Student {
private int id;
private String name;
private int age;
private String sex;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
'}';
}
}
1.7 pom.xml
<dependencies>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.10</version>
</dependency>
<!--jdbc-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<!--junit 单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--添加slf4j日志api-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>2.0.0</version>
</dependency>
<!--添加logback-classic依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.11</version>
<scope>test</scope>
</dependency>
<!--添加logback-core依赖-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.11</version>
</dependency>
</dependencies>
1.8 文件目录
二、代码编写
2.1 查看全部数据
StudentMapper
List<Student> selectAll();
StudentMapper.xml
<select id="selectAll" resultType="Student">
select * from student_info;
</select>
执行sql,之后的操作主要修改3中的代码,其他的代码可以直接复制过来
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectAll();
System.out.println(students);
//4. 释放SqlSession
sqlSession.close();
2.2 查看某个id
对应的数据
StudentMapper
Student selectById(int id);
StudentMapper.xml
参数占位符:
#{}
:会将=
之后的内容替换为?
,可以防止SQL注入select * from student_info where id = ?;
${}
:拼sql,存在注入问题select * from student_info where id = 1;
转义字符处理:
- 转义字符:
<
=><
- CDATA区:
<![CDATA[<]]>
<select id="selectById" resultType="Student">
select * from student_info where id = #{id};
</select>
执行sql
Student student = mapper.selectById(1);
System.out.println(student);
2.3 多条件查询
假设我们需要查找年龄大于18岁的男性
StudentMapper
当存在多个变量的时候,我们需要用@Param
进行标注。例如String sex
只有标注了@Param("sex")
,#{sex}
才会知道传入的"male"
是给它的。
如果你因为代码中存在三个sex
而搞不懂它们之间的逻辑关系,不妨将String sex
修改为String male_of_female
然后重新运行一下程序,这是程序的结果不会发生变化。因为能够提示#{sex}
它应该获取传入的哪个值的是@Param("sex")
,而非String sex
中的sex
。
List<Student> selectByCondition(@Param("sex")String sex, @Param("age")int age);
StudentMapper.xml
<select id="selectByCondition" resultType="Student">
select * from student_info
where sex = #{sex}
and age > #{age};
</select>
执行sql
List<Student> students = mapper.selectByCondition("male", 18);
System.out.println(students);
2.4 多条件动态查询
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
if
StudentMapper
List<Student> selectByCondition(@Param("sex")String male_or_female, @Param("age")int age);
StudentMapper.xml
这里的if
语句的含义是,如果传入的字符串sex
不为null
并且字符串不为空,那么增加一个sex=#{sex}
的条件。当我们传入的sex
值为male
/female
,该查询语句表示查询年龄大于18岁的男性/女性。当传入的sex
为null
或者""
,该查询语句表示查询年龄大于18岁的所有人。
<select id="selectByCondition" resultType="Student">
select * from student_info
where age > #{age}
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
;
</select>
执行sql
// 年龄大于18岁的男性
List<Student> students = mapper.selectByCondition("male", 18);
// 输出 4
System.out.println(students.size());
// 年龄大于18的男性或女性
List<Student> students = mapper.selectByCondition("", 18);
// 输出 6
System.out.println(students.size());
where
接下来我们尝试通过name
和sex
这两个条件进行查询
<select id="selectByCondition" resultType="Student">
select * from student_info
where
<if test="name!=null and name!=''">
name = #{name}
</if>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
;
</select>
有一个比较尴尬的问题在于,如果传入的name
和sex
都为空,那么sql语句就变成了
select * from student_info where;
这显然是错误的。然而解决方式有两种,一种是在where
后面加入恒等式,修改之后的代码是这样的:
<select id="selectByCondition" resultType="Student">
select * from student_info
where 1 = 1
<if test="name!=null and name!=''">
name = #{name}
</if>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
;
</select>
但是这样的写法有一点捞,因为MyBatis早就预判了我们的预判,提供了<where></where>的标签,用这种标签,哪怕传入的参数都为空也没有关系了!
<select id="selectByCondition" resultType="Student">
select * from student_info
<where>
<if test="name!=null and name!=''">
name = #{name}
</if>
<if test="sex!=null and sex!=''">
and sex = #{sex}
</if>
</where>
;
</select>
然后我们对StudentMapper
做一些修改
List<Student> selectByCondition(@Param("sex")String sex, @Param("age")int age);
然后试着运行一下
List<Student> students = mapper.selectByCondition("", "");
System.out.println(students.size()); // 6
List<Student> students = mapper.selectByCondition("male", "");
System.out.println(students.size());// 4
List<Student> students = mapper.selectByCondition("male", "Tom");
System.out.println(students.size()); // 1
2.5 单条件动态查询
choose
choose
:选择,类似于Java中的switch
语句
StudentMapper
List<Student> selectByConditionSingle(@Param("sex")String sex, @Param("name")String name);
StudentMapper.xml
<select id="selectByConditionSingle" resultType="Student">
select * from student_info
where
<choose>
<when test="name!=null and name!=''">
name = #{name}
</when>
<when test="sex!=null and sex!=''">
sex = #{sex}
</when>
<otherwise>
1 = 1
</otherwise>
</choose>
;
</select>
执行sql
List<Student> students = mapper.selectByConditionSingle("", "");
System.out.println(students.size());
2.6 添加
StudentMapper
void add(Student student);
StudentMapper.xml
<insert id="add">
insert into student_info (id, name, age, sex)
values (#{id}, #{name}, #{age}, #{sex});
</insert>
执行sql
Student student = new Student();
student.setId(7);
student.setName("Grace");
student.setAge(21);
student.setSex("female");
mapper.add(student);
// 提交事务
sqlSession.commit();
如果不想手动提交事务,也可以在sqlSessionFactory.openSession()
中传入参数,即可自动提交事务:
SqlSession sqlSession = sqlSessionFactory.openSession(true);
2.7 修改
这一部分我们尝试将Grace
的年龄由21
修改为22
StudentMapper
void update(Student student);
StudentMapper.xml
<update id="update">
update student_info
set
name = #{name},
age = #{age},
sex = #{sex}
where
id = #{id};
</update>
执行sql
Student student = new Student();
student.setId(7);
student.setName("Grace");
student.setAge(22);
student.setSex("female");
mapper.update(student);
2.8 动态修改
在2.7中我们尝试修改了Grace
同学的年龄,但事实上我们也只是需要修改她的年龄,而无需修改其姓名、性别,可是碍于StudentMapper.xml
中的代码,我们又不得不在创建Student
对象实例时将姓名、性别信息添加进去。接下来介绍一种动态修改的方法,使得我们在update
时无需将不需要改动的信息传入。
StudentMapper
void update(Student student);
StudentMapper.xml
<update id="update">
update student_info
<set>
<if test="name!=null and name!=''">
name = #{name},
</if>
<if test="age!=null">
age = #{age},
</if>
<if test="sex!=null and sex!=''">
sex = #{sex},
</if>
</set>
where
id = #{id};
</update>
执行sql
Student student = new Student();
student.setId(7);
student.setAge(22);
mapper.update(student);
2.9 删除一个
StudentMapper
void deleteById(int id);
StudentMapper.xml
<delete id="deleteById">
delete from student_info where id = #{id};
</delete>
执行sql
mapper.deleteById(7);
2.10 删除多个
StudentMapper
void deleteByIds(@Param("ids") int[] ids);
StudentMapper.xml
如果在StudentMapper
的参数int[] ids
前没有添加@Param("ids")
,那么下面代码中的colletion
值需要修改为array
,因为在MyBatis中一个数组将会被封装为Map
类型,其中key
为array
,value
为该数组。
此外,由于删除的sql语句为delete from student_info where id in (id1,id2,...idn)
,而默认的<foeach></foreach>默认是不会用,
来分割每个id
的,因此需要加上separator=","
<delete id="deleteByIds">
delete from student_info
where id in (
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
);
</delete>
如果你觉得上面代码中in (xxx)
的这组括号看着很别扭,也可以把它删去,然后在<foreach>
中添加open="(" close=")"
,这样在遍历前会添加上一个左括号,遍历结束会添加上一个右括号,完美!
执行sql
mapper.deleteByIds(new int[]{7,8});