1 2 3 package Jt; 4 import java.io.*; 5 import java.sql.*; 6 import java.text.*; 7 8 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 } 52 catch(ClassNotFoundException cnfe) 53 { 54 handleException (cnfe); 55 } 56 catch(SQLException sqle) 57 { 58 handleException (sqle); 59 } 60 } 61 62 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; } 78 } 79 80 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) myStatement.close (); 98 99 return (results); 101 } 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 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 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 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 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; 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 226 } 227 228 return (1); 229 } 230 catch (SQLException e) { 231 handleException (e); 232 return (0); 233 } 234 } 235 236 238 void map_query (String query, JtMessage me) { 239 ResultSetMetaData mdata; 240 ResultSet res; 241 String name; 243 JtMessage event; 244 JtObject tmp; 245 246 249 handleTrace ("JtSQL.map_query ...."); 250 251 if (query == null) 252 return; 253 254 if (base == null) 255 return; 256 257 259 res = execute_query (query); 260 261 if (res == null) { 262 handleTrace ("map_query:res:null"); 263 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 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 (tmp); 297 event.setMsgId ("JtOBJECT"); 298 event.setMsgSubject (me.getMsgSubject ()); 299 301 302 if (me.getMsgReplyTo () != null) { 304 this.sendMessage (me.getMsgReplyTo (), event); 308 } 309 310 } 311 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 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 if (e.getMsgId().equals("JtCONNECT")) { 391 connect (); 392 return (connection); 393 } 394 395 if (e.getMsgId().equals("JtQUERY")) { 397 query = (String ) e.getMsgContent(); 398 show_output (execute_query (query)); 399 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 return (executePreparedUpdate 418 ((PreparedStatement) e.getMsgContent())); 419 } 420 421 if (e.getMsgId().equals("JtUPDATE")) { 423 query = (String ) e.getMsgContent(); 424 return (execute_update (query)); 425 } 426 427 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 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 451 452 public void setUser (String newUser) { 453 user = newUser; 454 } 455 456 457 460 461 public String getUser () { 462 return (user); 463 } 464 465 466 470 471 public void setPassword (String newPassword) { 472 password = newPassword; 473 } 474 475 476 479 480 public String getPassword () { 481 return (password); 482 } 483 484 485 489 490 public void setUrl (String newUrl) { 491 url = newUrl; 492 } 493 494 497 498 public String getUrl () { 499 return (url); 500 } 501 502 503 507 508 public void setDriver (String newDriver) { 509 driver = newDriver; 510 } 511 512 515 public String getDriver () { 516 return (driver); 517 } 518 519 522 523 public void setBase (String newBase) { 524 base = newBase; 525 } 526 527 530 531 public String getBase () { 532 return (base); 533 } 534 535 536 539 540 public void setConnection (Connection newConnection) { 541 connection = newConnection; 542 } 543 544 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.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.sendMessage ("database", "message"); 567 568 569 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 590 591 public static void main (String [] args) { 592 test (); 593 } 594 }
| Popular Tags
|