Oracle导出SQL,解决Clob字段长度大于4000,导入不成功的问题

1.pom.xml

<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc</artifactId>
	<version>11.02</version>
</dependency>
<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>4.6.10</version>
</dependency>

2.数据库配置文件,db.setting,放在maven项目的src/main/resources下

url = jdbc:oracle:thin:@127.0.0.1:1521:orcl
user = xxx
pass = xxx
## 可选配置
# 是否在日志中显示执行的SQL
showSql = true
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = true

3.java代码

import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import java.util.stream.Collectors;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.text.StrSpliter;
import cn.hutool.db.Db;
import oracle.sql.CLOB;

public class OracleBigDataToSql {
	public static void main(String[] args) {
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String tableName = "你的表名";
		try {
			File sqlFile = null;
			connection = Db.use().getConnection();
			ps = connection.prepareStatement("select * from " + tableName);
			rs = ps.executeQuery();
			ResultSetMetaData metaData = ps.getMetaData();
			int columnCount = metaData.getColumnCount();

			sqlFile = new File("C:\\"+tableName+"_CLOB.sql");
			while (rs.next()) {
				StringBuffer declare = new StringBuffer();
				declare.append("declare\r\n");
				StringBuffer sb = new StringBuffer();
				sb.append("insert into " + tableName + "(");

				StringBuffer k = new StringBuffer();
				StringBuffer v = new StringBuffer();
				for (int i = 0; i < columnCount; i++) {
					k.append(metaData.getColumnName(i + 1)).append(",");
					int type = metaData.getColumnType(i + 1);
					Object value = rs.getObject(i + 1);
					if (value != null) {
						if (Arrays.asList(Types.VARCHAR, Types.CHAR, Types.LONGVARCHAR, Types.CLOB).contains(type)) {
							if (Types.CLOB == type) {
								CLOB clob = (CLOB) value;
								String string = clob.stringValue();
								declare.append("clob_" + i + " clob:=" + splitBigText(string) + ";\r\n");
								v.append("clob_" + i);
							} else {
								v.append("'" + value + "'");
							}
						} else if (Arrays.asList(Types.DATE, Types.TIME, Types.TIMESTAMP).contains(type)) {
							if (Types.DATE == type) {
								v.append("to_date('" + DateUtil.formatDate((Date) value) + "','yyyy-mm-dd')");
							} else if (Types.TIME == type) {
								v.append("to_date('" + DateUtil.formatTime((Time) value) + "','hh24:mi:ss')");
							} else if (Types.TIMESTAMP == type) {
								v.append("to_date('" + DateUtil.formatDateTime((Timestamp) value)
										+ "','yyyy-mm-dd hh24:mi:ss')");
							}
						} else {
							v.append(value);
						}
					} else {
						v.append(value);
					}
					v.append(",");
				}
				k.deleteCharAt(k.length() - 1);
				v.deleteCharAt(v.length() - 1);
				sb.append(k).append(") values(").append(v).append(");");

				declare.append("begin\r\n").append(sb).append("\r\n").append("end;\r\n/");

				System.out.println(declare.toString());
				FileUtil.appendUtf8String(declare.toString() + "\r\n", sqlFile);
			}
		} catch (Exception e) {
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			Db.use().closeConnection(connection);
		}
	}

	public static String splitBigText(String text) {
		text = text.replaceAll("\'", "\'\'");
		String[] splitByLength = StrSpliter.splitByLength(text, 3000);
		return Arrays.asList(splitByLength).stream().map(o -> "'" + o + "'").collect(Collectors.joining("||"));
	}
}

4.原理

declare 
a_clob clob:='1111'||'22222';
begin
insert into aa(a1) values (a_clob);
end;