Changeset: 11c30e3b7966 for monetdb-java
URL: https://dev.monetdb.org/hg/monetdb-java?cmd=changeset;node=11c30e3b7966
Modified Files:
ChangeLog
release.txt
src/main/java/nl/cwi/monetdb/jdbc/MonetConnection.java
src/main/java/nl/cwi/monetdb/jdbc/MonetDriver.java.in
tests/Test_CallableStmt.java
tests/Test_PSmetadata.java
tests/Test_Rmetadata.java
Branch: default
Log Message:
Set the connection properties treat_clob_as_varchar and treat_blob_as_binary to
true by default for faster processing (less objects created, less memory
needed) when querying CLOB or BLOB column data.
diffs (200 lines):
diff --git a/ChangeLog b/ChangeLog
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,15 @@
# ChangeLog file for monetdb-java
# This file is updated with Maddlog
+* Thu Oct 8 2020 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- The connection properties treat_clob_as_varchar and treat_blob_as_binary
+ are now set to true by default within the JDBC driver. This is done
+ as it results by default in less memory usage, (much) faster response
+ and better user experience for many generic JDBC applications (like
+ SQuirreL SQL, DBeaver, etc) when fetching data from CLOB or BLOB result
+ columns. See release.txt for more information and how you can turn
+ it off to get the old JDBC driver behavior if you require it.
+
* Wed Oct 7 2020 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Added support for new Java 8 java.sql.Types: Types.TIME_WITH_TIMEZONE and
Types.TIMESTAMP_WITH_TIMEZONE.
diff --git a/release.txt b/release.txt
--- a/release.txt
+++ b/release.txt
@@ -22,21 +22,37 @@ Supported connection properties are:
user=<login name>
password=<secret value>
so_timeout=<time in milliseconds> default is: 0 (no timeout)
- treat_blob_as_binary=true default is: false
- treat_clob_as_varchar=true default is: false
- language=<sql or mal> default is: sql
- debug=true default is: false
+ treat_clob_as_varchar=false default is: true
+ treat_blob_as_binary=false default is: true
+ language=<sql or mal> default is: sql
+ debug=true default is: false
logfile=<name of logfile>
hash=<SHA512, SHA384, SHA256 or SHA1>
+The treat_clob_as_varchar property (when set to true) will let the method
+ResultSetMetaData.getColumnType(int) to return Types.VARCHAR instead of
Types.CLOB
+for CLOB ResultSet columns. Generic JDBC applications such as SQuirreL SQL and
DBeaver
+will than use rs.getString() instead of rs.getClob() to fetch any CLOB column
data.
+Using rs.getString() avoids creating new objects and multiple copies of the
clob
+string data resulting in (much) faster response and better user experience.
+This property is turned on by default as of release monetdb-jdbc-2.30.jre8.jar.
+You can turn it off if you prefer the old behavior.
+
+The treat_blob_as_binary property (when set to true) will let the method
+ResultSetMetaData.getColumnType(int) to return Types.VARBINARY instead of
Types.BLOB
+for BLOB ResultSet columns. Generic JDBC applications such as SQuirreL SQL and
DBeaver
+will than use rs.getBytes() instead of rs.getBlob() to fetch any BLOB column
data.
+Using rs.getBytes() avoids creating new objects and multiple copies of the blob
+string data resulting in (much) faster response and better user experience.
+This property is turned on by default as of release monetdb-jdbc-2.30.jre8.jar.
+You can turn it off if you prefer the old behavior.
+
We recommend to set following connection properties:
so_timeout=20000
- treat_clob_as_varchar=true
- treat_blob_as_binary=true
Multiple connection properties are separated by the & character.
For example:
-
jdbc:monetdb://localhost:41000/mydb?user=monetdb&password=onlyiknow&so_timeout=20000&treat_clob_as_varchar=true&treat_blob_as_binary=true
+
jdbc:monetdb://localhost:41000/mydb?user=monetdb&password=onlyiknow&so_timeout=20000
See also: https://www.monetdb.org/Documentation/SQLreference/Programming/JDBC
@@ -168,6 +184,18 @@ Notes and Tips for Java Programmers usin
getString(int columnIndex) method, because internally all data
values (of all types) are stored as Strings, so no conversions are needed.
+- Avoid using rs.getObject() as it will need to construct a new Object for
+ each value, even for primitive types such as int, long, boolean.
+
+- Avoid using rs.getClob(). Instead use getString() for all CLOB
+ columns, which is much faster and uses much (3 times) less memory.
+
+- Avoid using rs.getBlob(). Instead use getBytes() to get a byte array
+ or use getString() to get a string containing hex pairs, for all BLOB
+ columns. These methods are much faster and use much less memory.
+ The getString() is the fastest way as no conversions are done at all.
+ The getBytes() will need to convert the hex char string into a new bytes[].
+
- Try to avoid calling "rs.get...(String columnLabel)" methods inside the
while(rs.next()) {...} loop. Instead resolve the columnLabels to column
numbers before the loop via method "int findColumn(String columnLabel)"
@@ -177,30 +205,6 @@ Notes and Tips for Java Programmers usin
See also the example Java JDBC program on:
https://www.monetdb.org/Documentation/SQLreference/Programming/JDBC
-- Avoid using rs.getObject() as it will need to construct a new Object for
- each value, even for primitive types such as int, long, boolean.
-
-- Avoid using rs.getClob(). Instead use getString() for all CLOB
- columns, which is much faster and uses much (3 times) less memory.
-Tip: For generic applications (such as SQuirreL) use connection property
- treat_clob_as_varchar=true in the JDBC connection string. This will
- let "ResultSetMetaData.getColumnType(int)" to return Types.VARCHAR instead
- of Types.CLOB for clob ResultSet columns.
- The generic application will than use rs.getString() instead of rs.getClob()
- for those clob column data, resulting in (much) faster response.
-
-- Avoid using rs.getBlob(). Instead use getBytes() to get a byte array
- or use getString() to get a string containing hex pairs, for all BLOB
- columns. These methods are much faster and use much less memory.
- The getString() is the fastest way as no conversions are done at all.
- The getBytes() will need to convert the hex char string into a new bytes[].
-Tip: For generic applications (such as SQuirreL) use connection property
- treat_blob_as_binary=true in the JDBC connection string. This will
- let "ResultSetMetaData.getColumnType(int)" to return Types.VARBINARY instead
- of Types.BLOB for blob resultset columns.
- The generic application will than use rs.getBytes() instead of rs.getBlob()
- for those blob column data, resulting in (much) faster response.
-
- By default the ResultSets created by methods in DatabaseMetaData
which return a ResultSet (such as dbmd.getColumns(...)) are
TYPE_SCROLL_INSENSITIVE, so they cache their ResultSet data to
@@ -218,7 +222,7 @@ Warning:
the same Connection (so one MapiSocket), this may lead to incorrect behavior
and results (due to race conditions).
You will need to serialize the processing of the threads in your Java program.
- Alternatively you could use a separate JDBC Connection for each thread.
+ Alternatively you can use a separate JDBC Connection for each thread.
Note: as of Febr 2020 (monetdb-jdbc-2.29.jre8.jar) we compile all
the java sources to target: Java SE 8 (profile compact2), so
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
@@ -137,9 +137,9 @@ public class MonetConnection
private final int lang;
/** Whether or not BLOB is mapped to Types.VARBINARY instead of
Types.BLOB within this connection */
- private boolean treatBlobAsVarBinary = false;
+ private boolean treatBlobAsVarBinary = true;
/** Whether or not CLOB is mapped to Types.VARCHAR instead of
Types.CLOB within this connection */
- private boolean treatClobAsVarChar = false;
+ private boolean treatClobAsVarChar = true;
/** The last set query timeout on the server as used by Statement,
PreparedStatement and CallableStatement */
protected int lastSetQueryTimeout = 0; // 0 means no timeout, which is
the default on the server
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
@@ -228,15 +228,15 @@ final public class MonetDriver implement
prop.choices = new String[] { "SHA512", "SHA384", "SHA256",
"SHA1" };
dpi[5] = prop;
- prop = new DriverPropertyInfo("treat_blob_as_binary", "false");
+ prop = new DriverPropertyInfo("treat_blob_as_binary", "true");
prop.required = false;
- prop.description = "Should blob columns be mapped to
Types.VARBINARY instead of default Types.BLOB in ResultSets and
PreparedStatements";
+ prop.description = "Should blob columns be mapped to
Types.VARBINARY instead of Types.BLOB in ResultSets and PreparedStatements"; //
recommend for increased performance due to less overhead
prop.choices = boolean_choices;
dpi[6] = prop;
- prop = new DriverPropertyInfo("treat_clob_as_varchar", "false");
+ prop = new DriverPropertyInfo("treat_clob_as_varchar", "true");
prop.required = false;
- prop.description = "Should clob columns be mapped to
Types.VARCHAR instead of default Types.CLOB in ResultSets and
PreparedStatements"; // recommend for increased performance due to less overhead
+ prop.description = "Should clob columns be mapped to
Types.VARCHAR instead of Types.CLOB in ResultSets and PreparedStatements"; //
recommend for increased performance due to less overhead
prop.choices = boolean_choices;
dpi[7] = prop;
diff --git a/tests/Test_CallableStmt.java b/tests/Test_CallableStmt.java
--- a/tests/Test_CallableStmt.java
+++ b/tests/Test_CallableStmt.java
@@ -10,7 +10,7 @@ import java.sql.*;
public class Test_CallableStmt {
public static void main(String[] args) throws Exception {
- Connection con = DriverManager.getConnection(args[0]);
+ Connection con = DriverManager.getConnection(args[0] +
"&treat_clob_as_varchar=false");
Statement stmt = null;
CallableStatement cstmt = null;
try {
diff --git a/tests/Test_PSmetadata.java b/tests/Test_PSmetadata.java
--- a/tests/Test_PSmetadata.java
+++ b/tests/Test_PSmetadata.java
@@ -11,7 +11,7 @@ import java.sql.*;
public class Test_PSmetadata {
public static void main(String[] args) throws Exception {
// Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); // not
needed anymore for self registering JDBC drivers
- Connection con = DriverManager.getConnection(args[0]);
+ Connection con = DriverManager.getConnection(args[0] +
"&treat_clob_as_varchar=false");
Statement stmt = con.createStatement();
PreparedStatement pstmt;
ResultSet rs = null;
diff --git a/tests/Test_Rmetadata.java b/tests/Test_Rmetadata.java
--- a/tests/Test_Rmetadata.java
+++ b/tests/Test_Rmetadata.java
@@ -11,7 +11,7 @@ import java.sql.*;
public class Test_Rmetadata {
public static void main(String[] args) throws Exception {
// Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); // not
needed anymore for self registering JDBC drivers
- Connection con = DriverManager.getConnection(args[0]);
+ Connection con = DriverManager.getConnection(args[0] +
"&treat_clob_as_varchar=false");
Statement stmt = con.createStatement();
ResultSet rs = null;
ResultSetMetaData rsmd = null;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
