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

This article mainly introduces "how to use JDBC core class control for JDBC processing". In daily operations, I believe many people have doubts about how to use JDBC core class control for JDBC processing. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to use JDBC core class control for JDBC processing". Next, please follow the editor to study!

The sequence of actions outlined in the following table may best show the values provided by the JDBC abstraction of the Spring framework. The table shows which operations Spring is responsible for and what you need to do.

ActionSpringYou defines connection parameters

X Open connection X

Specifies the SQL statement.

X declares parameters and provides parameter values

X prepares and runs the statement. X

Set a loop to traverse the results, if any. X

Do the work of each iteration.

X handles any exceptions. X

Handle exceptions. X

Close connections, statements, and result sets. X

The Spring framework handles all the underlying details that make JDBC such a boring API.

3.1 Select a method for JDBC database access

You can choose several methods to form the basis of JDBC database access. In addition to the three forms of JdbcTemplate, the new SimpleJdbcInsert and SimpleJdbcCall methods optimize database metadata, and the RDBMS Object style adopts an object-oriented approach similar to JDO Query design. Once you start using one of these methods, you can still mix and match to include functions from other methods. All methods require JDBC 2.0-compliant drivers, while some advanced features require JDBC 3.0 drivers.

JdbcTemplate is the classic and most popular Spring JDBC method. This lowest-level approach and all other methods use JdbcTemplate behind the scenes.

NamedParameterJdbcTemplate wraps a JdbcTemplate to provide named parameters instead of the traditional JDBC? Placeholder. This method provides better documentation and ease of use when you have multiple SQL statement parameters.

SimpleJdbcInsert and SimpleJdbcCall optimize database metadata to limit the amount of configuration necessary. This approach simplifies coding, so you only need to provide the name of the table or procedure (stored procedure) and provide a parameter mapping that matches the column name. This method is effective only if the database provides sufficient metadata. If the database does not provide this metadata, you must provide an explicit configuration of the parameters.

RDBMS objects (including MappingSqlQuery, SqlUpdate, and StoredProcedure) require you to create reusable and thread-safe objects during data access layer initialization. This method is modeled on JDO queries, where query strings are defined, parameters are declared, and queries are compiled. When you are finished, you can call execute (...) and update (...) multiple times with various parameter values. And findObject (...) Method.

3.2 packet structure

The JDBC abstraction framework of the Spring framework consists of four different packages:

Core: org.springframework.jdbc.core contains the JdbcTemplate class and its various callback interfaces, as well as various related classes. A subpackage named org.springframework.jdbc.core.simple contains the SimpleJdbcInsert and SimpleJdbcCall classes. Another subpackage named org.springframework.jdbc.core.namedparam contains the NamedParameterJdbcTemplate class and related supporting classes. See using JDBC core classes to control basic JDBC handling and error handling, JDBC batch operations, and using SimpleJdbc classes to simplify JDBC operations.

Datasource: org.springframework.jdbc.datasource contains a utility class for easy access to DataSource and various simple DataSource implementations that can be used to test and run unmodified JDBC code outside the Java EE container. A subpackage named org.springfamework.jdbc.datasource.embedded provides support for creating embedded databases using Java database engines such as HSQL, H2, and Derby. See controlling database connections and embedded database support.

Object: org.springframework.jdbc.object contains classes that represent RDBMS queries, updates, and stored procedures as thread-safe reusable objects. See Modeling JDBC operations as Java objects. Although the objects returned by the query are naturally disconnected from the database, JDO models this method. The higher-level JDBC abstraction depends on the lower-level abstraction in the org.springframework.jdbc.core package.

The support: the org.springframework.jdbc.support package provides SQLException transformation capabilities and some utility classes. Exceptions thrown during JDBC processing are converted to those defined in the org.springframework.dao package. This means that code that uses the Spring JDBC abstraction layer does not need to implement JDBC or RDBMS-specific error handling. All converted exceptions are uncheck exceptions, which gives you the option to catch exceptions that can be recovered from them while passing other exceptions to the caller. See working with SQLExceptionTranslator.

3.3.Using JDBC core classes to control JDBC handling and error handling

This section describes how to use the JDBC core class to control JDBC processing, including error handling. It includes the following topics:

Use JdbcTemplate

Use NamedParameterJdbcTemplate

Use SQLExceptionTranslator

Execute statement

Execute query

Update the database

Get automatically generated primary key

3.3.1 using JdbcTemplate

JdbcTemplate is the core class in JDBC's core package. It handles the creation and release of resources, which helps you avoid common mistakes, such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow (for example, statement creation and execution), while the application code provides SQL and extracts the results. JdbcTemplate class:

Run a SQL query

Update statements and stored procedure calls

Iterate over the ResultSet instance and extract the returned parameter values.

Catch JDBC exceptions and convert them to the generic, more informative exception hierarchy defined in the org.springframework.dao package. (see consistent exception hierarchy)

When you use JdbcTemplate for your code, you only need to implement the callback interface to provide them with clearly defined conventions. Given the Connection and PreparedStatementCreator callback interfaces provided by the JdbcTemplate class, a prepared statement is created, providing SQL and any necessary parameters. The same is true for the CallableStatementCreator interface, which creates callable statements. The RowCallbackHandler interface extracts values from each row of the ResultSet.

You can use JdbcTemplate in the DAO implementation by instantiating the DataSource reference directly, or you can configure it in the Spring IoC container and provide it to the DAO as a Bean reference.

You should always configure DataSource as Bean in the Spring IoC container. In the first case, the bean is provided directly to the service. In the second case, it is provided to the prepared template.

All SQL emitted by this class is recorded at the DEBUG level, which corresponds to the fully qualified class name of the template instance (usually JdbcTemplate, but may be different if you use a custom subclass of the JdbcTemplate class).

The following sections provide some examples of JdbcTemplate usage. These examples are not an exhaustive list of all the features exposed by JdbcTemplate. Please refer to the attached javadoc.

Query (SELECT)

The following query gets the number of rows in the association:

Int rowCount = this.jdbcTemplate.queryForObject ("select count (*) from t_actor", Integer.class)

The following query uses binding variables:

Int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject ("select count (*) from t_actor where first_name =?", Integer.class, "Joe")

The following query finds the string:

String lastName = this.jdbcTemplate.queryForObject ("select last_name from t_actor where id =?", String.class, 1212L)

The following query finds and populates a single realm object:

Actor actor = jdbcTemplate.queryForObject ("select first_name, last_name from t_actor where id =?", (resultSet, rowNum)-> {Actor newActor = newActor (); newActor.setFirstName (resultSet.getString ("first_name")); newActor.setLastName (resultSet.getString ("last_name")); return newActor;}, 1212L)

The following query finds and populates the list of realm objects:

List actors = this.jdbcTemplate.query ("select first_name, last_name from t_actor", (resultSet, rowNum)-> {Actor actor = new Actor (); actor.setFirstName (resultSet.getString ("first_name")); actor.setLastName (resultSet.getString ("last_name")); return actor;})

If the last two code snippets do exist in the same application, it makes sense to remove duplicates in both RowMapper lambda expressions and extract them into a single field, which can then be referenced by the DAO method as needed.

For example, use the code snippet you wrote earlier, as follows:

Private final RowMapper actorRowMapper = (resultSet, rowNum)-> {Actor actor = new Actor (); actor.setFirstName (resultSet.getString ("first_name")); actor.setLastName (resultSet.getString ("last_name")); return actor;}; public List findAllActors () {return this.jdbcTemplate.query ("select first_name, last_name from t_actor", actorRowMapper);}

Update with JdbcTemplate (INSERT,UPDATE and DELETE)

You can use update (..) Method to perform insert, update, and delete operations. Parameter values are usually provided as variable parameters or as an array of objects.

The following example inserts a new data:

This.jdbcTemplate.update ("insert into t_actor (first_name, last_name) values", "Leonor", "Watling")

The following example updates existing data:

This.jdbcTemplate.update ("update t_actor set last_name =? where id =?", "Banjo", 5276L)

The following example deletes a piece of data:

This.jdbcTemplate.update ("delete from t_actor where id =?", Long.valueOf (actorId))

Reference code: org.liyong.dataaccess.starter.JdbcTemplateTransactionManagerIocContainer

Other JdbcTemplate operations

You can use execute (..) Method to run any SQL. Therefore, this method is often used in DDL statements. It is accepted for variable overloading of callback interfaces, arrays of bound variables, and so on. The following example creates a table:

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

The following example calls a stored procedure:

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

More complex stored procedure support will be introduced later.

JdbcTemplate Best practic

Once configured, the instance of the JdbcTemplate class is thread-safe. It is important because it means that you can configure a single instance of JdbcTemplate and then safely inject the shared reference into multiple DAO (or repositories). JdbcTemplate is stateful because it maintains a reference to DataSource, but this state is not session state.

A common practice for using the JdbcTemplate class (and the associated NamedParameterJdbcTemplate class) is to configure DataSource in the Spring configuration file and then inject shared DataSource bean dependencies into the DAO class. The JdbcTemplate is created in the setter of the data source. This results in a DAO similar to the following:

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

The following example shows the corresponding XML configuration:

An alternative to explicit configuration is dependency injection using component scanning and annotation support. In this case, you can annotate the class with @ Repository (which makes it a candidate for component scanning) and annotate the DataSource setter method with @ Autowired. The following example shows how to do this:

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

Annotate the class with @ Repository.

Annotate the DataSource setter method with @ Autowired.

Create a new JdbcTemplate using DataSource.

The following example shows the corresponding XML configuration:

If you use Spring's JdbcDaoSupport class and extend the various DAO classes supported by JDBC, your subclass will inherit a setDataSource (..) from the JdbcDaoSupport class. Method. You can choose whether or not to inherit from this class. The JdbcDaoSupport category is provided only for convenience.

No matter which template initialization style you choose to use (or not), there is no need to create a new instance of the JdbcTemplate class every time you run SQL. After configuration, the JdbcTemplate instance is thread-safe. If your application accesses multiple databases, you may need multiple JdbcTemplate instances, which requires multiple data sources, and then multiple JdbcTemplate instances with different configurations.

Reference code: org.liyong.dataaccess.starter.JdbcTemplateBestTransactionManagerIocContainer

3.3.2 using NamedParameterJdbcTemplate

Instead of using only classic placeholders (?) Instead of programming JDBC statements, the NamedParameterJdbcTemplate class adds support for programming JDBC statements with named parameters. The NamedParameterJdbcTemplate class wraps the JdbcTemplate and delegates it to the wrapped JdbcTemplate to do most of its work. This section describes only those parts of the NamedParameterJdbcTemplate class that are different from the JdbcTemplate itself, programming JDBC statements by using named parameters. The following example shows how to use NamedParameterJdbcTemplate:

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

Notice that named parameter symbols are used in the values assigned to the sql variable and in the corresponding values inserted into the namedParameters variable (type MapSqlParameterSource).

Alternatively, you can use the Map-based format to pass named parameters and their corresponding values to the NamedParameterJdbcTemplate instance. The rest of the methods exposed by NamedParameterJdbcOperations and implemented by the NamedParameterJdbcTemplate class follow a similar pattern and will not be repeated here.

The following example illustrates the use of the Map-based format:

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

The SqlParameterSource interface is a nice feature related to NamedParameterJdbcTemplate (and exists in the same Java package). You have seen an implementation example of this interface in one of the previous code snippets (the MapSqlParameterSource class). SqlParameterSource is the source of named parameter values for NamedParameterJdbcTemplate. The MapSqlParameterSource class is a simple implementation that surrounds the java.util.Map adapter, where the key is the parameter name and the value is the parameter value.

Another SqlParameterSource implementation is the BeanPropertySqlParameterSource class. This class wraps an arbitrary JavaBean (that is, an instance of a class that follows the JavaBean convention) and uses the properties of the wrapped JavaBean as the source of named parameter values.

The following example shows a typical JavaBean:

Public class Actor {private Long id; private String firstName; private String lastName; public String getFirstName () {return this.firstName;} public String getLastName () {return this.lastName;} public Long getId () {return this.id;} / / setters omitted...}

The following example uses NamedParameterJdbcTemplate to return the number of members of the class shown in the previous example:

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

Remember, the NamedParameterJdbcTemplate class wraps the classic JdbcTemplate template. If you need to access the wrapped JdbcTemplate instance to access functionality available only in the JdbcTemplate class, you can use the getJdbcOperations () method to access the wrapped JdbcTemplate through the JdbcOperations interface.

See also JdbcTemplate best practices for guidance on using NamedParameterJdbcTemplate classes in the context of your application.

Reference code: org.liyong.dataaccess.starter.NamedParameterTransactionManagerIocContainer

Mark position

3.3.3 using SQLExceptionTranslator

SQLExceptionTranslator is an interface implemented by a class that can convert between SQLExceptions and Spring's own org.springframework.dao.DataAccessException, regardless of data access policy. To improve precision, the implementation can be generic (for example, using SQLState code for JDBC) or proprietary (for example, using Oracle error codes).

SQLErrorCodeSQLExceptionTranslator is the default implementation of SQLExceptionTranslator. This implementation uses specific vendor code. It is more accurate than the SQLState implementation. The conversion of the error code is based on the code in a JavaBean type class named SQLErrorCodes. This class is created and populated by SQLErrorCodesFactory, which is a factory that creates a SQLErrorCodes based on the contents of a configuration file called sql-error-codes.xml. This file is populated with vendor code and based on the DatabaseProductName obtained from DatabaseMetaData. Use the code of the actual database you are using.

SQLErrorCodeSQLExceptionTranslator applies the matching rules in the following order:

Any custom transformation implemented by the subclass. Typically, the specific SQLErrorCodeSQLExceptionTranslator provided will be used, so this rule does not apply. It applies only if you do provide a subclass implementation.

Any custom implementation of the SQLExceptionTranslator interface provided as the customSqlExceptionTranslator property of the SQLErrorCodes class.

Search the list of instances of the CustomSQLErrorCodesTranslation class (provided for the customTranslations property of the SQLErrorCodes class) to find a match.

Error code matching is applied.

Use the backup converter. SQLExceptionSubclassTranslator is the default backup converter. If this converter is not available, the next backup converter is SQLStateSQLExceptionTranslator.

By default, SQLErrorCodesFactory is used to define error codes and custom exception conversions. Look for them from the classpath file named sql-error-codes.xml and find a matching SQLErrorCodes instance based on the database name in the database metadata of the database in use.

You can extend SQLErrorCodeSQLExceptionTranslator, as shown in the following example:

Public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {protected DataAccessException customTranslate (String task, String sql, SQLException sqlEx) {if (sqlEx.getErrorCode ()) =-12345) {return new DeadlockLoserDataAccessException (task, sqlEx);} return null;}}

In the previous example, a specific error code (- 12345) is converted, while other errors are converted by the default converter. To use this custom converter, it must be passed to JdbcTemplate through the setExceptionTranslator method, and this JdbcTemplate must be used in all data access processes that require this converter. The following example shows how to use this custom converter:

Private JdbcTemplate jdbcTemplate;public void setDataSource (DataSource dataSource) {/ / create a JdbcTemplate and set data source this.jdbcTemplate = new JdbcTemplate (); this.jdbcTemplate.setDataSource (dataSource); / / create a custom translator and set the DataSource for the default translation lookup CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator (); tr.setDataSource (dataSource); this.jdbcTemplate.setExceptionTranslator (tr) } public void updateShippingCharge (long orderId, long pct) {/ / use the prepared JdbcTemplate for this update this.jdbcTemplate.update ("update orders" + "set shipping_charge = shipping_charge *? / 100" +" where id =? ", pct, orderId);}

The custom converter passes a data source to look for the error code in sql-error-codes.xml.

3.3.4 run statement

Running the SQL statement requires very little code. You need a data source and a JdbcTemplate, including the convenient methods provided by JdbcTemplate. The following example shows what the smallest but full-featured class needs to include to create a new table:

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

3.3.5 run the query

Some query methods return a single value. To retrieve a count or a specific value from a row, use queryForObject (..). The latter converts the returned JDBC Type to the Java class passed as a parameter. If the type conversion is invalid, InvalidDataAccessApiUsageException is thrown. The following example contains two query methods, one for int and the other for querying String:

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

In addition to a single result query method, there are several methods that return a list containing entries for each row returned by the query. The most common method is queryForList (..), which uses the column name as the key and returns a List, where each element is a Map and each column contains an entry. If you added a method to retrieve a list of all rows in the previous example, it might look like this:

Private JdbcTemplate jdbcTemplate;public void setDataSource (DataSource dataSource) {this.jdbcTemplate = new JdbcTemplate (dataSource);} public List getList () {return this.jdbcTemplate.queryForList ("select * from mytable");}

The list returned is similar to the following:

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

3.3.6 Update the database

The following example updates the column of a primary key:

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

In the previous example, the SQL statement has placeholders for row parameters. You can pass parameter values as variable parameters or as an array of objects. Therefore, you should explicitly wrap the base type in the basic wrapper class, or you should use autoboxing.

3.3.7 retrieve automatically generated primary keys

The update () convenient method supports the retrieval of primary keys generated by the database. This support is part of the JDBC 3.0 standard. For more information, see Chapter 13.6 of the specification. This method takes PreparedStatementCreator as its first parameter, which is how to specify the desired insert statement. The other parameter is KeyHolder, which contains the primary key generated when it returns successfully from the update. There is no standard single way to create the appropriate PreparedStatement (which explains why method signatures are like this). The following example is valid on Oracle, but may not apply on other platforms:

Final String INSERT_SQL = "insert into my_test (name) values (?)"; final String name = "Rob"; KeyHolder keyHolder = new GeneratedKeyHolder (); jdbcTemplate.update (connection-> {PreparedStatement ps = connection.prepareStatement (INSERT_SQL, new String [] {"id"}); ps.setString (1, name); return ps;}, keyHolder); / / keyHolder.getKey () now contains the generated key3.4 controls database connections

This section includes:

Use DataSource

Use DataSourceUtils

Implement SmartDataSource

Extended AbstractDataSource

Use SingleConnectionDataSource

Use DriverManagerDataSource

Use TransactionAwareDataSourceProxy

Use DataSourceTransactionManager

3.4.1 using DataSource

Spring obtains a connection to the database through DataSource. DataSource is part of the JDBC specification and is a general connection factory. It enables the container or framework to hide connection pooling and transaction management problems from application code. As a developer, you don't need to know the details of how to connect to the database. This is the responsibility of the administrator who sets up the data source. You probably play both roles when developing and testing code, but you don't have to know how to configure production data sources.

When using Spring's JDBC layer, you can get the data source from JNDI, or you can use the connection pooling implementation provided by third parties to configure your own data source. The traditional choices are Apache Commons DBCP and C3P0 of the DataSource class with bean style. For modern JDBC connection pooling, consider using HikariCP with its generator style API.

You should only use the DriverManagerDataSource and SimpleDriverDataSource classes (included in the Spring distribution) for testing! When multiple connection requests are made, these variants do not provide buffer pools and perform poorly.

The following sections are implemented using Spring's DriverManagerDataSource. Several other DataSource variants will be introduced later.

To configure DriverManagerDataSource:

Usually the same as the JDBC connection, get the connection to the DriverManagerDataSource.

Specify the standard class name of the JDBC driver so that DriverManager can load the driver class.

Provides URL for different JDBC drivers. (refer to the driver's documentation for the correct value.)

Provide a user name and password to connect to the database.

The following example shows how to configure DriverManagerDataSource in Java:

DriverManagerDataSource dataSource = new DriverManagerDataSource (); dataSource.setDriverClassName ("org.hsqldb.jdbcDriver"); dataSource.setUrl ("jdbc:hsqldb:hsql://localhost:"); dataSource.setUsername ("sa"); dataSource.setPassword ("")

The following example shows the corresponding XML configuration:

The next two examples show the basic connectivity and configuration of DBCP and C3P0. To learn more about options that help control pooling capabilities, refer to the product documentation for the appropriate connection pooling implementation.

The following example shows the DBCP configuration:

The following example shows the C3P0 configuration:

3.4.2 using DataSourceUtils

The SmartDataSource interface should be implemented by a class that provides a connection to a relational database. It extends the DataSource interface to allow classes that use it to query whether a connection should be closed after a given operation. This usage is very effective when you know that you need to reuse connections.

3.4.4 extended AbstractDataSource

AbstractDataSource is the abstract base class of Spring's DataSource implementation. It implements the code common to all DataSource implementations. If you write your own DataSource implementation, you should extend the AbstractDataSource class.

3.4.5 using SingleConnectionDataSource

The SingleConnectionDataSource class is an implementation of the SmartDataSource interface, which wraps a single Connection that is not closed after each use. This is not a multithreading feature.

If any client code calls close on the assumption that a pooled connection is established, such as when using a persistence tool, the preventClose property should be set to true. This setting returns a proxy that encloses the physical wrapper. Note that you can no longer convert this object to a local Oracle Connection or similar object.

SingleConnectionDataSource is primarily a test class. SingleConnectionDataSource is primarily a test class. For example, it combines with a simple JNDI environment to easily test code outside the application server. Compared with DriverManagerDataSource, it always reuses the same connection, avoiding too much physical connection creation.

3.4.6 use DriverManagerDataSource

The DriverManagerDataSource class is an implementation of the standard DataSource interface, which configures a pure JDBC driver through the bean property and returns a new Connection each time.

This implementation is useful for testing and stand-alone environments outside the Java EE container and can be used as a DataSource bean in a Spring IoC container or in conjunction with a simple JNDI environment. Assume that the Connection.close () call using the pool closes the connection, so any persistence code that recognizes DataSource should work. However, even in a test environment, using JavaBean-style connection pooling (such as commons-dbcp) is so easy that it is always used on DriverManagerDataSource.

3.4.7 using TransactionAwareDataSourceProxy

TransactionAwareDataSourceProxy is the proxy for the target DataSource. The agent wraps the target DataSource to increase awareness of the transactions managed by Spring. In this respect, it is similar to the transactional JNDI data source provided by the Java EE server.

This class is rarely used unless you need to call existing code and implement it through a standard JDBC DataSource interface. In this case, you can still make the code available while making it involved in Spring-managed transactions. In general, it is best to use a higher level of resource management abstraction to write your own new code, such as JdbcTemplate or DataSourceUtils.

For more details, see TransactionAwareDataSourceProxy javadoc.

3.4.8 using DataSourceTransactionManager

The DataSourceTransactionManager class is an PlatformTransactionManager implementation of a single JDBC data source. It binds the JDBC connection from the specified data source to the currently executing thread, possibly allowing one thread connection per data source.

Retrieving JDBC connections through DataSourceUtils.getConnection (DataSource) instead of Java EE's standard DataSource.getConnection requires application code. It throws an unchecked org.springframework.dao exception instead of a checked SQLException. All framework classes, such as JdbcTemplate, implicitly use this policy. If not used with the transaction manager, the lookup policy behaves exactly the same as the normal policy. Therefore, it can be used in any case.

The DataSourceTransactionManager class supports custom isolation levels and timeouts, which are applied to the appropriate JDBC statement query timeout. To support the latter, the application code must use JdbcTemplate or call DataSourceUtils.applyTransactionTimeout (..) for each statement created. Method.

In the case of a single resource, you can use this implementation instead of JtaTransactionManager because it does not require the container to support JTA. As long as you follow the desired connection lookup mode, you can switch between the two is just a matter of configuration. JTA does not support custom isolation levels.

3.5 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.

At this point, the study on "how to use JDBC core class control for JDBC processing" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.