oracle 数据库网络传输,数据库表数据传输到Oracle方案

JdbcTemplate jdbc = new JdbcTemplate(dataSource);  //使用已配置好的数据源

SqlRowSet rowSet = jdbc.queryForRowSet(“select * from temp_table where rownum=1“);  //临时表和正式表结构完全一样

String[] columnNames = rowSet.getMetaData().getColumnNames();  //获取表的所有列

jdbc.update(sql);  //执行insert或merge语句

String[] values=StringUtils.splitByWholeSeparatorPreserveAllTokens(line,”|@|”);  //切分数据行

StringBuilder sqlInsert = new StringBuilder(“insert all\n”); //拼接sql insert语句

sqlInsert.append(“into temp_table values (‘“).append(StringUtilis.join(split,”’,’”,0,Math.min(split.length, columnNames.length))).append(“’)\n”);

sqlInsert.append(“select 1 from dual”);

StringBuilder sqlMerge = new StringBuilder(“merge into formal_table n\n”); //拼接sql merge语句

sqlMerge.append(“using (select * from temp_table) t on t.id=n.id\n”);

sqlMerge.append(“when matched then update set ”);

for(int i=1;i

sqlMerge.setCharAt(sqlMerge.length()-1, ‘\n’); //替换末尾逗号为换行符

sqlMerge.append(“when not matched then insert values (t.”).append(StringUtils.join(columnNames, “,t.”)).append(“)”);