# rouyi-db2

**Repository Path**: liao_ru_qi/rouyi-db2

## Basic Information

- **Project Name**: rouyi-db2
- **Description**: rouyi-oracel 改的 db2版本管理框架
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 10
- **Forks**: 4
- **Created**: 2021-08-25
- **Last Updated**: 2025-07-30

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Java, db2

## README

# Oracle转db2 
若依版本 [https://github.com/yangzongzhuan/RuoYi-Oracle](https://github.com/yangzongzhuan/RuoYi-Oracle)
## sql
number -》 INTEGER 和 BIGINT 两种 根据number长度决定
varchar2  varchar -》 VARCHAR
char -》 CHARACTER
date  -》 TIMESTAMP    db2获取当前时间方法只有这一组 我找到的  CURRENT TIMESTAMP   （ sysdate -》 CURRENT TIMESTAMP）
-- db2日期时间 date 只有年月日 time 只有时分秒 TIMESTAMP 年月日时分秒  https://www.cnblogs.com/lbnnbs/p/4781523.html
索引要根据情况缩减 db2 长度太长会出问题

## 依赖及建立连接
```xml
        <db2.version>db2jcc4</db2.version>

        <!--db2驱动 admin -->
<dependency>
<groupId>com.ibm.db2.jcc</groupId>
<artifactId>db2jcc</artifactId>
<version>${db2.version}</version>
</dependency>
```
```yaml
        driverClassName: com.ibm.db2.jcc.DB2Driver
        druid:
         # 主库数据源
         master:
          url: jdbc:db2://192.168.2.245:50000/HMHDDB:currentSchema=DEMO;
          username: root
          password: 123456
```
> 修改到此步已经可以启动了，但运行会出错，xml文件里的sql还没改
## 改xml sql
```sql
-- 将insert的
select (.*).nextval as .* from DUAL
-- 替换成下面 （获取自增 需要有 sequence 可用正则替换）
values nextval for $1


 to_date()
 -- 里面不能使用#{}  用 concat( #{},'') 

 -- SysUserOnlineMapper.saveOnline
 merge into sys_user_online a
 using (select count(1) as co,'${sessionId}'  as sessionid
 from sys_user_online
 where sessionid = #{sessionId}) b
on (co>0 and b.sessionid = a.sessionid)
 when matched then
update
 set login_name       = #{loginName},
 dept_name        = #{deptName},
 ipaddr           = #{ipaddr},
 login_location   = #{loginLocation},
 browser          = #{browser},
 os               = #{os},
 status           = #{status},
 start_timestamp  = #{startTimestamp},
 last_access_time = #{lastAccessTime},
 expire_time      = #{expireTime}
 when not matched then
insert
values
 (#{sessionId}, #{loginName}, #{deptName}, #{ipaddr}, #{loginLocation}, #{browser}, #{os}, #{status}, #{startTimestamp}, #{lastAccessTime}, #{expireTime})


 SysMenuMapper
 visible = 0  =>    visible = '0'
 nvl(m.perms,'') => (case when m.perms is not null then m.perms else '' end)



```
> 进行到这里 可以 登录了

> 测试用户管理

> 分页
```yaml
pagehelper:
 helperDialect: DB2
```

```xml
<!--mybatis-config.xml-->
<properties>
 <property name="db2databaseName." value="demo."/><!-- db2数据库配置名称 后面加上一个点-->
 <property name="db2databaseName" value="demo"/><!-- db2数据库配置名称 后面不加点-->
</properties>
```
```sql
${db2databaseName.}FIND_IN_SET (char(${deptId}), ancestors)
FIND_IN_SET 方法不能使用#{} 会报错(-418 42610 参数标识符运用无效) 需改${} 前加${db2databaseName.}
char() 将数字转字符

```

>新增
```sql
-- Unique判断唯一的sql
select * from (select rownumber() over() as rownum,a.* from (
                                                             sql
                                                             ) a ) a where	 rownum <![CDATA[ <= ]]> 1
-- checkLoginNameUnique  为毛你是count 还加rownum啊啊啊
select count(1) from sys_user where login_name=#{loginName}
-- insert all  db2没有这个 用mysql那个

 insert into sys_role_dept(role_id, dept_id) values
        <foreach item="item" index="index" collection="list" separator=",">
 (#{item.roleId},#{item.deptId})
        </foreach>


```
> 修改 导出 等其余操作均无问题

> 角色 查询 新增 等均无问题
> 修改
>
```sql
--selectMenuTree 
select concat(trim(char(m.menu_id)),
-- char 数据库是char(10),但是实际保存时只给了6位,那么数据库自动添加4位空格 使用char时配合trim方法
```


> 最后 代码生成
```xml
    <sql id="db2Tanle">
		select lower(tabname) as table_name,create_time as create_time,STATS_TIME as update_time,remarks as table_comment from syscat.tables
		where tabschema  =UPPER('${db2databaseName}')
		  and tabname not like 'QRTZ_%'
		  and tabname not like 'GEN_%'
		  and lower(tabname) not in  (SELECT table_name FROM gen_table)
	</sql>

	<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
		<include refid="db2Tanle"/>
		<if test="tableName != null and tableName != ''">
			AND lower(dt.table_name)  like lower(concat(concat('%', #{tableName}), '%'))
		</if>
		<if test="tableComment != null and tableComment != ''">
			AND lower(dtc.comments) like lower(concat(concat('%', #{tableComment}), '%'))
		</if>
	</select>
	
	<select id="selectDbTableListByNames" resultMap="GenTableResult">
		<include refid="db2Tanle"/>
		and lower(dt.table_name) in
	    <foreach collection="array" item="name" open="(" separator="," close=")">
 			#{name}
        </foreach> 
	</select>
    <select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
			<include refid="db2Tanle"/>
		  and lower(tabname) = #{tableName}
	</select>
```

>最后将generator的vm文件修改

-- sql.vm
-- 菜单 SQL
insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(${table.menuId}, '${functionName}', ${parentMenuId}, 1, '/${moduleName}/${businessName}', 'C', '0', '${permissionPrefix}:view', '#', 'admin', CURRENT TIMESTAMP, '', null, '${functionName}菜单');

-- 按钮 SQL
insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(nextval for seq_sys_menu, '${functionName}查询', ${table.menuId}, 1,  '#',  'F', '0', '${permissionPrefix}:list',         '#', 'admin', CURRENT TIMESTAMP, '', null, '');

insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(nextval for seq_sys_menu, '${functionName}新增', ${table.menuId}, 2,  '#',  'F', '0', '${permissionPrefix}:add',          '#', 'admin', CURRENT TIMESTAMP, '', null, '');

insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(nextval for seq_sys_menu, '${functionName}修改', ${table.menuId}, 3,  '#',  'F', '0', '${permissionPrefix}:edit',         '#', 'admin', CURRENT TIMESTAMP, '', null, '');

insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(nextval for seq_sys_menu, '${functionName}删除', ${table.menuId}, 4,  '#',  'F', '0', '${permissionPrefix}:remove',       '#', 'admin', CURRENT TIMESTAMP, '', null, '');

insert into sys_menu (menu_id, menu_name, parent_id, order_num, url, menu_type, visible, perms, icon, create_by, create_time, update_by, update_time, remark)
values(nextval for seq_sys_menu, '${functionName}导出', ${table.menuId}, 5,  '#',  'F', '0', '${permissionPrefix}:export',       '#', 'admin', CURRENT TIMESTAMP, '', null, '');


-- mapper.xml.vm

sql 修改上面有说明就是那两个insert





```yaml
version: '3.1'
services:
  db2:
    image: ibmcom/db2
    restart: always
    container_name: db2
    privileged: true
    environment:
      LICENSE: accept
      DB2INST1_PASSWORD: 123456
      DBNAME: DB2DB
    volumes:
      - ./db2data:/database
    ports:
      - 50000:50000
```

```bash
#创建用户组
groupadd db2group
#添加用户到该组
useradd -m -g db2group -d /home/db2root db2root
#修改test密码
passwd db2root
#连续输入两次密码
#切换到db2inst1用户下给db2root赋予连接权限
su db2inst1
#连接数据库
db2 connect to db2db
#赋予连接权限
db2 grant connect on database to user db2root
#赋予增删改查权限
db2 grant DATAACCESS on database to user db2root
#赋予数据库权限
db2 grant DBADM on database to user db2root
#关闭连接
db2 connect reset
#其他常用命令
#创建数据库
db2 create db db2db using codeset utf-8 territory CN
#查看当前所有数据库
db2 list db direcotry
#查看库中的表名
db2 list tables
#更多命令省略
```