001 /* 002 @license.text@ 003 */ 004 package biz.hammurapi.sql; 005 006 import java.io.IOException; 007 import java.io.InputStream; 008 import java.io.InputStreamReader; 009 import java.io.Reader; 010 import java.io.StringWriter; 011 import java.sql.Connection; 012 import java.sql.DriverManager; 013 import java.sql.PreparedStatement; 014 import java.sql.ResultSet; 015 import java.sql.ResultSetMetaData; 016 import java.sql.SQLException; 017 import java.sql.Statement; 018 import java.util.ArrayList; 019 import java.util.Collection; 020 import java.util.HashMap; 021 import java.util.Map; 022 import java.util.Properties; 023 024 import javax.sql.DataSource; 025 026 import biz.hammurapi.config.ConfigurationException; 027 import biz.hammurapi.config.Context; 028 import biz.hammurapi.config.DomConfigFactory; 029 import biz.hammurapi.config.MapContext; 030 import biz.hammurapi.config.PropertyParser; 031 import biz.hammurapi.metrics.MeasurementCategoryFactory; 032 import biz.hammurapi.metrics.TimeIntervalCategory; 033 import biz.hammurapi.sql.hypersonic.HypersonicInMemoryDataSource; 034 import biz.hammurapi.util.ExceptionSink; 035 036 037 /** 038 * This class contains methods to process SQL statements in more convenient 039 * way comparing to standard JDBC. 040 * @author Pavel Vlasov 041 * @version $Revision: 1.14 $ 042 */ 043 public class SQLProcessor { 044 private DataSource dataSource; 045 private Connection connection; 046 private PropertyParser propertyParser; 047 private TimeIntervalCategory timeIntervalCategory=MeasurementCategoryFactory.getTimeIntervalCategory(SQLProcessor.class); 048 private Context nameMap; 049 050 /** 051 * If SQLProcessor constructed with this constructor 052 * then it obtains connection from the datasource, processes 053 * request and closes connection. 054 * @param dataSource DataSource 055 * @param nameMap NameMap allows to write parameterized SQL statements 056 * like "SELECT ${AMOUNT} FROM ${ACCOUNT} WHERE ${ANUM}=? AND CLOSED=1" 057 * nameMap shall contain mapping from AMOUNT, ACCOUNT and ANUM to actual 058 * database field names. If nameMap doesn't contain mapping for some 059 * properties then property names will be used as property values. 060 * See {@link biz.hammurapi.config.PropertyParser}. 061 * One property value can contain a reference to another property. 062 * If nameMap is null then no property parsing will happen. 063 */ 064 public SQLProcessor(DataSource dataSource, Context nameMap) { 065 this(nameMap); 066 this.dataSource=dataSource; 067 } 068 069 /** 070 * @param nameMap 071 */ 072 private SQLProcessor(Context nameMap) { 073 super(); 074 this.nameMap=nameMap; 075 if (nameMap!=null) { 076 propertyParser=new PropertyParser(nameMap, true); 077 } 078 } 079 080 /** 081 * If SQLProcessor created with this constructor then is doesn't 082 * close the connection after processing. 083 * @param connection 084 * @param nameMap See {@link biz.hammurapi.sql.SQLProcessor#SQLProcessor(DataSource, Properties)} 085 */ 086 public SQLProcessor(Connection connection, Context nameMap) { 087 this(nameMap); 088 this.connection=connection; 089 } 090 091 /** 092 * Replaces ${<property name>} with property value. See {@link biz.hammurapi.config.PropertyParser} 093 * @param str 094 * @return parsed string 095 */ 096 public String parse(String str) { 097 if (propertyParser==null) { 098 return str; 099 } 100 101 return propertyParser.parse(str); 102 } 103 104 /** 105 * Returns connection if you need it for JDBC calls outside of the SQLProcessor 106 * @return Connection 107 * @throws SQLException 108 */ 109 public Connection getConnection() throws SQLException { 110 return connection==null ? dataSource==null ? null : dataSource.getConnection() : connection; 111 } 112 113 /** 114 * Closes connection if it was provided by DataSource. Does nothing otherwise. 115 * @param connection Connection to release. 116 * @throws SQLException 117 */ 118 public void releaseConnection(Connection connection) throws SQLException { 119 if (this.connection==null && connection!=null) { 120 connection.close(); 121 } 122 } 123 124 /** 125 * Processes SQL SELECT statement in the following way: 126 * <UL> 127 * <li>Obtains connection</li> 128 * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li> 129 * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li> 130 * <li>Iterates through result set and invokes rowProcessor.process() on each row</li> 131 * <li>If there was no rows and rowProcess is instance of {@link RowProcessorEx} then rowProcessor.onEmptyResultSet() is invoked</li> 132 * <li>ResultSet, Statement and connection are properly released</li> 133 * </UL> 134 * 135 * @param sql SQL statment to execute 136 * @param parameterizer Parameterizer 137 * @param rowProcessor RowProcessor 138 * @throws SQLException 139 */ 140 public void processSelect(String sql, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException { 141 Connection con=getConnection(); 142 try { 143 processSelect(con, sql, parameterizer, rowProcessor); 144 } finally { 145 releaseConnection(con); 146 } 147 } 148 149 /** 150 * @param con 151 * @param sql 152 * @param parameterizer 153 * @param rowProcessor 154 * @param releaseConnection 155 * @throws SQLException 156 */ 157 private void processSelect(Connection con, String sql, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException { 158 long start = timeIntervalCategory==null ? 0 : timeIntervalCategory.getTime(); 159 if (parameterizer==null) { 160 Statement statement=con.createStatement(); 161 try { 162 ResultSet rs=statement.executeQuery(parse(sql)); 163 if (propertyParser!=null) { 164 rs = new ResultSetProxy(this, rs); 165 } 166 167 try { 168 if (rowProcessor instanceof MetadataAwareRowProcessor) { 169 ((MetadataAwareRowProcessor) rowProcessor).processMetadata(rs.getMetaData()); 170 } 171 172 boolean isEmpty=true; 173 174 while (rs.next()) { 175 isEmpty=false; 176 if (!rowProcessor.process(rs)) { 177 break; 178 } 179 } 180 181 if (isEmpty && rowProcessor instanceof RowProcessorEx) { 182 ((RowProcessorEx) rowProcessor).onEmptyResultSet(); 183 } 184 } finally { 185 rs.close(); 186 } 187 } catch (SQLException e) { 188 throw new SQLExceptionEx("Failed to execute statement: "+sql, e); 189 } finally { 190 statement.close(); 191 } 192 } else { 193 PreparedStatement statement=con.prepareStatement(parse(sql)); 194 parameterizer.parameterize(statement); 195 try { 196 ResultSet rs=statement.executeQuery(); 197 try { 198 if (rowProcessor instanceof MetadataAwareRowProcessor) { 199 ((MetadataAwareRowProcessor) rowProcessor).processMetadata(rs.getMetaData()); 200 } 201 202 boolean isEmpty=true; 203 204 while (rs.next()) { 205 isEmpty=false; 206 if (!rowProcessor.process(propertyParser==null ? rs : new ResultSetProxy(this, rs))) { 207 break; 208 } 209 } 210 211 if (isEmpty && rowProcessor instanceof RowProcessorEx) { 212 ((RowProcessorEx) rowProcessor).onEmptyResultSet(); 213 } 214 } finally { 215 rs.close(); 216 } 217 } catch (SQLException e) { 218 throw new SQLExceptionEx("Failed to execute statement: "+sql, e); 219 } finally { 220 statement.close(); 221 } 222 } 223 if (timeIntervalCategory!=null) { 224 timeIntervalCategory.addInterval(sql, start); 225 } 226 } 227 228 /** 229 * Processes SQL INSERT, UPDATE or DELETE statement in the following way: 230 * <UL> 231 * <li>Obtains connection</li> 232 * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li> 233 * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li> 234 * <li>Executes update</li> 235 * <li>ResultSet, Statement and connection are properly released</li> 236 * </UL> 237 * 238 * @param sql SQL statment to execute 239 * @param parameterizer Parameterizer 240 * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)} 241 * @throws SQLException 242 */ 243 public int processUpdate(String sql, Parameterizer parameterizer) throws SQLException { 244 Connection con=getConnection(); 245 try { 246 return processUpdate(con, sql, parameterizer); 247 } finally { 248 releaseConnection(con); 249 } 250 } 251 252 /** 253 * @param con 254 * @param sql 255 * @param parameterizer 256 * @return 257 * @throws SQLException 258 */ 259 private int processUpdate(Connection con, String sql, Parameterizer parameterizer) throws SQLException { 260 long start = timeIntervalCategory==null ? 0 : timeIntervalCategory.getTime(); 261 String parsedSql = parse(sql); 262 try { 263 if (parameterizer==null) { 264 Statement statement=con.createStatement(); 265 try { 266 return statement.executeUpdate(parsedSql); 267 } catch (SQLException e) { 268 throw new SQLExceptionEx("Failed to execute statement: "+parsedSql, e); 269 } finally { 270 statement.close(); 271 } 272 } 273 274 PreparedStatement statement=con.prepareStatement(parsedSql); 275 try { 276 parameterizer.parameterize(statement); 277 return statement.executeUpdate(); 278 } catch (SQLException e) { 279 throw new SQLExceptionEx("Failed to execute statement: "+parsedSql, e); 280 } finally { 281 statement.close(); 282 } 283 } finally { 284 if (timeIntervalCategory!=null) { 285 timeIntervalCategory.addInterval(parsedSql, start); 286 } 287 } 288 } 289 290 /** 291 * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements 292 * maintained by other developers. 293 * @param resourceName 294 * @param parameterizer 295 * @param rowProcessor 296 * @throws SQLException 297 * @throws IOException 298 */ 299 public void processResourceSelect(String resourceName, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException, IOException { 300 processSelect(resourceToString(resourceName), parameterizer, rowProcessor); 301 } 302 303 /** 304 * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements 305 * maintained by other developers. 306 * @param resourceName 307 * @param parameterizer 308 * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)} 309 * @throws SQLException 310 * @throws IOException 311 */ 312 public int processResourceUpdate(String resourceName, Parameterizer parameterizer) throws SQLException, IOException { 313 return processUpdate(resourceToString(resourceName), parameterizer); 314 } 315 316 private String resourceToString(String resourceName) throws IOException { 317 InputStream in=getClass().getClassLoader().getResourceAsStream(resourceName); 318 if (in==null) { 319 return null; 320 } 321 322 StringWriter sw=new StringWriter(); 323 try { 324 char[] buf=new char[1024]; 325 Reader r=new InputStreamReader(in); 326 try { 327 int len; 328 while((len=r.read(buf))!=-1) { 329 sw.write(buf, 0, len); 330 } 331 } finally { 332 r.close(); 333 } 334 } finally { 335 sw.close(); 336 } 337 return sw.toString(); 338 } 339 340 /** 341 * Excecutes script with ; as statement separator 342 * @param reader Script source 343 * @throws IOException 344 * @throws SQLException 345 * @see SQLProcessor#executeScript(Reader, char) 346 */ 347 public void executeScript(Reader reader) throws IOException, SQLException { 348 executeScript(reader, ';'); 349 } 350 351 /** 352 * Executes series of SQL statement read from reader and separated by statementSeparator 353 * @param reader Script source 354 * @param statementSeparator Statement separator 355 * @throws IOException 356 * @throws SQLException 357 */ 358 public void executeScript(Reader reader, char statementSeparator) throws IOException, SQLException { 359 executeScript(reader, statementSeparator, null); 360 } 361 362 /** 363 * 364 * @param reader Script source 365 * @param statementSeparator Statement separator 366 * @param exceptionSink Exception sink. Consumes exceptions thrown by individual statements. If sink is null then exception is 367 * rethrown and script execution terminates. 368 * @throws IOException 369 * @throws SQLException 370 */ 371 public void executeScript(Reader reader, char statementSeparator, ExceptionSink exceptionSink) throws IOException, SQLException { 372 if (reader!=null) { 373 Connection con=getConnection(); 374 try { 375 Statement stmt=con.createStatement(); 376 try { 377 try { 378 StringBuffer sb=new StringBuffer(); 379 int ch; 380 while ((ch=reader.read())!=-1) { 381 if (ch==statementSeparator) { 382 // Double separator is replaced with one e.g. ;; -> ; 383 int nextCh=reader.read(); 384 if (nextCh==-1) { 385 break; 386 } else if (nextCh==statementSeparator) { 387 sb.append((char) nextCh); 388 } else { 389 executeBuffer(stmt, sb, exceptionSink); 390 sb=new StringBuffer(); 391 sb.append((char) nextCh); 392 } 393 } else { 394 sb.append((char) ch); 395 } 396 } 397 executeBuffer(stmt, sb, exceptionSink); 398 } finally { 399 reader.close(); 400 } 401 } finally { 402 stmt.close(); 403 } 404 } finally { 405 releaseConnection(con); 406 } 407 } 408 } 409 410 /** 411 * @param stmt 412 * @param sb 413 * @throws SQLException 414 */ 415 private void executeBuffer(Statement stmt, StringBuffer sb, ExceptionSink exceptionSink) throws SQLException { 416 String sql=sb.toString().trim(); 417 if (sql.length()!=0) { 418 try { 419 stmt.execute(sql); 420 } catch (SQLException e) { 421 if (exceptionSink == null) { 422 throw new SQLExceptionEx("SQL: "+sql, e); 423 } else { 424 exceptionSink.consume(sql, e); 425 } 426 } 427 } 428 } 429 430 /** 431 * Executes SQL statement and returns collection backed by the database. 432 * @param sql Select statement to execute 433 * @param parameterizer Parameterizer 434 * @param projector Projector which instantiates objects. It is null then projector 435 * which projects row to collection will be used. 436 * @return Collection backed by the database. The collection doesn't hold 437 * any SQL resources open, neither it keeps any refernces to created objects. 438 * It keeps only references to the SQLProcessor, parameterizer 439 * and projector. Thus any call to one of collection methods retursn 'fresh' 440 * results from the database. 441 * 442 * Iterators created by this collection open ResultSet and close it when 443 * Iterator.hasNext() returns false. 444 */ 445 public Collection project(String sql, Parameterizer parameterizer, Projector projector) { 446 return new ResultSetCollection(this, parse(sql), parameterizer, projector==null ? defaultProjector : projector); 447 } 448 449 /** 450 * Executes SQL statement and returns collection backed by the database. 451 * Rows are projected to collection of field values. 452 * @param sql Select statement to execute 453 * @param parameterizer Parameterizer 454 * @param projector Projector which instantiates objects. It is null then projector 455 * which projects row to collection will be used. 456 * @return Collection backed by the database. The collection doesn't hold 457 * any SQL resources open, neither it keeps any refernces to created objects. 458 * It keeps only references to the SQLProcessor and parameterizer. Thus any call to one of collection methods retursn 'fresh' 459 * results from the database. 460 * 461 * Iterators created by this collection open ResultSet and close it when 462 * Iterator.hasNext() returns false. 463 */ 464 public Collection project(String sql, Parameterizer parameterizer) { 465 return new ResultSetCollection(this, parse(sql), parameterizer, defaultProjector); 466 } 467 468 /** 469 * Executes SQL statement and returns collection backed by the database. 470 * @param sql Select statement to execute 471 * @return Collection backed by the database. The collection doesn't hold 472 * any SQL resources open, neither it keeps any refernces to created objects. 473 * It keeps only references to the SQLProcessor. 474 * Thus any call to one of collection methods retursn 'fresh' 475 * results from the database. 476 * 477 * Iterators created by this collection open ResultSet and close it when 478 * Iterator.hasNext() returns false. 479 */ 480 public Collection project(String sql) { 481 return new ResultSetCollection(this, parse(sql), null, defaultProjector); 482 } 483 484 /** 485 * Executes SQL statement and returns collection backed by the database. 486 * @param sql Select statement to execute 487 * @param parameterizer Parameterizer 488 * @param theInterface Iterface to implement 489 * @return Collection backed by the database. The collection doesn't hold 490 * any SQL resources open, neither it keeps any refernces to created objects. 491 * It keeps only references to the SQLProcessor, parameterizer 492 * and projector. Thus any call to one of collection methods retursn 'fresh' 493 * results from the database. 494 * 495 * Iterators created by this collection open ResultSet and close it when 496 * Iterator.hasNext() returns false. 497 */ 498 public Collection project(String sql, Parameterizer parameterizer, Class theInterface) { 499 return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, null)); 500 } 501 502 /** 503 * Executes SQL statement and returns collection backed by the database. 504 * @param sql Select statement to execute 505 * @param parameterizer Parameterizer 506 * @param theInterface Interface to implement 507 * @return Collection backed by the database. The collection doesn't hold 508 * any SQL resources open, neither it keeps any refernces to created objects. 509 * It keeps only references to the SQLProcessor, parameterizer 510 * and projector. Thus any call to one of collection methods retursn 'fresh' 511 * results from the database. 512 * 513 * Iterators created by this collection open ResultSet and close it when 514 * Iterator.hasNext() returns false. 515 */ 516 public Collection project(String sql, Parameterizer parameterizer, Class theInterface, Object delegate) { 517 return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, delegate, null)); 518 } 519 520 /** 521 * Executes SQL statement and returns collection backed by the database. 522 * @param sql Select statement to execute 523 * @param parameterizer Parameterizer 524 * @param projector Projector which instantiates objects. If it is null 525 * then projector which projects row to collection will be used 526 * @param pageSize Maximum number of records to return 527 * @param pageNum Number of page. Starts with 1. 528 * @return Collection backed by the database. The collection doesn't hold 529 * any SQL resources open, neither it keeps any refernces to created objects. 530 * It keeps only references to the SQLProcessor, parameterizer 531 * and projector. Thus any call to one of collection methods retursn 'fresh' 532 * results from the database. 533 * 534 * Iterators created by this collection open ResultSet and close it when 535 * Iterator.hasNext() returns false. 536 */ 537 public Collection project(String sql, Parameterizer parameterizer, Projector projector, int pageNum, int pageSize) { 538 return new ResultSetCollection(this, parse(sql), parameterizer, projector==null ? defaultProjector : projector, pageNum, pageSize); 539 } 540 541 /** 542 * Executes SQL statement and returns collection backed by the database. 543 * @param sql Select statement to execute 544 * @param parameterizer Parameterizer 545 * @param theInterface Interface to implement 546 * @param pageSize Maximum number of records to return 547 * @param pageNum Number of page. Starts with 1. 548 * @return Collection backed by the database. The collection doesn't hold 549 * any SQL resources open, neither it keeps any refernces to created objects. 550 * It keeps only references to the SQLProcessor, parameterizer 551 * and projector. Thus any call to one of collection methods retursn 'fresh' 552 * results from the database. 553 * 554 * Iterators created by this collection open ResultSet and close it when 555 * Iterator.hasNext() returns false. 556 */ 557 public Collection project(String sql, Parameterizer parameterizer, Class theInterface, int pageNum, int pageSize) { 558 return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, null), pageNum, pageSize); 559 } 560 561 /** 562 * Executes SQL statement and returns collection backed by the database. 563 * @param sql Select statement to execute 564 * @param parameterizer Parameterizer 565 * @param theInterface Interface to implement 566 * @param delegate Object to delegate invocations which didn't match field getters/setters 567 * @param pageSize Maximum number of records to return 568 * @param pageNum Number of page. Starts with 1. 569 * @return Collection backed by the database. The collection doesn't hold 570 * any SQL resources open, neither it keeps any refernces to created objects. 571 * It keeps only references to the SQLProcessor, parameterizer 572 * and projector. Thus any call to one of collection methods retursn 'fresh' 573 * results from the database. 574 * 575 * Iterators created by this collection open ResultSet and close it when 576 * Iterator.hasNext() returns false. 577 */ 578 public Collection project(String sql, Parameterizer parameterizer, Class theInterface, Object delegate, int pageNum, int pageSize) { 579 return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, delegate, null), pageNum, pageSize); 580 } 581 582 /** 583 * Executes SQL statement and puts results to receiver 584 * @param sql Select statement to execute 585 * @param parameterizer Parameterizer 586 * @param projector Projector which instantiates objects. If it is null then 587 * projector which projects row to collection will be used 588 * @param receiver Collection to put results to 589 * @return receiver with added objects. Convenient for calls like 590 * Iterator it=processor.project(..., new LinkedList()); 591 * @throws SQLException 592 */ 593 public Collection project(final String sql, final Parameterizer parameterizer, final Projector projector, final Collection receiver) throws SQLException { 594 processSelect( 595 sql, 596 parameterizer, 597 new RowProcessor() { 598 public boolean process(ResultSet rs) throws SQLException { 599 Object o = (projector==null ? defaultProjector : projector).project(rs); 600 if (o instanceof DataAccessObject) { 601 ((DataAccessObject) o).setSQLProcessor(SQLProcessor.this); 602 } 603 receiver.add(o); 604 return true; 605 } 606 }); 607 608 return receiver; 609 } 610 611 /** 612 * Executes SQL statement and puts results to receiver 613 * @param sql Select statement to execute 614 * @param parameterizer Parameterizer 615 * @param theInterface Interface to implement 616 * @param receiver Collection to put results to 617 * @return receiver with added objects. Convenient for calls like 618 * Iterator it=processor.project(..., new LinkedList()); 619 * @throws SQLException 620 */ 621 public Collection project(final String sql, final Parameterizer parameterizer, final Class theInterface, final Collection receiver) throws SQLException { 622 final Projector projector=new InterfaceProjector(theInterface, null); 623 processSelect( 624 sql, 625 parameterizer, 626 new RowProcessor() { 627 public boolean process(ResultSet rs) throws SQLException { 628 Object o = projector.project(rs); 629 if (o instanceof DataAccessObject) { 630 ((DataAccessObject) o).setSQLProcessor(SQLProcessor.this); 631 } 632 receiver.add(o); 633 return true; 634 } 635 }); 636 637 return receiver; 638 } 639 640 /** 641 * Executes SQL statement and puts results to receiver 642 * @param sql Select statement to execute 643 * @param parameterizer Parameterizer 644 * @param theInterface Interface to implement 645 * @param delegate Object to delegate invocations which didn't match field getters/setters. 646 * @param receiver Collection to put results to 647 * @return receiver with added objects. Convenient for calls like 648 * Iterator it=processor.project(..., new LinkedList()); 649 * @throws SQLException 650 */ 651 public Collection project(final String sql, final Parameterizer parameterizer, final Class theInterface, Object delegate, final Collection receiver) throws SQLException { 652 final Projector projector=new InterfaceProjector(theInterface, delegate, null); 653 processSelect( 654 sql, 655 parameterizer, 656 new RowProcessor() { 657 public boolean process(ResultSet rs) throws SQLException { 658 Object o = projector.project(rs); 659 if (o instanceof DataAccessObject) { 660 ((DataAccessObject) o).setSQLProcessor(SQLProcessor.this); 661 } 662 receiver.add(o); 663 return true; 664 } 665 }); 666 667 return receiver; 668 } 669 670 671 /** 672 * Executes SQL statement and puts results to receiver 673 * @param sql Select statement to execute 674 * @param parameterizer Parameterizer 675 * @param projector Projector which instantiates objects. If it is null then 676 * projector which projects row to collection will be used. 677 * @param receiver Collection to put results to 678 * @param pageSize Maximum number of records to return 679 * @param pageNum Number of page. Starts with 1. 680 * @return receiver with added objects. Convenient for calls like 681 * Iterator it=processor.project(..., new LinkedList()); 682 * @throws SQLException 683 */ 684 public Collection project(final String sql, final Parameterizer parameterizer, final Projector projector, final Collection receiver, final int pageSize, final int pageNum) throws SQLException { 685 final int[] counter={0}; 686 687 processSelect( 688 sql, 689 parameterizer, 690 new RowProcessor() { 691 public boolean process(ResultSet rs) throws SQLException { 692 if (++counter[0]>(pageNum-1)*pageSize && counter[0]<=pageNum*pageSize) { 693 Object o = (projector==null ? defaultProjector : projector).project(rs); 694 if (o instanceof DataAccessObject) { 695 ((DataAccessObject) o).setSQLProcessor(SQLProcessor.this); 696 } 697 receiver.add(o); 698 } 699 return true; 700 } 701 }); 702 703 return receiver; 704 } 705 706 private Projector defaultProjector=new Projector() { 707 public Object project(ResultSet rs) throws SQLException { 708 Collection ret=new ArrayList(); 709 for (int i=1, j=rs.getMetaData().getColumnCount(); i <= j; i++) { 710 ret.add(rs.getObject(i)); 711 } 712 return ret; 713 } 714 }; 715 716 /** 717 * @param string 718 * @param parameterizer 719 * @param projector Projector. If it is null then projector which projects 720 * row to collection will be used. 721 * @return 722 * @throws SQLException 723 */ 724 public Object projectSingleObject(String sql, Parameterizer parameterizer, final Projector projector) throws SQLException { 725 final Object[] ret={null}; 726 processSelect( 727 sql, 728 parameterizer, 729 new RowProcessor() { 730 public boolean process(ResultSet rs) throws SQLException { 731 ret[0] = (projector==null ? defaultProjector : projector).project(rs); 732 if (ret[0] instanceof DataAccessObject) { 733 ((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this); 734 } 735 return false; 736 } 737 }); 738 739 return ret[0]; 740 } 741 742 /** 743 * Executes query and injects values from the first row to target object. 744 * @param string 745 * @param parameterizer 746 * @param target Object to inject values to 747 * @throws SQLException 748 */ 749 public void inject(String sql, Parameterizer parameterizer, final Map columnMap, final Object target) throws SQLException { 750 processSelect( 751 sql, 752 parameterizer, 753 new RowProcessor() { 754 public boolean process(ResultSet rs) throws SQLException { 755 ResultSetMetaData metaData = rs.getMetaData(); 756 Map contextMap=new HashMap(); 757 for (int i=1, cc=metaData.getColumnCount(); i<=cc; i++) { 758 String colName=metaData.getColumnName(i); 759 String propertyName=BaseReflectionProjector.propertyName(colName); 760 761 if (columnMap!=null && columnMap.containsKey(propertyName)) { 762 propertyName=(String) columnMap.get(propertyName); 763 } 764 765 if (propertyName!=null) { 766 contextMap.put(propertyName, rs.getObject(colName)); 767 } 768 } 769 770 try { 771 DomConfigFactory.inject(target, new MapContext(contextMap)); 772 } catch (ConfigurationException e) { 773 throw new SQLExceptionEx(e); 774 } 775 return false; 776 } 777 }); 778 } 779 780 /** 781 * @param sql 782 * @param parameterizer 783 * @return Object representing first row 784 * @throws SQLException 785 */ 786 public Object projectSingleObject(String sql, Parameterizer parameterizer, Class theInterface) throws SQLException { 787 final Projector projector=new InterfaceProjector(theInterface, null); 788 final Object[] ret={null}; 789 processSelect( 790 sql, 791 parameterizer, 792 new RowProcessor() { 793 public boolean process(ResultSet rs) throws SQLException { 794 ret[0] = projector.project(rs); 795 if (ret[0] instanceof DataAccessObject) { 796 ((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this); 797 } 798 return false; 799 } 800 }); 801 802 return ret[0]; 803 } 804 805 /** 806 * @param sql 807 * @param parameterizer 808 * @return boolean value of the first column of the first row or 'false' if there are no rows. 809 * @throws SQLException 810 */ 811 public boolean projectSingleBoolean(String sql, Parameterizer parameterizer) throws SQLException { 812 final boolean[] ret={false}; 813 processSelect( 814 sql, 815 parameterizer, 816 new RowProcessor() { 817 public boolean process(ResultSet rs) throws SQLException { 818 ret[0] = rs.getBoolean(1); 819 return false; 820 } 821 }); 822 823 return ret[0]; 824 } 825 826 /** 827 * @param sql 828 * @param parameterizer 829 * @return byte value of the first column of the first row or 0 if there are no rows. 830 * @throws SQLException 831 */ 832 public byte projectSingleByte(String sql, Parameterizer parameterizer) throws SQLException { 833 final byte[] ret={0}; 834 processSelect( 835 sql, 836 parameterizer, 837 new RowProcessor() { 838 public boolean process(ResultSet rs) throws SQLException { 839 ret[0] = rs.getByte(1); 840 return false; 841 } 842 }); 843 844 return ret[0]; 845 } 846 847 /** 848 * @param sql 849 * @param parameterizer 850 * @return byte[] value of the first column of the first row or null if there are no rows. 851 * @throws SQLException 852 */ 853 public byte[] projectSingleBytes(String sql, Parameterizer parameterizer) throws SQLException { 854 final byte[][] ret={null}; 855 processSelect( 856 sql, 857 parameterizer, 858 new RowProcessor() { 859 public boolean process(ResultSet rs) throws SQLException { 860 ret[0] = rs.getBytes(1); 861 return false; 862 } 863 }); 864 865 return ret[0]; 866 } 867 868 /** 869 * @param sql 870 * @param parameterizer 871 * @return int value of the first column of the first row or 0 if there are no rows. 872 * @throws SQLException 873 */ 874 public int projectSingleInt(String sql, Parameterizer parameterizer) throws SQLException { 875 final int[] ret={0}; 876 processSelect( 877 sql, 878 parameterizer, 879 new RowProcessor() { 880 public boolean process(ResultSet rs) throws SQLException { 881 ret[0] = rs.getInt(1); 882 return false; 883 } 884 }); 885 886 return ret[0]; 887 } 888 889 /** 890 * @param sql 891 * @param parameterizer 892 * @return short value of the first column of the first row or 0 if there are no rows. 893 * @throws SQLException 894 */ 895 public short projectSingleShort(String sql, Parameterizer parameterizer) throws SQLException { 896 final short[] ret={0}; 897 processSelect( 898 sql, 899 parameterizer, 900 new RowProcessor() { 901 public boolean process(ResultSet rs) throws SQLException { 902 ret[0] = rs.getShort(1); 903 return false; 904 } 905 }); 906 907 return ret[0]; 908 } 909 910 /** 911 * @param sql 912 * @param parameterizer 913 * @return double value of the first column of the first row or 0 if there are no rows. 914 * @throws SQLException 915 */ 916 public double projectSingleDouble(String sql, Parameterizer parameterizer) throws SQLException { 917 final double[] ret={0}; 918 processSelect( 919 sql, 920 parameterizer, 921 new RowProcessor() { 922 public boolean process(ResultSet rs) throws SQLException { 923 ret[0] = rs.getDouble(1); 924 return false; 925 } 926 }); 927 928 return ret[0]; 929 } 930 931 /** 932 * @param sql 933 * @param parameterizer 934 * @return float value of the first column of the first row or 0 if there are no rows. 935 * @throws SQLException 936 */ 937 public float projectSingleFloat(String sql, Parameterizer parameterizer) throws SQLException { 938 final float[] ret={0}; 939 processSelect( 940 sql, 941 parameterizer, 942 new RowProcessor() { 943 public boolean process(ResultSet rs) throws SQLException { 944 ret[0] = rs.getFloat(1); 945 return false; 946 } 947 }); 948 949 return ret[0]; 950 } 951 952 /** 953 * @param sql 954 * @param parameterizer 955 * @return long value of the first column of the first row or 0 if there are no rows. 956 * @throws SQLException 957 */ 958 public long projectSingleLong(String sql, Parameterizer parameterizer) throws SQLException { 959 final long[] ret={0}; 960 processSelect( 961 sql, 962 parameterizer, 963 new RowProcessor() { 964 public boolean process(ResultSet rs) throws SQLException { 965 ret[0] = rs.getLong(1); 966 return false; 967 } 968 }); 969 970 return ret[0]; 971 } 972 973 /** 974 * @param sql 975 * @param parameterizer 976 * @return String value of the first column of the first row or null if there are no rows. 977 * @throws SQLException 978 */ 979 public String projectSingleString(String sql, Parameterizer parameterizer) throws SQLException { 980 final String[] ret={null}; 981 processSelect( 982 sql, 983 parameterizer, 984 new RowProcessor() { 985 public boolean process(ResultSet rs) throws SQLException { 986 ret[0] = rs.getString(1); 987 return false; 988 } 989 }); 990 991 return ret[0]; 992 } 993 994 /** 995 * @param sql 996 * @param parameterizer 997 * @return object value of the first column of the first row or null if there are no rows. 998 * @throws SQLException 999 */ 1000 public Object projectSingleObject(String sql, Parameterizer parameterizer) throws SQLException { 1001 final Object[] ret={null}; 1002 processSelect( 1003 sql, 1004 parameterizer, 1005 new RowProcessor() { 1006 public boolean process(ResultSet rs) throws SQLException { 1007 ret[0] = rs.getObject(1); 1008 return false; 1009 } 1010 }); 1011 1012 return ret[0]; 1013 } 1014 1015 private static Map methodMap=new HashMap(); 1016 1017 static { 1018 methodMap.put("boolean", "projectSingleBoolean"); 1019 methodMap.put("byte", "projectSingleByte"); 1020 methodMap.put("byte[]", "projectSingleBytes"); 1021 methodMap.put("char", "projectSingleChar"); 1022 methodMap.put("int", "projectSingleInt"); 1023 methodMap.put("short", "projectSingleShort"); 1024 methodMap.put("double", "projectSingleDouble"); 1025 methodMap.put("float", "projectSingleFloat"); 1026 methodMap.put("long", "projectSingleLong"); 1027 methodMap.put("java.lang.String", "projectSingleString"); 1028 methodMap.put("java.lang.Object", "projectSingleObject"); 1029 } 1030 1031 /** 1032 * Finds projectSingleXXX method for a particular type. 1033 * @param className 1034 * @return 1035 */ 1036 public static String findProjectSingleMethodName(String className) { 1037 return (String) methodMap.get(className); 1038 } 1039 1040 /** 1041 * @param sql 1042 * @param parameterizer 1043 * @return char value of the first column of the first row or 0 if there are no rows. 1044 * @throws SQLException 1045 */ 1046 public char projectSingleChar(String sql, Parameterizer parameterizer) throws SQLException { 1047 final char[] ret={0}; 1048 processSelect( 1049 sql, 1050 parameterizer, 1051 new RowProcessor() { 1052 public boolean process(ResultSet rs) throws SQLException { 1053 String str=rs.getString(1); 1054 if (str!=null && str.length()>0) { 1055 ret[0]=str.charAt(0); 1056 } 1057 return false; 1058 } 1059 }); 1060 1061 return ret[0]; 1062 } 1063 1064 1065 1066 /** 1067 * @param string 1068 * @param parameterizer 1069 * @return 1070 * @throws SQLException 1071 */ 1072 public Object projectSingleObject(String sql, Parameterizer parameterizer, Class theInterface, Object delegate) throws SQLException { 1073 final Projector projector=new InterfaceProjector(theInterface, delegate, null); 1074 final Object[] ret={null}; 1075 processSelect( 1076 sql, 1077 parameterizer, 1078 new RowProcessor() { 1079 public boolean process(ResultSet rs) throws SQLException { 1080 ret[0] = projector.project(rs); 1081 if (ret[0] instanceof DataAccessObject) { 1082 ((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this); 1083 } 1084 return false; 1085 } 1086 }); 1087 1088 return ret[0]; 1089 } 1090 1091 /** 1092 * Generates primary key. 1093 * @param primaryKeysTable Table holding primary keys counters. DDL: 1094 * <PRE>CREATE TABLE <I>table name</I> ( 1095 KEY_NAME VARCHAR(50) NOT NULL 1096 , KEY_VALUE INTEGER DEFAULT '0' NOT NULL 1097 , PRIMARY KEY (KEY_NAME) 1098 );</PRE> 1099 * @param keyName Key name 1100 * @return 1101 * @throws SQLException 1102 */ 1103 public int nextPK(final String primaryKeysTable, final String keyName) throws SQLException { 1104 final Connection con=getConnection(); 1105 try { 1106 boolean ac=con.getAutoCommit(); 1107 try { 1108 con.setAutoCommit(false); 1109 int value = nextPK(con, primaryKeysTable, keyName); 1110 con.commit(); 1111 return value; 1112 } catch (SQLException e) { 1113 con.rollback(); 1114 throw e; 1115 } finally { 1116 con.setAutoCommit(ac); 1117 } 1118 } finally { 1119 releaseConnection(con); 1120 } 1121 } 1122 1123 /** 1124 * @param con 1125 * @param primaryKeysTable 1126 * @param keyName 1127 * @return 1128 * @throws SQLException 1129 */ 1130 public int nextPK(final Connection con, final String primaryKeysTable, final String keyName) throws SQLException { 1131 final Parameterizer parameterizer=new Parameterizer() { 1132 public void parameterize(PreparedStatement preparedStatement) throws SQLException { 1133 preparedStatement.setString(1, keyName); 1134 } 1135 }; 1136 1137 final int[] value={0}; 1138 1139 processSelect( 1140 con, 1141 "SELECT KEY_VALUE FROM "+primaryKeysTable+" WHERE KEY_NAME=?", 1142 parameterizer, 1143 new RowProcessorEx() { 1144 public boolean process(ResultSet resultSet) throws SQLException { 1145 value[0]=resultSet.getInt("KEY_VALUE")+1; 1146 processUpdate(con, "UPDATE "+primaryKeysTable+" SET KEY_VALUE=KEY_VALUE+1 WHERE KEY_NAME=?", parameterizer); 1147 return false; 1148 } 1149 1150 public void onEmptyResultSet() throws SQLException { 1151 processUpdate(con, "INSERT INTO "+primaryKeysTable+" (KEY_NAME, KEY_VALUE) VALUES (?, 0)", parameterizer); 1152 } 1153 }); 1154 return value[0]; 1155 } 1156 1157 interface Person { 1158 String getFirstName(); 1159 String getLastName(); 1160 } 1161 1162 public static void main(final String[] args) throws Exception { 1163 doCool(); 1164 doDull(); 1165 } 1166 1167 /** 1168 * @throws ClassNotFoundException 1169 * @throws SQLException 1170 */ 1171 private static void doDull() throws ClassNotFoundException, SQLException { 1172 Class.forName("org.hsqldb.jdbcDriver"); 1173 Connection con=DriverManager.getConnection("jdbc:hsqldb:.", "sa", ""); 1174 try { 1175 Statement st=con.createStatement(); 1176 try { 1177 //st.executeUpdate("create table people (first_name varchar(200), last_name varchar(200))"); 1178 } finally { 1179 st.close(); 1180 } 1181 1182 PreparedStatement ps=con.prepareStatement("insert into people (first_name, last_name) values (?, ?)"); 1183 try { 1184 for (int i=0; i<20; i++) { 1185 ps.setString(1,"Pavel-" + i); 1186 ps.setString(2, "Vlasov"); 1187 ps.execute(); 1188 } 1189 } finally { 1190 ps.close(); 1191 } 1192 1193 Statement st1=con.createStatement(); 1194 try { 1195 st1.executeUpdate("insert into people (first_name, last_name) values ('Olga', 'Vlasov')"); 1196 } finally { 1197 st1.close(); 1198 } 1199 1200 Statement st2=con.createStatement(); 1201 try { 1202 ResultSet rs=st2.executeQuery("select * from people"); 1203 try { 1204 while (rs.next()) { 1205 System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME")); 1206 } 1207 } finally { 1208 rs.close(); 1209 } 1210 } finally { 1211 st2.close(); 1212 } 1213 } finally { 1214 con.close(); 1215 } 1216 } 1217 1218 /** 1219 * @throws ClassNotFoundException 1220 * @throws IOException 1221 * @throws SQLException 1222 */ 1223 private static void doCool() throws ClassNotFoundException, IOException, SQLException { 1224 HypersonicInMemoryDataSource ds=new HypersonicInMemoryDataSource((Reader) null); 1225 try { 1226 SQLProcessor processor=new SQLProcessor(ds, null); 1227 processor.processUpdate("create table people (first_name varchar(200), last_name varchar(200))", null); 1228 1229 final int[] counter={0}; 1230 for (int i=0; i<20; i++) { 1231 processor.processUpdate("insert into people (first_name, last_name) values (?, ?)", 1232 new Parameterizer() { 1233 public void parameterize(PreparedStatement ps) throws SQLException { 1234 ps.setString(1,"Dhawal "+ ++counter[0]); 1235 ps.setString(2, "Manwatkar"); 1236 } 1237 }); 1238 } 1239 1240 processor.processUpdate("insert into people (first_name, last_name) values ('Pavel', 'Vlasov')", null); 1241 1242 processor.processSelect("select * from people", null, 1243 new RowProcessor() { 1244 public boolean process(ResultSet rs) throws SQLException { 1245 System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME")); 1246 return true; 1247 } 1248 }); 1249 1250 } finally { 1251 ds.shutdown(); 1252 } 1253 } 1254 1255 public TimeIntervalCategory getTimeIntervalCategory() { 1256 return timeIntervalCategory; 1257 } 1258 public void setTimeIntervalCategory( 1259 TimeIntervalCategory timeIntervalCategory) { 1260 this.timeIntervalCategory = timeIntervalCategory; 1261 } 1262 1263 public Context getNameMap() { 1264 return nameMap; 1265 } 1266 1267 public void executeTransaction(Transaction transaction) throws SQLException { 1268 Connection con=getConnection(); 1269 try { 1270 boolean ac=con.getAutoCommit(); 1271 try { 1272 con.setAutoCommit(false); 1273 SQLProcessor processor=new SQLProcessor(con, nameMap); 1274 try { 1275 if (transaction.execute(processor)) { 1276 con.commit(); 1277 } else { 1278 con.rollback(); 1279 } 1280 } catch (SQLException e) { 1281 con.rollback(); 1282 throw e; 1283 } 1284 } finally { 1285 con.setAutoCommit(ac); 1286 } 1287 } finally { 1288 releaseConnection(con); 1289 } 1290 } 1291 1292 protected DataSource getDataSource() { 1293 return dataSource; 1294 } 1295 }