Spring’s JdbcTemplate
helps in resource management and exception handling, we just need to encapsulate the database statements in a callback object and pass it to JdbcTemplate
for execution. In this article, I will show you an example of how we can execute database operations using JdbcTemplate
.
Flow of JDBC Operation
The diagram below defines the JDBC steps in executing an operation.
Example of JdbcTemplate
JdbcTemplate
needs a DataSource
object for database access. The actual DataSource
object being referred to by JdbcTemplate
can be any implementation of javax.sql.DataSource
. In our example, we will base our DataSource
on org.springframework.jdbc.datasource.DriverManagerDataSource
. Once we have configured DataSource
bean, we will have to inject it into JdbcTemplate
bean. We can then wire the JdbcTemplate
bean into our example class and then use it to access the database.
In the below applicationContext.xml
, you can see all the configured beans.
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:jdbc="http://www.springframework.org/schema/jdbc" 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.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost/test" /> <property name="username" value="root" /> <property name="password" value="mnrpass" /> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="springDsConExample" class="com.javarticles.jdbc.jdbctemplate.SpringJdbcTemplateExample"> <property name="jdbcTemplate"> <ref bean="jdbcTemplate" /> </property> </bean> </beans>
SpringJdbcTemplateExample
will be our example class. It will act as our application class as well as bean. In its main
method, we get its bean from our context and execute the database statements using JdbcTemplate
object.
SpringJdbcTemplateExample:
package com.javarticles.jdbc.jdbctemplate; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.BadSqlGrammarException; import org.springframework.jdbc.core.JdbcTemplate; public class SpringJdbcTemplateExample { private JdbcTemplate jdbcTemplate; public static void main(String[] args) throws SQLException { ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml"); SpringJdbcTemplateExample springDsExample = (SpringJdbcTemplateExample) context.getBean("springDsConExample"); springDsExample.executeStatements(); } public void executeStatements() throws SQLException { getJdbcTemplate().execute("DROP TABLE IF EXISTS `TEST`"); getJdbcTemplate().execute("CREATE TABLE TEST (NAME varchar(50) not null)"); getJdbcTemplate().execute("INSERT into TEST(NAME) VALUES ('JDBC')"); getJdbcTemplate().update("INSERT into TEST(NAME) VALUES (?)", "JDBC Template"); List<Map<String, Object>> list = getJdbcTemplate().queryForList("SELECT NAME FROM TEST"); for (Map<String, Object> row : list) { System.out.println(row.get("name")); } //bad grammar example try { getJdbcTemplate().queryForList("SELECT NAM FROM TEST"); } catch (BadSqlGrammarException e) { System.out.println("DataAccessException->" + e.getMessage() + ",SQL Code->" + ((SQLException)e.getCause()).getErrorCode()); } } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } }
Output:
JDBC JDBC Template DataAccessException->StatementCallback; bad SQL grammar [SELECT NAM FROM TEST]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'NAM' in 'field list',SQL Code->1054
DataAccessException
In case of any SQLException
, spring catches it and re-throws it as DataAccessException
. There are many exceptions that extend from DataAccessException
like BadSqlGrammarException
, CannotAcquireLockException
etc., each exception addresses a specific failure and represents an error category. Internally, spring maps the SQL error codes based on error category for each database.
The exception hierarchy extending from DataAccessException
aims to let user code find and handle the kind of error encountered without knowing the details of the particular data access API in use (e.g. JDBC). The caller encountering the exception is in a better situation to handle it as the exception is more specific rather than the generic SQLException
and also has reference to the actual SQLException
that caused the error.
If you notice my example, in the end, I execute a bad query to demonstrate how spring manages exceptions. It throws BadSqlGrammarException
as the column name specified in the query is wrong.
JdbcDaoSupport Example
Instead of we explicitly maintaining the JdbcTemplate
member, its setter and getter methods, we just need to extend JdbcDaoSupport
class to inherit its getJdbcTemplate
.
package com.javarticles.jdbc.jdbctemplate; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.BadSqlGrammarException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.support.JdbcDaoSupport; public class SpringJdbcDaoSupportExample extends JdbcDaoSupport { public static void main(String[] args) throws SQLException { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); SpringJdbcDaoSupportExample springDsExample = (SpringJdbcDaoSupportExample) context .getBean("springDsConExample"); springDsExample.executeStatements(); } public void executeStatements() throws SQLException { getJdbcTemplate().execute("DROP TABLE IF EXISTS `TEST`"); getJdbcTemplate().execute( "CREATE TABLE TEST (NAME varchar(50) not null)"); getJdbcTemplate().execute("INSERT into TEST(NAME) VALUES ('JDBC')"); getJdbcTemplate().update("INSERT into TEST(NAME) VALUES (?)", "JDBC Template"); List<Map<String, Object>> list = getJdbcTemplate().queryForList( "SELECT NAME FROM TEST"); for (Map<String, Object> row : list) { System.out.println(row.get("name")); } // bad grammar example try { getJdbcTemplate().queryForList("SELECT NAM FROM TEST"); } catch (BadSqlGrammarException e) { System.out.println("DataAccessException->" + e.getMessage() + ",SQL Code->" + ((SQLException) e.getCause()).getErrorCode()); } } }
We will also have to configure the bean in our applicationContext.xml
<bean id="springJdbcDaoSupportExample" class="com.javarticles.jdbc.jdbctemplate.SpringJdbcDaoSupportExample"> <property name="jdbcTemplate"> <ref bean="jdbcTemplate" /> </property> </bean>
Download Source Code
In this article, I have shown you an example of JdbcTemplate
. You can download the source code here: springtemplate.zip