MyBatis3.x整理:(三)动态SQL

本文深入解析MyBatis框架中动态SQL的实现方式,包括if、choose、where、trim、set、foreach等元素的使用场景及技巧,通过具体示例展示了如何构建灵活的SQL查询和更新语句。

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

一.if,choose

<select id="queryForList" resultType="Employee">
		SELECT id,name,sn,salary,deptId FROM employee
		where 1= 1
			<if test="keyword!=null">
				<bind name="keywordLike" value="'%'+keyword + '%'"/>
				AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
			</if>
			<if test="minSalary!=null">
				AND salary >= #{minSalary}
			</if>
			<if test="maxSalary!=null">
				AND salary &lt;= #{maxSalary}
			</if>
			<if test="deptId>0">
				AND deptId = #{deptId}
			</if>
	
	</select>
<select id="query" resultType="Employee">
	SELECT id,name,sn,salary,deptId FROM employee where 1=1
	<choose>
		<when test="deptId >0">
		and dept_id = #{deptId}
		</when>
		<otherwise>
		and detp_id IS NOT NULL
		</otherwise>
	</choose>
</select>

 

 二.where,trim,set

where元素,如果查询条件 没有" WHERE " 关键词,则自动在查询条件前插入" WHERE " .如果查询条件以"AND"或"OR"开头,那么就会使用WHER关键词替换

<select id="queryForList" resultType="Employee">
		SELECT id,name,sn,salary,deptId FROM employee
		<where>
			<if test="keyword!=null">
				<bind name="keywordLike" value="'%'+keyword + '%'"/>
				AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
			</if>
			<if test="minSalary!=null">
				AND salary >= #{minSalary}
			</if>
			<if test="maxSalary!=null">
				AND salary &lt;= #{maxSalary}
			</if>
			<if test="deptId>0">
				AND deptId = #{deptId}
			</if>
		</where>
	</select>

set元素同where元素相似,也能根据set中的sql动态地去掉最后的逗号,并在前面添加set关键词,如果没有内容 ,也会选择忽略set语句 ,适用于update语句

<update id="update">

    update employee
    <set>
        <if test="name!=null">
            name = #{name},
        </if>
        <if test="sn!=null">
            sn= #{sn},
        </if>
        <if test="salary!=null">
            salary= #{salary},
        </if>
    </set>
</update>

 Mapper文件

<delete id="batchDelete">
    delete from employee
    <foreach collection="ids" open="(" close=")" separator="," item="id">
        #{id}
    </foreach>
</delete>

 

三.foreach

批量删除

Mapper接口

void batchDelete(@Param("ids")Long[] ids)

 

四.bind,sql,include

sql:可以把相同的sql片段起一个名字,并使用include元素在sql任意位置使用

<!-- 多个查询共同使用的的SQL -->
	<sql id="base_where">
		<where>
			<if test="keyword!=null">
				<bind name="keywordLike" value="'%'+keyword + '%'"/>
				AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
			</if>
			<if test="minSalary!=null">
				AND salary >= #{minSalary}
			</if>
			<if test="maxSalary!=null">
				AND salary &lt;= #{maxSalary}
			</if>
			<if test="deptId>0">
				AND deptId = #{deptId}
			</if>
		</where>
	</sql>
select id="queryForList" resultType="Employee">
		SELECT id,name,sn,salary,deptId FROM employee
		<include refid="base_where" />
		<if test="pageSize>0">
			LIMIT #{start},#{pageSize}
		</if>
	</select>

 

bind:使用OGNL表达式创建一个变量并将其绑定在上下文中

<select id="queryForList" resultType="Employee">
		SELECT id,name,sn,salary,deptId FROM employee
		<where>
			<if test="keyword!=null">
				<bind name="keywordLike" value="'%'+keyword + '%'"/>
				AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
			</if>
			<if test="minSalary!=null">
				AND salary >= #{minSalary}
			</if>
			<if test="maxSalary!=null">
				AND salary &lt;= #{maxSalary}
			</if>
			<if test="deptId>0">
				AND deptId = #{deptId}
			</if>
		</where>
</select>

 

 

源码下载

上一篇: MyBatis3.x整理:(二)MyBatis拓展

下一篇: MyBatis3.x整理:(四)对象关系映射​​​​​​​

 

### Cause: java.lang.IllegalStateException: Missing the data source name: &#39;rw_ds&#39; at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425) ... 82 common frames omitted Caused by: java.lang.IllegalStateException: Missing the data source name: &#39;rw_ds&#39; at com.google.common.base.Preconditions.checkState(Preconditions.java:589) at org.apache.shardingsphere.driver.jdbc.core.connection.ConnectionManager.getConnections(ConnectionManager.java:301) at org.apache.shardingsphere.infra.executor.sql.prepare.driver.DriverExecutionPrepareEngine.group(DriverExecutionPrepareEngine.java:82) at org.apache.shardingsphere.infra.executor.sql.prepare.AbstractExecutionPrepareEngine.prepare(AbstractExecutionPrepareEngine.java:62) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionGroupContext(ShardingSpherePreparedStatement.java:403) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:374) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:65) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110) at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:169) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) at com.sun.proxy.$Proxy444.query(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154) ... 89 common frames omitted
最新发布
08-09
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值