3.5 JDBC Batch Operations
Most JDBC drivers will improve performance if multiple calls are batched into the same prepared statement. By grouping updates into batches, you can limit the number of round trips to the database.
3.5.3 Basic batch operations using JdbcTemplate
By implementing a special the BatchPreparedStatementSetter two methods and the implementation of the interface as the second parameter passed in batchUpdate method calls, can complete the JdbcTemplate batch. You can use the getBatchSize method to provide the current batch size. You can set the statement’s parameter values using the setValues method. This method is called the number of times you specify in the getBatchSize call. The following example updates the T_actor table based on the entries in the list and uses the entire list as a batch:
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int[] batchUpdate(final List<Actor> actors) {
return this.jdbcTemplate.batchUpdate(
"update t_actor set first_name = ? , last_name = ? where id = ?".new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
Actor actor = actors.get(i);
ps.setString(1, actor.getFirstName());
ps.setString(2, actor.getLastName());
ps.setLong(3, actor.getId().longValue());
}
public int getBatchSize(a) {
returnactors.size(); }}); }// ... additional methods
}
Copy the codeIf you process an update stream or read from a file, you may have the preferred batch size, but the last batch may not have that many entries. In this case, you can use InterruptibleBatchPreparedStatementSetter interface, this interface can be in the input source interrupt batch after running out (unused) the translator: it means the data source data. The isBatchExhausted method allows you to signal the end of a batch.
3.5.2 List of objects for batch operations
JdbcTemplate and NamedParameterJdbcTemplate provide batch update provides another way. Instead of implementing a special batch interface, all the parameter values in the call are provided as a list. The framework loops these values and uses an internal statement setter. The API will be different, depending on whether you use named parameters or not. For named parameters, you provide an SqlParameterSource array with one entry for each member of the batch. . You can use SqlParameterSourceUtils createBatch convenient method to create the array, to a bean style array of objects (with parameters corresponding getter method), the string key Map instance (contain the corresponding parameters as values), or mixed.
The following example shows batch updates with named parameters:
public class JdbcActorDao implements ActorDao {
private NamedParameterTemplate namedParameterJdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}
public int[] batchUpdate(List<Actor> actors) {
return this.namedParameterJdbcTemplate.batchUpdate(
"update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
SqlParameterSourceUtils.createBatch(actors));
}
// ... additional methods
}
Copy the codeFor using classic SQL statements? Placeholder, passing in an array of objects containing the updated values. The array of objects must have one entry for each placeholder in the SQL statement, and they must be in the same order as defined in the SQL statement.
The following example is the same as the previous one, except that it uses classic JDBC? Placeholder:
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int[] batchUpdate(final List<Actor> actors) {
List<Object[]> batch = new ArrayList<Object[]>();
for (Actor actor : actors) {
Object[] values = new Object[] {
actor.getFirstName(), actor.getLastName(), actor.getId()};
batch.add(values);
}
return this.jdbcTemplate.batchUpdate(
"update t_actor set first_name = ? , last_name = ? where id = ?",
batch);
}
// ... additional methods
}
Copy the codeAll of the batch update methods we described earlier return an int array containing the number of affected rows for each batch entry. This count is reported by the JDBC driver. If the count is not available, the JDBC driver returns the value -2.
In this case, the corresponding JDBC type for each value needs to be derived from the given Java type by automatically setting the value on the underlying PreparedStatement. Although this usually works well, there are potential problems (for example, null values containing maps). In this case, the Spring by default will invoke the ParameterMetaData. GetParameterType, this regarding the JDBC driver can be very expensive. If there are any performance issues, should use the latest driver version, and consider spring. JDBC. GetParameterType. Ignore the attribute set to true (as a JVM system property, or in the class path to the root directory of the spring. The properties file). For example, Oracle 12C (SPR-16139) was reported.
Or, you can consider through BatchPreparedStatementSetter (as before), through based on “the List < Object > [] calls provide explicit type array, The corresponding JDBC type is explicitly specified by a “registerSqlType call” on the server. Custom “MapSqlParameterSource instance, or through an example from a Java statement BeanPropertySqlParameterSource attribute types in SQL type, even for a null value.
3.5.3 Batch operation with multiple batches
The previous batch update example dealt with batches that were so large that you wanted to break them up into smaller batches. You can do this using the previously mentioned method by calling the batchUpdate method multiple times, but there is a more convenient way. In addition to SQL statements, this approach also contains a collection of objects, the object contains parameter, number of each batch to be updated and a ParameterizedPreparedStatementSetter to set the parameter values of the prepared statement. The framework iterates over the supplied values and divides the update call into batches of the specified size.
The following example shows batch updates using the batch size of 100:
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public int[][] batchUpdate(final Collection<Actor> actors) {
int[][] updateCounts = jdbcTemplate.batchUpdate(
"update t_actor set first_name = ? , last_name = ? where id = ?",
actors,
100,
(PreparedStatement ps, Actor actor) -> {
ps.setString(1, actor.getFirstName());
ps.setString(2, actor.getLastName());
ps.setLong(3, actor.getId().longValue());
});
return updateCounts;
}
// ... additional methods
}
Copy the codeThe batch update method of this call returns an int array containing the array entries for each batch and the number of rows affected by each update. The length of the top array indicates the number of batches running, and the length of the second layer of resin indicates the number of updates in that batch. The number of updates in each batch should be the batch size provided for all batches (the last one may be less), depending on the total number of update objects provided. The update count for each update statement is the update count reported by the JDBC driver. If the count is not available, the JDBC driver returns the value -2.
3.6 Using SimpleJdbc class to simplify JDBC operations
The SimpleJdbcInsert and SimpleJdbcCall classes provide simplified configuration by leveraging database metadata that can be retrieved through the JDBC driver. This means you can do less upfront configuration, but you can override or turn off metadata processing if you’re willing to provide all the details in your code.
3.6.1 Insert data using SimpleJdbcInsert
Let’s start by looking at the SimpleJdbcInsert class with the fewest configuration options. You should instantiate SimpleJdbcInsert in the initialization method of the data access layer. For this example, the initialization method is the setDataSource method. You don’t 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 of this class follow the fluid style, which returns an instance of SimpleJdbcInsert that lets you link all the configuration methods. The following example uses only one configuration method (we’ll show examples of multiple methods 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
}
Copy the codeThe execute method used here takes pure java.util.map as its only argument. The important thing to note here is that the keys used for the Map must match the column names of the tables defined in the database. This is because we read the metadata to construct the actual INSERT statement.
3.6.2 Retrieve the automatically generated primary key using SimpleJdbcInsert
The next example uses the same insert as the previous example, but instead of passing the ID, it retrieves the automatically generated key and sets it on the new Actor object. When SimpleJdbcInsert was created, in addition to specifying the table name, it used the usingGeneratedKeyColumns method to specify the name of the generated key column.
The following listing shows how it works:
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
}
Copy the codeThe main difference when you run inserts using the second method is that instead of adding the ID to the Map, you call the executeAndReturnKey method. This returns a java.lang.Number object that you can use to create instances of numeric types used in your domain classes. You can’t rely on all databases to return specific Java classes here. Java.lang. Number is the base class you can rely on. If you have multiple automatically generated columns, or generated values are Numbers, you can use from executeAndReturnKeyHolder KeyHolder method returns.
3.6.3 Specify columns for SimpleJdbcInsert
You can use the usingColumns method to specify a list of column names to limit the number of inserted columns, 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
}
Copy the codeThe execution of inserts is the same as that of relying on metadata to determine which columns to use.
3.6.4 Use SqlParameterSource to provide parameter values
Supplying parameter values with Map works fine, but it’s not the easiest class to use. Spring provides some implementations of the SqlParameterSource interface that you can use instead. The first is BeanPropertySqlParameterSource, if you have a compatible JavaBean class contains values, this is a very handy class. It extracts parameter values using the corresponding getter methods. The following example demonstrates 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
}
Copy the codeAnother option is MapSqlParameterSource, which is similar to Map but provides a more convenient addValue method that can be called chained. 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
}
Copy the codeAs you can see, the configuration is the same. Only executing code can change to use these alternative input classes.
3.6.5 Calling a Stored Procedure with SimpleJdbcCall
The SimpleJdbcCall class uses metadata in the database to look up the names of in and out parameters, so you don’t have to declare them explicitly. You can declare parameters, if you like, or parameters (such as ARRAY or STRUCT) that are not automatically mapped to Java classes. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from the MySQL database. The stored procedure example reads the specified participant entry and returns first_name, last_name, and birth_date columns as out parameters. The following listing shows the first 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;
Copy the codeThe in_id parameter contains the ID of the actor you are looking for. The OUT parameter returns data read from the table.
You can declare SimpleJdbcCall the same way you declare SimpleJdbcInsert. You should instantiate and configure this class in the initialization method of the data access layer. In contrast to the StoredProcedure class, you do not have to create subclasses or declare parameters that can be looked up in the database metadata.
The following SimpleJdbcCall configuration example uses the previous stored procedure (except for the DataSource, the only configuration option 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
}
Copy the codeThe code you write to perform the call involves creating an SqlParameterSource with the IN parameter. You must provide a name for the input value that matches the name of the parameter name declared in the stored procedure. Case and case don’t have to match because you use metadata to determine how database objects should be referenced in stored procedures. What the source specifies for the stored procedure is not necessarily how the stored procedure is stored in the database. Some databases convert names to all uppercase, while others use lowercase or specified case.
The execute method takes the IN parameter and returns a Map of all the out parameters typed by the names specified IN the stored procedure. In the current instance, these are out_first_name, out_last_name, and out_birth_date.
The last part of the execute method creates an Actor instance that is used to return the retrieved data. Again, it is important to use the names of the out parameters because they are declared in the stored procedure. Again, the case of the OUT parameter name stored in the result mapping table matches the case of the OUT parameter name in the database, which may vary from database to database. To make your code more portable, you should perform a case-insensitive lookup or instruct Spring to use LinkedCase Sensitivemap. To do this, you can create your own JdbcTemplate and setResultsMapCaseInsensitive attribute set to true. You can then pass this custom JdbcTemplate instance into the Constructor of 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
}
Copy the codeBy doing this, you can avoid collisions in cases where the parameter name is used to return.
3.6.6 Explicitly declare the parameters to be used for SimpleJdbcCall
Earlier in this chapter, we described how to derive parameters from metadata, but you can declare them explicitly if you need to. You can do this by creating and configuring SimpleJdbcCall using the defineParameters method, which takes a variable number of SqlParameter objects as input. For more information on how to define SqlParameter, see the next section.
If you are using a database that is not spring-supported, you must declare it explicitly. Currently, Spring supports metadata lookup for stored procedure calls against the following databases: Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase. We also support metadata lookups for MySQL, Microsoft SQL Server, and Oracle storage methods.
You can choose to explicitly declare one, some, or all of the arguments. Where parameters are not explicitly declared, parameter metadata is still used. To bypass the metadata for all of the processing of potential parameters and use only the declared parameters, without ProcedureColumnMetaDataAccess method can be as part of the statement to invoke. Suppose you declare two or more different call signatures for a database function. IN this case, you call useInParameterNames to specify the list of IN parameter names to be included IN the given signature.
The following example shows a fully declared procedure call, using 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
}
Copy the codeThe execution and end result of both examples are the same. The second example explicitly specifies all the details, rather than relying on metadata.
3.6.7 How do I define SqlParameters
To define parameters for the SimpleJdbc class and the RDBMS operation class (described in Java objects as JDBC operation models), SqlParameter or one of its subclasses can be used. To do this, you usually specify parameter names and SQL types in the constructor. Specify the SQL type by using the java.sqL. Types constant. Earlier in this chapter, we saw declarations like the following:
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
Copy the codeThe first line with SqlParameter declares an IN parameter. By using SqlQuery and its subclasses, which can be found IN Understanding SqlQuery, you can use the IN parameter for stored procedure calls and queries.
The second line (with SqlOutParameter) declares the OUT parameter to be used in the stored procedure call. There is also an SqlInOutParameter (the parameter that provides the IN value to the procedure and returns the value) for the InOut parameter.
Only parameters declared as SqlParameter and SqlInOutParameter are used to provide input values. This is different from the StoredProcedure class, which (for backward compatibility reasons) allows input values for parameters declared as SQLOutParameters.
For the IN parameter, IN addition to the name and SQL type, you can specify a decimal place for numeric data or a type name for a custom database type. For the OUT parameter, a RowMapper can be provided to handle the mapping of rows returned from the REF cursor. Another option is to specify an SqlReturnType, which provides an opportunity to define custom processing of the return value.
3.6.8 Calling the storage function by using SimpleJdbcCall
A stored function can be called in much the same way as a stored procedure, except that the function name is provided instead of the procedure name. You use the withFunctionName method as part of the configuration to indicate that you want to call the function and generate the corresponding string for the function call. A special call (executeFunction) is used to run the function, which returns the function’s return value as an object of the specified type, meaning you don’t have to retrieve the return value from the resulting Map. A similar convenience method (called executeObject) can be used for stored procedures that have only one out parameter. The following example (for MySQL) is based on a storage function called get_actor_name that returns the full name of the participant:
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;
Copy the codeTo call this function, we again create a SimpleJdbcCall in the initialization method, as shown in the following example:
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall funcGetActorName;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
Copy the codeThe executeFunction method used returns a String containing the return value of the function call.
3.6.9 Return a ResultSet or REF cursor from SimpleJdbcCall
The SimpleJdbcInsert and SimpleJdbcCall classes provide simplified configuration by leveraging database metadata that can be retrieved through the JDBC driver. This means you can do less upfront configuration, but you can override or turn off metadata processing if you’re willing to provide all the details in your code.
3.6.1 EnablingSimpleJdbcInsertInsert data
Let’s start by looking at the SimpleJdbcInsert class with the fewest configuration options. You should instantiate SimpleJdbcInsert in the initialization method of the data access layer. For this example, the initialization method is the setDataSource method. You don’t 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 of this class follow the fluid style, which returns an instance of SimpleJdbcInsert that lets you link all the configuration methods. The following example uses only one configuration method (we’ll show examples of multiple methods 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
}
Copy the codeThe execute method used here takes pure java.util.map as its only argument. The important thing to note here is that the keys used for the Map must match the column names of the tables defined in the database. This is because we read the metadata to construct the actual INSERT statement.
3.6.2 EnablingSimpleJdbcInsertRetrieve automatically generated primary keys
The next example uses the same insert as the previous example, but instead of passing the ID, it retrieves the automatically generated key and sets it on the new Actor object. When SimpleJdbcInsert was created, in addition to specifying the table name, it used the usingGeneratedKeyColumns method to specify the name of the generated key column. The following listing shows how it works:
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
}
Copy the codeThe main difference when you run inserts using the second method is that instead of adding the ID to the Map, you call the executeAndReturnKey method. This returns a java.lang.Number object that you can use to create instances of numeric types used in your domain classes. You can’t rely on all databases to return specific Java classes here. You can rely on this basic java.lang.Number type. If you have multiple automatically generated columns, or generated values are Numbers, you can use from executeAndReturnKeyHolder KeyHolder method returns.
3.6.3 forSimpleJdbcInsertThe specified column
You can use the usingColumns method to specify a list of column names to limit the number of inserted columns, 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
}
Copy the codeThe execution of inserts is the same as that of relying on metadata to determine which columns to use.
3.6.4 radar echoes captured usingSqlParameterSourceProvide parameter values
Supplying parameter values with Map works fine, but it’s not the easiest class to use. Spring provides some implementations of the SqlParameterSource interface that you can use instead. The first is BeanPropertySqlParameterSource, if you have a compatible JavaBean class contains values, this is a very handy class. It extracts parameter values using the corresponding getter methods. The following example demonstrates 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
}
Copy the codeAnother option is MapSqlParameterSource, which is similar to Map but provides a more convenient addValue method that can be called chained. 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
}
Copy the codeAs you can see, the configuration is the same. Only executing code can change to use these alternative input classes.
3.6.5 throughSimpleJdbcCallCalling a stored procedure
The SimpleJdbcCall class uses metadata in the database to look up the names of in and out parameters, so you don’t have to declare them explicitly. You can declare parameters, if you like, or parameters (such as ARRAY or STRUCT) that are not automatically mapped to Java classes. The first example shows a simple procedure that returns only scalar values in VARCHAR and DATE format from the MySQL database. The sample stored procedure reads the specified actor entry and returns first_name, last_name, and birth_date columns as out parameters. The following listing shows the first 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;
Copy the codeThe in_id parameter contains the ID of the actor you are looking for. The OUT parameter returns data read from the table.
You can declare SimpleJdbcCall the same way you declare SimpleJdbcInsert. You should instantiate and configure this class in the initialization method of the data access layer. In contrast to the StoredProcedure class, you do not have to create subclasses or declare parameters that can be looked up in the database metadata. The following SimpleJdbcCall configuration example uses the previous stored procedure (except for the DataSource, the only configuration option 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
}
Copy the codeThe code you write to perform the call involves creating an SqlParameterSource with the IN parameter. You must provide a name for the input value that matches the name of the parameter name declared in the stored procedure. Case and case don’t have to match because you use metadata to determine how database objects should be referenced in stored procedures. What the source specifies for the stored procedure is not necessarily how the stored procedure is stored in the database. Some databases convert names to all uppercase, while others use lowercase or specified case.
The execute method takes the IN parameter and returns a Map of all the out parameters typed by the names specified IN the stored procedure. In the current instance, these are out_first_name, out_last_name, and out_birth_date.
The last part of the execute method creates an Actor instance that is used to return the retrieved data. Again, it is important to use the names of the out parameters because they are declared in the stored procedure. Again, the case of the OUT parameter name stored in the result mapping table matches the case of the OUT parameter name in the database, which may vary from database to database. To make your code more portable, you should perform a case-insensitive lookup or instruct Spring to use LinkedCase Sensitivemap. To do this, you can create your own JdbcTemplate and setResultsMapCaseInsensitive attribute set to true. You can then pass this custom JdbcTemplate instance into the Constructor of 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
}
Copy the codeBy doing this, you can avoid collisions in cases where the parameter name is used to return.
3.6.6 Explicitly declare the parameters to be used for SimpleJdbcCall
Earlier in this chapter, we described how to derive parameters from metadata, but you can declare them explicitly if you need to. You can do this by creating and configuring SimpleJdbcCall using the defineParameters method, which takes a variable number of SqlParameter objects as input. For more information on how to define SqlParameter, see the next section.
If you are using a database that is not spring-supported, you must declare it explicitly. Currently, Spring supports metadata lookup for stored procedure calls against the following databases: Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase. We also support metadata lookup with MySQL, Microsoft SQL Server and Oracle storage capabilities.
You can choose to explicitly declare one, some, or all of the parameters. Where parameters are not explicitly declared, parameter metadata is still used. To bypass the metadata for all of the processing of potential parameters and use only the declared parameters, without ProcedureColumnMetaDataAccess method can be as part of the statement to invoke. Suppose you declare two or more different call signatures for a database function. IN this case, you call useInParameterNames to specify the list of IN parameter names to be included IN the given signature.
The following example shows a fully declared procedure call, using 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
}
Copy the codeThe execution and end result of both examples are the same. The second example explicitly specifies all the details, rather than relying on metadata.
3.6.7 How to define itSqlParameters
To define parameters for both the SimpleJdbc class and the RDBMS operation class (found in JDBC operations modeled as Java objects), SqlParameter or one of its subclasses can be used. To do this, you typically specify parameter names and SQL types in the constructor. Specify the SQL type by using the java.sqL. Types constant. Earlier in this chapter, we saw declarations like the following:
new SqlParameter("in_id", Types.NUMERIC),
new SqlOutParameter("out_first_name", Types.VARCHAR),
Copy the codeThe first line with SqlParameter declares an IN parameter. By using SqlQuery and its subclasses, which can be found IN Understanding SqlQuery, you can use the IN parameter for stored procedure calls and queries.
The second line (with SqlOutParameter) declares the OUT parameter to be used in the stored procedure call. There is also an SqlInOutParameter (the parameter that provides the IN value to the procedure and returns the value) for the InOut parameter.
Only parameters declared as SqlParameter and SqlInOutParameter are used to provide input values. This is different from the StoredProcedure class, which (for backward compatibility reasons) allows input values for parameters declared as SQLOutParameters.
For the IN parameter, IN addition to the name and SQL type, you can specify a decimal place for numeric data or a type name for a custom database type. For the OUT parameter, a RowMapper can be provided to handle the mapping of rows returned from the REF cursor. Another option is to specify an SqlReturnType, which provides an opportunity to define custom processing of the return value.
3.6.8 Call the stored function using SimpleJdbcCall
A stored function can be called in much the same way as a stored procedure, except by providing the function name instead of the stored procedure name. You use the withFunctionName method as part of the configuration to indicate that you want to call the function and generate the corresponding string for the function call. A special call (executeFunction) is used to run the function, which returns the function’s return value as an object of the specified type, meaning you don’t have to retrieve the return value from the resulting Map. A similar convenience method (called executeObject) can be used for stored procedures that have only one out parameter. The following example (for MySQL) is based on a storage function called get_actor_name that returns the full name of 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;
Copy the codeTo call this function, we again create a SimpleJdbcCall in the initialization method, as shown in the following example:
public class JdbcActorDao implements ActorDao {
private JdbcTemplate jdbcTemplate;
private SimpleJdbcCall funcGetActorName;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(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
}
Copy the codeThe executeFunction method used returns a String containing the return value of the function call.
3.6.9 Return a ResultSet or REF cursor from SimpleJdbcCall
Calling a stored procedure or function that returns a result set is a bit tricky. Some databases return result sets during JDBC result processing, while others require specific types of parameters that are explicitly registered. Both methods require additional processing to traverse the result set and process the returned rows. With SimpleJdbcCall, you can use the returningResultSet method and declare the RowMapper implementation to be used for a particular parameter. If a result set is returned in the result stored procedure, no name is defined, so the returned results must match the order in which the RowMapper implementation was declared. The name specified is still used to store the list of processed results in the result Map returned by the EXECUTE statement.
The next example (for MySQL) uses a stored procedure that takes no IN argument and returns all rows IN 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;
Copy the codeTo invoke this stored procedure, you can declare a RowMapper. Because the class to be mapped follows JavaBean rules, you can use the BeanPropertyRowMapper, which is created by passing in the necessary classes to be mapped in 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(a) {
Map m = procReadAllActors.execute(new HashMap<String, Object>(0));
return (List) m.get("actors");
}
// ... additional methods
}
Copy the codeThe execute call passes an empty Map because it takes no arguments. The list of actors is then retrieved from the resulting Map and returned to the caller.
The author
Personally engaged in the financial industry, I have worked in chongqing’s first-class technical team of Yiji Pay, Sijian Technology and an online car hailing platform, and now I am working in a bank responsible for the construction of unified payment system. I have a strong interest in the financial industry. It also practices big data, data storage, automated integration and deployment, distributed microservices, responsive programming, and artificial intelligence. At the same time, he is also keen on technology sharing, creating public accounts and blog sites to share knowledge system. Concern public number: young IT male get latest technical article push!
Blog: Youngitman.tech
CSDN: blog.csdn.net/liyong10288…
Wechat Official Account: 
Technical Exchange Group: 
This article is published by OpenWrite, a blogging tool platform