I've been working with virtual machines. Which turned out to be a good idea while trying to install Oracle on Ubuntu Linux. I ran into a few problems.

• install the libaio1 package.

2008-Jan-07 Update: A reader mentioned that the package name ends in a one not an el.

Oracle needs this package installed before you install the .deb package. After the package is installed with the dpkg -i command, you are supposed to run the /etc/init.d/oracle-xe configure command. However this command produced no response. Nor did trying to stop and start Oracle. After a bit of playing, I did the followig:

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
$ORACLE_HOME/config/script/XE.sh

I still can’t get Oracle to work but at least that XE script did something.

Configuring SqlUpdate and derived classes within the applicationContext.xml file is not straightforward because the class's constructor requires an integer array when a prepared statement is used. I got around this issue by using a factory class and method.

We'll start with the object that we actually need. The communicationsUpdate bean:

<bean id="communicationsUpdate" factory-bean="communicationsUpdateFactory" factory-method="createInstance"/>

.The Java class for this bean is nearly trivial:

public class CommunicationsUpdate extends SqlUpdate {
  public int run(final String communication_type, final int source_organization_id, final int destination_organization_id, final int address_to_person_id, final int sent_by_person_id, final int order_id, final int dsid) {
    Object[] params = new Object[] { communication_type, new Integer(source_organization_id), new Integer(destination_organization_id), new Integer(address_to_person_id), new Integer(sent_by_person_id), new Integer(order_id), new Integer(dsid) };
    return update(params);
  }
}

The run method's parameter includes every field that needs to be updated. It creates an object array and then passes the object array into the parent's update method (SqlUpdate is the parent class).

Next we turn our attention to the factory class. Here is the bean description:

<bean id="communicationsUpdateFactory" class="com.codebits.dao.CommunicationsUpdateFactory">
  <property name="dataSource" ref="dataSource" />
  <property name="sql" value="UPDATE communications SET communication_type=?,source_organization_id=?,destination_organization_id=?,address_to_person_id=?,sent_by_person_id=?,order_id=? WHERE dsid=?" />
  <property name="parameters">
    <list>
      <ref bean="communicationType_type"/>
      <ref bean="sourceOrganizationId_type"/>
      <ref bean="destinationOrganizationId_type"/>
      <ref bean="addresstoPersonId_type"/>
      <ref bean="sendByPersonId_type"/>
      <ref bean="orderId_type"/>
      <ref bean="dsId_type"/>
    </list>
  </property>
</bean>

You'll notice that the SQL for the update is specified right in the bean definition. As are the parameters for the prepared statement. The Java source behind the factory is quite generic. It looks like this:

public class CommunicationsUpdateFactory {

  private DataSource dataSource = null;

  private String sql = null;

  private List parameters = null;

  public CommunicationsUpdate createInstance() {
    CommunicationsUpdate action = new CommunicationsUpdate();
    action.setDataSource(getDataSource());
    action.setSql(getSql());
    for (Iterator iterator = parameters.iterator(); iterator.hasNext(); ) {
      action.declareParameter((SqlParameter)iterator.next());
    }
    action.compile();
      return action;
  }
  ... standard getters and setters ...
}

The SQLParameters are also defined in the applicationContext.xml file:

<bean id="communicationType_type" class="com.codebits.dao.sqlparameter.CommunicationType"/>
<bean id="sourceOrganizationId_type" class="com.codebits.dao.sqlparameter.SourceOrganizationId"/>
<bean id="destinationOrganizationId_type" class="com.codebits.dao.sqlparameter.DestinationOrganizationId"/>
<bean id="addresstoPersonId_type" class="com.codebits.dao.sqlparameter.AddressToPersonId"/>
<bean id="sendByPersonId_type" class="com.codebits.dao.sqlparameter.SentByPersonId"/>
<bean id="orderId_type" class="com.codebits.dao.sqlparameter.OrderId"/>
<bean id="dsId_type" class="com.codebits.dao.sqlparameter.DsId"/>

They all look the same except that the Types constant may change.

public class CommunicationType extends SqlParameter {

  public CommunicationType() { super(Types.VARCHAR); }

}

I posted this example as much to demonstrate how a factory method can be defined as to show how the SqlUpdate class is used. I'm not sure how popular SqlUdate is in this era of Hibernate!