christophertownsondotcom

Out-of-the box, Spring pro­vides a well-conceived and fine-grained run­time data access excep­tion hier­ar­chy. As I am sure most of you are already aware, for most data­bases, this is achieved by map­ping SQL error codes to excep­tions via a file called sql-error-codes.xml (this lives in the org.springframework.jdbc.support pack­age inside the org.springframework.jdbc.jar). How­ever, what is less com­monly know — because it is less com­monly required — is how easy it is to cus­tomise this excep­tion han­dling process. I came across a case where I needed to do this recently, so I thought I would share the process with you here.

I was work­ing on an appli­ca­tion that inte­grates with an Ora­cle data­base and I was oper­at­ing on a table that had trig­gers asso­ci­ated with it. These trig­gers were per­form­ing com­plex data integrity checks. Now, the prob­lem here of course comes when the trig­ger raises an error. In Ora­cle PL/SQL, this is done through the raise_application_error func­tion. This takes 2 required argu­ments: a num­ber in the range –20000 to –20999 (which will become a pos­i­tive SQL excep­tion code) and a var­char (which will be included in the excep­tion mes­sage). So, for exam­ple, raise_application_error(-20999, 'something went wrong!') will result in a SQL excep­tion with code 20999 and will include the mes­sage “some­thing went wrong!”. So far, so obvious.

Now, because Ora­cle will not let you raise an appli­ca­tion error using this tech­nique that results in a stan­dard, pre­de­fined SQL error code, Spring clearly will have no idea what the real nature of the prob­lem is and your appli­ca­tion will throw an UncategorizedSQLException, mak­ing it dif­fi­cult to han­dle with any intel­li­gence. So, for the sake of argu­ment, let’s say we have a trig­ger that looks like this:

CREATE OR REPLACE TRIGGER check_data_integrity_trigger
BEFORE INSERT OR UPDATE ON some_table
    REFERENCING OLD AS pre NEW AS post
    FOR EACH ROW
DECLARE
    lv_table_col some_table.table_col%type;
BEGIN
    SELECT DISTINCT table_col INTO lv_table_col FROM some_table WHERE another_col = :post.another_col;
    IF lv_some_col != :post.some_col THEN
        raise_application_error(-20999, 'constraint violation: table_col must be unique for each another_col'); -- hey, Bob, why don't you just normalise your schema, rather than using nasty triggers? ;-)
    END IF;
EXCEPTION
    WHEN no_data_found THEN
        NULL;
END check_data_integrity_trigger;
/

ALTER TRIGGER check_data_integrity_trigger ENABLE;

And we have a unit test that looks like this:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:com/foo/bar/applicationContext.xml")
@Transactional
@TransactionConfiguration(defaultRollback = true)
public class SomeDAOIntegrationTest {

    @Autowired private SomeDAO dao;

    @Test(expected = DataIntegrityViolationException.class) public void shouldNotBeAbleToInsertTwoDifferentValuesForTableColWhenAnotherColHasTheSameValue() throws Exception {
        // given
        SomeTableBean b1 = instanceWhereTableColValueIs("foo").andAnotherColValueIs("baz");
        dao.insert(b1);
        SomeTableBean b2 = instanceWhereTableColValueIs("bar").andAnotherColValueIs("baz");
        // when
        dao.insert(b2);
    }

}

Just so we get the full pic­ture, let’s say the DAO imple­men­ta­tion looks like like this:

@Repository @SuppressWarnings("synthetic-access") public class SomeJdbcDAO implements SomeDAO {

    private final JdbcTemplate db;

    @Autowired public SomeJdbcDAO(DataSource dataSource) {
        db = new JdbcTemplate(dataSource);
    }

    @Override @Transactional public int insert(SomeTableBean bean) {
        Long id = db.queryForLong("select some_table_seq.nextval from dual");
        bean.setId(id);
        return db.update("insert into some_table(table_id, table_col, another_col) values(?, ?, ?)", id, bean.getTableCol(), bean.getAnotherCol());
    }

}

Now, when we run our test, it fails because, rather than get­ting the expected DataIntegrityViolationException, we get an UncategorizedSQLException. To make the test pass, all we need to do is add a sim­ple Spring con­text file to the root of the class­path called sql-error-codes.xml, like so:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:util="http://www.springframework.org/schema/util"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">

    <bean id="dataIntegrityViolatonTriggerCodes" class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
        <property name="errorCodes" value="20999" /><!-- comma-separated list of error code to translate into the given exception class -->
        <property name="exceptionClass" value="org.springframework.dao.DataIntegrityViolationException" /><!-- we could also make this a custom exception if desired but why create our own when a perfectly suitable class already exists? -->
    </bean>

    <util:list id="customTranslations">
        <ref bean="dataIntegrityViolatonTriggerCodes" />
    </util:list>

    <bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
        <property name="customTranslations" ref="customTranslations" />
    </bean>

</beans>

Now, when­ever the trig­ger raises its cus­tom error, the expected DataIntegrityViolationException will be thrown and our appli­ca­tion will be able to inter­pret the mean­ing of the error and act accord­ingly if necessary.