Java Articles

Spring JdbcTemplate Example

0
By on Spring Data Access

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.

JdbcTemplateFlow

JDBC Operation Flow

 

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

Share.

Leave A Reply