JtSQL


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