目录
一.基础操作
1.删除
2.新增
3.更新
4.查询
5.XML映射文件
二、动态SQL
1.<if>
2.<where>
3.<set>
4.<foreach>
5.<sql>
6.<include>
一.基础操作
1.删除
参数占位符:
注意:
#{...}相比于${...}具有性能高、防止SQL注入的优势
2.新增
3.更新
4.查询
普通查询:
条件查询:
注意:#{...}不能放在' '当中,使用${..,},会发生SQL注入,解决办法是使用contact()函数
数据封装:
5.XML映射文件
实例:
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace为Mapper接口的全限定名-->
<mapper namespace="com.itheima.springbootmybatisquickstart.mapper.EmpMapper"><!--id为函数名,resultType为返回的对象的全限定名--><select id="IndistinctSelect" resultType="com.itheima.springbootmybatisquickstart.pojo.Emp"><!--执行的SQL语句-->select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime from emp where name like concat('%',#{name},'%') and gender=#{gender} and entrydate between #{begin} and #{end}</select>
</mapper>
二、动态SQL
1.<if>
用于判断条件是否成立。使用test属性进行条件判断,如果条件为true,则拼接SQL
<!--执行的SQL语句-->select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime,update_time updateTime 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>
2.<where>
where元素只会在子元素有内容的情况下才插入where语句。而且会自动去除子句的开头的AND或OR
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace为Mapper接口的全限定名-->
<mapper namespace="com.itheima.springbootmybatisquickstart.mapper.EmpMapper"><!--id为函数名,resultType为返回的对象的全限定名--><select id="IndistinctSelect" resultType="com.itheima.springbootmybatisquickstart.pojo.Emp"><!--执行的SQL语句-->select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime,update_time updateTime 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></select>
</mapper>
3.<set>
动态地在行首插入SET关键字,并会删除额外的逗号。(用在update语句中)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace为Mapper接口的全限定名-->
<mapper namespace="com.itheima.springbootmybatisquickstart.mapper.EmpMapper"><update id="update">update emp<set><if test="username!=null">username=#{username},</if><if test="name!=null">name=#{name},</if><if test="gender!=null">gender=#{gender},</if><if test="image!=null">image=#{image},</if><if test="job!=null">job=#{job},</if><if test="entrydate!=null">entrydate=#{entrydate},</if><if test="deptId!=null">dept_id=#{deptId},</if><if test="updateTime!=null">update_time=#{updateTime}</if></set>where id = #{id}</update>
</mapper>
4.<foreach>
5.<sql>
定义可重复的SQL片段
<sql id="commonSelect">select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime,update_time updateTime from emp</sql>
6.<include>
通过属性refid,指定包含的sql片段
<include refid="commonSelect"/>