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;