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    }