Using JDBC with Spring

There are many persistence technologies out there. Hibernate, iBATIS, and JPAare
just a few. Despite this, a good number of applications are writing Java objects to a
database the old-fashioned way: they earn it. No, wait—that’s how people make
money. The tried-and-true method for persisting data is with good old JDBC.
And why not? JDBCdoesn’t require mastering another framework’s query language. It’s built on top of SQL, which is the data access language. Plus, you can more
finely tune the performance of your data access when you use JDBCthan with practically any other technology. And JDBCallows you to take advantage of your database’s
proprietary features, where other frameworks may discourage or flat-out prohibit this.
What’s more, JDBClets you work with data at a much lower level than the persistence frameworks, allowing you to access and manipulate individual columns in a
database. This fine-grained approach to data access comes in handy in applications,
such as reporting applications, where it doesn’t make sense to organize the data into
objects, just to then unwind it back into raw data.
But all is not sunny in the world of JDBC. With its power, flexibility, and other niceties also come some not-so-niceties.
Tackling runaway JDBC code
Though JDBCgives you an APIthat works closely with yourdatabase, you’re responsible for handling everything related to accessing the database. This includes managing
database resources and handling exceptions.
If you’ve ever written JDBCthat inserts data into the database, the following
shouldn’t be too alien to you.

private static final String SQL_INSERT_SPITTER =
"insert into spitter (username, password, fullname) values (?, ?, ?)";
private DataSource dataSource;
public void addSpitter(Spitter spitter) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(SQL_INSERT_SPITTER);
stmt.setString(1, spitter.getUsername());
stmt.setString(2, spitter.getPassword());
stmt.setString(3, spitter.getFullName());
stmt.execute();
} catch (SQLException e) {
// do something...not sure what, though
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// I'm even less sure about what to do here
}
}
}

private static final String SQL_UPDATE_SPITTER =
"update spitter set username = ?, password = ?, fullname = ?"
+ "where id = ?";
public void saveSpitter(Spitter spitter) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = dataSource.getConnection();

stmt = conn.prepareStatement(SQL_UPDATE_SPITTER);
stmt.setString(1, spitter.getUsername());
stmt.setString(2, spitter.getPassword());
stmt.setString(3, spitter.getFullName());
stmt.setLong(4, spitter.getId());
stmt.execute();
} catch (SQLException e) {
// Still not sure what I'm supposed to do here
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
// or here
}
}

}

private static final String SQL_SELECT_SPITTER =
"select id, username, fullname from spitter where id = ?";
public Spitter getSpitterById(long id) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement(SQL_SELECT_SPITTER);
stmt.setLong(1, id);
rs = stmt.executeQuery();
Spitter spitter = null;
if (rs.next()) {
spitter = new Spitter();
spitter.setId(rs.getLong("id"));
spitter.setUsername(rs.getString("username"));

spitter.setPassword(rs.getString("password"));
spitter.setFullName(rs.getString("fullname"));
}
return spitter;
} catch (SQLException e) {
} finally {
if(rs != null) {
try {
rs.close();
} catch(SQLException e) {}
}
if(stmt != null) {
try {
stmt.close();
} catch(SQLException e) {}
}
if(conn != null) {
try {
conn.close();
} catch(SQLException e) {}
}
}
return null;

}

Working with JDBC templates

Spring’s JDBC framework will clean up your JDBCcode by shouldering the burden of
resource management and exception handling. This leaves you free to write only the

code necessary to move data to and from the database.
All that a SimpleJdbcTemplateneeds to do its work is a DataSource. This makes it easy
enough to configure a SimpleJdbcTemplatebean in Spring with the following XML:
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
<constructor-arg ref="dataSource" />
</bean>
The actual DataSourcebeing referred to by the dataSourceproperty can be any
implementation of javax.sql.DataSource, including those we created in section 5.2.
Now we can wire the jdbcTemplatebean into our DAOand use it to access the database. For example, suppose that the Spitter DAOis written to use SimpleJdbcTemplate:
public class JdbcSpitterDAO implements
SpitterDAO {
...
private SimpleJdbcTemplate jdbcTemplate;
public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
You’d then wire the jdbcTemplateproperty of JdbcSpitterDAOas follows:
<bean id="spitterDao"
class="com.habuma.spitter.persistence.SimpleJdbcTemplateSpitterDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

With a SimpleJdbcTemplateat our DAO’s disposal, we can greatly simplify the
addSpitter()method from listing The new SimpleJdbcTemplate-based

addSpitter()method is shown next.

public void addSpitter(Spitter spitter) {
jdbcTemplate.update(SQL_INSERT_SPITTER,
spitter.getUsername(),
spitter.getPassword(),
spitter.getFullName(),
spitter.getEmail(),
spitter.isUpdateByEmail());
spitter.setId(queryForIdentity());
}

Querying for a Spitterusing SimpleJdbcTemplate

public Spitter getSpitterById(long id) {
return jdbcTemplate.queryForObject(
SQL_SELECT_SPITTER_BY_ID,
new ParameterizedRowMapper<Spitter>() {
public Spitter mapRow(ResultSet rs, int rowNum)
throws SQLException {
Spitter spitter = new Spitter();
spitter.setId(rs.getLong(1));
spitter.setUsername(rs.getString(2));
spitter.setPassword(rs.getString(3));
spitter.setFullName(rs.getString(4));
return spitter;
}
},
id
);
}

Using named parameters with Spring JDBC templates

public void addSpitter(Spitter spitter) {
Map<String, Object> params = new HashMap<String, Object>();
params.put("username", spitter.getUsername());
params.put("password", spitter.getPassword());
params.put("fullname", spitter.getFullName());
jdbcTemplate.update(SQL_INSERT_SPITTER, params);
spitter.setId(queryForIdentity());

}

USING SPRING’S DAO SUPPORT CLASSES FOR JDBC

public class JdbcSpitterDao extends SimpleJdbcDaoSupport
implements SpitterDao {
...

}


public void addSpitter(Spitter spitter) {
getSimpleJdbcTemplate().update(SQL_INSERT_SPITTER,
spitter.getUsername(),
spitter.getPassword(),
spitter.getFullName(),
spitter.getEmail(),
spitter.isUpdateByEmail());
spitter.setId(queryForIdentity());
}

When configuring your DAOclass in Spring, you could directly wire a SimpleJdbcTemplatebean into its jdbcTemplateproperty as follows:
<bean id="spitterDao"
 class="com.habuma.spitter.persistence.JdbcSpitterDao"> <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

This will work,