1、isNull, isNotNull与isEmpty, isNotEmpty区别
在iBATIS中isNull用于判断参数是否为Null,isNotNull相反。
isEmpty判断参数是否为Null或者空,满足其中一个条件则其true,isNotEmpty相反,当参数既不为Null也不为空是其为true
在iBATIS中isNull用于判断参数是否为Null,isNotNull相反。
isEmpty判断参数是否为Null或者空,满足其中一个条件则其true,isNotEmpty相反,当参数既不为Null也不为空是其为true
isNull: Checks if a property is null.
isNotNull: Checks if a property is not null.
isEmpty: Checks to see if the value of a Collection, String or String.valueOf() property is null or empty (size() < 1).
isNotEmpty: Checks to see if the value of a Collection, String or String.valueOf() property is not null and not empty(size() < 1)
2、模糊查询中参数的引用
模糊查询是针对字符串而言的,如果遇到两个单引号要包含一个参数,则不能再用#来引用变量了.
例子:
下面是错误的写法(运行将出错):
select * from USERS where USER_NAME like '%#username#%'
正确的写法如下(用$代替#
):
select * from USERS where USER_NAME like '%$username$%'
这种写法,虽然能够实现模糊查询,但是存在SQL注入的安全漏洞。
要解决这个问题,可以有两种方案:
方案1,ibatis的sql写法如下:
select * from USERS where USER_NAME like #username#
由调用的程序,给参数username的前后拼装上%,如下:
sqlMapClientTemplate.queryForList("getUserByName", "%"+name+"%")
缺点:需要在程序中拼装上模糊查询的符合%
方案2:对方法一的修改,将字符串的拼装放在ibatis的sql中,而不是通过程序拼装。该方案,针对不同的数据库,写法不一样,如下:
mysql: select * from USERS where USER_NAME like concat('%',#username#,'%')
oracle: select * from USERS where USER_NAME like '%'||#username#||'%'
SQL Server: select * from USERS where USER_NAME like '%'+#username#+'%'
3、map作为参数类和结果类,配置的不同
在使用Map作为结果集返回类型时候,必须这么设置结果集类型resultClass="java.util.HashMap",这时候,需要根据字段的名称来取值,值类型为Object,key类型为String。而作为参数时,parameterClass="map"。
例子:
<select id="getXXX" parameterClass="map" resultClass="java.util.HashMap">
select ...
</select>
dao:
public List<Map<String,Object>> getXXX(Map map) {
return getSqlMapClientTemplate().queryForList("getXXX", map);
}
4、 动态SQL片段
使用sql片段,可以起到复用的效果
<sql id="where_statement">
<dynamic prepend="where">
<isNotEmpty prepend="and" property="username">
username = #username:VARCHAR#
</isNotEmpty>
<isNotEmpty property="statusList" prepend="and">
status in
<iterate property="statusList" conjunction="," open="(" close=")">
#statusList[]#
</iterate>
</isNotEmpty>
</dynamic>
</sql>
<select id="MS-COUNT-USER" parameterClass="TA-User">
select count(*) from user
<include refid="where_statement" />
</select>
<select id="MS-GET-USER" parameterClass="TA-User" resultMap="RM-User">
select * from user
<include refid="where_statement" />
</select>
5、 <selectKey>获取新插入记录的主键
MySQL数据库:
<insert id="insertUser-MY-SQL" parameterClass="user">
INSERT INTO T_USER(name, password) VALUES(#name#,#password#)
<selectKey resultClass="int" keyProperty="id" >
SELECT LAST_INSERT_ID() as id
</selectKey>
</insert>
Oracle数据库:
<insert id="insertUser-ORACLE" parameterClass="user">
<selectKey resultClass="int" keyProperty="id" type="pre">
SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
</selectKey>
INSERT INTO t_user(id,name,password) VALUES(#id#,#name#,#password#)
</insert>
SQL Server数据库:
<insert id="insertUser-MS-SQL" parameterClass="user">
INSERT INTO T_USER(name, password) VALUES(#name#,#password#)
<selectKey resultClass="int" keyProperty="id" type="post">
SELECT @@IDENTITY AS ID
</selectKey>
</insert>
该方法不安全,应当用SCOPE_IDENTITY(),但这个函数属于域函数,需要在一个语句块中执行,改进如下:
<insert id="insertUser-MS-SQL" parameterClass="user">
<selectKey resultClass="int" keyProperty="id">
INSERT INTO T_USER(name, password)
VALUES (#name#,#password#)
SELECT SCOPE_IDENTITY() AS ID
</selectKey>
</insert>
6、jdbctype与javatype的映射
JDBC Type | Java Type |
---|---|
CHAR | String |
VARCHAR | String |
LONGVARCHAR | String |
NUMERIC | java.math.BigDecimal |
DECIMAL | java.math.BigDecimal |
BIT | boolean |
BOOLEAN | boolean |
TINYINT | byte |
SMALLINT | short |
INTEGER | int |
BIGINT | long |
REAL | float |
FLOAT | double |
DOUBLE | double |
BINARY | byte[] |
VARBINARY | byte[] |
LONGVARBINARY | byte[] |
DATE | java.sql.Date |
TIME | java.sql.Time |
TIMESTAMP | java.sql.Timestamp |
CLOB | Clob |
BLOB | Blob |
ARRAY | Array |
DISTINCT | mapping of underlying type |
STRUCT | Struct |
REF | Ref |
DATALINK | java.net.URL |
JAVA_OBJECT | underlying Java class |