Simplifying JDBC Operations with SimpleJdbc Classes

The SimpleJdbcInsert and SimpleJdbcCall classes provide simplified configuration using database metadata that can be obtained through the JDBC driver. This means less configuration work, although you can override or disable metadata processing if you prefer to provide detail in your code.

Inserting data using SimpleJdbcInsert

Let's start by looking at the SimpleJdbcInsert class with a minimum number of configuration parameters. You need to instantiate SimpleJdbcInsert in the data access layer initialization method. In this example, the initializing method is the setDataSource method. There is no need to subclass the SimpleJdbcInsert class. Instead, you can create a new instance and set the table name using the withTableName method. The configuration methods for this class follow the fluid style, which returns an instance of SimpleJdbcInsert, allowing all configuration methods to be chained together. The following example uses only one configuration method (examples of multiple methods will be shown later):

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource).withTableName("t_actor");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(3);
        parameters.put("id", actor.getId());
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        insertActor.execute(parameters);
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource).withTableName("t_actor")
    fun add(actor: Actor) {
        val parameters = mutableMapOf<String, Any>()
        parameters["id"] = actor.id
        parameters["first_name"] = actor.firstName
        parameters["last_name"] = actor.lastName
        insertActor.execute(parameters)
    }
    // ... additional methods
}

Used here the execute method takes the usual java.util.Map as its only parameter. It is important to note that the keys used for the Map must match the table column names as defined in the database. This occurs because the metadata is read to create the actual insert statement.

Getting auto-generated keys using SimpleJdbcInsert

The following example uses the same insert as same as in the previous example, but instead of passing id, it automatically retrieves the generated key and then sets it for a new Actor object. When you create a SimpleJdbcInsert, in addition to specifying the table name, the insert specifies the name of the generated key column using the usingGeneratedKeyColumns method. The following listing shows how this is done:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor").usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }
// ... additional methods
}

The main difference when performing an insert using the second approach is that you don't add id to Map, but call the executeAndReturnKey method. This returns a java.lang.Number object that can be used to instantiate the numeric type used in the domain class. Here you can't rely on all databases to return a specific Java class. java.lang.Number is the main class that can be used. If there are multiple automatically generated columns or the generated values are not numeric, then you can use the KeyHolder returned by the executeAndReturnKeyHolder method.

Set columns to SimpleJdbcInsert

You can limit the columns to be inserted by specifying a list of column names using the usingColumns method, as shown in the following example:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingColumns("first_name", "last_name")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        Map<String, Object> parameters = new HashMap<String, Object>(2);
        parameters.put("first_name", actor.getFirstName());
        parameters.put("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    //  ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingColumns("first_name", "last_name")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = mapOf(
                "first_name" to actor.firstName,
                "last_name" to actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters);
        return actor.copy(id = newId.toLong())
    }
    // ... additional methods
}

The insertion process is the same as if you resorted to metadata to determine which columns to use.

Using SqlParameterSource to specify parameter values

Using Map to specify parameter values works well enough, but it's not the most user-friendly class. Spring contains several implementations of the SqlParameterSource interface that can be used instead. The first one is BeanPropertySqlParameterSource, which is a very handy class if you have a JavaBean-compatible class that holds your values. It uses the appropriate getter to retrieve the parameter values. The following example shows how to use BeanPropertySqlParameterSource:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        SqlParameterSource parameters = new BeanPropertySqlParameterSource(actor);
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = BeanPropertySqlParameterSource(actor)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }
    // ... additional methods
}

Another option is MapSqlParameterSource , which is similar to Map but provides a more convenient addValue method that can be chained together. The following example shows how to use it:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcInsert insertActor;
    public void setDataSource(DataSource dataSource) {
        this.insertActor = new SimpleJdbcInsert(dataSource)
                .withTableName("t_actor")
                .usingGeneratedKeyColumns("id");
    }
    public void add(Actor actor) {
        SqlParameterSource parameters = new MapSqlParameterSource()
                .addValue("first_name", actor.getFirstName())
                .addValue("last_name", actor.getLastName());
        Number newId = insertActor.executeAndReturnKey(parameters);
        actor.setId(newId.longValue());
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val insertActor = SimpleJdbcInsert(dataSource)
            .withTableName("t_actor")
            .usingGeneratedKeyColumns("id")
    fun add(actor: Actor): Actor {
        val parameters = MapSqlParameterSource()
                    .addValue("first_name", actor.firstName)
                    .addValue("last_name", actor.lastName)
        val newId = insertActor.executeAndReturnKey(parameters)
        return actor.copy(id = newId.toLong())
    }
    // ... additional methods
}

As you can see, the configuration is the same. To use these alternative input classes, you only need to change the executing code.

Calling a stored procedure with SimpleJdbcCall

The SimpleJdbcCall class uses metadata in the database to look up the names of the in and out parameters, so there is no need to explicitly declare them. You can declare parameters if you prefer to do so or if you have parameters (such as ARRAY or STRUCT) that do not automatically map to the Java class. The first example shows a simple procedure that returns only scalar values in the format VARCHAR and DATE from a MySQL database. The example procedure reads the specified actor record and returns the first_name, last_name, and birth_date columns as out parameters. The following listing shows the last example:

CREATE PROCEDURE read_actor (
    IN in_id INTEGER,
    OUT out_first_name VARCHAR(100),
    OUT out_last_name VARCHAR(100),
    OUT out_birth_date DATE)
BEGIN
    SELECT first_name, last_name, birth_date
    INTO out_first_name, out_last_name, out_birth_date
    FROM t_actor where id = in_id;
END;

The in_id parameter contains the id of the actor you are looking for. The out parameters return data read from the table.

You can declare SimpleJdbcCall in a manner similar to SimpleJdbcInsert. You must instantiate and configure the class in the initialization method of your data access layer. Compared to the StoredProcedure class, there is no need to subclass and declare parameters that can be searched in database metadata. The following example SimpleJdbcCall configuration uses the previous stored procedure (the only configuration parameter other than DataSource is the name of the stored procedure):

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        this.procReadActor = new SimpleJdbcCall(dataSource)
                .withProcedureName("read_actor");
    }
    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val procReadActor = SimpleJdbcCall(dataSource)
            .withProcedureName("read_actor")
    fun readActor(id: Long): Actor {
        val source = MapSqlParameterSource().addValue("in_id", id)
        val output = procReadActor.execute(source)
        return Actor(
                id,
                output["out_first_name"] as String,
                output["out_last_name"] as String,
                output["out_birth_date"] as Date)
    }
        // ... additional methods
}

The code you write to make the call involves creating a SqlParameterSource containing the IN parameter. You need to match the name specified for the input value with the name of the parameter declared in the stored procedure. The case does not have to match because you use metadata to determine how the database objects should be referenced in the stored procedure. What is specified in the source for a stored procedure is not necessarily the same as how it is stored in the database. Some databases convert names to all uppercase, while others use lowercase or use a specified case.

The execute method takes IN parameters and returns a Map containing any out parameters, the key ones being the names specified in the stored procedure. In this case, these are out_first_name, out_last_name and out_birth_date.

The last part of the execute method creates an Actor instance that will be used to return the received data. Again, it is important to use the names of the out parameters as they are declared in the stored procedure. Additionally, the case of the out parameter names stored in the resulting Map is the same as the case of the out parameter names in the database, which may differ between databases. To make the code more platform independent, you can do case-insensitive searches or tell Spring to use LinkedCaseInsensitiveMap. To accomplish the latter, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. You can then pass this customized JdbcTemplate instance into the constructor of your SimpleJdbcCall. The following example shows this configuration:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor");
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private var procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }).withProcedureName("read_actor")
    // ... additional methods
}

By doing this, you will avoid conflicts in the case used for the names of your return parameters.

Explicitly declaring parameters for use in SimpleJdbcCall

Earlier in this chapter we described how parameters are inferred from metadata, but you can declare them explicitly if you want. You can do this by creating and configuring a SimpleJdbcCall using the declareParameters method, which takes a variable number of SqlParameter objects as input. For more information on how to define SqlParameter, see next section.

You can explicitly declare one, more, or all parameters. Parameter metadata is still used where parameters are not explicitly declared. To bypass all metadata lookup processing for potential parameters and use only the declared parameters, you can call the withoutProcedureColumnMetaDataAccess method as part of the declaration. Suppose two or more different call signatures are declared for a database function. In this case, useInParameterNames is called to specify a list of IN parameter names to include for a given signature.

The following example shows a fully declared procedure call and uses information from the previous example:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadActor;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadActor = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        new SqlParameter("in_id", Types.NUMERIC),
                        new SqlOutParameter("out_first_name", Types.VARCHAR),
                        new SqlOutParameter("out_last_name", Types.VARCHAR),
                        new SqlOutParameter("out_birth_date", Types.DATE)
                );
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
        private val procReadActor = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_actor")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("in_id")
                .declareParameters(
                        SqlParameter("in_id", Types.NUMERIC),
                        SqlOutParameter("out_first_name", Types.VARCHAR),
                        SqlOutParameter("out_last_name", Types.VARCHAR),
                        SqlOutParameter("out_birth_date", Types.DATE)
    )
        // ... additional methods
}

The execution and end results are the same in these two examples. In the second example, all the details are specified explicitly, rather than using metadata.

Ways to define SqlParameters

To define a parameter for classes SimpleJdbc, as well as for RDBMS operation classes (discussed in "Modeling JDBC operations as Java objects ") you can use SqlParameter or one of its subclasses. To do this, you usually specify the parameter name and SQL type in the constructor. The SQL type is specified using the java.sql.Types constants. Earlier in this chapter, we saw declarations similar to the following:

new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),

SqlParameter("in_id", Types.NUMERIC),
SqlOutParameter("out_first_name", Types.VARCHAR),

The first line with SqlParameter declares the IN parameter. You can use IN parameters both for calls to stored procedures and for queries using SqlQuery and its subclasses (discussed in the section "Introduction to SqlQuery").

The second line (with SqlOutParameter) declares out parameter to use in the stored procedure call. There is also a SqlInOutParameter for InOut parameters (parameters that provide a procedure with an IN value and that also return a value).

For IN parameters, In addition to the name and SQL type, you can specify set for numeric data or a type name for custom created database types. For out parameters, you can specify a RowMapper to handle the display of rows returned from the REF cursor. Another option is to specify a SqlReturnType, which allows you to define custom handling of return values.

Calling a stored function with SimpleJdbcCall

Call You can define a stored function in much the same way as you can define a stored procedure, except that you specify the name of the function rather than the name of the procedure. The withFunctionName method is used as part of the configuration to indicate that a function call needs to be made, and then the appropriate string to call the function is generated. To execute a function, a specialized call (executeFunction) is used, which returns the function's return value as an object of the specified type, which means that it is not necessary to obtain the return value from the resulting Map. A similar helper method (named executeObject) is also available for stored procedures that have only one out parameter. The following example (for MySQL) is based on the get_actor_name stored function, which returns the full name of the actor:

CREATE FUNCTION get_actor_name (in_id INTEGER)
RETURNS VARCHAR(200) READS SQL DATA
BEGIN
    DECLARE out_name VARCHAR(200);
    SELECT concat(first_name, ' ', last_name)
        INTO out_name
        FROM t_actor where id = in_id;
    RETURN out_name;
END;

To call this function, we again create a SimpleJdbcCall in the initialization method, as shown in the following example:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall funcGetActorName;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.funcGetActorName = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName("get_actor_name");
    }
    public String getActorName(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        String name = funcGetActorName.executeFunction(String.class, in);
        return name;
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
    private val jdbcTemplate = JdbcTemplate(dataSource).apply {
        isResultsMapCaseInsensitive = true
    }
    private val funcGetActorName = SimpleJdbcCall(jdbcTemplate)
            .withFunctionName("get_actor_name")
    fun getActorName(id: Long): String {
        val source = MapSqlParameterSource().addValue("in_id", id)
        return funcGetActorName.executeFunction(String::class.java, source)
    }
    // ... additional methods
}

Method used executeFunction returns a String containing the return value from the function call.

Return a ResultSet or REF cursor from a SimpleJdbcCall object

Calling a stored procedure or function that returns a result set is a bit of a confusing task. Some databases return result sets during JDBC result processing, while others require an explicitly registered out parameter of a specific type. Both approaches require additional processing to iterate over the result set and process the returned rows. With SimpleJdbcCall you can use the returningResultSet method and declare a RowMapper implementation that will be used for a given parameter. If a result set is returned while processing the results, then the names will not be defined, so the results returned must match the order in which the RowMapper implementations are declared. The specified name is still used to store the processed list of results in the resulting Map that is returned from the execute statement.

The following example (for MySQL) uses a stored procedure that does not accept any IN parameters and returns all rows from the t_actor table:

CREATE PROCEDURE read_all_actors()
BEGIN
 SELECT a.id, a.first_name, a.last_name, a.birth_date FROM t_actor a;
END;

To call this procedure, you can declare a RowMapper. Since the class you want to map to follows the JavaBean rules, you can use BeanPropertyRowMapper, which is created by passing the required class to map to the newInstance method. The following example shows how to do this:

public class JdbcActorDao implements ActorDao {
    private SimpleJdbcCall procReadAllActors;
    public void setDataSource(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setResultsMapCaseInsensitive(true);
        this.procReadAllActors = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("read_all_actors")
                .returningResultSet("actors",
                BeanPropertyRowMapper.newInstance(Actor.class));
    }
    public List getActorsList() {
        Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
        return (List) m.get("actors");
    }
    // ... additional methods
}

class JdbcActorDao(dataSource: DataSource) : ActorDao {
        private val procReadAllActors = SimpleJdbcCall(JdbcTemplate(dataSource).apply {
            isResultsMapCaseInsensitive = true
        }).withProcedureName("read_all_actors")
                .returningResultSet("actors",
                        BeanPropertyRowMapper.newInstance(Actor::class.java))
    fun getActorsList(): List<Actor> {
        val m = procReadAllActors.execute(mapOf<String, Any>())
        return m["actors"] as List<Actor>
    }
    // ... additional methods
}

Calling execute passes an empty Map because this the call does not take any parameters. The list of actors is then retrieved from the resulting Map and returned to the calling code.