java oracle clob string 大字符串存储【转】

fteair · 发表于 2020-6-24 14:36:25

public class ClobTest {
static String url = "jdbc:oracle:thin:@10.12.10.18:1521:orcl";
static String user = "cwbe1_9999";
static String pwd = "or777";
static String text = "这是要插入到CLOB里面的数据,更新数据！" + "update";
private static int clobImport() throws ClassNotFoundException, SQLException {
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
String sql = "insert into ETLNEEDPARAM(F_KEY,F_VALUE) values ('defaultpo',?)";// 要执行的SQL语句
PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句
// PreparedStatement支持SQL带有问号？，可以动态替换？的内容。
Reader clobReader = new StringReader(text); // 将 text转成流形式
stmt.setCharacterStream(1, clobReader, text.length());// 替换sql语句中的？
int num = stmt.executeUpdate();// 执行SQL
if (num > 0) {
System.out.println("ok");
} else {
System.out.println("NO");
}
stmt.close();
conn.close();
return num;
}
private static int clobUpdate(String key) throws ClassNotFoundException, SQLException {
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
String sql = "update ETLNEEDPARAM set F_VALUE = ? where F_KEY = ? ";// 要执行的SQL语句
PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句
// PreparedStatement支持SQL带有问号？，可以动态替换？的内容。
Reader clobReader = new StringReader(text); // 将 text转成流形式
stmt.setString(2, key);
stmt.setCharacterStream(1, clobReader, text.length());// 替换sql语句中的？
int num = stmt.executeUpdate();// 执行SQL
if (num > 0) {
System.out.println("ok");
} else {
System.out.println("NO");
}
stmt.close();
conn.close();
return num;
}
private static String clobExport() throws ClassNotFoundException, SQLException, IOException {
CLOB clob = null;
String sql = "select F_VALUE from ETLNEEDPARAM where F_KEY ='test1'";
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
String content = "";
if (rs.next()) {
clob = (oracle.sql.CLOB) rs.getClob("F_VALUE"); // 获得CLOB字段str
// 注释：用 rs.getString("str")无法得到数据，返回的是 NULL;
content = ClobToString(clob);
}
stmt.close();
conn.close();
return content;
}
// 将字CLOB转成STRING类型
public static String ClobToString(CLOB clob) throws SQLException, IOException {
String reString = "";
Reader is = clob.getCharacterStream();// 得到流
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
StringBuffer sb = new StringBuffer();
// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
while (s != null) {
sb.append(s);
s = br.readLine();
}
reString = sb.toString();
return reString;
}
public static void main(String[] args) throws IOException,ClassNotFoundException, SQLException {
// System.out.println(clobImport());
System.out.println(clobUpdate("fmo"));
System.out.println(clobUpdate("epo"));
// System.out.println(clobExport());
}