一、Mybatis基础操作
1、需求
完成增删改查,查询有根据ID查询和分页查询,删除有根据ID删除和根据ID批量删除
2、准备
2.1 创建工程
创建一个新的springboot工程,引入对应的起步依赖(mybatis、mysql、lombok)
2.2 引入数据库连接信息
application.properties中引入数据库连接信息
2.3 数据库表和对应实体类(实体类采用驼峰命名)
SQL:
-- 创建数据库 create database if not exists 库名; -- 使用数据库 use mybatis; ......
实体类:
2.4 Mapper接口EmpMapper
@Mapper public interface EmpMapper { }
3、删除
3.1 功能实现
3.1.1 接口方法:
@Mapper public interface EmpMapper { @Delete("delete from emp where id = #{id}") public void delete(Integer id); }
注意: 如果mapper接口方法形参只有一个普通类型的参数,#{…} 里面的属性名可以随便写,如:#{id}、#{value}。
3.1.2 测试
@SpringBootTest class SpringbootMybatisQuickstart2ApplicationTests { @Autowired private EmpMapper empMapper; @Test public void testDel(){ empMapper.delete(15); } }
在单元测试类中通过@Autowired 注解EmpMapper接口。然后调用其delete方法传递参数进行测试。
3.2 日志输入
-
在application.properties中,打开mybatis的日志,并指定输出到控制台
#指定mybatis输出日志的位置, 输出控制台 mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
-
开启日志之后,再次运行单元测试。可以看到在控制台中,输出了执行的SQL语句。 delete from emp where id = ? 参数16没有在后面拼接,id值是使用 ? 进行占位,这种SQL语句称为预编译SQL
3.3 预编译SQL
3.3.1 介绍(优点)
-
性能更高
预编译SQL,编译一次之后会将编译后的SQL语句缓存起来,后面再次执行这条insert语句时,SQL语句一样,不会再次编译。 只是输入的参数不同。(编译:语法检查->缓存->编译->缓存 预编译SQL只需要编译一次)
-
防止SQL注入
将敏感字进行转义,安全
3.3.2 SQL注入
SQL注入是通过操作输入来修改事先定义好的SQL语句,用以达到执行代码对服务器进行攻击的方法。)
3.3.4 参数占位符
介绍:在Mybatis中提供的参数占位符有两种:${...}, #{...}。
-
#{...}
执行SQL时,会将#{…}替换为 ? ,生成预编译SQL,会自动设置参数值。 使用时机:参数传递,都使用#{…}
-
${...}
拼接SQL。直接将参数拼接在SQL语句中,存在SQL注入问题。 使用时机:如果对表名、列表进行动态设置时使用。 注意:在项目开发中,建议使用#{...},生成预编译SQL,防止SQL注入 模糊查询的拼接: 方式一:字符串拼接(有注入风险) 例如:'%${...}%' 方式二:sql字符串函数concat() 例如:concat('%',#{...},'%') 注意:'%#{...}%' 这个写法不行,会变成 '%?%'
4、新增
4.1 基本新增
接口方法:
/** * 插入数据 * @param emp 要保存到数据库的对象,数据就在对象的属性值中 * 注意:emp后面的表字段要和数据库中表字段名字一样,value中#{}的数据名字要和POJO中属性名一样 */ @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " + "value (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})") void insert(Emp emp);
#{...} 里面写的名称是对象的属性
4.2 主键返回
介绍:在数据添加成功后,需要获取插入数据库数据的主键。 接口方法:
/** * 插入数据 * @param em 要保存到数据库的对象,数据就在对象的属性值中 * 注意:emp后面的表字段要和数据库中表字段名字一样,value中#{}的数据名字要和POJO中属性名一样 */ @Options(keyProperty = "id", useGeneratedKeys = true) // 让插入的数据id值回填到对象中去(emp的id属性值) @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " + "value (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})") void insert(Emp emp);
注意:该方式只适合主键的回填,keyProperty假如填入其他属性,该属性位置回填的还是主键值
5、更新
接口方法:
// 修改数据 @Update("update emp set username=#{username}, name=#{name}, job=#{job} where id=#{id}") void update (Emp emp);
6、查询
6.1 根据ID查询
mapper方法:
/** * 查询指定的数据 * @param id * @return */ @Select("select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp where id=#{id}") // 字段列表,直接select * 也一样 Emp selectById(Integer id);
6.2 数据封装(3种方式)
6.2.1 起别名
在SQL语句中,对不一样的列名起别名,别名和实体类属性名一样。
@Select("select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}") public Emp getById(Integer id);
6.2.2 手动结果映射
通过 @Results及@Result 进行手动结果映射。
@Results({@Result(column = "dept_id", property = "deptId"), @Result(column = "create_time", property = "createTime"), @Result(column = "update_time", property = "updateTime")}) @Select("select * from emp where id = #{id}") public Emp getById(Integer id);
6.2.3 开启驼峰命名(推荐)
介绍:如果字段名与属性名符合驼峰命名规则,开启驼峰命名自动映射,mybatis会自动通过驼峰命名规则映射。
配置:
#开启驼峰命名自动映射,即从经典数据库列名 a_column 映射到经典 Java 属性名 aColumn。 mybatis.configuration.map-underscore-to-camel-case=true
注意:要使用驼峰命名前提是实体类的属性与数据库表中的字段名严格遵守驼峰命名 -> Java bean设计多个单词属性名用小驼峰,MySQL设计多个单词字段名用下划线。
6.3 条件查询
mapper方法:
方式一:
@Select("select * from emp where name like '%${name}%' and gender = #{gender} " + "and entrydate between #{begin} and #{end} order by update_time desc") List<Emp> list2(String name,Short gender,LocalDate begin,LocalDate end); // '%${name}%'
注意:这种方式使用 $ 进行字符串拼接,这种方式由于是字符串拼接,并不是预编译的形式,所以效率不高、且存在sql注入风险。
方式二:
@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} " + "and entrydate between #{begin} and #{end} order by update_time desc") List<Emp> list2(String name,Short gender,LocalDate begin,LocalDate end); // concat('%',#{name},'%')
注意:这种方式,生成的SQL都是预编译的SQL语句。 性能高、安全。 推荐
测试:
@Test void TestSelect2(){ LocalDate begin = LocalDate.of(2000, 12, 14); LocalDate end = LocalDate.of(2013, 9, 10); List<Emp> list = empMapper.list2("张", (short) 1, begin, end); for (Emp emp : list) { System.out.println(emp); } }
6.4 参数名说明
在springBoot的2.x版本之后,springBoot的父工程对compiler编译插件进行了默认的参数 parameters 配置,使得在编译时,会在字节码文件中保留原方法形参的名称,所以#{…}里面可以直接通过形参名获取对应的值。
在springBoot的1.x版本中,所有接口方法形参编译后的为var1、var2 …,此时如果有多个参数,就需要通过@Param注解来指定SQL语句中的参数名。
6.5 问题分析
当查询条件时动态的,可以不传或传多个,有的参数会是null,这样查询结果是不正确的,正确的做法应该是,传递了参数,再组装这个查询条件。如果没有传递参数,就不应该组装这个查询条件。 SQL语句会随着用户的输入或外部条件的变化而变化,称为 动态SQL。
二、Mybatis动态SQL
1、XML映射文件
使用Mybatis的注解,主要是来完成一些简单的增删改查功能。如果需要实现复杂的SQL功能,建议使用XML来配置映射语句,也就是将SQL语句写在XML配置文件中。
1.1 SQL映射配置文件的规范:
-
XML映射文件的名称与Mapper接口名称一致,并且将XML映射文件和Mapper接口放置在相同包下(如果没有在properties配置文件中指定位置)。(同包同名)
-
XML映射文件的namespace属性与Mapper接口全限定名一致。
-
XML映射文件中sql语句的id与Mapper 接口中的方法名一致,并保持参数类型和返回值类型一致。
1.2 模板:
<?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="接口全限定名"> <select id="接口方法名" resultType="返回值类型"> <!-- sql语句 --> </select> </mapper>
1.3 配置文件位置
-
位置一(同包同名) :
-
注意:<select>标签的resultType属性,指的是查询返回的单条记录所封装的类型。
-
位置二(配置文件):
配置文件中设置:
#下面这些内容是为了让MyBatis映射 #指定Mybatis的Mapper文件,如果没配置,xml文件要和对应的Mapper接口文件要在同一路径中 mybatis.mapper-locations=classpath:mappers/*xml #指定Mybatis的实体目录,可以在xml文件中使用类型时省略包名 mybatis.type-aliases-package=com.itheima.pojo
-
注意:要么按相同路径,要么在properties文件中指定位置,两种位置不能都放,会冲突!
-
例子:
List<Emp> list3();
<?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.itheima.mapper.EmpMapper"> <!--<select id="list3" resultType="com.itheima.pojo.Emp"> select * from emp; </select>--> <select id="list3" resultType="Emp"> # 配置后可省略包名 select * from emp; </select> </mapper>
(在properties配置文件中指定实体目录后可省略包名)
@Test void testSelect3(){ List<Emp> emps = empMapper.list3(); for (Emp emp : emps) { System.out.println(emp); } }
2、if
<if>:用于判断条件是否成立,使用test属性进行条件判断,如果条件为true,则拼接SQL。 <where>:where 元素只会在子元素有内容的情况下才插入where子句。而且会自动去除子句的开头的AND 或OR
2.1 条件查询
mapper方法:
// xml实现映射,实现动态拼接SQL List<Emp> list4(@Param("name") String abc, @Param("gender") Short gender, @Param("begin") LocalDate begin, @Param("end") LocalDate end);
xml映射文件:
<select id="list4" resultType="Emp"> select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp where <if test="name != null"> name like concat('%', #{name}, '%') </if> <if test="gender != null"> and gender = #{gender} </if> <if test="begin != null and end != null"> and entrydate between #{begin} and #{end} </if> order by update_time desc </select>
测试:
@Test void list4() { //测试动态SQL LocalDate begin = LocalDate.of(2000, 1, 1); LocalDate end = LocalDate.of(2010, 1, 1); List<Emp> emps = empMapper.list4("张三", (short) 1, begin, end); emps.forEach(System.out::println); }
问题: 少传第一个参数多and,不传多参数where
解决:where标签 where 元素只会在子元素有内容的情况下才插入where子句。而且会自动去除子句的开头的AND 或OR。 改造后:
<select id="list4" resultType="Emp"> select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp <where> <if test="name != null"> name like concat('%', #{name}, '%') </if> <if test="gender != null"> and gender = #{gender} </if> <if test="begin != null and end != null"> and entrydate between #{begin} and #{end} </if> </where> order by update_time desc </select>
2.2 更新员工
<set>:动态地在行首插入 SET 关键字,并会删掉额外的逗号。(用在update语句中)
mapper方法:
// 动态更新 void update2(Emp emp);
xml映射文件:
<update id="update2"> update emp set <if test="username != null"> username=#{username}, </if> <if test="name != null"> name=#{name}, </if> <if test="job != null"> job = #{job} </if> where id = #{id} </update>
测试:
@Test void update2() { Emp emp = Emp.builder() .id(19) .username("zs") .name("张三三") .job((short) 3) .build(); empMapper.update2(emp); }
set关键字 动态地在行首插入 SET 关键字,并会删掉额外的逗号。(用在update语句中)
优化:
<update id="update2"> update emp <set> <if test="username != null"> username=#{username}, </if> <if test="name != null"> name=#{name}, </if> <if test="job != null"> job = #{job} </if> </set> where id = #{id} </update>
3、foreach
3.1 属性
collection:集合名称 item:集合遍历出来的元素/项 separator:每一次遍历使用的分隔符 open:遍历开始前拼接的片段 close:遍历结束后拼接的片段
3.2 作用
批量删除数据
3.3 示例
mapper方法:
// 动态删除 void delete2(@Param("ids") List<Integer> ids);
xml映射文件:
<!-- 删除操作--> <delete id="delete2"> delete from emp where id in <foreach collection="ids" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete>
测试:
@Test public void delete2() { List<Integer> ids = Arrays.asList(1,2,3,4,5,6,7); empMapper.delete2(ids); } // 部分id没有,也没报错
4、sql/include
4.1 标签
<sql>
:定义可重用的 SQL 片段。 <include>
:通过属性refid,指定包含的sql片段。
4.2 问题分析:
在xml映射文件中配置的SQL,有时可能会存在很多重复的片段,此时就会存在很多冗余的代码。
4.3 解决:
可以对重复的代码片段进行抽取:
1. 将其通过 标签封装到一个SQL片段
<select id="list3" resultType="Emp"># 配置后可省略包名 select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp </select> <select id="list4" resultType="Emp"> select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp <where> <if test="name != null"> name like concat('%', #{name}, '%') </if> <if test="gender != null"> and gender = #{gender} </if> <if test="begin != null and end != null"> and entrydate between #{begin} and #{end} </if> </where> order by update_time desc </select>
2. 然后在原来抽取的地方通过 标签进行引用
<sql id="common_select"> select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp </sql> <select id="list3" resultType="Emp"># 配置后可省略包名 <include refid="common_select"/> </select> <select id="list4" resultType="Emp"> <include refid="common_select"/> <where> <if test="name != null"> name like concat('%', #{name}, '%') </if> <if test="gender != null"> and gender = #{gender} </if> <if test="begin != null and end != null"> and entrydate between #{begin} and #{end} </if> </where> order by update_time desc </select>