SQLProcessor.java

Violations

Source code

/*
 * hgcommons 9
 * Hammurapi Group Common Library
 * Copyright (C) 2003  Hammurapi Group
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 *
 * URL: http://www.hammurapi.biz/hammurapi-biz/ef/xmenu/hammurapi-group/products/products/hgcommons/index.html
 * e-Mail: support@hammurapi.biz
 */
package biz.hammurapi.sql;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import biz.hammurapi.config.ConfigurationException;
import biz.hammurapi.config.Context;
import biz.hammurapi.config.DomConfigFactory;
import biz.hammurapi.config.MapContext;
import biz.hammurapi.config.PropertyParser;
import biz.hammurapi.metrics.MeasurementCategoryFactory;
import biz.hammurapi.metrics.TimeIntervalCategory;
import biz.hammurapi.sql.hsqldb.HsqldbInMemoryDataSource;
import biz.hammurapi.sql.syntax.StatementFragment;
import biz.hammurapi.util.ExceptionSink;


/**
 * This class contains methods to process SQL statements in more convenient
 * way comparing to standard JDBC.
 * @author Pavel Vlasov
 * @version $Revision: 1.14 $
 */
public class SQLProcessor {
	private DataSource dataSource;
	private Connection connection;
	private PropertyParser propertyParser;
	private TimeIntervalCategory timeIntervalCategory=MeasurementCategoryFactory.getTimeIntervalCategory(SQLProcessor.class);
	private Context nameMap;

	/**
	 * If SQLProcessor constructed with this constructor
	 * then it obtains connection from the datasource, processes
	 * request and closes connection.
	 * @param dataSource DataSource
	 * @param nameMap NameMap allows to write parameterized SQL statements
	 * like "SELECT ${AMOUNT} FROM ${ACCOUNT} WHERE ${ANUM}=? AND CLOSED=1"
	 * nameMap shall contain mapping from AMOUNT, ACCOUNT and ANUM to actual
	 * database field names. If nameMap doesn't contain mapping for some
	 * properties then property names will be used as property values.
	 * See {@link biz.hammurapi.config.PropertyParser}.
	 * One property value can contain a reference to another property.
	 * If nameMap is null then no property parsing will happen.
	 */
	public SQLProcessor(DataSource dataSource, Context nameMap) {
		this(nameMap);
		this.dataSource=dataSource;
	}
	
	/**
	 * @param nameMap
	 */
	private SQLProcessor(Context nameMap) {
		super();
		this.nameMap=nameMap;
		if (nameMap!=null) {
			propertyParser=new PropertyParser(nameMap, true);
		}
	}

	/**
	 * If SQLProcessor created with this constructor then is doesn't
	 * close the connection after processing.
	 * @param connection
	 * @param nameMap See {@link biz.hammurapi.sql.SQLProcessor#SQLProcessor(DataSource, Properties)}
	 */
	public SQLProcessor(Connection connection, Context nameMap) {
		this(nameMap);
		this.connection=connection;
	}
	
	/**
	 * Replaces ${<property name>} with property value. See {@link biz.hammurapi.config.PropertyParser}
	 * @param str
	 * @return parsed string
	 */
	public String parse(String str) {
		if (propertyParser==null) {
			return str;
		}
		
		return propertyParser.parse(str);
	}
	
	/**
	 * Returns connection if you need it for JDBC calls outside of the SQLProcessor
	 * @return Connection
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return connection==null ? dataSource==null ? null : dataSource.getConnection() : connection; 
	}
	
	/**
	 * Closes connection if it was provided by DataSource. Does nothing otherwise.
	 * @param connection Connection to release.
	 * @throws SQLException
	 */
	public void releaseConnection(Connection connection) throws SQLException {
		if (this.connection==null && connection!=null) {
			connection.close();
		}
	}
	
	public void processSelect(StatementFragment fragment, RowProcessor rowProcessor) throws SQLException {
		processSelect(fragment.toSqlString(), fragment, rowProcessor);
	}
	
	/**
	 * Processes SQL SELECT statement in the following way:
	 * <UL>
	 * 	<li>Obtains connection</li>
	 * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li>
	 * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li>
	 * <li>Iterates through result set and invokes rowProcessor.process() on each row</li>
	 * <li>If there was no rows and rowProcess is instance of {@link RowProcessorEx} then rowProcessor.onEmptyResultSet() is invoked</li>
	 * <li>ResultSet, Statement and connection are properly released</li>
	 * </UL>
	 *
	 * @param sql SQL statment to execute
	 * @param parameterizer Parameterizer
	 * @param rowProcessor RowProcessor
	 * @throws SQLException
	 */
	public void processSelect(String sql, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException {
		Connection con=getConnection();
		try {
			processSelect(con, sql, parameterizer, rowProcessor);		
		} finally {
			releaseConnection(con);
		}
	}
	
	/**
	 * @param con
	 * @param sql
	 * @param parameterizer
	 * @param rowProcessor
	 * @param releaseConnection
	 * @throws SQLException
	 */
	private void processSelect(Connection con, String sql, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException {
		long start = timeIntervalCategory==null ? 0 : timeIntervalCategory.getTime();
		if (parameterizer==null) {
			Statement statement=con.createStatement();
			try {
				ResultSet rs=statement.executeQuery(parse(sql));
				if (propertyParser!=null) {
					rs = new ResultSetProxy(this, rs);
				}
				
				try {
					if (rowProcessor instanceof MetadataAwareRowProcessor) {
						((MetadataAwareRowProcessor) rowProcessor).processMetadata(rs.getMetaData());
					}
					
					boolean isEmpty=true;
					
					while (rs.next()) {
						isEmpty=false;
						if (!rowProcessor.process(rs)) {
							break;
						}
					}
					
					if (isEmpty && rowProcessor instanceof RowProcessorEx) {
						((RowProcessorEx) rowProcessor).onEmptyResultSet();
					}
				} finally {
					rs.close();
				}
			} catch (SQLException e) {
				throw new SQLExceptionEx("Failed to execute statement: "+sql, e);
			} finally {
				statement.close();
			}				
		} else {
			PreparedStatement statement=con.prepareStatement(parse(sql));
			parameterizer.parameterize(statement, 1);
			try {
				ResultSet rs=statement.executeQuery();
				try {
					if (rowProcessor instanceof MetadataAwareRowProcessor) {
						((MetadataAwareRowProcessor) rowProcessor).processMetadata(rs.getMetaData());
					}
					
					boolean isEmpty=true;
					
					while (rs.next()) {							
						isEmpty=false;
						if (!rowProcessor.process(propertyParser==null ? rs : new ResultSetProxy(this, rs))) {
							break;
						}
					}
					
					if (isEmpty && rowProcessor instanceof RowProcessorEx) {
						((RowProcessorEx) rowProcessor).onEmptyResultSet();
					}
				} finally {
					rs.close();
				}
			} catch (SQLException e) {
				throw new SQLExceptionEx("Failed to execute statement: "+sql, e);
			} finally {
				statement.close();
			}				
		}
		if (timeIntervalCategory!=null) {
		    timeIntervalCategory.addInterval(sql, start);
		}
	}

	public int processUpdate(StatementFragment fragment) throws SQLException {
		return processUpdate(fragment.toSqlString(), fragment);
	}
	
	/**
	 * Processes SQL INSERT, UPDATE or DELETE statement in the following way:
	 * <UL>
	 * <li>Obtains connection</li>
	 * <LI>If parameterizer==null creates Statement, creates PreparedStatement otherwise</li>
	 * <LI>Invokes parameterizer.parameterize() if parameterizer is not null</li>
	 * <li>Executes update</li>
	 * <li>ResultSet, Statement and connection are properly released</li>
	 * </UL>
	 *
	 * @param sql SQL statment to execute
	 * @param parameterizer Parameterizer
	 * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)}
	 * @throws SQLException
	 */
	public int processUpdate(String sql, Parameterizer parameterizer) throws SQLException {
		Connection con=getConnection();
		try {
			return processUpdate(con, sql, parameterizer);
		} finally {
			releaseConnection(con);
		}
	}
	
	/**
	 * @param con
	 * @param sql
	 * @param parameterizer
	 * @return
	 * @throws SQLException
	 */
	private int processUpdate(Connection con, String sql, Parameterizer parameterizer) throws SQLException {
		long start = timeIntervalCategory==null ? 0 : timeIntervalCategory.getTime();
		String parsedSql = parse(sql);
		try {
			if (parameterizer==null) {
				Statement statement=con.createStatement();
				try {
					return statement.executeUpdate(parsedSql);
				} catch (SQLException e) {
					throw new SQLExceptionEx("Failed to execute statement: "+parsedSql, e);
				} finally {
					statement.close();
				}
			}
			
			PreparedStatement statement=con.prepareStatement(parsedSql);
			try {
				parameterizer.parameterize(statement, 1);
				return statement.executeUpdate();
			} catch (SQLException e) {
				throw new SQLExceptionEx("Failed to execute statement: "+parsedSql, e);
			} finally {
				statement.close();
			}
		} finally {
			if (timeIntervalCategory!=null) {				
				timeIntervalCategory.addInterval(parsedSql, start);
			}			
		}
	}

	/**
	 * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements
	 * maintained by other developers.
	 * @param resourceName
	 * @param parameterizer
	 * @param rowProcessor
	 * @throws SQLException
	 * @throws IOException
	 */
	public void processResourceSelect(String resourceName, Parameterizer parameterizer, RowProcessor rowProcessor) throws SQLException, IOException {
		processSelect(resourceToString(resourceName), parameterizer, rowProcessor);
	}

	/**
	 * Processes SELECT statement read from resource file. Suitable for huge SQL statements or SQL statements
	 * maintained by other developers.
	 * @param resourceName
	 * @param parameterizer
	 * @return Number of updates. See {@link Statement#executeUpdate(java.lang.String)}
	 * @throws SQLException
	 * @throws IOException
	 */
	public int processResourceUpdate(String resourceName, Parameterizer parameterizer) throws SQLException, IOException {
		return processUpdate(resourceToString(resourceName), parameterizer);
	}
	
	private String resourceToString(String resourceName) throws IOException {
		InputStream in=getClass().getClassLoader().getResourceAsStream(resourceName);
		if (in==null) {
			return null;			
		} 
		
		StringWriter sw=new StringWriter();
		try {
			char[] buf=new char[1024];
			Reader r=new InputStreamReader(in);
			try {
				int len;
				while((len=r.read(buf))!=-1) {
					sw.write(buf, 0, len);
				}
			} finally {
				r.close();
			}
		} finally {
			sw.close();
		}
		return sw.toString();
	}
	
	/**
	 * Excecutes script with ; as statement separator
	 * @param reader Script source
	 * @throws IOException
	 * @throws SQLException
	 * @see SQLProcessor#executeScript(Reader, char)
	 */
	public void executeScript(Reader reader) throws IOException, SQLException {
		executeScript(reader, ';');
	}
	
	/**
	 * Executes series of SQL statement read from reader and separated by statementSeparator
	 * @param reader Script source
	 * @param statementSeparator Statement separator
	 * @throws IOException
	 * @throws SQLException
	 */
	public void executeScript(Reader reader, char statementSeparator) throws IOException, SQLException {
		executeScript(reader, statementSeparator, null);
	}
	
	/**
	 *
	 * @param reader Script source
	 * @param statementSeparator Statement separator
	 * @param exceptionSink Exception sink. Consumes exceptions thrown by individual statements. If sink is null then exception is
	 * rethrown and script execution terminates.
	 * @throws IOException
	 * @throws SQLException
	 */
	public void executeScript(Reader reader, char statementSeparator, ExceptionSink exceptionSink) throws IOException, SQLException {	
		if (reader!=null) {
			Connection con=getConnection();
			try {
				Statement stmt=con.createStatement();
				try {				
					try {
						StringBuffer sb=new StringBuffer();
						int ch;
						while ((ch=reader.read())!=-1) {
							if (ch==statementSeparator) {
								// Double separator is replaced with one e.g. ;; -> ;

								int nextCh=reader.read();
								if (nextCh==-1) {
									break;
								} else if (nextCh==statementSeparator) {
									sb.append((char) nextCh);								
								} else {
									executeBuffer(stmt, sb, exceptionSink);
									sb=new StringBuffer();
									sb.append((char) nextCh);
								}
							} else {
								sb.append((char) ch);
							}
						}
						executeBuffer(stmt, sb, exceptionSink);
					} finally {
						reader.close();
					}
				} finally {
					stmt.close();
				}
			} finally {
				releaseConnection(con);
			}
		}		
	}

	/**
	 * @param stmt
	 * @param sb
	 * @throws SQLException
	 */
	private void executeBuffer(Statement stmt, StringBuffer sb, ExceptionSink exceptionSink) throws SQLException {
		String sql=sb.toString().trim(); 
		if (sql.length()!=0) {
			try {
				stmt.execute(sql);
			} catch (SQLException e) {
				if (exceptionSink == null) {
					throw new SQLExceptionEx("SQL: "+sql, e);
				} else {
					exceptionSink.consume(sql, e);
				}
			}
		}
	}
	
	public Collection project(StatementFragment fragment, Projector projector) {
		return project(fragment.toSqlString(), fragment, projector);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param projector Projector which instantiates objects. It is null then projector
	 * which projects row to collection will be used.
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Projector projector) {
		return new ResultSetCollection(this, parse(sql), parameterizer, projector==null ? defaultProjector : projector);
	}
	
	public Collection project(StatementFragment fragment) {
		return project(fragment.toSqlString(), fragment);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * Rows are projected to collection of field values.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param projector Projector which instantiates objects. It is null then projector
	 * which projects row to collection will be used.
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor and parameterizer. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer) {
		return new ResultSetCollection(this, parse(sql), parameterizer, defaultProjector);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor.
	 * Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql) {
		return new ResultSetCollection(this, parse(sql), null, defaultProjector);
	}		

	public Collection project(StatementFragment fragment, Class theInterface) {
		return project(fragment.toSqlString(), fragment, theInterface);
	}	
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Iterface to implement
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Class theInterface) {
		return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, null));
	}

	public Collection project(StatementFragment fragment, Class theInterface, Object delegate) {
		return project(fragment.toSqlString(), fragment, theInterface, delegate);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Interface to implement
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Class theInterface, Object delegate) {
		return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, delegate, null));
	}
	
	public Collection project(StatementFragment fragment, Projector projector, int pageNum, int pageSize) {
		return project(fragment.toSqlString(), fragment, projector, pageNum, pageSize);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param projector Projector which instantiates objects. If it is null
	 * then projector which projects row to collection will be used
	 * @param pageSize Maximum number of records to return
	 * @param pageNum Number of page. Starts with 1.
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Projector projector, int pageNum, int pageSize) {
		return new ResultSetCollection(this, parse(sql), parameterizer, projector==null ? defaultProjector : projector, pageNum, pageSize);
	}
	
	public Collection project(StatementFragment fragment, Class theInterface, int pageNum, int pageSize) {
		return project(fragment.toSqlString(), fragment, theInterface, pageNum, pageSize);
	}
	
	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Interface to implement
	 * @param pageSize Maximum number of records to return
	 * @param pageNum Number of page. Starts with 1.
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Class theInterface, int pageNum, int pageSize) {
		return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, null), pageNum, pageSize);
	}	

	public Collection project(StatementFragment fragment, Class theInterface, Object delegate, int pageNum, int pageSize) {
		return project(fragment.toSqlString(), fragment, theInterface, delegate, pageNum, pageSize);
	}

	/**
	 * Executes SQL statement and returns collection backed by the database.
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Interface to implement
	 * @param delegate Object to delegate invocations which didn't match field getters/setters
	 * @param pageSize Maximum number of records to return
	 * @param pageNum Number of page. Starts with 1.
	 * @return Collection backed by the database. The collection doesn't hold
	 * any SQL resources open, neither it keeps any refernces to created objects.
	 * It keeps only references to the SQLProcessor, parameterizer
	 * and projector. Thus any call to one of collection methods retursn 'fresh'
	 * results from the database.
	 *
	 * Iterators created by this collection open ResultSet and close it when
	 * Iterator.hasNext() returns false.
	 */
	public Collection project(String sql, Parameterizer parameterizer, Class theInterface, Object delegate, int pageNum, int pageSize) {
		return new ResultSetCollection(this, parse(sql), parameterizer, new InterfaceProjector(theInterface, delegate, null), pageNum, pageSize);
	}	

	public Collection project(StatementFragment fragment, final Projector projector, final Collection receiver) throws SQLException {
		return project(fragment.toSqlString(), fragment, projector, receiver);
	}
	
	/**
	 * Executes SQL statement and puts results to receiver
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param projector Projector which instantiates objects. If it is null then
	 * projector which projects row to collection will be used
	 * @param receiver Collection to put results to
	 * @return receiver with added objects. Convenient for calls like
	 * Iterator it=processor.project(..., new LinkedList());
	 * @throws SQLException
	 */
	public Collection project(final String sql, final Parameterizer parameterizer, final Projector projector, final Collection receiver) throws SQLException {
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						Object o = (projector==null ? defaultProjector : projector).project(rs);
						if (o instanceof DataAccessObject) {
							((DataAccessObject) o).setSQLProcessor(SQLProcessor.this);
						}
						receiver.add(o);
						return true;
					}
		});
		
		return receiver;
	}
	
	public Collection project(StatementFragment fragment, final Class theInterface, final Collection receiver) throws SQLException {
		return project(fragment.toSqlString(), fragment, theInterface, receiver);
	}

	/**
	 * Executes SQL statement and puts results to receiver
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Interface to implement
	 * @param receiver Collection to put results to
	 * @return receiver with added objects. Convenient for calls like
	 * Iterator it=processor.project(..., new LinkedList());
	 * @throws SQLException
	 */
	public Collection project(final String sql, final Parameterizer parameterizer, final Class theInterface, final Collection receiver) throws SQLException {
		final Projector projector=new InterfaceProjector(theInterface, null);
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						Object o = projector.project(rs);
						if (o instanceof DataAccessObject) {
							((DataAccessObject) o).setSQLProcessor(SQLProcessor.this);
						}
						receiver.add(o);
						return true;
					}
		});
	
		return receiver;
	}
	
	public Collection project(StatementFragment fragment, final Class theInterface, Object delegate, final Collection receiver) throws SQLException {
		return project(fragment.toSqlString(), fragment, theInterface, delegate, receiver);
	}

	/**
	 * Executes SQL statement and puts results to receiver
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param theInterface Interface to implement
	 * @param delegate Object to delegate invocations which didn't match field getters/setters.
	 * @param receiver Collection to put results to
	 * @return receiver with added objects. Convenient for calls like
	 * Iterator it=processor.project(..., new LinkedList());
	 * @throws SQLException
	 */
	public Collection project(final String sql, final Parameterizer parameterizer, final Class theInterface, Object delegate, final Collection receiver) throws SQLException {
		final Projector projector=new InterfaceProjector(theInterface, delegate, null);
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						Object o = projector.project(rs);
						if (o instanceof DataAccessObject) {
							((DataAccessObject) o).setSQLProcessor(SQLProcessor.this);
						}
						receiver.add(o);
						return true;
					}
		});
	
		return receiver;
	}
	
	public Collection project(StatementFragment fragment, final Projector projector, final Collection receiver, final int pageSize, final int pageNum) throws SQLException {
		return project(fragment.toSqlString(), fragment, projector, receiver, pageSize, pageNum);
	}

	/**
	 * Executes SQL statement and puts results to receiver
	 * @param sql Select statement to execute
	 * @param parameterizer Parameterizer
	 * @param projector Projector which instantiates objects. If it is null then
	 * projector which projects row to collection will be used.
	 * @param receiver Collection to put results to
	 * @param pageSize Maximum number of records to return
	 * @param pageNum Number of page. Starts with 1.
	 * @return receiver with added objects. Convenient for calls like
	 * Iterator it=processor.project(..., new LinkedList());
	 * @throws SQLException
	 */
	public Collection project(final String sql, final Parameterizer parameterizer, final Projector projector, final Collection receiver, final int pageSize, final int pageNum) throws SQLException {
		final int[] counter={0};
		
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						if (++counter[0]>(pageNum-1)*pageSize && counter[0]<=pageNum*pageSize) {
							Object o = (projector==null ? defaultProjector : projector).project(rs);
							if (o instanceof DataAccessObject) {
								((DataAccessObject) o).setSQLProcessor(SQLProcessor.this);
							}
							receiver.add(o);
						}
						return true;
					}
		});
	
		return receiver;
	}
	
	private Projector defaultProjector=new Projector() {
		public Object project(ResultSet rs) throws SQLException {
			Collection ret=new ArrayList();
			for (int i=1, j=rs.getMetaData().getColumnCount(); i <= j; i++) {
				ret.add(rs.getObject(i));
			}
			return ret;
		}
	};
	
	public Object projectSingleObject(StatementFragment fragment, final Projector projector) throws SQLException {
		return projectSingleObject(fragment.toSqlString(), fragment, projector);
	}
	
	/**
	 * @param string
	 * @param parameterizer
	 * @param projector Projector. If it is null then projector which projects
	 * row to collection will be used.
	 * @return
	 * @throws SQLException
	 */
	public Object projectSingleObject(String sql, Parameterizer parameterizer, final Projector projector) throws SQLException {
		final Object[] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = (projector==null ? defaultProjector : projector).project(rs);
						if (ret[0] instanceof DataAccessObject) {
							((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this);
						}
						return false;
					}
		});
	
		return ret[0];
	}

	public void inject(StatementFragment fragment, final Map columnMap, final Object target) throws SQLException {
		inject(fragment.toSqlString(), fragment, columnMap, target);
	}
	
	/**
	 * Executes query and injects values from the first row to target object.
	 * @param string
	 * @param parameterizer
	 * @param target Object to inject values to
	 * @throws SQLException
	 */
	public void inject(String sql, Parameterizer parameterizer, final Map columnMap, final Object target) throws SQLException {
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ResultSetMetaData metaData = rs.getMetaData();
						Map contextMap=new HashMap();
						for (int i=1, cc=metaData.getColumnCount(); i<=cc; i++) {
							String colName=metaData.getColumnName(i);
							String propertyName=BaseReflectionProjector.propertyName(colName);
							
							if (columnMap!=null && columnMap.containsKey(propertyName)) {
								propertyName=(String) columnMap.get(propertyName);
							}
							
							if (propertyName!=null) {
								contextMap.put(propertyName, rs.getObject(colName));
							}
						}
						
						try {
							DomConfigFactory.inject(target, new MapContext(contextMap));
						} catch (ConfigurationException e) {
							throw new SQLExceptionEx(e);
						}						
						return false;
					}
		});
	}

	public Object projectSingleObject(StatementFragment fragment, Class theInterface) throws SQLException {
		return projectSingleObject(fragment.toSqlString(), fragment, theInterface);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return Object representing first row
	 * @throws SQLException
	 */
	public Object projectSingleObject(String sql, Parameterizer parameterizer, Class theInterface) throws SQLException {
		final Projector projector=new InterfaceProjector(theInterface, null);
		final Object[] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = projector.project(rs);
						if (ret[0] instanceof DataAccessObject) {
							((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this);
						}
						return false;
					}
		});
	
		return ret[0];
	}
	
	public boolean projectSingleBoolean(StatementFragment fragment) throws SQLException {
		return projectSingleBoolean(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return boolean value of the first column of the first row or 'false' if there are no rows.
	 * @throws SQLException
	 */
	public boolean projectSingleBoolean(String sql, Parameterizer parameterizer) throws SQLException {
		final boolean[] ret={false};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getBoolean(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public byte projectSingleByte(StatementFragment fragment) throws SQLException {
		return projectSingleByte(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return byte value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public byte projectSingleByte(String sql, Parameterizer parameterizer) throws SQLException {
		final byte[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getByte(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public byte[] projectSingleBytes(StatementFragment fragment) throws SQLException {
		return projectSingleBytes(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return byte[] value of the first column of the first row or null if there are no rows.
	 * @throws SQLException
	 */
	public byte[] projectSingleBytes(String sql, Parameterizer parameterizer) throws SQLException {
		final byte[][] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getBytes(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public int projectSingleInt(StatementFragment fragment) throws SQLException {
		return projectSingleInt(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return int value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public int projectSingleInt(String sql, Parameterizer parameterizer) throws SQLException {
		final int[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getInt(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public short projectSingleShort(StatementFragment fragment) throws SQLException {
		return projectSingleShort(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return short value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public short projectSingleShort(String sql, Parameterizer parameterizer) throws SQLException {
		final short[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getShort(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public double projectSingleDouble(StatementFragment fragment) throws SQLException {
		return projectSingleDouble(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return double value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public double projectSingleDouble(String sql, Parameterizer parameterizer) throws SQLException {
		final double[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getDouble(1);						
						return false;
					}
		});
	
		return ret[0];
	}
		
	public float projectSingleFloat(StatementFragment fragment) throws SQLException {
		return projectSingleFloat(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return float value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public float projectSingleFloat(String sql, Parameterizer parameterizer) throws SQLException {
		final float[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getFloat(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public long projectSingleLong(StatementFragment fragment) throws SQLException {
		return projectSingleLong(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return long value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public long projectSingleLong(String sql, Parameterizer parameterizer) throws SQLException {
		final long[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getLong(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public String projectSingleString(StatementFragment fragment) throws SQLException {
		return projectSingleString(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return String value of the first column of the first row or null if there are no rows.
	 * @throws SQLException
	 */
	public String projectSingleString(String sql, Parameterizer parameterizer) throws SQLException {
		final String[] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getString(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	public Object projectSingleObject(StatementFragment fragment) throws SQLException {
		return projectSingleObject(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return object value of the first column of the first row or null if there are no rows.
	 * @throws SQLException
	 */
	public Object projectSingleObject(String sql, Parameterizer parameterizer) throws SQLException {
		final Object[] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = rs.getObject(1);						
						return false;
					}
		});
	
		return ret[0];
	}
	
	private static Map methodMap=new HashMap();
	
	static {
		methodMap.put("boolean", "projectSingleBoolean");
		methodMap.put("byte", "projectSingleByte");
		methodMap.put("byte[]", "projectSingleBytes");
		methodMap.put("char", "projectSingleChar");
		methodMap.put("int", "projectSingleInt");
		methodMap.put("short", "projectSingleShort");
		methodMap.put("double", "projectSingleDouble");
		methodMap.put("float", "projectSingleFloat");
		methodMap.put("long", "projectSingleLong");
		methodMap.put("java.lang.String", "projectSingleString");
		methodMap.put("java.lang.Object", "projectSingleObject");
	}
	
	/**
	 * Finds projectSingleXXX method for a particular type.
	 * @param className
	 * @return
	 */
	public static String findProjectSingleMethodName(String className) {
		return (String) methodMap.get(className);
	}	
	
	public char projectSingleChar(StatementFragment fragment) throws SQLException {
		return projectSingleChar(fragment.toSqlString(), fragment);
	}
	
	/**
	 * @param sql
	 * @param parameterizer
	 * @return char value of the first column of the first row or 0 if there are no rows.
	 * @throws SQLException
	 */
	public char projectSingleChar(String sql, Parameterizer parameterizer) throws SQLException {
		final char[] ret={0};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						String str=rs.getString(1);
						if (str!=null && str.length()>0) {
							ret[0]=str.charAt(0);
						}
						return false;
					}
		});
	
		return ret[0];
	}
	
	
	
	public Object projectSingleObject(StatementFragment fragment, Class theInterface, Object delegate) throws SQLException {
		return projectSingleObject(fragment.toSqlString(), fragment, theInterface, delegate);
	}
	
	/**
	 * @param string
	 * @param parameterizer
	 * @return
	 * @throws SQLException
	 */
	public Object projectSingleObject(String sql, Parameterizer parameterizer, Class theInterface, Object delegate) throws SQLException {
		final Projector projector=new InterfaceProjector(theInterface, delegate, null);
		final Object[] ret={null};
		processSelect(
				sql, 
				parameterizer, 
				new RowProcessor() {
					public boolean process(ResultSet rs) throws SQLException {
						ret[0] = projector.project(rs);
						if (ret[0] instanceof DataAccessObject) {
							((DataAccessObject) ret[0]).setSQLProcessor(SQLProcessor.this);
						}
						return false;
					}
		});
	
		return ret[0];
	}
	
	/**
	 * Generates primary key.
	 * @param primaryKeysTable Table holding primary keys counters. DDL:
	 * <PRE>CREATE TABLE <I>table name</I> (
       KEY_NAME VARCHAR(50) NOT NULL
     , KEY_VALUE INTEGER DEFAULT '0' NOT NULL
     , PRIMARY KEY (KEY_NAME)
);</PRE>
	 * @param keyName Key name
	 * @return
	 * @throws SQLException
	 */
	public int nextPK(final String primaryKeysTable, final String keyName) throws SQLException {
		final Connection con=getConnection();
		try {
			boolean ac=con.getAutoCommit();
			try {
				con.setAutoCommit(false);
				int value = nextPK(con, primaryKeysTable, keyName);				
				con.commit();
				return value;
			} catch (SQLException e) {
				con.rollback();
				throw e;
			} finally {
				con.setAutoCommit(ac);
			}
		} finally {
			releaseConnection(con);
		}
	}
	
	/**
	 * @param con
	 * @param primaryKeysTable
	 * @param keyName
	 * @return
	 * @throws SQLException
	 */
	public int nextPK(final Connection con, final String primaryKeysTable, final String keyName) throws SQLException {
		final Parameterizer parameterizer=new Parameterizer() {
			public int parameterize(PreparedStatement preparedStatement, int idx) throws SQLException {
				preparedStatement.setString(idx++, keyName);
				return idx;
			}					
		};
		
		final int[] value={0};
		
		processSelect(
				con, 
				"SELECT KEY_VALUE FROM "+primaryKeysTable+" WHERE KEY_NAME=?",
				parameterizer, 
				new RowProcessorEx() {
					public boolean process(ResultSet resultSet) throws SQLException {
						value[0]=resultSet.getInt("KEY_VALUE")+1;
						processUpdate(con, "UPDATE "+primaryKeysTable+" SET KEY_VALUE=KEY_VALUE+1 WHERE KEY_NAME=?", parameterizer);
						return false;
					}

					public void onEmptyResultSet() throws SQLException {
						processUpdate(con, "INSERT INTO "+primaryKeysTable+" (KEY_NAME, KEY_VALUE) VALUES (?, 0)", parameterizer);
					}							
				});
		return value[0];
	}

	interface Person {
		String getFirstName();
		String getLastName();
	}	
	
	public static void main(final String[] args) throws Exception {
		doCool();
		doDull();
	}

	/**
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static void doDull() throws ClassNotFoundException, SQLException {
		Class.forName("org.hsqldb.jdbcDriver");
		Connection con=DriverManager.getConnection("jdbc:hsqldb:.", "sa", "");
		try {
			Statement st=con.createStatement();
			try {
				//st.executeUpdate("create table people (first_name varchar(200), last_name varchar(200))");

			} finally {
				st.close();
			}
			
			PreparedStatement ps=con.prepareStatement("insert into people (first_name, last_name) values (?, ?)");
			try {
				for (int i=0; i<20; i++) {
					ps.setString(1,"Pavel-" + i);
					ps.setString(2, "Vlasov");
					ps.execute();
				}
			} finally {
				ps.close();
			}
			
			Statement st1=con.createStatement();
			try {
				st1.executeUpdate("insert into people (first_name, last_name) values ('Olga', 'Vlasov')");
			} finally {
				st1.close();
			}			
			
			Statement st2=con.createStatement();
			try {
				ResultSet rs=st2.executeQuery("select * from people");
				try {
					while (rs.next()) {
						System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME"));
					}
				} finally {
					rs.close();
				}
			} finally {
				st2.close();
			}
		} finally {
			con.close();
		}		
	}
	
	/**
	 * @throws ClassNotFoundException
	 * @throws IOException
	 * @throws SQLException
	 */
	private static void doCool() throws ClassNotFoundException, IOException, SQLException {
		HsqldbInMemoryDataSource ds=new HsqldbInMemoryDataSource((Reader) null);	
		try {
			SQLProcessor processor=new SQLProcessor(ds, null);			
			processor.processUpdate("create table people (first_name varchar(200), last_name varchar(200))", null);
			
			final int[] counter={0};
			for (int i=0; i<20; i++) {
				processor.processUpdate("insert into people (first_name, last_name) values (?, ?)", 
						new Parameterizer() {
							public int parameterize(PreparedStatement ps, int idx) throws SQLException {
								ps.setString(idx++,"Dhawal "+ ++counter[0]);
								ps.setString(idx++, "Manwatkar");
								return idx;
							}					
				});
			}
			
			processor.processUpdate("insert into people (first_name, last_name) values ('Pavel', 'Vlasov')", null); 
			
			processor.processSelect("select * from people",	null,
					new RowProcessor() {
						public boolean process(ResultSet rs) throws SQLException {
							System.out.println(rs.getString("FIRST_NAME")+" "+rs.getString("LAST_NAME"));
							return true;
						}					
					});
			
		} finally {
			ds.shutdown();
		}
	}
	
	public TimeIntervalCategory getTimeIntervalCategory() {
        return timeIntervalCategory;
    }
    public void setTimeIntervalCategory(TimeIntervalCategory timeIntervalCategory) {
        this.timeIntervalCategory = timeIntervalCategory;
    }

	public Context getNameMap() {
		return nameMap;
	}
	
	public void executeTransaction(Transaction transaction) throws SQLException {
		Connection con=getConnection();
		try {
			boolean ac=con.getAutoCommit();			
			try {
				con.setAutoCommit(false);
				SQLProcessor processor=new SQLProcessor(con, nameMap);
				try {
					if (transaction.execute(processor)) {
						con.commit();
					} else {
						con.rollback();
					}
				} catch (SQLException e) {
					con.rollback();
					throw e;
				}
			} finally {
				con.setAutoCommit(ac);
			}
		} finally {
			releaseConnection(con);
		}
	}

	protected DataSource getDataSource() {
		return dataSource;
	}
}