Using the JDBC core classes to control basic JDBC processing and error handling - Java-Springs

JdbcTemplate

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 work flow such as statement creation and execution, leaving application code to provide SQL and extract results. The Jdbc Template class executes SQL queries, update statements and stored procedure calls, performs iteration over Result Sets 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. spring frame work.dao package.

When you use the Jdbc Template for your code, you only need to implement callback interfaces, giving them a clearly defined contract. The Prepared Statement Creator call back interface creates a prepared statement given a Connection provided by this class, providing SQL and any necessary parameters.The same is true for the Call able Statement Creator interface, which creates callable statements.The Row Call back Handler interface extracts values from each row of a ResultSet.

The Jdbc Template 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.

All SQL issued by this class is logged at the DEBUG level under the category corresponding to the fully qualified class name of the template instance (typically JdbcTemplate, but it may be different if you are using a custom subclass of the JdbcTemplate class)

Examples of JdbcTemplate class usage

These examples are not an exhaustive list of all of the functionality exposed by the JdbcTemplate; see the attendant Javadocs for that.

Querying (SELECT)

Here is a simple query for getting the number of rows in a relation:

int rowCount = this.jdbcTemplate.queryForInt("select count(*) from t_actor");

A simple query using a bind variable:

int countOfActorsNamedJoe = this.jdbcTemplate.queryForInt("select count(*) from t_actor where first_name = ?", "Joe");

Querying for a String:

String lastName = this.jdbcTemplate.queryForObject(
"select last_name from t_actor where id = ?",
new Object[]{1212L}, String.class);

Querying and populating a single domain object:

Actor actor = this.jdbcTemplate.queryForObject(
"select first_name, last_name from t_actor where id = ?",
new Object[]{1212L},
new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
});

Querying and populating a number of domain objects:

List<Actor> actors = this.jdbcTemplate.query(
"select first_name, last_name from t_actor",
new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
});

If the last two snippets of code actually existed in the same application, it would make sense to remove the duplication present in the two Row Mapper anonymous inner classes, and extract them out into a single class (typically a static inner class) that can then be referenced by DAO methods as needed. For example, it may be better to write the last code snippet as follows:

public List<Actor> findAllActors() {
return this.jdbcTemplate.query
( "select first_name, last_name from t_actor", new ActorMapper());
}
private static final class ActorMapper implements RowMapper<Actor> {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
}

Updating (INSERT/UPDATE/DELETE) with jdbcTemplate

You use the update(..) method to perform insert, update and delete operations. Parameter values are usually provided as var args or alternatively as an object array.

this.jdbcTemplate.update(
"insert into t_actor (first_name, last_name) values (?, ?)",
"Leonor", "Watling");
this.jdbcTemplate.update(
"update t_actor set = ? where id = ?",
"Banjo", 5276L);
this.jdbcTemplate.update(
"delete from actor where id = ?",
Long.valueOf(actorId));

Other jdbcTemplate operations

You can use the execute(..) method to execute any arbitrary SQL, and as such the method is often used for DDL statements. It is heavily overloaded with variants taking callback interfaces, binding variable arrays, and so on.

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

The following example invokes a simple stored procedure.

this.jdbcTemplate.update(
"call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
Long.valueOf(unionId));

JdbcTemplate best practices

Instances of the Jdbc Template class are threadsafe once configured. This is important because it means that you can configure a single instance of a JdbcTemplate and then safely inject this shared reference into multiple DAOs (or repositories). The Jdbc Template is stateful, in that it maintains a reference to a DataSource, but this state is not conversational state.

A common practice when using the JdbcTemplate class (and the associated Simple Jdbc Template and Named Parameter Jdbc Template classes) is to configure a DataSource in your Spring configuration file, and then dependency-inject that shared Data Source bean into your DAO classes; the JdbcTemplate is created in the setter for the Data Source.This leads to DAOs that look in part like the following:

public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow.
}

An alternative to explicit configuration is to use component-scanning and annotation support for dependency injection.In this case you annotate the class with @Repository (which makes it a candidate for component-scanning) and annotate the DataSource setter method with @Autowired.

@Repository
public class JdbcCorporateEventDao implements CorporateEventDao {
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
// JDBC-backed implementations of the methods on the CorporateEventDao follow.
}

If you are using Spring's Jdbc Dao Support class, and your various JDBC-backed DAO classes extend from it, then your sub-class inherits a setDataSource(..) method from the Jdbc Dao Support class. You can choose whether to inherit from this class. The Jdbc Dao Support class is provided as a convenience only.

Regardless of which of the above template initialization styles you choose to use (or not), it is seldom necessary to create a new instance of a Jdbc Template class each time you want to execute SQL. Once configured, a JdbcTemplate instance is threadsafe. You may want multiple Jdbc Template instances if your application accesses multiple databases, which requires multiple DataSources, and sub sequently multiple differently configured JdbcTemplates.

NamedParameterJdbcTemplate

The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ('?') arguments.The Named Parameter Jdbc Template class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(*) from T_ACTOR where first_name = :first_name";
SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName);
return namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}

Notice the use of the named parameter notation in the value assigned to the sql variable, and the corresponding value that is plugged into the namedParameters variable (of type MapSql Parameter Source).

Alternatively, you can pass along named parameters and their corresponding values to a Named Parameter Jdbc Template instance by using the Map-based style.The remaining methods exposed by the Named Paramete rJdbc Operations and implemented by the Named Parameter Jdbc Template class follow a similar pattern and are not covered here.

The following example shows the use of the Map-based style.

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActorsByFirstName(String firstName) {
String sql = "select count(*) from T_ACTOR where first_name = :first_name";
Map namedParameters = Collections.singletonMap("first_name", firstName);
return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
}

One nice feature related to the Named Parameter Jdbc Template (and existing in the same Java package) is the Sql Parameter Source interface. You have already seen an example of an implementation of this interface in one of the previous code snippet (the MapSqlP arameter Source class). An SqlP arameter Source is a source of named parameter values to a Named Parameter Jdbc Template. The Map Sql Parameter Source class is a very simple implementation that is simply an adapter around a java. util .Map, where the keys are the parameter names and the values are the parameter values.

Another SqlParameterSource implementation is the Bean Property Sql Parameter Source class. This class wraps an arbitrary JavaBean (that is, an instance of a class that adheres to the Java Bean conventions), and uses the properties of the wrapped JavaBean as the source of named parameter values.

public class Actor {
private Long id;
private String firstName;
private String lastName;
public String getFirstName() {
return this.firstName;
}
public String getLastName() {
return this.lastName;
}
public Long getId() {
return this.id;
}
// setters omitted...
}
// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int countOfActors(Actor exampleActor) {
// notice how the named parameters match the properties of the above 'Actor' class
String sql =
"select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
return this.namedParameterJdbcTemplate.queryForInt (sql, namedParameters);
}

Remember that the Named Parameter Jdbc Template class wraps a classic JdbcTemplate template; if you need access to the wrapped Jdbc Template instance to access functionality only present in the JdbcTemplate class, you can use the getJdbcOperations() method to access the wrapped Jdbc Template through the JdbcOperations interface.

SimpleJdbcTemplate

The Simple Jdbc Template class wraps the classic JdbcTemplate and leverages Java 5 language features such as varargs and autoboxing.

The value-add of the Simple Jdbc Template class in the area of syntactic-sugar is best illustrated with a before-and-after example. The next code snippet shows data access code that uses the classic Jdbc Template, followed by a code snippet that does the same job with the Simple Jdbc Template.

// classic JdbcTemplate-style...
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public Actor findActor(String specialty, int age) {
String sql = "select id, first_name, last_name from T_ACTOR" +
" where specialty = ? and age = ?";
RowMapper<Actor> mapper = new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
// notice the wrapping up of the argumenta in an array
return (Actor) jdbcTemplate.queryForObject (sql, new Object[] {specialty, age}, mapper);
}

Here is the same method, with the SimpleJdbcTemplate.

// SimpleJdbcTemplate-style...
private SimpleJdbcTemplate simpleJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
public Actor findActor(String specialty, int age) {
String sql = "select id, first_name, last_name from T_ACTOR" +
" where specialty = ? and age = ?";
RowMapper<Actor> mapper = new RowMapper<Actor>() {
public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
Actor actor = new Actor();
actor.setId(rs.getLong("id"));
actor.setFirstName(rs.getString("first_name"));
actor.setLastName(rs.getString("last_name"));
return actor;
}
};
// notice the use of varargs since the parameter values now come
// after the RowMapper parameter
return this.simpleJdbcTemplate.queryForObject(sql, mapper, specialty, age);
}

SQLExceptionTranslator

SQLException Translator is an interface to be implemented by classes that can translate between SQL Exceptions and Spring's own org. spring frame work. dao. Data Access Exception, which is agnostic in regard to data access strategy. Implementations can be generic (for example, using SQLState codes for JDBC) or proprietary (for example, using Oracle error codes) for greater precision. SQL Error Code SQL Exception Translator is the implementation of SQL Exception Translator that is used by default.This implementation uses specific vendor codes.

It is more precise than the SQLState implementation. The error code translations are based on codes held in a JavaBean type class called SQL ErrorCodes. This class is created and populated by an SQL Error Codes Factory which as the name suggests is a factory for creating SQLErrorCodes based on the contents of a configuration file named sql-error-codes.xml. This file is populated with vendor codes and based on the DatabaseProductName taken from the DatabaseMetaData. The codes for the acual database you are using are used.

The SQLErrorCodeSQLExceptionTranslator applies matching rules in the following sequence:

  1. Any custom implementation of the SQL Exception Translator interface that is provided as the custom Sql Exception Translator property of the SQLErrorCodes class.
  2. The list of instances of the Custom SQL Error Codes Translation class, provided for the custom Translations property of the SQL Error Codes class, are searched for a match.
  3. Error code matching is applied.
  4. Use the fallback translator. SQL Exception Sub class Translator is the default fallback translator. If this translation is not available then the next fall back translator is the SQL State SQL Exception Translator.
You can extend SQLErrorCodeSQLExceptionTranslator:
public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
protected DataAccessException customTranslate (String task, String sql, SQLException sqlex) {
if (sqlex.getErrorCode() == -12345) {
return new DeadlockLoserDataAccessException(task, sqlex);
}
return null;
}
}

In this example, the specific error code -12345 is translated and other errors are left to be translated by the default translator implementation. To use this custom translator, it is necessary to pass it to the JdbcTemplate through the method set Exception Translator and to use this Jdbc Template for all of the data access processing where this translator is needed. Here is an example of how this custom translator can be used:

private JdbcTemplate jdbcTemoplate;
public void setDataSource(DataSource dataSource) {
// create a JdbcTemplate and set data source
this.jdbcTemplate = new JdbcTemplate();
this.jdbcTemplate.setDataSource(dataSource);
// create a custom translator and set the
DataSource for the default translation lookup

CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
tr.setDataSource(dataSource);
this.jdbcTemplate.setExceptionTranslator(tr);
}
public void updateShippingCharge(long orderId, long pct) {
// use the prepared JdbcTemplate for this update
this.jdbcTemplate.update(
"update orders" +
" set shipping_charge = shipping_charge * ? / 100" +
" where id = ?"
pct, orderId);
}

The custom translator is passed a data source in order to look up the error codes in sql-error-codes.xml.

Executing statements

Executing an SQL statement requires very little code. You need a Data Source and an Jdbc Template, including the convenience methods that are provided with the Jdbc Template. The following example shows what you need to include for a minimal but fully functional class that creates a new table:

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAStatement {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void doExecute() {
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
}
}

Running queries

Some query methods return a single value. To retrieve a count or a specific value from one row, use queryForInt(..), query ForLong(..) or query For Object(..). The latter converts the returned JDBC Type to the Java class that is passed in as an argument. If the type conversion is invalid, then an Invalid Data Access Api Usage Exception is thrown. Here is an example that contains two query methods, one for an int and one that queries for a String.

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class RunAQuery {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int getCount() {
return this.jdbcTemplate.queryForInt("select count(*) from mytable");
}
public String getName() {
return (String) this.jdbcTemplate.queryForObject ("select name from mytable", String.class);
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
}

In addition to the single result query methods, several methods return a list with an entry for each row that the query returned. The most generic method is queryForList(..) which returns a List where each entry is a Map with each entry in the map representing the column value for that row. If you add a method to the above example to retrieve a list of all the rows, it would look like this:

private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Map<String, Object>> getList() {
return this.jdbcTemplate.queryForList("select * from mytable");
}

The list returned would look something like this:

[{name=Bob, id=1}, {name=Mary, id=2}]

Updating the database

The following example shows a column up dated for a certain primary key. In this example, an SQL statement has place holders for row parameters. The parameter values can be passed in as varargs or alternatively as an array of objects. Thus primitives should be wrapped in the primitive wrapper classes explicitly or using auto-boxing

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ExecuteAnUpdate {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void setName(int id, String name) {
this.jdbcTemplate.update(
"update mytable set name = ? where id = ?",
name, id);
}
}

Retrieving auto-generated keys

An update() convenience method supports the retrieval of primary keys generated by the data base.This support is part of the JDBC 3.0 standard.The method takes a PreparedStatementCreator as its first argument, and this is the way the required insert statement is specified. The other argument is a Key Holder, which contains the generated key on successful return from the update. There is not a standard single way to create an appropriate Prepared Statement (which explains why the method signature is the way it is).The following example works on Oracle but may not work on other platforms:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement (Connection connection) throws SQLException {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, name);
return ps;
}
},
keyHolder);
// keyHolder.getKey() now contains the generated key.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Java-Springs Topics