最近公司要是实现的两个Oracle数据库数据的同步,由于dblink被禁,考虑用动态SQL实现下
1.通过表名查询对应表的列字段
<select id="queryColumnsByTable" parameterType="java.lang.String" resultType="java.lang.String">
SELECT T.COLUMN_NAME
FROM USER_TAB_COLUMNS T
WHERE T.TABLE_NAME = #{tableName}
</select>
2.通过表名查询要同步的数据
- sql如下:
<select id="querySyncData" resultType="map">
select * from ${tableName}
</select>
- 返回查询的数据
List<Map<String, Object>> querySyncData(@Param("tableName") String tableName);
3.同步数据的插入
- 插入数据构建
tableName = tableName; // 表名
columns = Map<String, Object> ; // key列名 value列名
syncDataList = List<Map<String, Object>>; // 查询同步的数据
batchMap.put("tableName", tableName);
batchMap.put("columns", columns);
batchMap.put("list", syncDataList);
void insertSyncData(@Param("batchMap") Map<String, Object> batchMap);
- SQL
<insert id="insertSyncData" parameterType="map">
insert all
<foreach collection="batchMap.list" item="line">
into ${batchMap.tableName} (
<foreach collection="batchMap.columns" index="key" item="value" separator=",">
${key}
</foreach>
) values (
<foreach collection="line" index="key" item="value" separator=",">
#{value}
</foreach>
)
</foreach>
select 1 from dual
</insert>
