ibatis中使用安全的拼接语句,动态查询,大于、小于、等于（Ibatis uses secure splicing statements, dynamic query, greater than, less than, equal to）
ibatis中使用安全的拼接语句,动态查询,大于、小于、等于(Ibatis
uses secure splicing statements, dynamic query, greater than,
less than, equal to)
Ibatis uses secure splicing statements, dynamic query, greater than, less than, equal to
Using secure splicing statements to dynamically query in ibatis
Ibatis is one of the advantages over JDBC, safe and efficient
Explanation of text in annotation
Passing parameters with Map
The SQL / * dynamic parameters
Attribute keyword
Meaning
If the parameter is equal to the value, the query condition is valid.
If the parameter is not equal to the value, the query condition is valid.
If the parameter is greater than the value, the query condition is valid.
If the parameter is equal to the value, the query condition is valid.
If the parameter is less than the value, the query condition is valid. As follows:
ADOLESCENT = 'TRUE'
If the parameter is in use, the query condition is valid.
If the parameter is not used, the query condition is valid.
If the parameter is NULL, the query condition is valid.
If the parameter is not NULL, the query condition is valid.
If the parameter is empty, the query condition is valid.
If the parameter is not empty, the query condition is valid. The data type of the parameter is Collection and the parameter is not NULL or String"". As follows:
FIRST_NAME=#firstName#
isnotempty > < /
isparameterpresent > <
如果参数类不为null则查询条件有效.
isnotparameterpresent > <
checks to see if the parameter object is not present (zero).
example usage:
< isnotparameterpresent prepend = "and" >
employee _ type = 'default'
isnotparameterpresent > < /
在复杂查询过程中, 我们常常需要根据用户的选择决定查询条件, 这里发生变化的并不只是sql 中的参数, 包括select 语句中所包括的字段和限定条件, 都可能发生变化.典型情况, 如在一个复杂的组合查询页面, 我们必须根据用户的选择和输入决定查询的条件组合.
一个典型的页面如下:
对于这个组合查询页面, 根据用户选择填写的内容财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容, 我们应为其生成不同的查询语句.如用户没有填写任何信息即提交查询请求, 我们应该返回所有记录:
select * from t _ user;
如用户只在页面上填写了姓名 "erica", 我们应该生成类似:
select * from t where name like '% _ user erica%; 的sql查询语句.
如用户只在页面上填写了地址 "beijing", 我们应该生成类似:
select * from t _ user where address like '%%'; 的sql beijing.
而如果用户同时填写了姓名和地址 ("erica" & "beijing '), 则我们应生成类似:
select * from t where name like '% _ user erica% and address
like'%% '的sql查询语句 beijing.
对于ibatis 这样需要预先指定sql 语句的orm 实现而言, 传统的做法无非通过if - else 语句对输入参数加以判定, 然后针对用户选择调用不同的statement 定义.对于上面这种简单的情况 (两种查询条件的排列组合, 共4 种情况) 而言, statement 的重复定义工作已经让人不厌其烦, 而对于动辄拥有七八个查询条件, 乃至十几个查询条件
的排列组合而言, 琐碎反复的statement定义实在让人不堪承受.
考虑到这个问题, ibatis引入了动态映射机制综治信访维稳工作机制反恐怖工作机制企业员工晋升机制公司员工晋升机制员工晋升机制图, 即在statement定义中, 根据不同的
查询参数, 设定对应的sql语句.
还是以上面的示例为例:
< select id = "getusers"
parameterclass = "user"
resultmap = "get user result" >
select
id
name
sex
from t _ user
< dynamic prepend = "where" >
< isnotempty prepend = "and" property = "name" >
(name like # name #)
isnotempty > < /
< isnotempty prepend = "and" property = "address" >
(address like # address #)
isnotempty > < /
< / small >
select > < /
通过dynamic 节点, 我们定义了一个动态的where 子句.此where
子句中将
可能包含两个针对name 和address 字段的判断条件.而这两个字段
是否加入检索取决于用户所提供的查询条件 (字段是否为空
[isnotempty]).
对于一个典型的web程序而言, 我们通过httpservletrequest获得
表关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf单中的字段名并将其设入查询参数, 如:
user.setname (request.getparameter ("name");
user.setaddress (request.getparameter ("address"));
sqlmap.queryforlist ("user.getusers" user);
在执行queryforlist ("user.getusers" user) 时, ibatis 即根据
配置文
件中设定的sql动态生成规则,
Creating the corresponding SQL statement.
In the example above, we specify the name and address genera
by determining the node isNotEmpty
Dynamic rules of sex:
(name like #name#)
The semantics corresponding to this node is that if the parameter class's "name" attribute is nonempty (isNotEmpty, that is, null)
The string "" "", then, in the generated SQL Where sentence, includes the name like #name#, which is called the "string"
#name# will fill in the name attribute value of the parameter class.
The decision of the Address attribute is exactly the same as that of the name attribute.
In this way, we introduce the dynamic node into the statement definition, which simply implements the dynamic generation of the SQL decision clause, which will bring great convenience to the complex combination query. The definition of a node can be very flexible, and we can even use nested decision nodes to implement complex dynamic mappings, such as:
(name=#name#
Address=#address#
)
This definition defines that only when the user provides the name information can the query be combined with the address data (if only address data is provided, and the name information is ignored, it will still be regarded as full retrieval). The Dynamic node and the prepend attribute in the decision node indicate that the SQL clause defined in this node is in
Prefix of occurrence in subject SQL. Such as:
(name like #name#)
(address like #address#)
If the "name" attribute value is "Erica", "address" attribute to "Beijing", it will generate an SQL clause similar to the following (the actual operation period will be generated with placeholder PreparedStatement, then filling it with data):
WHERE (name like 'Beijing') AND (address like 'Beijing')
The WHERE statement is defined in the dynamic node to the dynamic node, so the setting of prepend ("WHERE") as a prefix, and the "AND", is actually a isNotEmpty node corresponding to the address property of the prepend set, it leads the SQL clause defined in the corresponding node. As for the isNotEmpty node the name property, because the ibatis will automatically determine whether to add the prepend prefix, here (name like #name#) is the first condition in the WHERE clause Clause, without the AND prefix, so automatically omitted.
The decision node is not limited to isNotEmpty, and ibatis provides a rich definition function. The decision nodes are divided into two classes:
Unary decision
The unary decision is based on the determination of the attribute value itself, such as whether the attribute is NULL and whether it is null or not.
In the example above, isNotEmpty is a typical unary decision.
Unary decision node has:
Node name Description
Is this property provided in the parameter class?
is opposite to
Is the attribute value NULL?
is opposite to if the attribute is Collection or String, its size is <1, if not more than two types, then through the String.valueOf (attribute value)
After obtaining the value of its String type, judge whether the size of <1 is opposite to .
Two element decision
The two element decision has two decision parameters, the first is the attribute name, but the decision value, such as the value
< isgreaterthan前置=”和“资产=“年龄”
comparevalue =“18”>
(年龄= #年龄#)
< / isgreaterthan >
其中，产权=“年龄”指定了属性名“年龄”，comparevalue =“18”“18”指明了判定值为。上面判定节点isgreaterthan对应的语义是:如果年龄属性大于18(comparevalue)，则在SQL中加入(年龄= #年龄#)条件。
二元判定节点有:
节点名属性值与comparevalues的关系
< >相等相等。
< isnotequal >不等。
< isgreaterthan >大于
< isgreaterequal >大于等于
< islessthan >小于
< islessequal >小于等于
浙公网安备 33021202002483