java mysql nclob_java-Oracle 12.2数据库中CLOB的JDBC流给出了错误...

从Oracle 12.1到Oracle 12.2数据库的迁移遇到了问题.

显然,升级后CLOB列的编码不同.

我正在使用JDBC8进行数据库连接.

以下数据库脚本已执行：

create table oracle_compatibility_test

(

id number(19) not null primary key,

name varchar2(500) not null,

name_as_clob clob not null

);

insert all

into oracle_compatibility_test (id, name, name_as_clob) values (1, 'test1', 'test1')

into oracle_compatibility_test (id, name, name_as_clob) values (2, 'test2???rt', 'test2???rt')

select *

from dual;

commit;

然后,我编写了一个程序,以通过JDBC读取插入的值并将其打印到控制台：

package de.soptim.pie;

import java.io.IOException;

import java.io.Reader;

import java.nio.charset.Charset;

import java.nio.charset.StandardCharsets;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Locale;

import java.util.Map;

import java.util.Properties;

public final class OracleCompatibilityTest {

//A 12.2 Oracle Database

private static final String DB1 = "jdbc:oracle:thin:@server1:1521";

//A 12.1 Oracle Database

private static final String DB2 = "jdbc:oracle:thin:@server2:1521";

private static final String USED_DATABASE = DB1;

public static void main(String[] args) {

printDatabaseCharset();

try (Connection connection = DriverManager.getConnection(USED_DATABASE, databaseProperties());

Statement statement = connection.createStatement();

ResultSet result = statement.executeQuery(

"select name, name_as_clob, convert(name_as_clob, 'AL32UTF8') as utf_8_clob " +

"from oracle_compatibility_test " +

"order by id asc")) {

readRow(result);

readRow(result);

} catch (Exception e) {

throw new RuntimeException(e);

}

}

private static void printDatabaseCharset() {

try (Connection connection = DriverManager.getConnection(USED_DATABASE, databaseProperties());

Statement statement = connection.createStatement();

ResultSet result = statement.executeQuery(

"select * from database_properties where property_name = 'NLS_CHARACTERSET'")) {

result.next();

System.out.println("read charset from database: " + result.getString("property_value") + "

");

} catch (Exception e) {

throw new RuntimeException(e);

}

}

private static void readRow(ResultSet result) throws SQLException, IOException {

result.next();

String name = result.getString("name");

String nameAsClob = result.getString("name_as_clob");

Clob clobAsClobObject = result.getClob("name_as_clob");

String clobObjectAsString = clobAsClobObject.getSubString(1, (int) clobAsClobObject.length());

String clobReadAsCharacterStream = readFromCharacterStream(result);

String utf8clob = result.getString("utf_8_clob");

StringBuilder sb = new StringBuilder();

sb.append("read name: ")

.append(name)

.append("

name read as clob: ")

.append(nameAsClob)

.append("

name read as clob-object: ")

.append(clobObjectAsString)

.append("

clob read as character-stream: ")

.append(clobReadAsCharacterStream)

.append("

clob converted to utf-8: ")

.append(utf8clob)

.append("

");

System.out.println(sb.toString());

}

private static String readFromCharacterStream(ResultSet result) throws SQLException, IOException {

try (Reader reader = result.getCharacterStream("name_as_clob")) {

StringBuilder stringBuilder = new StringBuilder();

int c;

while ((c = reader.read()) != -1) {

stringBuilder.append((char) c);

}

return stringBuilder.toString();

}

}

private static Properties databaseProperties() {

Properties prop = new Properties();

prop.put("user", "user");

prop.put("password", "password");

return prop;

}

}

使用Oracle 12.1时,输出如下：

read charset from database: WE8ISO8859P15

read name: test1

name read as clob: test1

name read as clob-object: test1

clob read as character-stream: test1

clob converted to utf-8: t e s t 1

read name: test2???rt

name read as clob: test2???rt

name read as clob-object: test2???rt

clob read as character-stream: test2???rt

clob converted to utf-8: t e s t 2 ? ? ? r t

使用Oracle 12.2时,输出如下：

read charset from database: WE8ISO8859P15

read name: test1

name read as clob: test1

name read as clob-object: test1

clob read as character-stream: test1

clob converted to utf-8: t e s t 1

read name: test2???rt

name read as clob: test2???rt

name read as clob-object: test2???rt

clob read as character-stream: test2???rt

clob converted to utf-8: t e s t 2 ? ? ? r t

JDBC-Driver错误地将字符集自动检测为UTF-8,但是Stream确实在ISO8859-15中.不能在JDBC8中显式设置字符集.

从数据库返回的流在Oracle 12.1下以UTF-8编码