mybatis动态sql

动态sql注入

第一：编写一个实体类

package com.zzy.pojo;

import lombok.Data;

import java.util.Date;
@Data
public class Blog {
    private String id;
    private  String title;
    private String author;
    private Date createTime;
    private int views;

}

第二：编写一个dao层

package com.zzy.dao;

import com.zzy.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper {
     int  addBlog(Blog blog);
     //IF查询
     List<Blog> queryBlogIf(Map map);
     //choose查询语句
     List<Blog> queryBlogChoose(Map map);
     //where查询语句
     List<Blog> queryBlogWhere(Map map);
     //set更新语句
     int updateBlog(Map map);
     //foreach查询
     List<Blog> queryBlogForeach(Map map);

}

编写一个对应的BlogMapper.xml

<?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.zzy.dao.BlogMapper">
    <insert id="addBlog" parameterType="Blog">
        insert into blog(id,title,author,create_time,views)values (#{id},#{title},#{author},#{createTime},#{views});


    </insert>
    <!--    if语句的查询-->
    <!--    <select id="queryBlogIf" parameterType="map" resultType="Blog">-->
    <!--&#45;&#45; 这里的1=1是保证SQL语句能顺利的执行 保证能执行if不会错误-->
    <!--        select  *from blog where 1=1-->
    <!--        <if test="title !=null">-->
    <!--            and title=#{title}-->
    <!--        </if>-->
    <!--        <if test="author !=null">-->
    <!--            and author=#{author}-->
    <!--        </if>-->
    <!--    </select>-->


    <!--    where语句的查询  <where></where> 这里的where语句 直接替代上面if中的 1=1情况 多种条件的时候where 会自动匹配and
     他会自动的识别 拼接sql语句的where 最后面语句拼接and-->
    <select id="queryBlogWhere" resultType="Blog" parameterType="map">
        select *from blog
        <where>
            <if test="title !=null">
                title=#{title}
            </if>
            <if test="author !=null">
                and author=#{author}
            </if>
        </where>

    </select>
    <!-- choose-->
    <select id="queryBlogChoose" resultType="Blog" parameterType="map">
        select *from blog
        <where>
            <choose>
                <when test="title!=null">title=#{title}</when>
                <when test="author!=null">author=#{author}</when>
                <otherwise>and views =#{views}</otherwise>

            </choose>

        </where>

    </select>
    <!--set更新-->
    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title!=null">
                title=#{title},
            </if>
            <if test="author!=null">
                author=#{author}
            </if>
            where id=#{id}

        </set>

    </update>
    <!--    sql 片段 抽取公共的sql片段 -->
    <sql id="if-title-author">
        <if test="title!=null">
            title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </sql>
    <select id="queryBlogIf" parameterType="map" resultType="Blog">

        select *from blog
        <where>
            -- refid=sql片段中的id 引用 （include标签）
            <include refid="if-title-author"></include>
        </where>

    </select>
    <!--    foreach 注意and后面一定要有空格否则报错 -->
    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select *from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

</mapper>