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}