Mercurial > hg > monetdb-java

comparison src/main/java/org/monetdb/jdbc/MonetResultSetMetaData.java @ 713:c3c424a90a42

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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().
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 04 Jan 2023 23:34:14 +0100 (2023-01-04)
parents
children ad7b08ef7745
comparison
equal deleted inserted replaced
712:7cec464246f2 713:c3c424a90a42
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2023 MonetDB B.V.
7 */
8
9 package org.monetdb.jdbc;
10
11 import java.sql.SQLException;
12 import java.sql.ResultSetMetaData;
13 import java.sql.Types;
14 import java.util.Map;
15
16 /**
17 *<pre>
18 * A {@link ResultSetMetaData} suitable for the MonetDB database.
19 *
20 * An object that can be used to get information about the types and
21 * properties of the columns in a ResultSet object.
22 *</pre>
23 *
24 * @author Martin van Dinther
25 * @version 1.0
26 */
27 public final class MonetResultSetMetaData
28 extends MonetWrapper
29 implements ResultSetMetaData
30 {
31 /** The parental Connection object */
32 private final MonetConnection conn;
33
34 /** A ResultSetResponse object to retrieve resultset metadata from */
35 private final MonetConnection.ResultSetResponse header;
36
37 /** The schema names of the columns in this ResultSet */
38 private final String[] schemas;
39 /** The table names of the columns in this ResultSet */
40 private final String[] tables;
41 /** The names of the columns in this ResultSet */
42 private final String[] columns;
43 /** The MonetDB type names of the columns in this ResultSet */
44 private final String[] types;
45 /** The JDBC SQL type codes of the columns in this ResultSet.
46 * The content will be derived once from the MonetDB String[] types */
47 private final int[] JdbcSQLTypes;
48 /** The lengths of the columns in this ResultSet */
49 private final int[] lengths;
50 /** The precisions of the columns in this ResultSet */
51 private final int[] precisions;
52 /** The scales of the columns in this ResultSet */
53 private final int[] scales;
54
55 /* For the methods: isNullable() and isAutoIncrement(), we need to query the server.
56 * To do this efficiently we query many columns combined in one SELECT
57 * query and cache the results in following arrays.
58 */
59 private final int array_size;
60 /** Whether info for a column is already queried or not */
61 private final boolean[] _is_queried;
62 /** Whether info for a column is already fetched or not */
63 private final boolean[] _is_fetched;
64 /** The nullability of the columns in this ResultSet */
65 private final int[] _isNullable;
66 /** The auto increment property of the columns in this ResultSet */
67 private final boolean[] _isAutoincrement;
68 /** an upper bound value to calculate the range of columns to query */
69 private int nextUpperbound;
70
71
72 /**
73 * Main constructor backed by the given connection and header.
74 *
75 * @param connection the parent connection
76 * @param header a ResultSetResponse containing the metadata
77 * @throws IllegalArgumentException if called with null for one of the arguments
78 */
79 MonetResultSetMetaData(
80 final MonetConnection connection,
81 final MonetConnection.ResultSetResponse header)
82 throws IllegalArgumentException
83 {
84 if (connection == null) {
85 throw new IllegalArgumentException("Connection may not be null!");
86 }
87 if (header == null) {
88 throw new IllegalArgumentException("Header may not be null!");
89 }
90 this.conn = connection;
91 this.header = header;
92 schemas = header.getSchemaNames();
93 tables = header.getTableNames();
94 columns = header.getNames();
95 lengths = header.getColumnLengths();
96 types = header.getTypes();
97 precisions = header.getColumnPrecisions();
98 scales = header.getColumnScales();
99
100 if (columns.length != tables.length || columns.length != types.length ) {
101 throw new IllegalArgumentException("Inconsistent Header metadata");
102 }
103
104 // derive the JDBC SQL type codes from the types[] names once
105 JdbcSQLTypes = new int[types.length];
106 for (int i = 0; i < types.length; i++) {
107 int javaSQLtype = MonetDriver.getJdbcSQLType(types[i]);
108 if (javaSQLtype == Types.CLOB) {
109 if (connection.mapClobAsVarChar())
110 javaSQLtype = Types.VARCHAR;
111 } else
112 if (javaSQLtype == Types.BLOB) {
113 if (connection.mapBlobAsVarBinary())
114 javaSQLtype = Types.VARBINARY;
115 }
116 JdbcSQLTypes[i] = javaSQLtype;
117 }
118
119 // initialize structures for storing columns info on nullability and autoincrement
120 array_size = columns.length + 1; // add 1 as in JDBC columns start from 1 (array from 0).
121 _is_queried = new boolean[array_size];
122 _is_fetched = new boolean[array_size];
123 _isNullable = new int[array_size];
124 _isAutoincrement = new boolean[array_size];
125 nextUpperbound = array_size;
126 }
127
128 /**
129 * Returns the number of columns in this ResultSet object.
130 *
131 * @return the number of columns
132 */
133 @Override
134 public int getColumnCount() {
135 // for debug: System.out.println("In rsmd.getColumnCount() = " + columns.length + ". this rsmd object = " + this.toString());
136 return columns.length;
137 }
138
139 /**
140 * Indicates whether the designated column is automatically numbered.
141 *
142 * This method is currently very expensive for BIGINT,
143 * INTEGER, SMALLINT and TINYINT result column types
144 * as it needs to retrieve the information from the
145 * database using an SQL meta data query.
146 *
147 * @param column the first column is 1, the second is 2, ...
148 * @return true if so; false otherwise
149 * @throws SQLException if there is no such column or a database access error occurs
150 */
151 @Override
152 public boolean isAutoIncrement(final int column) throws SQLException {
153 // only few integer types can be auto incrementable in MonetDB
154 // see: https://www.monetdb.org/Documentation/SQLReference/DataTypes/SerialDatatypes
155 switch (getColumnType(column)) {
156 case Types.BIGINT:
157 case Types.INTEGER:
158 case Types.SMALLINT:
159 case Types.TINYINT:
160 try {
161 if (_is_fetched[column] != true) {
162 fetchColumnInfo(column);
163 }
164 return _isAutoincrement[column];
165 } catch (IndexOutOfBoundsException e) {
166 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
167 }
168 }
169
170 return false;
171 }
172
173 /**
174 * Indicates whether a column's case matters.
175 *
176 * @param column the first column is 1, the second is 2, ...
177 * @return true for all character string columns else false
178 * @throws SQLException if there is no such column
179 */
180 @Override
181 public boolean isCaseSensitive(final int column) throws SQLException {
182 switch (getColumnType(column)) {
183 case Types.CHAR:
184 case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness
185 case Types.CLOB:
186 return true;
187 case Types.VARCHAR:
188 final String monettype = getColumnTypeName(column);
189 if (monettype != null && monettype.length() == 4) {
190 // data of type inet or uuid is not case sensitive
191 if ("inet".equals(monettype)
192 || "uuid".equals(monettype))
193 return false;
194 }
195 return true;
196 }
197
198 return false;
199 }
200
201 /**
202 * Indicates whether the designated column can be used in a
203 * where clause.
204 *
205 * Returning true for all here, even for CLOB, BLOB.
206 *
207 * @param column the first column is 1, the second is 2, ...
208 * @return true
209 * @throws SQLException if there is no such column
210 */
211 @Override
212 public boolean isSearchable(final int column) throws SQLException {
213 checkColumnIndexValidity(column);
214 return true;
215 }
216
217 /**
218 * Indicates whether the designated column is a cash value.
219 * From the MonetDB database perspective it is by definition
220 * unknown whether the value is a currency, because there are
221 * no currency datatypes such as MONEY. With this knowledge
222 * we can always return false here.
223 *
224 * @param column the first column is 1, the second is 2, ...
225 * @return false
226 * @throws SQLException if there is no such column
227 */
228 @Override
229 public boolean isCurrency(final int column) throws SQLException {
230 checkColumnIndexValidity(column);
231 return false;
232 }
233
234 /**
235 * Indicates the nullability of values in the designated column.
236 *
237 * This method is currently very expensive as it needs to
238 * retrieve the information from the database using an SQL
239 * meta data query.
240 *
241 * @param column the first column is 1, the second is 2, ...
242 * @return the nullability status of the given column; one of
243 * columnNoNulls, columnNullable or columnNullableUnknown
244 * @throws SQLException if there is no such column or a database access error occurs
245 */
246 @Override
247 public int isNullable(final int column) throws SQLException {
248 checkColumnIndexValidity(column);
249 try {
250 if (_is_fetched[column] != true) {
251 fetchColumnInfo(column);
252 }
253 return _isNullable[column];
254 } catch (IndexOutOfBoundsException e) {
255 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
256 }
257 }
258
259 /**
260 * Indicates whether values in the designated column are signed
261 * numbers.
262 * Within MonetDB all numeric types (except oid and ptr) are signed.
263 *
264 * @param column the first column is 1, the second is 2, ...
265 * @return true if so; false otherwise
266 * @throws SQLException if there is no such column
267 */
268 @Override
269 public boolean isSigned(final int column) throws SQLException {
270 // we can hardcode this, based on the colum type
271 switch (getColumnType(column)) {
272 case Types.TINYINT:
273 case Types.SMALLINT:
274 case Types.INTEGER:
275 case Types.REAL:
276 case Types.FLOAT:
277 case Types.DOUBLE:
278 case Types.DECIMAL:
279 case Types.NUMERIC:
280 return true;
281 case Types.BIGINT:
282 final String monettype = getColumnTypeName(column);
283 if (monettype != null && monettype.length() == 3) {
284 // data of type oid or ptr is not signed
285 if ("oid".equals(monettype)
286 || "ptr".equals(monettype))
287 return false;
288 }
289 return true;
290 // All other types should return false
291 // case Types.BOOLEAN:
292 // case Types.DATE: // can year be negative?
293 // case Types.TIME: // can time be negative?
294 // case Types.TIME_WITH_TIMEZONE:
295 // case Types.TIMESTAMP: // can year be negative?
296 // case Types.TIMESTAMP_WITH_TIMEZONE:
297 default:
298 return false;
299 }
300 }
301
302 /**
303 * Indicates the designated column's normal maximum width in
304 * characters.
305 *
306 * @param column the first column is 1, the second is 2, ...
307 * @return the normal maximum number of characters allowed as the
308 * width of the designated column
309 * @throws SQLException if there is no such column
310 */
311 @Override
312 public int getColumnDisplaySize(final int column) throws SQLException {
313 checkColumnIndexValidity(column);
314 if (lengths != null) {
315 try {
316 return lengths[column - 1];
317 } catch (IndexOutOfBoundsException e) {
318 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
319 }
320 }
321 return 1;
322 }
323
324 /**
325 * Gets the designated column's suggested title for use in
326 * printouts and displays. The suggested title is usually
327 * specified by the SQL AS clause. If a SQL AS is not specified,
328 * the value returned from getColumnLabel will be the same as
329 * the value returned by the getColumnName method.
330 *
331 * @param column the first column is 1, the second is 2, ...
332 * @return the suggested column title
333 * @throws SQLException if there is no such column
334 */
335 @Override
336 public String getColumnLabel(final int column) throws SQLException {
337 return getColumnName(column);
338 }
339
340 /**
341 * Gets the designated column's name
342 *
343 * @param column the first column is 1, the second is 2, ...
344 * @return the column name
345 * @throws SQLException if there is no such column
346 */
347 @Override
348 public String getColumnName(final int column) throws SQLException {
349 checkColumnIndexValidity(column);
350 try {
351 return columns[column - 1];
352 } catch (IndexOutOfBoundsException e) {
353 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
354 }
355 }
356
357 /**
358 * Gets the designated column's table's catalog name.
359 * MonetDB does not support the catalog naming concept as in: catalog.schema.table naming scheme
360 *
361 * @param column the first column is 1, the second is 2, ...
362 * @return null or the name of the catalog for the table in which the given
363 * column appears or "" if not applicable
364 * @throws SQLException if there is no such column
365 */
366 @Override
367 public String getCatalogName(final int column) throws SQLException {
368 checkColumnIndexValidity(column);
369 return null; // MonetDB does NOT support catalog qualifiers
370
371 }
372
373 /**
374 * Get the designated column's schema name.
375 *
376 * @param column the first column is 1, the second is 2, ...
377 * @return schema name or "" if not applicable
378 * @throws SQLException if there is no such column
379 */
380 @Override
381 public String getSchemaName(final int column) throws SQLException {
382 checkColumnIndexValidity(column);
383 if (schemas != null) {
384 try {
385 return schemas[column - 1];
386 } catch (IndexOutOfBoundsException e) {
387 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
388 }
389 }
390 return "";
391 }
392
393 /**
394 * Gets the designated column's table name.
395 *
396 * @param column the first column is 1, the second is 2, ...
397 * @return table name or "" if not applicable
398 * @throws SQLException if there is no such column
399 */
400 @Override
401 public String getTableName(final int column) throws SQLException {
402 checkColumnIndexValidity(column);
403 if (tables != null) {
404 try {
405 return tables[column - 1];
406 } catch (IndexOutOfBoundsException e) {
407 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
408 }
409 }
410 return "";
411 }
412
413 /**
414 * Retrieves the designated column's SQL type.
415 *
416 * @param column the first column is 1, the second is 2, ...
417 * @return SQL type from java.sql.Types
418 * @throws SQLException if there is no such column
419 */
420 @Override
421 public int getColumnType(final int column) throws SQLException {
422 checkColumnIndexValidity(column);
423 try {
424 return JdbcSQLTypes[column - 1];
425 } catch (IndexOutOfBoundsException e) {
426 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
427 }
428 }
429
430 /**
431 * Retrieves the designated column's database-specific type name.
432 *
433 * @param column the first column is 1, the second is 2, ...
434 * @return type name used by the database. If the column type is a
435 * user-defined type, then a fully-qualified type name is
436 * returned.
437 * @throws SQLException if there is no such column
438 */
439 @Override
440 public String getColumnTypeName(final int column) throws SQLException {
441 checkColumnIndexValidity(column);
442 try {
443 final String monettype = types[column - 1];
444 if (monettype.endsWith("_interval")) {
445 /* convert the interval type names to valid SQL data type names,
446 * such that generic applications can use them in create table statements
447 */
448 if ("day_interval".equals(monettype))
449 return "interval day";
450 if ("month_interval".equals(monettype))
451 return "interval month";
452 if ("sec_interval".equals(monettype))
453 return "interval second";
454 }
455 return monettype;
456 } catch (IndexOutOfBoundsException e) {
457 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
458 }
459 }
460
461 /**
462 * Get the designated column's specified column size.
463 * For numeric data, this is the maximum precision.
464 * For character data, this is the length in characters.
465 * For datetime datatypes, this is the length in characters
466 * of the String representation (assuming the maximum
467 * allowed precision of the fractional seconds component).
468 * For binary data, this is the length in bytes.
469 * For the ROWID datatype, this is the length in bytes.
470 * 0 is returned for data types where the column size is not applicable.
471 *
472 * @param column the first column is 1, the second is 2, ...
473 * @return precision
474 * @throws SQLException if there is no such column
475 */
476 @Override
477 public int getPrecision(final int column) throws SQLException {
478 switch (getColumnType(column)) {
479 case Types.BIGINT:
480 return 19;
481 case Types.INTEGER:
482 return 10;
483 case Types.SMALLINT:
484 return 5;
485 case Types.TINYINT:
486 return 3;
487 case Types.REAL:
488 return 7;
489 case Types.FLOAT:
490 case Types.DOUBLE:
491 return 15;
492 case Types.DECIMAL:
493 case Types.NUMERIC:
494 // these data types have a variable precision (max precision is 38)
495 if (precisions != null) {
496 try {
497 return precisions[column - 1];
498 } catch (IndexOutOfBoundsException e) {
499 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
500 }
501 }
502 return 18;
503 case Types.CHAR:
504 case Types.VARCHAR:
505 case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness
506 case Types.CLOB:
507 // these data types have a variable length
508 if (precisions != null) {
509 try {
510 int prec = precisions[column - 1];
511 if (prec <= 0) {
512 // apparently no positive precision or max length could be fetched
513 // use columnDisplaySize() value as fallback
514 prec = getColumnDisplaySize(column);
515 precisions[column - 1] = prec;
516 }
517 return prec;
518 } catch (IndexOutOfBoundsException e) {
519 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
520 }
521 }
522 // apparently no precisions array is available
523 // use columnDisplaySize() value as alternative
524 return getColumnDisplaySize(column);
525 case Types.BINARY:
526 case Types.VARBINARY:
527 case Types.BLOB:
528 // these data types have a variable length
529 if (precisions != null) {
530 try {
531 int prec = precisions[column - 1];
532 if (prec <= 0) {
533 // apparently no positive precision or max length could be fetched
534 // use columnDisplaySize() value as fallback
535 // It expect number of bytes, not number of hex chars
536 prec = (getColumnDisplaySize(column) / 2) +1;
537 precisions[column - 1] = prec;
538 }
539 return prec;
540 } catch (IndexOutOfBoundsException e) {
541 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
542 }
543 }
544 // apparently no precisions array is available
545 // use columnDisplaySize() value as alternative
546 // It expect number of bytes, not number of hex chars
547 return (getColumnDisplaySize(column) / 2) +1;
548 case Types.DATE:
549 return 10; // 2020-10-08
550 case Types.TIME:
551 return 15; // 21:51:34.399753
552 case Types.TIME_WITH_TIMEZONE:
553 return 21; // 21:51:34.399753+02:00
554 case Types.TIMESTAMP:
555 return 26; // 2020-10-08 21:51:34.399753
556 case Types.TIMESTAMP_WITH_TIMEZONE:
557 return 32; // 2020-10-08 21:51:34.399753+02:00
558 case Types.BOOLEAN:
559 return 1;
560 default:
561 // All other types should return 0
562 return 0;
563 }
564 }
565
566 /**
567 * Gets the designated column's number of digits to right of
568 * the decimal point.
569 * 0 is returned for data types where the scale is not applicable.
570 *
571 * @param column the first column is 1, the second is 2, ...
572 * @return scale
573 * @throws SQLException if there is no such column
574 */
575 @Override
576 public int getScale(final int column) throws SQLException {
577 switch (getColumnType(column)) {
578 case Types.DECIMAL:
579 case Types.NUMERIC:
580 {
581 // these data types may have a variable scale, max scale is 38
582
583 // Special handling for: day_interval and sec_interval as they are
584 // mapped to Types.NUMERIC and Types.DECIMAL types (see MonetDriver typeMap)
585 // They appear to have a fixed scale (tested against Oct2020)
586 final String monettype = getColumnTypeName(column);
587 if ("interval day".equals(monettype))
588 return 0;
589 if ("interval second".equals(monettype))
590 return 3;
591
592 if (scales != null) {
593 try {
594 return scales[column - 1];
595 } catch (IndexOutOfBoundsException e) {
596 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
597 }
598 }
599 return 0;
600 }
601 case Types.TIME:
602 case Types.TIME_WITH_TIMEZONE:
603 case Types.TIMESTAMP:
604 case Types.TIMESTAMP_WITH_TIMEZONE:
605 if (scales != null) {
606 try {
607 return scales[column - 1];
608 } catch (IndexOutOfBoundsException e) {
609 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
610 }
611 }
612 // support microseconds, so scale 6
613 return 6; // 21:51:34.399753
614
615 // All other types should return 0
616 // case Types.BIGINT:
617 // case Types.INTEGER:
618 // case Types.SMALLINT:
619 // case Types.TINYINT:
620 // case Types.REAL:
621 // case Types.FLOAT:
622 // case Types.DOUBLE:
623 // case Types.CHAR:
624 // case Types.VARCHAR:
625 // case Types.LONGVARCHAR: // MonetDB doesn't use type LONGVARCHAR, it's here for completeness
626 // case Types.CLOB:
627 // case Types.BINARY:
628 // case Types.VARBINARY:
629 // case Types.BLOB:
630 // case Types.DATE:
631 // case Types.BOOLEAN:
632 default:
633 return 0;
634 }
635 }
636
637 /**
638 * Indicates whether the designated column is definitely not
639 * writable. MonetDB does not support cursor updates, so
640 * nothing is writable.
641 *
642 * @param column the first column is 1, the second is 2, ...
643 * @return true if so; false otherwise
644 * @throws SQLException if there is no such column
645 */
646 @Override
647 public boolean isReadOnly(final int column) throws SQLException {
648 checkColumnIndexValidity(column);
649 return true;
650 }
651
652 /**
653 * Indicates whether it is possible for a write on the
654 * designated column to succeed.
655 *
656 * @param column the first column is 1, the second is 2, ...
657 * @return true if so; false otherwise
658 * @throws SQLException if there is no such column
659 */
660 @Override
661 public boolean isWritable(final int column) throws SQLException {
662 checkColumnIndexValidity(column);
663 return false;
664 }
665
666 /**
667 * Indicates whether a write on the designated column will
668 * definitely succeed.
669 *
670 * @param column the first column is 1, the second is 2, ...
671 * @return true if so; false otherwise
672 * @throws SQLException if there is no such column
673 */
674 @Override
675 public boolean isDefinitelyWritable(final int column) throws SQLException {
676 checkColumnIndexValidity(column);
677 return false;
678 }
679
680 /**
681 * Returns the fully-qualified name of the Java class whose instances
682 * are manufactured if the method ResultSet.getObject is called to
683 * retrieve a value from the column.
684 * ResultSet.getObject may return a subclass of the class returned by
685 * this method.
686 *
687 * @param column the first column is 1, the second is 2, ...
688 * @return the fully-qualified name of the class in the Java programming
689 * language that would be used by the method ResultSet.getObject
690 * to retrieve the value in the specified column. This is the
691 * class name used for custom mapping.
692 * @throws SQLException if there is no such column
693 */
694 @Override
695 public String getColumnClassName(final int column) throws SQLException {
696 checkColumnIndexValidity(column);
697 try {
698 final String MonetDBType = types[column - 1];
699 Class<?> type = null;
700 if (conn != null) {
701 final Map<String,Class<?>> map = conn.getTypeMap();
702 if (map != null && map.containsKey(MonetDBType)) {
703 type = (Class)map.get(MonetDBType);
704 }
705 }
706 if (type == null) {
707 // fallback to the standard SQL type Class mappings
708 type = MonetResultSet.getClassForType(JdbcSQLTypes[column - 1]);
709 }
710 if (type != null) {
711 return type.getCanonicalName();
712 }
713 throw new SQLException("column type mapping null: " + MonetDBType, "M0M03");
714 } catch (IndexOutOfBoundsException e) {
715 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
716 }
717 }
718
719
720 /**
721 * A private utility method to check validity of column index number
722 *
723 * @param column the first column is 1, the second is 2, ...
724 * @throws SQLDataException when invalid column index number
725 */
726 private final void checkColumnIndexValidity(final int column) throws java.sql.SQLDataException {
727 if (column < 1 || column > columns.length)
728 throw MonetResultSet.newSQLInvalidColumnIndexException(column);
729 }
730
731 /**
732 * A private method to fetch the isNullable and isAutoincrement values
733 * combined for a specific column.
734 * The fetched values are stored in the array caches.
735 *
736 * @param column the first column is 1, the second is 2, ...
737 * @throws SQLException if there is no such column
738 */
739 private final void fetchColumnInfo(final int column) throws SQLException {
740 // for debug: System.out.println("fetchColumnInfo(" + column + ")");
741 checkColumnIndexValidity(column);
742 if (_is_fetched[column] != true) {
743 // fetch column info for multiple columns combined in one go
744 fetchManyColumnsInfo(column);
745 }
746 if (_is_fetched[column])
747 return;
748
749 // apparently no data could be fetched for this resultset column, fall back to defaults
750 _isNullable[column] = columnNullableUnknown;
751 _isAutoincrement[column] = false;
752 }
753
754 /**
755 * A private method to fetch the isNullable and isAutoincrement values
756 * for many fully qualified columns combined in one SQL query to reduce the number of queries sent.
757 * As fetching this meta information from the server per column is costly we combine the querying of
758 * the isNullable and isAutoincrement values and cache it in internal arrays.
759 * We also do this for many columns combined in one query to reduce
760 * the number of queries needed for fetching this metadata for all resultset columns.
761 * Many generic JDBC database tools (e.g. SQuirreL, DBeaver) request this meta data for each
762 * column of each resultset, so these optimisations reduces the number of meta data queries significantly.
763 *
764 * @param column the first column is 1, the second is 2, ...
765 * @throws SQLException if a database access error occurs
766 */
767 private final void fetchManyColumnsInfo(final int column) throws SQLException {
768 // for debug: System.out.println("fetchManyColumnsInfo(" + column + ")");
769
770 // Most queries have less than 80 resultset columns
771 // So 80 is a good balance between speedup (up to 79x) and size of generated query sent to server
772 final int MAX_COLUMNS_PER_QUERY = 80;
773
774 // Determine the optimal startcol to make use of fetching up to 80 columns in one query.
775 int startcol = column;
776 if ((startcol > 1) && (startcol + MAX_COLUMNS_PER_QUERY >= nextUpperbound)) {
777 // we can fetch info from more columns in one query if we start with a lower startcol
778 startcol = nextUpperbound - MAX_COLUMNS_PER_QUERY;
779 if (startcol < 1) {
780 startcol = 1;
781 } else
782 if (startcol > column) {
783 startcol = column;
784 }
785 nextUpperbound = startcol; // next time this nextUpperbound value will be used
786 // for debug: System.out.println("fetchManyColumnsInfo(" + column + ")" + (startcol != column ? " changed into startcol: " + startcol : "") + " nextUpperbound: " + nextUpperbound);
787 }
788
789 final StringBuilder query = new StringBuilder(410 + (MAX_COLUMNS_PER_QUERY * 150));
790 /* next SQL query is a simplified version of query in MonetDatabaseMetaData.getColumns(), to fetch only the needed attributes of a column */
791 query.append("SELECT " +
792 "s.\"name\" AS schnm, " +
793 "t.\"name\" AS tblnm, " +
794 "c.\"name\" AS colnm, " +
795 "cast(CASE c.\"null\" WHEN true THEN ").append(ResultSetMetaData.columnNullable)
796 .append(" WHEN false THEN ").append(ResultSetMetaData.columnNoNulls)
797 .append(" ELSE ").append(ResultSetMetaData.columnNullableUnknown)
798 .append(" END AS int) AS nullable, ").append(
799 "cast(CASE WHEN c.\"default\" IS NOT NULL AND c.\"default\" LIKE 'next value for %' THEN true ELSE false END AS boolean) AS isautoincrement " +
800 "FROM \"sys\".\"columns\" c " +
801 "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " +
802 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
803 "WHERE ");
804
805 /* combine the conditions for multiple (up to 80) columns into the WHERE-clause */
806 String schName = null;
807 String tblName = null;
808 String colName = null;
809 int queriedcolcount = 0;
810 for (int col = startcol; col < array_size && queriedcolcount < MAX_COLUMNS_PER_QUERY; col++) {
811 if (_is_fetched[col] != true) {
812 if (_is_queried[col] != true) {
813 _isNullable[col] = columnNullableUnknown;
814 _isAutoincrement[col] = false;
815 schName = getSchemaName(col);
816 if (schName != null && !schName.isEmpty()) {
817 tblName = getTableName(col);
818 if (tblName != null && !tblName.isEmpty()) {
819 colName = getColumnName(col);
820 if (colName != null && !colName.isEmpty()) {
821 if (queriedcolcount > 0)
822 query.append(" OR ");
823 query.append("(s.\"name\" = ").append(MonetWrapper.sq(schName));
824 query.append(" AND t.\"name\" = ").append(MonetWrapper.sq(tblName));
825 query.append(" AND c.\"name\" = ").append(MonetWrapper.sq(colName));
826 query.append(")");
827 _is_queried[col] = true; // flag it
828 queriedcolcount++;
829 }
830 }
831 }
832 if (_is_queried[col] != true) {
833 // make sure we do not try to query it again next time as it is not queryable
834 _is_fetched[col] = true;
835 }
836 }
837 }
838 }
839
840 if (queriedcolcount == 0)
841 return;
842
843 // execute query to get information on queriedcolcount (or less) columns.
844 final java.sql.Statement stmt = conn.createStatement();
845 if (stmt != null) {
846 // for debug: System.out.println("SQL (len " + query.length() + "): " + query.toString());
847 final java.sql.ResultSet rs = stmt.executeQuery(query.toString());
848 if (rs != null) {
849 String rsSchema = null;
850 String rsTable = null;
851 String rsColumn = null;
852 while (rs.next()) {
853 rsSchema = rs.getString(1); // col 1 is schnm
854 rsTable = rs.getString(2); // col 2 is tblnm
855 rsColumn = rs.getString(3); // col 3 is colnm
856 // find the matching schema.table.column entry in the array
857 for (int col = 1; col < array_size; col++) {
858 if (_is_fetched[col] != true && _is_queried[col]) {
859 colName = getColumnName(col);
860 if (colName != null && colName.equals(rsColumn)) {
861 tblName = getTableName(col);
862 if (tblName != null && tblName.equals(rsTable)) {
863 schName = getSchemaName(col);
864 if (schName != null && schName.equals(rsSchema)) {
865 // found matching entry
866 // for debug: System.out.println("Found match at [" + col + "] for " + schName + "." + tblName + "." + colName);
867 _isNullable[col] = rs.getInt(4); // col 4 is nullable (or "NULLABLE")
868 _isAutoincrement[col] = rs.getBoolean(5); // col 5 is isautoincrement (or "IS_AUTOINCREMENT")
869 _is_fetched[col] = true;
870 queriedcolcount--;
871 // we found the match, exit the for-loop
872 col = array_size;
873 }
874 }
875 }
876 }
877 }
878 }
879 rs.close();
880 }
881 stmt.close();
882 }
883
884 if (queriedcolcount != 0) {
885 // not all queried columns have resulted in a returned data row.
886 // make sure we do not match those columns again next run
887 for (int col = startcol; col < array_size; col++) {
888 if (_is_fetched[col] != true && _is_queried[col]) {
889 _is_fetched[col] = true;
890 // for debug: System.out.println("Found NO match at [" + col + "] for " + getSchemaName(col) + "." + getTableName(col) + "." + getColumnName(col));
891 }
892 }
893 }
894 }
895 }
896