This article describes the usage of the Spring JDBC Template in a stand-alone application.
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. Links and Literature
4.2. vogella Java example code
If you need more assistance we offer Online Training and Onsite training as well as consulting