mybatis整合DB2数据库

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>