Shulou(Shulou.com)06/01 Report--

This article introduces the relevant knowledge of "what is the method of batch operation of JDBC". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

JDBC batch operation

If you batch multiple calls to the same prepared statement, most JDBC drivers will improve performance. By grouping updates into batches, you can limit the number of round trips to the database.

3.5.3 basic batch operations using JdbcTemplate

You can complete the batchUpdate batch by implementing two methods of the special interface, BatchPreparedStatementSetter, and passing that implementation as the second parameter in the JdbcTemplate method call. You can use the getBatchSize method to provide the size of the current batch. You can use the setValues method to set the parameter values of the statement. 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 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 () {return actors.size ();}});} / /. Additional methods}

If you are processing an update stream or reading from a file, you may have the preferred batch size, but the last batch may not have that number of entries. In this case, you can use the InterruptibleBatchPreparedStatementSetter interface, which interrupts batch processing after the input source is exhausted. The isBatchExhausted method allows you to signal the end of the batch.

3.5.2 list of objects for batch operations

Both JdbcTemplate and NamedParameterJdbcTemplate provide another way to provide batch updates. Instead of implementing a special batch interface, all parameter values in the call are provided as a list. The framework loops through these values and uses an internal statement setter. API will be different, depending on whether you use named parameters. For named parameters, you provide an array of SqlParameterSource, and each member of the batch has an entry. You can use the SqlParameterSourceUtils.createBatch convenience method to create this array, passing in an array of bean-style objects (with a getter method corresponding to the parameter), a string key Map instance (containing the corresponding parameter as a value), or a mix.

The following example shows batch updates using named parameters:

Public class JdbcActorDao implements ActorDao {private NamedParameterTemplate namedParameterJdbcTemplate; public void setDataSource (DataSource dataSource) {this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate (dataSource);} public int [] batchUpdate (List actors) {return this.namedParameterJdbcTemplate.batchUpdate ("update t_actor set first_name =: firstName, last_name =: lastName where id =: id", SqlParameterSourceUtils.createBatch (actors);} / /. Additional methods}

For using classic SQL statements? Placeholder, pass in a list of objects that contain the updated values. Each placeholder of the object array in the SQL statement must have an entry, and they must be in the same order as defined in the SQL statement.

The following example is the same as the previous example, except that it uses the 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 actors) {List batch = new ArrayList (); 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}

All 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 a value of-2.

In this case, by automatically setting the value on the underlying PreparedStatement, you need to derive the corresponding JDBC type of each value from the given Java type. Although this usually works well, there are potential problems (for example, null values containing Map). In this case, Spring calls ParameterMetaData.getParameterType by default, which can be expensive for JDBC drivers. If you encounter performance problems, you should use the latest version of the driver and consider setting the spring.jdbc.getParameterType.ignore property to true (as a JVM system property or in the spring.properties file in the classpath root). Such as the report on Oracle 12c (SPR-16139).

Alternatively, you can consider explicitly specifying the corresponding JDBC type through BatchPreparedStatementSetter (as shown earlier), through an array of explicit types provided for "List-based calls, and through" registerSqlType calls on the server. Customize the MapSqlParameterSource instance, or get the SQL type from the property type declared by Java through the BeanPropertySqlParameterSource instance, even for null values.

3.5.3 batch operations with multiple batches

The batches of the previous batch update example are so large that you want to break them down into several smaller batches. You can use the previously mentioned method to do this by calling the batchUpdate method multiple times, but now there is a more convenient way. In addition to the SQL statement, this method includes a collection of objects that contain parameters, the number of updates to be made per batch, and a ParameterizedPreparedStatementSetter to set the parameter values for the prepared statement. The framework iterates through the values provided and divides the update call into batches of the specified size.

The following example shows a batch update with a 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 actors) {int [] [] updateCounts = jdbcTemplate.batchUpdate ("update t_actor set first_name =?, last_name =? Where id =? ", actors, 100,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}

The batch update method of this call returns an int array containing array entries for each batch and an array 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 the 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 a value of-2.

3.6 simplify JDBC operations using the SimpleJdbc class

The SimpleJdbcInsert and SimpleJdbcCall classes provide simplified configuration by leveraging database metadata that can be retrieved through the JDBC driver. This means that you can do less upfront configuration, but if you are willing to provide all the details in the code, you can override or turn off metadata processing.

3.6.1 insert data using SimpleJdbcInsert

Let's first look at the SimpleJdbcInsert class with minimal 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 method of this class follows the style of fluid, which returns an instance of SimpleJdbcInsert, which allows you to link to all configuration methods. The following example uses only one configuration method (we will 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 parameters = new HashMap (3); parameters.put ("id", actor.getId ()); parameters.put ("first_name", actor.getFirstName ()) Parameters.put ("last_name", actor.getLastName ()); insertActor.execute (parameters);} / /. Additional methods}

The execute method used here takes pure java.util.Map as its only parameter. An important point to note here is that the key for Map must match the column name of the table defined in the database. This is because we read the metadata to construct the actual insert statement.

3.6.2 retrieve automatically generated primary keys by using SimpleJdbcInsert

The next example uses the same insert as the previous example, but instead of passing id, it retrieves the automatically generated key and sets it on the new Actor object. When creating a SimpleJdbcInsert, in addition to specifying the table name, it also uses 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 parameters = new HashMap (2); parameters.put ("first_name", actor.getFirstName ()) Parameters.put ("last_name", actor.getLastName ()); Number newId = insertActor.executeAndReturnKey (parameters); actor.setId (newId.longValue ());} / /. Additional methods}

The main difference when running the insert using the second method is that instead of adding ID to the Map, you call the executeAndReturnKey method. This returns a java.lang.Number object that you can use to create an instance of the numeric type used in the domain class. You can't rely on all databases to return specific Java classes here. Java.lang.Number is the basic class you can rely on. If you have multiple automatically generated columns, or if the generated values are non-numeric, you can use the KeyHolder returned from the executeAndReturnKeyHolder method.

3.6.3 specify a column for SimpleJdbcInsert

You can use the usingColumns method to specify a list of column names to restrict which columns are inserted, 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 parameters = new HashMap (2) Parameters.put ("first_name", actor.getFirstName ()); parameters.put ("last_name", actor.getLastName ()); Number newId = insertActor.executeAndReturnKey (parameters); actor.setId (newId.longValue ());} / /. Additional methods}

The execution of the insert is the same as relying on metadata to determine which column to use.

3.6.4 use SqlParameterSource to provide parameter values

Using Map to provide parameter values works fine, but this is not the most convenient class to use. Spring provides some implementations of the SqlParameterSource interface that you can use instead. The first is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compatible class that contains values. It uses the corresponding getter method to extract 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}

Another option is MapSqlParameterSource, which is similar to Map but provides a more convenient addValue method that can be 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}

As you can see, the configuration is the same. Only by executing the code can you change to use these alternative input classes.

3.6.5 calling stored procedures using SimpleJdbcCall

The SimpleJdbcCall class uses metadata from the database to find the names of in and out parameters, so you don't have to declare them explicitly. If you prefer, you can declare parameters, or you can declare parameters that are not automatically mapped to the Java class (such as ARRAY or STRUCT). The first example shows a simple process that returns only scalar values in VARCHAR and DATE format from the MySQL database. This stored procedure example reads the specified participant entry and returns the first_name,last_name and birth_date columns as an out parameter. The following listing shows the first example:

CREATE PROCEDURE read_actor (IN in_id INTEGER, OUT out_first_name VARCHAR, OUT out_last_name VARCHAR, 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 participant you are looking for. The out parameter returns the data read from the table.

You can declare SimpleJdbcCall in a manner similar to declaring SimpleJdbcInsert. You should instantiate and configure this class in the initialization method of the data access layer. Compared to the StoredProcedure class, you do not need to create subclasses or declare parameters that can be found in the database metadata.

The following SimpleJdbcCall configuration example uses the previous stored procedure (with the exception of 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}

The code you write to execute the call involves creating a SqlParameterSource that contains the IN parameter. You must provide a name for the input value that matches the name of the parameter declared in the stored procedure. Case does not have to match because you use metadata to determine how database objects should be referenced in stored procedures. What is specified for the stored procedure in the source 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 an IN parameter and returns a Map that contains all out parameters typed by the name specified in the stored procedure. In the current instance, they are out_first_name,out_last_name and out_birth_date.

The last part of the execute method creates an instance of Actor 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. Similarly, the case of out parameter names stored in the result mapping table matches the case of out parameter names 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 LinkedCaseInsensitiveMap. To do this, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. You can then pass this custom JdbcTemplate instance to 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}

By doing this, you can avoid conflicts in cases that are used to return parameter names.

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 necessary. 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 about how to define SqlParameter, see the next section.

If you are using a database that is not supported by Spring, you must explicitly declare it. Currently, Spring supports metadata lookups for stored procedure calls to the following databases: Apache Derby,DB2,MySQL,Microsoft SQL Server,Oracle and Sybase. We also support metadata lookup for MySQL,Microsoft SQL Server and Oracle storage methods.

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

The following example shows a fully declared procedure call and uses the 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}

The 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 SqlParameters

To define parameters for the SimpleJdbc class and the RDBMS operation class (described in the Java object as the JDBC operation model), you can use SqlParameter or one of its subclasses. To do this, you usually specify the parameter name and SQL type in the constructor. Specify the SQL type by using the java.sql.Types constant. Earlier in this chapter, we saw a statement similar to the following:

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

The first line with SqlParameter declares an IN parameter. You can use the IN parameter for stored procedure calls and queries by using SqlQuery and its subclasses, which can be found in understanding SqlQuery.

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

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 you to provide input values for parameters declared as SqlOutParameter.

For IN parameters, you can specify a decimal place for numeric data or a type name for a custom database type, in addition to the name and SQL type. For the out parameter, you can provide a RowMapper to handle the mapping of rows returned from the REF cursor. Another option is to specify a SqlReturnType, which provides an opportunity to define a custom process for the return value.

3.6.8 by calling a stored function using SimpleJdbcCall

You can call a stored function in almost the same way as calling a stored procedure, except that you provide a function name instead of a 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 return value of the function as an object of the specified type, which means that you do not have to retrieve the return value from the resulting Map. A similar convenient method (named executeObject) can be used for stored procedures that have only one out parameter. The following example (for MySQL) is based on a stored function named get_actor_name, which returns the full name of the participant:

CREATE FUNCTION get_actor_name (in_id INTEGER) RETURNS VARCHAR READS SQL DATABEGIN DECLARE out_name VARCHAR; 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 once 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}

The executeFunction method used returns a String containing the return value of the function call.

3.6.9 return ResultSet or REF cursors from SimpleJdbcCall

The SimpleJdbcInsert and SimpleJdbcCall classes provide simplified configuration by leveraging database metadata that can be retrieved through the JDBC driver. This means that you can do less upfront configuration, but if you are willing to provide all the details in the code, you can override or turn off metadata processing.

3.6.1 insert data by using SimpleJdbcInsert

Let's first look at the SimpleJdbcInsert class with minimal 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 method of this class follows the style of fluid, which returns an instance of SimpleJdbcInsert, which allows you to link to all configuration methods. The following example uses only one configuration method (we will 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 parameters = new HashMap (3); parameters.put ("id", actor.getId ()); parameters.put ("first_name", actor.getFirstName ()) Parameters.put ("last_name", actor.getLastName ()); insertActor.execute (parameters);} / /. Additional methods}

The execute method used here takes pure java.util.Map as its only parameter. An important point to note here is that the key for Map must match the column name of the table defined in the database. This is because we read the metadata to construct the actual insert statement.

3.6.2 automatic generation of primary keys by using SimpleJdbcInsert retrieval

The next example uses the same insert as the previous example, but instead of passing id, it retrieves the automatically generated key and sets it on the new Actor object. When creating a SimpleJdbcInsert, in addition to specifying the table name, it also uses 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 parameters = new HashMap (2); parameters.put ("first_name", actor.getFirstName ()) Parameters.put ("last_name", actor.getLastName ()); Number newId = insertActor.executeAndReturnKey (parameters); actor.setId (newId.longValue ());} / /. Additional methods}

The main difference when running the insert using the second method is that instead of adding ID to the Map, you call the executeAndReturnKey method. This returns a java.lang.Number object that you can use to create an instance of the numeric type used in the domain class. 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 if the generated values are non-numeric, you can use the KeyHolder returned from the executeAndReturnKeyHolder method.

3.6.3 specify a column for SimpleJdbcInsert

You can use the usingColumns method to specify a list of column names to restrict which columns are inserted, 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 parameters = new HashMap (2) Parameters.put ("first_name", actor.getFirstName ()); parameters.put ("last_name", actor.getLastName ()); Number newId = insertActor.executeAndReturnKey (parameters); actor.setId (newId.longValue ());} / /. Additional methods}

The execution of the insert is the same as relying on metadata to determine which column to use.

3.6.4 use SqlParameterSource to provide parameter values

Using Map to provide parameter values works fine, but this is not the most convenient class to use. Spring provides some implementations of the SqlParameterSource interface that you can use instead. The first is BeanPropertySqlParameterSource, which is a very convenient class if you have a JavaBean-compatible class that contains values. It uses the corresponding getter method to extract 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}

Another option is MapSqlParameterSource, which is similar to Map but provides a more convenient addValue method that can be 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}

As you can see, the configuration is the same. Only by executing the code can you change to use these alternative input classes.

3.6.5 calling stored procedures through SimpleJdbcCall

The SimpleJdbcCall class uses metadata from the database to find the names of in and out parameters, so you don't have to declare them explicitly. If you prefer, you can declare parameters, or you can declare parameters that are not automatically mapped to the Java class (such as ARRAY or STRUCT). The first example shows a simple process 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 the first_name,last_name and birth_date columns as an out parameter. The following listing shows the first example:

CREATE PROCEDURE read_actor (IN in_id INTEGER, OUT out_first_name VARCHAR, OUT out_last_name VARCHAR, 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 parameter returns the data read from the table.

You can declare SimpleJdbcCall in a manner similar to declaring SimpleJdbcInsert. You should instantiate and configure this class in the initialization method of the data access layer. Compared to the StoredProcedure class, you do not need to create subclasses or declare parameters that can be found in the database metadata. The following SimpleJdbcCall configuration example uses the previous stored procedure (with the exception of 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}

The code you write to execute the call involves creating a SqlParameterSource that contains the IN parameter. You must provide a name for the input value that matches the name of the parameter declared in the stored procedure. Case does not have to match because you use metadata to determine how database objects should be referenced in stored procedures. What is specified for the stored procedure in the source 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 an IN parameter and returns a Map that contains all out parameters typed by the name specified in the stored procedure. In the current instance, they are out_first_name,out_last_name and out_birth_date.

The last part of the execute method creates an instance of Actor 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. Similarly, the case of out parameter names stored in the result mapping table matches the case of out parameter names 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 LinkedCaseInsensitiveMap. To do this, you can create your own JdbcTemplate and set the setResultsMapCaseInsensitive property to true. You can then pass this custom JdbcTemplate instance to 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}

By doing this, you can avoid conflicts in cases that are used to return parameter names.

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 necessary. 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 about how to define SqlParameter, see the next section.

If you are using a database that is not supported by Spring, you must explicitly declare it. Currently, Spring supports metadata lookups for stored procedure calls to the following databases: Apache Derby,DB2,MySQL,Microsoft SQL Server,Oracle and Sybase. We also support metadata lookup for MySQL,Microsoft SQL Server and Oracle storage capabilities.

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

The following example shows a fully declared procedure call and uses the 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}

The 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 SqlParameters

To define parameters for the SimpleJdbc class and the RDBMS operation class (found in the JDBC operation modeled as a Java object), you can use SqlParameter or one of its subclasses. To do this, you usually specify the parameter name and SQL type in the constructor. Specify the SQL type by using the java.sql.Types constant. Earlier in this chapter, we saw a statement similar to the following:

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

The first line with SqlParameter declares an IN parameter. You can use the IN parameter for stored procedure calls and queries by using SqlQuery and its subclasses, which can be found in understanding SqlQuery.

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

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 you to provide input values for parameters declared as SqlOutParameter.

For IN parameters, you can specify a decimal place for numeric data or a type name for a custom database type, in addition to the name and SQL type. For the out parameter, you can provide a RowMapper to handle the mapping of rows returned from the REF cursor. Another option is to specify a SqlReturnType, which provides an opportunity to define a custom process for the return value.

3.6.8 call stored functions by using SimpleJdbcCall

You can call a stored function in almost the same way as calling a stored procedure, except that you provide a function name instead of a 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 return value of the function as an object of the specified type, which means that you do not have to retrieve the return value from the resulting Map. A similar convenient method (named executeObject) can be used for stored procedures that have only one out parameter. The following example (for MySQL) is based on a stored function named get_actor_name, which returns the full name of actor:

CREATE FUNCTION get_actor_name (in_id INTEGER) RETURNS VARCHAR READS SQL DATABEGIN DECLARE out_name VARCHAR; 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 once 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}

The executeFunction method used returns a String containing the return value of the function call.

3.6.9 return ResultSet or REF cursors 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 specific parameters. If a result set is returned in a result stored procedure, no name is defined, so the returned results must match the order in which the RowMapper implementation is declared. The specified name 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 does not take the IN parameter 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

To call this stored procedure, you can declare RowMapper. Because the class you are mapping to follows JavaBean rules, you can use BeanPropertyRowMapper, which is created by passing in the required class to map 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 () {Map m = procReadAllActors.execute (new HashMap (0)); return (List) m.get ("actors");} / /. Additional methods}

The execute call passes an empty Map because this call takes no parameters. The actor list is then retrieved from the resulting Map and returned to the caller.

This is the end of the content of "what is the method of batch operation of JDBC". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.