Changeset: 8700d9ef2ace for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=8700d9ef2ace Modified Files: ChangeLog src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java Branch: default Log Message:
Added possibility via a connection property to let the JDBC driver return columnType value Types.VARCHAR instead of Types.CLOB in case the result column of a ResultSet or parameter in a PreparedStatement is of data type 'clob'. With this connection property set, you can reduce the overhead when working with clob column data from generic JDBC programs. diffs (186 lines): diff --git a/ChangeLog b/ChangeLog --- a/ChangeLog +++ b/ChangeLog @@ -1,6 +1,28 @@ # ChangeLog file for monetdb-java # This file is updated with Maddlog +* Thu Sep 28 2017 Martin van Dinther <[email protected]> +- Added possibility to let the JDBC driver return columnType value + Types.VARCHAR instead of Types.CLOB in case the result column of a + ResultSet or parameter in a PreparedStatement is of data type 'clob'. + To enable this behavior provide it as JDBC connection URL property: + treat_clob_as_varchar=true + For example: jdbc:monetdb://localhost:50000/demo?treat_clob_as_varchar=true + See also: + https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/JDBC + This custom clob mapping informs generic JDBC programs to fetch clob + column values via method ResultSet.getString() instead of getClob() + and Clob.getCharacterStream() and next fetching from the stream. + As MonetDB server MAPI communication protocol do not support + streaming of parts of a CLOB value, the current implementation is to + send over the whole CLOB value as a string. Therefore there is no + performance gain when fetching those Clob values via getClob() and + getCharacterStream(). In fact it creates a lot of overhead (additional + objects and method calls) and triples the amount of needed Java Heap memory + for each Clob value! + With this connection property set, you can reduce this overhead when + working with clob column data from generic JDBC programs. + * Thu Sep 7 2017 Martin van Dinther <[email protected]> - Implemented PreparedStatement methods setNCharacterStream(int, Reader) and setNCharacterStream(int, Reader, long). diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java --- a/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java @@ -141,8 +141,10 @@ public class MonetConnection /** The language which is used */ final int lang; - /** Whether or not BLOB is mapped to BINARY within the driver */ - private final boolean blobIsBinary; + /** Whether or not BLOB is mapped to Types.VARBINARY instead of Types.BLOB within this connection */ + private boolean treatBlobAsVarBinary = false; + /** Whether or not CLOB is mapped to Types.VARCHAR instead of Types.CLOB within this connection */ + private boolean treatClobAsVarChar = false; /** * Constructor of a Connection for MonetDB. At this moment the @@ -201,12 +203,18 @@ public class MonetConnection if (hash != null) conn_props.setProperty("hash", hash); - String blobIsBinary_prop = props.getProperty("treat_blob_as_binary"); - if (blobIsBinary_prop != null) { - blobIsBinary = Boolean.parseBoolean(blobIsBinary_prop); - conn_props.setProperty("treat_blob_as_binary", Boolean.toString(blobIsBinary)); - } else { - blobIsBinary = false; + String treatBlobAsVarBinary_prop = props.getProperty("treat_blob_as_binary"); + if (treatBlobAsVarBinary_prop != null) { + treatBlobAsVarBinary = Boolean.parseBoolean(treatBlobAsVarBinary_prop); + conn_props.setProperty("treat_blob_as_binary", Boolean.toString(treatBlobAsVarBinary)); + typeMap.put("blob", Byte[].class); + } + + String treatClobAsVarChar_prop = props.getProperty("treat_clob_as_varchar"); + if (treatClobAsVarChar_prop != null) { + treatClobAsVarChar = Boolean.parseBoolean(treatClobAsVarChar_prop); + conn_props.setProperty("treat_clob_as_varchar", Boolean.toString(treatClobAsVarChar)); + typeMap.put("clob", String.class); } int sockTimeout = 0; @@ -1252,8 +1260,11 @@ public class MonetConnection } } catch (SQLException se) { String msg = se.getMessage(); - // System.out.println("Con.isValid(): " + msg); - if (msg != null && msg.equals("Current transaction is aborted (please ROLLBACK)")) { + // System.out.println(se.getSQLState() + " Con.isValid(): " + msg); + if (msg != null && msg.equalsIgnoreCase("Current transaction is aborted (please ROLLBACK)")) { + // Must use equalsIgnoreCase() here because up to Jul2017 release 'Current' was 'current' so with lowercase c. + // It changed to 'Current' after Jul2017 release. We need to support all server versions. + // SQLState = 25005 isValid = true; } /* ignore stmt errors/exceptions, we are only testing if the connection is still alive and usable */ @@ -1371,7 +1382,9 @@ public class MonetConnection name.equals("so_timeout") || name.equals("debug") || name.equals("hash") || - name.equals("treat_blob_as_binary")) { + name.equals("treat_blob_as_binary") || + name.equals("treat_clob_as_varchar")) + { conn_props.setProperty(name, value); } else { addWarning("setClientInfo: " + name + "is not a recognised property", "01M07"); @@ -1571,12 +1584,22 @@ public class MonetConnection } /** - * Returns whether the BLOB type should be mapped to BINARY type. + * Returns whether the JDBC BLOB type should be mapped to VARBINARY type. + * This allows generic JDBC programs to fetch Blob data via getBytes() + * instead of getBlob() and Blob.getBinaryStream() to reduce overhead. */ - boolean getBlobAsBinary() { - return blobIsBinary; + boolean mapBlobAsVarBinary() { + return treatBlobAsVarBinary; } + /** + * Returns whether the JDBC CLOB type should be mapped to VARCHAR type. + * This allows generic JDBC programs to fetch Clob data via getString() + * instead of getClob() and Clob.getCharacterStream() to reduce overhead. + */ + boolean mapClobAsVarChar() { + return treatClobAsVarChar; + } /** * Sends the given string to MonetDB as special transaction command. diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in b/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in --- a/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in @@ -253,6 +253,11 @@ final public class MonetDriver implement prop.description = "Whether BLOBs on the server should be treated as BINARY types, thus mapped to byte[]"; props.add(prop); + prop = new DriverPropertyInfo("treat_clob_as_varchar", "false"); + prop.required = false; + prop.description = "Whether CLOBs on the server should be treated and handled as VARCHAR types in the JDBC driver"; // recommend for increased performance due to less overhead + props.add(prop); + prop = new DriverPropertyInfo("so_timeout", "0"); prop.required = false; prop.description = "Defines the maximum time to wait in milliseconds on a blocking read socket call"; // this corresponds to the Connection.setNetworkTimeout() method introduced in JDBC 4.1 diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java b/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java --- a/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetPreparedStatement.java @@ -156,6 +156,14 @@ public class MonetPreparedStatement for (int i = 0; rs.next(); i++) { monetdbType[i] = rs.getString(type_colnr); javaType[i] = MonetDriver.getJavaType(monetdbType[i]); + if (javaType[i] == Types.CLOB) { + if (connection.mapClobAsVarChar()) + javaType[i] = Types.VARCHAR; + } else + if (javaType[i] == Types.BLOB) { + if (connection.mapBlobAsVarBinary()) + javaType[i] = Types.VARBINARY; + } digits[i] = rs.getInt(digits_colnr); scale[i] = rs.getInt(scale_colnr); if (rscolcnt == 3) diff --git a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java --- a/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/nl/cwi/monetdb/jdbc/MonetResultSet.java @@ -196,14 +196,21 @@ public class MonetResultSet * thereby improving getXyz() method performance. */ private void populateJdbcSQLtypesArray() { + MonetConnection connection = null; + try { + connection = (MonetConnection)statement.getConnection(); + } catch (SQLException se) { /* ignore it */ } + for (int i = 0; i < types.length; i++) { int javaSQLtype = MonetDriver.getJavaType(types[i]); JdbcSQLTypes[i] = javaSQLtype; + if (javaSQLtype == Types.CLOB) { + if (connection != null && connection.mapClobAsVarChar()) + JdbcSQLTypes[i] = Types.VARCHAR; + } else if (javaSQLtype == Types.BLOB) { - try { - if (((MonetConnection)statement.getConnection()).getBlobAsBinary()) - JdbcSQLTypes[i] = Types.BINARY; - } catch (SQLException se) { /* ignore it */ } + if (connection != null && connection.mapBlobAsVarBinary()) + JdbcSQLTypes[i] = Types.VARBINARY; } } } _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
