The Carbon SQL Module

Purpose

The SQL Service is designed to simplify the use of JDBC in a Java application. A major design goal was simplifying the functional interface by leveraging a component-oriented approach. In addition, the service also simplifies the management of SQL code and seperates the role of the DBA and the Java developer, via a declarative query-specification model.

The SQL Service contains no database or driver-specific code, so it should be usable on any platform, though some features are only availble when JDBC 2.0-compliant drivers are present.

The SQL Service provides factory-style methods for creating java.sql.PreparedStatement and java.sql.CallableStatement objects. The service's functional interface, or StatementFactory, defines two such methods: createPreparedStatement and createCallableStatement.

A default implementation, DefaultStatementFactoryImpl is provided, along with a corresponding configuration interface, StatementFactoryConfiguration .

Introduction

The SQL Service provides a simple API to retrieve PreparedStatement and CallableStatements. In order to use this service, create an instance of the StatementFactory component and invoke the create methods, as appropriate.

SQL Service components can be accessed in two ways. They may be accessed directly via the Component Lookup Service, or implicitely via declarative component references. In general, using component references provides a cleaner means of utilizing this service in situations where JDBC data-access is associated with a functional module, which itself might be modeled as a Carbon component. However, in situations where this is not possible or desireable, the Component Lookup Service provides a feasible alternative.

Component Configuration

Each project team that uses the SQL service should have one or more SQL Service Configurations containing one or more SQL statements.

<Configuration ConfigurationInterface="org.sape.carbon.services.sql.StatementFactoryConfiguration">
  <FunctionalInterface>org.sape.carbon.services.sql.StatementFactory</FunctionalInterface>
  <FunctionalImplementationClass>org.sape.carbon.services.sql.DefaultStatementFactoryImpl</FunctionalImplementationClass>
  <!-- Default values for statement configuration -->
  <ConnectionFactory>ref:///sql/connection/test/StandaloneConnectionFactory</ConnectionFactory>
  <MaxRows>5</MaxRows>
  <StatementArray>
    <Statement>
        <QueryName>selectAccounts</QueryName>
        <Query>SELECT * FROM accounts</Query>
        <!-- New parameters -->
        <ResultSetType>TYPE_SCROLL_SENSITIVE</ResultSetType>
        <ResultSetConcurrency>CONCUR_UPDATABLE</ResultSetConcurrency>
    </Statement>

    <Statement>
        <QueryName>getUser</QueryName>
        <Query>SELECT * FROM users WHERE id = ? </Query>
            <!-- Override the default values -->
        <MaxRows>10</MaxRows>
    </Statement>
  <StatementArray>
</Configuration>

Configuration parameters for StatementFactoryConfiguration

Property	Data Type / Value	Description
Configuration Interface	org.sape.carbon.services.sql. StatementFactoryConfiguration	Defines the configuration Interface. Only if we need to use an alternate implementation for this service, this value would be something else.
Functional Interface	org.sape.carbon.services.sql. StatementFactory	Functional Interface defines the programming contract, In this case, we have the createPreparedStatement and createCallableStatement.
Functional Implementation	org.sape.carbon.services.sql. DefaultStatementFactoryImpl	Implementation for this service
ConnectionFactory	Default connection factory, to be used if the statements fail to mention a connection factory .	ex. ref:///sql/connection/test/StandaloneConnectionFactory
ResultSetType ResultSetConcurrency MaxRows FetchSize MaxFieldSize QueryTimeOut	Optional parameters	Default values for Statement Configuration Properties. These can be individually set at a statement level, refer to the StatementConfiguration Information for details on each parameter.
Statement	StatementConfiguration Block	Statement block defines one SQL statement. In a component, we will normally see more than one such statement block.

Configuration parameters for StatementConfiguration

Property	Data Type / Value	Description
Configuration Interface	org.sape.carbon.services.sql. StatementConfiguration	Defines the configuration Interface for a statement.
ConnectionFactory	Optional ConnectionFactory Component	If not mentioned, connection factory of the FactoryConfiguration is used.
ResultSetType ResultSetConcurrency MaxRows FetchSize MaxFieldSize QueryTimeOut	Optional parameters	If not mentioned, these values are set by the values called out in the FactoryConfiguration. These values are properties of the java.sql.PreparedStatement and java.sql.CallableStatement.

Example Using Component References

The configuration for the component must contain a reference to the Sql Factory configuration.

<!-- Component configuration for some business component -->
<Configuration ConfigurationInterface="com.somecompany.somebizcomponent.BizConfiguration">
<FunctionalInterface>com.somecompany.somebizcomponent.BizInterface</FunctionalInterface>
<FunctionalImplementationClass>com.somecompany.somebizcomponent.BizImpl</FunctionalImplementationClass>

 <!-- Reference to the statement factory -->
 <StatementFactory>ref:///sql/MyStatementFactory</StatementFactory>
 </Configuration>

 

 // Component code using the sql component

 // Query Name
 public static final String SELECT_ACCOUNTS="selectAccounts";
 
 // config is the component configuration and StatementFactory
 // is one of configuration elements.
 // ex. <StatementFactory>ref:///sql/MyStatementFactory</StatementFactory>
 
 StatementFactory statementFactory = config.getStatementFactory();
 
 // Create prepared statement
 java.sql.PreparedStatement preparedStatement = statementFactory.createPreparedStatement(SELECT_ACCOUNTS);
 

Example Using Lookup Service

A reference to a SQL Service component may be accessed via the Component Lookup Service as follows:

 // Query Name
 public static final String SELECT_ACCOUNTS="selectAccounts";
 // Lookup.getInstance().fetchComponent returns the component
 StatementFactory statementFactory = (StatementFactory) Lookup.getInstance().fetchComponent("/sql/MySqlStatementFactory");

 // Create prepared statement
 java.sql.PreparedStatement preparedStatement = statementFactory.createPreparedStatement(SELECT_ACCOUNTS);

Closing Connection

An important point to note is that the SQL service does not manage the connections, and the developer must remember to close the connection, after you are done with the statement. Obtain the connection from the statement and explicitly close it. Example statement.getConnection().close()