Pluggable java.sql.PreparedStatement parameter setters and java.util.ResultSet readers

Since 3.2.6RC1

JBossCMP is built on top of JDBC API and expects database drivers to be JDBC compliant. Unfortunately, in some cases some drivers by known or unknown reasons behave not as it is expected from the JDBC spec point of view. One of the areas is setting java.sql.PreparedStatement parameters and reading java.sql.ResultSet. If you've got such a driver, you might need to adapt the parts of JBossCMP that set java.sql.PreparedStatement parameters and read java.sql.ResultSet to your driver. These parts are pluggable and you can provide your own implementations and it is very easy.

java.sql.PreparedStatement parameter setters

Parameter setters implement the following interface

/*
 * JBoss, the OpenSource J2EE webOS
 *
 * Distributable under LGPL license.
 * See terms of license at gnu.org.
 */
package org.jboss.ejb.plugins.cmp.jdbc;

import org.jboss.logging.Logger;

import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Implementations of this interface are used to set java.sql.PreparedStatement parameters.
 * 
 * @author <a href="mailto:alex@jboss.org">Alexey Loubyansky</a>
 * @version <tt>$Revision: 1.1.2.1 $</tt>
 */
public interface JDBCParameterSetter
{
   /**
    * Sets a parameter of a specific JDBC type.
    * @param ps  the java.sql.PreparedStatement to set parameter on
    * @param index  the index of the parameter
    * @param jdbcType  the JDBC type of the parameter as defined by java.sql.Types
    * @param value  parameter value
    * @param log  the logger
    * @throws SQLException
    */ 
   void set(PreparedStatement ps, int index, int jdbcType, Object value, Logger log) throws SQLException;
}

java.sql.ResultSet readers

Result readers implement the following interface

/*
 * JBoss, the OpenSource J2EE webOS
 *
 * Distributable under LGPL license.
 * See terms of license at gnu.org.
 */
package org.jboss.ejb.plugins.cmp.jdbc;

import org.jboss.logging.Logger;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Implementations of this interface are used to read java.sql.ResultSet.
 *
 * @author <a href="mailto:alex@jboss.org">Alexey Loubyansky</a>
 * @version <tt>$Revision: 1.1.2.1 $</tt>
 */
public interface JDBCResultSetReader
{
   /**
    * Reads one column from the java.sql.ResultSet.
    * @param rs  the java.sql.ResultSet to read from
    * @param index  the index of the column
    * @param destination  the expected Java class of result
    * @param log  the logger
    * @return  column value
    * @throws SQLException
    */
   Object get(ResultSet rs, int index, Class destination, Logger log) throws SQLException;

Example

For an example here is a simple implementation for java.lang.Long.

/*
 * JBoss, the OpenSource J2EE webOS
 *
 * Distributable under LGPL license.
 * See terms of license at gnu.org.
 */
package org.jboss.test.cmp2.advanced.ejb;

import org.jboss.ejb.plugins.cmp.jdbc.JDBCParameterSetter;
import org.jboss.ejb.plugins.cmp.jdbc.JDBCResultSetReader;
import org.jboss.logging.Logger;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * @author <a href="mailto:alex@jboss.org">Alexey Loubyansky</a>
 * @version <tt>$Revision: $</tt>
 */
public class JDBCLongSetterAndReader
   implements JDBCParameterSetter, JDBCResultSetReader
{
   public void set(PreparedStatement ps, int index, int jdbcType, Object value, Logger log) throws SQLException
   {
      log.debug("param: i=" + index + ", value=" + value);
      if(value == null)
      {
         ps.setNull(index, jdbcType);
      }
      else
      {
         ps.setLong(index, ((Long)value).longValue());
      }
   }

   public Object get(ResultSet rs, int index, Class destination, Logger log) throws SQLException
   {
      long value = rs.getLong(index);
      Object result;
      if(rs.wasNull())
      {
         result = null;
      }
      else
      {
         result = new Long(value);
      }
      log.debug("result: i=" + index + ", value=" + value);
      return result;
   }
}

To plug it in we add param-setter and result-reader elements to the mapping element in standardjbosscmp-jdbc.xml or jbosscmp-jdbc.xml like

<type-mapping>
   <name>DatabaseName</name>
...
   <mapping>
      <java-type>java.lang.Long</java-type>
      <jdbc-type>BIGINT</jdbc-type>
      <sql-type>BIGINT</sql-type>
      <param-setter>org.jboss.test.cmp2.advanced.ejb.JDBCLongSetterAndReader</param-setter>
      <result-reader>org.jboss.test.cmp2.advanced.ejb.JDBCLongSetterAndReader</result-reader>
   </mapping>
...
</type-mapping>

Both param-setter and result-reader are optional. The DTD is

<!--
Specifies the mapping from a java type to a jdbc and a sql type.
-->
<!ELEMENT mapping (java-type, jdbc-type, sql-type, param-setter?, result-reader?)>