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>
本文转自 zhouhaipeng 51CTO博客,原文链接:http://blog.51cto.com/tianya23/697788,如需转载请自行联系原作者