mybatis动态sql 与分页-优快云博客

本文详细解析MyBatis动态SQL的各种用法，包括if、trim、foreach等标签的应用场景，以及如何处理模糊查询和特殊字符。同时，深入探讨MyBatis分页查询的局限性及解决方案，介绍PageHelper插件的配置与使用，提升大数据量下的查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mybatis动态sql 与分页

1、mybatis动态sql
2、模糊查询
3、查询返回结果集的处理
4、分页查询
5、特殊字符处理

1、mybatis动态sql

1.1 if
比如说我们需要修改商品信息的时候，不可以修改成空值，就需要if来判断

<update id="updateByPrimaryKeySelective" parameterType="com.hsl.model.Goods" >
    update goods
    <set >
      <if test="gName != null" >
        g_name = #{gName,jdbcType=VARCHAR},
      </if>
      <if test="gModel != null" >
        g_model = #{gModel,jdbcType=VARCHAR},
      </if>
      <if test="gBatch != null" >
        g_batch = #{gBatch,jdbcType=VARCHAR},
      </if>
      <if test="gUnit != null" >
        g_unit = #{gUnit,jdbcType=VARCHAR},
      </if>
      <if test="gPrice != null" >
        g_price = #{gPrice,jdbcType=VARCHAR},
      </if>
      <if test="gRemarks != null" >
        g_remarks = #{gRemarks,jdbcType=VARCHAR},
      </if>
    </set>
    where g_id = #{gId,jdbcType=INTEGER}
  </update>

1.2 trim （空格）
添加商品的时候

<insert id="insertSelective" parameterType="com.hsl.model.Goods" >
    insert into goods
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="gId != null" >
        g_id,
      </if>
      <if test="gName != null" >
        g_name,
      </if>
      <if test="gModel != null" >
        g_model,
      </if>
      <if test="gBatch != null" >
        g_batch,
      </if>
      <if test="gUnit != null" >
        g_unit,
      </if>
      <if test="gPrice != null" >
        g_price,
      </if>
      <if test="gRemarks != null" >
        g_remarks,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="gId != null" >
        #{gId,jdbcType=INTEGER},
      </if>
      <if test="gName != null" >
        #{gName,jdbcType=VARCHAR},
      </if>
      <if test="gModel != null" >
        #{gModel,jdbcType=VARCHAR},
      </if>
      <if test="gBatch != null" >
        #{gBatch,jdbcType=VARCHAR},
      </if>
      <if test="gUnit != null" >
        #{gUnit,jdbcType=VARCHAR},
      </if>
      <if test="gPrice != null" >
        #{gPrice,jdbcType=VARCHAR},
      </if>
      <if test="gRemarks != null" >
        #{gRemarks,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

1.3 foreach
遍历集合，批量查询、通常用于in关键字
前两个比较容易

foreach元素的属性主要有item，index，collection，open，separator，close。
1、collection表示如何来得到这个集合，如果传入的直接为一个List，那么collection值就为list，如果直接传入的为一个array不可变数组，那么collection值就为array，如果传入的为一个dto，比如dto里面的array变量名为idLists，那么collection的值就为idLists。
2、item表示集合中每一个元素进行迭代时的别名，比如item为value，那么，每次获取的都使用#{value}即可
3、index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，一般很少使用这个
4、open表示该语句以什么开始
5、separator表示在每次进行迭代之间以什么符号作为分隔符
6、close表示以什么结束

下面我讲一下foreach

首先我们改变GoodsMapper.java

 List<Goods> selectGoodsByIdIn(@Param("gId") List gId );

GoodsMapper.xml自动生成

<select id="selectGoodsByIdIn" resultType="com.hsl.model.Goods"></select>

改写代码

<!--通过gid foreach遍历出结果-->
  <select id="selectGoodsByIdIn" resultType="com.hsl.model.Goods" parameterType="java.util.List">
    select * form goods where bid in
    <foreach collection="gId" item="gid" open="(" close=")" separator=",">
      #{bid}
    </foreach>

  </select>

GoodsService.java添加代码

 List<Goods> selectGoodsByIdIn(List gId );

其实现类添加代码

 @Override
    public List<Goods> selectGoodsByIdIn(List gId) {
        return goodsMapper.selectGoodsByIdIn(gId);
    }

测试类GoodsServiceImplTest.java添加

  public void selectGoodsByIdIn() {
        List listId = new ArrayList();
        listId.add(1);
        listId.add(2);
        listId.add(3);
        List<Goods> list=this.goodsService.selectGoodsByIdIn(listId);
        for (Goods goods : list) {
            System.out.println("商品信息为："+goods);
        }


    }

测试
在这里插入图片描述

测试成功！

结合我另外两篇博文来看：

https://blog.youkuaiyun.com/water_java/article/details/103067493

https://blog.youkuaiyun.com/water_java/article/details/103097132

1.4 其他

choose/set/where

<update id="updateByPrimaryKey" parameterType="com.hsl.model.Goods" >
    update goods
    set g_name = #{gName,jdbcType=VARCHAR},
      g_model = #{gModel,jdbcType=VARCHAR},
      g_batch = #{gBatch,jdbcType=VARCHAR},
      g_unit = #{gUnit,jdbcType=VARCHAR},
      g_price = #{gPrice,jdbcType=VARCHAR},
      g_remarks = #{gRemarks,jdbcType=VARCHAR}
    where g_id = #{gId,jdbcType=INTEGER}
  </update>

2、模糊查询

模糊查询（3种方式）
2.1 参数中直接加入%%

2.2 使用${…}代替#{…}（不建议使用该方式，有SQL注入风险）

      关键：#{...}与${...}区别？
      参数类型为字符串，#会在前后加单引号[']，$则直接插入值

      注：
      1) mybatis中使用OGNL表达式传递参数
      2) 优先使用#{...}
      3) ${...}方式存在SQL注入风险

2.3 SQL字符串拼接CONCAT

案例：
GoodsMapper.xml

<select id="selectGoodsLike1" resultMap="BaseResultMap" parameterType="java.lang.String">
        select * from goods
        <where>
            g_name like #{gName}
        </where>

    </select>
    <select id="selectGoodsLike2" resultType="com.hsl.model.Goodss" parameterType="java.lang.String">
    select * from goods where g_name like '${gName}'
  </select>
    <select id="selectGoodsLike3" resultMap="BaseResultMap" parameterType="java.lang.String">
    select * from goods where g_name like concat('%',#{gName},'%')
  </select>
    <select id="list1" resultMap="BaseResultMap">
    select * from goods
  </select>
    <select id="list2" resultType="com.hsl.model.Goods">
    select * from goods
  </select>

自动生成的实体类：
package com.hsl.model;

public class Goods {
private Integer gId;

private String gName;

private String gModel;

private String gBatch;

private String gUnit;

private String gPrice;

private String gRemarks;

public Goods(Integer gId, String gName, String gModel, String gBatch, String gUnit, String gPrice, String gRemarks) {
    this.gId = gId;
    this.gName = gName;
    this.gModel = gModel;
    this.gBatch = gBatch;
    this.gUnit = gUnit;
    this.gPrice = gPrice;
    this.gRemarks = gRemarks;
}

@Override
public String toString() {
    return "Goods{" +
            "gId=" + gId +
            ", gName='" + gName + '\'' +
            ", gModel='" + gModel + '\'' +
            ", gBatch='" + gBatch + '\'' +
            ", gUnit='" + gUnit + '\'' +
            ", gPrice='" + gPrice + '\'' +
            ", gRemarks='" + gRemarks + '\'' +
            '}';
}

public Goods() {
    super();
}

public Integer getgId() {
    return gId;
}

public void setgId(Integer gId) {
    this.gId = gId;
}

public String getgName() {
    return gName;
}

public void setgName(String gName) {
    this.gName = gName;
}

public String getgModel() {
    return gModel;
}

public void setgModel(String gModel) {
    this.gModel = gModel;
}

public String getgBatch() {
    return gBatch;
}

public void setgBatch(String gBatch) {
    this.gBatch = gBatch;
}

public String getgUnit() {
    return gUnit;
}

public void setgUnit(String gUnit) {
    this.gUnit = gUnit;
}

public String getgPrice() {
    return gPrice;
}

public void setgPrice(String gPrice) {
    this.gPrice = gPrice;
}

public String getgRemarks() {
    return gRemarks;
}

public void setgRemarks(String gRemarks) {
    this.gRemarks = gRemarks;
}

}

第二个方法是我用了自己建的另一个实体类：

package com.hsl.model;

/**
 * @author water
 * @site www.water.com
 * @company xxx公司
 * @create 2019-11-16 11:44
 */
public class Goodss {
    private Integer g_id;

    private String g_name;

    private String g_model;

    private String g_batch;

    private String g_unit;

    private String g_price;

    private String g_remarks;

    public Goodss() {
    }

    public Goodss(Integer g_id, String g_name, String g_model, String g_batch, String g_unit, String g_price, String g_remarks) {
        this.g_id = g_id;
        this.g_name = g_name;
        this.g_model = g_model;
        this.g_batch = g_batch;
        this.g_unit = g_unit;
        this.g_price = g_price;
        this.g_remarks = g_remarks;
    }

    @Override
    public String toString() {
        return "Goodss{" +
                "g_id=" + g_id +
                ", g_name='" + g_name + '\'' +
                ", g_model='" + g_model + '\'' +
                ", g_batch='" + g_batch + '\'' +
                ", g_unit='" + g_unit + '\'' +
                ", g_price='" + g_price + '\'' +
                ", g_remarks='" + g_remarks + '\'' +
                '}';
    }

    public Integer getG_id() {
        return g_id;
    }

    public void setG_id(Integer g_id) {
        this.g_id = g_id;
    }

    public String getG_name() {
        return g_name;
    }

    public void setG_name(String g_name) {
        this.g_name = g_name;
    }

    public String getG_model() {
        return g_model;
    }

    public void setG_model(String g_model) {
        this.g_model = g_model;
    }

    public String getG_batch() {
        return g_batch;
    }

    public void setG_batch(String g_batch) {
        this.g_batch = g_batch;
    }

    public String getG_unit() {
        return g_unit;
    }

    public void setG_unit(String g_unit) {
        this.g_unit = g_unit;
    }

    public String getG_price() {
        return g_price;
    }

    public void setG_price(String g_price) {
        this.g_price = g_price;
    }

    public String getG_remarks() {
        return g_remarks;
    }

    public void setG_remarks(String g_remarks) {
        this.g_remarks = g_remarks;
    }
}

对比两个实体类
第二个是和数据库表一至的

GoodsMapper.java添加代码

   List<Goods> selectGoodsLike1(@Param("gName") String gName);

    List<Object> selectGoodsLike2(@Param("gName") String gName);

    List<Goods> selectGoodsLike3(@Param("gName") String gName);

GoodsService.java添加代码

  //模糊查询
    List<Goods> selectGoodsLike1(String gName);

    List<Object> selectGoodsLike2(String gName);

    List<Goods> selectGoodsLike3(String gName);

GoodsService.java实现类GoodsServiceImpl.java添加代码

  @Override
    public List<Goods> selectGoodsLike1(String gName) {
        return goodsMapper.selectGoodsLike1(gName);
    }
    @Override
    public List<Object> selectGoodsLike2(String gName) {
        return goodsMapper.selectGoodsLike2(gName);
    }
    @Override
    public List<Goods> selectGoodsLike3(String gName) {
        return goodsMapper.selectGoodsLike3(gName);
    }

测试类GoodsServiceImplTest.java添加：


    @Test
    public void selectGoodsLike1() {
        List<Goods> list = this.goodsService.selectGoodsLike1("%qq%");

        for (Goods goods : list) {
            System.out.println("商品信息：" + goods);
        }
    }

    @Test
    public void selectGoodsLike2() {
        List<Object> list = this.goodsService.selectGoodsLike2("%qq%");
        for (Object goods : list) {
            System.out.println("商品信息：" + goods);
        }
    }

    @Test
    public void selectGoodsLike3() {
        List<Goods> list = this.goodsService.selectGoodsLike3("qq");
        for (Goods goods : list) {
            System.out.println("商品信息：" + goods);
        }
    }

三个方法测试下来都是同样的结果
在这里插入图片描述

3、查询返回结果集的处理

resultMap：适合使用返回值是自定义实体类的情况
resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型

3.1 使用resultMap返回自定义类型集合

3.2 使用resultType返回List<T>

3.3 使用resultType返回单个对象

3.4 使用resultType返回List<Map>，适用于多表查询返回结果集

3.5 使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集

GoodsMapper.xml

   <select id="list1" resultMap="BaseResultMap">
    select * from goods
  </select>
    <select id="list2" resultType="com.hsl.model.Goodss">
    select * from goods
  </select>
    <select id="list3" resultMap="BaseResultMap" parameterType="com.hsl.model.GoodsVo">
        select * from goods where g_id in
        <foreach collection="gIds" open="(" close=")" separator="," item="gId">
            #{gId}
        </foreach>
    </select>
    <select id="list4" resultType="java.util.Map">
    select * from goods
  </select>
    <select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
    select * from goods where g_id= #{gId}
  </select>

GoodsMapper.java

//处理返回结果集
    List<Goods> list1();

    List<Object> list2();

    List<Goods> list3(GoodsVo goodsVo);

    List<Map> list4();

    Map list5(Map map);

GoodsService.java添加代码

  List<Goods> list1();

    List<Object> list2();

    List<Goods> list3(GoodsVo goodsVo);

    List<Map> list4();

    Map list5(Map map);

GoodsService.java实现类GoodsServiceImpl.java添加代码

   @Override
    public List<Goods> list1() {
        return goodsMapper.list1();
    }

    @Override
    public List<Object> list2() {
        return goodsMapper.list2();
    }

    @Override
    public List<Goods> list3(GoodsVo goodsVo) {
        return goodsMapper.list3(goodsVo);
    }

    @Override
    public List<Map> list4() {
        return goodsMapper.list4();
    }

    @Override
    public Map list5(Map map) {
        return goodsMapper.list5(map);
    }

测试类GoodsServiceImplTest.java添加

   @Test
    public void selectGoodsList1() {
      List<Goods> list=this.goodsService.list1();
        for (Goods goods : list) {
            System.out.println("商品信息为："+goods);
        }

    }

    @Test
    public void selectGoodsList2() {
        List<Object> list=this.goodsService.list2();
        for (Object goods : list) {
            System.out.println("商品信息为："+goods);
        }

    }

    @Test
    public void selectGoodsList3() {
        List list0=new ArrayList();
        list0.add(1);
        list0.add(2);
        list0.add(3);
        GoodsVo v=new GoodsVo();
        v.setgIds(list0);
        List<Goods> list=this.goodsService.list3(v);
        for (Goods goods : list) {
            System.out.println("商品信息为："+goods);
        }

    }


    @Test
    public void selectGoodsList4() {
        for (Map map : this.goodsService.list4()) {
            System.out.println(map);
        }

    }

    @Test
    public void selectGoodsList5() {
        Map map=new HashMap();
        map.put("gId",1);
        System.out.println(this.goodsService.list5(map));
    }

4、分页查询

为什么要重写mybatis的分页？
Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的

struts拦截器
定义一个拦截器类
invoke
sysout(“action方法被调用前执行的功能”)
method.invoke
sysout(“action方法被调用后执行的功能”)

struts-sy.xml
	将拦截器的类申明到interceptors
	引用拦截器
	<action>
		<interceptor-ref>

4.1 导入分页插件

   <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.1.2</version>
   </dependency>

4.2 将pagehelper插件配置到mybatis中

   <!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
   <plugin interceptor="com.github.pagehelper.PageInterceptor">
   </plugin>
  <!-- 注意要加在environments前-->

4.3 在你需要进行分页的Mybatis方法前调用PageHelper.startPage静态方法即可，紧跟在这个方法后的第一个Mybatis查询方法会被进行分页

   //设置分页处理
   if (null != pageBean && pageBean.isPaginate()) {
     PageHelper.startPage(pageBean.getCurPage(), pageBean.getPageRecord());
   }

4.4 获取分页信息(二种方式)

 4.4.1 使用插件后，查询实际返回的是Page<E>，而非List<E>，Page继承了ArrayList，同时还包含分页相关的信息
      Page<Book> page = (Page<Book>)list;
      System.out.println("页码：" + page.getPageNum());
      System.out.println("页大小：" + page.getPageSize());
      System.out.println("总记录：" + page.getTotal());
 4.4.2 使用PageInfo
      PageInfo pageInfo = new PageInfo(list);
      System.out.println("页码：" + pageInfo.getPageNum());
      System.out.println("页大小：" + pageInfo.getPageSize());
      System.out.println("总记录：" + pageInfo.getTotal());

案例：
GoodsMapper.xml

  <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
        select * from goods
        <where>
            <if test="g_name!=null and g_name != ''">
                g_name like #{gName}
            </if>
        </where>
    </select>

GoodsMapper.java

  List<Map> listPager(Map map);

GoodsService.java添加代码

 List<Map> listPager(Map map, PageBean pageBean);

GoodsService.java实现类GoodsServiceImpl.java添加代码

  @Override
    public List<Map> listPager(Map map, PageBean pageBean) {
        if(pageBean != null && pageBean.isPagination()){
            PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
        }
        List<Map> list = goodsMapper.listPager(map);
        if(pageBean != null && pageBean.isPagination()){
            PageInfo pageInfo = new PageInfo(list);
            System.out.println("页码："+pageInfo.getPageNum());
            System.out.println("页大小："+pageInfo.getPageSize());
            System.out.println("总记录："+pageInfo.getTotal());
            pageBean.setTotal(pageInfo.getTotal()+"");
        }
        return list;

    }

测试类GoodsServiceImplTest.java添加

  @Test
    public void listPage() {
        Map map = new HashMap();
        map.put("gName", "%qq%");
        PageBean pageBean = new PageBean();
        List<Map> list = this.goodsService.listPager(map, pageBean);
        for (Map map1 : list) {
            System.out.println("信息：" + map1);
        }

    }

在这里插入图片描述

5、特殊字符处理

特殊字符处理

>(&gt;)   
<(&lt;)  
&(&amp;) 
空格(&nbsp;)

<![CDATA[ <= ]]>

GoodsMapper.xml

  <select id="list6" resultMap="BaseResultMap" parameterType="com.hsl.model.GoodsVo">
        select * from goods
        <where>
            <if test="null != min and min != ''">
                <![CDATA[  and #{min} < g_price ]]>
            </if>
            <if test="null != max and max != ''">
                <![CDATA[ and #{max} > g_price ]]>
            </if>
        </where>
    </select>

GoodsMapper.java

  List<Goods> list6(GoodsVo goodsVo);

GoodsService.java添加代码

 List<Goods> list6(GoodsVo goodsVo);

GoodsService.java实现类GoodsServiceImpl.java添加代码

 @Override
    public List<Goods> list6(GoodsVo goodsVo) {
        return goodsMapper.list6(goodsVo);
    }

测试类GoodsServiceImplTest.java添加

@Test
    public void list6() {
       GoodsVo goodsVo=new GoodsVo();
       goodsVo.setMin(15);
       goodsVo.setMax(25);

       List <Goods> list=this.goodsService.list6(goodsVo);
        for (Goods goods : list) {
            System.out.println(goods);
        }
    }

GoodsVo.java

package com.hsl.model;

import java.util.List;

/**
 * @author water
 * @site www.water.com
 * @company xxx公司
 * @create 2019-11-16 8:54
 * 用于连表查询
 */
public class GoodsVo extends Goods{
   private List<Integer> gIds;
   private Integer min;
   private Integer max;

    public Integer getMin() {
        return min;
    }

    public void setMin(Integer min) {
        this.min = min;
    }

    public Integer getMax() {
        return max;
    }

    public void setMax(Integer max) {
        this.max = max;
    }

    public List<Integer> getgIds() {
        return gIds;
    }

    public void setgIds(List<Integer> gIds) {
        this.gIds = gIds;
    }
}

帮助类：
PageBean.java

package com.hsl.util;

import javax.servlet.http.HttpServletRequest;
import java.io.Serializable;
import java.util.Map;

public class PageBean implements Serializable {

	private static final long serialVersionUID = 2422581023658455731L;

	//页码
	private int page=1;
	//每页显示记录数
	private int rows=10;
	//总记录数
	private int total=0;
	//是否分页
	private boolean isPagination=true;
	//上一次的请求路径
	private String url;
	//获取所有的请求参数
	private Map<String,String[]> map;
	
	public PageBean() {
		super();
	}
	
	//设置请求参数
	public void setRequest(HttpServletRequest req) {
		String page=req.getParameter("page");
		String rows=req.getParameter("rows");
		String pagination=req.getParameter("pagination");
		this.setPage(page);
		this.setRows(rows);
		this.setPagination(pagination);
		this.url=req.getContextPath()+req.getServletPath();
		this.map=req.getParameterMap();
	}
	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public Map<String, String[]> getMap() {
		return map;
	}

	public void setMap(Map<String, String[]> map) {
		this.map = map;
	}

	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}
	
	public void setPage(String page) {
		if(null!=page&&!"".equals(page.trim()))
			this.page = Integer.parseInt(page);
	}

	public int getRows() {
		return rows;
	}

	public void setRows(int rows) {
		this.rows = rows;
	}
	
	public void setRows(String rows) {
		if(null!=rows&&!"".equals(rows.trim()))
			this.rows = Integer.parseInt(rows);
	}

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}
	
	public void setTotal(String total) {
		this.total = Integer.parseInt(total);
	}

	public boolean isPagination() {
		return isPagination;
	}
	
	public void setPagination(boolean isPagination) {
		this.isPagination = isPagination;
	}
	
	public void setPagination(String isPagination) {
		if(null!=isPagination&&!"".equals(isPagination.trim()))
			this.isPagination = Boolean.parseBoolean(isPagination);
	}
	
	/**
	 * 获取分页起始标记位置
	 * @return
	 */
	public int getStartIndex() {
		//(当前页码-1)*显示记录数
		return (this.getPage()-1)*this.rows;
	}
	
	/**
	 * 末页
	 * @return
	 */
	public int getMaxPage() {
		int totalpage=this.total/this.rows;
		if(this.total%this.rows!=0)
			totalpage++;
		return totalpage;
	}
	
	/**
	 * 下一页
	 * @return
	 */
	public int getNextPage() {
		int nextPage=this.page+1;
		if(this.page>=this.getMaxPage())
			nextPage=this.getMaxPage();
		return nextPage;
	}
	
	/**
	 * 上一页
	 * @return
	 */
	public int getPreivousPage() {
		int previousPage=this.page-1;
		if(previousPage<1)
			previousPage=1;
		return previousPage;
	}

	@Override
	public String toString() {
		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", isPagination=" + isPagination
				+ "]";
	}
}

StringUtils.java

package com.hsl.util;

public class StringUtils {
	// 私有的构造方法，保护此类不能在外部实例化
	private StringUtils() {
	}

	/**
	 * 如果字符串等于null或去空格后等于""，则返回true，否则返回false
	 * 
	 * @param s
	 * @return
	 */
	public static boolean isBlank(String s) {
		boolean b = false;
		if (null == s || s.trim().equals("")) {
			b = true;
		}
		return b;
	}
	
	/**
	 * 如果字符串不等于null或去空格后不等于""，则返回true，否则返回false
	 * 
	 * @param s
	 * @return
	 */
	public static boolean isNotBlank(String s) {
		return !isBlank(s);
	}

}