单表查询操作

参数占位符#{}和${}

• #{}：相当于JDBC里面替换占位符的操作方式（#{}->“”）.相当于预编译处理（预编译处理可以防止SQL注入问题）
• ${}：相当于直接替换(desc这种关键字)，但这种不能预防SQL注入

select * from userinfo where username='${name}'

${} VS #{}

1. ${}是直接替换，#{}是预执行；
2. ${} 会存在SQL 注入问题，#{}不存在SQL注入问题

SQL 注入

UserInfo userInfo = userMapper.login("admin","' or 1='1");

mysql> select * from userinfo where username = 'admin' and password ='' or 1='1';+----+----------+----------+-------+---------------------+---------------------+-------+| id | username | password | photo | createtime| updatetime| state |+----+----------+----------+-------+---------------------+---------------------+-------+|1 | admin| admin| | 2021-12-06 17:10:48 | 2021-12-06 17:10:48 | 1 |+----+----------+----------+-------+---------------------+---------------------+-------+1 row in set (0.00 sec)

like模糊查询

• 用concat进行字符串拼接

 <select id="findListByName" resultMap="BaseMap">select * from userinfo where username like concat('%',#{name},'%')</select>

多表查询操作

一对一多表查询

• 一对一的多表查询：
• 需要设置resultMap中有个association标签，property对应实体类的属性名，resultMap是关联属性的字典映射(必须要设置)，columnPrefix是设置前缀，当多表查询中有相同的字段的话，就会报错

<?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.example.demo.mapper.ArticleInfoMapper"><resultMap id="BaseMap" type="com.example.demo.model.ArticleInfo"><!--主键--><id property="id" column="id"></id><!--普通属性--><result property="updatetime" column="updatetime"></result><result property="title" column="title"></result><result property="content" column="content"></result><result property="createtime" column="createtime"></result><result property="rcount" column="rcount"></result><!--自定义对象属性--><association property="user" resultMap="com.example.demo.mapper.UserMapper.BaseMap" columnPrefix="u_"></association></resultMap><select id="getAll" resultType="com.example.demo.model.ArticleInfo">select a.*,u.id from articleinfo as a left join userinfo as u on a.uid = u.id;</select><select id="getAll2" resultMap="BaseMap">select a.*,u.id as u_id ,u.username as u_username,u.password as u_password from articleinfo as a left join userinfo as u on a.uid = u.id;</select></mapper>

一对多多表查询

• collection标签，用法同association

 <resultMap id="BaseMapper2" type="com.example.demo.model.UserInfo"><!--映射主键的）（表中主键和程序实体类中的主键）--><id column="id" property="id"></id><!--普通列的映射--><result column="username" property="name"></result><result column="password" property="password"></result><result column="photo" property="photo"></result><result column="createtime" property="createtime"></result><result column="updatetime" property="updatetime"></result><!--外部关联--><collection property="artlist" resultMap="com.example.demo.mapper.ArticleInfoMapper.BaseMap"columnPrefix="a_"></collection></resultMap> <select id="getAll3" resultMap="BaseMapper2">select u.*,a.id a_id,a.title a_title from userinfo u left join articleinfo a on u.id=a.uid </select>

动态SQL使用

if标签

• 注册分为必填和选填，如果在添加用户的时候有不确定的字段传入，就需要使用动态标签if来判断

//p是传递过来的参数名，并不是表的字段名 <insert id="add3">insert into userinfo(username,password,<if test="p!=null"> photo,</if> state)values(#{username},#{password},<if test="p!=null">#{p},</if> #{state}) </insert>

trim标签

• trim标签的属性：
• prefix：表示整个语句块，以prefix的值作为前缀
• suffix：表示整个语句块，以suffix的值作为后缀
• prefixOverrides：去掉最前面的符合条件的字符
• suffixOverrides：去掉最后面的符合条件的字符

 <insert id="add4">insert into userinfo<trim prefix="(" suffix=")" suffixOverrides=","><if test="username!=null">username,</if><if test="password!=null">password,</if><if test="p!=null">photo,</if><if test="state!=null">state,</if></trim>values<trim prefix="(" suffix=")" suffixOverrides=","><if test="username!=null">#{username},</if><if test="password!=null">#{password},</if><if test="p!=null">#{p},</if><if test="state!=null">#{state},</if></trim></insert>

where标签

• where标签首先可以帮助我们生成where，如果有查询条件，那么就生成where，如果没有查询条件，就会忽略where
• 其次where标签可以判断第一个查询条件前面有没有and，如果有则会删除

<select id="login2" resultType="com.example.demo.model.UserInfo">select * from userinfo<where><if test="username!=null">username=#{username}</if><if test="password!=null">and password=#{password}</if></where></select>

set标签

和where的使用基本一样
可以自动帮助你处理最后一个逗号，并且自动写set

<update id="update" parameterType="map">update blog<set><if test="newTitle != null">title=#{newTitle},</if><if test="newAuthor != null">author=#{newAuthor},</if><if test="newViews != null">views = #{newViews}</if></set><where><if test="id != null">id=#{id}</if><if test="title != null">and title=#{title}</if><if test="author != null">and author=#{author}</if><if test="views != null">and views = #{views}</if></where></update>

foreach标签

• foreach属性：
• collection：参数集合的名字
• item：给接下来要遍历的集合起的名字
• open：加的前缀是什么
• close：加的后缀是什么
• separator：每次遍历之间间隔的字符串

 <delete id="dels">delete from userinfo where id in<foreach collection="list" item="item" open="(" close=")" separator="," >#{item}</foreach> </delete>

先赞后看，养成习惯！！！^ _ ^♥♥♥
每天都更新知识点哦！！！
码字不易，大家的支持就是我坚持下去的动力。点赞后不要忘记关注我哦！