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.List;
025
026import org.apache.commons.logging.Log;
027import org.apache.commons.logging.LogFactory;
028
029import org.springframework.dao.InvalidDataAccessApiUsageException;
030import org.springframework.jdbc.core.SqlInOutParameter;
031import org.springframework.jdbc.core.SqlOutParameter;
032import org.springframework.jdbc.core.SqlParameter;
033import org.springframework.lang.Nullable;
034import org.springframework.util.StringUtils;
035
036/**
037 * A generic implementation of the {@link CallMetaDataProvider} interface.
038 * This class can be extended to provide database specific behavior.
039 *
040 * @author Thomas Risberg
041 * @author Juergen Hoeller
042 * @since 2.5
043 */
044public class GenericCallMetaDataProvider implements CallMetaDataProvider {
045
046        /** Logger available to subclasses. */
047        protected static final Log logger = LogFactory.getLog(CallMetaDataProvider.class);
048
049
050        private final String userName;
051
052        private boolean supportsCatalogsInProcedureCalls = true;
053
054        private boolean supportsSchemasInProcedureCalls = true;
055
056        private boolean storesUpperCaseIdentifiers = true;
057
058        private boolean storesLowerCaseIdentifiers = false;
059
060        private boolean procedureColumnMetaDataUsed = false;
061
062        private final List<CallParameterMetaData> callParameterMetaData = new ArrayList<>();
063
064
065        /**
066         * Constructor used to initialize with provided database meta-data.
067         * @param databaseMetaData meta-data to be used
068         */
069        protected GenericCallMetaDataProvider(DatabaseMetaData databaseMetaData) throws SQLException {
070                this.userName = databaseMetaData.getUserName();
071        }
072
073
074        @Override
075        public void initializeWithMetaData(DatabaseMetaData databaseMetaData) throws SQLException {
076                try {
077                        setSupportsCatalogsInProcedureCalls(databaseMetaData.supportsCatalogsInProcedureCalls());
078                }
079                catch (SQLException ex) {
080                        if (logger.isWarnEnabled()) {
081                                logger.warn("Error retrieving 'DatabaseMetaData.supportsCatalogsInProcedureCalls': " + ex.getMessage());
082                        }
083                }
084                try {
085                        setSupportsSchemasInProcedureCalls(databaseMetaData.supportsSchemasInProcedureCalls());
086                }
087                catch (SQLException ex) {
088                        if (logger.isWarnEnabled()) {
089                                logger.warn("Error retrieving 'DatabaseMetaData.supportsSchemasInProcedureCalls': " + ex.getMessage());
090                        }
091                }
092                try {
093                        setStoresUpperCaseIdentifiers(databaseMetaData.storesUpperCaseIdentifiers());
094                }
095                catch (SQLException ex) {
096                        if (logger.isWarnEnabled()) {
097                                logger.warn("Error retrieving 'DatabaseMetaData.storesUpperCaseIdentifiers': " + ex.getMessage());
098                        }
099                }
100                try {
101                        setStoresLowerCaseIdentifiers(databaseMetaData.storesLowerCaseIdentifiers());
102                }
103                catch (SQLException ex) {
104                        if (logger.isWarnEnabled()) {
105                                logger.warn("Error retrieving 'DatabaseMetaData.storesLowerCaseIdentifiers': " + ex.getMessage());
106                        }
107                }
108        }
109
110        @Override
111        public void initializeWithProcedureColumnMetaData(DatabaseMetaData databaseMetaData, @Nullable String catalogName,
112                        @Nullable String schemaName, @Nullable String procedureName) throws SQLException {
113
114                this.procedureColumnMetaDataUsed = true;
115                processProcedureColumns(databaseMetaData, catalogName, schemaName,  procedureName);
116        }
117
118        @Override
119        public List<CallParameterMetaData> getCallParameterMetaData() {
120                return this.callParameterMetaData;
121        }
122
123        @Override
124        @Nullable
125        public String procedureNameToUse(@Nullable String procedureName) {
126                if (procedureName == null) {
127                        return null;
128                }
129                else if (isStoresUpperCaseIdentifiers()) {
130                        return procedureName.toUpperCase();
131                }
132                else if (isStoresLowerCaseIdentifiers()) {
133                        return procedureName.toLowerCase();
134                }
135                else {
136                        return procedureName;
137                }
138        }
139
140        @Override
141        @Nullable
142        public String catalogNameToUse(@Nullable String catalogName) {
143                if (catalogName == null) {
144                        return null;
145                }
146                else if (isStoresUpperCaseIdentifiers()) {
147                        return catalogName.toUpperCase();
148                }
149                else if (isStoresLowerCaseIdentifiers()) {
150                        return catalogName.toLowerCase();
151                }
152                else {
153                        return catalogName;
154                }
155        }
156
157        @Override
158        @Nullable
159        public String schemaNameToUse(@Nullable String schemaName) {
160                if (schemaName == null) {
161                        return null;
162                }
163                else if (isStoresUpperCaseIdentifiers()) {
164                        return schemaName.toUpperCase();
165                }
166                else if (isStoresLowerCaseIdentifiers()) {
167                        return schemaName.toLowerCase();
168                }
169                else {
170                        return schemaName;
171                }
172        }
173
174        @Override
175        @Nullable
176        public String metaDataCatalogNameToUse(@Nullable String catalogName) {
177                if (isSupportsCatalogsInProcedureCalls()) {
178                        return catalogNameToUse(catalogName);
179                }
180                else {
181                        return null;
182                }
183        }
184
185        @Override
186        @Nullable
187        public String metaDataSchemaNameToUse(@Nullable String schemaName) {
188                if (isSupportsSchemasInProcedureCalls()) {
189                        return schemaNameToUse(schemaName);
190                }
191                else {
192                        return null;
193                }
194        }
195
196        @Override
197        @Nullable
198        public String parameterNameToUse(@Nullable String parameterName) {
199                if (parameterName == null) {
200                        return null;
201                }
202                else if (isStoresUpperCaseIdentifiers()) {
203                        return parameterName.toUpperCase();
204                }
205                else if (isStoresLowerCaseIdentifiers()) {
206                        return parameterName.toLowerCase();
207                }
208                else {
209                        return parameterName;
210                }
211        }
212
213        @Override
214        public boolean byPassReturnParameter(String parameterName) {
215                return false;
216        }
217
218        @Override
219        public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
220                return new SqlOutParameter(parameterName, meta.getSqlType());
221        }
222
223        @Override
224        public SqlParameter createDefaultInOutParameter(String parameterName, CallParameterMetaData meta) {
225                return new SqlInOutParameter(parameterName, meta.getSqlType());
226        }
227
228        @Override
229        public SqlParameter createDefaultInParameter(String parameterName, CallParameterMetaData meta) {
230                return new SqlParameter(parameterName, meta.getSqlType());
231        }
232
233        @Override
234        public String getUserName() {
235                return this.userName;
236        }
237
238        @Override
239        public boolean isReturnResultSetSupported() {
240                return true;
241        }
242
243        @Override
244        public boolean isRefCursorSupported() {
245                return false;
246        }
247
248        @Override
249        public int getRefCursorSqlType() {
250                return Types.OTHER;
251        }
252
253        @Override
254        public boolean isProcedureColumnMetaDataUsed() {
255                return this.procedureColumnMetaDataUsed;
256        }
257
258
259        /**
260         * Specify whether the database supports the use of catalog name in procedure calls.
261         */
262        protected void setSupportsCatalogsInProcedureCalls(boolean supportsCatalogsInProcedureCalls) {
263                this.supportsCatalogsInProcedureCalls = supportsCatalogsInProcedureCalls;
264        }
265
266        /**
267         * Does the database support the use of catalog name in procedure calls?
268         */
269        @Override
270        public boolean isSupportsCatalogsInProcedureCalls() {
271                return this.supportsCatalogsInProcedureCalls;
272        }
273
274        /**
275         * Specify whether the database supports the use of schema name in procedure calls.
276         */
277        protected void setSupportsSchemasInProcedureCalls(boolean supportsSchemasInProcedureCalls) {
278                this.supportsSchemasInProcedureCalls = supportsSchemasInProcedureCalls;
279        }
280
281        /**
282         * Does the database support the use of schema name in procedure calls?
283         */
284        @Override
285        public boolean isSupportsSchemasInProcedureCalls() {
286                return this.supportsSchemasInProcedureCalls;
287        }
288
289        /**
290         * Specify whether the database uses upper case for identifiers.
291         */
292        protected void setStoresUpperCaseIdentifiers(boolean storesUpperCaseIdentifiers) {
293                this.storesUpperCaseIdentifiers = storesUpperCaseIdentifiers;
294        }
295
296        /**
297         * Does the database use upper case for identifiers?
298         */
299        protected boolean isStoresUpperCaseIdentifiers() {
300                return this.storesUpperCaseIdentifiers;
301        }
302
303        /**
304         * Specify whether the database uses lower case for identifiers.
305         */
306        protected void setStoresLowerCaseIdentifiers(boolean storesLowerCaseIdentifiers) {
307                this.storesLowerCaseIdentifiers = storesLowerCaseIdentifiers;
308        }
309
310        /**
311         * Does the database use lower case for identifiers?
312         */
313        protected boolean isStoresLowerCaseIdentifiers() {
314                return this.storesLowerCaseIdentifiers;
315        }
316
317
318        /**
319         * Process the procedure column meta-data.
320         */
321        private void processProcedureColumns(DatabaseMetaData databaseMetaData,
322                        @Nullable String catalogName, @Nullable String schemaName, @Nullable String procedureName) {
323
324                String metaDataCatalogName = metaDataCatalogNameToUse(catalogName);
325                String metaDataSchemaName = metaDataSchemaNameToUse(schemaName);
326                String metaDataProcedureName = procedureNameToUse(procedureName);
327                if (logger.isDebugEnabled()) {
328                        logger.debug("Retrieving meta-data for " + metaDataCatalogName + '/' +
329                                        metaDataSchemaName + '/' + metaDataProcedureName);
330                }
331
332                try {
333                        List<String> found = new ArrayList<>();
334                        boolean function = false;
335
336                        try (ResultSet procedures = databaseMetaData.getProcedures(
337                                        metaDataCatalogName, metaDataSchemaName, metaDataProcedureName)) {
338                                while (procedures.next()) {
339                                        found.add(procedures.getString("PROCEDURE_CAT") + '.' + procedures.getString("PROCEDURE_SCHEM") +
340                                                        '.' + procedures.getString("PROCEDURE_NAME"));
341                                }
342                        }
343
344                        if (found.isEmpty()) {
345                                // Functions not exposed as procedures anymore on PostgreSQL driver 42.2.11
346                                try (ResultSet functions = databaseMetaData.getFunctions(
347                                                metaDataCatalogName, metaDataSchemaName, metaDataProcedureName)) {
348                                        while (functions.next()) {
349                                                found.add(functions.getString("FUNCTION_CAT") + '.' + functions.getString("FUNCTION_SCHEM") +
350                                                                '.' + functions.getString("FUNCTION_NAME"));
351                                                function = true;
352                                        }
353                                }
354                        }
355
356                        if (found.size() > 1) {
357                                throw new InvalidDataAccessApiUsageException(
358                                                "Unable to determine the correct call signature - multiple signatures for '" +
359                                                metaDataProcedureName + "': found " + found + " " + (function ? "functions" : "procedures"));
360                        }
361                        else if (found.isEmpty()) {
362                                if (metaDataProcedureName != null && metaDataProcedureName.contains(".") &&
363                                                !StringUtils.hasText(metaDataCatalogName)) {
364                                        String packageName = metaDataProcedureName.substring(0, metaDataProcedureName.indexOf('.'));
365                                        throw new InvalidDataAccessApiUsageException(
366                                                        "Unable to determine the correct call signature for '" + metaDataProcedureName +
367                                                        "' - package name should be specified separately using '.withCatalogName(\"" +
368                                                        packageName + "\")'");
369                                }
370                                else if ("Oracle".equals(databaseMetaData.getDatabaseProductName())) {
371                                        if (logger.isDebugEnabled()) {
372                                                logger.debug("Oracle JDBC driver did not return procedure/function/signature for '" +
373                                                                metaDataProcedureName + "' - assuming a non-exposed synonym");
374                                        }
375                                }
376                                else {
377                                        throw new InvalidDataAccessApiUsageException(
378                                                        "Unable to determine the correct call signature - no " +
379                                                        "procedure/function/signature for '" + metaDataProcedureName + "'");
380                                }
381                        }
382
383                        if (logger.isDebugEnabled()) {
384                                logger.debug("Retrieving column meta-data for " + (function ? "function" : "procedure") + ' ' +
385                                                metaDataCatalogName + '/' + metaDataSchemaName + '/' + metaDataProcedureName);
386                        }
387                        try (ResultSet columns = function ?
388                                        databaseMetaData.getFunctionColumns(metaDataCatalogName, metaDataSchemaName, metaDataProcedureName, null) :
389                                        databaseMetaData.getProcedureColumns(metaDataCatalogName, metaDataSchemaName, metaDataProcedureName, null)) {
390                                while (columns.next()) {
391                                        String columnName = columns.getString("COLUMN_NAME");
392                                        int columnType = columns.getInt("COLUMN_TYPE");
393                                        if (columnName == null && isInOrOutColumn(columnType, function)) {
394                                                if (logger.isDebugEnabled()) {
395                                                        logger.debug("Skipping meta-data for: " + columnType + " " + columns.getInt("DATA_TYPE") +
396                                                                        " " + columns.getString("TYPE_NAME") + " " + columns.getInt("NULLABLE") +
397                                                                        " (probably a member of a collection)");
398                                                }
399                                        }
400                                        else {
401                                                int nullable = (function ? DatabaseMetaData.functionNullable : DatabaseMetaData.procedureNullable);
402                                                CallParameterMetaData meta = new CallParameterMetaData(function, columnName, columnType,
403                                                                columns.getInt("DATA_TYPE"), columns.getString("TYPE_NAME"),
404                                                                columns.getInt("NULLABLE") == nullable);
405                                                this.callParameterMetaData.add(meta);
406                                                if (logger.isDebugEnabled()) {
407                                                        logger.debug("Retrieved meta-data: " + meta.getParameterName() + " " +
408                                                                        meta.getParameterType() + " " + meta.getSqlType() + " " +
409                                                                        meta.getTypeName() + " " + meta.isNullable());
410                                                }
411                                        }
412                                }
413                        }
414                }
415                catch (SQLException ex) {
416                        if (logger.isWarnEnabled()) {
417                                logger.warn("Error while retrieving meta-data for procedure columns: " + ex);
418                        }
419                }
420        }
421
422        private static boolean isInOrOutColumn(int columnType, boolean function) {
423                if (function) {
424                        return (columnType == DatabaseMetaData.functionColumnIn ||
425                                        columnType == DatabaseMetaData.functionColumnInOut ||
426                                        columnType == DatabaseMetaData.functionColumnOut);
427                }
428                else {
429                        return (columnType == DatabaseMetaData.procedureColumnIn ||
430                                        columnType == DatabaseMetaData.procedureColumnInOut ||
431                                        columnType == DatabaseMetaData.procedureColumnOut);
432                }
433        }
434
435}