1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package com.finalist.tools.database;
19
20 import java.math.*;
21 import java.sql.*;
22 import java.util.*;
23 import java.io.*;
24 import java.lang.reflect.*;
25
26 import oracle.sql.*;
27 import oracle.jdbc.*;
28
29 /***
30 * Implementation of the rowmapper for Oracle. This class has been tested against
31 * Oracle 8.1.7 and 8.2.
32 *
33 * @author P.S.D.Reitsma, Michael O'Connor - Finalist IT Group
34 */
35 public class OracleRowMapper extends RowMapper {
36
37
38 /***
39 * Method that binds an object of a specified type into a PreparedStatement and returns the PreparedStatement.
40 *
41 * The following mappings are made.
42 * <table border="1" cellspacing="2" cellpadding="2">
43 * <tr><td><b>Input Type </b></td><td><b>Method used </b></td><td><b>NullType used </b></td></tr>
44 * <tr><td>String </td><td>setString() </td><td>java.sql.Types.VARCHAR </td></tr>
45 * <tr><td>int/Integer </td><td>setInt() </td><td>java.sql.Types.Numeric </td></tr>
46 * <tr><td>byte/Byte </td><td>setByte() </td><td>java.sql.Types.Numeric </td></tr>
47 * <tr><td>short/Short </td><td>setShort() </td><td>java.sql.Types.Numeric </td></tr>
48 * <tr><td>float/Float </td><td>setFloat() </td><td>java.sql.Types.Numeric </td></tr>
49 * <tr><td>long/Long </td><td>setLong() </td><td>java.sql.Types.Numeric </td></tr>
50 * <tr><td>double/Double </td><td>setDouble() </td><td>java.sql.Types.Numeric </td></tr>
51 * <tr><td>util.Date </td><td>setDate() </td><td>java.sql.Types.Date </td></tr>
52 * <tr><td>util.Calendar </td><td>setDate() </td><td>java.sql.Types.Date </td></tr>
53 *
54 *
55 * </table><br>
56 *
57 * @return PreparedStatement the resulting PreparedStatement after the bind has happened.
58 * @param pstmt PreparedStatement to which the object should be bound.
59 * @param bindVar the object that is to be bound.
60 * @param bindVarClassType the full classname that identifies the type of the class
61 * @param pos the position at which the object should be bound into the statement.
62 * @throws NotMappableException when no appropriate bind method could be found
63 * @throws SQLException
64 */
65 public PreparedStatement bind(PreparedStatement pstmt, Object bindVar, String bindVarClassType, int pos) throws SQLException, NotMappableException {
66 if (bindVarClassType.endsWith("String")) {
67 if (bindVar == null) {
68 pstmt.setNull(pos, java.sql.Types.VARCHAR);
69 }
70 else {
71 pstmt.setString(pos, (String) bindVar);
72 }
73
74 }
75 else if (bindVarClassType.endsWith("int")) {
76 if (bindVar == null) {
77 pstmt.setNull(pos, java.sql.Types.NUMERIC);
78 }
79 else {
80 pstmt.setInt(pos, ((Integer) bindVar).intValue());
81 }
82
83 }
84 else if (bindVarClassType.endsWith("Integer")) {
85 if (bindVar == null) {
86 pstmt.setNull(pos, java.sql.Types.NUMERIC);
87 }
88 else {
89 pstmt.setInt(pos, ((Integer) bindVar).intValue());
90 }
91
92 }
93 else if (bindVarClassType.toLowerCase().endsWith("byte")) {
94 if (bindVar == null) {
95 pstmt.setNull(pos, java.sql.Types.NUMERIC);
96 }
97 else {
98 pstmt.setByte(pos, ((Byte) bindVar).byteValue());
99 }
100
101 }
102 else if (bindVarClassType.toLowerCase().endsWith("short")) {
103 if (bindVar == null) {
104 pstmt.setNull(pos, java.sql.Types.NUMERIC);
105 }
106 else {
107 pstmt.setShort(pos, ((Short) bindVar).shortValue());
108 }
109
110 }
111 else if (bindVarClassType.toLowerCase().endsWith("float")) {
112 if (bindVar == null) {
113 pstmt.setNull(pos, java.sql.Types.NUMERIC);
114 }
115 else {
116 pstmt.setFloat(pos, ((Float) bindVar).floatValue());
117 }
118
119 }
120 else if (bindVarClassType.toLowerCase().endsWith("long")) {
121 if (bindVar == null) {
122 pstmt.setNull(pos, java.sql.Types.NUMERIC);
123 }
124 else {
125 pstmt.setLong(pos, ((Long) bindVar).longValue());
126 }
127
128 }
129 else if (bindVarClassType.toLowerCase().endsWith("double")) {
130 if (bindVar == null) {
131 pstmt.setNull(pos, java.sql.Types.NUMERIC);
132 }
133 else {
134 pstmt.setDouble(pos, ((Double) bindVar).doubleValue());
135 }
136
137 }
138 else if (bindVarClassType.toLowerCase().endsWith("math.bigdecimal")) {
139 if (bindVar == null) {
140 pstmt.setNull(pos, java.sql.Types.NUMERIC);
141 }
142 else {
143 pstmt.setDouble(pos, ((BigDecimal) bindVar).doubleValue());
144 }
145
146 }
147 else if (bindVarClassType.endsWith("util.Date")) {
148 if (bindVar == null) {
149 pstmt.setNull(pos, java.sql.Types.DATE);
150 }
151 else {
152 pstmt.setTimestamp(pos, new Timestamp(((java.util.Date) bindVar).getTime()));
153 }
154
155 }
156 else if (bindVarClassType.endsWith("sql.Date")) {
157 if (bindVar == null) {
158 pstmt.setNull(pos, java.sql.Types.DATE);
159 }
160 else {
161 pstmt.setTimestamp(pos, new Timestamp(((java.sql.Date) bindVar).getTime()));
162 }
163
164 }
165 else if (bindVarClassType.endsWith("sql.Timestamp")) {
166 if (bindVar == null) {
167 pstmt.setNull(pos, java.sql.Types.TIMESTAMP);
168 }
169 else {
170 pstmt.setTimestamp(pos, (java.sql.Timestamp) bindVar);
171 }
172
173 }
174 else if (bindVarClassType.endsWith("Calendar")) {
175 if (bindVar == null) {
176 pstmt.setNull(pos, java.sql.Types.TIMESTAMP);
177 }
178 else {
179 pstmt.setTimestamp(pos, new Timestamp(((Calendar) bindVar).getTime().getTime()));
180 }
181
182 }
183 else if ((bindVar == null) && (bindVarClassType.equals("undetermined"))) {
184 pstmt.setNull(pos, java.sql.Types.VARCHAR);
185
186 }
187 else {
188 NotMappableException nme = new NotMappableException();
189 nme.setDuringBind(true);
190 nme.setSourceType(bindVarClassType);
191 if (bindVar == null) bindVar = "null";
192 nme.setSourceValue(bindVar.toString());
193 nme.setTargetName("position " + pos);
194 throw nme;
195 }
196
197 return pstmt;
198 }
199
200
201 /***
202 * Method reads the values from a ResultSet and puts them as properties into
203 * a HashMap. The keys of the HashMap will be the column aliases of the SQL-Statement
204 * and the values will be java Objects.
205 * The following conversions rules are valid:
206 * <table border="1" cellspacing="2" cellpadding="2">
207 * <tr><td><b>Oracle Datatype</b></td><td><b>Java Object type</b></td></tr>
208 * <tr><td>VARCHAR2 </td><td>String </td></tr>
209 * <tr><td>DATE </td><td>java.sql.Timestamp </td></tr>
210 * <tr><td>NUMBER(<3,0) </td><td>Byte </td></tr>
211 * <tr><td>NUMBER(>2 <6,0) </td><td>Short </td></tr>
212 * <tr><td>NUMBER(>5 <10,0) </td><td>Integer </td></tr>
213 * <tr><td>NUMBER(>9 <19,0) </td><td>Long </td></tr>
214 * <tr><td>NUMBER( (scale and precision)<13) </td><td>Float </td></tr>
215 * <tr><td>NUMBER( (scale and precision)>12) </td><td>Double </td></tr>
216 * <tr><td>CHAR </td><td>String </td></tr>
217 * <tr><td>Date </td><td>java.util.Date </td></tr>
218 *
219 * </table>
220 *
221 *
222 * TODO: more mappings could be made.
223 *
224 * @param rset ResultSet
225 * @param target The HashMap in which the values are stored
226 * @exception SQLException thrown when some SQL related error occurs.
227 * @exception NotMappableException thrown when no appropriate mapping could be found to map
228 * a SQL datatype to a Java counterpart.
229 */
230 public HashMap unBindIntoHashMap(ResultSet rset, HashMap target) throws SQLException, NotMappableException {
231 ResultSetMetaData rsmd = rset.getMetaData();
232 for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
233 String name = rsmd.getColumnName(i).toUpperCase();
234 String type = rsmd.getColumnTypeName(i);
235
236 if (type.equals("DATE")) {
237 Timestamp timeStamp = rset.getTimestamp(i);
238 target.put(name, timeStamp);
239 continue;
240 }
241
242 if (type.equals("NUMBER")) {
243 Object o = rset.getObject(i);
244 int precision = rsmd.getPrecision(i);
245 int scale = rsmd.getScale(i);
246
247 if (scale == 0) {
248
249 if (precision == 0) {
250
251
252
253
254 if (o != null) {
255 target.put(name, new Integer(((BigDecimal) o).intValue()));
256 }
257 else {
258 target.put(name, new Integer("0"));
259 }
260 continue;
261 }
262 if (precision <= 2) {
263
264 if (o != null) {
265 target.put(name, new Byte(((BigDecimal) o).byteValue()));
266 }
267 else {
268 target.put(name, new Byte("0"));
269 }
270 continue;
271 }
272 if (precision <= 5) {
273
274 if (o != null) {
275 target.put(name, new Short(((BigDecimal) o).shortValue()));
276 }
277 else {
278 target.put(name, new Short("0"));
279 }
280 continue;
281 }
282 if (precision <= 9) {
283
284 if (o != null) {
285 target.put(name, new Integer(((BigDecimal) o).intValue()));
286 }
287 else {
288 target.put(name, new Integer("0"));
289 }
290 continue;
291 }
292 if (precision <= 18) {
293
294 if (o != null) {
295 target.put(name, new Long(((BigDecimal) o).longValue()));
296 }
297 else {
298 target.put(name, new Long("0"));
299 }
300 continue;
301 }
302 }
303 if (precision + scale <= 12) {
304 if (o != null) {
305 target.put(name, new Float(((BigDecimal) o).floatValue()));
306 }
307 else {
308 target.put(name, new Float(0.0));
309 }
310 continue;
311 }
312 if (precision + scale <= 64) {
313 if (o != null) {
314 target.put(name, new Double(((BigDecimal) o).doubleValue()));
315 }
316 else {
317 target.put(name, new Double(0.0));
318 }
319 continue;
320 }
321 }
322
323 if (type.indexOf("CHAR") > -1) {
324 String s = rset.getString(i);
325 target.put(name, s);
326 continue;
327 }
328
329 if (type.indexOf("CLOB") > -1) {
330 CLOB clob = ((OracleResultSet) rset).getCLOB(i);
331 String res = null;
332 if (clob != null) {
333 char[] characterBuffer = new char[(int) clob.length()];
334
335 try {
336
337 Reader charInstream = clob.getCharacterStream();
338 charInstream.read(characterBuffer);
339 res = new String(characterBuffer);
340 charInstream.close();
341 }
342 catch (IOException io) {
343 NotMappableException nme = new NotMappableException();
344 nme.setMessage("IO Exception occurred :" + io.getMessage());
345 throw new NotMappableException();
346 }
347 }
348 target.put(name, res);
349 continue;
350 }
351
352
353 if (type.indexOf("LONG") > -1) {
354 byte[] bytes = rset.getBytes(i);
355 target.put(name, (bytes == null ? null : new String(bytes)));
356 continue;
357 }
358
359 Object o = rset.getObject(i);
360 target.put(name, o);
361
362 }
363
364 return target;
365 }
366
367
368 /***
369 * Method reads the values from a ResultSet and puts them as properties into
370 * a bean. The column-property mapping happens through the use of column aliases
371 * in the SQL Statement. The column aliases should map to a property name. The mapping
372 * is not case sensitive.
373 *
374 * TODO: need for more mapping might arise.
375 *
376 * @param rset ResultSet
377 * @param target The bean whose properties should be set.
378 * @exception SQLException thrown when some SQL related error occurs.
379 * @exception NotMappableException thrown when no appropriate mapping could be found to map
380 * a SQL datatype to a Java counterpart.
381 */
382 public Object unBindIntoBean(ResultSet rset, Object target) throws SQLException, NotMappableException {
383 ResultSetMetaData rsmd = rset.getMetaData();
384 String sourceType = null;
385 String sourceName = null;
386 String targetType = null;
387 for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
388 try {
389
390 sourceType = rsmd.getColumnTypeName(i);
391
392 sourceName = rsmd.getColumnName(i);
393
394 targetType = MethodInvoker.getReturnType(target, rsmd.getColumnName(i));
395
396 if (sourceType.indexOf("CHAR") > -1 && targetType.endsWith("String")) {
397 String targetValue = rset.getString(i);
398 MethodInvoker.setProperty(target, sourceName, targetValue);
399 continue;
400 }
401
402 if (sourceType.startsWith("DATE")) {
403 Timestamp date = rset.getTimestamp(i);
404 if (targetType.endsWith("util.Date")) {
405 java.util.Date targetValue = null;
406
407 if (date != null) {
408 targetValue = new java.util.Date(date.getTime());
409 }
410 MethodInvoker.setProperty(target, sourceName, targetValue);
411 continue;
412
413 }
414 else if (targetType.endsWith("sql.Date")) {
415 java.sql.Date targetValue = null;
416
417 if (date != null) {
418 targetValue = new java.sql.Date(date.getTime());
419 }
420 MethodInvoker.setProperty(target, sourceName, targetValue);
421 continue;
422
423 }
424 else if (targetType.endsWith("sql.Timestamp")) {
425 MethodInvoker.setProperty(target, sourceName, date);
426 continue;
427
428 }
429 else if (targetType.endsWith("Calendar")) {
430 java.util.Calendar targetValue = Calendar.getInstance();
431
432 if (date != null) {
433 targetValue.setTime(date);
434 MethodInvoker.setProperty(target, sourceName, targetValue);
435 }
436 continue;
437
438 }
439 else if (targetType.endsWith("String")) {
440 String targetValue = null;
441 if (date != null) {
442 targetValue = date.toString();
443 MethodInvoker.setProperty(target, sourceName, targetValue);
444 }
445 continue;
446 }
447 }
448
449 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().indexOf("int") > -1) {
450 if (rset.getObject(i) != null) {
451 Integer targetValue = new Integer(rset.getInt(i));
452 MethodInvoker.setProperty(target, sourceName, targetValue);
453 }
454 continue;
455 }
456
457 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("float")) {
458 if (rset.getObject(i) != null) {
459 Float targetValue = new Float(rset.getFloat(i));
460 MethodInvoker.setProperty(target, sourceName, targetValue);
461 }
462 continue;
463 }
464
465 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("byte")) {
466 if (rset.getObject(i) != null) {
467 Byte targetValue = new Byte(rset.getByte(i));
468 MethodInvoker.setProperty(target, sourceName, targetValue);
469 }
470 continue;
471 }
472
473 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("short")) {
474 if (rset.getObject(i) != null) {
475 Short targetValue = new Short(rset.getShort(i));
476 MethodInvoker.setProperty(target, sourceName, targetValue);
477 }
478 continue;
479 }
480
481 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("double")) {
482 if (rset.getObject(i) != null) {
483 Double targetValue = new Double(rset.getDouble(i));
484 MethodInvoker.setProperty(target, sourceName, targetValue);
485 }
486 continue;
487 }
488
489 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("long")) {
490 if (rset.getObject(i) != null) {
491 Long targetValue = new Long(rset.getLong(i));
492 MethodInvoker.setProperty(target, sourceName, targetValue);
493 }
494 continue;
495 }
496
497 if (sourceType.startsWith("NUMBER") && targetType.toLowerCase().endsWith("math.bigdecimal")) {
498 if (rset.getObject(i) != null) {
499 BigDecimal targetValue = rset.getBigDecimal(i);
500 MethodInvoker.setProperty(target, sourceName, targetValue);
501 }
502 continue;
503 }
504
505 if (sourceType.startsWith("NUMBER") && targetType.endsWith("String")) {
506 if (rset.getObject(i) != null) {
507 String targetValue = "" + rset.getInt(i);
508 MethodInvoker.setProperty(target, sourceName, targetValue);
509 }
510 continue;
511 }
512
513 if (sourceType.indexOf("CLOB") > -1 && targetType.endsWith("String")) {
514 CLOB clob = ((OracleResultSet) rset).getCLOB(i);
515 String targetValue = null;
516 if (clob != null) {
517 char[] characterBuffer = new char[(int) clob.length()];
518
519 try {
520
521 Reader charInstream = clob.getCharacterStream();
522 charInstream.read(characterBuffer);
523 targetValue = new String(characterBuffer);
524 charInstream.close();
525 }
526 catch (IOException io) {
527 NotMappableException nme = new NotMappableException();
528 nme.setMessage("IO Exception ocurred :" + io.getMessage());
529 throw new NotMappableException();
530 }
531 MethodInvoker.setProperty(target, sourceName, targetValue);
532 }
533
534 continue;
535 }
536
537
538 if (sourceType.indexOf("LONG") > -1) {
539 byte[] bytes = rset.getBytes(i);
540 MethodInvoker.setProperty(target, sourceName, (bytes == null ? null : new String(bytes)));
541 continue;
542 }
543
544
545 NotMappableException nme = new NotMappableException();
546 nme.setDuringBind(false);
547 nme.setSourceName(sourceName);
548 nme.setSourceType(sourceType);
549 nme.setTargetType(targetType);
550 throw nme;
551
552 }
553 catch (IllegalAccessException iae) {
554 System.out.println("!!No public getter/setter found in templateBean for column " + sourceName);
555
556 }
557 catch (InvocationTargetException ite) {
558 throw new NotMappableException("An InvocationTargetException ocurred");
559 }
560 catch (NoSuchMethodException nsme) {
561 System.out.println("!!No getter/setter found in templateBean for column " + sourceName);
562
563 }
564
565 }
566
567 return target;
568
569 }
570
571
572 /*** converts datatypes from oracle specific to java specific types.
573 * @param columnList ArrayList filled with hashMaps. HashMap consists of name, type, scale and precision.
574 * @return columnList with altered datatypes
575 */
576
577 public static ArrayList convertDatatypes(ArrayList columnList) {
578 for (int i = 0; i < columnList.size(); i++) {
579 ColumnBean column = (ColumnBean) columnList.get(i);
580
581 String type = column.getSqlType();
582 int scale = column.getScale();
583 int precision = column.getPrecision();
584
585 if (type.equals("DATE")) {
586 column.setJavaType("java.util.Date");
587 continue;
588 }
589 if (type.equals("NUMBER")) {
590 if (scale == 0) {
591 if (precision == 0) {
592
593
594
595
596 column.setJavaType("int");
597 continue;
598 }
599 if (precision <= 2) {
600
601 column.setJavaType("byte");
602 continue;
603 }
604 if (precision <= 5) {
605 column.setJavaType("short");
606 continue;
607 }
608 if (precision <= 9) {
609
610 column.setJavaType("int");
611 continue;
612 }
613 if (precision <= 18) {
614
615 column.setJavaType("long");
616 continue;
617 }
618 }
619 if (precision + scale <= 12) {
620 column.setJavaType("float");
621 continue;
622 }
623 if (precision + scale <= 64) {
624 column.setJavaType("double");
625 continue;
626 }
627 }
628 if (type.indexOf("CHAR") > -1) {
629 column.setJavaType("String");
630 continue;
631 }
632 column.setJavaType("Object");
633 }
634 return columnList;
635 }
636 }