001/* 002 * Copyright 2002-2020 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.jdbc.core.metadata; 018 019import java.sql.DatabaseMetaData; 020import java.sql.ResultSet; 021import java.sql.SQLException; 022import java.sql.Types; 023import java.util.ArrayList; 024import java.util.Arrays; 025import java.util.HashMap; 026import java.util.List; 027import java.util.Map; 028 029import org.apache.commons.logging.Log; 030import org.apache.commons.logging.LogFactory; 031 032import org.springframework.dao.DataAccessResourceFailureException; 033import org.springframework.jdbc.support.JdbcUtils; 034import org.springframework.lang.Nullable; 035 036/** 037 * A generic implementation of the {@link TableMetaDataProvider} interface 038 * which should provide enough features for all supported databases. 039 * 040 * @author Thomas Risberg 041 * @author Juergen Hoeller 042 * @since 2.5 043 */ 044public class GenericTableMetaDataProvider implements TableMetaDataProvider { 045 046 /** Logger available to subclasses. */ 047 protected static final Log logger = LogFactory.getLog(TableMetaDataProvider.class); 048 049 /** indicator whether column meta-data should be used. */ 050 private boolean tableColumnMetaDataUsed = false; 051 052 /** the version of the database. */ 053 @Nullable 054 private String databaseVersion; 055 056 /** the name of the user currently connected. */ 057 @Nullable 058 private String userName; 059 060 /** indicates whether the identifiers are uppercased. */ 061 private boolean storesUpperCaseIdentifiers = true; 062 063 /** indicates whether the identifiers are lowercased. */ 064 private boolean storesLowerCaseIdentifiers = false; 065 066 /** indicates whether generated keys retrieval is supported. */ 067 private boolean getGeneratedKeysSupported = true; 068 069 /** indicates whether the use of a String[] for generated keys is supported. */ 070 private boolean generatedKeysColumnNameArraySupported = true; 071 072 /** database products we know not supporting the use of a String[] for generated keys. */ 073 private List<String> productsNotSupportingGeneratedKeysColumnNameArray = 074 Arrays.asList("Apache Derby", "HSQL Database Engine"); 075 076 /** Collection of TableParameterMetaData objects. */ 077 private List<TableParameterMetaData> tableParameterMetaData = new ArrayList<>(); 078 079 080 /** 081 * Constructor used to initialize with provided database meta-data. 082 * @param databaseMetaData meta-data to be used 083 */ 084 protected GenericTableMetaDataProvider(DatabaseMetaData databaseMetaData) throws SQLException { 085 this.userName = databaseMetaData.getUserName(); 086 } 087 088 089 public void setStoresUpperCaseIdentifiers(boolean storesUpperCaseIdentifiers) { 090 this.storesUpperCaseIdentifiers = storesUpperCaseIdentifiers; 091 } 092 093 public boolean isStoresUpperCaseIdentifiers() { 094 return this.storesUpperCaseIdentifiers; 095 } 096 097 public void setStoresLowerCaseIdentifiers(boolean storesLowerCaseIdentifiers) { 098 this.storesLowerCaseIdentifiers = storesLowerCaseIdentifiers; 099 } 100 101 public boolean isStoresLowerCaseIdentifiers() { 102 return this.storesLowerCaseIdentifiers; 103 } 104 105 106 @Override 107 public boolean isTableColumnMetaDataUsed() { 108 return this.tableColumnMetaDataUsed; 109 } 110 111 @Override 112 public List<TableParameterMetaData> getTableParameterMetaData() { 113 return this.tableParameterMetaData; 114 } 115 116 @Override 117 public boolean isGetGeneratedKeysSupported() { 118 return this.getGeneratedKeysSupported; 119 } 120 121 @Override 122 public boolean isGetGeneratedKeysSimulated(){ 123 return false; 124 } 125 126 @Override 127 @Nullable 128 public String getSimpleQueryForGetGeneratedKey(String tableName, String keyColumnName) { 129 return null; 130 } 131 132 public void setGetGeneratedKeysSupported(boolean getGeneratedKeysSupported) { 133 this.getGeneratedKeysSupported = getGeneratedKeysSupported; 134 } 135 136 public void setGeneratedKeysColumnNameArraySupported(boolean generatedKeysColumnNameArraySupported) { 137 this.generatedKeysColumnNameArraySupported = generatedKeysColumnNameArraySupported; 138 } 139 140 @Override 141 public boolean isGeneratedKeysColumnNameArraySupported() { 142 return this.generatedKeysColumnNameArraySupported; 143 } 144 145 146 @Override 147 public void initializeWithMetaData(DatabaseMetaData databaseMetaData) throws SQLException { 148 try { 149 if (databaseMetaData.supportsGetGeneratedKeys()) { 150 logger.debug("GetGeneratedKeys is supported"); 151 setGetGeneratedKeysSupported(true); 152 } 153 else { 154 logger.debug("GetGeneratedKeys is not supported"); 155 setGetGeneratedKeysSupported(false); 156 } 157 } 158 catch (SQLException ex) { 159 if (logger.isWarnEnabled()) { 160 logger.warn("Error retrieving 'DatabaseMetaData.getGeneratedKeys': " + ex.getMessage()); 161 } 162 } 163 try { 164 String databaseProductName = databaseMetaData.getDatabaseProductName(); 165 if (this.productsNotSupportingGeneratedKeysColumnNameArray.contains(databaseProductName)) { 166 if (logger.isDebugEnabled()) { 167 logger.debug("GeneratedKeysColumnNameArray is not supported for " + databaseProductName); 168 } 169 setGeneratedKeysColumnNameArraySupported(false); 170 } 171 else { 172 if (isGetGeneratedKeysSupported()) { 173 if (logger.isDebugEnabled()) { 174 logger.debug("GeneratedKeysColumnNameArray is supported for " + databaseProductName); 175 } 176 setGeneratedKeysColumnNameArraySupported(true); 177 } 178 else { 179 setGeneratedKeysColumnNameArraySupported(false); 180 } 181 } 182 } 183 catch (SQLException ex) { 184 if (logger.isWarnEnabled()) { 185 logger.warn("Error retrieving 'DatabaseMetaData.getDatabaseProductName': " + ex.getMessage()); 186 } 187 } 188 189 try { 190 this.databaseVersion = databaseMetaData.getDatabaseProductVersion(); 191 } 192 catch (SQLException ex) { 193 if (logger.isWarnEnabled()) { 194 logger.warn("Error retrieving 'DatabaseMetaData.getDatabaseProductVersion': " + ex.getMessage()); 195 } 196 } 197 198 try { 199 setStoresUpperCaseIdentifiers(databaseMetaData.storesUpperCaseIdentifiers()); 200 } 201 catch (SQLException ex) { 202 if (logger.isWarnEnabled()) { 203 logger.warn("Error retrieving 'DatabaseMetaData.storesUpperCaseIdentifiers': " + ex.getMessage()); 204 } 205 } 206 207 try { 208 setStoresLowerCaseIdentifiers(databaseMetaData.storesLowerCaseIdentifiers()); 209 } 210 catch (SQLException ex) { 211 if (logger.isWarnEnabled()) { 212 logger.warn("Error retrieving 'DatabaseMetaData.storesLowerCaseIdentifiers': " + ex.getMessage()); 213 } 214 } 215 } 216 217 @Override 218 public void initializeWithTableColumnMetaData(DatabaseMetaData databaseMetaData, @Nullable String catalogName, 219 @Nullable String schemaName, @Nullable String tableName) throws SQLException { 220 221 this.tableColumnMetaDataUsed = true; 222 locateTableAndProcessMetaData(databaseMetaData, catalogName, schemaName, tableName); 223 } 224 225 @Override 226 @Nullable 227 public String tableNameToUse(@Nullable String tableName) { 228 if (tableName == null) { 229 return null; 230 } 231 else if (isStoresUpperCaseIdentifiers()) { 232 return tableName.toUpperCase(); 233 } 234 else if (isStoresLowerCaseIdentifiers()) { 235 return tableName.toLowerCase(); 236 } 237 else { 238 return tableName; 239 } 240 } 241 242 @Override 243 @Nullable 244 public String catalogNameToUse(@Nullable String catalogName) { 245 if (catalogName == null) { 246 return null; 247 } 248 else if (isStoresUpperCaseIdentifiers()) { 249 return catalogName.toUpperCase(); 250 } 251 else if (isStoresLowerCaseIdentifiers()) { 252 return catalogName.toLowerCase(); 253 } 254 else { 255 return catalogName; 256 } 257 } 258 259 @Override 260 @Nullable 261 public String schemaNameToUse(@Nullable String schemaName) { 262 if (schemaName == null) { 263 return null; 264 } 265 else if (isStoresUpperCaseIdentifiers()) { 266 return schemaName.toUpperCase(); 267 } 268 else if (isStoresLowerCaseIdentifiers()) { 269 return schemaName.toLowerCase(); 270 } 271 else { 272 return schemaName; 273 } 274 } 275 276 @Override 277 @Nullable 278 public String metaDataCatalogNameToUse(@Nullable String catalogName) { 279 return catalogNameToUse(catalogName); 280 } 281 282 @Override 283 @Nullable 284 public String metaDataSchemaNameToUse(@Nullable String schemaName) { 285 if (schemaName == null) { 286 return schemaNameToUse(getDefaultSchema()); 287 } 288 return schemaNameToUse(schemaName); 289 } 290 291 /** 292 * Provide access to default schema for subclasses. 293 */ 294 @Nullable 295 protected String getDefaultSchema() { 296 return this.userName; 297 } 298 299 /** 300 * Provide access to version info for subclasses. 301 */ 302 @Nullable 303 protected String getDatabaseVersion() { 304 return this.databaseVersion; 305 } 306 307 /** 308 * Method supporting the meta-data processing for a table. 309 */ 310 private void locateTableAndProcessMetaData(DatabaseMetaData databaseMetaData, 311 @Nullable String catalogName, @Nullable String schemaName, @Nullable String tableName) { 312 313 Map<String, TableMetaData> tableMeta = new HashMap<>(); 314 ResultSet tables = null; 315 try { 316 tables = databaseMetaData.getTables( 317 catalogNameToUse(catalogName), schemaNameToUse(schemaName), tableNameToUse(tableName), null); 318 while (tables != null && tables.next()) { 319 TableMetaData tmd = new TableMetaData(); 320 tmd.setCatalogName(tables.getString("TABLE_CAT")); 321 tmd.setSchemaName(tables.getString("TABLE_SCHEM")); 322 tmd.setTableName(tables.getString("TABLE_NAME")); 323 if (tmd.getSchemaName() == null) { 324 tableMeta.put(this.userName != null ? this.userName.toUpperCase() : "", tmd); 325 } 326 else { 327 tableMeta.put(tmd.getSchemaName().toUpperCase(), tmd); 328 } 329 } 330 } 331 catch (SQLException ex) { 332 if (logger.isWarnEnabled()) { 333 logger.warn("Error while accessing table meta-data results: " + ex.getMessage()); 334 } 335 } 336 finally { 337 JdbcUtils.closeResultSet(tables); 338 } 339 340 if (tableMeta.isEmpty()) { 341 if (logger.isInfoEnabled()) { 342 logger.info("Unable to locate table meta-data for '" + tableName + "': column names must be provided"); 343 } 344 } 345 else { 346 processTableColumns(databaseMetaData, findTableMetaData(schemaName, tableName, tableMeta)); 347 } 348 } 349 350 private TableMetaData findTableMetaData(@Nullable String schemaName, @Nullable String tableName, 351 Map<String, TableMetaData> tableMeta) { 352 353 if (schemaName != null) { 354 TableMetaData tmd = tableMeta.get(schemaName.toUpperCase()); 355 if (tmd == null) { 356 throw new DataAccessResourceFailureException("Unable to locate table meta-data for '" + 357 tableName + "' in the '" + schemaName + "' schema"); 358 } 359 return tmd; 360 } 361 else if (tableMeta.size() == 1) { 362 return tableMeta.values().iterator().next(); 363 } 364 else { 365 TableMetaData tmd = tableMeta.get(getDefaultSchema()); 366 if (tmd == null) { 367 tmd = tableMeta.get(this.userName != null ? this.userName.toUpperCase() : ""); 368 } 369 if (tmd == null) { 370 tmd = tableMeta.get("PUBLIC"); 371 } 372 if (tmd == null) { 373 tmd = tableMeta.get("DBO"); 374 } 375 if (tmd == null) { 376 throw new DataAccessResourceFailureException( 377 "Unable to locate table meta-data for '" + tableName + "' in the default schema"); 378 } 379 return tmd; 380 } 381 } 382 383 /** 384 * Method supporting the meta-data processing for a table's columns. 385 */ 386 private void processTableColumns(DatabaseMetaData databaseMetaData, TableMetaData tmd) { 387 ResultSet tableColumns = null; 388 String metaDataCatalogName = metaDataCatalogNameToUse(tmd.getCatalogName()); 389 String metaDataSchemaName = metaDataSchemaNameToUse(tmd.getSchemaName()); 390 String metaDataTableName = tableNameToUse(tmd.getTableName()); 391 if (logger.isDebugEnabled()) { 392 logger.debug("Retrieving meta-data for " + metaDataCatalogName + '/' + 393 metaDataSchemaName + '/' + metaDataTableName); 394 } 395 try { 396 tableColumns = databaseMetaData.getColumns( 397 metaDataCatalogName, metaDataSchemaName, metaDataTableName, null); 398 while (tableColumns.next()) { 399 String columnName = tableColumns.getString("COLUMN_NAME"); 400 int dataType = tableColumns.getInt("DATA_TYPE"); 401 if (dataType == Types.DECIMAL) { 402 String typeName = tableColumns.getString("TYPE_NAME"); 403 int decimalDigits = tableColumns.getInt("DECIMAL_DIGITS"); 404 // Override a DECIMAL data type for no-decimal numerics 405 // (this is for better Oracle support where there have been issues 406 // using DECIMAL for certain inserts (see SPR-6912)) 407 if ("NUMBER".equals(typeName) && decimalDigits == 0) { 408 dataType = Types.NUMERIC; 409 if (logger.isDebugEnabled()) { 410 logger.debug("Overriding meta-data: " + columnName + " now NUMERIC instead of DECIMAL"); 411 } 412 } 413 } 414 boolean nullable = tableColumns.getBoolean("NULLABLE"); 415 TableParameterMetaData meta = new TableParameterMetaData(columnName, dataType, nullable); 416 this.tableParameterMetaData.add(meta); 417 if (logger.isDebugEnabled()) { 418 logger.debug("Retrieved meta-data: '" + meta.getParameterName() + "', sqlType=" + 419 meta.getSqlType() + ", nullable=" + meta.isNullable()); 420 } 421 } 422 } 423 catch (SQLException ex) { 424 if (logger.isWarnEnabled()) { 425 logger.warn("Error while retrieving meta-data for table columns: " + ex.getMessage()); 426 } 427 } 428 finally { 429 JdbcUtils.closeResultSet(tableColumns); 430 } 431 } 432 433 434 /** 435 * Inner class representing table meta-data. 436 */ 437 private static class TableMetaData { 438 439 @Nullable 440 private String catalogName; 441 442 @Nullable 443 private String schemaName; 444 445 @Nullable 446 private String tableName; 447 448 public void setCatalogName(String catalogName) { 449 this.catalogName = catalogName; 450 } 451 452 @Nullable 453 public String getCatalogName() { 454 return this.catalogName; 455 } 456 457 public void setSchemaName(String schemaName) { 458 this.schemaName = schemaName; 459 } 460 461 @Nullable 462 public String getSchemaName() { 463 return this.schemaName; 464 } 465 466 public void setTableName(String tableName) { 467 this.tableName = tableName; 468 } 469 470 @Nullable 471 public String getTableName() { 472 return this.tableName; 473 } 474 } 475 476}