001/*
002 * Copyright 2002-2018 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.jdbc.support.nativejdbc.NativeJdbcExtractor;
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        private String databaseVersion;
054
055        /** the name of the user currently connected */
056        private String userName;
057
058        /** indicates whether the identifiers are uppercased */
059        private boolean storesUpperCaseIdentifiers = true;
060
061        /** indicates whether the identifiers are lowercased */
062        private boolean storesLowerCaseIdentifiers = false;
063
064        /** indicates whether generated keys retrieval is supported */
065        private boolean getGeneratedKeysSupported = true;
066
067        /** indicates whether the use of a String[] for generated keys is supported */
068        private boolean generatedKeysColumnNameArraySupported = true;
069
070        /** database products we know not supporting the use of a String[] for generated keys */
071        private List<String> productsNotSupportingGeneratedKeysColumnNameArray =
072                        Arrays.asList("Apache Derby", "HSQL Database Engine");
073
074        /** Collection of TableParameterMetaData objects */
075        private List<TableParameterMetaData> tableParameterMetaData = new ArrayList<TableParameterMetaData>();
076
077        /** NativeJdbcExtractor that can be used to retrieve the native connection */
078        private NativeJdbcExtractor nativeJdbcExtractor;
079
080
081        /**
082         * Constructor used to initialize with provided database meta-data.
083         * @param databaseMetaData meta-data to be used
084         */
085        protected GenericTableMetaDataProvider(DatabaseMetaData databaseMetaData) throws SQLException {
086                this.userName = databaseMetaData.getUserName();
087        }
088
089
090        public void setStoresUpperCaseIdentifiers(boolean storesUpperCaseIdentifiers) {
091                this.storesUpperCaseIdentifiers = storesUpperCaseIdentifiers;
092        }
093
094        public boolean isStoresUpperCaseIdentifiers() {
095                return this.storesUpperCaseIdentifiers;
096        }
097
098        public void setStoresLowerCaseIdentifiers(boolean storesLowerCaseIdentifiers) {
099                this.storesLowerCaseIdentifiers = storesLowerCaseIdentifiers;
100        }
101
102        public boolean isStoresLowerCaseIdentifiers() {
103                return this.storesLowerCaseIdentifiers;
104        }
105
106
107        @Override
108        public boolean isTableColumnMetaDataUsed() {
109                return this.tableColumnMetaDataUsed;
110        }
111
112        @Override
113        public List<TableParameterMetaData> getTableParameterMetaData() {
114                return this.tableParameterMetaData;
115        }
116
117        @Override
118        public boolean isGetGeneratedKeysSupported() {
119                return this.getGeneratedKeysSupported;
120        }
121
122        @Override
123        public boolean isGetGeneratedKeysSimulated(){
124                return false;
125        }
126
127        @Override
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        @Override
146        public void setNativeJdbcExtractor(NativeJdbcExtractor nativeJdbcExtractor) {
147                this.nativeJdbcExtractor = nativeJdbcExtractor;
148        }
149
150        protected NativeJdbcExtractor getNativeJdbcExtractor() {
151                return this.nativeJdbcExtractor;
152        }
153
154
155        @Override
156        public void initializeWithMetaData(DatabaseMetaData databaseMetaData) throws SQLException {
157                try {
158                        if (databaseMetaData.supportsGetGeneratedKeys()) {
159                                logger.debug("GetGeneratedKeys is supported");
160                                setGetGeneratedKeysSupported(true);
161                        }
162                        else {
163                                logger.debug("GetGeneratedKeys is not supported");
164                                setGetGeneratedKeysSupported(false);
165                        }
166                }
167                catch (SQLException ex) {
168                        if (logger.isWarnEnabled()) {
169                                logger.warn("Error retrieving 'DatabaseMetaData.getGeneratedKeys': " + ex.getMessage());
170                        }
171                }
172                try {
173                        String databaseProductName = databaseMetaData.getDatabaseProductName();
174                        if (this.productsNotSupportingGeneratedKeysColumnNameArray.contains(databaseProductName)) {
175                                if (logger.isDebugEnabled()) {
176                                        logger.debug("GeneratedKeysColumnNameArray is not supported for " + databaseProductName);
177                                }
178                                setGeneratedKeysColumnNameArraySupported(false);
179                        }
180                        else {
181                                if (isGetGeneratedKeysSupported()) {
182                                        if (logger.isDebugEnabled()) {
183                                                logger.debug("GeneratedKeysColumnNameArray is supported for " + databaseProductName);
184                                        }
185                                        setGeneratedKeysColumnNameArraySupported(true);
186                                }
187                                else {
188                                        setGeneratedKeysColumnNameArraySupported(false);
189                                }
190                        }
191                }
192                catch (SQLException ex) {
193                        if (logger.isWarnEnabled()) {
194                                logger.warn("Error retrieving 'DatabaseMetaData.getDatabaseProductName': " + ex.getMessage());
195                        }
196                }
197
198                try {
199                        this.databaseVersion = databaseMetaData.getDatabaseProductVersion();
200                }
201                catch (SQLException ex) {
202                        if (logger.isWarnEnabled()) {
203                                logger.warn("Error retrieving 'DatabaseMetaData.getDatabaseProductVersion': " + ex.getMessage());
204                        }
205                }
206
207                try {
208                        setStoresUpperCaseIdentifiers(databaseMetaData.storesUpperCaseIdentifiers());
209                }
210                catch (SQLException ex) {
211                        if (logger.isWarnEnabled()) {
212                                logger.warn("Error retrieving 'DatabaseMetaData.storesUpperCaseIdentifiers': " + ex.getMessage());
213                        }
214                }
215
216                try {
217                        setStoresLowerCaseIdentifiers(databaseMetaData.storesLowerCaseIdentifiers());
218                }
219                catch (SQLException ex) {
220                        if (logger.isWarnEnabled()) {
221                                logger.warn("Error retrieving 'DatabaseMetaData.storesLowerCaseIdentifiers': " + ex.getMessage());
222                        }
223                }
224        }
225
226        @Override
227        public void initializeWithTableColumnMetaData(DatabaseMetaData databaseMetaData, String catalogName,
228                        String schemaName, String tableName) throws SQLException {
229
230                this.tableColumnMetaDataUsed = true;
231                locateTableAndProcessMetaData(databaseMetaData, catalogName, schemaName, tableName);
232        }
233
234        @Override
235        public String tableNameToUse(String tableName) {
236                if (tableName == null) {
237                        return null;
238                }
239                else if (isStoresUpperCaseIdentifiers()) {
240                        return tableName.toUpperCase();
241                }
242                else if (isStoresLowerCaseIdentifiers()) {
243                        return tableName.toLowerCase();
244                }
245                else {
246                        return tableName;
247                }
248        }
249
250        @Override
251        public String catalogNameToUse(String catalogName) {
252                if (catalogName == null) {
253                        return null;
254                }
255                else if (isStoresUpperCaseIdentifiers()) {
256                        return catalogName.toUpperCase();
257                }
258                else if (isStoresLowerCaseIdentifiers()) {
259                        return catalogName.toLowerCase();
260                }
261                else {
262                        return catalogName;
263                }
264        }
265
266        @Override
267        public String schemaNameToUse(String schemaName) {
268                if (schemaName == null) {
269                        return null;
270                }
271                else if (isStoresUpperCaseIdentifiers()) {
272                        return schemaName.toUpperCase();
273                }
274                else if (isStoresLowerCaseIdentifiers()) {
275                        return schemaName.toLowerCase();
276                }
277                else {
278                        return schemaName;
279                }
280        }
281
282        @Override
283        public String metaDataCatalogNameToUse(String catalogName) {
284                return catalogNameToUse(catalogName);
285        }
286
287        @Override
288        public String metaDataSchemaNameToUse(String schemaName) {
289                if (schemaName == null) {
290                        return schemaNameToUse(getDefaultSchema());
291                }
292                return schemaNameToUse(schemaName);
293        }
294
295        /**
296         * Provide access to default schema for subclasses.
297         */
298        protected String getDefaultSchema() {
299                return this.userName;
300        }
301
302        /**
303         * Provide access to version info for subclasses.
304         */
305        protected String getDatabaseVersion() {
306                return this.databaseVersion;
307        }
308
309        /**
310         * Method supporting the meta-data processing for a table.
311         */
312        private void locateTableAndProcessMetaData(
313                        DatabaseMetaData databaseMetaData, String catalogName, String schemaName, String tableName) {
314
315                Map<String, TableMetaData> tableMeta = new HashMap<String, TableMetaData>();
316                ResultSet tables = null;
317                try {
318                        tables = databaseMetaData.getTables(
319                                        catalogNameToUse(catalogName), schemaNameToUse(schemaName), tableNameToUse(tableName), null);
320                        while (tables != null && tables.next()) {
321                                TableMetaData tmd = new TableMetaData();
322                                tmd.setCatalogName(tables.getString("TABLE_CAT"));
323                                tmd.setSchemaName(tables.getString("TABLE_SCHEM"));
324                                tmd.setTableName(tables.getString("TABLE_NAME"));
325                                if (tmd.getSchemaName() == null) {
326                                        tableMeta.put(this.userName != null ? this.userName.toUpperCase() : "", tmd);
327                                }
328                                else {
329                                        tableMeta.put(tmd.getSchemaName().toUpperCase(), tmd);
330                                }
331                        }
332                }
333                catch (SQLException ex) {
334                        if (logger.isWarnEnabled()) {
335                                logger.warn("Error while accessing table meta-data results: " + ex.getMessage());
336                        }
337                }
338                finally {
339                        JdbcUtils.closeResultSet(tables);
340                }
341
342                if (tableMeta.isEmpty()) {
343                        if (logger.isWarnEnabled()) {
344                                logger.warn("Unable to locate table meta-data for '" + tableName + "': column names must be provided");
345                        }
346                }
347                else {
348                        processTableColumns(databaseMetaData, findTableMetaData(schemaName, tableName, tableMeta));
349                }
350        }
351
352        private TableMetaData findTableMetaData(String schemaName, String tableName, Map<String, TableMetaData> tableMeta) {
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() + " " +
419                                                        meta.getSqlType() + " " + 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                private String catalogName;
440
441                private String schemaName;
442
443                private String tableName;
444
445                public void setCatalogName(String catalogName) {
446                        this.catalogName = catalogName;
447                }
448
449                public String getCatalogName() {
450                        return this.catalogName;
451                }
452
453                public void setSchemaName(String schemaName) {
454                        this.schemaName = schemaName;
455                }
456
457                public String getSchemaName() {
458                        return this.schemaName;
459                }
460
461                public void setTableName(String tableName) {
462                        this.tableName = tableName;
463                }
464
465                public String getTableName() {
466                        return this.tableName;
467                }
468        }
469
470}