1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one or more
3 * contributor license agreements. See the NOTICE file distributed with
4 * this work for additional information regarding copyright ownership.
5 * The ASF licenses this file to You under the Apache License, Version 2.0
6 * (the "License"); you may not use this file except in compliance with
7 * the License. You may obtain a copy of the License at
8 *
9 * http://www.apache.org/licenses/LICENSE-2.0
10 *
11 * Unless required by applicable law or agreed to in writing, software
12 * distributed under the License is distributed on an "AS IS" BASIS,
13 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 * See the License for the specific language governing permissions and
15 * limitations under the License.
16 */
17 package org.apache.cocoon.util;
18
19 import java.io.BufferedInputStream;
20 import java.io.ByteArrayInputStream;
21 import java.io.File;
22 import java.io.FileInputStream;
23 import java.io.InputStream;
24 import java.io.Reader;
25 import java.math.BigDecimal;
26 import java.sql.Array;
27 import java.sql.Blob;
28 import java.sql.Clob;
29 import java.sql.Date;
30 import java.sql.PreparedStatement;
31 import java.sql.ResultSet;
32 import java.sql.SQLException;
33 import java.sql.Time;
34 import java.sql.Timestamp;
35 import java.sql.Types;
36 import java.util.Calendar;
37 import java.util.Collections;
38 import java.util.HashMap;
39 import java.util.Map;
40
41 import org.apache.avalon.framework.configuration.Configuration;
42 import org.apache.cocoon.servlet.multipart.Part;
43 import org.apache.commons.lang.BooleanUtils;
44 import org.apache.excalibur.source.Source;
45
46 /**
47 * Provide some utility methods to read from JDBC result sets or store
48 * them to JDBC statements. Largely copied from
49 * org.apache.cocoon.acting.AbstractDatabaseAction.
50 *
51 * <p>The following table lists all available column type names
52 * together with the JDBC methods used to get or set a column with
53 * that type. In some cases the returned type differs from the type
54 * returned by the getXXX method. To set a column, a number of
55 * conversions are automatically used. For details, please see the
56 * actual code.</p>
57 *
58 * <p><table border="1">
59 * <tr><th>type </th><th>getXXX </th><th>returns </th><th>setXXX </th></tr>
60 * <tr><td>clob </td><td>Clob </td><td>String </td><td>Clob </td></tr>
61 * <tr><td>ascii </td><td>Clob </td><td>String </td><td>asciStream </td></tr>
62 * <tr><td>big-decimal</td><td>BigDecimal </td><td> </td><td>BigDecimal </td></tr>
63 * <tr><td>binary </td><td> </td><td> </td><td>BinaryStream </td></tr>
64 * <tr><td>blob </td><td> </td><td> </td><td>Blob </td></tr>
65 * <tr><td>boolean </td><td>Boolean </td><td>Boolean </td><td>Boolean </td></tr>
66 * <tr><td>byte </td><td>Byte </td><td>Byte </td><td>Byte </td></tr>
67 * <tr><td>string </td><td>String </td><td> </td><td>String </td></tr>
68 * <tr><td>date </td><td>Date </td><td> </td><td>Date </td></tr>
69 * <tr><td>double </td><td>Double </td><td>Double </td><td>Double </td></tr>
70 * <tr><td>float </td><td>Float </td><td>Float </td><td>Float </td></tr>
71 * <tr><td>int </td><td>Int </td><td>Integer </td><td>Int </td></tr>
72 * <tr><td>long </td><td>Long </td><td>Long </td><td>Long </td></tr>
73 * <tr><td>short </td><td>Short </td><td> </td><td>Short </td></tr>
74 * <tr><td>time </td><td>Time </td><td> </td><td>Time </td></tr>
75 * <tr><td>time-stamp </td><td>Timestamp </td><td> </td><td>Timestamp </td></tr>
76 * <tr><td>array </td><td>Array </td><td> </td><td>Array </td></tr>
77 * <tr><td>row </td><td>Object </td><td>Struct </td><td>Object </td></tr>
78 * <tr><td>object </td><td>Object </td><td> </td><td>Object </td></tr>
79 * </table></p>
80 *
81 * @version CVS $Id: JDBCTypeConversions.java 452425 2006-10-03 11:18:47Z vgritsenko $
82 */
83 public class JDBCTypeConversions {
84 public static final Map typeConstants;
85
86 static {
87 /** Initialize the map of type names to jdbc column types.
88 Note that INTEGER, BLOB, and VARCHAR column types map to more than
89 one type name. **/
90 Map constants = new HashMap();
91 constants.put("clob", new Integer(Types.CLOB));
92 constants.put("ascii", new Integer(Types.CHAR));
93 constants.put("big-decimal", new Integer(Types.BIGINT));
94 constants.put("binary", new Integer(Types.VARBINARY));
95 constants.put("blob", new Integer(Types.BLOB));
96 constants.put("boolean", new Integer(Types.BIT));
97 constants.put("byte", new Integer(Types.TINYINT));
98 constants.put("string", new Integer(Types.VARCHAR));
99 constants.put("date", new Integer(Types.DATE));
100 constants.put("double", new Integer(Types.DOUBLE));
101 constants.put("float", new Integer(Types.FLOAT));
102 constants.put("int", new Integer(Types.INTEGER));
103 constants.put("long", new Integer(Types.NUMERIC));
104 constants.put("short", new Integer(Types.SMALLINT));
105 constants.put("time", new Integer(Types.TIME));
106 constants.put("time-stamp", new Integer(Types.TIMESTAMP));
107 constants.put("array", new Integer(Types.ARRAY));
108 constants.put("row", new Integer(Types.STRUCT));
109 constants.put("object", new Integer(Types.OTHER));
110 typeConstants = Collections.unmodifiableMap(constants);
111 }
112
113 /**
114 * Converts an object to a JDBC type. This has just been started
115 * and does not do much at the moment.
116 *
117 */
118 public static Object convert(Object value, String jType) {
119
120 Object object=null;
121 if (jType.equalsIgnoreCase("string")) {
122 if (value instanceof String) {
123 object = value;
124 } else {
125 object = value.toString();
126 }
127 } else if (jType.equalsIgnoreCase("int")) {
128 if (value instanceof String) {
129 object = Integer.decode((String)value);
130 } else if (value instanceof Integer) {
131 object = value;
132 } else {
133 //
134 }
135 } else if (jType.equalsIgnoreCase("long")) {
136 if (value instanceof String) {
137 object = Long.decode((String)value);
138 } else if (value instanceof Long) {
139 object = value;
140 } else {
141 //
142 }
143 } else {
144 // other types need parsing & creation
145 //
146 }
147 return object;
148 }
149
150 /**
151 * Get the Statement column so that the results are mapped correctly.
152 * (this has been copied from AbstractDatabaseAction and modified slightly)
153 */
154 public static Object getColumn(ResultSet set, Configuration column)
155 throws Exception {
156
157 Integer type = (Integer) JDBCTypeConversions.typeConstants.get(column.getAttribute("type"));
158 String dbcol = column.getAttribute("name");
159 Object value;
160
161 switch (type.intValue()) {
162 case Types.CLOB:
163 case Types.CHAR:
164 Clob dbClob = set.getClob(dbcol);
165 if (dbClob != null) {
166 int length = (int) dbClob.length();
167 char[] buffer = new char[length];
168 Reader r = dbClob.getCharacterStream();
169 try {
170 length = r.read(buffer);
171 value = new String(buffer, 0, length);
172 } finally {
173 r.close();
174 }
175 } else {
176 value = null;
177 }
178 break;
179 case Types.BIGINT:
180 value = set.getBigDecimal(dbcol);
181 break;
182 case Types.TINYINT:
183 value = new Byte(set.getByte(dbcol));
184 break;
185 case Types.VARCHAR:
186 value = set.getString(dbcol);
187 break;
188 case Types.DATE:
189 value = set.getDate(dbcol);
190 break;
191 case Types.DOUBLE:
192 value = new Double(set.getDouble(dbcol));
193 break;
194 case Types.FLOAT:
195 value = new Float(set.getFloat(dbcol));
196 break;
197 case Types.INTEGER:
198 value = new Integer(set.getInt(dbcol));
199 break;
200 case Types.NUMERIC:
201 value = new Long(set.getLong(dbcol));
202 break;
203 case Types.SMALLINT:
204 value = new Short(set.getShort(dbcol));
205 break;
206 case Types.TIME:
207 value = set.getTime(dbcol);
208 break;
209 case Types.TIMESTAMP:
210 value = set.getTimestamp(dbcol);
211 break;
212 case Types.ARRAY:
213 value = set.getArray(dbcol); // new Integer(set.getInt(dbcol));
214 break;
215 case Types.BIT:
216 value = BooleanUtils.toBooleanObject(set.getBoolean(dbcol));
217 break;
218 case Types.STRUCT:
219 value = set.getObject(dbcol);
220 break;
221 case Types.OTHER:
222 value = set.getObject(dbcol);
223 break;
224
225 default:
226 // The blob types have to be requested separately, via a Reader.
227 value = "";
228 break;
229 }
230
231 return value;
232 }
233
234
235 /**
236 * Set the Statement column so that the results are mapped correctly.
237 *
238 * @param statement the prepared statement
239 * @param position the position of the column
240 * @param value the value of the column
241 */
242 public static void setColumn(PreparedStatement statement, int position, Object value, Integer typeObject) throws Exception {
243 if (value instanceof String) {
244 value = ((String) value).trim();
245 }
246 if (typeObject == null) {
247 throw new SQLException("Can't set column because the type is unrecognized");
248 }
249 if (value == null) {
250 /** If the value is null, set the column value null and return **/
251 statement.setNull(position, typeObject.intValue());
252 return;
253 }
254 if ("".equals(value)) {
255 switch (typeObject.intValue()) {
256 case Types.CHAR:
257 case Types.CLOB:
258 case Types.VARCHAR:
259 /** If the value is an empty string and the column is
260 a string type, we can continue **/
261 break;
262 default:
263 /** If the value is an empty string and the column
264 is something else, we treat it as a null value **/
265 statement.setNull(position, typeObject.intValue());
266 return;
267 }
268 }
269
270 File file;
271 int length;
272 InputStream asciiStream;
273
274 //System.out.println("========================================================================");
275 //System.out.println("JDBCTypeConversions: setting type "+typeObject.intValue());
276 switch (typeObject.intValue()) {
277 case Types.CLOB:
278 //System.out.println("CLOB");
279 Clob clob;
280 if (value instanceof Clob) {
281 clob = (Clob) value;
282 } else if (value instanceof File) {
283 File asciiFile = (File) value;
284 asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
285 length = (int) asciiFile.length();
286 clob = new ClobHelper(asciiStream, length);
287 } else if (value instanceof Part) {
288 Part anyFile = (Part) value;
289 asciiStream = new BufferedInputStream(anyFile.getInputStream());
290 length = anyFile.getSize();
291 clob = new ClobHelper(asciiStream, length);
292 } else if (value instanceof JDBCxlobHelper) {
293 asciiStream = ((JDBCxlobHelper) value).inputStream;
294 length = ((JDBCxlobHelper) value).length;
295 clob = new ClobHelper(asciiStream, length);
296 } else if (value instanceof Source) {
297 asciiStream = ((Source) value).getInputStream();
298 length = (int)((Source) value).getContentLength();
299 clob = new ClobHelper(asciiStream, length);
300 } else {
301 String asciiText = value.toString();
302 asciiStream = new ByteArrayInputStream(asciiText.getBytes());
303 length = asciiText.length();
304 clob = new ClobHelper(asciiStream, length);
305 }
306
307 statement.setClob(position, clob);
308 break;
309 case Types.CHAR:
310 // simple large object, e.g. Informix's TEXT
311 //System.out.println("CHAR");
312
313 if (value instanceof File) {
314 File asciiFile = (File) value;
315 asciiStream = new BufferedInputStream(new FileInputStream(asciiFile));
316 length = (int) asciiFile.length();
317 } else if (value instanceof JDBCxlobHelper) {
318 asciiStream = ((JDBCxlobHelper) value).inputStream;
319 length = ((JDBCxlobHelper) value).length;
320 } else if (value instanceof Source) {
321 asciiStream = ((Source) value).getInputStream();
322 length = (int)((Source) value).getContentLength();
323 } else if (value instanceof Part) {
324 Part anyFile = (Part) value;
325 asciiStream = new BufferedInputStream(anyFile.getInputStream());
326 length = anyFile.getSize();
327 clob = new ClobHelper(asciiStream, length);
328 } else {
329 String asciiText = value.toString();
330 asciiStream = new BufferedInputStream(new ByteArrayInputStream(asciiText.getBytes()));
331 length = asciiText.length();
332 }
333
334 statement.setAsciiStream(position, asciiStream, length);
335 break;
336 case Types.BIGINT:
337 //System.out.println("BIGINT");
338 BigDecimal bd;
339
340 if (value instanceof BigDecimal) {
341 bd = (BigDecimal) value;
342 } else if (value instanceof Number) {
343 bd = BigDecimal.valueOf(((Number)value).longValue());
344 } else {
345 bd = new BigDecimal(value.toString());
346 }
347
348 statement.setBigDecimal(position, bd);
349 break;
350 case Types.TINYINT:
351 //System.out.println("TINYINT");
352 Byte b;
353 if (value instanceof Byte) {
354 b = (Byte) value;
355 } else if (value instanceof Number) {
356 b = new Byte(((Number) value).byteValue());
357 } else {
358 b = new Byte(value.toString());
359 }
360
361 statement.setByte(position, b.byteValue());
362 break;
363 case Types.DATE:
364 //System.out.println("DATE");
365 Date d;
366 if (value instanceof Date) {
367 d = (Date) value;
368 } else if (value instanceof java.util.Date) {
369 d = new Date(((java.util.Date) value).getTime());
370 } else if (value instanceof Calendar) {
371 d = new Date(((Calendar) value).getTime().getTime());
372 } else {
373 d = Date.valueOf(value.toString());
374 }
375
376 statement.setDate(position, d);
377 break;
378 case Types.DOUBLE:
379 //System.out.println("DOUBLE");
380 double db;
381
382 if (value instanceof Number) {
383 db = (((Number) value).doubleValue());
384 } else {
385 db = Double.parseDouble(value.toString());
386 }
387 statement.setDouble(position, db);
388 break;
389 case Types.FLOAT:
390 //System.out.println("FLOAT");
391 float f;
392
393 if (value instanceof Number) {
394 f = (((Number) value).floatValue());
395 } else {
396 f = Float.parseFloat(value.toString());
397 }
398 statement.setFloat(position, f);
399 break;
400 case Types.NUMERIC:
401 //System.out.println("NUMERIC");
402 long l;
403
404 if (value instanceof Number) {
405 l = (((Number) value).longValue());
406 } else {
407 l = Long.parseLong(value.toString());
408 }
409
410 statement.setLong(position, l);
411 break;
412 case Types.SMALLINT:
413 //System.out.println("SMALLINT");
414 Short s;
415 if (value instanceof Short) {
416 s = (Short) value;
417 } else if (value instanceof Number) {
418 s = new Short(((Number) value).shortValue());
419 } else {
420 s = new Short(value.toString());
421 }
422
423 statement.setShort(position, s.shortValue());
424 break;
425 case Types.TIME:
426 //System.out.println("TIME");
427 Time t;
428 if (value instanceof Time) {
429 t = (Time) value;
430 } else if (value instanceof java.util.Date){
431 t = new Time(((java.util.Date) value).getTime());
432 } else {
433 t = Time.valueOf(value.toString());
434 }
435
436 statement.setTime(position, t);
437 break;
438 case Types.TIMESTAMP:
439 //System.out.println("TIMESTAMP");
440 Timestamp ts;
441 if (value instanceof Time) {
442 ts = (Timestamp) value;
443 } else if (value instanceof java.util.Date) {
444 ts = new Timestamp(((java.util.Date) value).getTime());
445 } else {
446 ts = Timestamp.valueOf(value.toString());
447 }
448
449 statement.setTimestamp(position, ts);
450 break;
451 case Types.ARRAY:
452 //System.out.println("ARRAY");
453 statement.setArray(position, (Array) value); // no way to convert string to array
454 break;
455 case Types.STRUCT:
456 //System.out.println("STRUCT");
457 case Types.OTHER:
458 //System.out.println("OTHER");
459 statement.setObject(position, value);
460 break;
461 case Types.LONGVARBINARY:
462 //System.out.println("LONGVARBINARY");
463 statement.setTimestamp(position, new Timestamp((new java.util.Date()).getTime()));
464 break;
465 case Types.VARCHAR:
466 //System.out.println("VARCHAR");
467 statement.setString(position, value.toString());
468 break;
469 case Types.BLOB:
470 //System.out.println("BLOB");
471 if (value instanceof JDBCxlobHelper) {
472 statement.setBinaryStream(position, ((JDBCxlobHelper)value).inputStream, ((JDBCxlobHelper)value).length);
473 } else if (value instanceof Source){
474 statement.setBinaryStream(position, ((Source)value).getInputStream(), (int)((Source)value).getContentLength());
475 } else {
476 Blob blob;
477 if (value instanceof Blob) {
478 blob = (Blob) value;
479 } else if( value instanceof File) {
480 file = (File)value;
481 blob = new BlobHelper(new FileInputStream(file), (int) file.length());
482 } else if (value instanceof String) {
483 file = new File((String)value);
484 blob = new BlobHelper(new FileInputStream(file), (int) file.length());
485 } else if (value instanceof Part) {
486 Part anyFile = (Part) value;
487 blob = new BlobHelper(new BufferedInputStream(anyFile.getInputStream()), anyFile.getSize());
488 } else {
489 throw new SQLException("Invalid type for blob: "+value.getClass().getName());
490 }
491 //InputStream input = new BufferedInputStream(new FileInputStream(file));
492 statement.setBlob(position, blob);
493 }
494 break;
495 case Types.VARBINARY:
496 //System.out.println("VARBINARY");
497 if (value instanceof JDBCxlobHelper) {
498 statement.setBinaryStream(position, ((JDBCxlobHelper)value).inputStream, ((JDBCxlobHelper)value).length);
499 } else if (value instanceof Source){
500 statement.setBinaryStream(position, ((Source)value).getInputStream(), (int)((Source)value).getContentLength());
501 } else if (value instanceof Part) {
502 statement.setBinaryStream(position, ((Part)value).getInputStream(), ((Part)value).getSize());
503 } else {
504 if (value instanceof File) {
505 file = (File)value;
506 } else if (value instanceof String) {
507 file = new File((String)value);
508 } else {
509 throw new SQLException("Invalid type for blob: "+value.getClass().getName());
510 }
511 //InputStream input = new BufferedInputStream(new FileInputStream(file));
512 FileInputStream input = new FileInputStream(file);
513 statement.setBinaryStream(position, input, (int)file.length());
514 }
515 break;
516 case Types.INTEGER:
517 //System.out.println("INTEGER");
518 Integer i;
519 if (value instanceof Integer) {
520 i = (Integer) value;
521 } else if (value instanceof Number) {
522 i = new Integer(((Number) value).intValue());
523 } else {
524 i = new Integer(value.toString());
525 }
526 statement.setInt(position, i.intValue());
527 break;
528 case Types.BIT:
529 //System.out.println("BIT");
530 Boolean bo;
531 if (value instanceof Boolean) {
532 bo = (Boolean)value;
533 } else if (value instanceof Number) {
534 bo = BooleanUtils.toBooleanObject(((Number) value).intValue()==1);
535 } else {
536 bo = BooleanUtils.toBooleanObject(value.toString());
537 }
538 statement.setBoolean(position, bo.booleanValue());
539 break;
540
541 default:
542 //System.out.println("default");
543 throw new SQLException("Impossible exception - invalid type ");
544 }
545 //System.out.println("========================================================================");
546 }
547 }
Save This Page