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)
	);
		
			

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;
	}
}

			

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);

}
			

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 });
	}

}

			

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.