我们在JDBC中在构造sql语句的时候,常常给字段的值用问号?
代替,最后在使用方法对这些?
进行赋值,这是预编译。
使用预编译的好处可以防止sql注入。当然还有一种sql的执行方式就是即时执行。
SQL注入是一种常见的安全漏洞,它利用了未正确过滤或转义用户输入的数据,导致恶意用户可以在执行SQL查询时插入恶意的SQL代码。
下面我们来了解一下MyBatis程序中的即使执行和预编译的构建方式.
就像下面我们写道的根据某个字段查询单个信息的时候,我们传递了参数,在xml文件中对相应的字段进行赋值的时候使用${}这种方式就是构造sql语句即时执行的方式。
<select id="getUserById" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where id=${id}
</select>
我们从运行结果看到,执行的sql语句是直接被赋值的,并没有使用?
.
这种写法在程序执行的时候,我们可以看到sql语句中id的值先是被?
将位置占着的。这里?
表示的是只能是值,而不能是sql语句,这就防止了sql注入。
<select id="getUserById" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where id=#{id}
</select>
即时执行(${}): 优点:
${}
可以实现排序查询,而使用#{}
就不能实现排序查询,因为当使用#{}
查询时,如果传递的值为String就会加单引号,就会导致sql错误.缺点:
SQL注入是一种常见的安全漏洞,它利用了未正确过滤或转义用户输入的数据,导致恶意用户可以在执行SQL查询时插入恶意的SQL代码。 查询数据库可以看到用户名和密码都是admin.
正常情况下,用户只能通过密码来输入.
//用户登录的场景
UserEntity login(@Param("username")String username,@Param("password")String password);
<select id="login" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where username='${username}' and password='${password}'
</select>
//单元测试代码
@Test
void login() {
UserEntity user = userMapper.login("admin","admin");
System.out.println(user);
}
但是非法情况下,我们给password的属性填写一个语句就可以登录成功:
@Test
void login() {
UserEntity user = userMapper.login("admin","'or 1='1");
System.out.println(user);
}
由于and的优先级高,先执行前面的结果为false.但是后面的or表示的是两个表达式中只要有一个表达式为真,那么最后的结果就为真,那么1=‘1’,这个表达式就为真. 这就是最简单的SQL注入.
预编译(#{}): 优点:
缺点:
//UserMapper类下:
//传递排序规则
List<UserEntity> getAllByOrder(@Param("myorder")String myorder);
//UserMapper.xml
<select id="getAllByOrder" resultType="com.example.demo.entity.UserEntity">
select * from userinfo order by id #{myorder}
</select>
//单元测试
@Test
void getAllByOrder() {
List<UserEntity> list = userMapper.getAllByOrder("desc");
System.out.println(list);
}
当时当我们将#{}
换成${}
,再次运行就会执行成功。
select * from userinfo order by id ${myorder}
总结: {}和#{}的区别在于替换方式和安全性。{}是简单的字符串替换,直接将参数值拼接到SQL语句中,没有安全处理,存在安全风险和SQL注入风险;而#{}是预编译处理,将参数放入PreparedStatement中,使用占位符进行替换,提供了类型转换、安全处理和预编译功能,更加安全可靠。因此,为了防止SQL注入攻击和保证系统的安全性,推荐使用#{}作为参数占位符。
在上述博客中,我们简单介绍了<select>
标签.详情见:MyBatis项目创建与使用
接下来,我们来实现用户的增删改操作,对应使用MyBatis的标签如下:
<insert>
标签:插入语句.
<update>
标签:修改语句.
<delete>
标签:删除语句.
添加操作在接口中声明方法的时候,定义的返回值类型是int,因为默认的返回值是受影响的行数,在XML文件实现add方法时,也不需要规定返回值类型。
//UserMapper类下:
//添加用户
int add(UserEntity user);
//UserMapper.xml:
<insert id="add">
insert into userinfo(username,password,photo)values(#{username},#{password},#{photo})
</insert>
//单元测试:
@Test
void add() {
UserEntity user = new UserEntity();
user.setUsername("张三");
user.setPassword("123");
user.setPhoto("/image/default.png");
int result = userMapper.add(user);
System.out.println("受影响的行数: "+result);
}
特殊的添加:返回自增id 之前的方法默认情况下返回的是受影响的行数,如果想要返回自增id,具体实现如下。
//UserMapper类下:
//添加操作:返回自增id
int insert(UserEntity user);
//UserMapper.xml:
<insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into userinfo(username,password,photo)values(#{username},#{password},#{photo})
</insert>
//单元测试:
@Test
void insert() {
UserEntity user = new UserEntity();
user.setUsername("李四");
user.setPassword("123");
user.setPhoto("");
//因为在接口中声明的 insert 方法的参数为 user,所以测试的时候可以直接将 user对象传给这个insert方法
int result = userMapper.insert(user);
System.out.println("受影响的行数: "+result+" | id: "+user.getId());
}
在XML文件中的insert
标签中添加useGenerateKeys
,keyColumn
和keyProperty
属性。
useGenerateKeys
: 表示获取数据库中开启自增主键的值。在insert标签中表示的意思为获取本次添加的成员的自增主键的值。默认值为false.
keyColumn
: 表示设置自增主键在数据表中的字段名。
keyProperty
: 表示将获取到的自增主键的值赋值给keyProperty所指的属性(实体类).
修改的实现和删除一样在xml文件中的update标签中不用设置返回值类型(resultMap或者resultType),默认的返回值是受影响的行数,所以在UserMapper接口中声明方法的时候,返回值类型为int。
//UserMapper类下:
//修改操作
int update(UserEntity user);
//UserMapper.xml:
<update id="update">
update userinfo set username=#{username} where id=#{id}
</update>
//单元测试:
@Test
void update() {
UserEntity user = new UserEntity();
user.setId(1);
user.setUsername("管理员");
int result = userMapper.update(user);
System.out.println("受影响的行数"+result);
}
删除信息,默认返回的是受影响的行数,所以我们在声明方法的时候设置的返回值类型为int.
//UserMapper类下:
//删除操作
int delById(@Param("id")Integer id);
//UserMapper.xml:
<delete id="delById">
delete from userinfo where id=#{id}
</delete>
//单元测试:
@Test
void delById() {
int id = 2;
int result = userMapper.delById(id);
System.out.println("受影响的行数:"+result);
}
MyBatis中开启事务的注解:@Transactional
like查询,我们按照学习MySQL是使用的语法在XML文件中构造sql语句,在执行的时候会出现报错的问题。
//UserMapper类下:
//like查询
List<UserEntity> getLikeList(@Param("username")String username);
//UserMapper.xml:
<select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where username like %#{username}%
</select>
//单元测试:
@Test
void getLikeList() {
String username = "三";
List<UserEntity> list = userMapper.getLikeList(username);
System.out.println(list);
}
我们可以看到,使用预编译的方式不行,那我们可以尝试使用即时执行的方式,这种方式执行确实结果是对的,但是这里使用即时执行的方式并不满足使用它的条件,会出现sql注入的情况。
解决方案:
第一种解决方法是在XML中继续直接使用#{username}
,我们在业务代码中给username赋值为%三%
//UserMapper类下:
//like查询
List<UserEntity> getLikeList(@Param("username")String username);
//UserMapper.xml:
<select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where username like #{username}
</select>
//单元测试:
@Test
void getLikeList() {
String username = "%三%";
List<UserEntity> list = userMapper.getLikeList(username);
System.out.println(list);
}
虽然这种方式可以解决问题,但是在业务代码中写,看起来就是不太好看。
第二种解决方法是使用SQL语法中的concat
字段,对多个字符进行拼接。
//UserMapper类下:
//like查询
List<UserEntity> getLikeList(@Param("username")String username);
//UserMapper.xml:
<select id="getLikeList" resultType="com.example.demo.entity.UserEntity">
select * from userinfo where username like concat('%',#{username},'%')
</select>
//单元测试:
@Test
void getLikeList() {
String username = "三";
List<UserEntity> list = userMapper.getLikeList(username);
System.out.println(list);
}
这种写法既不会出现单引号套单引号的问题,在业务代码中进行like查询传参的时候,也没有出现使用多余的符号的问题。
MyBatis是通过实体类的属性名称和数据库中的字段名进行映射的,如果实体类中的属性名和数据库表中的字段名不同,在进行查询的时候,出现的结果中字段的值会为null.
解决方法:
将实体类中的属性名修改成和数据库表中的数据修改成一致的。这种方式只适合于当前这个实体类,只有你一个人使用了,如果其他人的代码中也使用了你创建的实体类,那么就不能使用这种方式来修改了。
使用SQL语句中的as对数据表中的字段名进行重命名,让字段名等于创建的实体类的属性名。
<select id="getAllByOrder" resultType="com.example.demo.entity.UserEntity">
select id,username as name from userinfo order by id ${myorder}
</select>
定义一个resultMap
,将属性名和字段名进行手动映射。
这里我们查询一篇文章对应的作者的名字,站在文章的角度进行多表联合查询就是一对一的情况。
使用注解的方式在MyBaits程序中构造SQL语句,我们想要使用SQL的查询,就可以在接口中的方法上加上注解@Select
,想要使用删除,可以在接口的方法上添加@Delete
,想要使用插入可以在方法上添加@Insert
,想要实现修改可以在方法上添加@Update
,然后将要执行的sql语句写在这些注解的参数中即可。
package com.example.demo.entity;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class ArticleInfo {
private int id;
private String title;
private String content;
private LocalDateTime createtime;
private LocalDateTime updatetime;
private int uid;
private int rcount;
private int state;
//链表字段
private String username;
}
package com.example.demo.mapper;
import com.example.demo.entity.ArticleInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface ArticleMapper {
@Select("select articleinfo.*,userinfo.username from articleinfo left join userinfo on articleinfo.uid=userinfo.id")
List<ArticleInfo> getAll();
}
package com.example.demo.mapper;
import com.example.demo.entity.ArticleInfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
class ArticleMapperTest {
@Autowired
private ArticleMapper articleMapper;
@Test
void getAll() {
List<ArticleInfo> list = articleMapper.getAll();
System.out.println(list);
}
}
运行结果:
一对多的多表查询,这里我们将查询步骤分为三步:
userinfo
类(用户实体类)中添加一个alist
属性,最后用来将得到文章信息组装到userinfo对象中。package com.example.demo.model;
import lombok.Data;
import java.time.LocalDateTime;
import java.util.List;
@Data
public class Userinfo {
private int id;
private String username;
private String password;
private String photo;
private LocalDateTime createtime;
private LocalDateTime updatetime;
private int state;
private List<ArticleInfo> alist;
}
UserinfoMapper
类和ArticleMapper
类中添加查询的方法package com.example.demo.entity;
import lombok.Data;
import java.time.LocalDateTime;
import java.util.List;
@Data
public class Userinfo {
private int id;
private String username;
private String password;
private String photo;
private LocalDateTime createtime;
private LocalDateTime updatetime;
private int state;
private List<ArticleInfo> alist;
}
UserMapperTest1
单元测试类中创建一个getUserList
方法,在这个方法中调用上述两个方法,最后调用setAlist
方法,将getListByUid
方法中得到的文章列表添加到userinfo
对象中,就完成了多表查询的一对多的情况package com.example.demo.mapper;
import com.example.demo.entity.ArticleInfo;
import com.example.demo.entity.Userinfo;
import com.example.demo.entity.ArticleInfo;
import com.example.demo.entity.Userinfo;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest//不能省略,告诉当前的测试程序,目前项目是运行在Spring容器中的
class UserMapperTest1 {
@Autowired
private UserinfoMapper userinfoMapper;
@Autowired
private ArticleMapper articleMapper;
@Test
void getUserList(){
int uid = 1;
//1.根据uid查询userinfo
Userinfo userinfo = userinfoMapper.getUserById2(uid);
//2.根据uid查询文章列表
List<ArticleInfo> list = articleMapper.getListByUid(uid);
//3.组装数据
userinfo.setAlist(list);
System.out.println(userinfo);
}
}
动态sql是MyBatis的强大特性之一,能够完成不同条件下不同的sql拼接。
我们在上网时,经常需要填写一些表单,其中有些选项是必填的,有些是选填的,那么这个时候在MyBatis程序中按照XML的方式构造sql语句时,是不能完全胜任的。比如填通讯信息的时候,出现了一个选填项是填写QQ号,如果不填这个选项,前端传给后端代码中的这个数据的值为null,现在规定让这一项在数据库中默认为空,如果如不使用标签,那么在XML中是无法完成这个规定。在数据库中null和空是两个概念。
语法:
<!-- test中的表达式是满足使用多个条件 -->
<if test="表达式">
<!-- 满足表达式的条件,就会进入执行其中的内容 -->
....
</if>
//动态sql添加操作<if>
int add2(Userinfo userinfo);
<insert id="add2">
insert into userinfo(username,password
<if test="photo != null">
,photo
</if>
)values(#{username},#{password}
<if test="photo != null">
,#{photo}
</if>
)
</insert>
//给对象的属性设置值得时候,给photo属性添加值
@Test
void add2() {
Userinfo userinfo = new Userinfo();
userinfo.setUsername("张三");
userinfo.setPassword("123");
userinfo.setPhoto("cat.png");
int result = userMapper.add2(userinfo);
System.out.println("执行的结果: "+result);
}
可以看到如果给photo没有设置值,那么在数据看中photo这一列是空的,不会出现null.这就解决了表单中可选项的填写问题了。如果填了表单中的可选项就会将值保存在数据库中,如果没有填写可选项,那么数据库中这个字段就不会有值。
上面我们说的表单中存在某个选填项,假设表单上所有的选项都是选填的,那么使用<if>
标签就不能满足我们的需求了。因为在判断给字段是否传值时,使用<if>
标签将字段包裹起来了,但是字段和字段之间要使用,
逗号隔开,所以我们还需要将逗号拼接上。但是我们不知道用户选填了那些字段,所以将逗号拼接上之后,还需要考虑逗号不能出现在开始的字段前面,结束的字段后面不能出现逗号。这个就需要使用<trim>
标签中的属性来解决了。
<trim>
标签的属性:
prefix
:表示整个语句块,以prefix的值作为前缀
suffix
:表示整个语句块,以suffix的值作为后缀
prefixOverrides
:表示整个语句块要去除掉的前缀
suffixOverrides
:表示整个语句块要去除掉的后缀
/*
* 动态sql <trim>标签
* */
int add3(Userinfo userinfo);
<trim>
标签中的prefix
和suffix
属性可以添加整个语句块的前缀和后缀,所以这里我们直接使用这两个属性拼接括号,我们在<if>
标签中将逗号拼接在字段的后面,使用suffixOverrides
属性指定要去除语句块中某个后缀(逗号),整个时候就会将语句块中最后一个字段之后的逗号去掉。 <insert id="add3">
insert into userinfo
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username != null">
username,
</if>
<if test="password != null">
password,
</if>
<if test="photo!=null">
photo,
</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username != null">
#{username},
</if>
<if test="password != null">
#{password},
</if>
<if test="photo != null">
#{photo},
</if>
</trim>
</insert>
@Test
void add3() {
Userinfo userinfo = new Userinfo();
userinfo.setUsername("李四");
userinfo.setPassword("666");
int result = userMapper.add2(userinfo);
System.out.println("执行的结果: "+result);
}
MyBatis 中多个都是非必传参数的解决方案:
<trim>
标签解决:
<where>
标签方案:
注意: <where>
只会自动帮你去除最前面的 and 关键字,使用 where 标签不会自动帮你去除最后面的and关键字.
MyBatis中,<where>
标签并不只能用于生成 AND 条件,它可以用于生成任何类型的条件语句(包括AND和OR)。
例如:
<select id="getUserList" parameterType="Map" resultType="User">
SELECT *
FROM users
WHERE age = #{age}
<where>
<if test="name != null">
OR name = #{name}
</if>
</where>
</select>
上述示例中,无论age的值是什么,都会根据name的值生成OR条件。
<set>
标签和<where>
标签在sql语句中添加方式相同,只不过where标签用在查询,set标签用在修改。但是<set>
标签是去掉代码块的后缀的,而<where>
标签是去掉代码块的前缀的。使用<set>
标签可以避免在更新操作中出现多余的逗号和无效的更新字段。
<update id="updateUser" parameterType="User">
UPDATE users
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="email != null">
email = #{email},
</if>
</set>
WHERE id = #{id}
</update>
在上述示例中,<set>
标签用于动态生成set子句。根据传入的参数值判断是否生成相应的更新字段,如果参数值为null,则不会生成相应的更新语句。
注意,在生成set子句时,每个更新字段末尾都会有一个逗号,即使是最后一个字段。这是因为在动态SQL中,可以通过条件判断来控制是否生成该字段,但为了简化逻辑和代码,可以在每个字段之后都加上逗号,不影响SQL的语法正确性。
另外,需要注意使用占位符(如#{name}
)来引用参数值,而不是直接拼接参数值。这样可以避免SQL注入攻击和确保参数值的正确性。
通过使用<set>
标签,可以根据条件动态生成UPDATE语句中的字段和对应的值,提高灵活性并避免不必要的逗号和无效的更新字段。
在MyBatis中,<foreach>
标签用于循环遍历集合或数组,并将其中的元素逐个应用到SQL语句中的特定位置,以便生成动态SQL。
<foreach>
标签通常与动态SQL一起使用,可以在in子句中动态生成多个值或者在批量插入/更新操作中循环处理多个数据。
下面是一个使用<foreach>
标签的示例:
<select id="getUserByIdList" parameterType="List" resultType="User">
SELECT *
FROM users
WHERE id IN
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
在上述示例中,<foreach>
标签将会循环遍历传入的List类型参数list,并将每个元素存储到id变量中。循环体中的#{id}
表示动态插入当前迭代的值。
生成的SQL语句可能类似于以下形式(假设list包含 [1, 2, 3]):
SELECT *
FROM users
WHERE id IN (1, 2, 3)
<foreach>
标签的常用属性:
collection
:指定要遍历的集合或数组。
item
:指定当前元素的别名。
index
:指定当前元素的索引。
open
:指定循环开始时的字符。
close
:指定循环结束时的字符。
separator
:指定每个元素之间的分隔符。
需要注意的是,<foreach>
标签也可以用于批量插入或更新操作中,通过循环处理多个数据。此时,可以将循环体中的SQL片段放置在合适的位置来重复执行插入或更新。
通过使用<foreach>
标签,可以实现对集合或数组的循环遍历,动态生成包含多个值的SQL语句,并在动态SQL中灵活地处理多个数据。
Generate