mapper接口和增删查改sql语句【笔记】

接口

package com.q.dao;

import com.q.pojo.Student1;

import java.util.List;

public interface StudentMapper1 {

    //    如果年龄小于20，则查询姓名tom的学生,如果年龄大于20小于30，则查询jim的学生,大于30，查询jack的信息
    Student1 getStudent(int age);

    //    查询的接口，传递一个对象过去，通过set的方法注入值
    List<Student1> getInformation(Student1 student1);

    //    更新数据
    int updateInformation(Student1 student1);

    //    插入数据
    int insertData(Student1 student1);

    //    批量查找数据
    List<Student1> bulkQuery(List<Integer> list);

    //批量添加
    int batchAdd(List<Student1> list);

    //批量删除
    int batchDeletion(List<Integer> list);


}

映射实现配置

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.q.dao.StudentMapper1">
    
    <!--    插入数据-->
    <insert id="insertData" parameterType="Student1">
        insert into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id !=null and id !=''">
                id,
            </if>
            <if test="name !=null and name !=''">
                name,
            </if>
            <if test="tid !=null and tid !=''">
                tid,
            </if>
            <if test="age !=null and age !=''">
                age,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id !=null and id !=''">
                #{id},
            </if>
            <if test="name !=null and name !=''">
                #{name},
            </if>
            <if test="tid !=null and tid !=''">
                #{tid},
            </if>
            <if test="age !=null and age !=''">
                #{age},
            </if>
        </trim>

    </insert>
    
    
    <!--    批量添加-->
    <insert id="batchAdd" parameterType="Student1">
        insert into student(
        id, name, tid, age
        )values
        <foreach collection="list" index="index" item="Student1" separator=",">
            (#{Student1.id},#{Student1.name},#{Student1.tid},#{Student1.age})
        </foreach>
    </insert>

    
    <!--   更新数据-->
    <update id="updateInformation" parameterType="Student1">
        update student
        <set>
            <if test="name !=null and name !=''">
                name =#{name},
            </if>
            <if test="age !=null and age !=''">
                age=#{age}，
            </if>
            <if test="tid !=null and tid !=''">
                tid=#{tid},
            </if>
        </set>
        where id=#{id};
    </update>

    
    <!--    批量删除-->
    <delete id="batchDeletion" parameterType="list">
        delete from student where id in
        <foreach collection="list" open="(" close=")" separator="," item="id">
            #{id}
        </foreach>
    </delete>

    
    <select id="getStudent" resultType="com.q.pojo.Student1">
        select * from mybatis.student
        <where>
            <if test="age != null">
                age=#{age}
            </if>
        </where>
    </select>

    
    <select id="getInformation" resultType="com.q.pojo.Student1" parameterType="Student1">
        select *
        from student
        <where>
            <bind name="name1" value="'%'+name+'%'"/>
            <if test="name !=null and name !=''">
                and name like #{name1}
            </if>
            <if test="age !=null and age !=''">
                and age=#{age}
            </if>
            <if test="id !=null and id !=''">
                and id=#{id}
            </if>
        </where>

    </select>
    
    <!--    批量查询-->
    <select id="bulkQuery" resultType="com.q.pojo.Student1" parameterType="list">
        select *
        from student where id in
        <foreach collection="list" open="(" close=")" separator="," item="id" index="i">
            #{id}
        </foreach>
    </select>
</mapper>

接口2

package com.q.dao;

import com.q.pojo.Blog;

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

public interface BlogMapper {

    /**
     * 添加的书
     *
     * @param blog 博客
     * @return int
     *///插入数据
    int addBook(Blog blog);

    /**
     * 查询博客
     *
     * @param map 地图
     * @return {@link List<Blog>}
     *///查询博客
    List<Blog> QueryBlog(Map<String, Object> map);

    /**
     * 查询博客选择
     *
     * @param map 地图
     * @return {@link List<Blog>}
     */
    List<Blog> QueryBlogChoose(Map<Object, Object> map);

    /**
     * 日期测试
     *
     * @param map 地图
     * @return int
     */
    int upDateTest(Map<Object, Object> map);

    /**
     * 选择博客
     *
     * @param map 地图
     * @return {@link List<Blog>}
     *///    查询id 1 2 3的博客
    List<Blog> selectBlog(Map<Object, Object> map);

    /**
     * 选择博客的名字
     *
     * @param string 字符串
     * @return {@link List<Blog>}
     */
    List<Blog> selectBlogByName(String string);


}

映射实现配置

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--绑定dao接口，会自动的找到sql语句-->
<mapper namespace="com.q.dao.BlogMapper">

    <insert id="addBook" parameterType="blog">
        insert into mybatis.blog(id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{create_time}, #{views});
    </insert>

    <!--    这个方法是update-->
    <update id="upDateTest">
        update mybatis.blog
        <set>
            <if test="title !=null">
                title =#{title},
            </if>
        </set>
        where id =#{id}
    </update>


    <select id="QueryBlog" parameterType="map" resultType="com.q.pojo.Blog">
        select *
        from mybatis.blog where 1=1
        <if test="title !=null">
            and title=#{title}
        </if>
        <if test="author !=null">
            and author=#{author}
        </if>
    </select>

    <!--sql片段，相当于封装-->
    <sql id="choose-when">
        <choose>
            <when test="title!=null">
                title=#{title}
            </when>
            <when test="author!=null">
                and author=#{author}
            </when>
            <otherwise>
                and views=#{views}
            </otherwise>
        </choose>
    </sql>
    
    <select id="QueryBlogChoose" resultType="com.q.pojo.Blog" parameterType="map">
        select * from mybatis.blog
        <where>
            <include refid="choose-when"/>
        </where>

    </select>


    <select id="selectBlog" resultType="com.q.pojo.Blog" parameterType="map">
        select *
        from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id =#{id}
            </foreach>
        </where>
    </select>
    
    <select id="selectBlogByName" resultType="com.q.pojo.Blog">
        <bind name="blogName" value="'%'+title+'%'"/>
        select * from mybatis.blog where title like #{blogName};
    </select>

</mapper>