SQLExceptionTranslator example

This section is about the handling or translating errors using SQLExceptionTranslator interface.

This section is about the handling or translating errors using SQLExceptionTranslator interface.

SQLExceptionTranslator example

The SQLExceptionTranslator interface is implemented by classes which are doubtful in term of data access. In other words we can say that it is a Strategy interface for translating between SQLExceptions and Spring's data access strategy-agnostic DataAccessException hierarchy.

The 'SQLExceptionTranslator' implements 'SQLErrorCodeSQLExceptionTranslator' for mapping error code that comes out of the database to catch the SQLException to meaningful application-level exceptions. The 'SQLErrorCodes' JavaBean class translates error code. The 'SQLErrorCodesFactory' creates 'SQLErrorCodes' which is based on the 'sql-error-codes.xml' .

EXAMPLE

The given below example implements 'SQLExceptionTranslator' interface using 'SQLErrorCodeSQLExceptionTranslator' to translate exceptions to meaningful application-level exceptions. In the class "CustomSQLErrorCodesTranslator" , the error with error code -12345 is translated and all the remaining errors are left for translation by the SQLExceptionTranslator interface. We pass the method setExceptionTranslator to JdbcTemplate because it is nesessary to implement custom translator. Given below is the demonstrate of this custom translator can be used :

CustomSQLErrorCodesTranslator.java

package net.roseindia;

import java.sql.SQLException;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.DeadlockLoserDataAccessException;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;

public class CustomSQLErrorCodesTranslator extends
		SQLErrorCodeSQLExceptionTranslator {
	protected DataAccessException customTranslate(String task, String sql,
			SQLException sqlex) {
		if (sqlex.getErrorCode() == -12345) {
			return new DeadlockLoserDataAccessException(task, sqlex);
		}
		return null;
	}

}

ExceptionTranslator.java

package net.roseindia;

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class ExceptionTranslator {
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
	// create a JdbcTemplate and set data source
	this.jdbcTemplate = new JdbcTemplate();
	this.jdbcTemplate.setDataSource(dataSource);
	// create a custom translator and set the DataSource for the default
	// translation lookup
	CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
	tr.setDataSource(dataSource);
	this.jdbcTemplate.setExceptionTranslator(tr);
	}

	public void updateRecords(String fn, long id) {
	// use the prepared JdbcTemplate for this update
	this.jdbcTemplate.update(
			"update skills set first_name = ? where id = ?", fn, id);
	}
}

ExceptionTranslator.xml

<?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: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="ET" class="net.roseindia.ExceptionTranslator">

<property name="dataSource" ref="dataSource" />

</bean>

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

destroy-method="close">

<property name="driverClass" value="com.mysql.jdbc.Driver" />

<property name="jdbcUrl" value="jdbc:mysql://192.168.10.13:3306/ankdb" />

<property name="user" value="root" />

<property name="password" value="root" />

</bean>

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

</beans>

ExceptionTranslatorMain.java

package net.roseindia;

import org.springframework.beans.factory.xml.XmlBeanFactory;

import org.springframework.core.io.ClassPathResource;

public class ExceptionTranslatorMain {
	public static void main(String[] args) {
	XmlBeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource(
			"ExceptionTranslator.xml"));

	ExceptionTranslator myBean = (ExceptionTranslator) beanFactory
			.getBean("ET");

	myBean.updateRecords("Ankit Kumar", 1101);
	}
}

OUTPUT

Before execution of code (database table-skills):