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