Dynamic SQL--官方文档
https://ibatis.apache.org/docs/dotnet/datamapper/ch03s09.html
3.9. Dynamic SQL
A very common problem with working directly with ADO is dynamic SQL. It is normally very difficult to work with SQL statements that change not only the values of parameters, but which parameters and columns are included at all. The typical solution is usually a mess of conditional if-else statements and horrid string concatenations. The desired result is often a query by example, where a query can be built to find objects that are similar to the example object. The iBATIS DataMapper API provides a relatively elegant solution that can be applied to any mapped statement element. Here is a simple example:
Example 3.56. A simple dynamic select sttatement, with two possible outcomes
<select id="dynamicGetAccountList" cacheModel="account-cache" parameterClass="Account" resultMap="account-result" >
select * from ACCOUNT
<isGreaterThan prepend="and" property="Id" compareValue="0">
where ACC_ID = #Id#
</isGreaterThan>
order by ACC_LAST_NAME
</select>
In the above example, there are two possible statements that could be created depending on the state of the Id property of the parameter object. If the Id parameter is greater than 0, then the statement will be created as follows:
select * from ACCOUNT where ACC_ID = ?
Or if the Id parameter is 0 or less, the statement will look as follows.
select * from ACCOUNT
The immediate usefulness of this might not become apparent until a more complex situation is encountered. For example, the following is a somewhat more complex example.
Example 3.57. A complex dynamic select statement, with 16 possible outcomes
<select id="dynamicGetAccountList" parameterClass="Account" resultMap="account-result" >
select * from ACCOUNT
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="FirstName">
( ACC_FIRST_NAME = #FirstName#
<isNotNull prepend="OR" property="LastName">
ACC_LAST_NAME = #LastName#
</isNotNull>
)
</isNotNull>
<isNotNull prepend="AND" property="EmailAddress">
ACC_EMAIL like #EmailAddress#
</isNotNull>
<isGreaterThan prepend="AND" property="Id" compareValue="0">
ACC_ID = #Id#
</isGreaterThan>
</dynamic>
order by ACC_LAST_NAME
</select>
Depending on the situation, there could be as many as 16 different SQL queries generated from the above dynamic statement. To code the if-else structures and string concatenations could get quite messy and require hundreds of lines of code.
Using dynamic statements is as simple as inserting some conditional tags around the dynamic parts of your SQL. For example:
Example 3.58. Creating a dynamic statement with conditional tags
<statement id="someName" parameterClass="Account" resultMap="account-result" >
select * from ACCOUNT
<dynamic prepend="where">
<isGreaterThan prepend="and" property="id" compareValue="0">
ACC_ID = #id#
</isGreaterThan>
<isNotNull prepend="and" property="lastName">
ACC_LAST_NAME = #lastName#
</isNotNull>
</dynamic>
order by ACC_LAST_NAME
</statement>
In the above statement, the <dynamic> element demarcates a section of the SQL that is dynamic. The dynamic element is optional and provides a way to manage a prepend in cases where the prepend ("WHERE") should not be included unless the contained conditions append to the statement. The statement section can contain any number of conditional elements (see below) that will determine whether the contained SQL code will be included in the statement. All of the conditional elements work based on the state of the parameter object passed into the query. Both the dynamic element and the conditional elements have a "prepend" attribute. The prepend attribute is a part of the code that is free to be overridden by the a parent element's prepend if necessary. In the above example the "where" prepend will override the first true conditional prepend. This is necessary to ensure that the SQL statement is built properly. For example, in the case of the first true condition, there is no need for the AND, and in fact it would break the statement. The following sections describe the various kinds of elements, including Binary Conditionals, Unary Conditionals, and Iterate.
Binary conditional elements compare a property value to a static value or another property value. If the result is true, the body content is included in the SQL query.
prepend – the overridable SQL part that will be prepended to the statement (optional) |
property – the property to be compared (required) |
compareProperty – the other property to be compared (required or compareValue) |
compareValue – the value to be compared (required or compareProperty) |
Table 3.7. Binary conditional attributes
Element | Description |
---|---|
<isEqual> | Checks the equality of a property and a value, or another property. Example Usage:
<isEqual prepend="AND" property="status" compareValue="Y"> MARRIED = ‘TRUE' </isEqual> |
<isNotEqual> | Checks the inequality of a property and a value, or another property. Example Usage:
<isNotEqual prepend="AND" property="status" compareValue="N"> MARRIED = ‘FALSE' </isNotEqual> |
<isGreaterThan> | Checks if a property is greater than a value or another property. Example Usage:
<isGreaterThan prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘FALSE' </isGreaterThan> |
<isGreaterEqual> | Checks if a property is greater than or equal to a value or another property. Example Usage:
<isGreaterEqual prepend="AND" property="shoeSize" compareValue="12"> BIGFOOT = ‘TRUE' </isGreaterEqual> |
<isLessEqual> | Checks if a property is less than or equal to a value or another property. Example Usage:
<isLessEqual prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘TRUE' </isLessEqual> |
Unary conditional elements check the state of a property for a specific condition.
prepend – the overridable SQL part that will be prepended to the statement (optional) |
property – the property to be checked (required) |
Table 3.8. Unary conditional attributes
Element | Description |
---|---|
<isPropertyAvailable> | Checks if a property is available (i.e is a property of the parameter object). Example Usage:
<isPropertyAvailable property="id" > ACCOUNT_ID=#id# </isPropertyAvailable> |
<isNotPropertyAvailable> | Checks if a property is unavailable (i.e not a property of the parameter object). Example Usage:
<isNotPropertyAvailable property="age" > STATUS='New' </isNotEmpty> |
<isNull> | Checks if a property is null. Example Usage:
<isNull prepend="AND" property="order.id" > ACCOUNT.ACCOUNT_ID = ORDER.ACCOUNT_ID(+) </isNotEmpty> |
<isNotNull> | Checks if a property is not null. Example Usage:
<isNotNull prepend="AND" property="order.id" > ORDER.ORDER_ID = #order.id# </isNotEmpty> |
<isEmpty> | Checks to see if the value of a Collection, String property is null or empty ("" or size() < 1). Example Usage:
<isEmpty property="firstName" > LIMIT 0, 20 </isNotEmpty> |
<isNotEmpty> | Checks to see if the value of a Collection, String property is not null and not empty ("" or size() < 1). Example Usage:
<isNotEmpty prepend="AND" property="firstName" > FIRST_NAME LIKE '%$FirstName$%' </isNotEmpty> |
These elements check for parameter object existence.
prepend – the overridable SQL part that will be prepended to the statement (optional)
Table 3.9. Testing to see if a parameter is present
Element | Description |
---|---|
<isParameterPresent> | Checks to see if the parameter object is present (not null).
<isParameterPresent prepend="AND"> EMPLOYEE_TYPE = #empType# </isParameterPresent> |
<isNotParameterPresent> | Checks to see if the parameter object is not present (null). Example Usage:
<isNotParameterPresent prepend="AND"> EMPLOYEE_TYPE = ‘DEFAULT' </isNotParameterPresent> |
This tag will iterate over a collection and repeat the body content for each item in a List
prepend – the overridable SQL part that will be prepended to the statement (optional) |
property – a property of type IList that is to be iterated over (required) |
open – the string with which to open the entire block of iterations, useful for brackets (optional) |
close – the string with which to close the entire block of iterations, useful for brackets (optional) |
conjunction – the string to be applied in between each iteration, useful for AND and OR (optional) |
Table 3.10. Creating a list of conditional clauses
Element | Description |
---|---|
<iterate> | Iterates over a property that is of type IList Example Usage:
<iterate prepend="AND" property="UserNameList"
open="(" close=")" conjunction="OR">
username=#UserNameList[]#
</iterate>
Note: It is very important to include the square brackets[] at the end of the List property name when using the Iterate element. These brackets distinguish this object as an List to keep the parser from simply outputting the List as a string. |
Despite the power of the full Dynamic Mapped Statement API discussed above, sometimes you just need a simple, small piece of your SQL to be dynamic. For this, SQL statements and statements can contain simple dynamic SQL elements to help implement dynamic order by clauses, dynamic select columns or pretty much any part of the SQL statement. The concept works much like inline parameter maps, but uses a slightly different syntax. Consider the following example:
Example 3.59. A dynamic element that changes the collating order
<statement id="getProduct" resultMap="get-product-result">
select * from PRODUCT order by $preferredOrder$
</statement>
In the above example the preferredOrder dynamic element will be replaced by the value of the preferredOrder property of the parameter object (just like a parameter map). The difference is that this is a fundamental change to the SQL statement itself, which is much more serious than simply setting a parameter value. A mistake made in a Dynamic SQL Element can introduce security, performance and stability risks. Take care to do a lot of redundant checks to ensure that the simple dynamic SQL elements are being used appropriately. Also, be mindful of your design, as there is potential for database specifics to encroach on your business object model. For example, you may not want a column name intended for an order by clause to end up as a property in your business object, or as a field value on your server page.
Simple dynamic elements can be included within <statements> and come in handy when there is a need to modify the SQL statement itself. For example:
Example 3.60. A dynamic element that changes the comparison operator
<statement id="getProduct" resultMap="get-product-result">
SELECT * FROM PRODUCT
<dynamic prepend="WHERE">
<isNotEmpty property="Description">
PRD_DESCRIPTION $operator$ #Description#
</isNotEmpty>
</dynamic>
</statement>
In the above example the operator property of the parameter object will be used to replace the $operator$ token. So if the operator property was equal to LIKE and the description property was equal to %dog%, then the SQL statement generated would be:
SELECT * FROM PRODUCT WHERE PRD_DESCRIPTION LIKE ‘%dog%'
相关文章
- Ansible自动化运维及官方文档
- 通读Python官方文档之wsgiref(未完成)
- ReactiveCocoa 4 官方文档翻译
- EasyTouch的使用官方文档操作步骤
- 云端软件平台 如何为自制的软件添加说明文档的快捷方式
- 欢迎参与 KubeVela 官方文档翻译活动
- 写给大家看的“不负责任” K8s 入门文档
- OpenGL ES着色器语言之语句和结构体(官方文档第六章)内建变量(官方文档第七、八章)
- OpenGL ES着色器语言之变量和数据类型(二)(官方文档第四章)
- 通过beego快速创建一个Restful风格API项目及API文档自动化
- 支付宝安全支付服务Android应用开发指南(摘取官方文档)
- 【资料汇编】结巴中文分词官方文档和源码分析系列文章
- Spring Boot 中使用 Swagger2 构建强大的 RESTful API 文档
- Android官方中文文档
- OpenGL ES着色器语言之着色概览(官方文档)
- VC文档 视图 框架窗口间的关系和消息传送规律
- SAP官方文档里关于CI和CD区别的解释:Continuous Integration vs Continuous Delivery
- 一起学微软Power BI系列-官方文档-入门指南(1)Power BI初步介绍
- atitit 方便搜索的文档文章结构框架.docx 目录 1.1. 三种搜索模式 tree hash关键词模式 关联搜索,对应的三种索引1 1.2. 好的标题规范与副标题1 1.3. Tr
- Atitit 基于文件的数据库保存系统json文档数据库 目录 1.1. 一行数据一个文件,一个文件夹微数据表表1 1.2. 保存C:wampwwwtisyetisye.php1 1.3
- Atitit 大数据体系图 大数据 技术 数据采集 gui自动化 爬虫 Nui自动化 Ocr技术 Tts语音处理 文档处理(office zip等) html文档处理解析 转
- Angular依赖注入官方文档的学习笔记
- Android修行手册 - Aspose文档格式excel/pdf/word/odt/图片等相互转换
- turtle简单教程文档
- brython 文档之内置 browser模块
- Hadoop-2.2.0中文文档—— Common - 超级用户模拟别的用户
- 【人工智能 Open AI】通用 API 网关系统(API Gateway)系统设计文档
- 【Android 逆向】x86 汇编 ( 参考资料 | Intel 官方的文档 | x86 汇编中文文档 | 汇编指令查询器 )
- Spring 官方教程:使用 Restdocs 创建 API 文档
- 官方文档中文版!Spring Cloud Stream 快速入门
- 006-基于hyperledger fabric1.4( 官方文档)编写第一个应用【外部nodejs调用】
- PostgreSQL的学习心得和知识总结(七十五)|深入理解PostgreSQL数据库文档说明及代码提交SGML文档编辑制作方法
- 蓝牙4.0——Android BLE开发官方文档翻译
- ES shrink ——一般是结合rollover一起使用的,一开始没有看懂官方shrink文档,当看了这个之后就明白了
- 官方文档 Upgrading Elasticsearch
- jQuery 文档操作方法
- locust===官方说明文档,关于tasks
- Linux之Ubuntu:Ubuntu中常使用的快捷键命令(sudo/dpkg等文件处理相关/系统硬件相关/文件管理/文档编辑/磁盘管理/系统管理/备份压缩)、常用基础案例(图文教程)之详细攻略