/
JdbcUtils.java
509 lines (478 loc) · 18.3 KB
/
JdbcUtils.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
/*
* Copyright 2002-2017 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.jdbc.support;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.sql.Types;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.lang.UsesJava7;
import org.springframework.util.ClassUtils;
import org.springframework.util.NumberUtils;
/**
* Generic utility methods for working with JDBC. Mainly for internal use
* within the framework, but also useful for custom JDBC access code.
*
* @author Thomas Risberg
* @author Juergen Hoeller
*/
public abstract class JdbcUtils {
/**
* Constant that indicates an unknown (or unspecified) SQL type.
* @see java.sql.Types
*/
public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;
// Check for JDBC 4.1 getObject(int, Class) method - available on JDK 7 and higher
private static final boolean getObjectWithTypeAvailable =
ClassUtils.hasMethod(ResultSet.class, "getObject", int.class, Class.class);
private static final Log logger = LogFactory.getLog(JdbcUtils.class);
/**
* Close the given JDBC Connection and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param con the JDBC Connection to close (may be {@code null})
*/
public static void closeConnection(Connection con) {
if (con != null) {
try {
con.close();
}
catch (SQLException ex) {
logger.debug("Could not close JDBC Connection", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
}
/**
* Close the given JDBC Statement and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param stmt the JDBC Statement to close (may be {@code null})
*/
public static void closeStatement(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException ex) {
logger.trace("Could not close JDBC Statement", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.trace("Unexpected exception on closing JDBC Statement", ex);
}
}
}
/**
* Close the given JDBC ResultSet and ignore any thrown exception.
* This is useful for typical finally blocks in manual JDBC code.
* @param rs the JDBC ResultSet to close (may be {@code null})
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
}
catch (SQLException ex) {
logger.trace("Could not close JDBC ResultSet", ex);
}
catch (Throwable ex) {
// We don't trust the JDBC driver: It might throw RuntimeException or Error.
logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
}
}
}
/**
* Retrieve a JDBC column value from a ResultSet, using the specified value type.
* <p>Uses the specifically typed ResultSet accessor methods, falling back to
* {@link #getResultSetValue(java.sql.ResultSet, int)} for unknown types.
* <p>Note that the returned value may not be assignable to the specified
* required type, in case of an unknown type. Calling code needs to deal
* with this case appropriately, e.g. throwing a corresponding exception.
* @param rs is the ResultSet holding the data
* @param index is the column index
* @param requiredType the required value type (may be {@code null})
* @return the value object (possibly not of the specified required type,
* with further conversion steps necessary)
* @throws SQLException if thrown by the JDBC API
* @see #getResultSetValue(ResultSet, int)
*/
@UsesJava7 // guard optional use of JDBC 4.1 (safe with 1.6 due to getObjectWithTypeAvailable check)
public static Object getResultSetValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException {
if (requiredType == null) {
return getResultSetValue(rs, index);
}
Object value;
// Explicitly extract typed value, as far as possible.
if (String.class == requiredType) {
return rs.getString(index);
}
else if (boolean.class == requiredType || Boolean.class == requiredType) {
value = rs.getBoolean(index);
}
else if (byte.class == requiredType || Byte.class == requiredType) {
value = rs.getByte(index);
}
else if (short.class == requiredType || Short.class == requiredType) {
value = rs.getShort(index);
}
else if (int.class == requiredType || Integer.class == requiredType) {
value = rs.getInt(index);
}
else if (long.class == requiredType || Long.class == requiredType) {
value = rs.getLong(index);
}
else if (float.class == requiredType || Float.class == requiredType) {
value = rs.getFloat(index);
}
else if (double.class == requiredType || Double.class == requiredType ||
Number.class == requiredType) {
value = rs.getDouble(index);
}
else if (BigDecimal.class == requiredType) {
return rs.getBigDecimal(index);
}
else if (java.sql.Date.class == requiredType) {
return rs.getDate(index);
}
else if (java.sql.Time.class == requiredType) {
return rs.getTime(index);
}
else if (java.sql.Timestamp.class == requiredType || java.util.Date.class == requiredType) {
return rs.getTimestamp(index);
}
else if (byte[].class == requiredType) {
return rs.getBytes(index);
}
else if (Blob.class == requiredType) {
return rs.getBlob(index);
}
else if (Clob.class == requiredType) {
return rs.getClob(index);
}
else if (requiredType.isEnum()) {
// Enums can either be represented through a String or an enum index value:
// leave enum type conversion up to the caller (e.g. a ConversionService)
// but make sure that we return nothing other than a String or an Integer.
Object obj = rs.getObject(index);
if (obj instanceof String) {
return obj;
}
else if (obj instanceof Number) {
// Defensively convert any Number to an Integer (as needed by our
// ConversionService's IntegerToEnumConverterFactory) for use as index
return NumberUtils.convertNumberToTargetClass((Number) obj, Integer.class);
}
else {
// e.g. on Postgres: getObject returns a PGObject but we need a String
return rs.getString(index);
}
}
else {
// Some unknown type desired -> rely on getObject.
if (getObjectWithTypeAvailable) {
try {
return rs.getObject(index, requiredType);
}
catch (AbstractMethodError err) {
logger.debug("JDBC driver does not implement JDBC 4.1 'getObject(int, Class)' method", err);
}
catch (SQLFeatureNotSupportedException ex) {
logger.debug("JDBC driver does not support JDBC 4.1 'getObject(int, Class)' method", ex);
}
catch (SQLException ex) {
logger.debug("JDBC driver has limited support for JDBC 4.1 'getObject(int, Class)' method", ex);
}
}
// Corresponding SQL types for JSR-310 / Joda-Time types, left up
// to the caller to convert them (e.g. through a ConversionService).
String typeName = requiredType.getSimpleName();
if ("LocalDate".equals(typeName)) {
return rs.getDate(index);
}
else if ("LocalTime".equals(typeName)) {
return rs.getTime(index);
}
else if ("LocalDateTime".equals(typeName)) {
return rs.getTimestamp(index);
}
// Fall back to getObject without type specification, again
// left up to the caller to convert the value if necessary.
return getResultSetValue(rs, index);
}
// Perform was-null check if necessary (for results that the JDBC driver returns as primitives).
return (rs.wasNull() ? null : value);
}
/**
* Retrieve a JDBC column value from a ResultSet, using the most appropriate
* value type. The returned value should be a detached value object, not having
* any ties to the active ResultSet: in particular, it should not be a Blob or
* Clob object but rather a byte array or String representation, respectively.
* <p>Uses the {@code getObject(index)} method, but includes additional "hacks"
* to get around Oracle 10g returning a non-standard object for its TIMESTAMP
* datatype and a {@code java.sql.Date} for DATE columns leaving out the
* time portion: These columns will explicitly be extracted as standard
* {@code java.sql.Timestamp} object.
* @param rs is the ResultSet holding the data
* @param index is the column index
* @return the value object
* @throws SQLException if thrown by the JDBC API
* @see java.sql.Blob
* @see java.sql.Clob
* @see java.sql.Timestamp
*/
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
Object obj = rs.getObject(index);
String className = null;
if (obj != null) {
className = obj.getClass().getName();
}
if (obj instanceof Blob) {
Blob blob = (Blob) obj;
obj = blob.getBytes(1, (int) blob.length());
}
else if (obj instanceof Clob) {
Clob clob = (Clob) obj;
obj = clob.getSubString(1, (int) clob.length());
}
else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
obj = rs.getTimestamp(index);
}
else if (className != null && className.startsWith("oracle.sql.DATE")) {
String metaDataClassName = rs.getMetaData().getColumnClassName(index);
if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
obj = rs.getTimestamp(index);
}
else {
obj = rs.getDate(index);
}
}
else if (obj instanceof java.sql.Date) {
if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
obj = rs.getTimestamp(index);
}
}
return obj;
}
/**
* Extract database meta data via the given DatabaseMetaDataCallback.
* <p>This method will open a connection to the database and retrieve the database metadata.
* Since this method is called before the exception translation feature is configured for
* a datasource, this method can not rely on the SQLException translation functionality.
* <p>Any exceptions will be wrapped in a MetaDataAccessException. This is a checked exception
* and any calling code should catch and handle this exception. You can just log the
* error and hope for the best, but there is probably a more serious error that will
* reappear when you try to access the database again.
* @param dataSource the DataSource to extract metadata for
* @param action callback that will do the actual work
* @return object containing the extracted information, as returned by
* the DatabaseMetaDataCallback's {@code processMetaData} method
* @throws MetaDataAccessException if meta data access failed
*/
public static Object extractDatabaseMetaData(DataSource dataSource, DatabaseMetaDataCallback action)
throws MetaDataAccessException {
Connection con = null;
try {
con = DataSourceUtils.getConnection(dataSource);
if (con == null) {
// should only happen in test environments
throw new MetaDataAccessException("Connection returned by DataSource [" + dataSource + "] was null");
}
DatabaseMetaData metaData = con.getMetaData();
if (metaData == null) {
// should only happen in test environments
throw new MetaDataAccessException("DatabaseMetaData returned by Connection [" + con + "] was null");
}
return action.processMetaData(metaData);
}
catch (CannotGetJdbcConnectionException ex) {
throw new MetaDataAccessException("Could not get Connection for extracting meta data", ex);
}
catch (SQLException ex) {
throw new MetaDataAccessException("Error while extracting DatabaseMetaData", ex);
}
catch (AbstractMethodError err) {
throw new MetaDataAccessException(
"JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
}
finally {
DataSourceUtils.releaseConnection(con, dataSource);
}
}
/**
* Call the specified method on DatabaseMetaData for the given DataSource,
* and extract the invocation result.
* @param dataSource the DataSource to extract meta data for
* @param metaDataMethodName the name of the DatabaseMetaData method to call
* @return the object returned by the specified DatabaseMetaData method
* @throws MetaDataAccessException if we couldn't access the DatabaseMetaData
* or failed to invoke the specified method
* @see java.sql.DatabaseMetaData
*/
public static Object extractDatabaseMetaData(DataSource dataSource, final String metaDataMethodName)
throws MetaDataAccessException {
return extractDatabaseMetaData(dataSource,
new DatabaseMetaDataCallback() {
@Override
public Object processMetaData(DatabaseMetaData dbmd) throws SQLException, MetaDataAccessException {
try {
Method method = DatabaseMetaData.class.getMethod(metaDataMethodName, (Class[]) null);
return method.invoke(dbmd, (Object[]) null);
}
catch (NoSuchMethodException ex) {
throw new MetaDataAccessException("No method named '" + metaDataMethodName +
"' found on DatabaseMetaData instance [" + dbmd + "]", ex);
}
catch (IllegalAccessException ex) {
throw new MetaDataAccessException(
"Could not access DatabaseMetaData method '" + metaDataMethodName + "'", ex);
}
catch (InvocationTargetException ex) {
if (ex.getTargetException() instanceof SQLException) {
throw (SQLException) ex.getTargetException();
}
throw new MetaDataAccessException(
"Invocation of DatabaseMetaData method '" + metaDataMethodName + "' failed", ex);
}
}
});
}
/**
* Return whether the given JDBC driver supports JDBC 2.0 batch updates.
* <p>Typically invoked right before execution of a given set of statements:
* to decide whether the set of SQL statements should be executed through
* the JDBC 2.0 batch mechanism or simply in a traditional one-by-one fashion.
* <p>Logs a warning if the "supportsBatchUpdates" methods throws an exception
* and simply returns {@code false} in that case.
* @param con the Connection to check
* @return whether JDBC 2.0 batch updates are supported
* @see java.sql.DatabaseMetaData#supportsBatchUpdates()
*/
public static boolean supportsBatchUpdates(Connection con) {
try {
DatabaseMetaData dbmd = con.getMetaData();
if (dbmd != null) {
if (dbmd.supportsBatchUpdates()) {
logger.debug("JDBC driver supports batch updates");
return true;
}
else {
logger.debug("JDBC driver does not support batch updates");
}
}
}
catch (SQLException ex) {
logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
}
return false;
}
/**
* Extract a common name for the database in use even if various drivers/platforms provide varying names.
* @param source the name as provided in database metadata
* @return the common name to be used
*/
public static String commonDatabaseName(String source) {
String name = source;
if (source != null && source.startsWith("DB2")) {
name = "DB2";
}
else if ("Sybase SQL Server".equals(source) ||
"Adaptive Server Enterprise".equals(source) ||
"ASE".equals(source) ||
"sql server".equalsIgnoreCase(source) ) {
name = "Sybase";
}
return name;
}
/**
* Check whether the given SQL type is numeric.
* @param sqlType the SQL type to be checked
* @return whether the type is numeric
*/
public static boolean isNumeric(int sqlType) {
return Types.BIT == sqlType || Types.BIGINT == sqlType || Types.DECIMAL == sqlType ||
Types.DOUBLE == sqlType || Types.FLOAT == sqlType || Types.INTEGER == sqlType ||
Types.NUMERIC == sqlType || Types.REAL == sqlType || Types.SMALLINT == sqlType ||
Types.TINYINT == sqlType;
}
/**
* Determine the column name to use. The column name is determined based on a
* lookup using ResultSetMetaData.
* <p>This method implementation takes into account recent clarifications
* expressed in the JDBC 4.0 specification:
* <p><i>columnLabel - the label for the column specified with the SQL AS clause.
* If the SQL AS clause was not specified, then the label is the name of the column</i>.
* @return the column name to use
* @param resultSetMetaData the current meta data to use
* @param columnIndex the index of the column for the look up
* @throws SQLException in case of lookup failure
*/
public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
String name = resultSetMetaData.getColumnLabel(columnIndex);
if (name == null || name.length() < 1) {
name = resultSetMetaData.getColumnName(columnIndex);
}
return name;
}
/**
* Convert a column name with underscores to the corresponding property name using "camel case". A name
* like "customer_number" would match a "customerNumber" property name.
* @param name the column name to be converted
* @return the name using "camel case"
*/
public static String convertUnderscoreNameToPropertyName(String name) {
StringBuilder result = new StringBuilder();
boolean nextIsUpper = false;
if (name != null && name.length() > 0) {
if (name.length() > 1 && name.substring(1, 2).equals("_")) {
result.append(name.substring(0, 1).toUpperCase());
}
else {
result.append(name.substring(0, 1).toLowerCase());
}
for (int i = 1; i < name.length(); i++) {
String s = name.substring(i, i + 1);
if (s.equals("_")) {
nextIsUpper = true;
}
else {
if (nextIsUpper) {
result.append(s.toUpperCase());
nextIsUpper = false;
}
else {
result.append(s.toLowerCase());
}
}
}
}
return result.toString();
}
}