Java > Open Source Codes > Jt > JtJDBCAdapter


1
2
3 package Jt;
4 import java.io.*;
5 import java.sql.*;
6 import javax.sql.*;
7 import java.text.*;
8 import Jt.jndi.*;
9
10 /**
11  * Jt Adapter for the JDBC API. This implementation supports Data Sources.
12  */
13
14 public class JtJDBCAdapter extends JtAdapter {
15   private String user;
16   private String password;
17   private String url;
18   private String driver;
19   private transient Connection connection = null;
20   private String base;
21   int n = 0;
22   private transient Statement myStatement;
23   private String datasource = null;
24   private transient JtJNDIAdapter jndiAdapter = null;
25   private transient DataSource dataSource = null;
26   private transient boolean initted = false;
27
28   private DataSource locateDataSource (String datasource) {
29     JtMessage msg = new JtMessage ("JtLOOKUP");
30     
31       
32     if (datasource == null)
33       return (null);
34
35     msg.setMsgContent (datasource);
36     jndiAdapter = new JtJNDIAdapter ();
37
38     return ((DataSource) jndiAdapter.processMessage (msg));
39
40   }
41   
42   private void initial () {
43
44
45
46     if (datasource != null) {
47       dataSource = locateDataSource (datasource);
48
49       if (dataSource == null) {
50         handleError ("JtJDBAdapter.connect: unable to locate datasource:" +
51          datasource);
52         connection = null;
53         return;
54
55       }
56
57       try {
58         connection = dataSource.getConnection ();
59       } catch (Exception ex) {
60         handleException (ex);
61       }
62     }
63
64   }
65
66
67   void connect ()
68   {
69     handleTrace ("JtJDBCAdapter.connect ....");
70
71
72     if (datasource != null) {
73
74       if (dataSource == null) {
75         handleWarning ("JtJDBAdapter.connect: unable to connect to Datasource: " +
76          datasource);
77         connection = null;
78         return;
79
80       }
81
82       try {
83         connection = dataSource.getConnection ();
84         handleTrace ("JtJDBCAdapter.connect: using data source ....");
85
86       } catch (Exception ex) {
87         handleException (ex);
88       }
89       return;
90     }
91
92     if (connection != null) {
93       return;
94     }
95
96     if (driver == null) {
97       handleError ("connect: null attribute (driver)");
98       return;
99     }
100
101     if (url == null) {
102       handleError ("connect: null attribute (url)");
103       return;
104     }
105
106     try
107     {
108       Class.forName(driver);
109       if (user == null)
110         connection = DriverManager.getConnection(url);
111       else
112         connection = DriverManager.getConnection(url,
113            user, password);
114       //System.out.println (connection.getAutoCommit ());
115 }
116     catch(ClassNotFoundException cnfe)
117     {
118         handleException (cnfe);
119     }
120     catch(SQLException sqle)
121     {
122         handleException (sqle);
123     }
124   }
125
126   // close: close connection
127
128   void close () {
129
130      if (connection == null)
131     return;
132
133      try {
134         connection.close ();
135      }
136      catch (SQLException sqle) {
137     handleException (sqle);
138      }
139      finally {
140         connection = null;
141      }
142   }
143
144   // Executes a query
145
146   ResultSet execute_query (String query) {
147
148       if (query == null)
149           return (null);
150
151       if (connection == null) {
152      handleError ("execute_query: no database connection");
153      return (null);
154       }
155
156       try {
157         myStatement = connection.createStatement ();
158         ResultSet results = myStatement.executeQuery (query);
159         
160         if (results == null) // check
161 myStatement.close ();
162
163         return (results); // check - close later
164
165         //results.close ();
166 }
167       catch(SQLException sqle)
168       {
169     handleTrace ("Message: " + sqle.getMessage ());
170     handleTrace ("SQL State: " + sqle.getSQLState ());
171     handleTrace ("Vendor code: " + sqle.getErrorCode ());
172     handleException (sqle);
173         return (null);
174       }
175 /*
176       finally {
177         if (myStatement != null) {
178           try {
179             myStatement.close ();
180           } catch (SQLException sqle) {
181           }
182         }
183       }
184 */
185
186  }
187
188   Object execute_update (String query) {
189     int cnt = 0;
190     Statement myStatement = null;
191
192       if (query == null)
193           return (null);
194
195       if (connection == null) {
196      handleError ("execute_query: no database connection");
197      return (null);
198       }
199
200       try {
201         myStatement = connection.createStatement ();
202         cnt = myStatement.executeUpdate (query);
203         myStatement.close ();
204         return new Integer (cnt);
205       }
206       catch(SQLException sqle)
207       {
208     handleTrace ("Message: " + sqle.getMessage ());
209     handleTrace ("SQL State: " + sqle.getSQLState ());
210     handleTrace ("Vendor code: " + sqle.getErrorCode ());
211     handleException (sqle);
212     return (null);
213       }
214
215  }
216
217   // show_output: show output of the query
218
219   int show_output (ResultSet res) {
220     
221     int ncol, n;
222     ResultSetMetaData meta;
223
224     if (res == null)
225     return (0);
226
227     try {
228     meta = res.getMetaData ();
229     }
230     catch (SQLException e) {
231     handleException (e);
232     return (0);
233     }
234
235
236  
237     try {
238         while (res.next ()) {
239     ncol = meta.getColumnCount ();
240         handleTrace ("output:ncol:"+ ncol);
241     for (n = 1; n <= ncol; n++) {
242         handleTrace ("output:" +
243             meta.getColumnName (n) + ":" +
244             res.getString (n));
245     }
246     }
247     return (1);
248     }
249     catch (SQLException e) {
250     handleException (e);
251     return (0);
252     }
253   }
254
255   // map: map database row onto object
256
257   int map (ResultSetMetaData meta,
258     ResultSet res, String obj_name) {
259     
260     int ncol, n;
261     DateFormat df = DateFormat.getDateInstance();
262
263     if ((meta == null) || (res == null) ||
264     (obj_name == null))
265     return (0);
266  
267     try {
268     ncol = meta.getColumnCount ();
269     for (n = 1; n <= ncol; n++) {
270           //handleTrace ("JtJDBCAdapter.map:" + meta.getColumnTypeName(n));
271 if (meta.getColumnTypeName(n).equals ("DATE") ||
272               meta.getColumnTypeName(n).equals ("DATETIME")) {
273             Date date = res.getDate (n);
274             if (date == null || date.equals (""))
275               continue; // check
276 String sdate = df.format (date);
277             this.setValue (obj_name,
278             meta.getColumnName (n),
279             sdate);
280             continue;
281           }
282       this.setValue (obj_name,
283             meta.getColumnName (n),
284             res.getString (n));
285 /*
286       handleTrace ("JtJDBCAdapter.map:" +
287                  meta.getColumnName (n) + ":" +
288                  res.getString (n));
289 */
290     }
291     
292     return (1);
293     }
294     catch (SQLException e) {
295     handleException (e);
296     return (0);
297     }
298   }
299
300   // map: map query onto object list. This method is being deprecated (see JtDAO)
301
302   void map_query (String query, JtMessage me) {
303     ResultSetMetaData mdata;
304     ResultSet res;
305     //int n = 0; - check memory leak
306 String name;
307     JtMessage event;
308         JtObject tmp;
309
310         //if (table == null)
311 //return;
312
313         handleTrace ("JtJDBCAdapter.map_query ....");
314
315     if (query == null)
316        return;
317
318     if (base == null)
319        return;
320
321         //query = "select * from " + table;
322
323         res = execute_query (query);
324
325     if (res == null) {
326            handleTrace ("map_query:res:null");
327            //myStatement.close ();
328 return;
329         }
330
331     try {
332        mdata = res.getMetaData ();
333     }
334     catch (SQLException e) {
335        handleException (e);
336        return;
337     }
338
339         try {
340       if (mdata == null) {
341             myStatement.close ();
342             //res.close (); // check
343 return;
344           }
345     } catch (SQLException e) {
346        handleException (e);
347        return;
348     }
349
350     try {
351     while (res.next ()) {
352        n++;
353        name = "" + n;
354            handleTrace ("creating object ..." + name);
355            tmp = (JtObject) this.createObject (base, name);
356        if (map (mdata, res, name) == 0)
357         continue;
358        event = new JtMessage ();
359            //event.setMsgContent (this.getObjName()+"."+name);
360 event.setMsgContent (tmp);
361            event.setMsgId ("JtOBJECT");
362            event.setMsgSubject (me.getMsgSubject ());
363        //event.setMsgSubject("JtMAP");
364
365
366            // send a reply
367 if (me.getMsgReplyTo () != null) {
368         // fireMessageEvent(event);
369 // else {
370 //event.setMsgIsReply (true);
371 this.sendMessage (me.getMsgReplyTo (), event);
372        }
373     
374     }
375         // res.close (); // check
376 myStatement.close ();
377         }
378     catch (SQLException e) {
379        handleException (e);
380     }
381 }
382
383   private PreparedStatement prepareStatement (String query)
384   {
385     PreparedStatement pst = null;
386
387     if (connection == null) {
388       handleError ("JtJDBCAdapter.prepareStatement: invalid connection (null)");
389       return (null);
390     }
391
392     if (query == null)
393      return (null);
394
395     try {
396       pst = connection.prepareStatement (query);
397     }
398     catch (Exception ex) {
399       handleException (ex);
400     }
401     return (pst);
402   }
403
404
405   private Object executePreparedUpdate (PreparedStatement pst )
406   {
407     int cnt = 0;
408     
409     if (pst == null) {
410       return (null);
411     }
412
413     if (connection == null) {
414       handleError ("JtJDBCAdapter.prepareStatement: invalid connection (null)");
415       return (null);
416     }
417     try {
418       cnt = pst.executeUpdate ();
419       return (new Integer (cnt));
420     } catch (Exception ex) {
421       handleException (ex);
422       return (ex);
423     }
424
425   }
426   
427   /**
428     * Process object messages.
429     * <ul>
430     * <li> JtCONNECT - Establishes a database connection using the appropriate object attributes
431     * (database driver, url, user/password if any).
432     * <li> JtEXECUTE_QUERY - Executes an SQL statement. Typically this is an SQL SELECT statement.
433     * Returns a ResultSet object. The message contains the query to be executed (msgContent).
434     * <li> JtEXECUTE_UPDATE - Executes an SQL INSERT, UPDATE, DELETE or DDL statement. Returns
435     * the row count (Integer) for INSERT, UPDATE or DELETE statements, or 0 for SQL statements
436     * that return nothing. The message contains the query to be executed (msgContent).
437     * <li> JtPREPARE_STATEMENT - Returns a prepared statement. The message contains the query (msgContent).
438     * <li> JtEXECUTE_PREPARED_UPDATE - Executes a prepared statement. The message contains the prepared statement
439     * to be executed. Returns the row count (Integer) for INSERT, UPDATE or DELETE statements, or 0 for SQL statements
440     * that return nothing.
441     * <li> JtCLOSE - Closes the database connection.
442     * </ul>
443     */
444
445   public Object processMessage (Object message) {
446   String content;
447   String query;
448   JtMessage e = (JtMessage) message;
449   Object reply;
450
451       if (e == null || (e.getMsgId() == null))
452           return (null);
453
454       if (!initted) {
455         initial ();
456         initted = true;
457       }
458
459       // establish connection
460 if (e.getMsgId().equals("JtCONNECT")) {
461     connect ();
462     return (connection);
463       }
464
465       // execute a query
466 if (e.getMsgId().equals("JtQUERY")) {
467         connect ();
468     query = (String ) e.getMsgContent();
469     show_output (execute_query (query));
470         //if (dataSource != null)
471 // close ();
472
473     return (null);
474       }
475
476       if (e.getMsgId().equals("JtEXECUTE_QUERY")) {
477     query = (String ) e.getMsgContent();
478         connect ();
479     reply = execute_query (query);
480         // When dealing with DataSources Close the connection after each operation
481 //if (dataSource != null)
482 // close ();
483 return (reply);
484
485       }
486
487
488       if (e.getMsgId().equals("JtPREPARE_STATEMENT")) {
489     query = (String ) e.getMsgContent();
490         connect ();
491     reply = prepareStatement (query);
492         //if (dataSource != null)
493 // close ();
494 return (reply);
495       }
496
497
498       if (e.getMsgId().equals("JtEXECUTE_PREPARED_UPDATE")) {
499     //query = (String) e.getMsgContent();
500 connect ();
501     reply = executePreparedUpdate
502          ((PreparedStatement) e.getMsgContent());
503         //if (dataSource != null)
504 // close ();
505 return (reply);
506       }
507
508       // execute an Update
509 if (e.getMsgId().equals("JtUPDATE")) {
510     query = (String ) e.getMsgContent();
511         connect ();
512     reply = execute_update (query);
513         //if (dataSource != null)
514 // close ();
515 return (reply);
516       }
517
518       // map query onto object list
519 if (e.getMsgId().equals("JtMAP")) {
520     query = (String ) e.getMsgContent();
521     if (query == null)
522        return (null);
523         connect ();
524     map_query (query, e);
525         //if (dataSource != null)
526 // close ();
527 return (null);
528       }
529
530       // close a connection
531 if (e.getMsgId().equals("JtCLOSE") || e.getMsgId().equals ("JtREMOVE")) {
532     close ();
533     return (null);
534       }
535       handleError
536     ("processMessage: invalid message id:"+
537         e.getMsgId());
538       return (null);
539   }
540
541   /**
542    * Specifies the database user.
543    * @param newUser user
544    */
545
546   public void setUser (String newUser) {
547     user = newUser;
548   }
549
550
551   /**
552    * Returns the database user.
553    */
554
555   public String getUser () {
556     return (user);
557   }
558
559
560   /**
561    * Specifies the database password.
562    * @param newPassword password
563    */
564
565   public void setPassword (String newPassword) {
566     password = newPassword;
567   }
568
569
570   /**
571    * Returns the database password.
572    */
573
574   public String getPassword () {
575     return (password);
576   }
577
578
579   /**
580    * Specifies the URL of the database to which to connect.
581    * @param newUrl url
582    */
583
584   public void setUrl (String newUrl) {
585     url = newUrl;
586   }
587
588   /**
589    * Returns the URL of the database.
590    */
591
592   public String getUrl () {
593     return (url);
594   }
595
596
597   /**
598    * Specifies the Datasource logical name (if any).
599    * @param datasource Datasource logical name
600    */
601
602   public void setDatasource (String datasource) {
603     this.datasource = datasource;
604   }
605
606   /**
607    * Returns the Datasource logical name (JNDI).
608    */
609
610   public String getDatasource () {
611     return (datasource);
612   }
613
614
615   /**
616    * Specifies the database driver.
617    * @param newDriver driver
618    */
619
620   public void setDriver (String newDriver) {
621     driver = newDriver;
622   }
623
624   /**
625    * Returns the database driver.
626    */
627   public String getDriver () {
628     return (driver);
629   }
630
631   /**
632    * Method being deprecated.
633    */
634
635   public void setBase (String newBase) {
636     base = newBase;
637   }
638
639   /**
640    * Method being deprecated.
641    */
642
643   public String getBase () {
644     return (base);
645   }
646
647
648   /**
649    * Specifies the database Connection.
650    */
651
652   public void setConnection (Connection newConnection) {
653     connection = newConnection;
654   }
655
656   /**
657    * Returns the database Connection.
658    */
659
660   public Connection getConnection () {
661     return (connection);
662   }
663
664
665   private static void test () {
666     JtObject main;
667     String query;
668
669     main = new JtObject ();
670     //main.setValue (main, "objTrace", "1");
671 main.createObject ("Jt.JtMessage", "message");
672
673     main.createObject ("Jt.JtJDBCAdapter", "database");
674     main.setValue ("database", "objTrace", "1");
675     //main.setValue ("message", "msgId", "JtCONNECT");
676
677     //main.sendMessage ("database", "message");
678
679
680     query = "select email from member where email='SUPPORT@FSW.COM';";
681
682     main.setValue ("message", "msgId", "JtQUERY");
683     main.setValue ("message", "msgContent", query);
684     main.sendMessage ("database", "message");
685     main.removeObject ("database");
686     main.removeObject ("message");
687
688   }
689
690   /**
691    * Unit tests the messages processed by JtJDBCAdapter. The following attributes should be
692    * included in the Jt resource file (.Jtrc):
693    * <ul>
694    * <li>Jt.JtJDBCAdapter.user
695    * <li>Jt.JtJDBCAdapter.password
696    * <li>Jt.JtJDBCAdapter.driver
697    * <li>Jt.JtJDBCAdapter.url
698    * <li>Jt.JtJDBCAdapter.datasource (if this attribute is present, the previous
699    * attributes are not needed)
700    * </ul>
701    */
702
703   public static void main (String [] args) {
704      test ();
705   }
706 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags