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