在连接数据库时我们一般使用都是jdbc，但它有一个非常普遍的例子，就是动态SQL。想要把各种参数及参数值和数据列都变为动态SQL一般是非常困难的，但ibatis中就有这么一套方法来实现，下面就一起来看看吧。
1、 动态SQL片段
< !--动态条件分页查询-- > <sql id="sql_count"> select count(*) </sql> < sql id = "sql_select" > select * </sql> < sql id = "sql_where" > from icp < dynamic prepend = "where" > <isNotEmpty prepend="and" property="name"> name like '%$name$%' </isNotEmpty> < isNotEmpty prepend = "and" property = "path" > path like '%path$%' < /isNotEmpty> < isNotEmpty prepend = "and" property = "area_id" > area_id = #area_id# < /isNotEmpty> < isNotEmpty prepend = "and" property = "hided" > hided = #hided# < /isNotEmpty> < / dynamic > < dynamic prepend = "" > <isNotNull property="_start"> <isNotNull property="_size"> limit #_start#, #_size# </isNotNull> </isNotNull> < /dynamic> < / sql > < select id = "findByParamsForCount" parameterClass = "map" resultClass = "int" > <include refid="sql_count"/> < include refid = "sql_where" / > </select> < select id = "findByParams" parameterClass = "map" resultMap = "icp.result_base" > <include refid="sql_select"/> < include refid = "sql_where" / > </select>
2、 数字范围查询
所传参数名称是捏造所得， 非数据库字段， 比如_img_size_ge、 _img_size_lt字段 < isNotEmpty prepend = "and" property = "_img_size_ge" > <![CDATA[ img_size >= #_img_size_ge# ]]> < /isNotEmpty> < isNotEmpty prepend = "and" property = "_img_size_lt" > <![CDATA[ img_size < #_img_size_lt# ]]> < /isNotEmpty> 多次使用一个参数也是允许的 < isNotEmpty prepend = "and" property = "_now" > <![CDATA[ execplantime >= #_now# ]]> < /isNotEmpty> < isNotEmpty prepend = "and" property = "_now" > <![CDATA[ closeplantime <= #_now# ]]> < /isNotEmpty>
3、 时间范围查询
< isNotEmpty prepend = "" property = "_starttime" > <isNotEmpty prepend="and" property="_endtime"> <![CDATA[ createtime >= #_starttime# and createtime < #_endtime# ]]> </isNotEmpty> < /isNotEmpty>
4、 in查询
<
isNotEmpty prepend = "and"
property = "_in_state" >
state in ('$_in_state$') <
/isNotEmpty>5、 like查询
< isNotEmpty prepend = "and" property = "chnameone" > (chnameone like '%$chnameone$%' or spellinitial like '%$chnameone$%') < /isNotEmpty> < isNotEmpty prepend = "and" property = "chnametwo" > chnametwo like '%$chnametwo$%' < /isNotEmpty>
6、 or条件
<
isEqual prepend = "and"
property = "_exeable"
compareValue = "N" >
<![CDATA[
(t.finished='11' or t.failure=3)
]]> <
/isEqual> <
isEqual prepend = "and"
property = "_exeable"
compareValue = "Y" >
<![CDATA[
t.finished in ('10','19') and t.failure<3
]]> <
/isEqual>7、 where子查询
< isNotEmpty prepend = "" property = "exprogramcode" > <isNotEmpty prepend="" property="isRational"> <isEqual prepend="and" property="isRational" compareValue="N"> code not in (select t.contentcode from cms_ccm_programcontent t where t.contenttype='MZNRLX_MA' and t.programcode = #exprogramcode#) </isEqual> </isNotEmpty> < /isNotEmpty> < select id = "findByProgramcode" parameterClass = "string" resultMap = "cms_ccm_material.result" > select * from cms_ccm_material where code in (select t.contentcode from cms_ccm_programcontent t where t.contenttype = 'MZNRLX_MA' and programcode = #value#) order by updatetime desc < /select>
8、 函数
< !--添加-- > <insert id="insert" parameterClass="RuleMaster"> insert into rulemaster( name, createtime, updatetime, remark ) values ( #name#, now(), now(), #remark# ) <selectKey keyProperty="id" resultClass="long"> select LAST_INSERT_ID() </selectKey> </insert> <!--更新-- > <update id="update" parameterClass="RuleMaster"> update rulemaster set name = #name#, updatetime = now(), remark = #remark# where id = #id# </update>
9、 map结果集
< !--动态条件分页查询-- > <sql id="sql_count"> select count(a.*) </sql> < sql id = "sql_select" > select a.id vid , a.img imgurl , a.img_s imgfile , b.vfilename vfilename , b.name name , c.id sid , c.url url , c.filename filename , c.status status < /sql> < sql id = "sql_where" > From secfiles c, juji b, videoinfo a where a.id = b.videoid and b.id = c.segmentid and c.status = 0 order by a.id asc, b.id asc, c.sortnum asc < dynamic prepend = "" > <isNotNull property="_start"> <isNotNull property="_size"> limit #_start#, #_size# </isNotNull> </isNotNull> < /dynamic> < / sql > < !--返回没有下载的记录总数-- > <select id="getUndownFilesForCount" parameterClass="map" resultClass="int"> <include refid="sql_count"/> <include refid="sql_where"/> </select> < !--返回没有下载的记录-- > <select id="getUndownFiles" parameterClass="map" resultClass="java.util.HashMap"> <include refid="sql_select"/> <include refid="sql_where"/> </select>
以上就是关于动态SQL的所有内容，你了解了吗?在java项目中常见问题里，如果还有什么疑问，请记得关注我们了解详情。
推荐阅读：