Spring Tutorials

Spring JdbcTemplate is a powerful mechanism to connect to the Database and execute SQL queries. It internally uses JDBC api, but eliminates a lot of problems of JDBC API.The JdbcTemplate can be used within a DAO implementation through direct instantiation with a DataSource reference, or be configured in a Spring IoC container and given to DAOs as a bean reference.

There are several problems of JDBC API as given below.

1. We need to write a lot of code before and after executing the query, such as creating connection, statement, closing resultset, connection etc.
2. We need to perform exception handling code on the database logic.
3. We need to handle transaction.
4. Repetition of all these codes from one to another database logic is a time consuming task.

Advantage of Spring JdbcTemplate

Spring JdbcTemplate eliminates all the above mentioned problems of JDBC API. It provides you methods to write the queries directly, so it saves a lot of work and time.

The JdbcTemplate class is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow such as statement creation and execution, leaving application code to provide SQL and extract results.

The JdbcTemplate class executes SQL queries, update statements and stored procedure calls, performs iteration over ResultSets and extraction of returned parameter values. It also catches JDBC exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Instances of the JdbcTemplate class are threadsafe once configured. So you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs.It handles the exception and provides the informative exception messages by the help of excepion classes defined in the org.springframework.dao package.

Let us create a database table Employee in our database Test. I assume you are working with MySQL database, if you work with any other database then you can change your DDL and SQL queries accordingly.

create table employee(
id int not null auto_increment,
name varchar(20) not null,
salary int not null,
primary key (id)
);

Now we need to provide a DataSource to the JdbcTemplate so it can configure itself to get database access. You can configure the DataSource in the XML file with a piece of code as shown below:

<bean id="dataSource" class=
    "org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/Test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>