This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 20936fe0543 [branch-2.1][improvement](jdbc catalog) Compatible with
ojdbc6 by adding version check (#39408)
20936fe0543 is described below
commit 20936fe05434758a1145cee94d4c24e17dc58643
Author: zy-kkk <[email protected]>
AuthorDate: Sat Aug 17 16:43:01 2024 +0800
[branch-2.1][improvement](jdbc catalog) Compatible with ojdbc6 by adding
version check (#39408)
pick (#39341)
In previous versions, we used a method based on JDBC 4.2 to read data,
so it was equivalent to abandoning support for ojdbc6. However, we
recently found that a large number of users still use Oracle version
11g, which will have some unexpected compatibility issues when using
ojdbc8 to connect. Therefore, I use version verification to make it
compatible with both ojdbc6 and ojdbc8, so that good compatibility can
be obtained through ojdbc6, and better reading efficiency can be
obtained through ojdbc8.
---
fe/be-java-extensions/jdbc-scanner/pom.xml | 4 +
.../org/apache/doris/jdbc/BaseJdbcExecutor.java | 30 +++++-
.../org/apache/doris/jdbc/OracleJdbcExecutor.java | 115 ++++++++++++++-------
fe/pom.xml | 6 ++
.../jdbc/test_oracle_jdbc_catalog.out | 4 +
.../jdbc/test_oracle_jdbc_catalog.groovy | 16 +++
6 files changed, 137 insertions(+), 38 deletions(-)
diff --git a/fe/be-java-extensions/jdbc-scanner/pom.xml
b/fe/be-java-extensions/jdbc-scanner/pom.xml
index bebf1c4ffc4..54da3601cc4 100644
--- a/fe/be-java-extensions/jdbc-scanner/pom.xml
+++ b/fe/be-java-extensions/jdbc-scanner/pom.xml
@@ -45,6 +45,10 @@ under the License.
<artifactId>HikariCP</artifactId>
<scope>provided</scope>
</dependency>
+ <dependency>
+ <groupId>org.semver4j</groupId>
+ <artifactId>semver4j</artifactId>
+ </dependency>
</dependencies>
<build>
diff --git
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/BaseJdbcExecutor.java
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/BaseJdbcExecutor.java
index 4336d09e744..08f5e65181e 100644
---
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/BaseJdbcExecutor.java
+++
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/BaseJdbcExecutor.java
@@ -32,11 +32,13 @@ import org.apache.log4j.Logger;
import org.apache.thrift.TDeserializer;
import org.apache.thrift.TException;
import org.apache.thrift.protocol.TBinaryProtocol;
+import org.semver4j.Semver;
import java.io.FileNotFoundException;
import java.lang.reflect.Array;
import java.net.MalformedURLException;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
@@ -65,6 +67,7 @@ public abstract class BaseJdbcExecutor implements
JdbcExecutor {
protected VectorTable outputTable = null;
protected int batchSizeNum = 0;
protected int curBlockRows = 0;
+ protected String jdbcDriverVersion;
public BaseJdbcExecutor(byte[] thriftParams) throws Exception {
TJdbcExecutorCtorParams request = new TJdbcExecutorCtorParams();
@@ -91,11 +94,12 @@ public abstract class BaseJdbcExecutor implements
JdbcExecutor {
.setConnectionPoolKeepAlive(request.connection_pool_keep_alive);
JdbcDataSource.getDataSource().setCleanupInterval(request.connection_pool_cache_clear_time);
init(config, request.statement);
+ this.jdbcDriverVersion = getJdbcDriverVersion();
}
public void close() throws Exception {
try {
- if (stmt != null) {
+ if (stmt != null && !stmt.isClosed()) {
try {
stmt.cancel();
} catch (SQLException e) {
@@ -524,6 +528,30 @@ public abstract class BaseJdbcExecutor implements
JdbcExecutor {
}
}
+ private String getJdbcDriverVersion() {
+ try {
+ if (conn != null) {
+ DatabaseMetaData metaData = conn.getMetaData();
+ return metaData.getDriverVersion();
+ } else {
+ return null;
+ }
+ } catch (SQLException e) {
+ LOG.warn("Failed to retrieve JDBC Driver version", e);
+ return null;
+ }
+ }
+
+ protected boolean isJdbcVersionGreaterThanOrEqualTo(String version) {
+ Semver currentVersion = Semver.coerce(jdbcDriverVersion);
+ Semver targetVersion = Semver.coerce(version);
+ if (currentVersion != null && targetVersion != null) {
+ return currentVersion.isGreaterThanOrEqualTo(targetVersion);
+ } else {
+ return false;
+ }
+ }
+
protected String trimSpaces(String str) {
int end = str.length() - 1;
while (end >= 0 && str.charAt(end) == ' ') {
diff --git
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/OracleJdbcExecutor.java
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/OracleJdbcExecutor.java
index 662f324eb23..6f38895335b 100644
---
a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/OracleJdbcExecutor.java
+++
b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/OracleJdbcExecutor.java
@@ -33,7 +33,7 @@ import java.nio.charset.CharsetDecoder;
import java.nio.charset.StandardCharsets;
import java.sql.Clob;
import java.sql.SQLException;
-import java.time.LocalDate;
+import java.sql.Timestamp;
import java.time.LocalDateTime;
public class OracleJdbcExecutor extends BaseJdbcExecutor {
@@ -65,42 +65,83 @@ public class OracleJdbcExecutor extends BaseJdbcExecutor {
@Override
protected Object getColumnValue(int columnIndex, ColumnType type, String[]
replaceStringList) throws SQLException {
- try {
- switch (type.getType()) {
- case TINYINT:
- return resultSet.getObject(columnIndex + 1, Byte.class);
- case SMALLINT:
- return resultSet.getObject(columnIndex + 1, Short.class);
- case INT:
- return resultSet.getObject(columnIndex + 1, Integer.class);
- case BIGINT:
- return resultSet.getObject(columnIndex + 1, Long.class);
- case FLOAT:
- return resultSet.getObject(columnIndex + 1, Float.class);
- case DOUBLE:
- return resultSet.getObject(columnIndex + 1, Double.class);
- case LARGEINT:
- case DECIMALV2:
- case DECIMAL32:
- case DECIMAL64:
- case DECIMAL128:
- return resultSet.getObject(columnIndex + 1,
BigDecimal.class);
- case DATE:
- case DATEV2:
- return resultSet.getObject(columnIndex + 1,
LocalDate.class);
- case DATETIME:
- case DATETIMEV2:
- return resultSet.getObject(columnIndex + 1,
LocalDateTime.class);
- case CHAR:
- case VARCHAR:
- case STRING:
- return resultSet.getObject(columnIndex + 1);
- default:
- throw new IllegalArgumentException("Unsupported column
type: " + type.getType());
- }
- } catch (AbstractMethodError e) {
- LOG.warn("Detected an outdated ojdbc driver. Please use ojdbc8 or
above.", e);
- throw new SQLException("Detected an outdated ojdbc driver. Please
use ojdbc8 or above.");
+ if (isJdbcVersionGreaterThanOrEqualTo("12.2.0")) {
+ return newGetColumnValue(columnIndex, type, replaceStringList);
+ } else {
+ return oldGetColumnValue(columnIndex, type, replaceStringList);
+ }
+ }
+
+ private Object newGetColumnValue(int columnIndex, ColumnType type,
String[] replaceStringList) throws SQLException {
+ switch (type.getType()) {
+ case TINYINT:
+ return resultSet.getObject(columnIndex + 1, Byte.class);
+ case SMALLINT:
+ return resultSet.getObject(columnIndex + 1, Short.class);
+ case INT:
+ return resultSet.getObject(columnIndex + 1, Integer.class);
+ case BIGINT:
+ return resultSet.getObject(columnIndex + 1, Long.class);
+ case FLOAT:
+ return resultSet.getObject(columnIndex + 1, Float.class);
+ case DOUBLE:
+ return resultSet.getObject(columnIndex + 1, Double.class);
+ case LARGEINT:
+ case DECIMALV2:
+ case DECIMAL32:
+ case DECIMAL64:
+ case DECIMAL128:
+ return resultSet.getObject(columnIndex + 1, BigDecimal.class);
+ case DATETIME:
+ case DATETIMEV2:
+ return resultSet.getObject(columnIndex + 1,
LocalDateTime.class);
+ case CHAR:
+ case VARCHAR:
+ case STRING:
+ return resultSet.getObject(columnIndex + 1);
+ default:
+ throw new IllegalArgumentException("Unsupported column type: "
+ type.getType());
+ }
+ }
+
+ private Object oldGetColumnValue(int columnIndex, ColumnType type,
String[] replaceStringList) throws SQLException {
+ switch (type.getType()) {
+ case TINYINT:
+ byte tinyIntVal = resultSet.getByte(columnIndex + 1);
+ return resultSet.wasNull() ? null : tinyIntVal;
+ case SMALLINT:
+ short smallIntVal = resultSet.getShort(columnIndex + 1);
+ return resultSet.wasNull() ? null : smallIntVal;
+ case INT:
+ int intVal = resultSet.getInt(columnIndex + 1);
+ return resultSet.wasNull() ? null : intVal;
+ case BIGINT:
+ long bigIntVal = resultSet.getLong(columnIndex + 1);
+ return resultSet.wasNull() ? null : bigIntVal;
+ case FLOAT:
+ float floatVal = resultSet.getFloat(columnIndex + 1);
+ return resultSet.wasNull() ? null : floatVal;
+ case DOUBLE:
+ double doubleVal = resultSet.getDouble(columnIndex + 1);
+ return resultSet.wasNull() ? null : doubleVal;
+ case LARGEINT:
+ case DECIMALV2:
+ case DECIMAL32:
+ case DECIMAL64:
+ case DECIMAL128:
+ BigDecimal decimalVal = resultSet.getBigDecimal(columnIndex +
1);
+ return resultSet.wasNull() ? null : decimalVal;
+ case DATETIME:
+ case DATETIMEV2:
+ Timestamp timestampVal = resultSet.getTimestamp(columnIndex +
1);
+ return resultSet.wasNull() ? null :
timestampVal.toLocalDateTime();
+ case CHAR:
+ case VARCHAR:
+ case STRING:
+ Object stringVal = resultSet.getObject(columnIndex + 1);
+ return resultSet.wasNull() ? null : stringVal;
+ default:
+ throw new IllegalArgumentException("Unsupported column type: "
+ type.getType());
}
}
diff --git a/fe/pom.xml b/fe/pom.xml
index af4a3f34c53..5cb0627d3fc 100644
--- a/fe/pom.xml
+++ b/fe/pom.xml
@@ -360,6 +360,7 @@ under the License.
<flatbuffers.version>1.12.0</flatbuffers.version>
<jacoco.version>0.8.10</jacoco.version>
<airlift.version>202</airlift.version>
+ <semver4j.version>5.3.0</semver4j.version>
</properties>
<profiles>
<profile>
@@ -1671,6 +1672,11 @@ under the License.
<artifactId>concurrent</artifactId>
<version>${airlift.version}</version>
</dependency>
+ <dependency>
+ <groupId>org.semver4j</groupId>
+ <artifactId>semver4j</artifactId>
+ <version>${semver4j.version}</version>
+ </dependency>
</dependencies>
</dependencyManagement>
<dependencies>
diff --git
a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
index 9fea31242a2..82afecb61bd 100644
--- a/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_oracle_jdbc_catalog.out
@@ -265,3 +265,7 @@ Doris
-- !query_lower_3 --
doris
+-- !query_ojdbc6_all_types --
+1 111 123 7456123.89 573 34 673.43 34.1264 60.0
23.231 99 9999 999999999 999999999999999999 999 99999
9999999999 9999999999999999999 1 china beijing alice
abcdefghrjkmnopq 123.45 12300 0.0012345 2022-01-21T05:23:01
2019-11-12T20:33:57.999 2019-11-12T20:33:57.999998
2019-11-12T20:33:57.999996 2019-11-12T20:33:57.999997 223-9 12
10:23:1.123457000
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N
+
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
index 057723a808e..571dda0e5d8 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_oracle_jdbc_catalog.groovy
@@ -21,6 +21,7 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
String s3_endpoint = getS3Endpoint()
String bucket = getS3BucketName()
String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/ojdbc8.jar";
+ String driver6_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/ojdbc6.jar";
if (enabled != null && enabled.equalsIgnoreCase("true")) {
String catalog_name = "oracle_catalog";
String internal_db_name = "regression_test_jdbc_catalog_p0";
@@ -281,5 +282,20 @@ suite("test_oracle_jdbc_catalog",
"p0,external,oracle,external_docker,external_d
qt_query_lower_3 """ select doris_3 from doris_test.lower_test; """
sql """drop catalog if exists ${catalog_name} """
+
+ // test for ojdbc6
+ sql """drop catalog if exists oracle_ojdbc6; """
+ sql """create catalog if not exists oracle_ojdbc6 properties(
+ "type"="jdbc",
+ "user"="doris_test",
+ "password"="123456",
+ "jdbc_url" =
"jdbc:oracle:thin:@${externalEnvIp}:${oracle_port}:${SID}",
+ "driver_url" = "${driver6_url}",
+ "driver_class" = "oracle.jdbc.OracleDriver"
+ );"""
+ sql """ use oracle_ojdbc6.DORIS_TEST; """
+ qt_query_ojdbc6_all_types """ select * from
oracle_ojdbc6.DORIS_TEST.TEST_ALL_TYPES order by 1; """
+
+ sql """drop catalog if exists oracle_ojdbc6; """
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
