1、插入主键自增长:前提是要设置自增长的字段:alter table HU_SERVER modify id int(10) auto_increment;
想取的主键的值,需要加 selectKey这段,而且前面sql语句里面不需要插入主键字段。对于不同的数据库,selectKey语句的写法是不同的。 针对不同数据库的写法如下:
Cloudscape VALUES IDENTITY_VAL_LOCAL()
DB2 VALUES IDENTITY_VAL_LOCAL()
Derby VALUES IDENTITY_VAL_LOCAL()
HSQLDB CALL IDENTITY()
MySql SELECT LAST_INSERT_ID()
SqlServer SELECT SCOPE_IDENTITY()
SYBASE SELECT @@IDENTITY
ORACLE SELECT CUSTOM_SQL.NEXTVAL AS ID FROM DUAL
<insertid="insertOneUser4"parameterClass="com.air.Account">
INSERT INTO USER_ACCOUNT
(
USERID,USERNAME, PASSWORD, GROUPNAME
)VALUES(
#userid#,
#username#,
#password#,
#groupname#
)
<selectKey
keyProperty="userid"
resultClass="int">
SELECT LAST_INSERT_ID()
</selectKey>
</insert>
实例:
<insertid="insertHServer"parameterClass="com.alibaba.h.console.domain.HServer">
INSERT INTO
H_SERVER(ID,NAME,IP,PORT,GMT_CREATED,GMT_MODIFIED)
VALUES(#id#,#name#,#ip#,#port#,#gmtCreated#,#gmtModified#)
<selectKeykeyProperty="id"resultClass="int"type="pre">
SELECT LAST_INSERT_ID()
</selectKey>
</insert>
插入时:不需要再setId,让自动生成
2、com.ibatis.common.beans.ProbeException: There is no READABLE property named 'ID' in class 'com.alibaba.h.console.domain.HServer'
原因:<typeAlias alias="hServerClass" type="com.alibaba.h.console.domain.HServer" />中的hServerClass被传入resultClass或者parameterClass
解决方法:直接使用原生的类:parameterClass="com.alibaba.h.console.domain.HServer"
3、传递多参数
方法1:使用对象构造查询参数
<selectid="selectStudentByIdAndName"resultClass="Student"parameterClass="Student">
select * from student where sid=#sid# and sname=#sname#
</select>
方法2:使用map封装查询参数
map 的定义一定要在sql的前面,第一次我做连写的时候就吧map的定义放在了后面,结果老是报错,说找不到map的定义;
【注意】limit #begin#,#increment# 的位置,否则dynamic为空的时候,会报sql语法错误
<parameterMapclass="java.util.HashMap"id="parameterMap">
<parameterproperty="listType"/>
<parameterproperty="listKey"/>
<parameterproperty="listValue"/>
<parameterproperty="availableBegin"/>
<parameterproperty="availableEnd"/>
<parameterproperty="begin"/>
<parameterproperty="increment"/>
</parameterMap>
<selectid="getBlackList"resultMap="HBlackListResult"
parameterMap="parameterMap">
SELECT
<includerefid="columns"/>
<![CDATA[
FROM black_list
]]>
<dynamicprepend="where">
<isNotEmptyprepend="and"property="listType">
list_type = #listType#
</isNotEmpty>
<isNotEmptyprepend="and"property="listKey">
list_key = #listKey#
</isNotEmpty>
<isNotEmptyprepend="and"property="listValue">
list_value = #listValue#
</isNotEmpty>
<isNotEmptyprepend="AND"property="availableBegin">
available_begin >= #availableBegin#
</isNotEmpty>
<isNotEmptyprepend="AND"property="availableEnd">
available_end <= #availableEnd#
</isNotEmpty>
</dynamic>
limit #begin#,#increment#
</select>
代码:
Map<String,Object> parameterMap = new HashMap<String,Object>();
parameterMap.put("listType", 1);
parameterMap.put("listKey", "list_key1");
parameterMap.put("listValue", "list_value1");
try {
parameterMap.put("availableBegin", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parseObject("2011-11-01 00:00:00"));
parameterMap.put("availableEnd", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parseObject("2011-11-10 00:00:00"));
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
parameterMap.put("begin",5);
parameterMap.put("increment", 10);
return getSqlMapClientTemplate().queryForList("HBlackList.getBlackList", parameterMap);
4、动态查询
<selectid="getBlackListCount"resultClass="java.lang.Long">
select count(*) from black_list
<dynamicprepend="where">
<isNotEmptyprepend="and"property="listType">
list_type = #listType#
</isNotEmpty>
<isNotEmptyprepend="and"property="listKey">
list_key = #listKey#
</isNotEmpty>
<isNotEmptyprepend="and"property="listValue">
list_value = #listValue#
</isNotEmpty>
<isNotEmptyprepend="AND"property="availableBeginBegin">
available_begin >= #availableBeginBegin#
</isNotEmpty>
<isNotEmptyprepend="AND"property="availableEndBegin">
available_end <= #availableEndBegin#
</isNotEmpty>
</dynamic>
</select>
注意点:
(1)resultClass类型:具体的类型
(2) '>'需要使用转义符号:‘<’
(3) timestamp支持查询时比较大小,可用>,< =等,已确认;datetime似乎不行,待确认?测试后为:支持,用法一样
5、输出完整的sql,只需要配置如下日志
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug,stdout
或者:
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configurationxmlns:log4j="http://jakarta.apache.org/log4j/">
<appendername="STDOUT"class="org.apache.log4j.ConsoleAppender">
<layoutclass="org.apache.log4j.PatternLayout">
<paramname="ConversionPattern"value="%d [%-5p] %c\:%x%L - %m%n"/>
</layout>
</appender>
<loggername="log4j.logger.com.ibatis.*"additivity="true">
<priorityvalue="debug"/>
<appender-refref="STDOUT"/>
</logger>
<loggername="log4j.logger.java.sql.*"additivity="true">
<priorityvalue="debug"/>
<appender-refref="STDOUT"/>
</logger>
<root>
<levelvalue="debug"/>
<appender-refref="STDOUT"/>
</root>
</log4j:configuration>