|
1.较复杂的查询操作
1.1 参数占位符 #{} 和 ${}
:预处理符,如将替换为,然后使用替换。:替换符,如将替换为。
两种占位符都可以正常使用的场合:传入的参数类型是数值类型
使用:- select * from userinfo where id=${id}
- select * from userinfo where id=2
复制代码 使用:- select * from userinfo where id=#{id}
- select * from userinfo where id=?
复制代码 对于这两种参数占位符,个人建议能使用就使用,因为存在SQL注入的问题,以及如果传入的类型是字符串也会出类型。
只能使用而不能使用的场合:传入参数类型为String
使用:- select * from userinfo where username=${username}
- //实际执行的语句
- Preparing: select * from userinfo where username=张三
复制代码 但是在sql中通过字符串来查询数据,是需要加上引号的,而使用生成的sql并没有带引号,因此不适用于字符串参数的sql。
使用:- select * from userinfo where username=#{username}
- //实际执行语句
- select * from userinfo where username=?
复制代码 由于使用是将目标参数替换为占位符,然后利用JDBC中的占位符机制实现sql语句的填充,所以使用构造的sql是可以正常运行的,并且没有SQL注入的问题。
所以,相比于,它支持所有类型的参数,包括数值类与字符串类,而支持数值类型,不支持字符串类型的参数,但也可以在原来sql里面为外面加上一对引号。- select * from userinfo where username='${username}'
- //实际执行语句
- Preparing: select * from userinfo where username='张三'
复制代码 当传递的参数为字符串类型的时候,虽然加上一对引号,使用也可以做到,但是存在SQL注入问题,所以仍然不推荐,有关SQL注入后面我们会介绍到。
大部分场合下,使用都可以解决,但还是存在一小部分只能是来处理的。
如当我们需要按照升序或者逆序得到数据库查询结果的时候,这种场合就只能使用,使用会报错,我们来演示一下。
首先,我们在接口中声明一个方法:作用就是按照排序获取结果集- public List<UserInfo> getOrderList(@Param(value = "order") String order);
复制代码 我们再去文件中去写sql语句:首先我们使用进行演示- <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
- select * from userinfo order by createtime ${order};
- </select>
复制代码 我们进行一个单元测试,单元测试代码很简单,就是调用sql,然后得到结果集:- @Test
- void getOrderList() {
- List<UserInfo> userMappers = userMapper.getOrderList("desc");
- System.out.println(userMappers);
- }
复制代码 单元测试结果:
可以正常查询,得到的结果与预期也是相同的。
我们再来试一试使用来构造sql语句:- <select id="getOrderList" resultType="com.example.demo.model.UserInfo">
- select * from userinfo order by createtime #{order};
- </select>
复制代码 单元测试逻辑与代码不变,我们再来看看单元测试执行的一个结果:
我们发现程序报错了,这是因为使用了的字符串替换了占位符,而我们所需要的不是一个字符串,而是直接一个的关键字,所以sql也抛出了语法错误,最终执行的sql为:- select * from userinfo order by createtime ‘desc';
复制代码 期望执行的sql为:- select * from userinfo order by createtime desc;
复制代码 所以在传递sql关键字的时候,不能使用,只能使用。
1.2SQL注入
SQL注入就是使用,使用一些特殊的语句,来达到非法获取数据的目的,如不通过正确的密码获取某账户的信息,下面我们来演示一下,就以登录的例子来演示,SQL注入可以在不知道密码的前提下登录成功,并且获取到用户的相关信息。
首先我将数据库只保留一个用户信息,目的是为了方便演示SQL注入问题:
第一步,在接口中定义方法,返回登录成功的用户对象。- public UserInfo login(@Param("username") String username, @Param(("password")) String password);
复制代码 第二步,在文件中编写SQL语句,我们需要演示SQL注入,所以我们使用来构造sql语句。- <select id="login" resultType="com.example.demo.model.UserInfo">
- select * from userinfo where username='${username}' and password='${password}';
- </select>
复制代码 第三步,编写测试类,我们在这个测试类中,传入有注入问题的SQL语句,使得不需要密码就能拿到相关的用户信息。- @Test
- void login() {
- String username = "admin";
- String password = "' or 1='1";
- UserInfo userInfo = userMapper.login(username, password);
- System.out.println(userInfo);
- }
复制代码 单元测试运行结果:
我们在不知道用户密码的情况下,登录成功,并拿到了用户的信息。
最终执行的一段sql语句为:- select * from userinfo where username='admin' and password='' or 1='1';
复制代码 相当于它在原来条件判断的语句下,后面有加上一个或的逻辑,并且或后面的表达式为,这样就使得原来的SQL语句中的条件判断部分一定为真,所以就在密码不知道的情况下拿到了用户的基本信息。
所以我们能不使用就不使用,因为存在SQL注入问题,如果必须使用则需要验证一下传递的参数是否合法,比如上面定义排序的sql,传递的参数只能是或者是,如果不是就不能执行这条SQL,防止SQL注入的发生。
1.3like查询
在Mybatis中使用查询比较特殊,因为直接使用会报错,而使用,由于输入的字符串情况很多,无法做到枚举,验证比较困难,无法避免SQL注入问题。
首先,我们来演示使用进行查询,步骤我就不详细写了,就是查询的步骤。
第一步,声明方法。- public List<UserInfo> getListByName(@Param("username") String username);
复制代码 第二步,xml写sql。- <select id="getListByName" resultType="com.example.demo.model.UserInfo">
- select * from userinfo where username like '%#{username}%'
- </select>
复制代码 第三步,单元测试。- @Test
- void getListByName() {
- String username = "a";
- List<UserInfo> list = userMapper.getListByName(username);
- for (UserInfo userInfo : list) {
- System.out.println(userInfo);
- }
- }
复制代码 运行结果:报错了,因为会被替换成一个字符串,而在这个语句中不能带上引号,带上就违背SQL语法,造成错误。
这个时候,由于多出一对引号,无法枚举所有情况进行验证会产生SQL注入,所以不能直接使用,我们需要搭配MySQL内置的字符串拼接语句。
我们将sql改为进行字符串拼接:- <select id="getListByName" resultType="com.example.demo.model.UserInfo">
- select * from userinfo where username like concat('%', #{username}, '%')
- </select>
复制代码 重新测试一下,发现可以正常执行了:
1.4resultType与resultMap
resultType表示数据库返回的数据映射在java程序中所对应的类型,只要定义类中的字段名与数据库中表的字段名字一致就没有任何问题,但是如果字段名存在冲突,则冲突的字段无法获取到数据库查询的结果。
比如用户名属性在数据库中的名字是,而在java程序类中的属性名为,此时通过mybatis将数据传递到程序中的对象时,获取到的属性为,就不能正确地获取到对应的属性值,为了解决这个数据库字段与类中中字段不匹配的问题,我们需要使用到resultMap。
resultMap的使用方式就是在文件中设置标签,至少需要设置两个属性,一个是表示你这个resultMap标签的名字,还有一个是属性,它表示映射到程序中类的类型,需包含包名。
这个标签里面需要设置至少两个子标签,一个是标签,另外一个是标签,前者表示主键,后者表示数据库表中普通的列,这两种标签也是至少需要设置两个属性,一个是表示数据库表中的字段名,另外一个是表示程序类中的字段名,如果只是在单表进行查询,只设置不同字段名的映射就可以了,但是如果是多表查询,必须将数据表中所有的字段与类中所有的字段生成映射关系。
就像下面这样,图中类与数据表字段是相同的,实际情况会存在不同的字段名:
1.4多表查询
1.4.1一对一表映射
一对一关系就是对于一个属性只与另外一个属性有关系的映射,这就是一对一的关系,举个例子,对于一篇博客,它只会对应到一个用户,则博客与用户的关系是一对一的关系,下面我们尝试在mybatis中实现一对一多表联查。
那么,首先我们先将数据库的博客表与查程序中的博客类对应起来,就是按照数据库中的博客表建立一个类:
- @Data
- public class Articleinfo {
- private Integer id;
- private String title;
- private String content;
- private String createtime;
- private String updatetime;
- private Integer uid;
- private Integer rcount;
- private Integer state;
- //不妨多一个属性,用户表
- private UserInfo userInfo;
- }
复制代码 目前文章表中只有一条数据,如下图:
第二步,创建接口和对应的文件。
第三步,在接口中声明方法和在xml中写sql标签与语句。- //根据文章名称获取文章对象
- public Articleinfo getArticleById(@Param("id") Integer id);
复制代码- <select id="getArticleById" resultType="com.example.demo.model.Articleinfo">
- select * from articleinfo where id=#{id};
- </select>
复制代码 第四步,编写测试方法,我们直接调用查询方法,然后使用日志输出对象。- @Test
- void getArticleById() {
- Articleinfo articleinfo = articleMapper.getArticleById(1);
- log.info("文章详情:" + articleinfo);
- }
复制代码 由于我们数据表与类的字段名是一致的,那些普通的属性都一一对应上了,都成功被赋值了,但是由于类在数据表中没有,所以并没有得到UserInfo对象,如果我们想要拿到这个对象,我们得使用。
问题主要有两个,第一,数据库查询到的用户表没有映射到对象,第二,查询的SQL语句是单表查询语句,不是多表查询语句。
所以想要实现一对一多表查询,需要设置多表查询SQL语句,我们使用左外连接进行多表查询:- <select id="getArticleById" resultMap="BaseMap">
- select a.*, u.* from articleinfo as a left join userinfo as u on a.uid=u.id where a.id=#{id};
- </select>
复制代码 此外,我们除了设置与类中每个属性与数据表的映射之外,我们还要在类对应的中使用标签。最少需要设置两个属性,一个是表示在主表中对应副表映射对象的变量名,另外一个是副表对应的。类对应的resultMap:- <resultMap id="BaseMap" type="com.example.demo.model.Articleinfo">
- <id column="id" property="id"></id>
- <result column="title" property="title"></result>
- <result column="content" property="content"></result>
- <result column="createtime" property="createtime"></result>
- <result column="updatetime" property="updatetime"></result>
- <result column="uid" property="uid"></result>
- <result column="rcount" property="rcount"></result>
- <result column="state" property="state"></result>
- <association property="userInfo" resultMap="com.example.demo.mapper.UserMapper.BaseMap"></association>
- </resultMap>
复制代码 类对应的resultMap:- <resultMap id="BaseMap" type="com.example.demo.model.UserInfo">
- <!-- column 表示数据库字段名 property 表示对应对象的字段名,设置这两个值可以建立映射-->
- <!-- 主键约束-->
- <id column="id" property="id"></id>
- <!--普通变量映射-->
- <result column="username" property="username"></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>
- <result column="state" property="state"></result>
- </resultMap>
复制代码 如果类的resultMap没有将所有的属性都与数据库的表映射,就会造成获取到的对象中的数据不完整,假设只设置了与的映射,那获取到的对象只有与有值。
将两张表的resultMap映射好后,我们运行同样的单元测试案例,运行结果如下:
但是,仍然存在一个问题,那就是我们所建的两个表存在名字相同的字段,可能会出现数据覆盖的情况,如两个表的主键都叫,但是在两个表的含义是不同的,在用户表它表示用户,在文章表它表示文章的,现在我们将获取两表的数据的改为不相同,再来看一看单元测试运行的结果:
按理来说,由于不存在为的用户,所以获取到对象应该为才对,但是运行的结果却存在对象,并且与文章表的重名字段都被赋值了文章表中的数据,为了解决这个问题,我们必须在文章表(主表)的中设置属性,它的值随便设置,作用是识别副表字段时加上一段前缀,如我们给用户表的字段加上前缀,此时sql中就不能使用来一次表示所有元素了,需要一个一个单独设置,并将字段全部重命名,带上前缀 。字段设置:- <association property="userInfo" columnPrefix="u_" resultMap="com.example.demo.mapper.UserMapper.BaseMap" ></association>
复制代码 SQL语句需要将用户表的字段全部重命名:- <select id="getArticleById" resultMap="BaseMap">
- select a.*, u.id as u_id,
- u.username as u_username,
- u.password as u_password,
- u.photo as u_photo,
- u.createtime as u_createtime,
- u.updatetime as u_updatetime,
- u.state as u_state
- from articleinfo as a left join userinfo as u on a.uid=u.id where a.id=#{id};
- </select>
复制代码
我们将对应的用户表的再改回为,让查询有关于类的数据。
再次运行单元测试案例:
我们是能够获取到相应的数据的,所以如果两个表字段重名了,进行多表查询时,需要设置属性,这样才能够避免不同表同名字段数据覆盖的问题。
所以,在创建数据库的数据表时,尽量不要让表与表中的字段重名。
1.4.2一对多表映射
一对多的关系,就是对于一个属性,它对映着多个其他的属性,比如用户与博客之间的关系,一个用户可以对应多篇博客,则用户与博客之间的关系就是一对多的关系。同样的下面我们尝试使用mybatis实现多对多的多表联查。
下面我们以用户表为主,文章表为辅,来演示如何进行一对多关系的多表查询。
既然是一对多的关系,那我们可以在类中加上一个储存对象的,来储存用户发布或所写的文章。- @Data
- public class UserInfo {
- private Integer id;
- private String username;
- private String password;
- private String photo;
- private String createtime;
- private String updatetime;
- private Integer state;
- private List<Articleinfo> aList;
- }
复制代码 实现多表查询的大致过程如下:
- 在Mapper接口中声明方法,我们声明一个方法,就是通过用户id获取用户信息以及对应的文章列表。
- 在文件当中写映射关系,与一对一多表查询不同的是,我们需要设置标签,而不是标签。
- 在文件的标签中至少设置resultMap名字,对应映射的类等属性,里面需要设置数据表与类中所有字段的映射,以及设置标签,需要设置属性表示需映射的对象名,设置resultMap即副表的路径,由于你无法保证表与表之间是否存在重名字段,需要设置为副表的字段添加上一个前缀,防止重名数据覆盖。
- <collection
- property="aList"
- resultMap="com.example.demo.mapper.ArticleMapper.BaseMap"
- columnPrefix="a_">
- </collection>
复制代码 在对应的文件当中写SQL标签以及语句。- <select id="getUserAndArticlesById" resultMap="BaseMap">
- select u.*,
- a.id as a_id,
- a.title as a_title,
- a.content as a_content,
- a.createtime as a_createtime,
- a.updatetime as a_updatetime,
- a.uid as a_uid,
- a.rcount as a_rcount,
- a.state as a_state
- from userinfo as u left join articleinfo as a on u.id=a.uid where u.id=#{uid}
- </select>
复制代码 编写单元测试代码,测试代码是否编写正确。- @Test
- void getUserAndArticlesById() {
- Integer id = 1;
- UserInfo userInfo = userMapper.getUserAndArticlesById(id);
- log.info("用户信息:" + userInfo);
- }
复制代码 运行结果:
2.动态SQL
首先来说一下什么是动态SQL,官方文档对于动态SQL的定义是:
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
前面所说的mybatis增删查改,那些传入的参数都是一定会传入的,但是在实际情况中,很多参数都是非必传参数,使用动态SQL就可以解决传入的参数是非必传参数的情况。
动态SQL可以解决多余符号的问题,如等。
2.1if标签
标签的作用就是判断一个参数是否有值,如果没有值就将对应的参数隐藏。
语法:- <if test="表达式">
- sql
- </if>
- //例如
- <if test="参数!=null">
- sql部分语句
- </if>
复制代码 当表达式为真,则插入标签中的,否则不插入。
我们以在用户表中插入一条数据为例,插入的数据中头像不是必传的参数:
方法声明:- //使用动态sql插入数据
- public int addUser(UserInfo userInfo);
复制代码 动态SQL语句:
其中的是非必传参数,我们使用标签来判断它是否有值,没有值就不插入目标的SQL语句。- <insert id="addUser">
- insert into userinfo(username, password
- <if test="photo!=null">
- , photo
- </if>
- ) values(#{username}, #{password}
- <if test="photo!=null">
- , #{photo}
- </if>
- )
- </insert>
复制代码 单元测试代码:- @Test
- void addUser() {
- UserInfo userInfo = new UserInfo();
- userInfo.setUsername("张三疯");
- userInfo.setPassword("123456");
- int res = userMapper.addUser(userInfo);
- log.info("受影响的行数为:" + res);
- }
复制代码 在单元测试代码中,我没有给photo赋值,if标签会判断它为空,不会插入对应photo的SQL,因此插入数据为默认值。
结果:
数据库查询结果:
再来试一试给photo传值的情况,它生成的SQL有三个参数:- @Test
- void addUser() {
- UserInfo userInfo = new UserInfo();
- userInfo.setUsername("张无忌");
- userInfo.setPassword("12345611");
- userInfo.setPhoto("张无忌.png");
- int res = userMapper.addUser(userInfo);
- log.info("受影响的行数为:" + res);
- }
复制代码 运行结果:
最终生成的语句多了一个参数。
2.2trim标签
前面所说的标签可以实现非必传参数SQL的构造,在极端情况下,有很多个非必传参数,此时如果只使用标签构造出的SQL语句很有可能会多出一个,因为有很多非必传参数,如果只传来一个参数,由于不确定后面是否还会有参数,因此会预留一个,此时如果没有其他参数,就会多出一个参数。
而标签可以做到这一点,它可以去除SQL语句前后多余的某个字符,它需要搭配标签使用。标签中有如下属性:
- prefix:表示整个语句块,以prefix的值作为前缀
- suffix:表示整个语句块,以suffix的值作为后缀
- prefixOverrides:表示整个语句块要去除掉的前缀
- suffixOverrides:表示整个语句块要去除掉的后缀
语法:- <trim prefix="前缀符", suffix="后缀符", prefixOverrides="去除多余的前缀字符", suffixOverrides="去除多余的后缀字符">
- <if test="表达式">
- ...
- </if>
- ...
- ...
- </trim>
复制代码 假设都是非必传参数,但是至少传递一个,我们来写插入语句的动态SQL。- <insert id="addUser2">
- insert into userinfo
- <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
- <if test="username!=null">
- username,
- </if>
- <if test="password!=null">
- password,
- </if>
- <if test="photo!=null">
- photo
- </if>
- </trim>
- values
- <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
- <if test="username!=null">
- #{username},
- </if>
- <if test="password!=null">
- #{password},
- </if>
- <if test="photo!=null">
- #{photo}
- </if>
- </trim>
- </insert>
复制代码 单元测试代码:- @Test
- void addUser2() {
- UserInfo userInfo = new UserInfo();
- userInfo.setUsername("wangwu");
- userInfo.setPassword("12345622");
- int res = userMapper.addUser(userInfo);
- log.info("受影响的行数为:" + res);
- }
复制代码 运行结果与生成的SQL语句:
我们发现逗号它自动去除了。
2.3where标签
标签主要是实现关键字的替换,如果SQL中没有使用到(没有查询条件),就会隐藏,存在查询条件,就会生成含有where的查询SQL语句,并且可以去除前面的。
我们就不以复杂的案例作为演示了,直接写一个最简单的查询,为了简单,我们删除数据库的其他数据,只留一条数据。
下面我们来写最简单的查询语句,就是根据获取一个用户信息。
写动态SQL时,我故意在最前面写一个来证明标签是可以自动删除前面多余的。- <select id="getUserById" resultType="com.example.demo.model.UserInfo">
- select * from userinfo
- <where>
- <if test="id!=null">
- and id=#{id}
- </if>
- </where>
- </select>
复制代码 单元测试代码:- @Test
- void gerUserById() {
- UserInfo userInfo = userMapper.getUserById(1);
- System.out.println(userInfo);
- //Assertions.assertNotNull(userInfo);
- }
复制代码 结果:
发现自动生成了where语句并删除了多余的。
如果我们查询一个值,就不会生成语句。
以上标签也可以使用- <trim prefix="where" prefixOverrides="and">
复制代码 替换。
2.4set标签
其实标签与标签很相似,只不过用来替换查询SQL,用于修改SQL中,用来自动生成部分的SQL语句。标签还可以自动去除最后面的一个。
比如我们写一个能够修改账户名,密码,头像的动态SQL,根据进行修改。
方法声明:- //使用动态SQL实现修改用户信息,包括账户名,密码,头像
- public int updateUser(UserInfo userInfo);
复制代码 动态SQL:- <update id="updateUser">
- update userinfo
- <set>
- <if test="username!=null">
- username=#{username},
- </if>
- <if test="password!=null">
- password=#{password},
- </if>
- <if test="photo!=null">
- photo=#{photo}
- </if>
- </set>
- where id=#{id}
- </update>
复制代码 单元测试代码:- @Test
- void updateUser() {
- UserInfo userInfo = new UserInfo();
- //修改密码为123456
- userInfo.setPassword("123456");
- userInfo.setId(1);
- int res = userMapper.updateUser(userInfo);
- log.info("受影响的行数:" + res);
- }
复制代码 运行结果:
修改成功并且可以根据传入参数个数自动生成相应的修改SQL,以及可以自动去除最后的。
以上标签也可以使用- <trim prefix="set" suffixOverrides=",">
复制代码 替换。
2.5foreach标签
对集合进行遍历可以使用标签,常用的场景有批量删除功能。
- collection:绑定方法参数中的集合,如 List,Set,Map或数组对象
- item:遍历时的每一个对象
- open:语句块开头的字符串
- close:语句块结束的字符串
- separator:每次遍历之间间隔的字符串
为了方便演示批量删除,我们随便插入几条数据到数据库:
方法声明:- //使用动态sql批量删除元素
- public int deleteIds(List<Integer> ids);
复制代码 动态SQL语句:- <delete id="deleteIds">`在这里插入代码片`
- delete from userinfo where id in
- <foreach collection="ids" open="(" close=")" separator="," item="id">
- #{id}
- </foreach>
- </delete>
复制代码 单元测试代码:
删除数据库中为10 11 12的用户。- @Test
- void deleteIds() {
- List<Integer> ids = new ArrayList<>();
- ids.add(10);
- ids.add(11);
- ids.add(12);
-
- int res = userMapper.deleteIds(ids);
- log.info("受影响的行数" + res);
- }
复制代码 运行结果:
成功生成了批量删除的SQL,这就是foreach标签的作用,它能够遍历集合。
总结
到此这篇关于Mybatis多表查询与动态SQL特性的文章就介绍到这了,更多相关Mybatis多表查询与动态SQL内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!
来源:https://www.jb51.net/article/266737.htm
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|