commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jstrac...@apache.org
Subject cvs commit: jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util JdbcSupport.java
Date Fri, 13 Sep 2002 12:14:17 GMT
jstrachan    2002/09/13 05:14:16

  Modified:    sql/src/java/org/apache/commons/sql/dynabean DynaSql.java
               sql      project.xml
               sql/xdocs index.xml
               sql/src/java/org/apache/commons/sql/util JdbcSupport.java
  Added:       sql/src/test/org/apache/commons/sql/dynabean
                        TestAxionDynaSql.java TestHsqlDbDynaSql.java
                        AbstractTestDynaSql.java
  Removed:     sql/src/test/org/apache/commons/sql/dynabean
                        TestDynaSql.java
  Log:
  Added the ability to query relational database via DynaBeans as well via
  
  	DyanSql.query(String sql);
  	DynaSql.query(String sql, List params);
  
  The implementation uses the ResultSetDynaClass from beanutils.
  
  Also refactored the DynaSql test cases to both test the query code and make it easier to
test individual database providers.
  
  Finally added some more documentation to the site.
  
  Revision  Changes    Path
  1.1                  jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/dynabean/TestAxionDynaSql.java
  
  Index: TestAxionDynaSql.java
  ===================================================================
  /*
   * $Header: /home/cvs/jakarta-commons-sandbox/jelly/src/java/org/apache/commons/jelly/CompilableTag.java,v
1.5 2002/05/17 15:18:12 jstrachan Exp $
   * $Revision: 1.5 $
   * $Date: 2002/05/17 15:18:12 $
   *
   * ====================================================================
   *
   * The Apache Software License, Version 1.1
   *
   * Copyright (c) 1999-2002 The Apache Software Foundation.  All rights
   * reserved.
   *
   * Redistribution and use in source and binary forms, with or without
   * modification, are permitted provided that the following conditions
   * are met:
   *
   * 1. Redistributions of source code must retain the above copyright
   *    notice, this list of conditions and the following disclaimer.
   *
   * 2. Redistributions in binary form must reproduce the above copyright
   *    notice, this list of conditions and the following disclaimer in
   *    the documentation and/or other materials provided with the
   *    distribution.
   *
   * 3. The end-user documentation included with the redistribution, if
   *    any, must include the following acknowlegement:
   *       "This product includes software developed by the
   *        Apache Software Foundation (http://www.apache.org/)."
   *    Alternately, this acknowlegement may appear in the software itself,
   *    if and wherever such third-party acknowlegements normally appear.
   *
   * 4. The names "The Jakarta Project", "Commons", and "Apache Software
   *    Foundation" must not be used to endorse or promote products derived
   *    from this software without prior written permission. For written
   *    permission, please contact apache@apache.org.
   *
   * 5. Products derived from this software may not be called "Apache"
   *    nor may "Apache" appear in their names without prior written
   *    permission of the Apache Group.
   *
   * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
   * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
   * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
   * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
   * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
   * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
   * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
   * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
   * SUCH DAMAGE.
   * ====================================================================
   *
   * This software consists of voluntary contributions made by many
   * individuals on behalf of the Apache Software Foundation.  For more
   * information on the Apache Software Foundation, please see
   * <http://www.apache.org/>.
   * 
   * $Id: CompilableTag.java,v 1.5 2002/05/17 15:18:12 jstrachan Exp $
   */
  package org.apache.commons.sql.dynabean;
  
  import javax.sql.DataSource;
  
  import junit.framework.Test;
  import junit.framework.TestCase;
  import junit.framework.TestSuite;
  import junit.textui.TestRunner;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  
  /**
   * DynaSql test harness for the Axion database
   *
   * @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
   * @version $Revision: 1.3 $
   */
  public class TestAxionDynaSql extends AbstractTestDynaSql
  {
      public static void main( String[] args ) 
      {
          TestRunner.run( suite() );
      }
      
      /**
       * A unit test suite for JUnit
       */
      public static Test suite()
      {
          return new TestSuite(TestAxionDynaSql.class);
      }
  
      /**
       * Constructor for the TestAxionDynaSql object
       *
       * @param testName
       */
      public TestAxionDynaSql(String testName)
      {
          super(testName);
      }
  
      protected String getDatabaseType() 
      {
          return "axion";
      }
      
      protected DataSource createDataSource() throws Exception
      {
          return createDataSource(
              "org.axiondb.jdbc.AxionDriver", 
              "jdbc:axiondb:diskdb:target/axiondb"
          );
      }
  }
  
  
  
  
  1.1                  jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/dynabean/TestHsqlDbDynaSql.java
  
  Index: TestHsqlDbDynaSql.java
  ===================================================================
  /*
   * $Header: /home/cvs/jakarta-commons-sandbox/jelly/src/java/org/apache/commons/jelly/CompilableTag.java,v
1.5 2002/05/17 15:18:12 jstrachan Exp $
   * $Revision: 1.5 $
   * $Date: 2002/05/17 15:18:12 $
   *
   * ====================================================================
   *
   * The Apache Software License, Version 1.1
   *
   * Copyright (c) 1999-2002 The Apache Software Foundation.  All rights
   * reserved.
   *
   * Redistribution and use in source and binary forms, with or without
   * modification, are permitted provided that the following conditions
   * are met:
   *
   * 1. Redistributions of source code must retain the above copyright
   *    notice, this list of conditions and the following disclaimer.
   *
   * 2. Redistributions in binary form must reproduce the above copyright
   *    notice, this list of conditions and the following disclaimer in
   *    the documentation and/or other materials provided with the
   *    distribution.
   *
   * 3. The end-user documentation included with the redistribution, if
   *    any, must include the following acknowlegement:
   *       "This product includes software developed by the
   *        Apache Software Foundation (http://www.apache.org/)."
   *    Alternately, this acknowlegement may appear in the software itself,
   *    if and wherever such third-party acknowlegements normally appear.
   *
   * 4. The names "The Jakarta Project", "Commons", and "Apache Software
   *    Foundation" must not be used to endorse or promote products derived
   *    from this software without prior written permission. For written
   *    permission, please contact apache@apache.org.
   *
   * 5. Products derived from this software may not be called "Apache"
   *    nor may "Apache" appear in their names without prior written
   *    permission of the Apache Group.
   *
   * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
   * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
   * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
   * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
   * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
   * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
   * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
   * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
   * SUCH DAMAGE.
   * ====================================================================
   *
   * This software consists of voluntary contributions made by many
   * individuals on behalf of the Apache Software Foundation.  For more
   * information on the Apache Software Foundation, please see
   * <http://www.apache.org/>.
   * 
   * $Id: CompilableTag.java,v 1.5 2002/05/17 15:18:12 jstrachan Exp $
   */
  package org.apache.commons.sql.dynabean;
  
  import javax.sql.DataSource;
  
  import junit.framework.Test;
  import junit.framework.TestCase;
  import junit.framework.TestSuite;
  import junit.textui.TestRunner;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  
  /**
   * DynaSql test harness for the HsqlDb database
   *
   * @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
   * @version $Revision: 1.3 $
   */
  public class TestHsqlDbDynaSql extends AbstractTestDynaSql
  {
      public static void main( String[] args ) 
      {
          TestRunner.run( suite() );
      }
      
      /**
       * A unit test suite for JUnit
       */
      public static Test suite()
      {
          return new TestSuite(TestHsqlDbDynaSql.class);
      }
  
      /**
       * Constructor for the TestHsqlDbDynaSql object
       *
       * @param testName
       */
      public TestHsqlDbDynaSql(String testName)
      {
          super(testName);
      }
  
      protected String getDatabaseType() 
      {
          return "hsqldb";
      }
      
      protected DataSource createDataSource() throws Exception
      {
          return createDataSource(
              "org.hsqldb.jdbcDriver", 
              "jdbc:hsqldb:target/hsqldb", 
              "sa", 
              ""
          );
      }
  }
  
  
  
  
  1.1                  jakarta-commons-sandbox/sql/src/test/org/apache/commons/sql/dynabean/AbstractTestDynaSql.java
  
  Index: AbstractTestDynaSql.java
  ===================================================================
  /*
   * $Header: /home/cvs/jakarta-commons-sandbox/jelly/src/java/org/apache/commons/jelly/CompilableTag.java,v
1.5 2002/05/17 15:18:12 jstrachan Exp $
   * $Revision: 1.5 $
   * $Date: 2002/05/17 15:18:12 $
   *
   * ====================================================================
   *
   * The Apache Software License, Version 1.1
   *
   * Copyright (c) 1999-2002 The Apache Software Foundation.  All rights
   * reserved.
   *
   * Redistribution and use in source and binary forms, with or without
   * modification, are permitted provided that the following conditions
   * are met:
   *
   * 1. Redistributions of source code must retain the above copyright
   *    notice, this list of conditions and the following disclaimer.
   *
   * 2. Redistributions in binary form must reproduce the above copyright
   *    notice, this list of conditions and the following disclaimer in
   *    the documentation and/or other materials provided with the
   *    distribution.
   *
   * 3. The end-user documentation included with the redistribution, if
   *    any, must include the following acknowlegement:
   *       "This product includes software developed by the
   *        Apache Software Foundation (http://www.apache.org/)."
   *    Alternately, this acknowlegement may appear in the software itself,
   *    if and wherever such third-party acknowlegements normally appear.
   *
   * 4. The names "The Jakarta Project", "Commons", and "Apache Software
   *    Foundation" must not be used to endorse or promote products derived
   *    from this software without prior written permission. For written
   *    permission, please contact apache@apache.org.
   *
   * 5. Products derived from this software may not be called "Apache"
   *    nor may "Apache" appear in their names without prior written
   *    permission of the Apache Group.
   *
   * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
   * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
   * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
   * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
   * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
   * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
   * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
   * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
   * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
   * SUCH DAMAGE.
   * ====================================================================
   *
   * This software consists of voluntary contributions made by many
   * individuals on behalf of the Apache Software Foundation.  For more
   * information on the Apache Software Foundation, please see
   * <http://www.apache.org/>.
   * 
   * $Id: CompilableTag.java,v 1.5 2002/05/17 15:18:12 jstrachan Exp $
   */
  package org.apache.commons.sql.dynabean;
  
  import java.io.InputStream;
  import java.io.IOException;
  import java.io.FileInputStream;
  import java.io.FileWriter;
  import java.util.ArrayList;
  import java.util.List;
  
  import javax.sql.DataSource;
  
  import junit.framework.Test;
  import junit.framework.TestCase;
  import junit.framework.TestSuite;
  import junit.textui.TestRunner;
  
  import org.apache.commons.beanutils.DynaBean;
  import org.apache.commons.beanutils.DynaClass;
  import org.apache.commons.beanutils.ResultSetIterator;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  
  import org.apache.commons.sql.builder.*;
  import org.apache.commons.sql.dynabean.DynaSql;
  import org.apache.commons.sql.io.DatabaseReader;
  import org.apache.commons.sql.model.*;
  import org.apache.commons.sql.util.DataSourceWrapper;
  import org.apache.commons.sql.util.DDLExecutor;
  
  /**
   * Abstract base class for testing the DynaSql against a number of 
   * different databases
   *
   * @author <a href="mailto:jstrachan@apache.org">James Strachan</a>
   * @version $Revision: 1.3 $
   */
  public abstract class AbstractTestDynaSql extends TestCase
  {
      /** The Log to which logging calls will be made. */
      private static final Log log = LogFactory.getLog( AbstractTestDynaSql.class );
      
      private String baseDir;
      
      /** the database model */
      private Database database;
      
      /** the database to connect to */
      private DataSource dataSource;
      
      /** for building the DDL to create the database */
      private SqlBuilder sqlBuilder;
  
      /** the simple API to the database */
      protected DynaSql dynaSql;
      
   
      public static void main( String[] args ) 
      {
          TestRunner.run( suite() );
      }
      
      /**
       * A unit test suite for JUnit
       */
      public static Test suite()
      {
          return new TestSuite(AbstractTestDynaSql.class);
      }
  
      /**
       * Constructor for the AbstractTestDynaSql object
       *
       * @param testName
       */
      public AbstractTestDynaSql(String testName)
      {
          super(testName);
      }
  
      // Test cases
      //-------------------------------------------------------------------------        
       
  
      /**
       * Insert some data
       */    
      public void testInsert() throws Exception 
      {
          // first lets check that the tables are available in our database
          
          assertTrue( "Database contains a table 'author'", database.findTable("author") !=
null );
          assertTrue( "Database contains a table 'book'", database.findTable("book") != null
);
          
          DynaBean author = dynaSql.newInstance("author");
  
          assertTrue("Found an author", author != null);
          
          author.set("author_id", new Integer(1));
          author.set("name", "Oscar Wilde");
          dynaSql.insert(author);        
  
          log.info( "Inserted author: " + author );
  
          author = dynaSql.newInstance("author");
          author.set("author_id", new Integer(2));
          author.set("name", "Ian Rankin");
          dynaSql.insert(author);
          
          log.info( "Inserted author: " + author );
          
          DynaBean book = dynaSql.newInstance("book");
          
          assertTrue("Found an book", book != null);
          
          book.set("author_id", new Integer(1));
          book.set("isbn", "ISBN-ABCDEF");
          book.set("title", "The Importance of being Earnest");
          dynaSql.insert(book);
          log.info( "Inserted book: " + book );
          
          book = dynaSql.newInstance("book");
          book.set("author_id", new Integer(2));
          book.set("isbn", "ISBN-XYZ");
          book.set("title", "The Hanging Garden");
          dynaSql.insert(book);
          
          log.info( "Inserted book: " + book );
          
          
          // now lets do some queries        
          doQuery();
          doQueryWithParameters();
      }
  
  
  
      // Implementation methods
      //-------------------------------------------------------------------------        
       
  
  
      /**
       * Test out some basic query operations
       */
      protected void doQuery() throws Exception {
          ResultSetIterator iter = dynaSql.query( "select * from book" );
          assertTrue("Found at least one row", iter.hasNext());
  
          DynaBean bean = (DynaBean) iter.next();
         
          assertTrue("Found a dynaBean row", bean != null);
          
          log.info( "Found book: " + bean.get("title") );
          
          assertEquals( "iter has corrrect isbn", "ISBN-ABCDEF", iter.get("isbn") );
          assertEquals( "iter has corrrect title", "The Importance of being Earnest", iter.get("title")
);
          
          assertEquals( "bean has corrrect isbn", "ISBN-ABCDEF", bean.get("isbn") );
          assertEquals( "bean has corrrect title", "The Importance of being Earnest", bean.get("title")
);
      }
  
      /**
       * Test out some queries with parameters
       */
      protected void doQueryWithParameters() throws Exception {
          List params = new ArrayList();
          params.add("The Hanging Garden");
          
          ResultSetIterator iter = dynaSql.query( "select * from book where title = ?", params
);
          assertTrue("Found at least one row", iter.hasNext());
  
          DynaBean bean = (DynaBean) iter.next();
  
          assertTrue("Found a dynaBean row", bean != null);
          
          log.info( "Found book: " + bean.get("title") );
                 
          assertEquals( "iter has corrrect isbn", "ISBN-XYZ", iter.get("isbn") );
          assertEquals( "iter has corrrect title", "The Hanging Garden", iter.get("title")
);
          
          assertEquals( "bean has corrrect isbn", "ISBN-XYZ", bean.get("isbn") );
          assertEquals( "bean has corrrect title", "The Hanging Garden", bean.get("title")
);
      }
  
      /**
       * The JUnit setup method
       */
      protected void setUp() throws Exception
      {
          super.setUp();
          
          baseDir = System.getProperty("basedir", ".");
          String uri = baseDir + "/src/test-input/datamodel.xml";
          
          DatabaseReader reader = new DatabaseReader ();
          database = (Database) reader.parse(new FileInputStream(uri));
          
          assertTrue("Loaded a valid database", database != null);
          
          dataSource = createDataSource();
          sqlBuilder = createSqlBuilder();
          
          executeDDL();
          
          dynaSql = new DynaSql(dataSource, database);
      }
  
      /**
       * @return the name of the database type to use to create the DDL
       */
      protected abstract String getDatabaseType();    
      
      /**
       * Factory method to create a DataSource
       */
      protected abstract DataSource createDataSource() throws Exception;
  
      /**
       * Creates an SqlBuilder based on the name of the database
       */
      protected SqlBuilder createSqlBuilder() throws Exception
      {
          return SqlBuilderFactory.newSqlBuilder(getDatabaseType());
      }
  
      /**
       * Creates the database on the given data source with the given SQL builder
       */    
      protected void executeDDL() throws Exception 
      {
          DDLExecutor executor = new DDLExecutor(dataSource, sqlBuilder);
          executor.createDatabase(database, true);
      }
      
      /**
       * Creates a new DataSource for the given JDBC URI
       */    
      protected DataSource createDataSource(String className, String connectURL) throws Exception

      {
          return createDataSource(className, connectURL, null, null);
      }
      
      /**
       * Creates a new DataSource for the given JDBC URI
       */    
      protected DataSource createDataSource(String className, String connectURL, String userName,
String password) throws Exception 
      {
          DataSourceWrapper wrapper = new DataSourceWrapper();
          wrapper.setDriverClassName(className);
          wrapper.setJdbcURL(connectURL);
          wrapper.setUserName(userName);
          wrapper.setPassword(password);
          return wrapper;
      }
  }
  
  
  
  
  1.4       +97 -1     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/dynabean/DynaSql.java
  
  Index: DynaSql.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/dynabean/DynaSql.java,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- DynaSql.java	12 Sep 2002 17:56:49 -0000	1.3
  +++ DynaSql.java	13 Sep 2002 12:14:16 -0000	1.4
  @@ -61,6 +61,8 @@
    */
   package org.apache.commons.sql.dynabean;
   
  +import java.lang.reflect.InvocationTargetException;
  +
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
  @@ -75,8 +77,11 @@
   
   import javax.sql.DataSource;
   
  +import org.apache.commons.beanutils.BeanUtils;
   import org.apache.commons.beanutils.DynaBean;
   import org.apache.commons.beanutils.DynaClass;
  +import org.apache.commons.beanutils.ResultSetDynaClass;
  +import org.apache.commons.beanutils.ResultSetIterator;
   
   import org.apache.commons.logging.Log;
   import org.apache.commons.logging.LogFactory;
  @@ -126,6 +131,84 @@
       }
       
       /**
  +     * <p>
  +     * Creates a new DynaBean instance of the given table name and copies the values from
the 
  +     * given source object. The source object can be a bean, a Map or a DynaBean.
  +     * </p>
  +     * <p>
  +     * This method is useful when iterating through an arbitrary DynaBean
  +     * result set after performing a query, then creating a copy as a DynaBean 
  +     * which is bound to a specific table. 
  +     * This new DynaBean cna be kept around, changed and stored back into the database.
  +     * </p>
  +     * 
  +     * @param tableName is the name of the database that the new DynaBean will be bound
  +     * @param source is either a bean, a Map or a DynaBean that will be used to populate
  +     *      returned DynaBean.
  +     * @return a DynaBean bound to the given table name and containing all the properties
from 
  +     *  the given source object
  +     */
  +    public DynaBean copy(String tableName, Object source) throws IllegalAccessException,
InstantiationException, InvocationTargetException {
  +        DynaBean answer = newInstance(tableName);
  +
  +        // copy all the properties from the source        
  +        BeanUtils.copyProperties(answer, source);
  +        
  +        return answer;
  +    }
  +
  +    /**
  +     * Performs the given SQL query returning an iterator over the results.
  +     */
  +    public ResultSetIterator query(String sql) throws SQLException, IllegalAccessException,
InstantiationException {
  +        ResultSetIterator answer = null;
  +        Connection connection = borrowConnection();
  +        Statement statement = null;
  +        ResultSet resultSet = null;
  +        try {
  +            statement = connection.createStatement();
  +            resultSet = statement.executeQuery(sql);
  +            answer = createResultSetIterator(connection, statement, resultSet);
  +            return answer;
  +        }
  +        finally {
  +            // if any exceptions are thrown, close things down
  +            if (answer == null) {
  +                closeResources(connection, statement, resultSet);
  +            }
  +        }
  +    }    
  +    
  +    /**
  +     * Performs the given parameterized SQL query returning an iterator over the results.
  +     * 
  +     * @return an Iterator which appears like a DynaBean for easy access to the properties.
  +     */
  +    public ResultSetIterator query(String sql, List parameters) throws SQLException, IllegalAccessException,
InstantiationException {
  +        ResultSetIterator answer = null;
  +        Connection connection = borrowConnection();
  +        PreparedStatement statement = null;
  +        ResultSet resultSet = null;
  +        try {
  +            statement = connection.prepareStatement(sql);
  +            int paramIdx = 1;
  +            for (Iterator iter = parameters.iterator(); iter.hasNext(); ) {
  +                Object param = iter.next();
  +                statement.setObject(paramIdx++, param);
  +            }
  +            resultSet = statement.executeQuery();
  +            answer = createResultSetIterator(connection, statement, resultSet);
  +            return answer;
  +        }
  +        finally {
  +            // if any exceptions are thrown, close things down
  +            if (answer == null) {
  +                closeResources(connection, statement, resultSet);
  +            }
  +        }
  +    }    
  +    
  +    /**
        * @return the SqlDynaClass for the given table name. If the SqlDynaClass does not
exist
        * then create a new one based on the Table definition
        */
  @@ -135,7 +218,7 @@
               Table table = getDatabase().findTable(tableName);
               if (table != null) {
                   answer = createSqlDynaClass(table);
  -                dynaClassCache.put(tableName, dynaClassCache);
  +                dynaClassCache.put(tableName, answer);
               }
               else {
                   log.warn( "No such table: " + tableName );
  @@ -459,5 +542,18 @@
           
           Object value = dynaBean.get(property.getName());    
           statement.setObject(sqlIndex, value);
  +    }
  +    
  +    /**
  +     * Factory method to create a new ResultSetIterator for the given result set, closing
the 
  +     * connection, statement and result set when the iterator is used or closed.
  +     */
  +    protected ResultSetIterator createResultSetIterator(
  +        Connection connection, Statement statement, ResultSet resultSet
  +    ) throws SQLException, IllegalAccessException, InstantiationException {
  +        
  +        // #### WARNING - the Connection, statement and resultSet are not closed.
  +        ResultSetDynaClass resultSetClass = new ResultSetDynaClass(resultSet);
  +        return (ResultSetIterator) resultSetClass.iterator();
       }
   }
  
  
  
  1.6       +10 -10    jakarta-commons-sandbox/sql/project.xml
  
  Index: project.xml
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/project.xml,v
  retrieving revision 1.5
  retrieving revision 1.6
  diff -u -r1.5 -r1.6
  --- project.xml	12 Sep 2002 17:56:49 -0000	1.5
  +++ project.xml	13 Sep 2002 12:14:16 -0000	1.6
  @@ -79,26 +79,26 @@
     <dependencies>
   
       <dependency>
  -      <id>commons-betwixt</id>
  -      <version>SNAPSHOT</version>
  +      <id>commons-logging</id>
  +      <version>1.0.1</version>
       </dependency>
   
       <dependency>
  -      <id>jdbc</id>
  -      <version>2.0</version>
  +      <id>commons-beanutils</id>
  +      <version>SNAPSHOT</version>
       </dependency>
   
  -    <!-- indirect -->
  -
       <dependency>
  -      <id>commons-logging</id>
  -      <version>1.0</version>
  +      <id>commons-betwixt</id>
  +      <version>SNAPSHOT</version>
       </dependency>
   
       <dependency>
  -      <id>commons-beanutils</id>
  -      <version>1.4-dev</version>
  +      <id>jdbc</id>
  +      <version>2.0</version>
       </dependency>
  +
  +    <!-- indirect -->
   
       <dependency>
         <id>commons-collections</id>
  
  
  
  1.6       +69 -2     jakarta-commons-sandbox/sql/xdocs/index.xml
  
  Index: index.xml
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/xdocs/index.xml,v
  retrieving revision 1.5
  retrieving revision 1.6
  diff -u -r1.5 -r1.6
  --- index.xml	13 Sep 2002 09:44:52 -0000	1.5
  +++ index.xml	13 Sep 2002 12:14:16 -0000	1.6
  @@ -63,14 +63,81 @@
       xmlFile="src/conf/myschema.xml" 
       targetDatabase="oracle"
       output="target/myschema-oracle.sql"/&gt;
  -</pre>
  -      
  +</pre>      
  +
         <p>
           It is hoped that Commons SQL can be used to create a 
           <a href="http://jakarta.apache.org/turbine/maven/">Maven</a> 
           plugin for projects wishing to create beans or OJB files from some logical relational
schema.
         </p>
  +    </section>
  +
   
  +    <section name="Using DynaBeans to access and change data in a database">
  +      <p>
  +		There's a simple API for querying and inserting, updating and deleting data via
  +		<a href="">DynaBeans</a>
  +		It essentially binds the commons-sql model beans
  +		(Database, Table, Column) to DyanClass, DynaBean and DynaProperty instances.
  +      </p>
  +	  <p>
  +	    So you can do things like
  +	  </p>					
  +
  +<pre>
  +  // lets parser the model from XML
  +  DatabaseReader reader = new DatabaseReader();
  +  Database model = (Database) reader.parse( "mymodel.xml" );
  +		
  +  // JDBC connection pool, maybe using DBCP and Pool from commons
  +  DataSource source = ...;
  +		
  +  // now lets add some data
  +  DynaSql dynaSql = new DynaSql(source, model);
  +		
  +  DynaBean author = dynaSql.newInstance( "author" );
  +  author.set( "name", "James" );
  +  author.set( "whatever", new Integer(1234));
  +  dynaSql.insert(author);
  +</pre>
  +		
  +	<p>
  +		Or perform arbitrary queries against the database like this
  +	</p>					
  +
  +<pre>
  +  // perform a query with no arguments		
  +  Iterator iter = dynaSql.query( "select * from book" );
  +  while (iter.hasNext()) {
  +    DynaBean book = (DynaBean) iter.next();
  +    String title = book.get("title");			
  +    ...
  +  }
  +		
  +  // perform a query with arguments
  +  List params = new ArrayList();
  +  params.add("Some title");
  +	
  +  Iterator iter = dynaSql.query( "select * from book where title = ?", params );
  +  while (iter.hasNext()) {
  +    DynaBean book = (DynaBean) iter.next();
  +    String title = book.get("title");			
  +    ...
  +  }
  +</pre>
  +		
  +		
  +		<p>
  +			This can be handy if you want something really simple and easy or need to
  +			create dynamic tables or handle arbitrary database schemas at runtime. 
  +			It can also be useful for writing simple bulk loading programs or working generically
  +			with SQL data.
  +		</p>
  +		<p>
  +			Though if your persistent schema is more well defined, tools like OJB or Torque might
be more applicable.
  +		</p>
  +	
  +			
   <!--
         <subsection name="Torque's generation tasks">
           <p>
  
  
  
  1.2       +11 -1     jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/JdbcSupport.java
  
  Index: JdbcSupport.java
  ===================================================================
  RCS file: /home/cvs/jakarta-commons-sandbox/sql/src/java/org/apache/commons/sql/util/JdbcSupport.java,v
  retrieving revision 1.1
  retrieving revision 1.2
  diff -u -r1.1 -r1.2
  --- JdbcSupport.java	12 Sep 2002 17:56:50 -0000	1.1
  +++ JdbcSupport.java	13 Sep 2002 12:14:16 -0000	1.2
  @@ -126,7 +126,7 @@
       /**
        * Returns a JDBC connection back into the pool
        */
  -    protected void returnConnection(Connection connection) throws SQLException {
  +    protected void returnConnection(Connection connection) {
           try {
               connection.close();
           }
  @@ -161,5 +161,15 @@
                   log.warn("Ignoring exception closing statement: " + e, e);
               }
           }
  +    }
  +    
  +    /**
  +     * A helper method to close down any resources used and return the JDBC connection
  +     * back to the pool
  +     */
  +    protected void closeResources(Connection connection, Statement statement, ResultSet
resultSet) {
  +        closeResultSet(resultSet);
  +        closeStatement(statement);
  +        returnConnection(connection);
       }
   }
  
  
  

--
To unsubscribe, e-mail:   <mailto:commons-dev-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:commons-dev-help@jakarta.apache.org>


Mime
View raw message