path: root/projects/net.wotonomy.persistence.adapter.jdbc/src/main/java/net/wotonomy/jdbcadaptor/JDBCChannel.java

/*
 Wotonomy: OpenStep design patterns for pure Java applications.
 Copyright (C) 2001 Michael Powers

 This library 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.1 of the License, or (at your option) any later version.

 This library 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, see http://www.gnu.org
 */
package net.wotonomy.jdbcadaptor;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import net.wotonomy.access.EOAdaptorChannel;
import net.wotonomy.access.EOAttribute;
import net.wotonomy.access.EOEntity;
import net.wotonomy.access.EOGeneralAdaptorException;
import net.wotonomy.access.EOSQLExpression;
import net.wotonomy.access.EOStoredProcedure;
import net.wotonomy.control.EOFetchSpecification;
import net.wotonomy.control.EOQualifier;
import net.wotonomy.foundation.NSArray;
import net.wotonomy.foundation.NSData;
import net.wotonomy.foundation.NSDictionary;
import net.wotonomy.foundation.NSKeyValueCoding;
import net.wotonomy.foundation.NSMutableDictionary;
import net.wotonomy.foundation.NSTimestamp;

/**
 * Concrete implementation of EOAdaptorChannel for use with JDBC.
 *
 * @author ezamudio@nasoft.com
 * @author $Author: cgruber $
 * @version $Revision: 903 $
 */
public class JDBCChannel extends EOAdaptorChannel {

	protected boolean _fetchInProgress;
	protected ResultSet _resultSet;
	protected Statement _statement;
	protected NSArray _attsToFetch;
	protected NSArray _resultAttributes;
	protected boolean _transactionWasOpen;
	protected NSDictionary _spReturnValues;
	protected int _resultCount;

	/**
	 * Creates a new JDBCChannel.
	 * 
	 * @param context The JDBCContext this channel belongs to.
	 */
	public JDBCChannel(JDBCContext context) {
		super(context);
	}

	protected JDBCContext _context() {
		return (JDBCContext) adaptorContext();
	}

	/*
	 * Sets the attributes to be fetched from the database.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#setAttributesToFetch(net.wotonomy.
	 * foundation.NSArray)
	 */
	public void setAttributesToFetch(NSArray atts) {
		_attsToFetch = atts;
	}

	/*
	 * Returns an array with the attributes that will be fetched.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#attributesToFetch()
	 */
	public NSArray attributesToFetch() {
		return _attsToFetch;
	}

	/*
	 * Cancels the fetch, rolling back the transaction.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#cancelFetch()
	 */
	public void cancelFetch() {
		if (_statement == null || _resultSet == null)
			return;
		try {
			_resultSet.close();
			_statement.cancel();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot cancel fetch in database.", ex);
		}
	}

	/*
	 * Closes the jdbc channel.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#closeChannel()
	 */
	public void closeChannel() {
		if (_statement == null)
			return;
		try {
			_statement.close();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to close the channel.", ex);
		}
	}

	/*
	 * If the fetch was done with an array of EOAttributes, returns that same array;
	 * otherwise it creates an array of EOAttributes based on the column names that
	 * will be fetched.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#describeResults()
	 */
	public NSArray describeResults() {
		if (_resultSet == null || !_fetchInProgress)
			throw new EOGeneralAdaptorException("Cannot describe results without a result set.");
		if (_resultAttributes == null) {
			try {
				ResultSetMetaData _rsmeta = _resultSet.getMetaData();
				EOAttribute[] attarr = new EOAttribute[_rsmeta.getColumnCount()];
				for (int i = 1; i <= attarr.length; i++) {
					EOAttribute a = new EOAttribute();
					a.setName("Attribute " + (i));
					a.setColumnName(_rsmeta.getColumnName(i));
					a.setClassName(_rsmeta.getColumnClassName(i));
					a.setExternalType(_rsmeta.getColumnTypeName(i));
					a.setPrecision(_rsmeta.getPrecision(i));
					a.setScale(_rsmeta.getScale(i));
					a.setAllowsNull(_rsmeta.isNullable(i) == ResultSetMetaData.columnNullable);
					a.setWidth(_rsmeta.getColumnDisplaySize(i));
					a.setReadOnly(_rsmeta.isReadOnly(i));
					attarr[i - 1] = a;
				}
				_resultAttributes = new NSArray(attarr);
			} catch (SQLException ex) {
				throw new JDBCAdaptorException("While trying to get the result set metadata.", ex);
			}
		}
		return _resultAttributes;
	}

	/*
	 * Deletes from the database the rows described by the qualifier, in the
	 * specified entity.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#deleteRowsDescribedByQualifier(net.
	 * wotonomy.control.EOQualifier, net.wotonomy.access.EOEntity)
	 */
	public int deleteRowsDescribedByQualifier(EOQualifier q, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareDeleteExpressionForQualifier(q);
		evaluateExpression(exp);
		return _resultCount;
	}

	/*
	 * Creates a java.sql.Statement object and executes it. If there is an open
	 * transaction, the statement is executed inside it; otherwise a transaction is
	 * started, the statement executed, and the transaction is committed.
	 * 
	 * @see
	 * net.wotonomy.access.EOAdaptorChannel#evaluateExpression(net.wotonomy.access.
	 * EOSQLExpression)
	 */
	public void evaluateExpression(EOSQLExpression sql) {
		if (!isOpen())
			throw new EOGeneralAdaptorException("Attempt to evaluate expression without opening the channel first.");
		try {
			_statement = _context().connection().createStatement();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot create java.sql.Statement", ex);
		}
		_resultSet = null;
		boolean isQuery = false;
		String text = sql.statement();
		try {
			// run an executeUpdate with these prefixes
			if (text.startsWith("INSERT") || text.startsWith("DELETE") || text.startsWith("UPDATE")) {
				conditionalBeginTransaction();
				_resultCount = _statement.executeUpdate(text);
				conditionalCommitTransaction();
				return;
			} else if (text.startsWith("SELECT")) {
				// run an executeQuery with SELECT
				if (_resultCount > 0)
					_statement.setMaxRows(_resultCount);
				_resultSet = _statement.executeQuery(text);
				_fetchInProgress = true;
				return;
			} else { // just plain execute
				conditionalBeginTransaction();
				isQuery = _statement.execute(text);
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to execute expression '" + text + "'", ex);
		}
		try {
			if (isQuery) {
				if (_resultCount > 0)
					_statement.setMaxRows(_resultCount);
				_resultSet = _statement.getResultSet();
			} else {
				_resultCount = _statement.getUpdateCount();
				conditionalCommitTransaction();
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to get the result set.", ex);
		}
	}

	/*
	 * Executes a stored procedure with the specified parameters. Any results that
	 * the procedure returns should be obtained by calling
	 * returnValuesForLastStoredProcedureInvocation.
	 * 
	 * @see
	 * net.wotonomy.access.EOAdaptorChannel#executeStoredProcedure(net.wotonomy.
	 * access.EOStoredProcedure, net.wotonomy.foundation.NSDictionary)
	 */
	public void executeStoredProcedure(EOStoredProcedure proc, NSDictionary values) {
		if (!isOpen())
			throw new EOGeneralAdaptorException("Attempt to execute a stored procedure on a closed channel.");
		conditionalBeginTransaction();
		try {
			// Assemble the procedure call
			StringBuffer buf = new StringBuffer("{ call ");
			buf.append(proc.externalName());
			NSArray args = proc.arguments();
			if (args != null && args.count() > 0) {
				buf.append("[");
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute) args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.OutParameter) {
						buf.append('?');
						buf.append(", ");
					}
				}
				buf.delete(buf.length() - 2, buf.length());
				buf.append("]");
			}
			buf.append(" }");
			// get the callable statement
			CallableStatement sp = _context().connection().prepareCall(buf.toString());
			if (args != null && args.count() > 0) {
				int pos = 1;
				// set the in and inOut parameters
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute) args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.OutParameter) {
						Object val = values.objectForKey(a.name());
						if (val == NSKeyValueCoding.NullValue)
							sp.setNull(pos, 0); // TODO: check sql type
						if (val instanceof String)
							sp.setString(pos, (String) val);
						else if (val instanceof BigDecimal)
							sp.setBigDecimal(pos, (BigDecimal) val);
						else if (val instanceof NSTimestamp)
							sp.setTimestamp(pos, (NSTimestamp) val);
						else if (val instanceof NSData)
							sp.setBytes(pos, ((NSData) val).bytes());
						else if (val instanceof Integer)
							sp.setInt(pos, ((Integer) val).intValue());
						else if (val instanceof Long)
							sp.setLong(pos, ((Long) val).longValue());
						else
							sp.setObject(pos, val);
						pos++;
					}
				}
			}
			// run the procedure
			sp.execute();
			// get the return values
			if (args != null && args.count() > 0) {
				int pos = 1;
				NSMutableDictionary retvals = new NSMutableDictionary();
				for (int i = 0; i < args.count(); i++) {
					EOAttribute a = (EOAttribute) args.objectAtIndex(i);
					if (a.parameterDirection() != EOAttribute.InParameter) {
						Object val = sp.getObject(pos);
						if (val == null)
							retvals.setObjectForKey(NSKeyValueCoding.NullValue, a.name());
						else if (val instanceof Blob) {
							try {
								retvals.setObjectForKey(new NSData(((Blob) val).getBinaryStream(), 1024), a.name());
							} catch (java.io.IOException ex) {
								// what should I do here?
								retvals.setObjectForKey(NSData.EmptyData, a.name());
							}
						} else
							retvals.setObjectForKey(val, a.name());
						pos++;
					}
				}
				_spReturnValues = retvals;
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to execute stored procedure.", ex);
		}
		conditionalCommitTransaction();
	}

	/*
	 * Fetches one row from the database
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#fetchRow()
	 */
	public NSMutableDictionary fetchRow() {
		if (_resultSet == null) {
			return null;
		}
		if (attributesToFetch() == null)
			throw new EOGeneralAdaptorException("Attempt to fetchRow without setting attributes to fetch first.");
		try {
			// If the current result set ends, there may be another one
			if (!_resultSet.next()) {
				_resultSet.close();
				_resultAttributes = null;
				_fetchInProgress = _statement.getMoreResults();
				if (_fetchInProgress)
					_resultSet = _statement.getResultSet();
				return null;
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to fetch row.", ex);
		}

		// Assemble the dictionary
		NSMutableDictionary dict = new NSMutableDictionary(attributesToFetch().count());
		try {
			for (int i = 0; i < attributesToFetch().count(); i++) {
				EOAttribute a = (EOAttribute) attributesToFetch().objectAtIndex(i);
				Object o = _resultSet.getObject(i + 1);
				if (o == null)
					o = NSKeyValueCoding.NullValue;
				dict.setObjectForKey(o, a.name());
			}
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("While trying to create row.", ex);
		}
		return dict;
	}

	/*
	 * Inserts a row into a table in the database.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#insertRow(net.wotonomy.foundation.
	 * NSDictionary, net.wotonomy.access.EOEntity)
	 */
	public void insertRow(NSDictionary row, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareInsertExpressionWithRow(row);
		evaluateExpression(exp);
	}

	/*
	 * Indicates if a fetch is in progress; that is, if a SELECT statement was
	 * executed and there are still rows to be fetched.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#isFetchInProgress()
	 */
	public boolean isFetchInProgress() {
		return _fetchInProgress;
	}

	/*
	 * Indicates if the channel is open.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#isOpen()
	 */
	public boolean isOpen() {
		boolean open = (_context().connection() != null);
		try {
			open = open || !_context().connection().isClosed();
		} catch (SQLException ex) {
			open = false;
		}
		return open;
	}

	/*
	 * Opens the channel. If the adaptor context has not yet made a connection to
	 * the database, this forces the context to connect.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#openChannel()
	 */
	public void openChannel() {
		try {
			if (_context().connection() == null || _context().connection().isClosed())
				_context().connect();
		} catch (SQLException ex) {
			throw new JDBCAdaptorException("Cannot open connection to database.", ex);
		}
	}

	/*
	 * Returns the values obtained from the last stored procedure executed.
	 * 
	 * @see net.wotonomy.access.EOAdaptorChannel#
	 * returnValuesForLastStoredProcedureInvocation()
	 */
	public NSDictionary returnValuesForLastStoredProcedureInvocation() {
		return _spReturnValues;
	}

	/*
	 * Creates a SELECT expression and executes it. If the attribute array is null,
	 * then the result's metadata is used to dynamically create an array of
	 * attributes.
	 * 
	 * @see
	 * net.wotonomy.access.EOAdaptorChannel#selectAttributes(net.wotonomy.foundation
	 * .NSArray, net.wotonomy.control.EOFetchSpecification, boolean,
	 * net.wotonomy.access.EOEntity)
	 */
	public void selectAttributes(NSArray atts, EOFetchSpecification fspec, boolean lock, EOEntity entity) {
		_resultAttributes = atts;
		EOSQLExpression expr = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		_fetchInProgress = true;
		expr.prepareSelectExpressionWithAttributes(atts, lock, fspec);
		// for now we store the fetch limit here
		if (fspec != null)
			_resultCount = fspec.fetchLimit();
		evaluateExpression(expr);
	}

	/*
	 * Creates and executes an UPDATE statement.
	 * 
	 * @see
	 * net.wotonomy.access.EOAdaptorChannel#updateValuesInRowsDescribedByQualifier(
	 * net.wotonomy.foundation.NSDictionary, net.wotonomy.control.EOQualifier,
	 * net.wotonomy.access.EOEntity)
	 */
	public int updateValuesInRowsDescribedByQualifier(NSDictionary row, EOQualifier q, EOEntity entity) {
		EOSQLExpression exp = adaptorContext().adaptor().expressionFactory().createExpression(entity);
		exp.prepareUpdateExpressionWithRow(row, q);
		evaluateExpression(exp);
		return _resultCount;
	}

	protected void conditionalBeginTransaction() {
		_transactionWasOpen = adaptorContext().hasOpenTransaction();
		if (!_transactionWasOpen)
			adaptorContext().beginTransaction();
	}

	protected void conditionalCommitTransaction() {
		if (!_transactionWasOpen)
			adaptorContext().commitTransaction();
		_transactionWasOpen = false;
	}

}
/*
 * $Log$ Revision 1.2 2006/02/18 22:59:22 cgruber make it compile with maven
 * dependencies and add a cvsignore.
 *
 * Revision 1.1 2006/02/16 13:22:23 cgruber Check in all sources in
 * eclipse-friendly maven-enabled packages.
 *
 * Revision 1.3 2003/08/14 02:15:11 chochos added lots of comments
 *
 * Revision 1.2 2003/08/13 20:45:20 chochos small fixes in evaluateExpression,
 * which has been successfully tested with a SELECT statement.
 *
 * Revision 1.1 2003/08/13 20:12:48 chochos a subclass of EOAdaptorChannel to be
 * used with JDBC.
 *
 */