We often have to map one-to-many relationships in our database projects. A customer can have many addresses, an order can contain many line items and so on. We are now providing a ResultSetExtractor implementation to deal with this common task.

Let’s look at the schema definition first:

CREATE TABLE customer(
  id BIGINT IDENTITY PRIMARY KEY, 
  name VARCHAR(255));
CREATE TABLE address (
  id BIGINT IDENTITY PRIMARY KEY, 
  customer_id BIGINT CONSTRAINT address_customer_ref 
    FOREIGN KEY REFERENCES customer (id), 
  street VARCHAR(255), 
  city VARCHAR(255));

Two tables linked by a foreign key constraint. To map this we need two domain classes - Customer and Address where Customer has a Set of Addresses.

public class Customer {

  private Integer id;

  private String name;

  private Set<Address> addresses = new HashSet<Address>();

  public Set<Address> getAddresses() {
    return addresses;
  }

  public void addAddress(Address address) {
    this.addresses.add(address);
  }

// other setters and getters

}

public class Address {

  private Integer id;

  private String street;

  private String city;

// setters and getters

}

Executing the following query we would potentially get multiple rows returned for each customer.

List<Customer> result = template.query(
  "select customer.id, customer.name, address.id, " +
    "address.customer_id, address.street, address.city " +
    "from customer " +
    "left join address on customer.id = address.customer_id " +
    "order by customer.id",
  resultSetExtractor);

To be able to handle the multiple rows we create a new CustomerAddressExtractor that extends the abstract class OneToManyResultSetExtractor. We parameterize the OneToManyResultSetExtractor with the root class (Customer), the child class (Address), and the class for the primary and foreign key (Integer).

public class CustomerAddressExtractor extends
    OneToManyResultSetExtractor<Customer, Address, Integer> {

  public CustomerAddressExtractor() {
    super(new CustomerMapper(), new AddressMapper());
  }

  @Override
  protected Integer mapPrimaryKey(ResultSet rs) throws SQLException {
    return rs.getInt("customer.id");
  }

  @Override
  protected Integer mapForeignKey(ResultSet rs) throws SQLException {
    if (rs.getObject("address.customer_id") == null) {
      return null;
    }
    else {
      return rs.getInt("address.customer_id");
    }
  }

  @Override
  protected void addChild(Customer root, Address child) {
    root.addAddress(child);
  }
}

We need a way to match the primary key of the Customer with the foreign key of the Address so we provide mappings for these via the abstract methods mapPrimaryKey and mapForeignKey. We have to take into account that there might not be an address record for every customer so the foreign key could be null. We also need to add the mapped Address instances to the Customer instance. We do this by implementing the abstract method addChild. We simply call the addAddress on the Customer class here.

Looking at the constructor of the CustomerAddressExtractor we see that we call the super constructor providing RowMapper implementations for the Customer and the Address classes. These are standard RowMappers that we in this example provide as static inner classes.

private static class CustomerMapper implements RowMapper<Customer> {

  public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
    Customer c = new Customer();
    c.setId(rs.getInt("customer.id"));
    c.setName(rs.getString("customer.name"));
    return c;
  }
}

private static class AddressMapper implements RowMapper<Address> {

  public Address mapRow(ResultSet rs, int rowNum) throws SQLException {
    Address a = new Address();
    a.setId(rs.getInt("address.id"));
    a.setStreet(rs.getString("address.street"));
    a.setCity(rs.getString("address.city"));
    return a;
  }
}

We now have a complete solution for this common problem.

Before you can use the Spring support for Querydsl you need to configure your application to use the Querydsl SQL support. See the instruction in the Mysema blog post on how this is done. Once you have generated your Querydsl query types then you can start using the Spring support as outlined bellow.

The central class in the Querydsl support is the QueryDslJdbcTemplate. Just like the NamedParameterJdbcTemplate it wraps a regular JdbcTemplate that you can get access to by calling the getJdbcOperations method. One thing to note is that when you use the QueryDslJdbcTemplate, there is no need to specify the SQL dialect to be used since the template will auto-detect this when it is created.

You can create a QueryDslJdbcTemplate by passing in a JdbcTemplate or a DataSource in the constructor. Here is some example code showing this:

private QueryDslJdbcTemplate template;

@Autowired
public void setDataSource(DataSource dataSource) {
    this.template = new QueryDslJdbcTemplate(dataSource);
}

At this point the template is ready to be used and we give examples for various uses below.

For queries you need to have a reference to the query type. For the examples in this document we define the query type as follows:

private final QProduct qProduct = QProduct.product;

Now we are ready to create the first query. Instead of directly creating an instance of SQLQueryImpl we ask the template for a managed instance.

SQLQuery sqlQuery = template.newSqlQuery()

The managed part here refers to the managing of the connection and the SQL dialect. The QueryDslJdbcTemplate will provide both of these. The dialect is set when the SQLQuery is created and the connection is provided when the SQLQuery is executed using the clone feature of the SQLQuery implementation class.

We continue to build this query providing from and where clauses:

SQLQuery sqlQuery = template.newSqlQuery().from(qProduct)
        .where(qProduct.category.eq(categoryId));

Here categoryId is a parameter that is passed in to the method.

The final step is to execute the query. Depending on how you want to map the results, there are two flavors of the query methods. You can

or

Here is an example using the query created above together with a MappingProjection for mapping the query results:

public List<Product> getProductListByCategory(final String categoryId)
        throws DataAccessException {

    SQLQuery sqlQuery = template.newSqlQuery().from(qProduct)
            .where(qProduct.category.eq(categoryId));

    return template.query(sqlQuery, new MappingProductProjection(qProduct);
}

private static class MappingProductProjection extends MappingProjection<Product> {

    public MappingProductProjection(QProduct qProduct) {
        super(Product.class, qProduct.productid,
            qProduct.name, qProduct.descn, qProduct.category));
    }

    @Override
    protected Product map(Tuple tuple) {
        Product product = new Product();

        product.setProductId(tuple.get(qProduct.productid));
        product.setName(tuple.get(qProduct.name));
        product.setDescription(tuple.get(qProduct.descn));
        product.setCategoryId(tuple.get(qProduct.category));

        return product;
    }
}

For inserts we need to call the template's insert method and implement an SqlInsertCallback to handle the mapping of data from the domain object values to the insert. Here is an example:

public void insertProduct(final Product product) throws DataAccessException {
    template.insert(qProduct, new SqlInsertCallback() {
        public long doInSqlInsertClause(SQLInsertClause sqlInsertClause) {
            return sqlInsertClause.columns(qProduct.productid, qProduct.name, 
                        qProduct.descn, qProduct.category)
                    .values(product.getProductId(), product.getName(), 
                        product.getDescription(), product.getCategoryId())
                    .execute();
        }
    });
}

Updates are similar to the inerts but we of course call the update method and implement an SqlUpdateCallback to provide the where clause and handle the mapping of update parameter values.

public void updateProduct(final Product product) throws DataAccessException {
    template.update(qProduct, new SqlUpdateCallback() {

        public long doInSqlUpdateClause(SQLUpdateClause sqlUpdateClause) {
            return sqlUpdateClause.where(qProduct.productid.eq(product.getProductId()))
                    .set(qProduct.name, product.getName())
                    .set(qProduct.descn, product.getDescription())
                    .set(qProduct.category, product.getCategoryId())
                    .execute();
        }
    });
}

Deletes are also very similar except we don't need to do any value mapping. We simply call the delete method and implement an SqlDeleteCallback with a where clause.

public void deleteProduct(final Product product) {
    template.delete(qProduct, new SqlDeleteCallback() {

        public long doInSqlDeleteClause(SQLDeleteClause sqlDeleteClause) {
            return sqlDeleteClause.where(qProduct.productid.eq(product.getProductId()))
                    .execute();
        }
    });
}

We'll start by looking at a very basic DataSource configuration using the traditional <bean> element.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
	   xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"> 
        <property name="URL" value="${jdbc.url}" /> 
        <property name="user" value="${jdbc.username}"/> 
        <property name="password" value="${jdbc.password}"/> 
        <property name="connectionCachingEnabled" value="true"/> 
    </bean>

    <context:property-placeholder location="classpath:jdbc.properties"/> 

</beans>

The new "orcl" namespace contains a pooling-data-source element used for easy configuration of the OracleDataSource. We will show several ways this element can be used and we will start with a basic one that can replace the traditional <bean> element configuration used above.

When using the pooling-data-source element connection caching is enabled by default and must explicitly be turned off using the connection-caching-enabled attribute if you don't want to use this pooling support.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl" 
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl 
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource"
        url="${jdbc.url}" username="${jdbc.username}" password="${jdbc.password}"/> 

    <context:property-placeholder location="classpath:jdbc.properties"/> 

</beans>

We used a property-placeholder in the previous example to provide connection properties. We can also read the properties directly from a properties file without using placeholders. This is done by using a properties-location attribute specifying the location of the properties file.

	Note
	When you specify properties using a property file there are two basic properties, url and username, where you can use the Oracle property name or the name traditionally used by Spring developers. For url we also accept URL and for username we also accept user.

We will use the following property file named orcl.properties and we will place it at the root of the classpath.

url=jdbc:oracle:thin:@//maui:1521/xe
username=spring
password=spring

Once we have this file in place we can reference it from our pooling-data-source entry and omit the property plceholder declarations for any properties provided in this file.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties"/> 

</beans>

You can even remove the properties-location attribute as long as you use the default location and name which is a file named orcl.properties at the root of the classpath.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource"/> 

</beans>

It's sometimes necessary to provide additional connection properties to control how the database access is configured. There are several ways you can provide these properties and they are outlined below.

url=jdbc:oracle:thin:@//maui:1521/xe
username=spring
password=spring
processEscapes=false

conn.url=jdbc:oracle:thin:@//maui:1521/xe
conn.username=spring
conn.password=spring
conn.processEscapes=false

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn" 
        properties-location="classpath:orcl.properties"/>

</beans>

conn.url=jdbc:oracle:thin:@//maui:1521/xe
conn.username=spring
conn.password=spring
conn.processEscapes=false
cache.InitialLimit=10

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn"
        connection-cache-properties-prefix="cache" 
        properties-location="classpath:orcl.properties"/>

</beans>

3.4.3 Using "connection-properties" element for additional connection properties

The connection properties can be specified using the connection-properties element.

	Note
	If you specify a connection-properties element then any connection properties specified in a property file other than the basic url, username and password will not be used.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:connection-properties>
            processEscapes=false 
        </orcl:connection-properties>
    </orcl:pooling-datasource>

</beans>

The connection properties are specified here.

3.4.4 Using "connection-cache-properties" element for additional cache properties

The connection cache properties can be specified using the connection-cache-properties element.

	Note
	If you specify a connection-cache-properties element then any connection cache properties specified in a property file will not be used.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:connection-properties>
            processEscapes=false
        </orcl:connection-properties>
        <orcl:connection-cache-properties>
            InitialLimit=10 
        </orcl:connection-cache-properties>
    </orcl:pooling-datasource>

</beans>

The connection cache properties are specified here.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:username-connection-proxy connection-context-provider="usernameProvider"/> 
    </orcl:pooling-datasource>

    <bean id="usernameProvider" class="org.springframework.data.jdbc.test.CurrentUsernameProvider"/>

</beans>

The pooling-data-source element has the following attributes:

The pooling-data-source element has the following child elements:

We are going to need a DataSource that is capable of participating in a "Fast Connection Failover" scenario. The only one we have available is the oracle.jdbc.pool.OracleDataSource implementation that we will configure using the "orcl" namespace. This DataSource configured with some additional properties used for RAC.

We will be using the following property file to specify the username and password for the following example.

username=spring
password=spring

The url used in this example is a two node RAC configuration using the thin driver. It is probably too long to fit on the screen or on the page so if you would like to see the entire url it's listed in the callout notes.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="racDataSource"
        url="jdbc:oracle:thin:@(description=(address_list=
            (address=(host=rac1)(protocol=tcp)(port=1521))
            (address=(host=rac2)(protocol=tcp)(port=1521)))
            (connect_data=(service_name=racdb1)))"
        properties-location="classpath:orcl.properties"
        fast-connection-failover-enabled="true" 
        ONS-configuration="rac1:6200,rac2:6200"/> 

    <bean id="transactionManager" 
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="racDataSource"/>
    </bean>

</beans>

In order for the Fast Connection Failover to be transparent to the end user you need to consider the overall impact of this failover. The original transaction will fail and another transaction will be started to retry the same operation. You need to consider any non-transactional side effects that the failed transaction might have caused. You also need to consider work done while the transaction is suspended. This could happen if a method with a transactional attribute of "REQUIRES_NEW" is executed within the original transaction.

Once you have considered any possible side effects, you can proceed to configure a RacFailoverInterceptor together with the AOP advisor and pointcut. The failover advisor must be before or at the same pointcut where the transaction advisor is applied. If the pointcuts for the failover advisor and the transaction advisor are at the same pointcut then the failover advisor must have a higher priority than the transaction advisor that it should wrap.

For the AOP advisor configuration we use the "aop" namespace and for the RacFailoverInterceptor we use the rac-failover-interceptor tag from the "orcl" namespace.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <aop:config>
        <aop:advisor pointcut="execution(* *..PetStoreFacade.insertOrder(..))"  
            advice-ref="racFailoverInterceptor" order="1"/>
        <aop:advisor pointcut="execution(* *..PetStoreFacade.*(..))"  
            advice-ref="txAdvice"/>
    </aop:config>

    <orcl:rac-failover-interceptor id="racFailoverInterceptor"/> 

    <tx:advice id="txAdvice">
        <tx:attributes>
            <tx:method name="insert*"/>
            <tx:method name="update*"/>
            <tx:method name="*" read-only="true"/>
        </tx:attributes>
    </tx:advice>

</beans>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/aop
       http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <aop:config>
        <aop:advisor 
            pointcut="@annotation(org.springframework.transaction.annotation.Transactional)" 
            advice-ref="racFailoverInterceptor" order="1"/>
    </aop:config>

    <orcl:rac-failover-interceptor id="racFailoverInterceptor"/> 

    <tx:annotation-driven/> 

</beans>

There is a number of optional attributes you can use to configure the rac-failover-interceptor.

* org.springframework.batch.retry.backoff.BackOffPolicy

JMS and Oracle Streams AQ can support a variety of payloads. These payloads are stored in the database and need to be converted to a Java representation in order for our programs to manipulate them. The following table outlines what payloads are supported and the corresponding classes that will work with these payloads and that will be able to convert them to and from a Java representation.

When you use the JmsTemplate together with the Oracle AQ JMS support you can use the aq-jms-connection-factory entry to provide a connection factory to the JmsTemplate.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl" 
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd"> 

 
    <orcl:aq-jms-connection-factory id="connectionFactory" 
            data-source="dataSource"/> 

    <bean id="jmsTemplate" class="org.springframework.jms.core.JmsTemplate">
        <property name="sessionTransacted" value="true"/>
        <property name="connectionFactory" ref="connectionFactory"/>
    </bean>

    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="dataSource" ... />

</beans>

The configuration for a Message-Driven POJO with a MessageListenerContainer is very similar. You use the same type of connection factory configuration. This is passed in to the listener container configuration. Here is an example using the JMS namespace support.

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl" 
       xmlns:jms="http://www.springframework.org/schema/jms"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/tx
       http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl 
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd
       http://www.springframework.org/schema/jms
       http://www.springframework.org/schema/jms/spring-jms-3.0.xsd">

    <context:annotation-config/>
    
    <tx:annotation-driven/>
    
    <bean id="messageDelegate" class="spring.test.MessageDelegate"/>
    
    <jms:listener-container connection-factory="connectionFactory" 
            transaction-manager="transactionManager">
        <jms:listener destination="jmsadmin.jms_text_queue" 
                ref="messageDelegate" method="handleMessage"/>
    </jms:listener-container> 
    
    <orcl:aq-jms-connection-factory id="connectionFactory" 
            data-source="dataSource"/> 
    
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="dataSource" ... />

</beans>

See the next section for how to configure the transaction support and use a the same local transaction as the JDBC or ORM data access.

The configurations in the previous section will take advantage of the transaction synchronization provided by Spring, but there will be two transactions. One transaction for the data access and one for the JMS messaging. They will be synchronized, so if the data access transaction commits then the messaging transaction will also commit while if the data access transaction roll back then the messaging transaction will also roll back.

There is always a chance that the commit for the messaging transaction could fail after the data access transaction has committed successfully. This is of course a problem that you would have to account for in your code by checking for duplicate delivery of a message.

A better solution is to configure both data access and the messaging to share a transaction. Most often this is done using JTA, and that works, but has some impact on performance. For JTA you need to use distributed transactions and XA capable resources designed for two-phase commits. This comes at an extra cost that we try to avoid if possible.

Another option is to have the data access and the messaging share a local data access transaction. This is possible since the Oracle AQ implementation consists of a set of tables and stored procedures running in the database accessed through a standard JDBC connection. If you use the same database for data access and messaging with AQ, then you can configure the connection factory to share the database connection and the local transaction. You configure this connection and transaction sharing by setting the attribute use-local-data-source-transaction to true.

    <orcl:aq-jms-connection-factory id="connectionFactory"
        use-local-data-source-transaction="true" 
        data-source="dataSource"/>

Configuring the connection factory to share a local data source transaction with the data access code has some implications for JMS connection and session caching. You can still configure a MessageListenerContainer to cache the JMS connection since each JMS session will be created as it's needed inside a data source transaction. However, if you cache the JMS session, then the database connection for it is established when the container starts up and it will not be possible to have this cached JMS session participite in the local data source transaction.

In many application server environments the JDBC connection is wrapped in an implementation specific class that delegates to the underlying native JDBC connection. Oracle's AQ connection factory needs the native Oracle connection and will throw an "oracle.jms.AQjmsException: JMS-112: Connection is invalid" exception if the connection is wrapped by a foreign class. To solve this problem you can specify a NativeJdbcExtractor that can be used to unwrap the connection. Spring provides a number of implementations to match the application server environment. Here is an example for specifying a NativeJdbcExtractor.

    <orcl:aq-jms-connection-factory id="connectionFactory"
        use-local-data-source-transaction="true"
        native-jdbc-extractor="dbcpNativeJdbcExtractor" 
        data-source="dataSource" />

    <bean id="dbcpNativeJdbcExtractor" 
        class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>

    <bean id="dbcpDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
            destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

For some use cases the default plain ConnectionFactory does not work and you need to explicitly use a QueueConnectionFactory or a TopicConnectionFactory. To support this requirement it is possible to specify this using the connection-factory-type attribute. The default is CONNECTION but you can specify QUEUE_CONNECTION or TOPIC_CONNECTION instead. Here is an example for specifying the connection factory type.

    <orcl:aq-jms-connection-factory id="connectionFactory"
        use-local-data-source-transaction="true"
        connection-factory-type="QUEUE_CONNECTION" 
        data-source="dataSource" />

    <bean id="dbcpDataSource" class="org.apache.commons.dbcp.BasicDataSource"
            destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

When you use a SYS.XMLType as payload there a few additional configuration settings are needed.

<bean id="jmsTemplate" class="org.springframework.jms.core.JmsTemplate">
    <property name="connectionFactory" ref="connectionFactory"/> 
    <property name="messageConverter">
        <bean id="messageConverter"
           class="org.springframework.data.jdbc.jms.support.converter.oracle.XmlMessageConverter">
            <constructor-arg>
                <bean class="org.springframework.data.jdbc.support.oracle.StringXmlTypeHandler"/> 
            </constructor-arg>
        </bean>
    </property>
</bean>

        String xmlval = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                "<product id=\"10\">\n" +
                " <description>Foo</description>\n" +
                " <price>2.05</price>\n" +
                "</product>";

        jmsTemplate.convertAndSend("jmsadmin.jms_xml_queue", xmlval);

<bean id="messageDelegate" class="org.springframework.data.jdbc.test.xml.MessageDelegate" />

<jms:listener-container connection-factory="connectionFactory" 
    transaction-manager="transactionManager"
    message-converter="messageConverter" 
    container-class="org.springframework.data.jdbc.jms.listener.oracle.XmlMessageListenerContainer"> 
    <jms:listener destination="jmsadmin.jms_xml_queue" 
        ref="messageDelegate" method="handleMessage">
    </jms:listener>
</jms:listener-container>

<bean id="messageConverter" 
   class="org.springframework.data.jdbc.jms.support.converter.oracle.XmlMessageConverter">
    <constructor-arg>
        <bean class="org.springframework.data.jdbc.support.oracle.DocumentXmlTypeHandler"/> 
    </constructor-arg>
</bean>

public class MessageDelegate {

    @Autowired
    private DomainService domainService;

    public void handleMessage(Document xmlDoc) 
            throws MessageConversionException, JMSException {
        domainService.processXmlMessage(xmlDoc);
    }

}

When you use a custom ADT as payload there are certain configuration settings that are needed. When creating the queue and its queue table you specify the custom type as the "queue_payload_type". This custom type is defined using a regular "CREATE TYPE" statement. In the code example that follow we have defined a PRODUCT type:

create or replace TYPE PRODUCT_TYPE AS OBJECT
(
  id INTEGER,
  description VARCHAR(50),
  price DECIMAL(12,2)
);

public interface DatumMapper {

    public Datum toDatum(Object object, Connection conn) throws SQLException;

    public Object fromDatum(Datum datum) throws SQLException;

}

<bean id="jmsTemplate" class="org.springframework.jms.core.JmsTemplate">
  <property name="connectionFactory" ref="connectionFactory"/> 
  <property name="messageConverter">
    <bean id="messageConverter"
       class="org.springframework.data.jdbc.jms.support.converter.oracle.MappingAdtMessageConverter">
       <constructor-arg>
         <bean class="org.springframework.data.jdbc.jms.support.oracle.StructDatumMapper"> 
           <constructor-arg index="0" value="JMSADMIN.PRODUCT_TYPE"/>
           <constructor-arg index="1" value="org.springframework.data.jdbc.test.domain.Product"/>
         </bean>
       </constructor-arg>
    </bean>
  </property>
</bean>

        Product product = new Product();
        product.setId(22L);
        product.setDescription("Foo");
        product.setPrice(new BigDecimal("42.95"));

        jms.convertAndSend("jmsadmin.jms_product_queue", product);

<bean id="messageDelegate" class="org.springframework.data.jdbc.test.adt.MessageDelegate" />

<jms:listener-container connection-factory="connectionFactory" 
    transaction-manager="transactionManager"
    message-converter="messageConverter" 
    container-class="org.springframework.data.jdbc.jms.listener.oracle.AdtMessageListenerContainer"> 
  <jms:listener destination="jmsadmin.jms_product_queue" 
      ref="messageDelegate" method="handleMessage">
  </jms:listener>
</jms:listener-container>

<bean id="messageConverter" 
   class="org.springframework.data.jdbc.jms.support.converter.oracle.MappingAdtMessageConverter">
  <constructor-arg>
    <bean class="org.springframework.data.jdbc.jms.support.oracle.StructDatumMapper"> 
      <constructor-arg index="0" value="JMSADMIN.PRODUCT_TYPE"/>
      <constructor-arg index="1" value="org.springframework.data.jdbc.test.domain.Product"/>
    </bean>
  </constructor-arg>
</bean>

public class MessageDelegate {

    @Autowired
    private DomainService domainService;

    public void handleMessage(Product product) 
            throws MessageConversionException, JMSException {
        domainService.saveProduct(product);
    }

}

To use the Oracle XML support you need to use a couple of jar files available in the Oracle XML Developers Kit download available from Oracle. You need xdb.jar and also the xmlparserv2.jar since the XMLType depends on this parser library.

There is optional support for Spring's Object/XML Mapping (OXM) support. If you use this support then you would also need a dependency for the Spring Framework OXM sub-project. The jar files needed is spring-oxm.jar.

All samples in this chapter access a table named "xml_table". Here is the DDL to create this table:

CREATE TABLE xml_table (
    id NUMBER(10),
    xml_text XMLTYPE,
    PRIMARY KEY (id));

To write XML data to a table you need to pass in the XML using a custom SqlTypeValue. In this implementation you would be responsible for setting the parameter value for the XML column in accordance with the API provided by the database driver.

For Oracle we provide a database specific implementation of an SqlXmlValue, which is an extension of the SqlTypeValue, that is easier to use. It works together with an SqlXmlHandler and adds an abstraction layer on top of the database specific APIs provided by the database vendors. There is a new SQLXML datatype in JDBC 4.0 that provides an abstraction, but so far it is not widely implemented.

In this example we have an XML value that we pass in as the second parameter. This XML value can be in the form of a String or an org.w3c.dom.Document. We use an SqlXmlHandler instance to gain access to a new instance of the SqlXmlValue. For the Oracle support the implementation classes are OracleXmlHandler and OracleXmlTypeValue respectively.

    simpleJdbcTemplate.update(
            "INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
            id,
            sqlXmlHandler.newSqlXmlValue(xml));

The implementation of the SqlXmlHandler is chosen in the data access configuration file and should be injected into the DAO or Repository class.

<bean id="sqlXmlHandler" 
    class="org.springframework.data.jdbc.support.oracle.OracleXmlHandler"/>

Oracle's XMLType supports passing in an java.io.InputStream but since this is not supported by the JDBC 4.0 SQLXML datatype you will have to use the Oracle specific OracleXmlTypeValue directly.

    simpleJdbcTemplate.update(
            "INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
            id,
            new OracleXmlTypeValue(is));

Running a query against a table with an XMLTYPE column requires a RowMapper that can handle retrieval of the XMLType and the corrsponding XML data. The OracleXmlHandler provides several methods that supports easy access to the XML data. It is typically used in a RowMapper.

    String s = simpleJdbcTemplate.queryForObject(
            "SELECT xml_text FROM xml_table WHERE id = ?",
            new ParameterizedRowMapper<String>() {
                public String mapRow(ResultSet rs, int i) throws SQLException {
                    String s = sqlXmlHandler.getXmlAsString(rs, 1);
                    return s;
                }
            },
            id);

The XML data can be retreived as a String, a java.io.InputStream, a java.io.Reader or a javax.xml.transform.Source.

To map an object to XML and write this XML to a table you first need to use marshalling support available from the Spring Web Services project. Once the object data is marshalled to XML we can write the XML to a column in a database table. The latter part is very similar to the the XML support discussed above. We need to pass in the XML using a custom SqlTypeValue. In the object mapping implementation you would be responsible for marshalling the object to XML before setting the parameter value.

In this example we have an object that needs to be marshalled to XML. We are using a Marshaller provided by the Spring Web Services project. The marshaller is typically configured and then injected into the DAO or Repository. Here is an example configuration using the JAXB 2.0 support. In addition to JAXB 2.0, there is also support for JAXB 1.0, Castor, XML Beans, JiBX and XStream.

    <bean id="marshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
        <property name="classesToBeBound">
            <list>
                <value>org.springframework.data.jdbc.samples.Item</value>
            </list>
        </property>
    </bean>

The JAXB 2.0 class that we are marshalling is a typical javaBean and it uses annotations for the meta data so there is no additional configuration needed.

package org.springframework.data.jdbc.samples;

import javax.xml.bind.annotation.*;
import java.math.BigDecimal;

@XmlRootElement(name = "item")
@XmlType(propOrder = {"name", "price"})
public class Item{
    private Long id = 0L;
    private String name;
    private BigDecimal price;

    @XmlAttribute(name="id")
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @XmlElement(name = "item-name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @XmlElement(name = "price")
    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }


    public String toString() {
        return "[" + id + "] " + name + " " + price;
    }
}

For Oracle we provide a database specific implementation of an SqlXmlMarshallingValue, which is an extension of the SqlXmlValue, that is easier to use. It works together with an SqlXmlObjectMappingHandler similar to the SqlXmlHandler that we used in the previous example. The object to be marshalled is passed in when the new instance of the SqlXmlValue is created.

For our dabase insert we pass in the marshalled value as the second parameter. The first parameter is the id of the object, and this will be use as the primary key for the row. We use an SqlXmlHandler instance to gain access to a new instance of the SqlXmlMappingValue. For the Oracle support the implementation classes are OracleXmlObjectMappingHandler and OracleXmlMarshallingValue respectively.

    simpleJdbcTemplate.update(
            "INSERT INTO xml_table (id, xml_text) VALUES (?, ?)",
            item.getId(),
            sqlXmlObjectMappingHandler
                    .newMarshallingSqlXmlValue(item));

The implementation of the SqlXmlObjectMappingHandler is chosen in the data access configuration file and should be injected into the DAO or Repository class.

<bean id="sqlXmlHandler" 
    class="org.springframework.data.jdbc.support.oracle.OracleXmlObjectMappingHandler">
    <property name="marshaller" ref="marshaller"/>
</bean>

Last piece we need is reading the XML from the database and have it unmarshalled to an Item object. We will perform this work in a RowMapper together with the SqlXmlObjectMappingHandler.

    Item i = simpleJdbcTemplate.queryForObject(
            "SELECT xml_text FROM xml_table WHERE id = ?",
            new ParameterizedRowMapper<Item>() {
                public Item mapRow(ResultSet rs, int i) throws SQLException {
                    return (Item) sqlXmlObjectMappingHandler
                            .getXmlAsObject(rs, 1);
                }
            },
            id);

The XML data is unsmarshalled using an Unmarshaller which in the JAXB 2.0 case is also implemented by the Jaxb2Marshaller class. It must be injected into the unmarshaller property of the SqlXmlObjectMappingHandler. Since marshalling and unmarshalling is performed by the same object we pass in the bean named marshaller for the unmarshaller property.

<bean id="sqlXmlHandler" 
    class="org.springframework.data.jdbc.support.oracle.OracleXmlObjectMappingHandler">
    <property name="unmarshaller" ref="marshaller"/>
</bean>

When your stored procedures has parameters that are declared using custom object types that aren't part of the standard JDBC types they are managed using JDBC Struct objects. When working with Oracle it's easier to work with Oracle's extension to Struct which is oracle.sql.STRUCT.

For the STRUCT examples we will use the following type declaration.

CREATE OR REPLACE TYPE actor_type
    AS OBJECT (id NUMBER(10), name VARCHAR2(50), age NUMBER);

The data contained in a STRUCT parameter can be accessed in two ways. Either using the SQLData interface which is part of the JDBC specification, or by using Oracle specific calls accessing the attributes directly. We will cover both methods.

Now we will look at the sample procedures used for this example. First one is the procedure we use to add the actor data.

CREATE OR REPLACE PROCEDURE add_actor (in_actor IN actor_type)
AS
BEGIN
  INSERT into actor (id, name, age) VALUES(in_actor.id, in_actor.name, in_actor.age);
END;

This procedure has one IN parameter (in_actor) of object type actor_type.

Next we show the procedure used to retrieve the actor data.

CREATE OR REPLACE PROCEDURE get_actor (in_actor_id IN NUMBER, out_actor OUT actor_type)
AS
BEGIN
  SELECT actor_type(id, name, age) INTO out_actor FROM actor WHERE id = in_actor_id;
END;

This procedure has two parameters, one IN parameter (in_actor_id) that is the id of the actor to retrieve and one OUT parameter (out_actor) of type actor_type to pass back the data retrieved.

The last piece we will cover here is the Java class that represents the type we are accessing. Here is the Actor implementation used in this example. It has the Java equivalent of the variables we defined for the type in the database. We also have setters and getters for all fields.

package org.springframework.data.jdbc.samples;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.math.BigDecimal;

public class Actor {

    private Long id;
    private String name;
    private int age;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String toString() {
    return "Actor: [" + id + "] " + name + " " + age;
    }

}

package org.springframework.data.jdbc.samples;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.math.BigDecimal;

public class SqlActor extends Actor implements SQLData {

    public String getSQLTypeName() throws SQLException {
        return "ACTOR_TYPE"; 
    }

    public void readSQL(SQLInput sqlInput, String string) throws SQLException { 
        setId(Long.valueOf(sqlInput.readLong()));
        setName(sqlInput.readString());
        setAge(sqlInput.readInt());
    }

    public void writeSQL(SQLOutput sqlOutput) throws SQLException { 
        sqlOutput.writeLong(getId().longValue());
        sqlOutput.writeString(getName());
        sqlOutput.writeInt(getAge());
    }

}

    this.addSqlActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("add_actor")
                .declareParameters(
                    new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE")); 

    public void addSqlActor(final SqlActor actor) {
        Map in = Collections.singletonMap("in_actor", actor); 
        addSqlActorCall.execute(in);
    }

    private class AddSqlActorProc extends StoredProcedure {

        public AddSqlActorProc(DataSource dataSource) {
            super(dataSource, "add_actor");
            declareParameter(new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE"));
        }

        public void execute(Actor actor) {
            Map in = Collections.singletonMap("in_actor", actor);
            this.execute(in);
        }

    }

        this.getSqlActorCall =
                new SimpleJdbcCall(dataSource).withProcedureName("get_actor")
                    .declareParameters(
                        new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE", 
                            new SqlReturnSqlData(SqlActor.class)) 
                    );

    public SqlActor getSqlActor(int id) {
        Map in = Collections.singletonMap("in_actor_id", id);
        return getSqlActorCall.executeObject(SqlActor.class, in); 
    }

    private class GetSqlActorProc extends StoredProcedure {

        public GetSqlActorProc(DataSource dataSource) {
            super(dataSource, "get_actor");
            declareParameter(new SqlParameter("in_actor_id", Types.NUMERIC));
            declareParameter(
                new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE",
                    new SqlReturnSqlData(SqlActor.class))
            );
        }

        public SqlActor execute(Long id) {
            Map in = Collections.singletonMap("in_actor_id", id);
            Map out = this.execute(in);
            return (SqlActor) out.get("out_actor");
        }

    }

    this.addActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("add_actor")
                .declareParameters(
                    new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE")); 

    public void addActor(final Actor actor) {
        Map in = Collections.singletonMap("in_actor", new SqlStructValue(actor)); 
        addActorCall.execute(in); 
    }

    private class AddActorProc extends StoredProcedure {

        public AddActorProc(DataSource dataSource) {
            super(dataSource, "add_actor");
            declareParameter(new SqlParameter("in_actor", OracleTypes.STRUCT, "ACTOR_TYPE"));
        }

        public void execute(Actor actor) {
            Map in = Collections.singletonMap("in_actor", new SqlStructValue(actor));
            this.execute(in);
        }

    }

    this.getActorCall =
            new SimpleJdbcCall(dataSource).withProcedureName("get_actor")
                .declareParameters(
                    new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE", 
                            new SqlReturnStruct(Actor.class)) 
            );

    public Actor getActor(int id) {
        Map in = Collections.singletonMap("in_actor_id", id);
        return getActorCall.executeObject(Actor.class, in); 
    }

    private class GetActorProc extends StoredProcedure {

        public GetActorProc(DataSource dataSource) {
            super(dataSource, "get_actor");
            declareParameter(new SqlParameter("in_actor_id", Types.NUMERIC));
            declareParameter(
                new SqlOutParameter("out_actor", OracleTypes.STRUCT, "ACTOR_TYPE",
                        new SqlReturnStruct(Actor.class))
            );
        }

        public Actor execute(Long id) {
            Map in = Collections.singletonMap("in_actor_id", id);
            Map out = this.execute(in);
            return (Actor) out.get("out_actor");
        }

    }

Sometimes your stored procedures has parameters that are declared as arrays of some type. These arrays are managed using JDBC Array objects. When working with Oracle it's sometimes easier to work with Oracle's extension to Array which is oracle.sql.ARRAY.

For the ARRAY examples we will use the following type declarations.

CREATE OR REPLACE TYPE actor_name_array 
    AS VARRAY(20) OF VARCHAR2(50);
CREATE OR REPLACE TYPE actor_id_array 
    AS VARRAY(20) OF NUMBER;

We will show how to access parameters using these declarations in two JDBC calls. The first one is a procedure call that deletes actor entries based on ids provided in an actor_id_array. The second example calls a function to retrieve an array of the names for all actors in the table.

CREATE OR REPLACE PROCEDURE delete_actors (in_actor_ids IN actor_id_array)
AS
BEGIN
  FOR i IN 1..in_actor_ids.count loop
    DELETE FROM actor WHERE id = in_actor_ids(i);
  END LOOP;
END;

    this.deleteActorsCall =
            new SimpleJdbcCall(dataSource).withProcedureName("delete_actors")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(
                    new SqlParameter("in_actor_ids", OracleTypes.ARRAY, "ACTOR_ID_ARRAY")); 

    public void deleteActors(final Long[] ids) {
        Map in = Collections.singletonMap("in_actor_ids", new SqlArrayValue(ids)); 
        deleteActorsCall.execute(in);
    }

    private class DeleteActorsProc extends StoredProcedure {

        public DeleteActorsProc(DataSource dataSource) {
            super(dataSource, "delete_actors");
            declareParameter(
                new SqlParameter("in_actor_ids", OracleTypes.ARRAY, "ACTOR_ID_ARRAY"));
        }

        public void execute(Long[] ids) {
            Map in = Collections.singletonMap("in_actor_ids", new SqlArrayValue(ids));
            Map out = this.execute(in);
        }

    }

    this.getActorNamesCall =
            new SimpleJdbcCall(dataSource).withFunctionName("get_actor_names")
                .withoutProcedureColumnMetaDataAccess()
                .declareParameters(
                     new SqlOutParameter("return", Types.ARRAY, "ACTOR_NAME_ARRAY", 
                         new SqlReturnArray())); 

    public String[] getActorNames() {
        Map in = Collections.emptyMap();
        return getActorNamesCall.executeFunction(String[].class, in); 
    }

    private class GetActorNamesProc extends StoredProcedure {

        public GetActorNamesProc(DataSource dataSource) {
            super(dataSource, "get_actor_names");
            setFunction(true);
            declareParameter(new SqlOutParameter("return", Types.ARRAY, "ACTOR_NAME_ARRAY",
                    new SqlReturnArray()));
        }

        public String[] execute() {
            Map in = Collections.emptyMap();
            Map out = this.execute(in);
            return (String[]) out.get("return");
        }

    }

The Spring Framework already contains implementations that simplify the handling of REF CURSORS but we include an example here just to complete the coverage of the handling of Oracle specific advanced data types. The procedure we are calling is declared as follows:

CREATE OR REPLACE PROCEDURE read_actors (out_actors_cur OUT sys_refcursor)
AS
BEGIN
  OPEN out_actors_cur FOR 'select * from actor';
END;

    this.readActorsCall =
            new SimpleJdbcCall(dataSource).withProcedureName("read_actors")
                .returningResultSet("out_actors_cur", 
                        ParameterizedBeanPropertyRowMapper.newInstance(Actor.class)); 

    public List<Actor> getActors() {
        return readActorsCall.executeObject(List.class, Collections.emptyMap()); 
    }

        new SqlOutParameter("out_actors_cur", OracleTypes.CURSOR, 
                ParameterizedBeanPropertyRowMapper.newInstance(Actor.class)) 

    private class ReadActorsProc extends StoredProcedure {

        public ReadActorsProc(DataSource dataSource) {
            super(dataSource, "read_actors");
            declareParameter(
                new SqlOutParameter("out_actors_cur", OracleTypes.CURSOR,
                        ParameterizedBeanPropertyRowMapper.newInstance(Actor.class))
            );

        }

        public List execute() {
            Map in = Collections.emptyMap();
            Map out = this.execute(in);
            return (List) out.get("out_actors_cur");
        }

    }

The Oracle JDBC implementation provides access to Oracle's Proxy Authentication feature. The Proxy Authentication lets you configure a connection pool using a proxy user account with very limited rights. Then, during the connection process, you would specify the actual user name for the end user. This user name must be configured to allow a proxy connection through the user proxy ("grant connect through proxyuser").

This is valuable for web applications where you typically set up a data source with a shared database user. If this shared user is a proxy user account and you supply the actual end user name then the proxy authentication feature will make any database access this user performs to be performed with the end users actual database user account.

To use this feature you must provide an implementation of the ConnectionUsernameProvider interface. This interface has a single method named getUserName that should return the user name for the current end user to be connected via the proxy user. It's up to the application developer to provide the appropriate implementation. One type of implementation would be to retrieve the current principal or user name from the SecurityContextHolder provided when you use Spring Security.

An example of what this implementation could look like is:

public class CurrentUsernameProvider implements ConnectionUsernameProvider {

    public String getUserName() {
        Object principal = 
            SecurityContextHolder.getContext().getAuthentication().getPrincipal(); 
        if (principal instanceof UserDetails) { 
            return ((UserDetails)principal).getUsername(); 
        } else { 
            return principal.toString(); 
        }
    }

}

See the Spring Security reference manual for more detail regarding the use of the SecurityContextHolder.

Connection proxy authentication is configured using the username-connection-proxy element. You also need to provide a reference to the user name provider that implements the ConnectionUsernameProvider interface mentioned above.

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:orcl="http://www.springframework.org/schema/data/orcl"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/data/orcl
       http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd">

    <orcl:pooling-datasource id="dataSource" 
        properties-location="classpath:orcl.properties">
        <orcl:username-connection-proxy connection-context-provider="usernameProvider"/> 
    </orcl:pooling-datasource>

    <bean id="usernameProvider" 
      class="org.springframework.data.jdbc.test.CurrentUsernameProvider"/>

</beans>

To set up the database proxy user and to grant the user accounts to participate in the proxy authentication you could use this SQL:

-- create the new proxy user account
create user proxyuser identified by proxypasswd;
grant create session to proxyuser;
-- grant existing user to connect  through the proxy
alter user spring grant connect through proxyuser;

In your connection properties file (orcl.properties) you would need to provide the proxy user credentials:

url=jdbc:oracle:thin:@//localhost:1521/xe
username=proxyuser
password=proxypasswd

	Note
	We are currently only supporting proxy authentication using user name with no password authentication for the user connecting through the proxy. Support for other types of proxy connections will be provided in future releases.

There are times when you want to prepare the database connection in certain ways that aren't easily supported using standard connection properties. One example would be to set certain session properties in the SYS_CONTEXT like MODULE or CLIENT_IDENTIFIER. This chapter explains how to use a ConnectionPreparer to accomplish this. The example will set the CLIENT_IDENTIFIER.

We will need to add a ConnectionInterceptor using AOP and then configure the ConnectionInterceptor with a ConnectionPreparer implementation that performs the necessary preparations. Lets first look at our custom ClientIdentifierConnectionPreparer that implements the ConnectionPreparer interface. There is only a single method named prepare that needs to be implemented. The prepared connection is the return value which gives you an opportunity to wrap the connection with a proxy class if needed.

package org.springframework.data.jdbc.samples;

import org.springframework.data.jdbc.support.ConnectionPreparer;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

public class ClientIdentifierConnectionPreparer implements ConnectionPreparer {

    String prepSql = "{ call DBMS_SESSION.SET_IDENTIFIER('SPRING') }"; 

    public Connection prepare(Connection conn) throws SQLException {
        CallableStatement cs = conn.prepareCall(prepSql); 
        cs.execute();
        cs.close();
        return conn;
    }

}

This example sets the CLIENT_IDENTIFIER to a fixed value, but you could implement a ConnectionPreparer that would use the current users login id. That way you can capture user login information even if your data source is configured with a shared user name.

The following application context entries show how this could be configured for your data source.

    <orcl:pooling-datasource id="dataSource" 
        connection-properties-prefix="conn"
        properties-location="classpath:orcl.properties"/>

    <aop:config> 
        <aop:advisor 
            pointcut="execution(java.sql.Connection javax.sql.DataSource.getConnection(..))" 
            advice-ref="testInterceptor"/>
    </aop:config>

    <bean id="testInterceptor" 
          class="org.springframework.data.jdbc.aop.ConnectionInterceptor">
        <property name="connectionPreparer" ref="connectionPreparer"/> 
    </bean>

    <bean id="connectionPreparer" 
        class="org.springframework.data.jdbc.samples.ClientIdentifierConnectionPreparer"/> 

Every time a new connection is obtained the connection preparer will set the CLIENT_IDENTIFIER. During database processing the value it was set to can be accessed using a call to a standard Oracle function - "sys_context('USERENV', 'CLIENT_IDENTIFIER')"