/*

  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.

 *

 */