Support free tutorials









vogella training Training Books



The Spring Jdbc Template for database access - Tutorial

Lars Vogel

Version 0.1

16.06.2008

Abstract

This article describes the usage of the Spring JDBC Template in a stand-alone application.


Table of Contents

1. Spring JDBC
2. Spring Installation
3. Using Spring JDBC template
3.1. Create Database
3.2. Create Java Project and domain model
3.3. Create the Data Access Object (DAO)Interface
3.4. Create the Dao with the JDBC Template
3.5. Main program (for testing)
4. Support this website
4.1. Thank you
4.2. Questions and Discussion
5. Links and Literature
5.1. Source Code
5.2. Spring Links

1. Spring JDBC

Spring provides a simplification in handling database access with the Spring JDBC Template.

The Spring JDBC Template has the following advantages compared with standard JDBC.

  • The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.

  • The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.

The Spring JDBC template offers several ways to query the database. queryForList() returns a list of HashMaps. The name of the column is the key in the hashmap for the values in the table.

More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.

2. Spring Installation

Please see Spring Installation.

To use the Spring JDBC template you need to add spring-jdbc.jar to your classpath (in addition to the standard jars described in the above installation link).

3. Using Spring JDBC template

The following example will demonstrate the usage of the JDBC template. The example is based on derby.

3.1. Create Database

Create a small derby database under c:\temp\databases\test01 with the table "Person". Please see Handling Database with the Eclipse Data Tools Project for an explanation on how to do this with the Eclipse Data Tools Project.

Create the table Person with the following SQL statement.

CREATE TABLE Person(ID int generated by default as identity (start with 1) not null,
    FIRSTNAME VARCHAR(20) NOT NULL,
    LASTNAME VARCHAR(20) NOT NULL,
    PRIMARY KEY (ID)); 

3.2. Create Java Project and domain model

Create a new Java project "de.vogella.spring.jdbctemplate" and include the minimal required jars into your classpath. Add also derby.jar to the classpath.

Create the following domain model.

package domainmodel;

public class Person {

  private String firstName;
  private String lastName;

  public String getFirstName() {
    return firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
} 

3.3. Create the Data Access Object (DAO)Interface

Although not required it is always a good idea to program against an interface. Create the following DAO interface.

package dao;

import java.util.List;

import javax.sql.DataSource;

import domainmodel.Person;

public interface IDao {

  void setDataSource(DataSource ds);

  void create(String firstName, String lastName);

  List<Person> select(String firstname, String lastname);

  List<Person> selectAll();

  void deleteAll();

  void delete(String firstName, String lastName);

} 

3.4. Create the Dao with the JDBC Template

Now you can create the Dao using the JDBC Template function.

package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.ResultSetExtractor;

import domainmodel.Person;

public class PersonResultSetExtractor implements ResultSetExtractor {

  @Override
  public Object extractData(ResultSet rs) throws SQLException {
    Person person = new Person();
    person.setFirstName(rs.getString(1));
    person.setLastName(rs.getString(2));
    return person;
  }

} 

package dao.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class PersonRowMapper implements RowMapper {

  @Override
  public Object mapRow(ResultSet rs, int line) throws SQLException {
    PersonResultSetExtractor extractor = new PersonResultSetExtractor();
    return extractor.extractData(rs);
  }

} 

package dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import dao.mapper.PersonRowMapper;
import domainmodel.Person;

public class DerbyDao implements IDao {
  private DataSource dataSource;

  public void setDataSource(DataSource ds) {
    dataSource = ds;
  }

  public void create(String firstName, String lastName) {
    JdbcTemplate insert = new JdbcTemplate(dataSource);
    insert.update("INSERT INTO PERSON (FIRSTNAME, LASTNAME) VALUES(?,?)",
        new Object[] { firstName, lastName });
  }

  public List<Person> select(String firstname, String lastname) {
    JdbcTemplate select = new JdbcTemplate(dataSource);
    return select
        .query("select  FIRSTNAME, LASTNAME from PERSON where FIRSTNAME = ? AND LASTNAME= ?",
            new Object[] { firstname, lastname },
            new PersonRowMapper());
  }

  public List<Person> selectAll() {
    JdbcTemplate select = new JdbcTemplate(dataSource);
    return select.query("select FIRSTNAME, LASTNAME from PERSON",
        new PersonRowMapper());
  }

  public void deleteAll() {
    JdbcTemplate delete = new JdbcTemplate(dataSource);
    delete.update("DELETE from PERSON");
  }

  public void delete(String firstName, String lastName) {
    JdbcTemplate delete = new JdbcTemplate(dataSource);
    delete.update("DELETE from PERSON where FIRSTNAME= ? AND LASTNAME = ?",
        new Object[] { firstName, lastName });
  }

} 

3.5. Main program (for testing)

Finally create the following test class for testing your Spring Jdbc Template.

package test;

import java.util.List;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import dao.DerbyDao;
import domainmodel.Person;

public final class Main {
  private Main() {
  };

  public static void main(String[] args) {
    DerbyDao dao = new DerbyDao();
    // Initialize the datasource, could /should be done of Spring
    // configuration
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("org.apache.derby.jdbc.EmbeddedDriver");
    dataSource.setUrl("jdbc:derby:c:\\temp\\database\\test01;create=true");
    dataSource.setUsername("");
    dataSource.setPassword("");
    // Inject the datasource into the dao
    dao.setDataSource(dataSource);

    dao.create("Lars", "Vogel");
    dao.create("Jim", "Knopf");
    dao.create("Lars", "Man");
    dao.create("Spider", "Man");
    System.out.println("Now select and list all persons");
    List<Person> list = dao.selectAll();
    for (Person myPerson : list) {
      System.out.print(myPerson.getFirstName() + " ");
      System.out.println(myPerson.getLastName());
    }
    System.out
        .println("Now select and list all persons with have the firstname Lars and lastname Vogel");
    list = dao.select("Lars", "Vogel");
    for (Person myPerson : list) {
      System.out.print(myPerson.getFirstName() + " ");
      System.out.println(myPerson.getLastName());
    }

    // Clean-up
    dao.deleteAll();
  }
} 

Run the main program in class Main to see the result.

4. Support this website

This tutorial is Open Content under the CC BY-NC-SA 3.0 DE license. Source code in this tutorial is distributed under the Eclipse Public License. See the vogella License page for details on the terms of reuse.

Writing and updating these tutorials is a lot of work. If this free community service was helpful, you can support the cause by giving a tip as well as reporting typos and factual errors.

4.1. Thank you

Please consider a contribution if this article helped you. It will help to maintain our content and our Open Source activities.

4.2. Questions and Discussion

If you find errors in this tutorial, please notify me (see the top of the page). Please note that due to the high volume of feedback I receive, I cannot answer questions to your implementation. Ensure you have read the vogella FAQ as I don't respond to questions already answered there.

5. Links and Literature

5.1. Source Code

Source Code of Examples

5.2. Spring Links

http://www.springframework.org/ Homepage of the Spring Framework

http://www.vogella.com/tutorials/SpringDependencyInjection/article.html Introduction into Spring Dependency Injection