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    }