org.mariadb.jdbc.MariaDbDatabaseMetaData Maven / Gradle / Ivy
Show all versions of mariadb-java-client Show documentation
/*
*
* MariaDB Client for Java
*
* Copyright (c) 2012-2014 Monty Program Ab.
* Copyright (c) 2015-2020 MariaDB Corporation Ab.
*
* This library is free software; you can redistribute it and/or modify it under
* the terms of the GNU Lesser General Public License as published by the Free
* Software Foundation; either version 2.1 of the License, or (at your option)
* any later version.
*
* This library is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
* FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
* for more details.
*
* You should have received a copy of the GNU Lesser General Public License along
* with this library; if not, write to Monty Program Ab [email protected].
*
* This particular MariaDB Client for Java file is work
* derived from a Drizzle-JDBC. Drizzle-JDBC file which is covered by subject to
* the following copyright and notice provisions:
*
* Copyright (c) 2009-2011, Marcus Eriksson
*
* Redistribution and use in source and binary forms, with or without modification,
* are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this list
* of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice, this
* list of conditions and the following disclaimer in the documentation and/or
* other materials provided with the distribution.
*
* Neither the name of the driver nor the names of its contributors may not be
* used to endorse or promote products derived from this software without specific
* prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
* IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
* INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
* NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
* PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
* WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY
* OF SUCH DAMAGE.
*
*/
package org.mariadb.jdbc;
import java.sql.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Locale;
import org.mariadb.jdbc.internal.ColumnType;
import org.mariadb.jdbc.internal.com.read.resultset.ColumnDefinition;
import org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet;
import org.mariadb.jdbc.internal.io.input.StandardPacketInputStream;
import org.mariadb.jdbc.internal.util.Utils;
import org.mariadb.jdbc.internal.util.constant.Version;
import org.mariadb.jdbc.internal.util.dao.Identifier;
import org.mariadb.jdbc.util.Options;
public class MariaDbDatabaseMetaData implements DatabaseMetaData {
public static final String DRIVER_NAME = "MariaDB Connector/J";
private final MariaDbConnection connection;
private final UrlParser urlParser;
private boolean datePrecisionColumnExist = true;
/**
* Constructor.
*
* @param connection connection
* @param urlParser Url parser
*/
public MariaDbDatabaseMetaData(Connection connection, UrlParser urlParser) {
this.connection = (MariaDbConnection) connection;
this.urlParser = urlParser;
}
private static String columnTypeClause(Options options) {
String upperCaseWithoutSize =
" UCASE(IF( COLUMN_TYPE LIKE '%(%)%', CONCAT(SUBSTRING( COLUMN_TYPE,1, LOCATE('(',"
+ "COLUMN_TYPE) - 1 ), SUBSTRING(COLUMN_TYPE ,1+locate(')', COLUMN_TYPE))), "
+ "COLUMN_TYPE))";
if (options.tinyInt1isBit) {
upperCaseWithoutSize =
" IF(COLUMN_TYPE like 'tinyint(1)%', 'BIT', " + upperCaseWithoutSize + ")";
}
if (!options.yearIsDateType) {
return " IF(COLUMN_TYPE like 'year%', 'SMALLINT', " + upperCaseWithoutSize + ")";
}
return upperCaseWithoutSize;
}
// Extract identifier quoted string from input String.
// Return new position, or -1 on error
private static int skipWhite(char[] part, int startPos) {
for (int i = startPos; i < part.length; i++) {
if (!Character.isWhitespace(part[i])) {
return i;
}
}
return part.length;
}
private static int parseIdentifier(char[] part, int startPos, Identifier identifier)
throws ParseException {
int pos = skipWhite(part, startPos);
if (part[pos] != '`') {
throw new ParseException(new String(part), pos);
}
pos++;
StringBuilder sb = new StringBuilder();
int quotes = 0;
for (; pos < part.length; pos++) {
char ch = part[pos];
if (ch == '`') {
quotes++;
} else {
for (int j = 0; j < quotes / 2; j++) {
sb.append('`');
}
if (quotes % 2 == 1) {
if (ch == '.') {
if (identifier.schema != null) {
throw new ParseException(new String(part), pos);
}
identifier.schema = sb.toString();
return parseIdentifier(part, pos + 1, identifier);
}
identifier.name = sb.toString();
return pos;
}
quotes = 0;
sb.append(ch);
}
}
throw new ParseException(new String(part), startPos);
}
private static int parseIdentifierList(char[] part, int startPos, List list)
throws ParseException {
int pos = skipWhite(part, startPos);
if (part[pos] != '(') {
throw new ParseException(new String(part), pos);
}
pos++;
for (; ; ) {
pos = skipWhite(part, pos);
char ch = part[pos];
switch (ch) {
case ')':
return pos + 1;
case '`':
Identifier id = new Identifier();
pos = parseIdentifier(part, pos, id);
list.add(id);
break;
case ',':
pos++;
break;
default:
throw new ParseException(new String(part, startPos, part.length - startPos), startPos);
}
}
}
private static int skipKeyword(char[] part, int startPos, String keyword) throws ParseException {
int pos = skipWhite(part, startPos);
for (int i = 0; i < keyword.length(); i++, pos++) {
if (part[pos] != keyword.charAt(i)) {
throw new ParseException(new String(part), pos);
}
}
return pos;
}
private static int getImportedKeyAction(String actionKey) {
if (actionKey == null) {
return DatabaseMetaData.importedKeyRestrict;
}
switch (actionKey) {
case "NO ACTION":
return DatabaseMetaData.importedKeyNoAction;
case "CASCADE":
return DatabaseMetaData.importedKeyCascade;
case "SET NULL":
return DatabaseMetaData.importedKeySetNull;
case "SET DEFAULT":
return DatabaseMetaData.importedKeySetDefault;
case "RESTRICT":
return DatabaseMetaData.importedKeyRestrict;
default:
throw new IllegalArgumentException("Illegal key action '" + actionKey + "' specified.");
}
}
/**
* Get imported keys.
*
* @param tableDef table definition
* @param tableName table name
* @param catalog catalog
* @param connection connection
* @return resultset resultset
* @throws ParseException exception
*/
private static ResultSet getImportedKeys(
String tableDef, String tableName, String catalog, MariaDbConnection connection)
throws ParseException {
String[] columnNames = {
"PKTABLE_CAT", "PKTABLE_SCHEM", "PKTABLE_NAME",
"PKCOLUMN_NAME", "FKTABLE_CAT", "FKTABLE_SCHEM",
"FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ",
"UPDATE_RULE", "DELETE_RULE", "FK_NAME",
"PK_NAME", "DEFERRABILITY"
};
ColumnType[] columnTypes = {
ColumnType.VARCHAR, ColumnType.NULL, ColumnType.VARCHAR,
ColumnType.VARCHAR, ColumnType.VARCHAR, ColumnType.NULL,
ColumnType.VARCHAR, ColumnType.VARCHAR, ColumnType.SMALLINT,
ColumnType.SMALLINT, ColumnType.SMALLINT, ColumnType.VARCHAR,
ColumnType.VARCHAR, ColumnType.SMALLINT
};
String[] parts = tableDef.split("\n");
List data = new ArrayList<>();
for (String part : parts) {
part = part.trim();
if (!part.startsWith("CONSTRAINT") && !part.contains("FOREIGN KEY")) {
continue;
}
char[] partChar = part.toCharArray();
Identifier constraintName = new Identifier();
int pos = skipKeyword(partChar, 0, "CONSTRAINT");
pos = parseIdentifier(partChar, pos, constraintName);
pos = skipKeyword(partChar, pos, "FOREIGN KEY");
List foreignKeyCols = new ArrayList<>();
pos = parseIdentifierList(partChar, pos, foreignKeyCols);
pos = skipKeyword(partChar, pos, "REFERENCES");
Identifier pkTable = new Identifier();
pos = parseIdentifier(partChar, pos, pkTable);
List primaryKeyCols = new ArrayList<>();
parseIdentifierList(partChar, pos, primaryKeyCols);
if (primaryKeyCols.size() != foreignKeyCols.size()) {
throw new ParseException(tableDef, 0);
}
int onUpdateReferenceAction = DatabaseMetaData.importedKeyRestrict;
int onDeleteReferenceAction = DatabaseMetaData.importedKeyRestrict;
for (String referenceAction : new String[] {"RESTRICT", "CASCADE", "SET NULL", "NO ACTION"}) {
if (part.contains("ON UPDATE " + referenceAction)) {
onUpdateReferenceAction = getImportedKeyAction(referenceAction);
}
if (part.contains("ON DELETE " + referenceAction)) {
onDeleteReferenceAction = getImportedKeyAction(referenceAction);
}
}
for (int i = 0; i < primaryKeyCols.size(); i++) {
String[] row = new String[columnNames.length];
row[0] = pkTable.schema; // PKTABLE_CAT
if (row[0] == null) {
row[0] = catalog;
}
row[1] = null; // PKTABLE_SCHEM
row[2] = pkTable.name; // PKTABLE_NAME
row[3] = primaryKeyCols.get(i).name; // PKCOLUMN_NAME
row[4] = catalog; // FKTABLE_CAT
row[5] = null; // FKTABLE_SCHEM
row[6] = tableName; // FKTABLE_NAME
row[7] = foreignKeyCols.get(i).name; // FKCOLUMN_NAME
row[8] = Integer.toString(i + 1); // KEY_SEQ
row[9] = Integer.toString(onUpdateReferenceAction); // UPDATE_RULE
row[10] = Integer.toString(onDeleteReferenceAction); // DELETE_RULE
row[11] = constraintName.name; // FK_NAME
row[12] = null; // PK_NAME - unlike using information_schema, cannot know constraint name
row[13] = Integer.toString(DatabaseMetaData.importedKeyNotDeferrable); // DEFERRABILITY
data.add(row);
}
}
String[][] arr = data.toArray(new String[0][]);
/* Sort array by PKTABLE_CAT, PKTABLE_NAME, and KEY_SEQ.*/
Arrays.sort(
arr,
(row1, row2) -> {
int result = row1[0].compareTo(row2[0]); // PKTABLE_CAT
if (result == 0) {
result = row1[2].compareTo(row2[2]); // PKTABLE_NAME
if (result == 0) {
result = row1[8].length() - row2[8].length(); // KEY_SEQ
if (result == 0) {
result = row1[8].compareTo(row2[8]);
}
}
}
return result;
});
return SelectResultSet.createResultSet(columnNames, columnTypes, arr, connection.getProtocol());
}
/**
* Retrieves a description of the primary key columns that are referenced by the given table's
* foreign key columns (the primary keys imported by a table). They are ordered by PKTABLE_CAT,
* PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
*
* Each primary key column description has the following columns:
*
*
* - PKTABLE_CAT String {@code =>} primary key table catalog being imported (may be
*
null)
* - PKTABLE_SCHEM String {@code =>} primary key table schema being imported (may be
*
null)
* - PKTABLE_NAME String {@code =>} primary key table name being imported
*
- PKCOLUMN_NAME String {@code =>} primary key column name being imported
*
- FKTABLE_CAT String {@code =>} foreign key table catalog (may be
null)
* - FKTABLE_SCHEM String {@code =>} foreign key table schema (may be
null
* )
* - FKTABLE_NAME String {@code =>} foreign key table name
*
- FKCOLUMN_NAME String {@code =>} foreign key column name
*
- KEY_SEQ short {@code =>} sequence number within a foreign key( a value of 1
* represents the first column of the foreign key, a value of 2 would represent the second
* column within the foreign key).
*
- UPDATE_RULE short {@code =>} What happens to a foreign key when the primary key is
* updated:
*
* - importedNoAction - do not allow update of primary key if it has been imported
*
- importedKeyCascade - change imported key to agree with primary key update
*
- importedKeySetNull - change imported key to
NULL if its primary key
* has been updated
* - importedKeySetDefault - change imported key to default values if its primary key
* has been updated
*
- importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
* - DELETE_RULE short {@code =>} What happens to the foreign key when primary is
* deleted.
*
* - importedKeyNoAction - do not allow delete of primary key if it has been imported
*
- importedKeyCascade - delete rows that import a deleted key
*
- importedKeySetNull - change imported key to NULL if its primary key has been
* deleted
*
- importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
- importedKeySetDefault - change imported key to default if its primary key has been
* deleted
*
* - FK_NAME String {@code =>} foreign key name (may be
null)
* - PK_NAME String {@code =>} primary key name (may be
null)
* - DEFERRABILITY short {@code =>} can the evaluation of foreign key constraints be
* deferred until commit
*
* - importedKeyInitiallyDeferred - see SQL92 for definition
*
- importedKeyInitiallyImmediate - see SQL92 for definition
*
- importedKeyNotDeferrable - see SQL92 for definition
*
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in the database
* @return ResultSet - each row is a primary key column description
* @throws SQLException if a database access error occurs
* @see #getExportedKeys
*/
public ResultSet getImportedKeys(String catalog, String schema, String table)
throws SQLException {
// We avoid using information schema queries by default, because this appears to be an expensive
// query (CONJ-41).
if (table == null) {
throw new SQLException("'table' parameter in getImportedKeys cannot be null");
}
if (catalog == null || catalog.isEmpty()) {
return getImportedKeysUsingInformationSchema(catalog, table);
}
try {
return getImportedKeysUsingShowCreateTable(catalog, table);
} catch (Exception e) {
// Likely, parsing failed, try out I_S query.
return getImportedKeysUsingInformationSchema(catalog, table);
}
}
private String dataTypeClause(String fullTypeColumnName) {
Options options = urlParser.getOptions();
return " CASE data_type"
+ " WHEN 'bit' THEN "
+ Types.BIT
+ " WHEN 'tinyblob' THEN "
+ Types.VARBINARY
+ " WHEN 'mediumblob' THEN "
+ Types.LONGVARBINARY
+ " WHEN 'longblob' THEN "
+ Types.LONGVARBINARY
+ " WHEN 'blob' THEN "
+ Types.LONGVARBINARY
+ " WHEN 'tinytext' THEN "
+ Types.VARCHAR
+ " WHEN 'mediumtext' THEN "
+ Types.LONGVARCHAR
+ " WHEN 'longtext' THEN "
+ Types.LONGVARCHAR
+ " WHEN 'text' THEN "
+ Types.LONGVARCHAR
+ " WHEN 'date' THEN "
+ Types.DATE
+ " WHEN 'datetime' THEN "
+ Types.TIMESTAMP
+ " WHEN 'decimal' THEN "
+ Types.DECIMAL
+ " WHEN 'double' THEN "
+ Types.DOUBLE
+ " WHEN 'enum' THEN "
+ Types.VARCHAR
+ " WHEN 'float' THEN "
+ Types.REAL
+ " WHEN 'int' THEN IF( "
+ fullTypeColumnName
+ " like '%unsigned%', "
+ Types.INTEGER
+ ","
+ Types.INTEGER
+ ")"
+ " WHEN 'bigint' THEN "
+ Types.BIGINT
+ " WHEN 'mediumint' THEN "
+ Types.INTEGER
+ " WHEN 'null' THEN "
+ Types.NULL
+ " WHEN 'set' THEN "
+ Types.VARCHAR
+ " WHEN 'smallint' THEN IF( "
+ fullTypeColumnName
+ " like '%unsigned%', "
+ Types.SMALLINT
+ ","
+ Types.SMALLINT
+ ")"
+ " WHEN 'varchar' THEN "
+ Types.VARCHAR
+ " WHEN 'varbinary' THEN "
+ Types.VARBINARY
+ " WHEN 'char' THEN "
+ Types.CHAR
+ " WHEN 'binary' THEN "
+ Types.BINARY
+ " WHEN 'time' THEN "
+ Types.TIME
+ " WHEN 'timestamp' THEN "
+ Types.TIMESTAMP
+ " WHEN 'tinyint' THEN "
+ (options.tinyInt1isBit
? "IF("
+ fullTypeColumnName
+ " like 'tinyint(1)%',"
+ Types.BIT
+ ","
+ Types.TINYINT
+ ") "
: Types.TINYINT)
+ " WHEN 'year' THEN "
+ (options.yearIsDateType ? Types.DATE : Types.SMALLINT)
+ " ELSE "
+ Types.OTHER
+ " END ";
}
private ResultSet executeQuery(String sql) throws SQLException {
Statement stmt = connection.createStatement();
SelectResultSet rs = (SelectResultSet) stmt.executeQuery(sql);
rs.setStatement(null); // bypass Hibernate statement tracking (CONJ-49)
rs.setForceTableAlias();
return rs;
}
private String escapeQuote(String value) {
if (value == null) {
return "NULL";
}
return "'" + Utils.escapeString(value, connection.getProtocol().noBackslashEscapes()) + "'";
}
/**
* Generate part of the information schema query that restricts catalog names In the driver,
* catalogs is the equivalent to MariaDB schemas.
*
* @param firstCondition separator is where
* @param sb String builder
* @param columnName - column name in the information schema table
* @param catalog - catalog name. This driver does not (always) follow JDBC standard for following
* special values, due to ConnectorJ compatibility 1. empty string ("") - matches current
* catalog (i.e database). JDBC standard says only tables without catalog should be returned -
* such tables do not exist in MariaDB. If there is no current catalog, then empty string
* matches any catalog. 2. null - if nullCatalogMeansCurrent=true (which is the default), then
* the handling is the same as for "" . i.e return current catalog.JDBC-conforming way would
* be to match any catalog with null parameter. This can be switched with
* nullCatalogMeansCurrent=false in the connection URL.
* @return part of SQL query ,that restricts search for the catalog.
*/
private boolean catalogCond(
boolean firstCondition, StringBuilder sb, String columnName, String catalog) {
// null catalog => searching without any catalog restriction
if (catalog == null && !connection.nullCatalogMeansCurrent) return firstCondition;
// nullCatalogMeansCurrent or empty catalog => search restricting to current catalog
if ((catalog == null && connection.nullCatalogMeansCurrent) || catalog.isEmpty()) {
sb.append(firstCondition ? " WHERE " : " AND ").append(columnName).append(" = database()");
return false;
}
// search with specified catalog
sb.append(firstCondition ? " WHERE " : " AND ")
.append(columnName)
.append("=")
.append(escapeQuote(catalog));
return false;
}
// Helper to generate information schema queries with "like" or "equals" condition (typically on
// table name)
private boolean patternCond(
boolean firstCondition, StringBuilder sb, String columnName, String tableName) {
if (tableName == null || "%".equals(tableName)) {
return firstCondition;
}
sb.append(firstCondition ? " WHERE " : " AND ")
.append(columnName)
.append((tableName.indexOf('%') == -1 && tableName.indexOf('_') == -1) ? "=" : " LIKE ")
.append("'")
.append(Utils.escapeString(tableName, true))
.append("'");
return false;
}
/**
* Retrieves a description of the given table's primary key columns. They are ordered by
* COLUMN_NAME.
*
* Each primary key column description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog
*
- TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- COLUMN_NAME String {@code =>} column name
*
- KEY_SEQ short {@code =>} sequence number within primary key( a value of 1
* represents the first column of the primary key, a value of 2 would represent the second
* column within the primary key).
*
- PK_NAME String {@code =>} primary key name
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in the database
* @return ResultSet - each row is a primary key column description
* @throws SQLException if a database access error occurs
*/
public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
// MySQL 8 now use 'PRI' in place of 'pri'
StringBuilder sb =
new StringBuilder(
"SELECT A.TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, A.TABLE_NAME, A.COLUMN_NAME, B.SEQ_IN_INDEX KEY_SEQ, B.INDEX_NAME PK_NAME "
+ " FROM INFORMATION_SCHEMA.COLUMNS A, INFORMATION_SCHEMA.STATISTICS B"
+ " WHERE A.COLUMN_KEY in ('PRI','pri') AND B.INDEX_NAME='PRIMARY' ");
catalogCond(false, sb, "A.TABLE_SCHEMA", catalog);
catalogCond(false, sb, "B.TABLE_SCHEMA", catalog);
patternCond(false, sb, "A.TABLE_NAME", table);
patternCond(false, sb, "B.TABLE_NAME", table);
sb.append(
" AND A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ORDER BY A.COLUMN_NAME");
return executeQuery(sb.toString());
}
/**
* Retrieves a description of the tables available in the given catalog. Only table descriptions
* matching the catalog, schema, table name and type criteria are returned. They are ordered by
* TABLE_TYPE, TABLE_CAT, TABLE_SCHEM and TABLE_NAME
* . Each table description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- TABLE_TYPE String {@code =>} table type. Typical types are "TABLE", "VIEW",
* "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
*
- REMARKS String {@code =>} explanatory comment on the table
*
- TYPE_CAT String {@code =>} the types catalog (may be
null)
* - TYPE_SCHEM String {@code =>} the types schema (may be
null)
* - TYPE_NAME String {@code =>} type name (may be
null)
* - SELF_REFERENCING_COL_NAME String {@code =>} name of the designated "identifier"
* column of a typed table (may be
null)
* - REF_GENERATION String {@code =>} specifies how values in SELF_REFERENCING_COL_NAME
* are created. Values are "SYSTEM", "USER", "DERIVED". (may be
null)
*
*
* Note: Some databases may not return information for all tables.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param tableNamePattern a table name pattern; must match the table name as it is stored in the
* database
* @param types a list of table types, which must be from the list of table types returned from
* {@link #getTableTypes},to include; null returns all types
* @return ResultSet - each row is a table description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getTables(
String catalog, String schemaPattern, String tableNamePattern, String[] types)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT TABLE_SCHEMA TABLE_CAT, "
+ "NULL TABLE_SCHEM, "
+ "TABLE_NAME, "
+ "IF(TABLE_TYPE='BASE TABLE' or TABLE_TYPE='SYSTEM VERSIONED', 'TABLE', TABLE_TYPE) as TABLE_TYPE, "
+ "TABLE_COMMENT REMARKS, "
+ "NULL TYPE_CAT, "
+ "NULL TYPE_SCHEM, "
+ "NULL TYPE_NAME, "
+ "NULL SELF_REFERENCING_COL_NAME, "
+ "NULL REF_GENERATION "
+ "FROM INFORMATION_SCHEMA.TABLES");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "TABLE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "TABLE_NAME", tableNamePattern);
if (types != null && types.length > 0) {
sb.append(firstCondition ? " WHERE " : " AND ").append(" TABLE_TYPE IN (");
for (int i = 0; i < types.length; i++) {
if (types[i] == null) {
continue;
}
sb.append(
"TABLE".equals(types[i]) ? "'BASE TABLE','SYSTEM VERSIONED'" : escapeQuote(types[i]));
if (i == types.length - 1) {
sb.append(")");
} else {
sb.append(",");
}
}
}
sb.append(" ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
return executeQuery(sb.toString());
}
/**
* Retrieves a description of table columns available in the specified catalog.
*
*
Only column descriptions matching the catalog, schema, table and column name criteria are
* returned. They are ordered by TABLE_CAT,TABLE_SCHEM, TABLE_NAME
* , and ORDINAL_POSITION.
*
*
Each column description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- COLUMN_NAME String {@code =>} column name
*
- DATA_TYPE int {@code =>} SQL type from java.sql.Types
*
- TYPE_NAME String {@code =>} Data source dependent type name, for a UDT the type
* name is fully qualified
*
- COLUMN_SIZE int {@code =>} column size.
*
- BUFFER_LENGTH is not used.
*
- DECIMAL_DIGITS int {@code =>} the number of fractional digits. Null is returned
* for data types where DECIMAL_DIGITS is not applicable.
*
- NUM_PREC_RADIX int {@code =>} Radix (typically either 10 or 2)
*
- NULLABLE int {@code =>} is NULL allowed.
*
* - columnNoNulls - might not allow
NULL values
* - columnNullable - definitely allows
NULL values
* - columnNullableUnknown - nullability unknown
*
* - REMARKS String {@code =>} comment describing column (may be
null)
* - COLUMN_DEF String {@code =>} default value for the column, which should be
* interpreted as a string when the value is enclosed in single quotes (may be
null
* )
* - SQL_DATA_TYPE int {@code =>} unused
*
- SQL_DATETIME_SUB int {@code =>} unused
*
- CHAR_OCTET_LENGTH int {@code =>} for char types the maximum number of bytes in the
* column
*
- ORDINAL_POSITION int {@code =>} index of column in table (starting at 1)
*
- IS_NULLABLE String {@code =>} ISO rules are used to determine the nullability for
* a column.
*
* - YES --- if the column can include NULLs
*
- NO --- if the column cannot include NULLs
*
- empty string --- if the nullability for the column is unknown
*
* - SCOPE_CATALOG String {@code =>} catalog of table that is the scope of a reference
* attribute (
null if DATA_TYPE isn't REF)
* - SCOPE_SCHEMA String {@code =>} schema of table that is the scope of a reference
* attribute (
null if the DATA_TYPE isn't REF)
* - SCOPE_TABLE String {@code =>} table name that this the scope of a reference
* attribute (
null if the DATA_TYPE isn't REF)
* - SOURCE_DATA_TYPE short {@code =>} source type of a distinct type or user-generated
* Ref type, SQL type from java.sql.Types (
null if DATA_TYPE isn't DISTINCT or
* user-generated REF)
* - IS_AUTOINCREMENT String {@code =>} Indicates whether this column is auto
* incremented
*
* - YES --- if the column is auto incremented
*
- NO --- if the column is not auto incremented
*
- empty string --- if it cannot be determined whether the column is auto incremented
*
* - IS_GENERATEDCOLUMN String {@code =>} Indicates whether this is a generated column
*
* - YES --- if this a generated column
*
- NO --- if this not a generated column
*
- empty string --- if it cannot be determined whether this is a generated column
*
*
*
* The COLUMN_SIZE column specifies the column size for the given column. For numeric data,
* this is the maximum precision. For character data, this is the length in characters. For
* datetime datatypes, this is the length in characters of the String representation (assuming the
* maximum allowed precision of the fractional seconds component). For binary data, this is the
* length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data
* types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param tableNamePattern a table name pattern; must match the table name as it is stored in the
* database
* @param columnNamePattern a column name pattern; must match the column name as it is stored in
* the database
* @return ResultSet - each row is a column description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getColumns(
String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException {
Options options = urlParser.getOptions();
StringBuilder sb =
new StringBuilder(
"SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME,")
.append(dataTypeClause("COLUMN_TYPE"))
.append(" DATA_TYPE,")
.append(columnTypeClause(options))
.append(" TYPE_NAME, ")
.append(" CASE DATA_TYPE")
.append(" WHEN 'time' THEN ")
.append(
(datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 10, CAST(11 + DATETIME_PRECISION as signed integer))"
: "10"))
.append(" WHEN 'date' THEN 10")
.append(
" WHEN 'datetime' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19"))
.append(
" WHEN 'timestamp' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19")
+ (options.yearIsDateType ? "" : " WHEN 'year' THEN 5")
+ " ELSE "
+ " IF(NUMERIC_PRECISION IS NULL, LEAST(CHARACTER_MAXIMUM_LENGTH,"
+ Integer.MAX_VALUE
+ "), NUMERIC_PRECISION) "
+ " END")
.append(" COLUMN_SIZE, 65535 BUFFER_LENGTH, ")
.append(" CONVERT (CASE DATA_TYPE")
.append(
" WHEN 'year' THEN "
+ (options.yearIsDateType ? "NUMERIC_SCALE" : "0")
+ " WHEN 'tinyint' THEN "
+ (options.tinyInt1isBit ? "0" : "NUMERIC_SCALE")
+ " ELSE NUMERIC_SCALE END, UNSIGNED INTEGER) DECIMAL_DIGITS,"
+ " 10 NUM_PREC_RADIX, IF(IS_NULLABLE = 'yes',1,0) NULLABLE,COLUMN_COMMENT REMARKS,"
+ " COLUMN_DEFAULT COLUMN_DEF, 0 SQL_DATA_TYPE, 0 SQL_DATETIME_SUB, "
+ " LEAST(CHARACTER_OCTET_LENGTH,"
+ Integer.MAX_VALUE
+ ") CHAR_OCTET_LENGTH,"
+ " ORDINAL_POSITION, IS_NULLABLE, NULL SCOPE_CATALOG, NULL SCOPE_SCHEMA, NULL SCOPE_TABLE, NULL SOURCE_DATA_TYPE,"
+ " IF(EXTRA = 'auto_increment','YES','NO') IS_AUTOINCREMENT, "
+ " IF(EXTRA in ('VIRTUAL', 'PERSISTENT', 'VIRTUAL GENERATED', 'STORED GENERATED') ,'YES','NO') IS_GENERATEDCOLUMN "
+ " FROM INFORMATION_SCHEMA.COLUMNS ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "TABLE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "TABLE_NAME", tableNamePattern);
firstCondition = patternCond(firstCondition, sb, "COLUMN_NAME", columnNamePattern);
sb.append(" ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION");
try {
return executeQuery(sb.toString());
} catch (SQLException sqlException) {
if (sqlException.getMessage().contains("Unknown column 'DATETIME_PRECISION'")) {
datePrecisionColumnExist = false;
return getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
}
throw sqlException;
}
}
/**
* Retrieves a description of the foreign key columns that reference the given table's primary key
* columns (the foreign keys exported by a table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
* FKTABLE_NAME, and KEY_SEQ.
*
*
Each foreign key column description has the following columns:
*
*
* - PKTABLE_CAT String {@code =>} primary key table catalog (may be
null)
* - PKTABLE_SCHEM String {@code =>} primary key table schema (may be
null
* )
* - PKTABLE_NAME String {@code =>} primary key table name
*
- PKCOLUMN_NAME String {@code =>} primary key column name
*
- FKTABLE_CAT String {@code =>} foreign key table catalog (may be
null)
* being exported (may be null)
* - FKTABLE_SCHEM String {@code =>} foreign key table schema (may be
null
* ) being exported (may be null)
* - FKTABLE_NAME String {@code =>} foreign key table name being exported
*
- FKCOLUMN_NAME String {@code =>} foreign key column name being exported
*
- KEY_SEQ short {@code =>} sequence number within foreign key( a value of 1
* represents the first column of the foreign key, a value of 2 would represent the second
* column within the foreign key).
*
- UPDATE_RULE short {@code =>} What happens to foreign key when primary is updated:
*
* - importedNoAction - do not allow update of primary key if it has been imported
*
- importedKeyCascade - change imported key to agree with primary key update
*
- importedKeySetNull - change imported key to
NULL if its primary key
* has been updated
* - importedKeySetDefault - change imported key to default values if its primary key
* has been updated
*
- importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
* - DELETE_RULE short {@code =>} What happens to the foreign key when primary is
* deleted.
*
* - importedKeyNoAction - do not allow delete of primary key if it has been imported
*
- importedKeyCascade - delete rows that import a deleted key
*
- importedKeySetNull - change imported key to
NULL if its primary key
* has been deleted
* - importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
- importedKeySetDefault - change imported key to default if its primary key has been
* deleted
*
* - FK_NAME String {@code =>} foreign key name (may be
null)
* - PK_NAME String {@code =>} primary key name (may be
null)
* - DEFERRABILITY short {@code =>} can the evaluation of foreign key constraints be
* deferred until commit
*
* - importedKeyInitiallyDeferred - see SQL92 for definition
*
- importedKeyInitiallyImmediate - see SQL92 for definition
*
- importedKeyNotDeferrable - see SQL92 for definition
*
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in this database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in this database
* @return a ResultSet object in which each row is a foreign key column description
* @throws SQLException if a database access error occurs
* @see #getImportedKeys
*/
public ResultSet getExportedKeys(String catalog, String schema, String table)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM, KCU.REFERENCED_TABLE_NAME PKTABLE_NAME,"
+ " KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, "
+ " KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ,"
+ " CASE update_rule "
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END UPDATE_RULE,"
+ " CASE DELETE_RULE"
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END DELETE_RULE,"
+ " RC.CONSTRAINT_NAME FK_NAME,"
+ " RC.UNIQUE_CONSTRAINT_NAME PK_NAME,"
+ importedKeyNotDeferrable
+ " DEFERRABILITY"
+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ " INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC"
+ " ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA"
+ " AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "KCU.REFERENCED_TABLE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "KCU.REFERENCED_TABLE_NAME", table);
sb.append(" ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ");
return executeQuery(sb.toString());
}
/**
* GetImportedKeysUsingInformationSchema.
*
* @param catalog catalog
* @param table table
* @return resultset
* @throws SQLException exception
*/
public ResultSet getImportedKeysUsingInformationSchema(final String catalog, String table)
throws SQLException {
if (table == null) {
throw new SQLException("'table' parameter in getImportedKeys cannot be null");
}
StringBuilder sb =
new StringBuilder(
"SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM, KCU.REFERENCED_TABLE_NAME PKTABLE_NAME,"
+ " KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, "
+ " KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ,"
+ " CASE update_rule "
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END UPDATE_RULE,"
+ " CASE DELETE_RULE"
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END DELETE_RULE,"
+ " RC.CONSTRAINT_NAME FK_NAME,"
+ " RC.UNIQUE_CONSTRAINT_NAME PK_NAME,"
+ importedKeyNotDeferrable
+ " DEFERRABILITY"
+ " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ " INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC"
+ " ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA"
+ " AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "KCU.TABLE_SCHEMA", catalog);
sb.append(firstCondition ? " WHERE " : " AND ")
.append("KCU.TABLE_NAME = ")
.append(escapeQuote(table));
sb.append(" ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ");
return executeQuery(sb.toString());
}
/**
* GetImportedKeysUsingShowCreateTable.
*
* @param catalog catalog
* @param table table
* @return resultset
* @throws Exception exception
*/
public ResultSet getImportedKeysUsingShowCreateTable(final String catalog, String table)
throws Exception {
if (catalog == null || catalog.isEmpty()) {
throw new IllegalArgumentException("catalog");
}
if (table == null || table.isEmpty()) {
throw new IllegalArgumentException("table");
}
ResultSet rs =
connection
.createStatement()
.executeQuery(
"SHOW CREATE TABLE "
+ MariaDbConnection.quoteIdentifier(catalog)
+ "."
+ MariaDbConnection.quoteIdentifier(table));
if (rs.next()) {
String tableDef = rs.getString(2);
return MariaDbDatabaseMetaData.getImportedKeys(tableDef, table, catalog, connection);
}
throw new SQLException("Fail to retrieve table information using SHOW CREATE TABLE");
}
/**
* Retrieves a description of a table's optimal set of columns that uniquely identifies a row.
* They are ordered by SCOPE.
*
* Each column description has the following columns:
*
*
* - SCOPE short {@code =>} actual scope of result
*
* - bestRowTemporary - very temporary, while using row
*
- bestRowTransaction - valid for remainder of current transaction
*
- bestRowSession - valid for remainder of current session
*
* - COLUMN_NAME String {@code =>} column name
*
- DATA_TYPE int {@code =>} SQL data type from java.sql.Types
*
- TYPE_NAME String {@code =>} Data source dependent type name, for a UDT the type
* name is fully qualified
*
- COLUMN_SIZE int {@code =>} precision
*
- BUFFER_LENGTH int {@code =>} not used
*
- DECIMAL_DIGITS short {@code =>} scale - Null is returned for data types where
* DECIMAL_DIGITS is not applicable.
*
- PSEUDO_COLUMN short {@code =>} is this a pseudo column like an Oracle ROWID
*
* - bestRowUnknown - may or may not be pseudo column
*
- bestRowNotPseudo - is NOT a pseudo column
*
- bestRowPseudo - is a pseudo column
*
*
*
* The COLUMN_SIZE column represents the specified column size for the given column. For
* numeric data, this is the maximum precision. For character data, this is the length in
* characters. For datetime datatypes, this is the length in characters of the String
* representation (assuming the maximum allowed precision of the fractional seconds component).
* For binary data, this is the length in bytes. For the ROWID datatype, this is the length in
* bytes. Null is returned for data types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in the database
* @param scope the scope of interest; use same values as SCOPE
* @param nullable include columns that are nullable.
* @return ResultSet - each row is a column description
* @throws SQLException if a database access error occurs
*/
public ResultSet getBestRowIdentifier(
String catalog, String schema, String table, int scope, final boolean nullable)
throws SQLException {
if (table == null) {
throw new SQLException("'table' parameter cannot be null in getBestRowIdentifier()");
}
boolean hasIsGeneratedCol =
(connection.isServerMariaDb() && connection.versionGreaterOrEqual(10, 2, 0));
StringBuilder sbInner =
new StringBuilder("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'PRI'");
catalogCond(false, sbInner, "TABLE_SCHEMA", catalog);
sbInner.append(" AND TABLE_NAME = ").append(escapeQuote(table));
StringBuilder sb =
new StringBuilder(
"SELECT "
+ bestRowSession
+ " SCOPE, COLUMN_NAME,"
+ dataTypeClause("COLUMN_TYPE")
+ " DATA_TYPE, DATA_TYPE TYPE_NAME,"
+ " IF(NUMERIC_PRECISION IS NULL, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) COLUMN_SIZE, 0 BUFFER_LENGTH,"
+ " NUMERIC_SCALE DECIMAL_DIGITS,"
+ (hasIsGeneratedCol
? ("IF(IS_GENERATED='NEVER'," + bestRowNotPseudo + "," + bestRowPseudo + ")")
: bestRowNotPseudo)
+ " PSEUDO_COLUMN"
+ " FROM INFORMATION_SCHEMA.COLUMNS"
+ " WHERE (COLUMN_KEY = 'PRI'"
+ " OR (COLUMN_KEY = 'UNI' AND NOT EXISTS ("
+ sbInner
+ " )))");
catalogCond(false, sb, "TABLE_SCHEMA", catalog);
sb.append(" AND TABLE_NAME = ").append(escapeQuote(table));
if (!nullable) sb.append(" AND IS_NULLABLE = 'NO'");
return executeQuery(sb.toString());
}
public boolean generatedKeyAlwaysReturned() {
return true;
}
/**
* Retrieves a description of the pseudo or hidden columns available in a given table within the
* specified catalog and schema. Pseudo or hidden columns may not always be stored within a table
* and are not visible in a ResultSet unless they are specified in the query's outermost SELECT
* list. Pseudo or hidden columns may not necessarily be able to be modified. If there are no
* pseudo or hidden columns, an empty ResultSet is returned.
*
*
Only column descriptions matching the catalog, schema, table and column name criteria are
* returned. They are ordered by TABLE_CAT,TABLE_SCHEM, TABLE_NAME
* and COLUMN_NAME.
*
*
Each column description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- COLUMN_NAME String {@code =>} column name
*
- DATA_TYPE int {@code =>} SQL type from java.sql.Types
*
- COLUMN_SIZE int {@code =>} column size.
*
- DECIMAL_DIGITS int {@code =>} the number of fractional digits. Null is returned
* for data types where DECIMAL_DIGITS is not applicable.
*
- NUM_PREC_RADIX int {@code =>} Radix (typically either 10 or 2)
*
- COLUMN_USAGE String {@code =>} The allowed usage for the column. The value
* returned will correspond to the enum name returned by PseudoColumnUsage.name()
*
- REMARKS String {@code =>} comment describing column (may be
null)
* - CHAR_OCTET_LENGTH int {@code =>} for char types the maximum number of bytes in the
* column
*
- IS_NULLABLE String {@code =>} ISO rules are used to determine the nullability for
* a column.
*
* - YES --- if the column can include NULLs
*
- NO --- if the column cannot include NULLs
*
- empty string --- if the nullability for the column is unknown
*
*
*
* The COLUMN_SIZE column specifies the column size for the given column. For numeric data,
* this is the maximum precision. For character data, this is the length in characters. For
* datetime datatypes, this is the length in characters of the String representation (assuming the
* maximum allowed precision of the fractional seconds component). For binary data, this is the
* length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data
* types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param tableNamePattern a table name pattern; must match the table name as it is stored in the
* database
* @param columnNamePattern a column name pattern; must match the column name as it is stored in
* the database
* @return ResultSet - each row is a column description
* @throws SQLException if a database access error occurs
* @see PseudoColumnUsage
* @since 1.7
*/
public ResultSet getPseudoColumns(
String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException {
return connection
.createStatement()
.executeQuery(
"SELECT ' ' TABLE_CAT, ' ' TABLE_SCHEM,"
+ "' ' TABLE_NAME, ' ' COLUMN_NAME, 0 DATA_TYPE, 0 COLUMN_SIZE, 0 DECIMAL_DIGITS,"
+ "10 NUM_PREC_RADIX, ' ' COLUMN_USAGE, ' ' REMARKS, 0 CHAR_OCTET_LENGTH, 'YES' IS_NULLABLE FROM DUAL "
+ "WHERE 1=0");
}
public boolean allProceduresAreCallable() {
return true;
}
public boolean allTablesAreSelectable() {
return true;
}
@Override
public String getURL() {
return urlParser.getInitialUrl();
}
public String getUserName() {
return urlParser.getUsername();
}
public boolean isReadOnly() {
return false;
}
public boolean nullsAreSortedHigh() {
return false;
}
public boolean nullsAreSortedLow() {
return !nullsAreSortedHigh();
}
public boolean nullsAreSortedAtStart() {
return false;
}
public boolean nullsAreSortedAtEnd() {
return !nullsAreSortedAtStart();
}
/**
* Return Server type. MySQL or MariaDB. MySQL can be forced for compatibility with option
* "useMysqlMetadata"
*
* @return server type
* @throws SQLException in case of socket error.
*/
public String getDatabaseProductName() throws SQLException {
if (urlParser.getOptions().useMysqlMetadata) {
return "MySQL";
}
if (connection.getProtocol().isServerMariaDb()
&& connection
.getProtocol()
.getServerVersion()
.toLowerCase(Locale.ROOT)
.contains("mariadb")) {
return "MariaDB";
}
return "MySQL";
}
public String getDatabaseProductVersion() {
return connection.getProtocol().getServerVersion();
}
public String getDriverName() {
return DRIVER_NAME;
}
public String getDriverVersion() {
return Version.version;
}
public int getDriverMajorVersion() {
return Version.majorVersion;
}
public int getDriverMinorVersion() {
return Version.minorVersion;
}
public boolean usesLocalFiles() {
return false;
}
public boolean usesLocalFilePerTable() {
return false;
}
public boolean supportsMixedCaseIdentifiers() throws SQLException {
return (connection.getLowercaseTableNames() == 0);
}
public boolean storesUpperCaseIdentifiers() {
return false;
}
public boolean storesLowerCaseIdentifiers() throws SQLException {
return (connection.getLowercaseTableNames() == 1);
}
public boolean storesMixedCaseIdentifiers() throws SQLException {
return (connection.getLowercaseTableNames() == 2);
}
public boolean supportsMixedCaseQuotedIdentifiers() throws SQLException {
return supportsMixedCaseIdentifiers();
}
public boolean storesUpperCaseQuotedIdentifiers() {
return storesUpperCaseIdentifiers();
}
public boolean storesLowerCaseQuotedIdentifiers() throws SQLException {
return storesLowerCaseIdentifiers();
}
public boolean storesMixedCaseQuotedIdentifiers() throws SQLException {
return storesMixedCaseIdentifiers();
}
public String getIdentifierQuoteString() {
return "`";
}
/**
* Retrieves a comma-separated list of all of this database's SQL keywords that are NOT also
* SQL:2003 keywords.
*
* @return the list of this database's keywords that are not also SQL:2003 keywords
*/
@Override
public String getSQLKeywords() {
return "ACCESSIBLE,ANALYZE,ASENSITIVE,BEFORE,BIGINT,BINARY,BLOB,CALL,CHANGE,CONDITION,DATABASE,DATABASES,"
+ "DAY_HOUR,DAY_MICROSECOND,DAY_MINUTE,DAY_SECOND,DELAYED,DETERMINISTIC,DISTINCTROW,DIV,DUAL,EACH,"
+ "ELSEIF,ENCLOSED,ESCAPED,EXIT,EXPLAIN,FLOAT4,FLOAT8,FORCE,FULLTEXT,GENERAL,HIGH_PRIORITY,"
+ "HOUR_MICROSECOND,HOUR_MINUTE,HOUR_SECOND,IF,IGNORE,IGNORE_SERVER_IDS,INDEX,INFILE,INOUT,INT1,INT2,"
+ "INT3,INT4,INT8,ITERATE,KEY,KEYS,KILL,LEAVE,LIMIT,LINEAR,LINES,LOAD,LOCALTIME,LOCALTIMESTAMP,LOCK,"
+ "LONG,LONGBLOB,LONGTEXT,LOOP,LOW_PRIORITY,MASTER_HEARTBEAT_PERIOD,MASTER_SSL_VERIFY_SERVER_CERT,"
+ "MAXVALUE,MEDIUMBLOB,MEDIUMINT,MEDIUMTEXT,MIDDLEINT,MINUTE_MICROSECOND,MINUTE_SECOND,MOD,MODIFIES,"
+ "NO_WRITE_TO_BINLOG,OPTIMIZE,OPTIONALLY,OUT,OUTFILE,PURGE,RANGE,READ_WRITE,READS,REGEXP,RELEASE,"
+ "RENAME,REPEAT,REPLACE,REQUIRE,RESIGNAL,RESTRICT,RETURN,RLIKE,SCHEMAS,SECOND_MICROSECOND,SENSITIVE,"
+ "SEPARATOR,SHOW,SIGNAL,SLOW,SPATIAL,SPECIFIC,SQL_BIG_RESULT,SQL_CALC_FOUND_ROWS,SQL_SMALL_RESULT,"
+ "SQLEXCEPTION,SSL,STARTING,STRAIGHT_JOIN,TERMINATED,TINYBLOB,TINYINT,TINYTEXT,TRIGGER,UNDO,UNLOCK,"
+ "UNSIGNED,USE,UTC_DATE,UTC_TIME,UTC_TIMESTAMP,VARBINARY,VARCHARACTER,WHILE,XOR,YEAR_MONTH,ZEROFILL";
}
/**
* List of numeric functions.
*
* @return List of numeric functions.
*/
@Override
public String getNumericFunctions() {
return "DIV,ABS,ACOS,ASIN,ATAN,ATAN2,CEIL,CEILING,CONV,COS,COT,CRC32,DEGREES,EXP,FLOOR,GREATEST,LEAST,LN,LOG,"
+ "LOG10,LOG2,MOD,OCT,PI,POW,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQRT,TAN,TRUNCATE";
}
/**
* List of string functions.
*
* @return List of string functions.
*/
@Override
public String getStringFunctions() {
return "ASCII,BIN,BIT_LENGTH,CAST,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONVERT,ELT,EXPORT_SET,"
+ "EXTRACTVALUE,FIELD,FIND_IN_SET,FORMAT,FROM_BASE64,HEX,INSTR,LCASE,LEFT,LENGTH,LIKE,LOAD_FILE,LOCATE,"
+ "LOWER,LPAD,LTRIM,MAKE_SET,MATCH AGAINST,MID,NOT LIKE,NOT REGEXP,OCTET_LENGTH,ORD,POSITION,QUOTE,"
+ "REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SOUNDS LIKE,SPACE,STRCMP,SUBSTR,SUBSTRING,"
+ "SUBSTRING_INDEX,TO_BASE64,TRIM,UCASE,UNHEX,UPDATEXML,UPPER,WEIGHT_STRING";
}
/**
* List of system functions.
*
* @return List of system functions.
*/
@Override
public String getSystemFunctions() {
return "DATABASE,USER,SYSTEM_USER,SESSION_USER,LAST_INSERT_ID,VERSION";
}
/**
* List of time and date functions.
*
* @return List of time and date functions.
*/
@Override
public String getTimeDateFunctions() {
return "ADDDATE,ADDTIME,CONVERT_TZ,CURDATE,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURTIME,DATEDIFF,"
+ "DATE_ADD,DATE_FORMAT,DATE_SUB,DAY,DAYNAME,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,EXTRACT,FROM_DAYS,"
+ "FROM_UNIXTIME,GET_FORMAT,HOUR,LAST_DAY,LOCALTIME,LOCALTIMESTAMP,MAKEDATE,MAKETIME,MICROSECOND,"
+ "MINUTE,MONTH,MONTHNAME,NOW,PERIOD_ADD,PERIOD_DIFF,QUARTER,SECOND,SEC_TO_TIME,STR_TO_DATE,SUBDATE,"
+ "SUBTIME,SYSDATE,TIMEDIFF,TIMESTAMPADD,TIMESTAMPDIFF,TIME_FORMAT,TIME_TO_SEC,TO_DAYS,TO_SECONDS,"
+ "UNIX_TIMESTAMP,UTC_DATE,UTC_TIME,UTC_TIMESTAMP,WEEK,WEEKDAY,WEEKOFYEAR,YEAR,YEARWEEK";
}
public String getSearchStringEscape() {
return "\\";
}
public String getExtraNameCharacters() {
return "#@";
}
public boolean supportsAlterTableWithAddColumn() {
return true;
}
public boolean supportsAlterTableWithDropColumn() {
return true;
}
public boolean supportsColumnAliasing() {
return true;
}
public boolean nullPlusNonNullIsNull() {
return true;
}
public boolean supportsConvert() {
return true;
}
/**
* Retrieves whether this database supports the JDBC scalar function CONVERT for conversions
* between the JDBC types fromType and toType. The JDBC types are the generic SQL data types
* defined in java.sql.Types.
*
* @param fromType the type to convert from; one of the type codes from the class java.sql.Types
* @param toType the type to convert to; one of the type codes from the class java.sql.Types
* @return true if so; false otherwise
*/
public boolean supportsConvert(int fromType, int toType) {
switch (fromType) {
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.REAL:
case Types.FLOAT:
case Types.DECIMAL:
case Types.NUMERIC:
case Types.DOUBLE:
case Types.BIT:
case Types.BOOLEAN:
switch (toType) {
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.REAL:
case Types.FLOAT:
case Types.DECIMAL:
case Types.NUMERIC:
case Types.DOUBLE:
case Types.BIT:
case Types.BOOLEAN:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
return true;
default:
return false;
}
case Types.BLOB:
switch (toType) {
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.REAL:
case Types.FLOAT:
case Types.DECIMAL:
case Types.NUMERIC:
case Types.DOUBLE:
case Types.BIT:
case Types.BOOLEAN:
return true;
default:
return false;
}
case Types.CHAR:
case Types.CLOB:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
switch (toType) {
case Types.BIT:
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
case Types.BIGINT:
case Types.FLOAT:
case Types.REAL:
case Types.DOUBLE:
case Types.NUMERIC:
case Types.DECIMAL:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
case Types.BLOB:
case Types.CLOB:
case Types.BOOLEAN:
case Types.NCHAR:
case Types.LONGNVARCHAR:
case Types.NCLOB:
return true;
default:
return false;
}
case Types.DATE:
switch (toType) {
case Types.DATE:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
return true;
default:
return false;
}
case Types.TIME:
switch (toType) {
case Types.TIME:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
return true;
default:
return false;
}
case Types.TIMESTAMP:
switch (toType) {
case Types.TIMESTAMP:
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
case Types.TIME:
case Types.DATE:
return true;
default:
return false;
}
default:
return false;
}
}
public boolean supportsTableCorrelationNames() {
return true;
}
public boolean supportsDifferentTableCorrelationNames() {
return true;
}
public boolean supportsExpressionsInOrderBy() {
return true;
}
public boolean supportsOrderByUnrelated() {
return true;
}
public boolean supportsGroupBy() {
return true;
}
public boolean supportsGroupByUnrelated() {
return true;
}
public boolean supportsGroupByBeyondSelect() {
return true;
}
public boolean supportsLikeEscapeClause() {
return true;
}
public boolean supportsMultipleResultSets() {
return true;
}
public boolean supportsMultipleTransactions() {
return true;
}
public boolean supportsNonNullableColumns() {
return true;
}
@Override
public boolean supportsMinimumSQLGrammar() {
return true;
}
@Override
public boolean supportsCoreSQLGrammar() {
return true;
}
@Override
public boolean supportsExtendedSQLGrammar() {
return false;
}
@Override
public boolean supportsANSI92EntryLevelSQL() {
return true;
}
@Override
public boolean supportsANSI92IntermediateSQL() {
return true;
}
@Override
public boolean supportsANSI92FullSQL() {
return true;
}
public boolean supportsIntegrityEnhancementFacility() {
return true;
}
public boolean supportsOuterJoins() {
return true;
}
public boolean supportsFullOuterJoins() {
return true;
}
public boolean supportsLimitedOuterJoins() {
return true;
}
public String getSchemaTerm() {
return "schema";
}
public String getProcedureTerm() {
return "procedure";
}
public String getCatalogTerm() {
return "database";
}
public boolean isCatalogAtStart() {
return true;
}
public String getCatalogSeparator() {
return ".";
}
public boolean supportsSchemasInDataManipulation() {
return false;
}
public boolean supportsSchemasInProcedureCalls() {
return false;
}
public boolean supportsSchemasInTableDefinitions() {
return false;
}
public boolean supportsSchemasInIndexDefinitions() {
return false;
}
public boolean supportsSchemasInPrivilegeDefinitions() {
return true;
}
public boolean supportsCatalogsInDataManipulation() {
return true;
}
public boolean supportsCatalogsInProcedureCalls() {
return true;
}
public boolean supportsCatalogsInTableDefinitions() {
return true;
}
public boolean supportsCatalogsInIndexDefinitions() {
return true;
}
public boolean supportsCatalogsInPrivilegeDefinitions() {
return true;
}
public boolean supportsPositionedDelete() {
return false;
}
public boolean supportsPositionedUpdate() {
return false;
}
public boolean supportsSelectForUpdate() {
return true;
}
public boolean supportsStoredProcedures() {
return true;
}
public boolean supportsSubqueriesInComparisons() {
return true;
}
public boolean supportsSubqueriesInExists() {
return true;
}
public boolean supportsSubqueriesInIns() {
return true;
}
public boolean supportsSubqueriesInQuantifieds() {
return true;
}
public boolean supportsCorrelatedSubqueries() {
return true;
}
public boolean supportsUnion() {
return true;
}
public boolean supportsUnionAll() {
return true;
}
public boolean supportsOpenCursorsAcrossCommit() {
return true;
}
public boolean supportsOpenCursorsAcrossRollback() {
return true;
}
public boolean supportsOpenStatementsAcrossCommit() {
return true;
}
public boolean supportsOpenStatementsAcrossRollback() {
return true;
}
public int getMaxBinaryLiteralLength() {
return 16777208;
}
public int getMaxCharLiteralLength() {
return 16777208;
}
public int getMaxColumnNameLength() {
return 64;
}
public int getMaxColumnsInGroupBy() {
return 64;
}
public int getMaxColumnsInIndex() {
return 16;
}
public int getMaxColumnsInOrderBy() {
return 64;
}
public int getMaxColumnsInSelect() {
return 256;
}
public int getMaxColumnsInTable() {
return 0;
}
public int getMaxConnections() {
return 0;
}
public int getMaxCursorNameLength() {
return 0;
}
public int getMaxIndexLength() {
return 256;
}
public int getMaxSchemaNameLength() {
return 32;
}
public int getMaxProcedureNameLength() {
return 64;
}
public int getMaxCatalogNameLength() {
return 0;
}
public int getMaxRowSize() {
return 0;
}
public boolean doesMaxRowSizeIncludeBlobs() {
return false;
}
public int getMaxStatementLength() {
return 0;
}
public int getMaxStatements() {
return 0;
}
public int getMaxTableNameLength() {
return 64;
}
public int getMaxTablesInSelect() {
return 256;
}
public int getMaxUserNameLength() {
return 16;
}
public int getDefaultTransactionIsolation() {
return Connection.TRANSACTION_REPEATABLE_READ;
}
/**
* Retrieves whether this database supports transactions. If not, invoking the method commit
* is a noop, and the isolation level is TRANSACTION_NONE.
*
* @return true if transactions are supported; false otherwise
*/
public boolean supportsTransactions() {
return true;
}
/* Helper to generate information schema with "equality" condition (typically on catalog name)
*/
/**
* Retrieves whether this database supports the given transaction isolation level.
*
* @param level one of the transaction isolation levels defined in java.sql.Connection
*
* @return true if so; false otherwise
* @see Connection
*/
public boolean supportsTransactionIsolationLevel(int level) {
switch (level) {
case Connection.TRANSACTION_READ_UNCOMMITTED:
case Connection.TRANSACTION_READ_COMMITTED:
case Connection.TRANSACTION_REPEATABLE_READ:
case Connection.TRANSACTION_SERIALIZABLE:
return true;
default:
return false;
}
}
public boolean supportsDataDefinitionAndDataManipulationTransactions() {
return true;
}
public boolean supportsDataManipulationTransactionsOnly() {
return false;
}
public boolean dataDefinitionCausesTransactionCommit() {
return true;
}
public boolean dataDefinitionIgnoredInTransactions() {
return false;
}
/**
* Retrieves a description of the stored procedures available in the given catalog. Only procedure
* descriptions matching the schema and procedure name criteria are returned. They are ordered by
* PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME and
* SPECIFIC_ NAME.
*
*
Each procedure description has the the following columns:
*
*
* - PROCEDURE_CAT String {@code =>} procedure catalog (may be
null)
* - PROCEDURE_SCHEM String {@code =>} procedure schema (may be
null)
* - PROCEDURE_NAME String {@code =>} procedure name
*
- reserved for future use
*
- reserved for future use
*
- reserved for future use
*
- REMARKS String {@code =>} explanatory comment on the procedure
*
- PROCEDURE_TYPE short {@code =>} kind of procedure:
*
* - procedureResultUnknown - Cannot determine if a return value will be returned
*
- procedureNoResult - Does not return a return value
*
- procedureReturnsResult - Returns a return value
*
* - SPECIFIC_NAME String {@code =>} The name which uniquely identifies this procedure
* within its schema.
*
*
* A user may not have permissions to execute any of the procedures that are returned by
* getProcedures
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param procedureNamePattern a procedure name pattern; must match the procedure name as it is
* stored in the database
* @return ResultSet - each row is a procedure description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT ROUTINE_SCHEMA PROCEDURE_CAT,"
+ "NULL PROCEDURE_SCHEM, "
+ "ROUTINE_NAME PROCEDURE_NAME,"
+ " NULL RESERVED1,"
+ " NULL RESERVED2,"
+ " NULL RESERVED3,"
+ " ROUTINE_COMMENT REMARKS,"
+ " CASE ROUTINE_TYPE "
+ " WHEN 'FUNCTION' THEN "
+ procedureReturnsResult
+ " WHEN 'PROCEDURE' THEN "
+ procedureNoResult
+ " ELSE "
+ procedureResultUnknown
+ " END PROCEDURE_TYPE,"
+ " SPECIFIC_NAME "
+ " FROM INFORMATION_SCHEMA.ROUTINES ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "ROUTINE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "ROUTINE_NAME", procedureNamePattern);
return executeQuery(sb.toString());
}
/* Is INFORMATION_SCHEMA.PARAMETERS available ?*/
private boolean haveInformationSchemaParameters() {
return connection.getProtocol().versionGreaterOrEqual(5, 5, 3);
}
/**
* Retrieves a description of the given catalog's stored procedure parameter and result columns.
*
*
Only descriptions matching the schema, procedure and parameter name criteria are returned.
* They are ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME and SPECIFIC_NAME. Within
* this, the return value, if any, is first. Next are the parameter descriptions in call order.
* The column descriptions follow in column number order.
*
*
Each row in the ResultSet is a parameter description or column description with
* the following fields:
*
*
* - PROCEDURE_CAT String {@code =>} procedure catalog (may be
null)
* - PROCEDURE_SCHEM String {@code =>} procedure schema (may be
null)
* - PROCEDURE_NAME String {@code =>} procedure name
*
- COLUMN_NAME String {@code =>} column/parameter name
*
- COLUMN_TYPE Short {@code =>} kind of column/parameter:
*
* - procedureColumnUnknown - nobody knows
*
- procedureColumnIn - IN parameter
*
- procedureColumnInOut - INOUT parameter
*
- procedureColumnOut - OUT parameter
*
- procedureColumnReturn - procedure return value
*
- procedureColumnResult - result column in
ResultSet
*
* - DATA_TYPE int {@code =>} SQL type from java.sql.Types
*
- TYPE_NAME String {@code =>} SQL type name, for a UDT type the type name is fully
* qualified
*
- PRECISION int {@code =>} precision
*
- LENGTH int {@code =>} length in bytes of data
*
- SCALE short {@code =>} scale - null is returned for data types where SCALE is not
* applicable.
*
- RADIX short {@code =>} radix
*
- NULLABLE short {@code =>} can it contain NULL.
*
* - procedureNoNulls - does not allow NULL values
*
- procedureNullable - allows NULL values
*
- procedureNullableUnknown - nullability unknown
*
* - REMARKS String {@code =>} comment describing parameter/column
*
- COLUMN_DEF String {@code =>} default value for the column, which should be
* interpreted as a string when the value is enclosed in single quotes (may be
null
* )
*
* - The string NULL (not enclosed in quotes) - if NULL was specified as the default
* value
*
- TRUNCATE (not enclosed in quotes) - if the specified default value cannot be
* represented without truncation
*
- NULL - if a default value was not specified
*
* - SQL_DATA_TYPE int {@code =>} reserved for future use
*
- SQL_DATETIME_SUB int {@code =>} reserved for future use
*
- CHAR_OCTET_LENGTH int {@code =>} the maximum length of binary and character based
* columns. For any other datatype the returned value is a NULL
*
- ORDINAL_POSITION int {@code =>} the ordinal position, starting from 1, for the
* input and output parameters for a procedure. A value of 0 is returned if this row
* describes the procedure's return value. For result set columns, it is the ordinal
* position of the column in the result set starting from 1. If there are multiple result
* sets, the column ordinal positions are implementation defined.
*
- IS_NULLABLE String {@code =>} ISO rules are used to determine the nullability for
* a column.
*
* - YES --- if the column can include NULLs
*
- NO --- if the column cannot include NULLs
*
- empty string --- if the nullability for the column is unknown
*
* - SPECIFIC_NAME String {@code =>} the name which uniquely identifies this procedure
* within its schema.
*
*
* Note: Some databases may not return the column descriptions for a procedure.
*
*
The PRECISION column represents the specified column size for the given column. For numeric
* data, this is the maximum precision. For character data, this is the length in characters. For
* datetime datatypes, this is the length in characters of the String representation (assuming the
* maximum allowed precision of the fractional seconds component). For binary data, this is the
* length in bytes. For the ROWID datatype, this is the length in bytes. Null is returned for data
* types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param procedureNamePattern a procedure name pattern; must match the procedure name as it is
* stored in the database
* @param columnNamePattern a column name pattern; must match the column name as it is stored in
* the database
* @return ResultSet - each row describes a stored procedure parameter or column
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getProcedureColumns(
String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern)
throws SQLException {
StringBuilder sb;
if (haveInformationSchemaParameters()) {
/*
* Get info from information_schema.parameters
*/
sb =
new StringBuilder(
"SELECT SPECIFIC_SCHEMA PROCEDURE_CAT, NULL PROCEDURE_SCHEM, SPECIFIC_NAME PROCEDURE_NAME,"
+ " PARAMETER_NAME COLUMN_NAME, "
+ " CASE PARAMETER_MODE "
+ " WHEN 'IN' THEN "
+ procedureColumnIn
+ " WHEN 'OUT' THEN "
+ procedureColumnOut
+ " WHEN 'INOUT' THEN "
+ procedureColumnInOut
+ " ELSE IF(PARAMETER_MODE IS NULL,"
+ procedureColumnReturn
+ ","
+ procedureColumnUnknown
+ ")"
+ " END COLUMN_TYPE,"
+ dataTypeClause("DTD_IDENTIFIER")
+ " DATA_TYPE,"
+ "DATA_TYPE TYPE_NAME,"
+ " CASE DATA_TYPE"
+ " WHEN 'time' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 10, CAST(11 + DATETIME_PRECISION as signed integer))"
: "10")
+ " WHEN 'date' THEN 10"
+ " WHEN 'datetime' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19")
+ " WHEN 'timestamp' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19")
+ " ELSE "
+ " IF(NUMERIC_PRECISION IS NULL, LEAST(CHARACTER_MAXIMUM_LENGTH,"
+ Integer.MAX_VALUE
+ "), NUMERIC_PRECISION) "
+ " END `PRECISION`,"
+ " CASE DATA_TYPE"
+ " WHEN 'time' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 10, CAST(11 + DATETIME_PRECISION as signed integer))"
: "10")
+ " WHEN 'date' THEN 10"
+ " WHEN 'datetime' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19")
+ " WHEN 'timestamp' THEN "
+ (datePrecisionColumnExist
? "IF(DATETIME_PRECISION = 0, 19, CAST(20 + DATETIME_PRECISION as signed integer))"
: "19")
+ " ELSE "
+ " IF(NUMERIC_PRECISION IS NULL, LEAST(CHARACTER_MAXIMUM_LENGTH,"
+ Integer.MAX_VALUE
+ "), NUMERIC_PRECISION) "
+ " END `LENGTH`,"
+ (datePrecisionColumnExist
? " CASE DATA_TYPE"
+ " WHEN 'time' THEN CAST(DATETIME_PRECISION as signed integer)"
+ " WHEN 'datetime' THEN CAST(DATETIME_PRECISION as signed integer)"
+ " WHEN 'timestamp' THEN CAST(DATETIME_PRECISION as signed integer)"
+ " ELSE NUMERIC_SCALE "
+ " END `SCALE`,"
: " NUMERIC_SCALE `SCALE`,")
+ "10 RADIX,"
+ procedureNullableUnknown
+ " NULLABLE,NULL REMARKS,NULL COLUMN_DEF,0 SQL_DATA_TYPE,0 SQL_DATETIME_SUB,"
+ "CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH ,ORDINAL_POSITION, '' IS_NULLABLE, SPECIFIC_NAME "
+ " FROM INFORMATION_SCHEMA.PARAMETERS ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "SPECIFIC_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "SPECIFIC_NAME", procedureNamePattern);
firstCondition = patternCond(firstCondition, sb, "PARAMETER_NAME", columnNamePattern);
sb.append(" ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION");
} else {
/* No information_schema.parameters
* For now, just a dummy result set is returned.
*/
sb =
new StringBuilder(
"SELECT '' PROCEDURE_CAT, '' PROCEDURE_SCHEM , '' PROCEDURE_NAME,'' COLUMN_NAME, 0 COLUMN_TYPE,"
+ "0 DATA_TYPE,'' TYPE_NAME, 0 `PRECISION`,0 LENGTH, 0 SCALE,10 RADIX,"
+ "0 NULLABLE,NULL REMARKS,NULL COLUMN_DEF,0 SQL_DATA_TYPE,0 SQL_DATETIME_SUB,"
+ "0 CHAR_OCTET_LENGTH ,0 ORDINAL_POSITION, '' IS_NULLABLE, '' SPECIFIC_NAME "
+ " FROM DUAL "
+ " WHERE 1=0 ");
}
try {
return executeQuery(sb.toString());
} catch (SQLException sqlException) {
if (sqlException.getMessage().contains("Unknown column 'DATETIME_PRECISION'")) {
datePrecisionColumnExist = false;
return getProcedureColumns(catalog, schemaPattern, procedureNamePattern, columnNamePattern);
}
throw sqlException;
}
}
/**
* Retrieves a description of the given catalog's system or user function parameters and return
* type.
*
*
Only descriptions matching the schema, function and parameter name criteria are returned.
* They are ordered by FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME
* and SPECIFIC_ NAME. Within this, the return value, if any, is first. Next
* are the parameter descriptions in call order. The column descriptions follow in column number
* order.
*
*
Each row in the ResultSet is a parameter description, column description or
* return type description with the following fields:
*
*
* - FUNCTION_CAT String {@code =>} function catalog (may be
null)
* - FUNCTION_SCHEM String {@code =>} function schema (may be
null)
* - FUNCTION_NAME String {@code =>} function name. This is the name used to invoke the
* function
*
- COLUMN_NAME String {@code =>} column/parameter name
*
- COLUMN_TYPE Short {@code =>} kind of column/parameter:
*
* - functionColumnUnknown - nobody knows
*
- functionColumnIn - IN parameter
*
- functionColumnInOut - INOUT parameter
*
- functionColumnOut - OUT parameter
*
- functionColumnReturn - function return value
*
- functionColumnResult - Indicates that the parameter or column is a column in the
*
ResultSet
*
* - DATA_TYPE int {@code =>} SQL type from java.sql.Types
*
- TYPE_NAME String {@code =>} SQL type name, for a UDT type the type name is fully
* qualified
*
- PRECISION int {@code =>} precision
*
- LENGTH int {@code =>} length in bytes of data
*
- SCALE short {@code =>} scale - null is returned for data types where SCALE is not
* applicable.
*
- RADIX short {@code =>} radix
*
- NULLABLE short {@code =>} can it contain NULL.
*
* - functionNoNulls - does not allow NULL values
*
- functionNullable - allows NULL values
*
- functionNullableUnknown - nullability unknown
*
* - REMARKS String {@code =>} comment describing column/parameter
*
- CHAR_OCTET_LENGTH int {@code =>} the maximum length of binary and character based
* parameters or columns. For any other datatype the returned value is a NULL
*
- ORDINAL_POSITION int {@code =>} the ordinal position, starting from 1, for the
* input and output parameters. A value of 0 is returned if this row describes the
* function's return value. For result set columns, it is the ordinal position of the column
* in the result set starting from 1.
*
- IS_NULLABLE String {@code =>} ISO rules are used to determine the nullability for
* a parameter or column.
*
* - YES --- if the parameter or column can include NULLs
*
- NO --- if the parameter or column cannot include NULLs
*
- empty string --- if the nullability for the parameter or column is unknown
*
* - SPECIFIC_NAME String {@code =>} the name which uniquely identifies this function
* within its schema. This is a user specified, or DBMS generated, name that may be
* different then the
FUNCTION_NAME for example with overload functions
*
*
* The PRECISION column represents the specified column size for the given parameter or column.
* For numeric data, this is the maximum precision. For character data, this is the length in
* characters. For datetime datatypes, this is the length in characters of the String
* representation (assuming the maximum allowed precision of the fractional seconds component).
* For binary data, this is the length in bytes. For the ROWID datatype, this is the length in
* bytes. Null is returned for data types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param functionNamePattern a procedure name pattern; must match the function name as it is
* stored in the database
* @param columnNamePattern a parameter name pattern; must match the parameter or column name as
* it is stored in the database
* @return ResultSet - each row describes a user function parameter, column or return
* type
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.6
*/
public ResultSet getFunctionColumns(
String catalog, String schemaPattern, String functionNamePattern, String columnNamePattern)
throws SQLException {
StringBuilder sb;
if (haveInformationSchemaParameters()) {
sb =
new StringBuilder(
"SELECT SPECIFIC_SCHEMA `FUNCTION_CAT`, NULL `FUNCTION_SCHEM`, SPECIFIC_NAME FUNCTION_NAME,"
+ " PARAMETER_NAME COLUMN_NAME, "
+ " CASE PARAMETER_MODE "
+ " WHEN 'IN' THEN "
+ functionColumnIn
+ " WHEN 'OUT' THEN "
+ functionColumnOut
+ " WHEN 'INOUT' THEN "
+ functionColumnInOut
+ " ELSE "
+ functionReturn
+ " END COLUMN_TYPE,"
+ dataTypeClause("DTD_IDENTIFIER")
+ " DATA_TYPE,"
+ "DATA_TYPE TYPE_NAME,NUMERIC_PRECISION `PRECISION`,CHARACTER_MAXIMUM_LENGTH LENGTH,NUMERIC_SCALE SCALE,10 RADIX,"
+ procedureNullableUnknown
+ " NULLABLE,NULL REMARKS,"
+ "CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH ,ORDINAL_POSITION, '' IS_NULLABLE, SPECIFIC_NAME "
+ " FROM INFORMATION_SCHEMA.PARAMETERS ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "SPECIFIC_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "SPECIFIC_NAME", functionNamePattern);
firstCondition = patternCond(firstCondition, sb, "PARAMETER_NAME", columnNamePattern);
sb.append(firstCondition ? " WHERE " : " AND ")
.append(
" ROUTINE_TYPE='FUNCTION' ORDER BY FUNCTION_CAT, SPECIFIC_NAME, ORDINAL_POSITION");
} else {
/*
* No information_schema.parameters
* TODO : figure out what to do with older versions (get info via mysql.proc)
* For now, just a dummy result set is returned.
*/
sb =
new StringBuilder(
"SELECT '' FUNCTION_CAT, NULL FUNCTION_SCHEM, '' FUNCTION_NAME,"
+ " '' COLUMN_NAME, 0 COLUMN_TYPE, 0 DATA_TYPE,"
+ " '' TYPE_NAME,0 `PRECISION`,0 LENGTH, 0 SCALE,0 RADIX,"
+ " 0 NULLABLE,NULL REMARKS, 0 CHAR_OCTET_LENGTH , 0 ORDINAL_POSITION, "
+ " '' IS_NULLABLE, '' SPECIFIC_NAME "
+ " FROM DUAL WHERE 1=0 ");
}
return executeQuery(sb.toString());
}
public ResultSet getSchemas() throws SQLException {
return executeQuery("SELECT '' TABLE_SCHEM, '' TABLE_catalog FROM DUAL WHERE 1=0");
}
public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException {
return executeQuery("SELECT ' ' table_schem, ' ' table_catalog FROM DUAL WHERE 1=0");
}
public ResultSet getCatalogs() throws SQLException {
return executeQuery("SELECT SCHEMA_NAME TABLE_CAT FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY 1");
}
public ResultSet getTableTypes() throws SQLException {
return executeQuery(
"SELECT 'TABLE' TABLE_TYPE UNION SELECT 'SYSTEM VIEW' TABLE_TYPE UNION SELECT 'VIEW' TABLE_TYPE");
}
/**
* Retrieves a description of the access rights for a table's columns.
*
*
Only privileges matching the column name criteria are returned. They are ordered by
* COLUMN_NAME and PRIVILEGE.
*
*
Each privilege description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- COLUMN_NAME String {@code =>} column name
*
- GRANTOR String {@code =>} grantor of access (may be
null)
* - GRANTEE String {@code =>} grantee of access
*
- PRIVILEGE String {@code =>} name of access (SELECT, INSERT, UPDATE, REFRENCES,
* ...)
*
- IS_GRANTABLE String {@code =>} "YES" if grantee is permitted to grant to others;
* "NO" if not;
null if unknown
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in the database
* @param columnNamePattern a column name pattern; must match the column name as it is stored in
* the database
* @return ResultSet - each row is a column privilege description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getColumnPrivileges(
String catalog, String schema, String table, String columnNamePattern) throws SQLException {
if (table == null) {
throw new SQLException("'table' parameter must not be null");
}
StringBuilder sb =
new StringBuilder(
"SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME,"
+ " COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM "
+ " INFORMATION_SCHEMA.COLUMN_PRIVILEGES ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "TABLE_SCHEMA", catalog);
sb.append(firstCondition ? " WHERE " : " AND ")
.append(" TABLE_NAME = ")
.append(escapeQuote(table));
patternCond(false, sb, "COLUMN_NAME", columnNamePattern);
sb.append(" ORDER BY COLUMN_NAME, PRIVILEGE_TYPE");
return executeQuery(sb.toString());
}
/**
* Retrieves a description of the access rights for each table available in a catalog. Note that a
* table privilege applies to one or more columns in the table. It would be wrong to assume that
* this privilege applies to all columns (this may be true for some systems but is not true for
* all.)
*
* Only privileges matching the schema and table name criteria are returned. They are ordered
* by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and
* PRIVILEGE.
*
*
Each privilege description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- GRANTOR String {@code =>} grantor of access (may be
null)
* - GRANTEE String {@code =>} grantee of access
*
- PRIVILEGE String {@code =>} name of access (SELECT, INSERT, UPDATE, REFRENCES,
* ...)
*
- IS_GRANTABLE String {@code =>} "YES" if grantee is permitted to grant to others;
* "NO" if not;
null if unknown
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param tableNamePattern a table name pattern; must match the table name as it is stored in the
* database
* @return ResultSet - each row is a table privilege description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
*/
public ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT TABLE_SCHEMA TABLE_CAT,NULL TABLE_SCHEM, TABLE_NAME, NULL GRANTOR,"
+ "GRANTEE, PRIVILEGE_TYPE PRIVILEGE, IS_GRANTABLE FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "TABLE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "TABLE_NAME", tableNamePattern);
sb.append("ORDER BY TABLE_SCHEMA, TABLE_NAME, PRIVILEGE_TYPE ");
return executeQuery(sb.toString());
}
/**
* Retrieves a description of a table's columns that are automatically updated when any value in a
* row is updated. They are unordered.
*
* Each column description has the following columns:
*
*
* - SCOPE short {@code =>} is not used
*
- COLUMN_NAME String {@code =>} column name
*
- DATA_TYPE int {@code =>} SQL data type from
java.sql.Types
* - TYPE_NAME String {@code =>} Data source-dependent type name
*
- COLUMN_SIZE int {@code =>} precision
*
- BUFFER_LENGTH int {@code =>} length of column value in bytes
*
- DECIMAL_DIGITS short {@code =>} scale - Null is returned for data types where
* DECIMAL_DIGITS is not applicable.
*
- PSEUDO_COLUMN short {@code =>} whether this is pseudo column like an Oracle ROWID
*
* - versionColumnUnknown - may or may not be pseudo column
*
- versionColumnNotPseudo - is NOT a pseudo column
*
- versionColumnPseudo - is a pseudo column
*
*
*
* The COLUMN_SIZE column represents the specified column size for the given column. For
* numeric data, this is the maximum precision. For character data, this is the length in
* characters. For datetime datatypes, this is the length in characters of the String
* representation (assuming the maximum allowed precision of the fractional seconds component).
* For binary data, this is the length in bytes. For the ROWID datatype, this is the length in
* bytes. Null is returned for data types where the column size is not applicable.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog;null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in the database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in the database
* @return a ResultSet object in which each row is a column description
* @throws SQLException if a database access error occurs
*/
public ResultSet getVersionColumns(String catalog, String schema, String table)
throws SQLException {
String sql =
"SELECT 0 SCOPE, ' ' COLUMN_NAME, 0 DATA_TYPE,"
+ " ' ' TYPE_NAME, 0 COLUMN_SIZE, 0 BUFFER_LENGTH,"
+ " 0 DECIMAL_DIGITS, 0 PSEUDO_COLUMN "
+ " FROM DUAL WHERE 1 = 0";
return executeQuery(sql);
}
/**
* Retrieves a description of the foreign key columns in the given foreign key table that
* reference the primary key or the columns representing a unique constraint of the parent table
* (could be the same or a different table). The number of columns returned from the parent table
* must match the number of columns that make up the foreign key. They are ordered by FKTABLE_CAT,
* FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.
*
*
Each foreign key column description has the following columns:
*
*
* - PKTABLE_CAT String {@code =>} parent key table catalog (may be
null)
* - PKTABLE_SCHEM String {@code =>} parent key table schema (may be
null)
* - PKTABLE_NAME String {@code =>} parent key table name
*
- PKCOLUMN_NAME String {@code =>} parent key column name
*
- FKTABLE_CAT String {@code =>} foreign key table catalog (may be
null)
* being exported (may be null)
* - FKTABLE_SCHEM String {@code =>} foreign key table schema (may be
null
* ) being exported (may be null)
* - FKTABLE_NAME String {@code =>} foreign key table name being exported
*
- FKCOLUMN_NAME String {@code =>} foreign key column name being exported
*
- KEY_SEQ short {@code =>} sequence number within foreign key( a value of 1
* represents the first column of the foreign key, a value of 2 would represent the second
* column within the foreign key).
*
- UPDATE_RULE short {@code =>} What happens to foreign key when parent key is
* updated:
*
* - importedNoAction - do not allow update of parent key if it has been imported
*
- importedKeyCascade - change imported key to agree with parent key update
*
- importedKeySetNull - change imported key to
NULL if its parent key has
* been updated
* - importedKeySetDefault - change imported key to default values if its parent key has
* been updated
*
- importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
* - DELETE_RULE short {@code =>} What happens to the foreign key when parent key is
* deleted.
*
* - importedKeyNoAction - do not allow delete of parent key if it has been imported
*
- importedKeyCascade - delete rows that import a deleted key
*
- importedKeySetNull - change imported key to
NULL if its primary key
* has been deleted
* - importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
*
- importedKeySetDefault - change imported key to default if its parent key has been
* deleted
*
* - FK_NAME String {@code =>} foreign key name (may be
null)
* - PK_NAME String {@code =>} parent key name (may be
null)
* - DEFERRABILITY short {@code =>} can the evaluation of foreign key constraints be
* deferred until commit
*
* - importedKeyInitiallyDeferred - see SQL92 for definition
*
- importedKeyInitiallyImmediate - see SQL92 for definition
*
- importedKeyNotDeferrable - see SQL92 for definition
*
*
*
* @param parentCatalog a catalog name; must match the catalog name as it is stored in the
* database; "" retrieves those without a catalog; null means drop catalog name
* from the selection criteria
* @param parentSchema a schema name; must match the schema name as it is stored in the database;
* "" retrieves those without a schema; null means drop schema name from the
* selection criteria
* @param parentTable the name of the table that exports the key; pattern, or null (means any
* table) value
* @param foreignCatalog a catalog name; must match the catalog name as it is stored in the
* database; "" retrieves those without a catalog; null means drop catalog name
* from the selection criteria
* @param foreignSchema a schema name; must match the schema name as it is stored in the database;
* "" retrieves those without a schema; null means drop schema name from the
* selection criteria
* @param foreignTable the name of the table that imports the key; pattern, or null (means any
* table) value is stored in the database
* @return ResultSet - each row is a foreign key column description
* @throws SQLException if a database access error occurs
* @see #getImportedKeys
*/
public ResultSet getCrossReference(
String parentCatalog,
String parentSchema,
String parentTable,
String foreignCatalog,
String foreignSchema,
String foreignTable)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM, KCU.REFERENCED_TABLE_NAME PKTABLE_NAME,"
+ " KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, "
+ " KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ,"
+ " CASE update_rule "
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END UPDATE_RULE,"
+ " CASE DELETE_RULE"
+ " WHEN 'RESTRICT' THEN 1"
+ " WHEN 'NO ACTION' THEN 3"
+ " WHEN 'CASCADE' THEN 0"
+ " WHEN 'SET NULL' THEN 2"
+ " WHEN 'SET DEFAULT' THEN 4"
+ " END DELETE_RULE,"
+ " RC.CONSTRAINT_NAME FK_NAME,"
+ " RC.UNIQUE_CONSTRAINT_NAME PK_NAME,"
+ importedKeyNotDeferrable
+ " DEFERRABILITY "
+ "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU"
+ " INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC"
+ " ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA"
+ " AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "KCU.REFERENCED_TABLE_SCHEMA", parentCatalog);
firstCondition = catalogCond(firstCondition, sb, "KCU.TABLE_SCHEMA", foreignCatalog);
firstCondition = patternCond(firstCondition, sb, "KCU.REFERENCED_TABLE_NAME", parentTable);
firstCondition = patternCond(firstCondition, sb, "KCU.TABLE_NAME", foreignTable);
sb.append("ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ");
return executeQuery(sb.toString());
}
/**
* Retrieves a description of all the data types supported by this database. They are ordered by
* DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.
*
* If the database supports SQL distinct types, then getTypeInfo() will return a single row
* with a TYPE_NAME of DISTINCT and a DATA_TYPE of Types.DISTINCT. If the database supports SQL
* structured types, then getTypeInfo() will return a single row with a TYPE_NAME of STRUCT and a
* DATA_TYPE of Types.STRUCT.
*
*
If SQL distinct or structured types are supported, then information on the individual types
* may be obtained from the getUDTs() method.
*
*
Each type description has the following columns:
*
*
* - TYPE_NAME String {@code =>} Type name
*
- DATA_TYPE int {@code =>} SQL data type from java.sql.Types
*
- PRECISION int {@code =>} maximum precision
*
- LITERAL_PREFIX String {@code =>} prefix used to quote a literal (may be
null
* )
* - LITERAL_SUFFIX String {@code =>} suffix used to quote a literal (may be
null
* )
* - CREATE_PARAMS String {@code =>} parameters used in creating the type (may be
*
null)
* - NULLABLE short {@code =>} can you use NULL for this type.
*
* - typeNoNulls - does not allow NULL values
*
- typeNullable - allows NULL values
*
- typeNullableUnknown - nullability unknown
*
* - CASE_SENSITIVE boolean{@code =>} is it case sensitive.
*
- SEARCHABLE short {@code =>} can you use "WHERE" based on this type:
*
* - typePredNone - No support
*
- typePredChar - Only supported with WHERE .. LIKE
*
- typePredBasic - Supported except for WHERE .. LIKE
*
- typeSearchable - Supported for all WHERE ..
*
* - UNSIGNED_ATTRIBUTE boolean {@code =>} is it unsigned.
*
- FIXED_PREC_SCALE boolean {@code =>} can it be a money value.
*
- AUTO_INCREMENT boolean {@code =>} can it be used for an auto-increment value.
*
- LOCAL_TYPE_NAME String {@code =>} localized version of type name (may be
* null)
* - MINIMUM_SCALE short {@code =>} minimum scale supported
*
- MAXIMUM_SCALE short {@code =>} maximum scale supported
*
- SQL_DATA_TYPE int {@code =>} unused
*
- SQL_DATETIME_SUB int {@code =>} unused
*
- NUM_PREC_RADIX int {@code =>} usually 2 or 10
*
*
* The PRECISION column represents the maximum column size that the server supports for the
* given datatype. For numeric data, this is the maximum precision. For character data, this is
* the length in characters. For datetime datatypes, this is the length in characters of the
* String representation (assuming the maximum allowed precision of the fractional seconds
* component). For binary data, this is the length in bytes. For the ROWID datatype, this is the
* length in bytes. Null is returned for data types where the column size is not applicable.
*
* @return a ResultSet object in which each row is an SQL type description
*/
public ResultSet getTypeInfo() {
String[] columnNames = {
"TYPE_NAME", "DATA_TYPE", "PRECISION", "LITERAL_PREFIX", "LITERAL_SUFFIX",
"CREATE_PARAMS", "NULLABLE", "CASE_SENSITIVE", "SEARCHABLE", "UNSIGNED_ATTRIBUTE",
"FIXED_PREC_SCALE", "AUTO_INCREMENT", "LOCAL_TYPE_NAME", "MINIMUM_SCALE", "MAXIMUM_SCALE",
"SQL_DATA_TYPE", "SQL_DATETIME_SUB", "NUM_PREC_RADIX"
};
ColumnType[] columnTypes = {
ColumnType.VARCHAR,
ColumnType.INTEGER,
ColumnType.INTEGER,
ColumnType.VARCHAR,
ColumnType.VARCHAR,
ColumnType.VARCHAR,
ColumnType.INTEGER,
ColumnType.BIT,
ColumnType.SMALLINT,
ColumnType.BIT,
ColumnType.BIT,
ColumnType.BIT,
ColumnType.VARCHAR,
ColumnType.SMALLINT,
ColumnType.SMALLINT,
ColumnType.INTEGER,
ColumnType.INTEGER,
ColumnType.INTEGER
};
String[][] data = {
{"BIT", "-7", "1", "", "", "", "1", "1", "3", "0", "0", "0", "BIT", "0", "0", "0", "0", "10"},
{
"BOOL", "-7", "1", "", "", "", "1", "1", "3", "0", "0", "0", "BOOL", "0", "0", "0", "0",
"10"
},
{
"TINYINT",
"-6",
"3",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"TINYINT",
"0",
"0",
"0",
"0",
"10"
},
{
"TINYINT UNSIGNED",
"-6",
"3",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"TINYINT UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"BIGINT",
"-5",
"19",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"BIGINT",
"0",
"0",
"0",
"0",
"10"
},
{
"BIGINT UNSIGNED",
"-5",
"20",
"",
"",
"[(M)] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"BIGINT UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"LONG VARBINARY",
"-4",
"16777215",
"'",
"'",
"",
"1",
"1",
"3",
"0",
"0",
"0",
"LONG VARBINARY",
"0",
"0",
"0",
"0",
"10"
},
{
"MEDIUMBLOB",
"-4",
"16777215",
"'",
"'",
"",
"1",
"1",
"3",
"0",
"0",
"0",
"MEDIUMBLOB",
"0",
"0",
"0",
"0",
"10"
},
{
"LONGBLOB",
"-4",
"2147483647",
"'",
"'",
"",
"1",
"1",
"3",
"0",
"0",
"0",
"LONGBLOB",
"0",
"0",
"0",
"0",
"10"
},
{
"BLOB", "-4", "65535", "'", "'", "", "1", "1", "3", "0", "0", "0", "BLOB", "0", "0", "0",
"0", "10"
},
{
"TINYBLOB",
"-4",
"255",
"'",
"'",
"",
"1",
"1",
"3",
"0",
"0",
"0",
"TINYBLOB",
"0",
"0",
"0",
"0",
"10"
},
{
"VARBINARY",
"-3",
"255",
"'",
"'",
"(M)",
"1",
"1",
"3",
"0",
"0",
"0",
"VARBINARY",
"0",
"0",
"0",
"0",
"10"
},
{
"BINARY", "-2", "255", "'", "'", "(M)", "1", "1", "3", "0", "0", "0", "BINARY", "0", "0",
"0", "0", "10"
},
{
"LONG VARCHAR",
"-1",
"16777215",
"'",
"'",
"",
"1",
"0",
"3",
"0",
"0",
"0",
"LONG VARCHAR",
"0",
"0",
"0",
"0",
"10"
},
{
"MEDIUMTEXT",
"-1",
"16777215",
"'",
"'",
"",
"1",
"0",
"3",
"0",
"0",
"0",
"MEDIUMTEXT",
"0",
"0",
"0",
"0",
"10"
},
{
"LONGTEXT",
"-1",
"2147483647",
"'",
"'",
"",
"1",
"0",
"3",
"0",
"0",
"0",
"LONGTEXT",
"0",
"0",
"0",
"0",
"10"
},
{
"TEXT", "-1", "65535", "'", "'", "", "1", "0", "3", "0", "0", "0", "TEXT", "0", "0", "0",
"0", "10"
},
{
"TINYTEXT",
"-1",
"255",
"'",
"'",
"",
"1",
"0",
"3",
"0",
"0",
"0",
"TINYTEXT",
"0",
"0",
"0",
"0",
"10"
},
{
"CHAR", "1", "255", "'", "'", "(M)", "1", "0", "3", "0", "0", "0", "CHAR", "0", "0", "0",
"0", "10"
},
{
"NUMERIC",
"2",
"65",
"",
"",
"[(M,D])] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"NUMERIC",
"-308",
"308",
"0",
"0",
"10"
},
{
"DECIMAL",
"3",
"65",
"",
"",
"[(M,D])] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"DECIMAL",
"-308",
"308",
"0",
"0",
"10"
},
{
"INTEGER",
"4",
"10",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"INTEGER",
"0",
"0",
"0",
"0",
"10"
},
{
"INTEGER UNSIGNED",
"4",
"10",
"",
"",
"[(M)] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"INTEGER UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"INT",
"4",
"10",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"INT",
"0",
"0",
"0",
"0",
"10"
},
{
"INT UNSIGNED",
"4",
"10",
"",
"",
"[(M)] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"INT UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"MEDIUMINT",
"4",
"7",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"MEDIUMINT",
"0",
"0",
"0",
"0",
"10"
},
{
"MEDIUMINT UNSIGNED",
"4",
"8",
"",
"",
"[(M)] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"MEDIUMINT UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"SMALLINT",
"5",
"5",
"",
"",
"[(M)] [UNSIGNED] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"SMALLINT",
"0",
"0",
"0",
"0",
"10"
},
{
"SMALLINT UNSIGNED",
"5",
"5",
"",
"",
"[(M)] [ZEROFILL]",
"1",
"0",
"3",
"1",
"0",
"1",
"SMALLINT UNSIGNED",
"0",
"0",
"0",
"0",
"10"
},
{
"FLOAT",
"7",
"10",
"",
"",
"[(M|D)] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"FLOAT",
"-38",
"38",
"0",
"0",
"10"
},
{
"DOUBLE",
"8",
"17",
"",
"",
"[(M|D)] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"DOUBLE",
"-308",
"308",
"0",
"0",
"10"
},
{
"DOUBLE PRECISION",
"8",
"17",
"",
"",
"[(M,D)] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"DOUBLE PRECISION",
"-308",
"308",
"0",
"0",
"10"
},
{
"REAL",
"8",
"17",
"",
"",
"[(M,D)] [ZEROFILL]",
"1",
"0",
"3",
"0",
"0",
"1",
"REAL",
"-308",
"308",
"0",
"0",
"10"
},
{
"VARCHAR", "12", "255", "'", "'", "(M)", "1", "0", "3", "0", "0", "0", "VARCHAR", "0", "0",
"0", "0", "10"
},
{
"ENUM", "12", "65535", "'", "'", "", "1", "0", "3", "0", "0", "0", "ENUM", "0", "0", "0",
"0", "10"
},
{
"SET", "12", "64", "'", "'", "", "1", "0", "3", "0", "0", "0", "SET", "0", "0", "0", "0",
"10"
},
{
"DATE", "91", "10", "'", "'", "", "1", "0", "3", "0", "0", "0", "DATE", "0", "0", "0", "0",
"10"
},
{
"TIME", "92", "18", "'", "'", "[(M)]", "1", "0", "3", "0", "0", "0", "TIME", "0", "0", "0",
"0", "10"
},
{
"DATETIME",
"93",
"27",
"'",
"'",
"[(M)]",
"1",
"0",
"3",
"0",
"0",
"0",
"DATETIME",
"0",
"0",
"0",
"0",
"10"
},
{
"TIMESTAMP",
"93",
"27",
"'",
"'",
"[(M)]",
"1",
"0",
"3",
"0",
"0",
"0",
"TIMESTAMP",
"0",
"0",
"0",
"0",
"10"
}
};
return SelectResultSet.createResultSet(
columnNames, columnTypes, data, connection.getProtocol());
}
/**
* Retrieves a description of the given table's indices and statistics. They are ordered by
* NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
*
*
Each index column description has the following columns:
*
*
* - TABLE_CAT String {@code =>} table catalog (may be
null)
* - TABLE_SCHEM String {@code =>} table schema (may be
null)
* - TABLE_NAME String {@code =>} table name
*
- NON_UNIQUE boolean {@code =>} Can index values be non-unique. false when TYPE is
* tableIndexStatistic
*
- INDEX_QUALIFIER String {@code =>} index catalog (may be
null);
* null when TYPE is tableIndexStatistic
* - INDEX_NAME String {@code =>} index name;
null when TYPE is
* tableIndexStatistic
* - TYPE short {@code =>} index type:
*
* - tableIndexStatistic - this identifies table statistics that are returned in
* conjuction with a table's index descriptions
*
- tableIndexClustered - this is a clustered index
*
- tableIndexHashed - this is a hashed index
*
- tableIndexOther - this is some other style of index
*
* - ORDINAL_POSITION short {@code =>} column sequence number within index; zero when
* TYPE is tableIndexStatistic
*
- COLUMN_NAME String {@code =>} column name;
null when TYPE is
* tableIndexStatistic
* - ASC_OR_DESC String {@code =>} column sort sequence, "A" {@code =>} ascending, "D"
* {@code =>} descending, may be
null if sort sequence is not supported;
* null when TYPE is tableIndexStatistic
* - CARDINALITY long {@code =>} When TYPE is tableIndexStatistic, then this is the
* number of rows in the table; otherwise, it is the number of unique values in the index.
*
- PAGES long {@code =>} When TYPE is tableIndexStatisic then this is the number of
* pages used for the table, otherwise it is the number of pages used for the current index.
*
- FILTER_CONDITION String {@code =>} Filter condition, if any. (may be
null
* )
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in this database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schema a schema name; must match the schema name as it is stored in this database; ""
* retrieves those without a schema; null means that the schema name should not
* be used to narrow the search
* @param table a table name; must match the table name as it is stored in this database
* @param unique when true, return only indices for unique values; when false, return indices
* regardless of whether unique or not
* @param approximate when true, result is allowed to reflect approximate or out of data values;
* when false, results are requested to be accurate
* @return ResultSet - each row is an index column description
* @throws SQLException if a database access error occurs
*/
public ResultSet getIndexInfo(
String catalog, String schema, String table, boolean unique, boolean approximate)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, NON_UNIQUE, "
+ " TABLE_SCHEMA INDEX_QUALIFIER, INDEX_NAME, "
+ tableIndexOther
+ " TYPE,"
+ " SEQ_IN_INDEX ORDINAL_POSITION, COLUMN_NAME, COLLATION ASC_OR_DESC,"
+ " CARDINALITY, NULL PAGES, NULL FILTER_CONDITION"
+ " FROM INFORMATION_SCHEMA.STATISTICS");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "TABLE_SCHEMA", catalog);
sb.append(firstCondition ? " WHERE " : " AND ")
.append("TABLE_NAME = ")
.append(escapeQuote(table));
if (unique) sb.append(" AND NON_UNIQUE = 0");
sb.append(" ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION");
return executeQuery(sb.toString());
}
/**
* Retrieves whether this database supports the given result set type. ResultSet.TYPE_FORWARD_ONLY
* and ResultSet.TYPE_SCROLL_INSENSITIVE are supported.
*
* @param type one of the following ResultSet constants:
*
* ResultSet.TYPE_FORWARD_ONLY
* ResultSet.TYPE_SCROLL_INSENSITIVE
* ResultSet.TYPE_SCROLL_SENSITIVE
*
*
* @return true if supported
*/
public boolean supportsResultSetType(int type) {
return (type == ResultSet.TYPE_SCROLL_INSENSITIVE || type == ResultSet.TYPE_FORWARD_ONLY);
}
/**
* Retrieves whether this database supports the given concurrency type in combination with the
* given result set type. All are supported, but combination that use
* ResultSet.TYPE_SCROLL_INSENSITIVE.
*
* @param type one of the following ResultSet constants:
*
* ResultSet.TYPE_FORWARD_ONLY
* ResultSet.TYPE_SCROLL_INSENSITIVE
* ResultSet.TYPE_SCROLL_SENSITIVE
*
*
* @param concurrency one of the following ResultSet constants:
*
* ResultSet.CONCUR_READ_ONLY
* ResultSet.CONCUR_UPDATABLE
*
*
* @return true if supported
*/
public boolean supportsResultSetConcurrency(int type, int concurrency) {
// Support all concurrency (ResultSet.CONCUR_READ_ONLY and ResultSet.CONCUR_UPDATABLE)
// so just return scroll type
return type == ResultSet.TYPE_SCROLL_INSENSITIVE || type == ResultSet.TYPE_FORWARD_ONLY;
}
public boolean ownUpdatesAreVisible(int type) {
return supportsResultSetType(type);
}
public boolean ownDeletesAreVisible(int type) {
return supportsResultSetType(type);
}
public boolean ownInsertsAreVisible(int type) {
return supportsResultSetType(type);
}
public boolean othersUpdatesAreVisible(int type) {
return false;
}
public boolean othersDeletesAreVisible(int type) {
return false;
}
public boolean othersInsertsAreVisible(int type) {
return false;
}
public boolean updatesAreDetected(int type) {
return false;
}
public boolean deletesAreDetected(int type) {
return false;
}
public boolean insertsAreDetected(int type) {
return false;
}
public boolean supportsBatchUpdates() {
return true;
}
/**
* Retrieves a description of the user-defined types (UDTs) defined in a particular schema.
* Schema-specific UDTs may have type JAVA_OBJECT, STRUCT, or
* DISTINCT.
*
* Only types matching the catalog, schema, type name and type criteria are returned. They are
* ordered by DATA_TYPE, TYPE_CAT, TYPE_SCHEM and
* TYPE_NAME. The type name parameter may be a fully-qualified name. In this case, the
* catalog and schemaPattern parameters are ignored.
*
*
Each type description has the following columns:
*
*
* - TYPE_CAT String {@code =>} the type's catalog (may be
null)
* - TYPE_SCHEM String {@code =>} type's schema (may be
null)
* - TYPE_NAME String {@code =>} type name
*
- CLASS_NAME String {@code =>} Java class name
*
- DATA_TYPE int {@code =>} type value defined in java.sql.Types. One of JAVA_OBJECT,
* STRUCT, or DISTINCT
*
- REMARKS String {@code =>} explanatory comment on the type
*
- BASE_TYPE short {@code =>} type code of the source type of a DISTINCT type or the
* type that implements the user-generated reference type of the SELF_REFERENCING_COLUMN of
* a structured type as defined in java.sql.Types (
null if DATA_TYPE is not
* DISTINCT or not STRUCT with REFERENCE_GENERATION = USER_DEFINED)
*
*
* Note: If the driver does not support UDTs, an empty result set is returned.
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema pattern name; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param typeNamePattern a type name pattern; must match the type name as it is stored in the
* database; may be a fully qualified name
* @param types a list of user-defined types (JAVA_OBJECT, STRUCT, or DISTINCT) to include;
* null returns all types
* @return ResultSet object in which each row describes a UDT
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.2
*/
@Override
public ResultSet getUDTs(
String catalog, String schemaPattern, String typeNamePattern, int[] types)
throws SQLException {
String sql =
"SELECT ' ' TYPE_CAT, NULL TYPE_SCHEM, ' ' TYPE_NAME, ' ' CLASS_NAME, 0 DATA_TYPE, ' ' REMARKS, 0 BASE_TYPE"
+ " FROM DUAL WHERE 1=0";
return executeQuery(sql);
}
public Connection getConnection() {
return connection;
}
public boolean supportsSavepoints() {
return true;
}
public boolean supportsNamedParameters() {
return false;
}
public boolean supportsMultipleOpenResults() {
return false;
}
public boolean supportsGetGeneratedKeys() {
return true;
}
/**
* Retrieves a description of the user-defined type (UDT) hierarchies defined in a particular
* schema in this database. Only the immediate super type/ sub type relationship is modeled. Only
* supertype information for UDTs matching the catalog, schema, and type name is returned. The
* type name parameter may be a fully-qualified name. When the UDT name supplied is a
* fully-qualified name, the catalog and schemaPattern parameters are ignored. If a UDT does not
* have a direct super type, it is not listed here. A row of the ResultSet object
* returned by this method describes the designated UDT and a direct supertype. A row has the
* following columns:
*
*
* - TYPE_CAT String {@code =>} the UDT's catalog (may be
null)
* - TYPE_SCHEM String {@code =>} UDT's schema (may be
null)
* - TYPE_NAME String {@code =>} type name of the UDT
*
- SUPERTYPE_CAT String {@code =>} the direct super type's catalog (may be
null
* )
* - SUPERTYPE_SCHEM String {@code =>} the direct super type's schema (may be
* null)
* - SUPERTYPE_NAME String {@code =>} the direct super type's name
*
*
* Note: If the driver does not support type hierarchies, an empty result set is
* returned.
*
* @param catalog a catalog name; "" retrieves those without a catalog; null means
* drop catalog name from the selection criteria
* @param schemaPattern a schema name pattern; "" retrieves those without a schema
* @param typeNamePattern a UDT name pattern; may be a fully-qualified name
* @return a ResultSet object in which a row gives information about the designated
* UDT
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.4
*/
public ResultSet getSuperTypes(String catalog, String schemaPattern, String typeNamePattern)
throws SQLException {
String sql =
"SELECT ' ' TYPE_CAT, NULL TYPE_SCHEM, ' ' TYPE_NAME, ' ' SUPERTYPE_CAT, ' ' SUPERTYPE_SCHEM, ' ' SUPERTYPE_NAME"
+ " FROM DUAL WHERE 1=0";
return executeQuery(sql);
}
/**
* Retrieves a description of the table hierarchies defined in a particular schema in this
* database.
*
*
Only supertable information for tables matching the catalog, schema and table name are
* returned. The table name parameter may be a fully-qualified name, in which case, the catalog
* and schemaPattern parameters are ignored. If a table does not have a super table, it is not
* listed here. Supertables have to be defined in the same catalog and schema as the sub tables.
* Therefore, the type description does not need to include this information for the supertable.
*
*
Each type description has the following columns:
*
*
* - TABLE_CAT String {@code =>} the type's catalog (may be
null)
* - TABLE_SCHEM String {@code =>} type's schema (may be
null)
* - TABLE_NAME String {@code =>} type name
*
- SUPERTABLE_NAME String {@code =>} the direct super type's name
*
*
* Note: If the driver does not support type hierarchies, an empty result set is
* returned.
*
* @param catalog a catalog name; "" retrieves those without a catalog; null means
* drop catalog name from the selection criteria
* @param schemaPattern a schema name pattern; "" retrieves those without a schema
* @param tableNamePattern a table name pattern; may be a fully-qualified name
* @return a ResultSet object in which each row is a type description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.4
*/
public ResultSet getSuperTables(String catalog, String schemaPattern, String tableNamePattern)
throws SQLException {
String sql =
"SELECT ' ' TABLE_CAT, ' ' TABLE_SCHEM, ' ' TABLE_NAME, ' ' SUPERTABLE_NAME FROM DUAL WHERE 1=0";
return executeQuery(sql);
}
/**
* Retrieves a description of the given attribute of the given type for a user-defined type (UDT)
* that is available in the given schema and catalog. Descriptions are returned only for
* attributes of UDTs matching the catalog, schema, type, and attribute name criteria. They are
* ordered by TYPE_CAT, TYPE_SCHEM, TYPE_NAME and
* ORDINAL_POSITION. This description does not contain inherited attributes. The
* ResultSet object that is returned has the following columns:
*
*
* - TYPE_CAT String {@code =>} type catalog (may be
null)
* - TYPE_SCHEM String {@code =>} type schema (may be
null)
* - TYPE_NAME String {@code =>} type name
*
- ATTR_NAME String {@code =>} attribute name
*
- DATA_TYPE int {@code =>} attribute type SQL type from java.sql.Types
*
- ATTR_TYPE_NAME String {@code =>} Data source dependent type name. For a UDT, the
* type name is fully qualified. For a REF, the type name is fully qualified and represents
* the target type of the reference type.
*
- ATTR_SIZE int {@code =>} column size. For char or date types this is the maximum
* number of characters; for numeric or decimal types this is precision.
*
- DECIMAL_DIGITS int {@code =>} the number of fractional digits. Null is returned
* for data types where DECIMAL_DIGITS is not applicable.
*
- NUM_PREC_RADIX int {@code =>} Radix (typically either 10 or 2)
*
- NULLABLE int {@code =>} whether NULL is allowed
*
* - attributeNoNulls - might not allow NULL values
*
- attributeNullable - definitely allows NULL values
*
- attributeNullableUnknown - nullability unknown
*
* - REMARKS String {@code =>} comment describing column (may be
null)
* - ATTR_DEF String {@code =>} default value (may be
null)
* - SQL_DATA_TYPE int {@code =>} unused
*
- SQL_DATETIME_SUB int {@code =>} unused
*
- CHAR_OCTET_LENGTH int {@code =>} for char types the maximum number of bytes in the
* column
*
- ORDINAL_POSITION int {@code =>} index of the attribute in the UDT (starting at 1)
*
- IS_NULLABLE String {@code =>} ISO rules are used to determine the nullability for
* a attribute.
*
* - YES --- if the attribute can include NULLs
*
- NO --- if the attribute cannot include NULLs
*
- empty string --- if the nullability for the attribute is unknown
*
* - SCOPE_CATALOG String {@code =>} catalog of table that is the scope of a reference
* attribute (
null if DATA_TYPE isn't REF)
* - SCOPE_SCHEMA String {@code =>} schema of table that is the scope of a reference
* attribute (
null if DATA_TYPE isn't REF)
* - SCOPE_TABLE String {@code =>} table name that is the scope of a reference
* attribute (
null if the DATA_TYPE isn't REF)
* - SOURCE_DATA_TYPE short {@code =>} source type of a distinct type or user-generated
* Ref type,SQL type from java.sql.Types (
null if DATA_TYPE isn't DISTINCT or
* user-generated REF)
*
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param typeNamePattern a type name pattern; must match the type name as it is stored in the
* database
* @param attributeNamePattern an attribute name pattern; must match the attribute name as it is
* declared in the database
* @return a ResultSet object in which each row is an attribute description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.4
*/
public ResultSet getAttributes(
String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern)
throws SQLException {
String sql =
"SELECT ' ' TYPE_CAT, ' ' TYPE_SCHEM, ' ' TYPE_NAME, ' ' ATTR_NAME, 0 DATA_TYPE,"
+ " ' ' ATTR_TYPE_NAME, 0 ATTR_SIZE, 0 DECIMAL_DIGITS, 0 NUM_PREC_RADIX, 0 NULLABLE,"
+ " ' ' REMARKS, ' ' ATTR_DEF, 0 SQL_DATA_TYPE, 0 SQL_DATETIME_SUB, 0 CHAR_OCTET_LENGTH,"
+ " 0 ORDINAL_POSITION, ' ' IS_NULLABLE, ' ' SCOPE_CATALOG, ' ' SCOPE_SCHEMA, ' ' SCOPE_TABLE,"
+ " 0 SOURCE_DATA_TYPE"
+ " FROM DUAL "
+ " WHERE 1=0";
return executeQuery(sql);
}
public boolean supportsResultSetHoldability(int holdability) {
return holdability == ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
public int getResultSetHoldability() {
return ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
public int getDatabaseMajorVersion() {
return connection.getProtocol().getMajorServerVersion();
}
public int getDatabaseMinorVersion() {
return connection.getProtocol().getMinorServerVersion();
}
@Override
public int getJDBCMajorVersion() {
return 4;
}
@Override
public int getJDBCMinorVersion() {
return 2;
}
@Override
public int getSQLStateType() {
return sqlStateSQL;
}
public boolean locatorsUpdateCopy() {
return false;
}
public boolean supportsStatementPooling() {
return false;
}
public RowIdLifetime getRowIdLifetime() {
return RowIdLifetime.ROWID_UNSUPPORTED;
}
public boolean supportsStoredFunctionsUsingCallSyntax() {
return true;
}
public boolean autoCommitFailureClosesAllResultSets() {
return false;
}
/**
* Retrieves a list of the client info properties that the driver supports. The result set
* contains the following columns
*
*
* - NAME String : The name of the client info property
*
- MAX_LEN int : The maximum length of the value for the property
*
- DEFAULT_VALUE String : The default value of the property
*
- DESCRIPTION String : A description of the property. This will typically contain
* information as to where this property is stored in the database.
*
*
* The ResultSet is sorted by the NAME column
*
* @return A ResultSet object; each row is a supported client info property
*/
public ResultSet getClientInfoProperties() {
ColumnDefinition[] columns = new ColumnDefinition[4];
columns[0] = ColumnDefinition.create("NAME", ColumnType.STRING);
columns[1] = ColumnDefinition.create("MAX_LEN", ColumnType.INTEGER);
columns[2] = ColumnDefinition.create("DEFAULT_VALUE", ColumnType.STRING);
columns[3] = ColumnDefinition.create("DESCRIPTION", ColumnType.STRING);
byte[] sixteenMb =
new byte[] {
(byte) 49, (byte) 54, (byte) 55, (byte) 55, (byte) 55, (byte) 50, (byte) 49, (byte) 53
};
byte[] empty = new byte[0];
ColumnType[] types =
new ColumnType[] {
ColumnType.STRING, ColumnType.INTEGER, ColumnType.STRING, ColumnType.STRING
};
List rows = new ArrayList<>(3);
rows.add(
StandardPacketInputStream.create(
new byte[][] {
"ApplicationName".getBytes(),
sixteenMb,
empty,
"The name of the application currently utilizing the connection".getBytes()
},
types));
rows.add(
StandardPacketInputStream.create(
new byte[][] {
"ClientUser".getBytes(),
sixteenMb,
empty,
("The name of the user that the application using the connection is performing work for. "
+ "This may not be the same as the user name that was used in establishing the connection.")
.getBytes()
},
types));
rows.add(
StandardPacketInputStream.create(
new byte[][] {
"ClientHostname".getBytes(),
sixteenMb,
empty,
"The hostname of the computer the application using the connection is running on"
.getBytes()
},
types));
return new SelectResultSet(
columns, rows, connection.getProtocol(), ResultSet.TYPE_SCROLL_INSENSITIVE);
}
/**
* Retrieves a description of the system and user functions available in the given catalog. Only
* system and user function descriptions matching the schema and function name criteria are
* returned. They are ordered by FUNCTION_CAT, FUNCTION_SCHEM,
* FUNCTION_NAME and SPECIFIC_ NAME.
*
* Each function description has the the following columns:
*
*
* - FUNCTION_CAT String {@code =>} function catalog (may be
null)
* - FUNCTION_SCHEM String {@code =>} function schema (may be
null)
* - FUNCTION_NAME String {@code =>} function name. This is the name used to invoke the
* function
*
- REMARKS String {@code =>} explanatory comment on the function
*
- FUNCTION_TYPE short {@code =>} kind of function:
*
* - functionResultUnknown - Cannot determine if a return value or table will be
* returned
*
- functionNoTable- Does not return a table
*
- functionReturnsTable - Returns a table
*
* - SPECIFIC_NAME String {@code =>} the name which uniquely identifies this function
* within its schema. This is a user specified, or DBMS generated, name that may be
* different then the
FUNCTION_NAME for example with overload functions
*
*
* A user may not have permission to execute any of the functions that are returned by
* getFunctions
*
* @param catalog a catalog name; must match the catalog name as it is stored in the database; ""
* retrieves those without a catalog; null means that the catalog name should not
* be used to narrow the search
* @param schemaPattern a schema name pattern; must match the schema name as it is stored in the
* database; "" retrieves those without a schema; null means that the schema name
* should not be used to narrow the search
* @param functionNamePattern a function name pattern; must match the function name as it is
* stored in the database
* @return ResultSet - each row is a function description
* @throws SQLException if a database access error occurs
* @see #getSearchStringEscape
* @since 1.6
*/
public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern)
throws SQLException {
StringBuilder sb =
new StringBuilder(
"SELECT ROUTINE_SCHEMA FUNCTION_CAT,NULL FUNCTION_SCHEM, ROUTINE_NAME FUNCTION_NAME,"
+ " ROUTINE_COMMENT REMARKS,"
+ functionNoTable
+ " FUNCTION_TYPE, SPECIFIC_NAME "
+ " FROM INFORMATION_SCHEMA.ROUTINES ");
boolean firstCondition = true;
firstCondition = catalogCond(firstCondition, sb, "ROUTINE_SCHEMA", catalog);
firstCondition = patternCond(firstCondition, sb, "ROUTINE_NAME", functionNamePattern);
sb.append(firstCondition ? " WHERE " : " AND ").append(" ROUTINE_TYPE='FUNCTION'");
return executeQuery(sb.toString());
}
public T unwrap(final Class iface) {
return null;
}
public boolean isWrapperFor(final Class iface) {
return false;
}
@Override
public long getMaxLogicalLobSize() {
return 4294967295L;
}
@Override
public boolean supportsRefCursors() {
return false;
}
}