Mybatis的动态SQL语句（insert,update,select语句)

最新推荐文章于 2024-09-22 23:52:03 发布

努力更新中

最新推荐文章于 2024-09-22 23:52:03 发布

阅读量2.2k

点赞数 2

文章标签： mybatis sql java

本文链接：https://blog.csdn.net/m0_70545163/article/details/133612805

版权

摘要：这篇文章，我将讲讲mybatis中关于插入（insert）、更新（update）、查询（select）经常使用的动态SQL语句。

1. 准备实体类BookEntity（我这里是直接建立实体类，但是如果实体类比较麻烦的话，可以直接使用map）

package com.fs.mybatis.entity;

public class BookEntity {
    private String bid;
    private String bname;
    private double price;
    private String author;
    private String cid;

    public BookEntity() {
    }

    public BookEntity(String bid, String bname, double price, String author, String cid) {
        this.bid = bid;
        this.bname = bname;
        this.price = price;
        this.author = author;
        this.cid = cid;
    }

    public String getBid() {
        return bid;
    }

    public void setBid(String bid) {
        this.bid = bid;
    }

    public String getBname() {
        return bname;
    }

    public void setBname(String bname) {
        this.bname = bname;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getCid() {
        return cid;
    }

    public void setCid(String cid) {
        this.cid = cid;
    }

    @Override
    public String toString() {
        return "BookEntity{" +
                "bid='" + bid + '\'' +
                ", bname='" + bname + '\'' +
                ", price=" + price +
                ", author='" + author + '\'' +
                ", cid='" + cid + '\'' +
                '}';
    }
}

2. 准备Mapper接口

package com.fs.mybatis.mapper;

import com.fs.mybatis.entity.BookEntity;
import org.apache.ibatis.annotations.Mapper;

import javax.swing.text.html.parser.Entity;
import java.util.List;
import java.util.Map;

@Mapper
public interface BookMapper {
    // 根据条件进行查询
    public List<BookEntity> queryBook(BookEntity bookEntity);


    public boolean updateBook(BookEntity bookEntity);

    public boolean insertBook(BookEntity bookEntity);

}

3. 查询（select) 语句（动态Sql语句）---- xml文件

<!--    查询书籍  -->
    <select id="queryBook" resultType="com.fs.mybatis.entity.BookEntity">
        select * from t_book
        <where>
            <if test="bid != null and bid != ''">
                bid = #{bid}
            </if>
            <if test="bname != null and bname != ''">
                and bname like '%${bname}%'
            </if>
            <if test="price != null and price != ''">
                and price = #{price}
            </if>
            <if test="author != null and author != ''">
                and author like '%${author}%'
            </if>
            <if test="cid != null and cid != ''">
                and cid = #{cid}
            </if>
        </where>
    </select>

4. 插入（insert)语句（动态Sql语句） ---- xml文件

<!--    添加书籍  -->
    <insert id="insertBook" >
        insert into t_book
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bid != null and bid != ''">
                bid,
            </if>
            <if test="bname != null and bname != ''">
                bname,
            </if>
            <if test="price != null and price != ''">
                price,
            </if>
            <if test="author != null and author != ''">
                author,
            </if>
            <if test="cid != null and cid != ''">
                cid
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="bid != null and bid != ''">
                #{bid},
            </if>
            <if test="bname != null and bname != ''">
                #{bname},
            </if>
            <if test="price != null and price != ''">
                #{price},
            </if>
            <if test="author != null and author != ''">
                #{author},
            </if>
            <if test="cid != null and cid != ''">
                #{cid}
            </if>
        </trim>
    </insert>

5. 修改（update)语句（动态Sql语句） ---- xml文件

<!--    修改书籍  -->
    <update id="updateBook" parameterType="com.fs.mybatis.entity.BookEntity">
        update t_book
        <set>
            <if test="bname != null and bname != ''">
                bname = #{bname},
            </if>
            <if test="price != null and price != ''">
                price = #{price},
            </if>
            <if test="author != null and author != ''">
                author = #{author},
            </if>
            <if test="cid != null and cid != ''">
                cid = #{cid}
            </if>
        </set>
        where bid = #{bid}
    </update>