1 29 30 package com.caucho.quercus.lib.db; 31 32 import com.caucho.quercus.env.BooleanValue; 33 import com.caucho.quercus.env.Env; 34 import com.caucho.quercus.env.NullValue; 35 import com.caucho.quercus.env.UnsetValue; 36 import com.caucho.quercus.env.Value; 37 import com.caucho.util.L10N; 38 import com.caucho.util.Log; 39 40 import java.lang.reflect.Constructor ; 41 import java.lang.reflect.Method ; 42 import java.sql.Connection ; 43 import java.sql.PreparedStatement ; 44 import java.sql.ResultSet ; 45 import java.sql.ResultSetMetaData ; 46 import java.sql.SQLException ; 47 import java.sql.Types ; 48 import java.util.logging.Level ; 49 import java.util.logging.Logger ; 50 51 52 55 public class JdbcStatementResource { 56 private static final Logger log = Log.open(JdbcStatementResource.class); 57 private static final L10N L = new L10N(JdbcStatementResource.class); 58 59 private JdbcConnectionResource _conn; 60 private ResultSet _rs; 61 private String _query; 62 private PreparedStatement _stmt; 63 private ResultSetMetaData _metaData; 64 private JdbcResultResource _resultResource = null; 65 66 private char[] _types; 67 private Value[] _params; 68 private Value[] _results; 69 70 private String _errorMessage = ""; 71 private int _errorCode; 72 73 private String _stmtType; 76 77 83 public JdbcStatementResource(JdbcConnectionResource connV, 84 String query) 85 throws SQLException 86 { 87 _conn = connV; 88 prepareStatement(query); 89 } 90 91 96 public JdbcStatementResource(JdbcConnectionResource connV) 97 { 98 _conn = connV; 99 } 100 101 108 protected boolean bindParams(Env env, 109 String types, 110 Value[] params) 111 { 112 115 final int size = types.length(); 116 117 if (size != params.length) { 119 env.warning(L.l("number of types does not match number of parameters")); 120 return false; 121 } 122 123 for (int i = 0; i < size; i++) { 125 if ("idsb".indexOf(types.charAt(i)) < 0) { 126 env.warning(L.l("invalid type string {0}", types)); 127 return false; 128 } 129 } 130 131 _types = new char[size]; 132 _params = new Value[size]; 133 134 for (int i = 0; i < size; i++) { 135 _types[i] = types.charAt(i); 136 _params[i] = params[i]; 137 } 138 139 return true; 140 } 141 142 160 public boolean bindResults(Env env, 161 Value[] outParams) 162 { 163 int size = outParams.length; 164 int numColumns; 165 166 try { 167 ResultSetMetaData md = getMetaData(); 168 169 numColumns = md.getColumnCount(); 170 } catch (SQLException e) { 171 log.log(Level.FINE, e.toString(), e); 172 return false; 173 } 174 175 if (size != numColumns) { 176 env.warning(L.l("number of bound variables does not equal number of columns")); 177 return false; 178 } 179 180 _results = new Value[size]; 181 182 System.arraycopy(outParams, 0, _results, 0, size); 183 184 return true; 185 } 186 187 190 public void close() 191 { 192 try { 193 if (_rs != null) 194 _rs.close(); 195 196 if (_stmt != null) 197 _stmt.close(); 198 199 } catch (SQLException e) { 200 _errorMessage = e.getMessage(); 201 _errorCode = e.getErrorCode(); 202 log.log(Level.FINE, e.toString(), e); 203 } 204 } 205 206 212 protected boolean dataSeek(int offset) 213 { 214 return JdbcResultResource.setRowNumber(_rs, offset); 215 } 216 217 222 public int errorCode() 223 { 224 return _errorCode; 225 } 226 227 232 public String errorMessage() 233 { 234 return _errorMessage; 235 } 236 237 243 public boolean execute(Env env) 244 { 245 try { 246 if (_types != null) { 247 int size = _types.length; 248 for (int i = 0; i < size; i++) { 249 switch (_types[i]) { 250 case 'i': 251 _stmt.setInt(i + 1, _params[i].toInt()); 252 break; 253 case 'd': 254 _stmt.setDouble(i + 1, _params[i].toDouble()); 255 break; 256 case 'b': 259 _stmt.setString(i + 1, _params[i].toString()); 260 break; 261 case 's': 262 _stmt.setString(i + 1, _params[i].toString()); 263 break; 264 default: 265 break; 266 } 267 } 268 } 269 270 return executeStatement(); 271 272 } catch (SQLException e) { 273 env.warning(L.l(e.toString())); 274 log.log(Level.FINE, e.toString(), e); 275 _errorMessage = e.getMessage(); 276 _errorCode = e.getErrorCode(); 277 return false; 278 } 279 } 280 281 285 protected boolean executeStatement() 286 throws SQLException 287 { 288 try { 289 290 if (_stmt.execute()) { 291 _conn.setAffectedRows(0); 292 _rs = _stmt.getResultSet(); 293 } else { 294 _conn.setAffectedRows(_stmt.getUpdateCount()); 295 } 296 297 return true; 298 299 } catch (SQLException e) { 300 _errorMessage = e.getMessage(); 301 _errorCode = e.getErrorCode(); 302 throw e; 303 } 304 } 305 306 311 public Value fetch(Env env) 312 { 313 try { 314 if (_rs.next()) { 315 if (_metaData == null) 316 _metaData = _rs.getMetaData(); 317 318 int size = _results.length; 319 for (int i = 0; i < size; i++) { 320 _results[i].set(_resultResource.getColumnValue(env, _rs, _metaData, i + 1)); 321 } 322 return BooleanValue.TRUE; 323 } else 324 return BooleanValue.FALSE; 325 326 } catch (SQLException e) { 327 log.log(Level.FINE, e.toString(), e); 328 return NullValue.NULL; 329 } 330 } 331 332 337 public boolean freeResult() 338 { 339 if (_rs == null) 340 return true; 341 342 try { 343 _rs.close(); 344 _rs = null; 345 if (_resultResource != null) { 346 _resultResource.close(); 347 _resultResource = null; 348 } 349 return true; 350 } catch (SQLException e) { 351 _errorMessage = e.getMessage(); 352 _errorCode = e.getErrorCode(); 353 log.log(Level.FINE, e.toString(), e); 354 return false; 355 } 356 } 357 358 363 protected ResultSetMetaData getMetaData() 364 throws SQLException 365 { 366 if (_metaData == null) 367 _metaData = _rs.getMetaData(); 368 369 return _metaData; 370 } 371 372 377 public int getNumRows() 378 throws SQLException 379 { 380 if (_rs != null) 381 return JdbcResultResource.getNumRows(_rs); 382 else 383 return 0; 384 } 385 386 391 protected PreparedStatement getPreparedStatement() 392 { 393 return _stmt; 394 } 395 396 401 public JdbcResultResource getResultMetadata() 402 { 403 if (_resultResource != null) { 404 _resultResource.setFieldOffset(0); 405 return _resultResource; 406 } 407 408 if ((_stmt == null) || (_rs == null)) 409 return null; 410 411 _resultResource = new JdbcResultResource(_stmt, _rs, _conn); 412 return _resultResource; 413 } 414 415 420 protected ResultSet getResultSet() 421 { 422 return _rs; 423 } 424 425 429 protected Connection getJavaConnection() 430 throws SQLException 431 { 432 return validateConnection().getJavaConnection(); 433 } 434 435 441 public String getStatementType() 442 { 443 447 _stmtType = _query; 448 _stmtType = _stmtType.replaceAll("\\s+.*", ""); 449 if (_stmtType.equals("")) { 450 _stmtType = "UNKNOWN"; 451 } else { 452 _stmtType = _stmtType.toUpperCase(); 453 String s = _stmtType.replaceAll("(SELECT|UPDATE|DELETE|INSERT|CREATE|DROP|ALTER|BEGIN|DECLARE)", ""); 454 if (!s.equals("")) { 455 _stmtType = "UNKNOWN"; 456 } 457 } 458 459 return _stmtType; 460 } 461 462 467 public int paramCount() 468 { 469 if (_query == null) 470 return -1; 471 472 int count = 0; 473 int length = _query.length(); 474 boolean inQuotes = false; 475 char c; 476 477 for (int i = 0; i < length; i++) { 478 c = _query.charAt(i); 479 480 if (c == '\\') { 481 if (i < length - 1) 482 i++; 483 continue; 484 } 485 486 if (inQuotes) { 487 if (c == '\'') 488 inQuotes = false; 489 continue; 490 } 491 492 if (c == '\'') { 493 inQuotes = true; 494 continue; 495 } 496 497 if (c == '?') { 498 count++; 499 } 500 } 501 502 return count; 503 } 504 505 511 public boolean prepare(String query) 512 { 513 try { 514 515 if (_stmt != null) 516 _stmt.close(); 517 518 _query = query; 519 520 if (this instanceof OracleStatement) { 521 _stmt = _conn.getConnection().prepareCall(query, 522 ResultSet.TYPE_SCROLL_INSENSITIVE, 523 ResultSet.CONCUR_READ_ONLY); 524 } else { 525 _stmt = _conn.getConnection().prepareStatement(query, 526 ResultSet.TYPE_SCROLL_INSENSITIVE, 527 ResultSet.CONCUR_READ_ONLY); 528 } 529 530 return true; 531 532 } catch (SQLException e) { 533 log.log(Level.FINE, e.toString(), e); 534 _errorMessage = e.getMessage(); 535 _errorCode = e.getErrorCode(); 536 return false; 537 } 538 } 539 540 546 public boolean prepareStatement(String query) 547 { 548 try { 549 550 if (_stmt != null) 551 _stmt.close(); 552 553 _query = query; 554 555 if (this instanceof OracleStatement) { 556 _stmt = _conn.getConnection().prepareCall(query, 557 ResultSet.TYPE_SCROLL_INSENSITIVE, 558 ResultSet.CONCUR_READ_ONLY); 559 } else { 560 _stmt = _conn.getConnection().prepareStatement(query, 561 ResultSet.TYPE_SCROLL_INSENSITIVE, 562 ResultSet.CONCUR_READ_ONLY); 563 } 564 565 return true; 566 567 } catch (SQLException e) { 568 log.log(Level.FINE, e.toString(), e); 569 _errorMessage = e.getMessage(); 570 _errorCode = e.getErrorCode(); 571 return false; 572 } 573 } 574 575 579 protected Value getParam(int i) 580 { 581 if (i >= _params.length) 582 return UnsetValue.UNSET; 583 584 return _params[i]; 585 } 586 587 591 protected int getParamLength() 592 { 593 return _params.length; 594 } 595 596 599 protected void setPreparedStatement(PreparedStatement stmt) 600 { 601 _stmt = stmt; 602 } 603 604 607 protected void setResultSet(ResultSet rs) 608 { 609 _rs = rs; 610 } 611 612 616 protected void setObject(int i, Object param) 617 throws Exception 618 { 619 try { 620 java.sql.ParameterMetaData pmd = _stmt.getParameterMetaData(); 622 int type = pmd.getParameterType(i); 623 624 switch (type) { 625 626 case Types.OTHER: 627 { 628 String typeName = pmd.getParameterTypeName(i); 630 if (typeName.equals("interval")) { 631 _stmt.setObject(i, param); 632 } else { 633 Class cl = Class.forName("org.postgresql.util.PGobject"); 634 Constructor constructor = cl.getDeclaredConstructor(null); 635 Object object = constructor.newInstance(); 636 637 Method method = cl.getDeclaredMethod("setType", new Class [] {String .class}); 638 method.invoke(object, new Object [] {typeName}); 639 640 method = cl.getDeclaredMethod("setValue", new Class [] {String .class}); 641 method.invoke(object, new Object [] {param}); 642 643 _stmt.setObject(i, object, type); 644 } 645 break; 646 } 647 648 case Types.DOUBLE: 649 { 650 String typeName = pmd.getParameterTypeName(i); 652 if (typeName.equals("money")) { 653 String s = param.toString(); 654 655 if (s.length() == 0) { 656 throw new IllegalArgumentException (L.l("argument `{0}' cannot be empty", param)); 657 } else { 658 659 String money = s; 660 661 if (s.charAt(0) == '$') 662 s = s.substring(1); 663 else 664 money = "$"+money; 665 666 try { 667 Double.parseDouble(s); 671 } catch (Exception ex) { 672 throw new IllegalArgumentException (L.l("cannot convert argument `{0}' to money", param)); 673 } 674 675 Class cl = Class.forName("org.postgresql.util.PGmoney"); 676 Constructor constructor = cl.getDeclaredConstructor(new Class [] {String .class}); 677 Object object = constructor.newInstance(new Object [] {money}); 678 679 _stmt.setObject(i, object, Types.OTHER); 680 681 break; 682 } 683 } 684 } 686 687 default: 688 _stmt.setObject(i, param, type); 689 } 690 } 691 catch (SQLException e) { 692 _errorMessage = e.getMessage(); 693 _errorCode = e.getErrorCode(); 694 throw e; 695 } 696 catch (Exception e) { 697 _stmt.clearParameters(); 698 throw e; 699 } 700 } 701 702 707 public String toString() 708 { 709 return getClass().getName() + "[" + _conn + "]"; 710 } 711 712 717 public JdbcConnectionResource validateConnection() 718 { 719 return _conn; 720 } 721 } 722 723 | Popular Tags |