动态sql和分页上

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 动态sql和分页上
  • mybatis中的动态SQL
  • mybatis中的模糊查询
  • mybatis中结果映射

1.mybatis中的动态SQL

Mybatis动态SQL是用来根据不同的条件动态生成SQL语句的一项功能。它可以实现:

1.条件查询

Mybatis动态SQL允许开发人员根据不同的条件动态构建查询语句。例如,通过使用标签,可以根据某些条件包含或排除查询的某些部分,从而实现更灵活的查询。

2.拼接SQL语句

动态SQL还可以用于拼接SQL语句。这使得开发人员可以在运行时根据具体需求构建SQL语句,从而适应更复杂的业务逻辑。

3.提高代码重用性

通过使用动态SQL,可以减少重复的SQL代码,提高代码的可重用性。例如,可以定义可重复使用的SQL片段,并通过标签在不同的查询中重用它们。

4.增强SQL语句的灵活性

Mybatis动态SQL通过使用诸如、、等标签提供了对SQL语句的精细控制。这增强了SQL语句的灵活性,允许开发人员根据具体的业务需求精确地控制查询的行为。

5.避免SQL注入风险

正确使用Mybatis动态SQL可以减少SQL注入的风险。通过使用参数化查询和避免字符串拼接,可以增强应用程序的安全性。

MyBatis的动态SQL是指对sql语句进行灵活操作;它是基于OGNL表达式,通过if,choose,when,otherwise,trim,where,set,foreach标签,对SQL语句进行灵活拼接、组装,从而提高开发人员的效率。

<?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.zlj.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
 <!-- <resultMap id="BaseResultMap" type="com.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="FLOAT" />
    </constructor>
  </resultMap>-->
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    *
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.zlj.model.Book">
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <select id="selectByBids" parameterType="java.util.List" resultType="com.zlj.model.Book" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid =
    <foreach collection="bids" item="bid" open="(" close=")" separator=",">
        #{bid}
    </foreach>
  </select>
</mapper>
package com.zlj.mapper;
import com.zlj.model.Book;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface BookMapper {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    List<Book> selectByBids(@Param("bids") List bids);
}
package com.zlj.biz.impl;
import com.zlj.biz.BookBiz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
/**
 * @author zlj
 * @create 2023-08-21 8:49
 */
public class BookBizImpl implements BookBiz {
    private  BookMapper bookMapper;
    public BookMapper getBookMapper() {
        return bookMapper;
    }
    @Override
    public int deleteByPrimaryKey(Integer bid) {
        return bookMapper.deleteByPrimaryKey(bid);
    }
    @Override
    public int insert(Book record) {
        return bookMapper.insert(record);
    }
    @Override
    public int insertSelective(Book record) {
        return bookMapper.insertSelective(record);
    }
    @Override
    public Book selectByPrimaryKey(Integer bid) {
        return bookMapper.selectByPrimaryKey(bid);
    }
    @Override
    public int updateByPrimaryKeySelective(Book record) {
        return bookMapper.updateByPrimaryKeySelective(record);
    }
    @Override
    public int updateByPrimaryKey(Book record) {
        return bookMapper.updateByPrimaryKey(record);
    }
    @Override
    public BookMapper setBookMapper(BookMapper mapper) {
        return this.bookMapper = mapper;
    }
    @Override
    public List<Book> selectByBids(List bids) {
        return bookMapper.selectByBids(bids);
    }
}
package com.zlj.biz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    BookMapper setBookMapper(BookMapper mapper);
    List<Book> selectByBids(List bids);
}
package com.zlj.demo;
import com.zlj.biz.impl.BookBizImpl;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Arrays;
import java.util.List;
/**
 * @author zlj
 * @create 2023-08-21 9:07
 */
public class demo1 {
    private SqlSession sqlSession;
    private BookBizImpl bookBiz;
    @Before
    public void a(){
        System.out.println("执行测试方法之前会执行的初始化代码块");
        sqlSession= SessionUtil.openSession();
        BookBizImpl bookBiz=new BookBizImpl();
        BookMapper mapper=sqlSession.getMapper(BookMapper.class);
        bookBiz.setBookMapper(mapper);
        this.bookBiz=bookBiz;
    }
    @After
    public void b(){
        System.out.println("执行测试方法之后会执行");
        sqlSession.commit();
    }
    @Test
    public void test1(){
        System.out.println("测试方法。。。");
        Book book = bookBiz.selectByPrimaryKey(33);
        System.out.println(book);
    }
@Test
    public void test2(){
        bookBiz.deleteByPrimaryKey(34);
    }
@Test
public void testById(){
    List<Integer> bids = Arrays.asList(new Integer[]{56, 57, 58, 59, 60});
    bookBiz.selectByBids(bids).forEach(System.out::print);//jdk1.8新特性
//    for (Book selectByBid : bookBiz.selectByBids(bids)) {
//        System.out.println(selectByBid);
//    }
}
}

2.mybatis中的模糊查询(三种:like1,like2,like3)

mybatis中#与$的区别

1.$是占位符传参(like2),#是预处理SQL(like1,like3)

2.外在形式,$传参不带引号,#传参自带引号

3.$传参存在sql注入,#不存在

4.$可以用来做动态列,完成动态sql的开发


<?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.zlj.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
 <!-- <resultMap id="BaseResultMap" type="com.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="FLOAT" />
    </constructor>
  </resultMap>-->
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    *
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultType="com.zlj.model.Book">
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <select id="selectByBids" parameterType="java.util.List" resultType="com.zlj.model.Book" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid =
    <foreach collection="bids" item="bid" open="(" close=")" separator=",">
        #{bid}
    </foreach>
  </select>
<select id="like1" resultType="com.zking.model.Book" parameterType="java.lang.String">
  select
  <include refid="Base_Column_List" />
  from t_mvc_book
  where bname like #{bname}
</select>
  <select id="like2" resultType="com.zking.model.Book" parameterType="java.lang.String">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like '${bname}'
  </select>
  <select id="like3" resultType="com.zking.model.Book" parameterType="java.lang.String">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
</mapper>
package com.zlj.mapper;
import com.zlj.model.Book;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface BookMapper {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    List<Book> selectByBids(@Param("bids") List bids);
    List<Book> like1(@Param("bname") String bname);
    List<Book> like2(@Param("bname") String bname);
    List<Book> like3(@Param("bname") String bname);
}
package com.zlj.biz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    BookMapper setBookMapper(BookMapper mapper);
    List<Book> selectByBids(List bids);
    List<Book> like1(String bname);
    List<Book> like2(String bname);
    List<Book> like3(String bname);
}
package com.zlj.biz.impl;
import com.zlj.biz.BookBiz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
/**
 * @author zlj
 * @create 2023-08-21 8:49
 */
public class BookBizImpl implements BookBiz {
    private  BookMapper bookMapper;
    public BookMapper getBookMapper() {
        return bookMapper;
    }
    @Override
    public int deleteByPrimaryKey(Integer bid) {
        return bookMapper.deleteByPrimaryKey(bid);
    }
    @Override
    public int insert(Book record) {
        return bookMapper.insert(record);
    }
    @Override
    public int insertSelective(Book record) {
        return bookMapper.insertSelective(record);
    }
    @Override
    public Book selectByPrimaryKey(Integer bid) {
        return bookMapper.selectByPrimaryKey(bid);
    }
    @Override
    public int updateByPrimaryKeySelective(Book record) {
        return bookMapper.updateByPrimaryKeySelective(record);
    }
    @Override
    public int updateByPrimaryKey(Book record) {
        return bookMapper.updateByPrimaryKey(record);
    }
    @Override
    public BookMapper setBookMapper(BookMapper mapper) {
        return this.bookMapper = mapper;
    }
    @Override
    public List<Book> selectByBids(List bids) {
        return bookMapper.selectByBids(bids);
    }
    @Override
    public List<Book> like1(String bname) {
        return bookMapper.like1(bname);
    }
    @Override
    public List<Book> like2(String bname) {
        return bookMapper.like2(bname);
    }
    @Override
    public List<Book> like3(String bname) {
        return bookMapper.like3(bname);
    }
}
package com.zlj.demo;
import com.zlj.biz.impl.BookBizImpl;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Arrays;
import java.util.List;
/**
 * @author zlj
 * @create 2023-08-21 9:07
 */
public class demo1 {
    private SqlSession sqlSession;
    private BookBizImpl bookBiz;
    @Before
    public void a(){
        System.out.println("执行测试方法之前会执行的初始化代码块");
        sqlSession= SessionUtil.openSession();
        BookBizImpl bookBiz=new BookBizImpl();
        BookMapper mapper=sqlSession.getMapper(BookMapper.class);
        bookBiz.setBookMapper(mapper);
        this.bookBiz=bookBiz;
    }
    @After
    public void b(){
        System.out.println("执行测试方法之后会执行");
        sqlSession.commit();
    }
    @Test
    public void test1(){
        System.out.println("测试方法。。。");
        Book book = bookBiz.selectByPrimaryKey(33);
        System.out.println(book);
    }
@Test
    public void test2(){
        bookBiz.deleteByPrimaryKey(34);
    }
@Test
public void testById(){
    List<Integer> bids = Arrays.asList(new Integer[]{56, 57, 58, 59, 60});
    bookBiz.selectByBids(bids).forEach(System.out::print);//jdk1.8新特性
//    for (Book selectByBid : bookBiz.selectByBids(bids)) {
//        System.out.println(selectByBid);
//    }
}
    @Test
    public void testlike1(){
        bookBiz.like1("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike2(){
        bookBiz.like2("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike3(){
        bookBiz.like3("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
}


3.mybatis中结果映射

使用mybatis的各种场景,返回的结果是多样的,resultType/resultMa


p

1.返回单表对应的实体类,仅有一个查询结果,可以用resultType,可以用resultMap(selectByPrimaryKey)

2.返回单表对应的实体类,有多个查询结果,可以用resultType,可以用resultMap(list01,list02)

3.返回多表对应的实体类,仅有一个查询结果,通常用resultType,也可以用resultMap(list03)

4.返回多表对应的实体类,有多个查询结果,通常用resultType,也可以用resultMap (list04)

5.返回单个列段,仅有一个查询结果,就用resultType(bname1)

6.返回单个列段,有多个查询结果,就用resultType(bname2)

<?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.zlj.mapper.BookMapper">
  <resultMap id="BaseResultMap" type="com.zlj.model.Book">
    <constructor>
      <idArg column="bid" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="bname" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="price" javaType="java.lang.Float" jdbcType="REAL" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    bid, bname, price
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.zlj.model.Book">
    insert into t_mvc_book (bid, bname, price
      )
    values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.zlj.model.Book">
    insert into t_mvc_book
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        bid,
      </if>
      <if test="bname != null">
        bname,
      </if>
      <if test="price != null">
        price,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="bid != null">
        #{bid,jdbcType=INTEGER},
      </if>
      <if test="bname != null">
        #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        #{price,jdbcType=REAL},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zlj.model.Book">
    update t_mvc_book
    <set>
      <if test="bname != null">
        bname = #{bname,jdbcType=VARCHAR},
      </if>
      <if test="price != null">
        price = #{price,jdbcType=REAL},
      </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.zlj.model.Book">
    update t_mvc_book
    set bname = #{bname,jdbcType=VARCHAR},
      price = #{price,jdbcType=REAL}
    where bid = #{bid,jdbcType=INTEGER}
  </update>
  <select id="selectByBids" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
<select id="like1" parameterType="java.lang.String" resultType="com.zlj.model.Book">
  select
  <include refid="Base_Column_List" />
  from t_mvc_book
  where bname like #{bname}
</select>
  <select id="like2" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like '${bname}'
  </select>
  <select id="like3" parameterType="java.lang.String" resultType="com.zlj.model.Book">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
  <select id="list01" resultType="com.zlj.model.Book" >
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
  <select id="list02" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_mvc_book
  </select>
<select id="list03" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid and sc.sid=#{sid} and sc.cid=#{cid}
</select>
  <select id="list04" resultType="java.util.Map" parameterType="java.util.Map">
  select s.*,sc.cid,sc.score from t_mysql_student s,t_mysql_score sc where s.sid=sc.sid
</select>
  <select id="bname1" parameterType="java.lang.Integer" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bid = #{bid,jdbcType=INTEGER}
  </select>
  <select id="bname2" parameterType="java.lang.String" resultType="java.lang.String">
    select
    bname
    from t_mvc_book
    where bname like concat('%',#{bname},'%')
  </select>
</mapper>
package com.zlj.mapper;
import com.zlj.model.Book;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface BookMapper {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    List<Book> selectByBids(@Param("bids") List bids);
    List<Book> like1(@Param("bname") String bname);
    List<Book> like2(@Param("bname") String bname);
    List<Book> like3(@Param("bname") String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(@Param("bid") Integer bid);
    List<String> bname2(@Param("bname") String bname);
}
package com.zlj.biz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
import java.util.Map;
public interface BookBiz {
    int deleteByPrimaryKey(Integer bid);
    int insert(Book record);
    int insertSelective(Book record);
    Book selectByPrimaryKey(Integer bid);
    int updateByPrimaryKeySelective(Book record);
    int updateByPrimaryKey(Book record);
    BookMapper setBookMapper(BookMapper mapper);
    List<Book> selectByBids(List bids);
    List<Book> like1(String bname);
    List<Book> like2(String bname);
    List<Book> like3(String bname);
    List<Book> list01();
    List<Book> list02();
    Map list03(Map map);
    List<Map> list04(Map map);
    String bname1(Integer bid);
    List<String> bname2(String bname);
}
package com.zlj.biz.impl;
import com.zlj.biz.BookBiz;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 8:49
 */
public class BookBizImpl implements BookBiz {
    private  BookMapper bookMapper;
    public BookMapper getBookMapper() {
        return bookMapper;
    }
    @Override
    public int deleteByPrimaryKey(Integer bid) {
        return bookMapper.deleteByPrimaryKey(bid);
    }
    @Override
    public int insert(Book record) {
        return bookMapper.insert(record);
    }
    @Override
    public int insertSelective(Book record) {
        return bookMapper.insertSelective(record);
    }
    @Override
    public Book selectByPrimaryKey(Integer bid) {
        return bookMapper.selectByPrimaryKey(bid);
    }
    @Override
    public int updateByPrimaryKeySelective(Book record) {
        return bookMapper.updateByPrimaryKeySelective(record);
    }
    @Override
    public int updateByPrimaryKey(Book record) {
        return bookMapper.updateByPrimaryKey(record);
    }
    @Override
    public BookMapper setBookMapper(BookMapper mapper) {
        return this.bookMapper = mapper;
    }
    @Override
    public List<Book> selectByBids(List bids) {
        return bookMapper.selectByBids(bids);
    }
    @Override
    public List<Book> like1(String bname) {
        return bookMapper.like1(bname);
    }
    @Override
    public List<Book> like2(String bname) {
        return bookMapper.like2(bname);
    }
    @Override
    public List<Book> like3(String bname) {
        return bookMapper.like3(bname);
    }
    @Override
    public List<Book> list01() {
        return bookMapper.list01();
    }
    @Override
    public List<Book> list02() {
        return bookMapper.list02();
    }
    @Override
    public Map list03(Map map){
    return bookMapper.list03(map);
    }
    @Override
    public List<Map> list04(Map map){
    return bookMapper.list04(map);
    }
    @Override
    public String bname1(Integer bid) {
        return bookMapper.bname1(bid);
    }
    @Override
    public List<String> bname2(String bname) {
        return bookMapper.bname2(bname);
    }
}
package com.zlj.demo;
import com.zlj.biz.impl.BookBizImpl;
import com.zlj.mapper.BookMapper;
import com.zlj.model.Book;
import com.zlj.utils.SessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * @author zlj
 * @create 2023-08-21 9:07
 */
public class demo1 {
    private SqlSession sqlSession;
    private BookBizImpl bookBiz;
    @Before
    public void a(){
        System.out.println("执行测试方法之前会执行的初始化代码块");
        sqlSession= SessionUtil.openSession();
        BookBizImpl bookBiz=new BookBizImpl();
        BookMapper mapper=sqlSession.getMapper(BookMapper.class);
        bookBiz.setBookMapper(mapper);
        this.bookBiz=bookBiz;
    }
    @After
    public void b(){
        System.out.println("执行测试方法之后会执行");
        sqlSession.commit();
    }
    @Test
    public void test1(){
        System.out.println("测试方法。。。");
        Book book = bookBiz.selectByPrimaryKey(33);
        System.out.println(book);
    }
@Test
    public void test2(){
        bookBiz.deleteByPrimaryKey(34);
    }
@Test
public void testById(){
    List<Integer> bids = Arrays.asList(new Integer[]{56, 57, 58, 59, 60});
    bookBiz.selectByBids(bids).forEach(System.out::print);//jdk1.8新特性
//    for (Book selectByBid : bookBiz.selectByBids(bids)) {
//        System.out.println(selectByBid);
//    }
}
    @Test
    public void testlike1(){
        bookBiz.like1("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike2(){
        bookBiz.like2("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlike3(){
        bookBiz.like3("%圣墟%").forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist01(){
        bookBiz.list01().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist02(){
        bookBiz.list02().forEach(System.out::print);//jdk1.8新特性
    }
    @Test
    public void testlist03(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        System.out.println(bookBiz.list03(map));
    }
    @Test
    public void testlist04(){
        Map map=new HashMap();
        map.put("sid","01");
        map.put("cid","01");
        bookBiz.list04(map).forEach(System.out::print);
    }
    @Test
    public void testbname1(){
        System.out.println(bookBiz.bname1(66));
    }
    @Test
    public void testbname2(){
        bookBiz.bname2("圣墟").forEach(System.out::print);
    }
}
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
SQL XML Java
【JavaEE进阶】 MyBatis之动态SQL
【JavaEE进阶】 MyBatis之动态SQL
|
27天前
|
SQL 人工智能 运维
数据库基础入门 — SQL排序与分页
数据库基础入门 — SQL排序与分页
16 0
|
2月前
|
SQL 缓存 Java
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
58 0
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
|
2月前
|
SQL XML Java
【MyBatis】动态SQL
【MyBatis】动态SQL
|
2月前
|
SQL XML Java
MyBatis 实现动态 SQL
 MyBatis 中的动态 SQL 就是SQL语句可以根据不同的情况情况来拼接不同的sql。 本文会介绍 xml 和 注解 两种方式的动态SQL实现方式。
56 1
|
2月前
|
SQL Java 数据库连接
Mybatis之动态SQL
【1月更文挑战第4天】Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题
35 2
|
2月前
|
SQL Java 数据库连接
一文彻底搞懂Mybatis系列(九)之Mybatis动态SQL标签总结
一文彻底搞懂Mybatis系列(九)之Mybatis动态SQL标签总结
|
3月前
|
SQL Java 数据库连接
【Java调试】通过SqlSessionFactory类对象获取mapper文件内的动态SQL在执行时的完整SQL及参数(2种使用方法+测试Demo及结果)
【Java调试】通过SqlSessionFactory类对象获取mapper文件内的动态SQL在执行时的完整SQL及参数(2种使用方法+测试Demo及结果)
42 0
|
3月前
|
SQL Java 数据库连接
MyBatis之动态SQL的使用
MyBatis之动态SQL的使用
|
3月前
|
SQL XML Java
掌握MyBatis动态SQL:从标签到实战的全面解析
掌握MyBatis动态SQL:从标签到实战的全面解析
70 0