Changeset: c3c424a90a42 for monetdb-java URL: https://dev.monetdb.org/hg/monetdb-java/rev/c3c424a90a42 Added Files: src/main/java/org/monetdb/jdbc/MonetResultSetMetaData.java Modified Files: src/main/java/org/monetdb/jdbc/MonetResultSet.java tests/JDBC_API_Tester.java Branch: default Log Message:
Improve implementation of ResultSet.getMetaData(). The current implementation creates a new ResultSetMetaData each time this method is called which is quite costly if it is called from inside a fetch-loop such as in the example on: https://en.wikipedia.org/wiki/Java_Database_Connectivity#Examples try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable")) { while (rs.next()) { int numColumns = rs.getMetaData().getColumnCount(); for (int i = 1; i <= numColumns; i++) { // Column numbers start at 1. // Also there are many methods on the result set to return // the column as a particular type. Refer to the Sun documentation // for the list of valid conversions. System.out.println( "COLUMN " + i + " = " + rs.getObject(i)); } } } As the ResultSetMetaData is static for a ResultSet it is better to create it once, cache it in the ResultSet object and return the cached object for next calls to ResultSet.getMetaData(). diffs (truncated from 2083 to 300 lines): diff --git a/src/main/java/org/monetdb/jdbc/MonetResultSet.java b/src/main/java/org/monetdb/jdbc/MonetResultSet.java --- a/src/main/java/org/monetdb/jdbc/MonetResultSet.java +++ b/src/main/java/org/monetdb/jdbc/MonetResultSet.java @@ -82,9 +82,13 @@ public class MonetResultSet private final String[] columns; /** The MonetDB types of the columns in this ResultSet */ private final String[] types; - /** The JDBC SQL types of the columns in this ResultSet. The content will be derived from the MonetDB types[] */ + /** The JDBC SQL types of the columns in this ResultSet. + * The content will be derived once from the MonetDB String[] types */ private final int[] JdbcSQLTypes; + /** A cache to reduce the number of ResultSetMetaData objects created by getMetaData() to maximum 1 per ResultSet */ + private ResultSetMetaData rsmd; + // the following have protected access modifier for the MonetVirtualResultSet subclass // they are accessed from MonetVirtualResultSet.absolute() /** The current line of the buffer split in columns */ @@ -324,6 +328,7 @@ public class MonetResultSet if (header != null && !header.isClosed()) { header.close(); } + rsmd = null; if (statement instanceof MonetStatement) ((MonetStatement)statement).closeIfCompletion(); } @@ -1237,10 +1242,6 @@ public class MonetResultSet return getLong(findColumn(columnLabel)); } - - /* helper for the anonymous class inside getMetaData */ - private abstract class rsmdw extends MonetWrapper implements ResultSetMetaData {} - /** * Retrieves the number, types and properties of this ResultSet object's * columns. @@ -1249,777 +1250,67 @@ public class MonetResultSet */ @Override public ResultSetMetaData getMetaData() throws SQLException { - // return inner class which implements the ResultSetMetaData interface - return new rsmdw() { - private final String[] schemas = (header != null) ? header.getSchemaNames() : null; - private final String[] tables = (header != null) ? header.getTableNames() : null; - private final int[] lengths = (header != null) ? header.getColumnLengths() : null; - private final int[] precisions = (header != null) ? header.getColumnPrecisions() : null; - private final int[] scales = (header != null) ? header.getColumnScales() : null; - private final MonetConnection conn = (MonetConnection)getStatement().getConnection(); - - // For the methods: isNullable() and isAutoIncrement(), we need to query the server. - // To do this efficiently we query many columns combined in one query and cache the results. - private final int array_size = columns.length + 1; // add 1 as in JDBC columns start from 1 (array from 0). - private final boolean[] _is_queried = new boolean[array_size]; - private final boolean[] _is_fetched = new boolean[array_size]; - private final int[] _isNullable = new int[array_size]; - private final boolean[] _isAutoincrement = new boolean[array_size]; - private int nextUpperbound = array_size; - - /** - * A private utility method to check validity of column index number - * @throws SQLDataException when invalid column index number - */ - private final void checkColumnIndexValidity(final int column) throws SQLDataException { - if (column < 1 || column > columns.length) - throw MonetResultSet.newSQLInvalidColumnIndexException(column); - } - - /** - * A private method to fetch the isNullable and isAutoincrement values - * combined for a specific column. - * The fetched values are stored in the above array caches. - */ - private final void fetchColumnInfo(final int column) throws SQLException { - // for debug: System.out.println("fetchColumnInfo(" + column + ")"); - checkColumnIndexValidity(column); - if (_is_fetched[column] != true) { - // fetch column info for multiple columns combined in one go - fetchManyColumnsInfo(column); - } - - if (_is_fetched[column]) - return; - - // apparently no data could be fetched for this resultset column, fall back to defaults - _isNullable[column] = columnNullableUnknown; - _isAutoincrement[column] = false; - } - - /** - * A private method to fetch the isNullable and isAutoincrement values - * for many fully qualified columns combined in one SQL query to reduce the number of queries sent. - * As fetching this meta information from the server per column is costly we combine the querying of - * the isNullable and isAutoincrement values and cache it in internal arrays. - * We also do this for many columns combined in one query to reduce - * the number of queries needed for fetching this metadata for all resultset columns. - * Many generic JDBC database tools (e.g. SQuirreL, DBeaver) request this meta data for each - * column of each resultset, so these optimisations reduces the number of meta data queries significantly. - */ - private final void fetchManyColumnsInfo(final int column) throws SQLException { - // for debug: System.out.println("fetchManyColumnsInfo(" + column + ")"); - - // Most queries have less than 80 resultset columns - // So 80 is a good balance between speedup (up to 79x) and size of generated query sent to server - final int MAX_COLUMNS_PER_QUERY = 80; - - // Determine the optimal startcol to make use of fetching up to 80 columns in one query. - int startcol = column; - if ((startcol > 1) && (startcol + MAX_COLUMNS_PER_QUERY >= nextUpperbound)) { - // we can fetch info from more columns in one query if we start with a lower startcol - startcol = nextUpperbound - MAX_COLUMNS_PER_QUERY; - if (startcol < 1) { - startcol = 1; - } else - if (startcol > column) { - startcol = column; - } - nextUpperbound = startcol; // next time this nextUpperbound value will be used - // for debug: System.out.println("fetchManyColumnsInfo(" + column + ")" + (startcol != column ? " changed into startcol: " + startcol : "") + " nextUpperbound: " + nextUpperbound); - } - - final StringBuilder query = new StringBuilder(410 + (MAX_COLUMNS_PER_QUERY * 150)); - /* next SQL query is a simplified version of query in MonetDatabaseMetaData.getColumns(), to fetch only the needed attributes of a column */ - query.append("SELECT " + - "s.\"name\" AS schnm, " + - "t.\"name\" AS tblnm, " + - "c.\"name\" AS colnm, " + - "cast(CASE c.\"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable) - .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls) - .append(" ELSE ").append(ResultSetMetaData.columnNullableUnknown) - .append(" END AS int) AS nullable, ").append( - "cast(CASE WHEN c.\"default\" IS NOT NULL AND c.\"default\" LIKE 'next value for %' THEN true ELSE false END AS boolean) AS isautoincrement " + - "FROM \"sys\".\"columns\" c " + - "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " + - "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + - "WHERE "); - - /* combine the conditions for multiple (up to 80) columns into the WHERE-clause */ - String schName = null; - String tblName = null; - String colName = null; - int queriedcolcount = 0; - for (int col = startcol; col < array_size && queriedcolcount < MAX_COLUMNS_PER_QUERY; col++) { - if (_is_fetched[col] != true) { - if (_is_queried[col] != true) { - _isNullable[col] = columnNullableUnknown; - _isAutoincrement[col] = false; - schName = getSchemaName(col); - if (schName != null && !schName.isEmpty()) { - tblName = getTableName(col); - if (tblName != null && !tblName.isEmpty()) { - colName = getColumnName(col); - if (colName != null && !colName.isEmpty()) { - if (queriedcolcount > 0) - query.append(" OR "); - query.append("(s.\"name\" = ").append(MonetWrapper.sq(schName)); - query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(tblName)); - query.append(" AND c.\"name\" = ").append(MonetWrapper.sq(colName)); - query.append(")"); - _is_queried[col] = true; // flag it - queriedcolcount++; - } - } - } - if (_is_queried[col] != true) { - // make sure we do not try to query it again next time as it is not queryable - _is_fetched[col] = true; - } - } - } - } - - if (queriedcolcount == 0) - return; - - // execute query to get information on queriedcolcount (or less) columns. - final Statement stmt = conn.createStatement(); - if (stmt != null) { - // for debug: System.out.println("SQL (len " + query.length() + "): " + query.toString()); - final ResultSet rs = stmt.executeQuery(query.toString()); - if (rs != null) { - String rsSchema = null; - String rsTable = null; - String rsColumn = null; - while (rs.next()) { - rsSchema = rs.getString(1); // col 1 is schnm - rsTable = rs.getString(2); // col 2 is tblnm - rsColumn = rs.getString(3); // col 3 is colnm - // find the matching schema.table.column entry in the array - for (int col = 1; col < array_size; col++) { - if (_is_fetched[col] != true && _is_queried[col]) { - colName = getColumnName(col); - if (colName != null && colName.equals(rsColumn)) { - tblName = getTableName(col); - if (tblName != null && tblName.equals(rsTable)) { - schName = getSchemaName(col); - if (schName != null && schName.equals(rsSchema)) { - // found matching entry - // for debug: System.out.println("Found match at [" + col + "] for " + schName + "." + tblName + "." + colName); - _isNullable[col] = rs.getInt(4); // col 4 is nullable (or "NULLABLE") - _isAutoincrement[col] = rs.getBoolean(5); // col 5 is isautoincrement (or "IS_AUTOINCREMENT") - _is_fetched[col] = true; - queriedcolcount--; - // we found the match, exit the for-loop - col = array_size; - } - } - } - } - } - } - rs.close(); - } - stmt.close(); - } - - if (queriedcolcount != 0) { - // not all queried columns have resulted in a returned data row. - // make sure we do not match those columns again next run - for (int col = startcol; col < array_size; col++) { - if (_is_fetched[col] != true && _is_queried[col]) { - _is_fetched[col] = true; - // for debug: System.out.println("Found NO match at [" + col + "] for " + getSchemaName(col) + "." + getTableName(col) + "." + getColumnName(col)); - } - } - } - } - - /** - * Returns the number of columns in this ResultSet object. - * - * @return the number of columns - */ - @Override - public int getColumnCount() { - return columns.length; - } - - /** - * Indicates whether the designated column is automatically numbered. - * - * This method is currently very expensive for BIGINT, - * INTEGER, SMALLINT and TINYINT result column types - * as it needs to retrieve the information from the - * database using an SQL meta data query. - * - * @param column the first column is 1, the second is 2, ... - * @return true if so; false otherwise - * @throws SQLException if a database access error occurs - */ - @Override - public boolean isAutoIncrement(final int column) throws SQLException { - // only few integer types can be auto incrementable in MonetDB - // see: https://www.monetdb.org/Documentation/SQLReference/DataTypes/SerialDatatypes - switch (getColumnType(column)) { - case Types.BIGINT: - case Types.INTEGER: - case Types.SMALLINT: - case Types.TINYINT: - try { - if (_is_fetched[column] != true) { - fetchColumnInfo(column); - } - return _isAutoincrement[column]; - } catch (IndexOutOfBoundsException e) { - throw MonetResultSet.newSQLInvalidColumnIndexException(column); - } - } - - return false; - } - - /** - * Indicates whether a column's case matters. - * - * @param column the first column is 1, the second is 2, ... - * @return true for all character string columns else false - */ - @Override - public boolean isCaseSensitive(final int column) throws SQLException { - switch (getColumnType(column)) { - case Types.CHAR: - case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness - case Types.CLOB: - return true; - case Types.VARCHAR: - final String monettype = getColumnTypeName(column); - if (monettype != null && monettype.length() == 4) { - // data of type inet or uuid is not case sensitive - if ("inet".equals(monettype) - || "uuid".equals(monettype)) - return false; - } - return true; - } - - return false; - } - - /** _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
