0 知识补充
在ORACLE中,数据库用户可以通过访问DUAL表获取一些系统信息,例如:
'SELECT SYSDATE FROM DUAL'命令返回当前系统日期
'SELECT 1 + 1 FROM DUAL'执行数学运算
'SELECT MY_SEQUENCE.NEXTVAL FROM DUAL'得到序列值
DB2中存在一个对应的表:SYSIBM.SYSDUMMY1用户可以通过访问这个表获取相应的信息,例如:
'SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1' 命令返回当前系统日期
'SELECT 1+1 FROM SYSIBM.SYSDUMMY1' 执行数学运算
'SELECT NEXTVAL FOR MYSEQ FROM SYSIBM.SYSDUMMY1' 得到序列值
1 依赖
<!-- DB2驱动jar包 -->
<!-- https://mvnrepository.com/artifact/cn.guoyukun.jdbc/db2jcc -->
<dependency>
<groupId>cn.guoyukun.jdbc</groupId>
<artifactId>db2jcc</artifactId>
<version>1.4.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/cn.guoyukun.jdbc/db2jcc_license_cu -->
<dependency>
<groupId>cn.guoyukun.jdbc</groupId>
<artifactId>db2jcc_license_cu</artifactId>
<version>1.4.2</version>
</dependency>
2 数据源配置 (druid)
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.ibm.db2.jcc.DB2Driver
url: jdbc:db2://xx.x.xxx.xxx:端口/数据库名称
username: 用户名
password: 密码
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 1 from SYSIBM.SYSDUMMY1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
filters: stat,wall,log4j
logSlowSql: true
3 设置自增
CREATE TABLE
T_RUNNING_THREAD
(
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 ),
NAME VARCHAR(150),
BEGINTIME VARCHAR(50),
ENDTIME VARCHAR(50),
DESC VARCHAR(4000),
SUCCESSFLAG CHARACTER(1),
PRIMARY KEY (ID)
)
加上GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 )这句
start with 1 :就是从1开始
increment by 1 :每次自增1
4 Mybatis单条插入并返回id
sqlmap:
在insert节点加上如下配置 是否使用 useGeneratedKeys 开关设置为true,keyProperty对应的就是要返回的字段名称
有一点需要注意的是,keyProperty对应的字段在POJO中必须有相应的setter方法,setter的参数类型还要一致,否则会报错。
<!-- 插入字典分类,返回自增主键 -->
<insert id="insertTermtypesId" parameterType="CommonTermtypes"
useGeneratedKeys="true" keyProperty="termTypeId">
insert into scf.cm_termtypes(
PARENTTYPEID,
TERMTYPECODE,TERMTYPENAME,
ITEMSTYPE,ISENABLED,
TYPEREMARK,CREATORID,
CREATETIME,LASTMODIFYID,
LASTMODIFYTIME
)
values (
#{parentTypeId},
#{termTypeCode},
#{termTypeName},
#{itemsType},
#{isEnabled},
#{typerEmark,jdbcType=VARCHAR},
#{creatorId,jdbcType=CHAR},
#{creatTime,jdbcType=TIMESTAMP},
#{lastModifyId,jdbcType=CHAR},
#{lastModifyTime,jdbcType=TIMESTAMP}
)
</insert>
不能加databaseId属性,否则会报错
<insert id="insertTreeTermOne" parameterType="CommonTreetermdata"
useGeneratedKeys="true" keyProperty="id" databaseId="db2">
可以不写userGeneratedKeys属性和keyProperty属性
<insert id="insertTreeTermOne" parameterType="CommonTreetermdata">
db2的SQL语句中需要加上库名称,如ccf.tablename ,否则会报错DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=XXX.CM_TERMTYPES
java:
在成功的插入数据后,会把设置的主键id填充到该commonTermtypes对象中
public static void main (String[] args){
CommonTermtypes commonTermtypes = new CommonTermtypes()
commonTermtypes.setxxx(xxx);
commonTermtypes.setxxx(xxx);
... ...
CommonTermtypesMapper.insert(commonTermtypes);
// 获取插入成功后自增的id
commonTermtypes.getTermTypeId();
}
5 Mybatis批量插入
SQL语句
insert into t(id, name) values (1, 'A'), (2, 'B'), (3, 'C');
insert into t(id,name)
select * from (
select 1,'Jimmy' from sysibm.sysdummy1 fetch first 1 row only
union all select 2,'Connie' from sysibm.sysdummy1 fetch first 1 row only
)
sqlmap
<insert id="insertTermtypesBatch" parameterType="CommonTermtypes">
insert into scf.cm_termtypes(
TERMTYPEID,
PARENTTYPEID,
TERMTYPECODE,TERMTYPENAME,
ITEMSTYPE,ISENABLED,
TYPEREMARK,CREATORID,
CREATETIME,LASTMODIFYID,
LASTMODIFYTIME
)
values
<foreach collection="list" item="item" separator=",">
(
#{item.termTypeId},
#{item.parentTypeId},
#{item.termTypeCode},
#{item.termTypeName},
#{item.itemsType},
#{item.isEnabled},
#{item.typerEmark,jdbcType=VARCHAR},
#{item.creatorId,jdbcType=CHAR},
#{item.creatTime,jdbcType=TIMESTAMP},
#{item.lastModifyId,jdbcType=CHAR},
#{item.lastModifyTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>