Spring JdbcTemplate, springjdbctemplate

Spring JdbcTemplate, springjdbctemplate

Reference link:

Https://my.oschina.net/u/437232/blog/279530

Http://jinnianshilongnian.iteye.com/blog/1423897

JdbcTemplate provides the following five methods:

• Execute method:It can be used to execute any SQL statement and is generally used to execute DDL statements;

• Update method and batchUpdate method:The update method is used to execute statements such as ADD, modify, and delete; The batchUpdate method is used to execute statements related to batch processing;

• Query Method and queryForXXX method:Used to execute query statements;

• Call method:Used to execute stored procedures and function-related statements.

The JdbcTemplate class supports the following callback classes:

• Pre-compiled statements and stored procedure creation callback:Creates statements based on the connection provided by JdbcTemplate;

PreparedStatementCreator: Get the Connection provided by JdbcTemplate through the callback. The user uses this Conncetion to create the relevant PreparedStatement;

CallableStatementCreator:Call back to obtain the Connection provided by JdbcTemplate. You can use this Conncetion to create the relevant CallableStatement;

• Pre-compiled statement value callback:It is used to set values for the parameters of the prepared statement;

PreparedStatementSetter:Get the PreparedStatement provided by JdbcTemplate through the callback. The user sets the value for the corresponding parameters of the pre-compiled statement;

BatchPreparedStatementSetter :;Similar to PreparedStatementSetter, but used for batch processing, you need to specify the batch size;

• Custom function callback:An extension point is provided to users. Users can perform any number of required operations at a specified extension point;

ConnectionCallback:Call back to obtain the Connection provided by JdbcTemplate. You can perform any number of operations on the Connection;

StatementCallback:Get the Statement provided by JdbcTemplate through callback. You can perform any number of operations on the Statement;

PreparedStatementCallback:Call back to obtain the PreparedStatement provided by JdbcTemplate. You can perform any number of operations on the PreparedStatement;

CallableStatementCallback:Call back to obtain the CallableStatement provided by JdbcTemplate. You can perform any number of operations on the CallableStatement;

• Result set processing callback:Process the ResultSet through callback or convert the ResultSet to the desired form;

RowMapper:This function is used to convert each row of data in the result set to the desired type. You need to implement the mapRow (ResultSet rs, int rowNum) method to convert each row of data to the corresponding type.

RowCallbackHandler:This method is used to process each row of ResultSet results. You need to implement the processRow (ResultSet rs) method to complete the processing. In this callback method, rs is not required. next (), which is executed by JdbcTemplate. You only need to obtain data by row and then process it.

ResultSetExtractor:It is used to extract result set data. You need to implement the extractData (ResultSet rs) method to process the result set. You must process the entire result set;

Next, let's take a look at the specific example. In this example, we cannot introduce all JdbcTemplate methods and callback class usage methods. We only introduce representative methods, and the rest are similar;

1) pre-compiled statements and stored procedure creation callback and custom function callback usage:

 

 

First, use PreparedStatementCreator to create a pre-compiled statement, and then use JdbcTemplate to return the statement through the PreparedStatementCallback callback. The user decides how to execute the PreparedStatement. Here we use the execute method.

2) pre-compiled statement value callback:

Java code:

Java code

Run the pre-compiled SQL statement through the int update (String SQL, PreparedStatementSetter pss) of JdbcTemplate. The SQL parameter is "insert into test (name) values (?) ", This SQL statement has a placeholder that needs to be set before execution. The PreparedStatementSetter implementation is to set the value. The setValues (PreparedStatement pstmt) callback method is used to set the value of the corresponding placeholder position. JdbcTemplate also provides a simpler method of "update (String SQL, Object... args) "to implement the set value, so you should use PreparedStatementSetter as long as this method does not meet your needs.

3) result set processing callback:

Java code:

Java code

Provided by the RowMapper InterfaceThe mapRow (ResultSet rs, int rowNum) method converts each row of the result set to a Map. Of course, it can be converted to other classes, such as the object painting form of the table.

Java code:

Java code

The RowCallbackHandler interface also provides the processRow (ResultSet rs) method to convert rows in the result set to the desired form.

Java code:

Java code

ResultSetExtractor uses the callback method extractData (ResultSet rs) to provide the user with the entire result set, allowing the user to decide how to process the result set.

Of course, JdbcTemplate provides a simpler queryForXXX method to simplify development:

Java code:

Java code

3) stored procedures and function callback:

Modify the setUp method of JdbcTemplateTest as follows:

Java code:

Java code

Specifically, create function FUNCTION_TEST is used to CREATE a user-defined FUNCTION. create procedure PROCEDURE_TEST is used to CREATE a stored PROCEDURE. Note that these creation statements are related to the database. The statements in this example only apply to the HSQLDB database.

Next, modify the tearDown method of JdbcTemplateTest, as shown below:

Java code:

Java code

The drop statement is used to delete the created stored procedures, user-defined functions, and database tables.

Next, let's take a look at how hsqldb calls udfs:

Java code:

Java code

• {Call FUNCTION_TEST (?)} :For SQL statements that define user-defined functions, note hsqldb {? = Call ...} And {call ...} The meaning is the same, and for example, the two meanings in mysql are different;

• Params: used to describe the placeholder parameters or named parameter types of user-defined functions; sqlParameter describes IN type parameters, SqlOutParameter describes OUT type parameters, SqlInOutParameter describes INOUT type parameters, and SqlReturnResultSet describes the ResultSet type data returned by calling stored procedures or udfs, sqlReturnResultSet must provide a result set processing callback to convert the result set to the corresponding form. The returned value of the hsqldb custom function is the ResultSet type.

• CallableStatementCreator:The Connection object is provided to create the CallableStatement object.

• OutValues: call the call method to return a Map <String, Object> Object of the type;

• OutValues. get ("result "):Obtain the result, that is, the converted data through the SqlReturnResultSet object. The name specified by SqlOutParameter, SqlInOutParameter, and SqlReturnResultSet is used to obtain the corresponding result from the Map returned after the call is executed, that is, name is the key of Map.

Note: hsqldb {? = Call ...} And {call ...} The meaning is the same, so calling a UDF will return a ResultSet containing the result.

Finally, let's take an example of how mysql calls a user-defined function:

Java code:

Java code

• GetMysqlDataSource:Start mysql first (this book uses version 5.4.3), and then log on to mysql to create the test database ("create database test;"). Before testing, download and add the mysql-connector-java-5.1.10.jar to classpath;

• {? = Call FUNCTION_TEST (?)} :{? = Call ...} Call udfs;

• Params:Instead of using SqlReturnResultSet to extract the result set data, SqlOutParameter is used to describe the returned value of the custom function;

• CallableStatementCreator:The meaning of the preceding example is the same;

• Cstmt. registerOutParameter (1, Types. INTEGER ):Register the OUT type parameter as the JDBC Type Types. INTEGER. In this example, the return type is Types. INTEGER.

• OutValues. get ("result "):Get the result and directly return the Integer type, which is much easier than hsqldb.

Finally, let's take a look at how to call the stored procedure:

Java code:

Java code

• {Call PROCEDURE_TEST (?, ?)} :Define the Stored Procedure SQL;

• Params:Define the stored procedure parameters. SqlInOutParameter describes the INOUT type parameters and SqlOutParameter describes the OUT type parameters;

• CallableStatementCreator: used to create CallableStatement, set the value and register the OUT parameter type;

• OutValues: Obtain the stored procedure result through the name defined by the SqlInOutParameter and SqlOutParameter parameters.

The JdbcTemplate class also provides many convenient methods, which are not described here. However, these methods follow the rules. The first method is to provide a callback interface for users to decide what to do, the second method can be considered as a convenient method (such as queryForXXX) for those simple operations.