ibatis动态查询:iBATIS动态查询的实现浅析
iBATIS动态查询实现主要是在iBATIS中使用安全拼接语句,动态查询 iBATIS比JDBC的优势:安全高效
iBATIS动态查询例子:(介绍说明文字在注释中)
<select id ="selectAllProducts" parameterClass ="Product" resultMap ="ProductResult"> select id,note from Product <dynamic prepend ="WHERE">
<!-- isNotNull判断参数是否存在,Integer类型 --> <isNotNull property ="id">
<!-- isGreaterThan判断参数是否大于compareValue,isGreaterEquals是大于等于 --> <isGreaterThan prepend =" and " property ="id" compareValue ="0"> id = #id# </isGreaterThan> </isNotNull>
<!-- isNotEmpty判断字串不为空,isEmpty可以判断字串为空 --> <isNotEmpty prepend =" and " property ="note">
<!-- 模糊查询不能用#,#在是用prepareStatement?插入参数,$是文本替换 --> note like '%$note$%' </isNotEmpty> </dynamic> </select>
iBATIS动态查询解释:
用Map传参数
<select id ="selectAllProducts" parameterClass ="java.util.HashMap" resultMap
="ProductResult">
select id,note from Product <dynamic prepend ="WHERE">
<!-- isPropertyAvailable判断属性是否有效 --> <isPropertyAvailable property ="id"> <isNotNull property ="id">
<!-- isLessThan判断参数是否小于compareValue,isLessEquals是小于等于 --> <isLessThan prepend =" and " property ="id" compareValue ="10"> id = #id# </isLessThan> </isNotNull>
</isPropertyAvailable> </dynamic> </select>
iBATIS动态查询几个常用属性
< isPropertyAvailable > 属性是存在
< isNotPropertyAvailable > 属性不存在
< isNull > 属性值是null
< isEmpty > 判断Collection.size < 1 或String.length<1
<isEqual > 等于
< isNotEqual > 不等于
< isGreaterThan > 大于
< isGreaterEqual > 大于等于
< isLessThan > 小于
< isLessEqual > 小于等于
iBATIS动态查询相关信息就向你介绍到这里希望对你了解iBATIS动态查询有所帮助
§<dynamic>标签
§二元标签
§一元标签
§<iterate>标签
§ 共同的属性 prepend,open,close
<dynamic>标签
§<dynamic>标签
§属性 prepend,open,close
二元条件标签
§<isEqual property=“age”compareValue=“20”>
比较属性值和静态值或另一个属性值是否相等
§<isNotEqual>
比较属性值和静态值或另一个属性值是否不相等。
§<isGreaterThan>
比较属性值是否大于静态值或另一个属性值。
§<isGreaterEqual>
比较属性值是否大于等于静态值或另一个属性值。
§<isLessThan>
比较属性值是否小于静态值或另一个属性值。
§<isLessEqual>
比较属性值是否小于等于静态值或另一个属性值。
一元条件标签
§<isPropertyAvailable>
检查是否存在该属性(存在parameter bean的属性)
§<isNotPropertyAvailable>
检查是否不存在该属性(不存在parameter bean的属性)
§<isNull>
检查属性是否为null
§<isNotNull>
检查属性是否不为null
§<isEmpty>
检查Collection.size()的值,属性的String或String.valueOf()值,是否为null或空(“”或size() < 1)
§<isNotEmpty>
检查Collection.size()的值,属性的String或String.valueOf()值,是否不为null或不为空(“”或size() > 0)
其他标签
§<isParameterPresent>
检查是否存在参数对象(不为null)
§<isNotParameterPresent>
检查是否不存在参数对象(参数对象为null)
§<iterate>
遍历类型为java.util.List的元素。
view plaincopy toclipboardprint?
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<!-- 动态查询1-->
<select id="getOrders1"parameterClass="com.air.Account"
resultClass="com.air.Product">
SELECT
orders.id as id,
orders.product asproduct,
orders.customer ascustomer
FROM orders
<dynamic prepend=" WHERE">
<isNullproperty="username">customer IS NOT NULL</isNull>
<isNotNullproperty="username">
orders.customer=#username#
</isNotNull>
</dynamic>
</select>
<!-- 动态查询2-->
<select id="getOrders2"parameterClass="com.air.Account"
resultClass="com.air.Product">
SELECT
o.id as id,
o.product as product,
o.customer as customer
FROM orders o
<isNotNullproperty="username">
INNER JOIN user_account a ONo.customer=a.username
</isNotNull>
<dynamicprepend="where ">
<isNotEmptyproperty="groupname" prepend="and " open="("close=")">
a.groupname=#groupname#
</isNotEmpty>
</dynamic>
</select>
<!-- 动态查询3-->
<select id="getOrders3"parameterClass="com.air.Product"
resultClass="com.air.Product">
SELECT
orders.id as id,
orders.product asproduct,
orders.customer ascustomer
FROM orders
<dynamic prepend="WHERE">
<isNotEmptyproperty="product" prepend="and" open="("close=")" removeFirstPrepend="true">
product=#product#
</isNotEmpty>
<isNotEmptyproperty="customer" prepend="and" open="("close=")">
customer=#customer#
</isNotEmpty>
</dynamic>
</select>
<!-- 动态查询4-->
<select id="getOrders4"parameterClass="com.air.Product"
resultClass="com.air.Product">
SELECT
*
FROM orders
<dynamic prepend="WHERE">
<iterateproperty="keywords" open="(" close=")"conjunction="OR">
product=#keywords[]#
</iterate>
</dynamic>
<!-- <dynamicprepend="WHERE product IN ">-->
<!-- <iterateproperty="keywords" open="(" close=")"conjunction=","> -->
<!-- #keywords[]#-->
<!-- </iterate>-->
<!-- </dynamic>-->
</select>
</sqlMap>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapPUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>
<!-- 动态查询1-->
<select id="getOrders1" parameterClass="com.air.Account"
resultClass="com.air.Product">
SELECT
orders.id as id,
orders.product as product,
orders.customer as customer
FROM orders
<dynamic prepend=" WHERE">
<isNullproperty="username">customer IS NOT NULL</isNull>
<isNotNullproperty="username">
orders.customer=#username#
</isNotNull>
</dynamic>
</select>
<!-- 动态查询2-->
<select id="getOrders2" parameterClass="com.air.Account"
resultClass="com.air.Product">
SELECT
o.id as id,
o.product as product,
o.customer as customer
FROM orders o
<isNotNullproperty="username">
INNER JOIN user_account a ONo.customer=a.username
</isNotNull>
<dynamic prepend="where">
<isNotEmptyproperty="groupname" prepend="and " open="("close=")">
a.groupname=#groupname#
</isNotEmpty>
</dynamic>
</select>
<!-- 动态查询3-->
<select id="getOrders3" parameterClass="com.air.Product"
resultClass="com.air.Product">
SELECT
orders.id as id,
orders.product as product,
orders.customer as customer
FROM orders
<dynamic prepend="WHERE">
<isNotEmptyproperty="product" prepend="and" open="("close=")" removeFirstPrepend="true">
product=#product#
</isNotEmpty>
<isNotEmptyproperty="customer" prepend="and" open="("close=")">
customer=#customer#
</isNotEmpty>
</dynamic>
</select>
<!-- 动态查询4-->
<select id="getOrders4" parameterClass="com.air.Product"
resultClass="com.air.Product">
SELECT
*
FROM orders
<dynamic prepend="WHERE">
<iterateproperty="keywords" open="(" close=")"conjunction="OR">
product=#keywords[]#
</iterate>
</dynamic>
<!-- <dynamic prepend="WHEREproduct IN ">-->
<!-- <iterateproperty="keywords" open="(" close=")"conjunction=","> -->
<!-- #keywords[]#-->
<!-- </iterate>-->
<!-- </dynamic>-->
</select>
</sqlMap>