View Javadoc

1   /* (c) Copyright 2003 Caleigo AB, All rights reserved. 
2    * 
3    * This library is free software; you can redistribute it and/or
4    * modify it under the terms of the GNU Lesser General Public
5    * License as published by the Free Software Foundation; either
6    * version 2.1 of the License, or (at your option) any later version.
7    * 
8    * This library is distributed in the hope that it will be useful,
9    * but WITHOUT ANY WARRANTY; without even the implied warranty of
10   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
11   * Lesser General Public License for more details.
12   * 
13   * You should have received a copy of the GNU Lesser General Public
14   * License along with this library; if not, write to the Free Software
15   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
16   *  
17   */
18  
19  package org.caleigo.core.service;
20  
21  
22  import java.util.*;
23  import java.sql.*;
24  
25  import org.caleigo.core.*;
26  import org.caleigo.core.exception.*;
27  import org.caleigo.toolkit.log.*;
28  
29  /*** <Description of JDBCDataService>
30   *
31   * @author  Dennis Zikovic
32   * @version 1.00
33   * 
34   *//* 
35   *
36   * WHEN        WHO               WHY & WHAT
37   * ------------------------------------------------------------------------------
38   * 2001-07-20  Dennis Zikovic    Creation
39   */
40  public class JDBCDataService extends AbstractDataService
41  {
42      // Constants ---------------------------------------------------------------
43      public static final int DEFAULT_MAX_CONNECTION_POOL_SIZE = 10;
44      
45      private static final int UNDEFINED_AUTO_INDEX = 0;
46      private static final int JDBC_AUTO_INDEX = 1;
47      private static final int SQLSERVER_AUTO_INDEX = 2;
48      private static final int POSTGRE_AUTO_INDEX = 3;
49      private static final int ORACLE_AUTO_INDEX = 4;
50      private static final int HSQLDB_AUTO_INDEX = 5;
51      private static final int UNSUPORTED_AUTO_INDEX = -1;
52      
53      // Class members -----------------------------------------------------------
54      private static Driver sDriver;
55      
56      // Data members ------------------------------------------------------------
57      private String mConnectionURL;
58      private Properties mConnectionInfo;
59      private SQLToolKit mSQLToolKit;
60      private ConnectionPool mConnectionPool;
61      
62      private int mAutoIndexMethod = UNDEFINED_AUTO_INDEX;
63      
64      private boolean mCalculateQuerySize = false;
65      private ThreadLocal mNumberOfOperations = new ThreadLocal();
66      private ThreadLocal mTransaction = new ThreadLocal();
67      
68      // Class methods -----------------------------------------------------------
69  //    static
70  //    {
71  //        if (sDriver == null)
72  //            try
73  //            {
74  //                Class driverClass = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
75  //                setDriver((Driver) driverClass.newInstance());
76  //            } catch (Exception e) {}
77  //    }
78      
79      public static void setDriver(Driver driver)
80      {
81          sDriver = driver;
82      }
83      
84      // Constructors ------------------------------------------------------------
85      
86      /*** Default constructor for JDBCDataService.
87       */
88      public JDBCDataService(IDataSourceDescriptor descriptor)
89      {
90          this(descriptor, descriptor.getSourceName(), "jdbc:odbc:"+descriptor.getSourceName(), new Properties(), DEFAULT_MAX_CONNECTION_POOL_SIZE);
91      }
92      
93      public JDBCDataService(IDataSourceDescriptor descriptor, Object serviceIdentity, String url)
94      {
95          this(descriptor, serviceIdentity, url, new Properties(), DEFAULT_MAX_CONNECTION_POOL_SIZE);
96      }
97      
98      public JDBCDataService(IDataSourceDescriptor descriptor, Object serviceIdentity, String url, String user, String password)
99      {
100         this(descriptor, serviceIdentity, url, new Properties(), DEFAULT_MAX_CONNECTION_POOL_SIZE);
101         mConnectionInfo.put("user", user);
102         mConnectionInfo.put("password", password);
103     }
104     
105     public JDBCDataService(IDataSourceDescriptor descriptor, Object serviceIdentity, String url, String user, String password, int maxConnectionPoolSize)
106     {
107         this(descriptor, serviceIdentity, url, new Properties(), maxConnectionPoolSize);
108         mConnectionInfo.put("user", user);
109         mConnectionInfo.put("password", password);
110     }
111     
112     public JDBCDataService(IDataSourceDescriptor descriptor, Object serviceIdentity, String url, String user, String password, String catalog)
113     {
114         this(descriptor, serviceIdentity, url, user, password);
115         if (catalog != null)
116             mConnectionInfo.put("catalog", catalog);
117     }
118     
119     public JDBCDataService(IDataSourceDescriptor descriptor, Object serviceIdentity, String url, java.util.Properties info, int maxConnectionPoolSize)
120     {
121         super(descriptor.getCodeName(), serviceIdentity, descriptor);
122         mConnectionURL = url;
123         mConnectionInfo = info;
124         mSQLToolKit = new SQLToolKit();
125         mConnectionPool = new ConnectionPool(maxConnectionPoolSize);
126         
127         // Perform data access verifaction on the data source.
128         if(DataAccessManager.getManager().getAccessLevel(descriptor)==DataAccessManager.NONE)
129             throw new SecurityException("No read access for "+descriptor+" data sources!");
130     }
131     
132     // IDataService implementation ---------------------------------------------
133     public IDataTransaction newTransaction()
134     {
135         return new JDBCDataTransaction();
136     }
137         
138     /*** Should return true if the service is online and reponding to calls.
139      */ 
140     public boolean ping()
141     {
142         boolean responding = false;
143         try
144         {
145             Connection connection = this.openConnection();
146             if(connection!=null)
147             {
148                 this.closeConnection(connection);
149                 responding = true;
150             }
151         }
152         catch(Exception e)
153         { 
154         } 
155         return responding;
156     }
157     
158     // Action methods ----------------------------------------------------------
159     
160     protected void executeLoad(Connection connection, IEntity entity, Qualifier qualifier) throws DataServiceException
161     {
162         String sql = null;
163         try
164         {
165             // Perform security access check.
166             if(DataAccessManager.getManager().getAccessLevel(entity.getEntityDescriptor())==DataAccessManager.NONE)
167                 throw new SecurityException("No read access for "+entity.getEntityDescriptor()+" entities!");
168                 
169             // Perform query.
170             if(!qualifier.canUniquelyQualify(entity.getEntityDescriptor()))
171                 throw new InvalidQualifierException("The qualifier must be an" +
172                         "uniqe qualifier for this entity's entity descriptor");
173             DataQuery dataQuery = new DataQuery(entity.getEntityDescriptor(), qualifier);
174             sql = mSQLToolKit.buildSelectCommand(dataQuery);
175             
176             Statement statement = connection.createStatement();
177             Log.print(this, "Performing Select: "+sql);
178             ResultSet set = statement.executeQuery(sql);
179             
180             // Read the result and update the entity status.
181             if(set.next())
182             {
183                 this.readResultSetRow(set, entity, false);
184                 
185                 // Reset entity if data READ access is not granted.
186                 if(!DataAccessManager.getManager().hasReadAccess(entity))
187                     entity.clear();
188                 else
189                 {
190                     entity.setStatusFlag(IEntity.PERSISTENT);
191                     entity.clearStatusFlag(IEntity.DIRTY | IEntity.EMPTY);
192                 }
193             }
194             else
195             {
196                 entity.setStatusFlag(IEntity.EMPTY);
197                 entity.clearStatusFlag(IEntity.DIRTY | IEntity.PERSISTENT);
198             }
199             set.close();
200         }
201         catch(SQLException e) 
202         { 
203             throw new DataServiceException("Select command failed", e, sql);
204         } 
205     }
206     
207     protected void executeQuery(Connection connection, DataQuery query, ISelection selection) throws DataServiceException
208     {
209         String sql = null;
210         try
211         {
212             // Perform security access check.
213             if(DataAccessManager.getManager().getAccessLevel(query.getEntityDescriptor())==DataAccessManager.NONE)
214                 throw new SecurityException("No read access for "+query.getEntityDescriptor()+" entities!");
215                 
216             // Perform query.
217             sql = mSQLToolKit.buildSelectCommand(query);
218 
219             Statement statement = connection.createStatement();
220             Log.print(this, "Performing Select: "+sql);
221             ResultSet set = statement.executeQuery(sql);
222             
223             // Read the result.
224             int nbrOfOperations = ((Integer) mNumberOfOperations.get()).intValue();
225             JDBCDataTransaction transaction = (JDBCDataTransaction) mTransaction.get();
226             IEntity entity;
227             
228             // Calculate the number of rows
229             float nbrOfRows = -1;
230             if (mCalculateQuerySize)
231             {
232                 long sizeCalcStart = System.currentTimeMillis();
233                 
234                 while(set.next())
235                     nbrOfRows++;
236                     
237                 Log.print(this, "Size calculation time: " + (System.currentTimeMillis() - sizeCalcStart));
238                     
239                 set.close();
240                 set = statement.executeQuery(sql);
241             }
242             
243             while(set.next() && !transaction.isAborted())
244             {
245                 // Create the entity
246                 entity = query.getEntityDescriptor().createEntity();
247 
248                 // Copy data from the set into the entity.
249                 this.readResultSetRow(set, entity, true);
250 
251                 // Update the entity status.
252                 entity.setStatusFlag(IEntity.PERSISTENT);
253                 entity.clearStatusFlag(IEntity.DIRTY | IEntity.EMPTY);
254                 
255                 // Store the entity if data READ access access is granted.
256                 if(DataAccessManager.getManager().hasReadAccess(entity))
257                     selection.addEntity(entity);
258                     
259                 // Update progress
260                 if (nbrOfRows != -1)
261                 {
262                     int progress = Math.round((set.getRow() / nbrOfRows) * 100f);
263                     transaction.updateProgress(progress);
264                 }
265             }
266             set.close();
267         }
268         catch(SQLException e) 
269         { 
270             throw new DataServiceException("Select command failed", e, sql);
271         } 
272     }
273     
274     protected void executeInsert(Connection connection, IEntity entity) throws DataServiceException
275     {
276         String sql = null;
277         try
278         {
279             // Perform security access check.
280             this.checkEntityAsStorable(entity);
281 //            if(!DataAccessManager.getManager().hasWriteAccess(entity))
282 //                throw new SecurityException("No write access for "+entity+"!");
283                 
284             // Prepare update command.
285             sql = mSQLToolKit.buildInsertCommand(entity);
286             Statement statement = connection.createStatement();
287             Log.print(this, "Performing Insert: "+sql);
288             
289             // Perform update command.
290             int count = -1;
291             if ((mAutoIndexMethod==JDBC_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX) && (mConnectionURL.indexOf("hsqldb")==-1))
292             {
293                 try
294                 {
295                     count = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
296                 }
297                 catch(Exception e)
298                 {
299                 }
300             }
301             if(count<0)
302                 count = statement.executeUpdate(sql);
303             
304             // Cast exception and cause roleback if single insert failed.
305             if(count!=1)
306                 throw new DataServiceException("Multiple insert commands are dissabled, affect count "+count+" caused rejection.");
307             
308             // Update autogenerated key indexes.
309             if(this.hasAutoIndexField(entity.getEntityDescriptor()))
310                 this.updateAutoIndex(statement, entity);
311         }
312         catch(SQLException e) 
313         { 
314             throw new DataServiceException("Insert command failed", e, sql);
315         } 
316     }
317     
318     protected void executeUpdate(Connection connection, IEntity entity, Qualifier qualifier) throws DataServiceException
319     {
320         String sql = null;
321         try
322         {
323             // Perform security access check.
324             this.checkEntityAsStorable(entity);
325 //            if(!DataAccessManager.getManager().hasWriteAccess(entity))
326 //                throw new SecurityException("No write access for "+entity+"!");
327                 
328             // Cancel update if entity is not dirty.
329             if(!entity.isDirty())
330             {
331                 Log.printWarning(this, "Ignored update of non-dirty entity: "+entity);
332                 return;
333             }
334             
335             // Perform update command.
336             sql = mSQLToolKit.buildUpdateCommand(entity, qualifier);
337             Statement statement = connection.createStatement();
338             Log.print(this, "Performing Update: "+sql);
339             int count = statement.executeUpdate(sql);
340             
341             // Cast exception and cause roleback if single update failed.
342             if(count!=1)
343                 throw new DataServiceException("Multiple update commands are dissabled, affect count "+count+" caused rejection.");
344         }
345         catch(SQLException e) 
346         { 
347             throw new DataServiceException("Update command failed", e, sql);
348         } 
349     }
350     
351     protected void executeDelete(Connection connection, IEntity entity) throws DataServiceException
352     {
353         String sql = null;
354         try
355         {
356             // Perform security access check.
357             this.checkEntityAsDeletable(entity);
358 //            if(!DataAccessManager.getManager().hasWriteAccess(entity))
359 //                throw new SecurityException("No write access for "+entity+"!");
360                 
361             // Perform delete command.
362             sql = mSQLToolKit.buildDeleteCommand(entity.getEntityDescriptor(), entity.getOriginQualifier());
363             Statement statement = connection.createStatement();
364             Log.print(this, "Performing Delete: "+sql);
365             int count = statement.executeUpdate(sql);
366             if(count>1)
367                 throw new DataServiceException("Multiple delete commands are dissabled, affect count "+count+" caused rejection.");
368         }
369         catch(SQLException e) 
370         { 
371             throw new DataServiceException("Delete command failed", e, sql);
372         } 
373     }
374     
375     protected void executeCreateTable(Connection connection, IEntityDescriptor entityDescriptor) throws DataServiceException
376     {
377         String sql = null;
378         try
379         {
380             // Perform delete command.
381             sql = mSQLToolKit.buildCreateTableCommand(entityDescriptor);
382             Statement statement = connection.createStatement();
383             Log.print(this, "Performing Create: "+sql);
384             int count = statement.executeUpdate(sql);
385             if(count>1)
386                 throw new DataServiceException("Multiple delete commands are dissabled, affect count "+count+" caused rejection.");
387         }
388         catch(SQLException e) 
389         { 
390             throw new DataServiceException("Create command failed", e, sql);
391         } 
392     }
393     
394     protected void executeCreateRelation(Connection connection, IEntityRelation entityRelation) throws DataServiceException
395     {
396         String sql = null;
397         try
398         {
399             // Perform delete command.
400             sql = mSQLToolKit.buildCreateRelationCommand(entityRelation);
401             Statement statement = connection.createStatement();
402             Log.print(this, "Performing Create: "+sql);
403             int count = statement.executeUpdate(sql);
404             if(count>1)
405                 throw new DataServiceException("Multiple delete commands are dissabled, affect count "+count+" caused rejection.");
406         }
407         catch(SQLException e) 
408         { 
409             throw new DataServiceException("Create comma relation command failed", e, sql);
410         } 
411     }
412     
413     // Access methods ----------------------------------------------------------
414     public String getURL() 
415     {
416         return mConnectionURL;
417     }
418     
419     public String getUser() 
420     {
421         return mConnectionInfo.getProperty("user", null);
422     }
423     
424     public String getPassword() 
425     {
426         return mConnectionInfo.getProperty("password", null);
427     }
428     
429     public SQLToolKit getSQLToolKit()
430     {
431         return mSQLToolKit;
432     }
433     
434     public void setSQLToolKit(SQLToolKit kit)
435     {
436         mSQLToolKit = kit;
437     }
438     
439     /***
440      * Changes the size of the connection pool.
441      * 
442      * @param maxConnectionPoolSize
443      * @throws IllegalStateException  if there are open connections in the connection pool.
444      */
445     public void setMaxConnectionPoolSize(int maxConnectionPoolSize)
446     {
447         mConnectionPool.setMaxConnectionPoolSize(maxConnectionPoolSize);
448     }
449     
450     /*** <p>Sets if this data service should calculate the size of a quary before
451      * the query result data is proccessed. This is used for calculating the progress
452      * of a query.
453      * Invoking this mehtod with true means that each query will be executed two
454      * times in the database. Depending on the jdbc driver implementation this
455      * could have a great impact on performance.
456      * 
457      * <p>By default no query sizes are calculated.
458      */
459     public void setCalculateQuerySize(boolean calculateSize)
460     {
461         mCalculateQuerySize = calculateSize;
462     }
463     
464     // Help methods ------------------------------------------------------------
465     
466     protected Connection openConnection() throws DataServiceException
467     {
468         return mConnectionPool.getConnection();
469     }
470     
471     protected void closeConnection(Connection connection) throws DataServiceException
472     {
473         mConnectionPool.releaseConnection(connection);
474     }
475     
476     protected void readResultSetRow(ResultSet set, IEntity entity, boolean useFastSetData) throws DataServiceException
477     {
478         try
479         {
480             IEntityDescriptor descriptor = entity.getEntityDescriptor();
481             SQLToolKit.IDataTypeConverter converter =null;
482             DataType dataType = null;
483             Object data = null;
484             
485             for(int j=0; j<set.getMetaData().getColumnCount(); j++)
486             {
487             	// Get field data.
488                 dataType = descriptor.getFieldDescriptor(j).getDataType();
489                 
490                 // Convert field data.
491                 converter = mSQLToolKit.getDataTypeConverter(dataType);
492                 data = this.getSetData(set, j+1, dataType);
493                 if(converter!=null)
494                     data = converter.convertFromDB(data);
495                 else if(data!=null && data.getClass()!=dataType.getDataClass())
496                     data = dataType.convertFrom(data);
497                     
498                 // Set data to entity. Note that if the useFastSetData flag is
499                 // set we can avoid the more resource demanding set data method.
500                 if(useFastSetData)
501                     this.setEntityData(entity, j, data);
502                 else
503                     entity.setData(descriptor.getFieldDescriptor(j), data);
504             }
505         }
506         catch(Exception e) 
507         { 
508             throw new DataServiceException("Select command failed", e);
509         } 
510     }
511     
512     protected Object getSetData(ResultSet set, int index, DataType dataType)
513     {
514         try
515         {
516             Object data = set.getObject(index);
517             
518             // Fix to handle hadle JDBC drivers that are inaware data types.
519             if(data==null && !set.wasNull())
520             {
521                 if(dataType==DataType.STRING)
522                     data = set.getString(index);
523                 else if(dataType==DataType.BYTE)
524                     data = new Byte(set.getByte(index));
525                 else if(dataType==DataType.SHORT)
526                     data = new Short(set.getShort(index));
527                 else if(dataType==DataType.INTEGER)
528                     data = new Integer(set.getInt(index));
529                 else if(dataType==DataType.LONG)
530                     data = new Long(set.getLong(index));
531                 else if(dataType==DataType.FLOAT)
532                     data = new Float(set.getFloat(index));
533                 else if(dataType==DataType.DOUBLE)
534                     data = new Double(set.getDouble(index));
535                 else if(dataType==DataType.BOOLEAN)
536                     data = new Boolean(set.getBoolean(index));
537                 else if(dataType instanceof DataType.BinaryType)
538                     data = dataType.convertFrom(set.getBinaryStream(index)); // (set.getBytes(index));
539             }
540             return data;
541         }
542         catch(Exception e) 
543         { 
544             throw new DataServiceException("Select command failed", e);
545         } 
546     }
547         
548     /*** This method updates autogenerated primary key field values.
549      * Note that this method can only be used directly after a insert and
550      * should still not be considered safe in an environment with frequent 
551      * inserts to the entity's table.
552      */
553     protected void updateAutoIndex(Statement statement, IEntity entity) throws DataServiceException
554     {
555         String com = null;        
556         IFieldDescriptor field = null;
557         
558         if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
559             Log.print(this, "Evaluating usable auto index method.");
560                 
561         // Standard java.sql method to access auto indexes. Requires 1.4 driver.
562         if((mAutoIndexMethod==JDBC_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX) && (mConnectionURL.indexOf("hsqldb")==-1))
563         {
564             try
565             {
566                 ResultSet set = statement.getGeneratedKeys();
567                 for(int j=0; j<entity.getEntityDescriptor().getFieldCount(); j++)
568                 {
569                     field = entity.getEntityDescriptor().getFieldDescriptor(j);
570                     if(field.isAutoGenerated() && field.isIdentityField())
571                     {
572                         // Read the result.
573                         if(set.next())
574                         {
575                             Object data = field.getDataType().convertFrom(set.getObject(1));
576                             entity.setData(field, data);
577                 
578                             mAutoIndexMethod = JDBC_AUTO_INDEX;
579                         }
580                     }
581                 }
582                 while(set.next());
583             }
584             catch(Exception e)
585             {
586                 if(mAutoIndexMethod==JDBC_AUTO_INDEX)
587                     throw new DataServiceException("Update of auto generated index failed!", e, com);
588             }
589             if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
590                 Log.print(this, "JDBC method not applicable.");
591         }
592         
593         // PostgreSQL specific solution.
594         if(mAutoIndexMethod==POSTGRE_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
595         {
596             try
597             {
598                 for(int j=0; j<entity.getEntityDescriptor().getFieldCount(); j++)
599                 {
600                     field = entity.getEntityDescriptor().getFieldDescriptor(j);
601                     if(field.isAutoGenerated() && field.isIdentityField())
602                     {
603                         com = "SELECT currval('"+field.getEntityDescriptor().getSourceName()+"_"+field.getSourceName()+"_seq') ";
604                         Statement identityStatement = statement.getConnection().createStatement();
605                         Log.print(this, "Key retrieval: "+com);
606                         ResultSet set = identityStatement.executeQuery(com);
607 
608                         // Read the result.
609                         if(set.next())
610                         {
611                             Object data = field.getDataType().convertFrom(set.getObject(1));
612                             entity.setData(field, data);
613                 
614                             mAutoIndexMethod = POSTGRE_AUTO_INDEX;
615                         }
616                         while(set.next());
617                     }
618                 }
619             }
620             catch(Exception e) 
621             { 
622                 Log.printWarning(this, "Postgre method failed!");
623                 if(mAutoIndexMethod==POSTGRE_AUTO_INDEX)
624                     throw new DataServiceException("Update of auto generated index failed!", e, com);
625             } 
626             if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
627                Log.print(this, "Postgre method not applicable.");
628         }
629         
630         // Microsoft SQL-Server specific solution.
631         if(mAutoIndexMethod==SQLSERVER_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
632         {
633             try
634             {
635                 for(int j=0; j<entity.getEntityDescriptor().getFieldCount(); j++)
636                 {
637                     field = entity.getEntityDescriptor().getFieldDescriptor(j);
638                     if(field.isAutoGenerated() && field.isIdentityField())
639                     {
640                         String sourceIdentifier = field.getEntityDescriptor().getSourceName();
641                         if (mSQLToolKit.isQuotingIdentifiers())
642                             sourceIdentifier = mSQLToolKit.getQuotingString() + field.getEntityDescriptor().getSourceName() + mSQLToolKit.getQuotingString();
643                         
644                         com = "SELECT @@IDENTITY FROM " + sourceIdentifier;
645                         Statement identityStatement = statement.getConnection().createStatement();
646                         Log.print(this, "Key retrieval: "+com);
647                         ResultSet set = identityStatement.executeQuery(com);
648 
649                         // Read the result.
650                         if(set.next())
651                         {
652                             Object data = field.getDataType().convertFrom(set.getObject(1));
653                             entity.setData(field, data);
654                 
655                             mAutoIndexMethod = SQLSERVER_AUTO_INDEX;
656                         }
657                         while(set.next());
658                     }
659                 }
660             }
661             catch(Exception e) 
662             { 
663                 if(mAutoIndexMethod==SQLSERVER_AUTO_INDEX)
664                    throw new DataServiceException("Update of auto generated index failed!", e, com);
665             } 
666             if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
667                 Log.print(this, "SQL-Server method not applicable.");
668         }
669         
670          // Oracle specific solution.
671         if(mAutoIndexMethod==ORACLE_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
672         {
673             try
674             {
675                 for(int j=0; j<entity.getEntityDescriptor().getFieldCount(); j++)
676                 {
677                     field = entity.getEntityDescriptor().getFieldDescriptor(j);
678                     if(field.isAutoGenerated() && field.isIdentityField())
679                     {
680                         com = "SELECT " + field.getEntityDescriptor().getSourceName() + "_" + field.getSourceName() + "_seq.currval FROM dual " + field.getEntityDescriptor().getSourceName();
681                         Statement identityStatement = statement.getConnection().createStatement();
682                         Log.print(this, "Key retrieval: "+com);
683                         ResultSet set = identityStatement.executeQuery(com);
684 
685                         // Read the result.
686                         if(set.next())
687                         {
688                             Object data = field.getDataType().convertFrom(set.getObject(1));
689                             entity.setData(field, data);
690                 
691                             mAutoIndexMethod = ORACLE_AUTO_INDEX;
692                         }
693                         while(set.next());
694                     }
695                 }
696             }
697             catch(Exception e) 
698             { 
699                 if(mAutoIndexMethod==ORACLE_AUTO_INDEX)
700                    throw new DataServiceException("Update of auto generated index failed!", e, com);
701             } 
702             if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
703                 Log.print(this, "Oracle method not applicable.");
704         }
705         
706         // Oracle specific solution.
707        if(mAutoIndexMethod==HSQLDB_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
708        {
709            try
710            {
711                for(int j=0; j<entity.getEntityDescriptor().getFieldCount(); j++)
712                {
713                    field = entity.getEntityDescriptor().getFieldDescriptor(j);
714                    if(field.isAutoGenerated() && field.isIdentityField())
715                    {
716                        com = "call identity()";
717                        Statement identityStatement = statement.getConnection().createStatement();
718                        Log.print(this, "Key retrieval: "+com);
719                        ResultSet set = identityStatement.executeQuery(com);
720 
721                        // Read the result.
722                        if(set.next())
723                        {
724                            Object data = field.getDataType().convertFrom(set.getObject(1));
725                            entity.setData(field, data);
726         
727                            mAutoIndexMethod = HSQLDB_AUTO_INDEX;
728                        }
729                        while(set.next());
730                    }
731                }
732            }
733            catch(Exception e) 
734            { 
735                if(mAutoIndexMethod==HSQLDB_AUTO_INDEX)
736                   throw new DataServiceException("Update of auto generated index failed!", e, com);
737            } 
738            if(mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
739                Log.print(this, "hsqldb method not applicable.");
740        }
741         
742         
743         
744         if(mAutoIndexMethod==UNSUPORTED_AUTO_INDEX || mAutoIndexMethod==UNDEFINED_AUTO_INDEX)
745         {
746             mAutoIndexMethod=UNSUPORTED_AUTO_INDEX;
747             throw new DataServiceException("Used JDBC Driver does not support extraction of Generated Keys!");    
748         }
749     }
750     
751     protected boolean hasAutoIndexField(IEntityDescriptor entityDescriptor)
752     {
753         IFieldDescriptor autoIndexField = null;
754         for(int j=0; autoIndexField==null && j<entityDescriptor.getFieldCount(); j++)
755             if(entityDescriptor.getFieldDescriptor(j).isAutoGenerated() && entityDescriptor.getFieldDescriptor(j).isIdentityField())
756                 autoIndexField = entityDescriptor.getFieldDescriptor(j);
757         return autoIndexField!=null;
758     }
759     
760     // Nested classes ----------------------------------------------------------
761     protected class JDBCDataTransaction extends AbstractDataTransaction
762     {
763         // Data members --------------------------------------------------------
764         private Connection mConnection;
765         private boolean mIsAborted;
766         private int mNbrOfOperations;
767         private int mCurrentOperation;
768         
769         // Constructors --------------------------------------------------------
770         public JDBCDataTransaction()
771         {
772             super(getTimeout());
773         }
774         
775         // Superclass overrides ------------------------------------------------
776         
777         /*** Commit performs all the stored operations in the transaction. 
778          * If any of the operations fail a rollback on all operations will be
779          * automatically performed and a TransactionFailedException will be thrown.
780          */
781         public synchronized void commit() throws DataServiceException
782         {
783             long commitStartTime = System.currentTimeMillis();
784             
785             // Get a connection.
786             mConnection = openConnection();
787             
788             // Log warning for long connection access time.
789             if(System.currentTimeMillis()-commitStartTime>50)
790             	Log.printWarning(this, "Long connection open time: " + (System.currentTimeMillis() - commitStartTime) + " ms");
791             
792             // Execute all operations
793             try
794             {
795                 try
796                 {
797                     if(mConnectionInfo.containsKey("catalog"))
798                         mConnection.setCatalog((String) mConnectionInfo.get("catalog"));
799                 }
800                 catch (SQLException e) {}
801                 
802                 // Perform operations
803                 try
804                 {
805                     mConnection.setAutoCommit(false);
806                 }
807                 catch(Exception e)
808                 {
809                     Log.printWarning(this, "Transaction used without database support!");
810                 }
811                 Enumeration dataOperations = this.getOperations();
812                 
813                 // Update thread local variables to handle progress
814                 mNumberOfOperations.set(new Integer(this.getNbrOfOperations()));
815                 mTransaction.set(this);
816                 
817                 mNbrOfOperations = this.getNbrOfOperations();
818                 if (mNbrOfOperations > 1)
819                     this.updateProgress(this.getNbrOfOperations() * 100, 0);
820                     
821                 mCurrentOperation = 1;
822                 while(dataOperations.hasMoreElements())
823                 {		
824                     DataOperation operation = (DataOperation)dataOperations.nextElement();
825                     switch(operation.getOperationType())
826                     {	
827                         case DataOperation.LOAD:
828                             executeLoad(mConnection, operation.getEntity(), operation.getQualifier());
829                         break;
830                         case DataOperation.QUERY:
831                             executeQuery(mConnection, operation.getDataQuery(), operation.getEntitySelection());
832                         break;
833                         case DataOperation.STORE:
834                             if(operation.getEntity().isPersistent())
835                                 executeUpdate(mConnection, operation.getEntity(), operation.getQualifier());
836                             else
837                                 executeInsert(mConnection, operation.getEntity());
838                         break;
839                         case DataOperation.DELETE:
840                             executeDelete(mConnection, operation.getEntity());
841                         break;
842                         case DataOperation.REFRESH:
843                             executeLoad(mConnection, operation.getEntity(), operation.getQualifier());
844                         break;
845                         case DataOperation.CREATE_TABLE:
846                             executeCreateTable(mConnection, operation.getEntityDescriptor());
847                         break;
848                         case DataOperation.CREATE_RELATION:
849                             executeCreateRelation(mConnection, operation.getEntityRelation());
850                         break;  			
851                     }
852                     
853                     if (mNbrOfOperations > 1)
854                         this.updateProgress(mCurrentOperation * 100);
855                     mCurrentOperation++;
856                 }
857                 
858                 // Commit transaction.
859                 mConnection.commit();
860                 Log.print(this, "Commit time: " + (System.currentTimeMillis() - commitStartTime) + " ms");
861                 
862                 // Update flags.
863                 dataOperations = this.getOperations();	
864                 while(dataOperations.hasMoreElements())
865                 {		
866                     DataOperation operation = (DataOperation)dataOperations.nextElement();
867                     switch(operation.getOperationType())
868                     {	
869                         case DataOperation.DELETE:
870                             operation.getEntity().clearStatusFlag(IEntity.DIRTY | IEntity.EMPTY | IEntity.PERSISTENT);
871                         break;
872                         case DataOperation.STORE:
873                             operation.getEntity().setStatusFlag(IEntity.PERSISTENT);
874                             operation.getEntity().clearStatusFlag(IEntity.DIRTY | IEntity.EMPTY);
875                         break;	
876                         case DataOperation.REFRESH: // Sets flags independant of transaction sucess!!
877                         case DataOperation.LOAD:
878                         case DataOperation.QUERY:
879                         break;
880                     }
881                 }                
882             }
883             catch(Exception e)
884             {
885                 String rollbackMsg;
886                 
887                 // Perform rollback on connection.
888                 try
889                 {
890                     mConnection.rollback();
891                     rollbackMsg = "rollback succesfull";
892                 }
893                 catch(SQLException eSQL)
894                 {   
895                     rollbackMsg = "rollback failed";
896                 }
897                 
898                 // Throw proper API exception.
899                 if (!mIsAborted)
900                 {
901                     if(e instanceof DataServiceException)
902                         throw new DataServiceException("Transaction failed, "+rollbackMsg, e, ((DataServiceException)e).getDescription());
903                     else
904                         throw new DataServiceException("Transaction failed, "+rollbackMsg+": "+e.getClass().getName()+" - "+e.getMessage(), e);
905                 }
906             }
907             finally
908             {
909                 // Return the connection for closuere or pooling.
910                 synchronized (mConnection)
911                 {
912                     closeConnection(mConnection);
913                     mConnection = null;
914                     mIsAborted = false;
915                 }
916             }
917 
918             // Adjust entity flags.
919         }
920         
921         public void abortTransaction() throws DataServiceException
922         {
923             try
924             {
925                 if (mConnection != null)
926                 {
927                     synchronized (mConnection)
928                     {
929                         Log.print(this, "Aborting transaction");
930                         mIsAborted = true;
931                         
932                         if (mConnection.getAutoCommit() == false)
933                             mConnection.rollback();
934                             
935                         mConnection.close();
936                     }
937                 }
938             } catch (SQLException e)
939             {
940                 throw new DataServiceException("Failed to abort transaction", e);
941             }
942         }
943         
944         // Access methods ------------------------------------------------------
945         public boolean isAborted()
946         {
947             return mIsAborted;
948         }
949                 
950         // Action methods ------------------------------------------------------
951         protected void updateProgress(int currentProgress)
952         {
953             super.updateProgress(mNbrOfOperations * 100, currentProgress * mCurrentOperation);
954         }
955     }
956     
957     protected class ConnectionPool
958     {
959         // Data members --------------------------------------------------------
960         private int mMaxSize;
961         private Connection[] mConnectionPool;
962         private boolean[] mFreeConnectionFlags;
963         private List mWaitingThreads;
964         
965         // Constructors --------------------------------------------------------
966         
967         /*** Creates a connection pool with the maximum size <code>maxSize</code>.
968          * Setting the maximum size to zero means that every call to <code>getConnection</code>
969          * will create a new connection.
970          */
971         public ConnectionPool(int maxSize)
972         {
973             mMaxSize = maxSize;
974             this.init();
975         }
976         
977         // Access methods ------------------------------------------------------
978         public synchronized void setMaxConnectionPoolSize(int maxConnectionPoolSize)
979         {
980             boolean okToChangeSize = true;
981             if (mFreeConnectionFlags != null)
982                 for (int i = 0; okToChangeSize && i < mFreeConnectionFlags.length; i++)
983                     okToChangeSize = mFreeConnectionFlags[i];
984                 
985             if (okToChangeSize)
986             {
987                 mMaxSize = maxConnectionPoolSize;
988                 this.init();
989             }
990             else
991                 throw new IllegalStateException("Open connections found");
992         }
993         
994         public Connection getConnection() throws DataServiceException
995         {
996             // If the pool size is zero just create a new connection
997             if(mMaxSize == 0)
998             {
999                 try
1000                 {
1001                     if (sDriver != null)
1002                         return sDriver.connect(mConnectionURL, mConnectionInfo);
1003 
1004                     if (mConnectionInfo.get("user") != null)
1005                         return DriverManager.getConnection(mConnectionURL, mConnectionInfo);
1006                     else
1007                         return DriverManager.getConnection(mConnectionURL);
1008                 }
1009                 catch(SQLException e)
1010                 { 
1011                     throw new DataServiceException("Failed to open database connection: " + e.getMessage());
1012                 }
1013             }
1014             
1015             // Get a free connection from the connection pool
1016             //Connection conn = null;
1017             try
1018             {
1019                 return this.getConnectionFromPool();
1020                 /*while ((conn = this.getNextFreeConnection()) == null)
1021                     this.wait();*/
1022             }
1023             catch (Exception e)
1024             {
1025                 throw new DataServiceException("Failed to open database connection: " + e.getMessage());
1026             }
1027             //return conn;
1028         }
1029         
1030         public int getMaxSize()
1031         {
1032             return mMaxSize;
1033         }
1034         
1035         // Action methods ------------------------------------------------------
1036         public synchronized void releaseConnection(Connection connection)
1037             throws DataServiceException
1038         {
1039             if(connection == null)
1040                 return;
1041             
1042             // If the pool size is zero just create a new connection
1043             if(mMaxSize == 0)
1044             {
1045                 try
1046                 {
1047                     connection.close();
1048                     return;
1049                 }
1050                 catch(SQLException e)
1051                 { 
1052                     throw new DataServiceException("Failed to open database connection: " + e.getMessage());
1053                 }
1054             }
1055             
1056             // Find the index of the connection
1057             int index = 0;
1058             for(; index < mConnectionPool.length && mConnectionPool[index] != connection; index++);
1059             
1060             // Mark the connection as free
1061             if(index < mConnectionPool.length)
1062             {
1063                 mFreeConnectionFlags[index] = true;
1064                 this.notifyAll();
1065             }
1066         }
1067         
1068         // Help methods --------------------------------------------------------
1069         private synchronized Connection getConnectionFromPool()
1070             throws SQLException, InterruptedException
1071         {
1072             Connection conn = null;
1073             // If there are no threads waiting for a connection try to get a free connection
1074             if(mWaitingThreads.size() == 0)
1075                 conn = this.getNextFreeConnectionFromPool();
1076             
1077             // If there was no free connection add this thread to the queue of waiting threads
1078             if(conn == null)
1079                 mWaitingThreads.add(Thread.currentThread());
1080             
1081             while(conn == null)
1082             {
1083                 Log.print(this, "Waiting for free connection.");
1084                 this.wait();
1085                 // Only try to get a free connection if the current thread is first in the queue
1086                 if (mWaitingThreads.get(0) == Thread.currentThread())
1087                     conn = this.getNextFreeConnectionFromPool();
1088             }
1089             
1090             // Remove the current thread from the queue if necessary
1091             if(mWaitingThreads.contains(Thread.currentThread()));
1092                 mWaitingThreads.remove(Thread.currentThread());
1093                 
1094             return conn;
1095         }
1096         
1097         private synchronized Connection getNextFreeConnectionFromPool()
1098             throws SQLException
1099         {
1100             int index = 0;
1101             
1102             // Find the index of the first free connection in the connection pool
1103             for(; index < mConnectionPool.length && mConnectionPool[index] != null && !mFreeConnectionFlags[index]; index++);
1104             
1105             if (index >= mConnectionPool.length)
1106                 // No free connections were found and the pool contains the maxinum number of
1107                 // allowed connections
1108                 return null;
1109             
1110             if(mConnectionPool[index] != null)
1111             {
1112                 // Found a free connection, mark it as not free and return it
1113                 mFreeConnectionFlags[index] = false;
1114 //                Log.print(this, "Returned connection with index " + index);
1115                 
1116                 if (mConnectionPool[index].isClosed())
1117                     mConnectionPool[index] = this.openNewConnection();
1118 
1119                 return mConnectionPool[index];
1120             }
1121             
1122             // Increase the number of connections in the pool by one and return the new connection
1123             mConnectionPool[index] = this.openNewConnection();
1124                 
1125             Log.print(this, "Extending connection pool for "+getServiceIdentity()+" to " + (index+1) + " connections.");
1126             return mConnectionPool[index];
1127         }
1128         
1129         private Connection openNewConnection() throws SQLException
1130         {
1131             if (sDriver != null)
1132                 return sDriver.connect(mConnectionURL, mConnectionInfo);
1133                 
1134             if (mConnectionInfo.get("user") != null)
1135                 return DriverManager.getConnection(mConnectionURL, mConnectionInfo);
1136             else
1137                 return DriverManager.getConnection(mConnectionURL);
1138         }
1139         
1140         private void init()
1141         {
1142             if (mMaxSize > 0)
1143             {
1144                 mConnectionPool = new Connection[mMaxSize];
1145                 mFreeConnectionFlags = new boolean[mMaxSize];
1146                 mWaitingThreads = new ArrayList();
1147             }
1148             else
1149             {
1150                 mConnectionPool = null;
1151                 mFreeConnectionFlags = null;
1152                 mWaitingThreads = null;
1153             }
1154         }
1155     }
1156 }