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