Basic use of Spring JDBC and transactions

Spring JDBC

First, the role

Convenient for us to operate the database;

Second, basic use

1. Configuration

First configure the data source (load Database driver class, database URL, user name password ...) ；

1. Registering a JDBC Template

That is to configure the bean class= "org.springframework.jdbc.core.JdbcTemplate", we need to use the object to manipulate the database; The bean needs to configure the DataSource property. The ref value is the data source bean;

1. To use the JDBC template by inheriting the Jdbcdaosupport class

The class has written the JdbcTemplate field, and as long as it inherits the class and injects a JDBC template bean reference to the JdbcTemplate field in the configuration, you can use the JDBC template in the Jdbcdaosupport subclass in the following way: This.getjdbctemplate (). How to change or delete ... ；

1. Using JdbcTemplate objects directly to manipulate databases

That is: JdbcTemplate object name = IOC container object name. Getbean ("jdbc template Beanid");

This can not inherit the Jdbcdaosupport class;

1. Single-line additions and deletions

This.getjdbctemplate (). Update (SQL statement, parameter 1 ..., parameter 2, parameter 3,.....);

Additions and deletions are used. Update method, parameter list is variable, can have any; the method has a return value that returns the number of rows affected

1. Batch deletion and modification

This.getjdbctemplate (). batchUpdate (SQL statement, list<objcet[]> parameter);

Where list<object[]> saves each row of parameter values (for example: List object. Add (New Object[]{id, "name", "Phone"})

1. Querying a column of data

Line: This.getjdbctemplate (). queryForObject (SQL statement, data type. class, Parameter 1, parameter 2,....);

Multiple lines: This.getjdbctemplate (). queryForList (SQL statement, data type. class, Parameter 1, parameter 2,....);

1. Querying multiple columns of data

This.getjdbctemplate (). queryForObject (SQL, RowMapper object, parameter 1, parameter 2,....);

Where the RowMapper object is the mapping of the queried column to an object;

Create RowMapper:

(1) rowmapper< object > Instance name =new beanpropertyrowmapper<> (object. Class);

(2) Write a class, implement the Rowmapper<t> interface, T is the object to be mapped, the interface has method Maprow (resultset parameter, int parameter), where the resultset parameter represents the current row, using the parameter name. Get Base data type ( Database column name/column number) gets to the specified column, and the second parameter is the current line number;

(3) Whether the query row or more lines, RowMapper will automatically traverse, all use the above method;

1. Named parameter jdbc Template (namedparameterjdbctempate)

The JDBC template is consistent with jdbctemplate, but it is possible to write parameter names in SQL statements that correspond to the names of the parameters in the additions and deletions method of the JDBC template

(0) Similarly, you need to configure the bean first, the constructor of the class is parameter, the configuration needs to be injected through the constructor to the datasource to it;

(1) The original JdbcTemplate object in the deletion and modification of the SQL statement when required parameters, the use of the number as a placeholder, and then in the method by the SQL statement parameter order to write parameters;

Example: sql= "select * from table where id=?" and name=? ”

This.getjdbctemplate (). queryForObject (SQL, RowMapper object, Id,name);

The order of the ID and name parameters here is consistent with the order of the parameters in the SQL statement

(2) in the named parameters of the JDBC template, with: Parameter name to occupy, adding or removing the difference method has a map, the map key is the parameter name in the SQL statement, the value is the value that needs to be manipulated;

Example: sql= "select * from table where Id=:id and Name=:name"

Map.put ("id", id value); Map.put ("name", name value);

Named parameter JDBC Template object. query (SQL,MAP);

(3) You can also use. Update (SQL statement, Sqlparametersource object) to operate;

Where: Sqlparametersource object name = new Beanpropertysqlparametersource (object instance);

This allows the object to be encapsulated together for easy writing, and it is important to note that the name in the placeholder for the method SQL statement needs to correspond to the field in the object;

Spring Transaction

0. function

1. transactions have adid characteristics;
2. Used to ensure the integrity and consistency of data;
3. And the transaction of the database is the same meaning;
4. Its underlying implementation is also a dynamic proxy for Java

Using transactions based on annotations

1. When using transactions, the SPRING-TX jar package is introduced, and the TX namespace is introduced in the configuration file.

1. In the XML configuration file, configure the Bean,id of the transaction manager to be arbitrary,

Class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager"

The bean also has to configure the DataSource property;

2. Enable transaction annotations

<tx:annotation-driven transaction-manager= "TransactionManager"/>

3. Using annotations

Write @transactional on a method of a class, the method uses the transaction, the code in it either executes successfully or rolls back on failure;

1. The propagation behavior of a transaction

(0) Meaning: Specifies how a transaction method should be propagated when it is called by another transaction method;

(1) For example: Method A is to buy a movie ticket; method B has a For loop call method A to achieve the purchase of multiple movie tickets, two methods are implemented transactions, when the problem (for the second sheet when the balance is insufficient, the movie ticket is insufficient ...), should all fail rollback, or the balance is not enough that part of the rollback?

(2) By default, when a problem occurs, it is all failed (that is, the transaction of method A (called method) hangs and runs within the transaction of method B (called method);

(3) You can also add parameters to the @transactional on the called Method:

Propagation=propagation.requires_new

When the implementation runs to the called Method A, method B hangs (that is, the portion of the balance is not enough, the balance and the movie ticket is sufficient to buy the success);

(4) It is important to note that when using transactional propagation behavior, the calling method and the called method should be in different classes;

1. Isolation level of a transaction
   1. You can set the isolation level isolation=isolation for the transaction method by adding a parameter to the @transactional. Level

1. Isolation_default: This is a platfromtransactionmanager default isolation level, using the default transaction isolation level of the database.

The other four correspond to the isolation level of JDBC

2. Isolation_read_uncommitted: This is the lowest isolation level of a transaction, which allows a foreign transaction to see the uncommitted data for this transaction. This isolation level produces dirty reads, non-repeatable reads, and Phantom reads.

3. isolation_read_committed: Ensure that a transaction modified data is committed before it can be read by another transaction. Another transaction cannot read uncommitted data for the transaction

4. Isolation_repeatable_read: This transaction isolation level prevents dirty reads and cannot be read repeatedly. However, Phantom reads may occur. In addition to ensuring that one transaction cannot read uncommitted data from another transaction, it ensures that the following conditions are avoided (non-repeatable read).

5. Isolation_serializable This is the most cost-effective, but most reliable, transaction isolation level. Transactions are processed for sequential execution. In addition to preventing dirty reading, non-repeatable reading, but also avoids phantom reading.

1. Specify rollback
   1. Transaction default rollback exception is run-time exception (that is, transaction is rolled back when method run-time exception)
   2. You can add parameters in @transactional, specify which exceptions are rolled back, and which exceptions do not roll back
   3. Norollbackfor/norollbackforclassnama, Rollbackfor/rollbackforclassnama

The former does not roll back, the exception will be thrown, but the modified data will not go back to the execution

The latter specifies which exceptions to roll back

1. Specify the time-out period

Add the timeout parameter (the value is an integer, in seconds) within the @transactional, specify the time the transaction method executes, and when the method executes timeout, throws a timeout exception;

I. Using transactions based on XML files (ASPECTJ framework)

1. The bean is configured as usual and the transaction manager is configured as above;

1. No need to write tags that enable transaction annotations;

1. Configure the properties of a transaction (similar to AspectJ AOP's facets)

<tx:advice id= "id free" transaction-manager= "transaction manager ID" >

<tx:attributes>

<tx:method name= "method name only (available with * number fuzzy match)" properties (such as delivery, specify rollback, read-only, timeout ...) propagation= "Requires_new"/>

....

If only the default property configuration is used, <tx:method name= "*"/>

1. Configure a transaction pointcut (with AspectJ AOP) to associate methods, transaction managers, and transaction properties

<aop:config>

Write Pointcut Expressions <aop:expression......>

Association <aop:advisor advice-ref= "transaction property ID" pointcut-ref= "pointcut expression id"/>

Ii. using transactions based on XML files (Spring comes with an AOP framework)

1. The bean is configured as usual and the transaction manager is configured as above;
2. No need to write tags that enable transaction annotations;
3. To generate the transaction agent:

<bean id= "Random id"

class= "Org.springframework.transaction.interceptor.TransactionProxyFactoryBean" >

Transaction manager <property Name= "TransactionManager" ref= "transaction manager Beanid" >

Target object <property name= "target" ref= "Destination Object Beanid" >

Transaction parameters <property name= "Transactionattributes" >

<props>

<prop key= "method in target object (method name only, available * fuzzy matching)" >

Each parameter value is separated by commas (parameter values such as propagation_rrequired ...)

</prop>

......

Other: There can be more than one prop in a props;

Transaction attribute parameter values please consult the API documentation;

-The Exception class name indicates a rollback when this exception occurs, and the + exception class name indicates that this exception occurs without rollback.

PS. Please consult the jar packages you need to use in your development process.

Basic use of Spring JDBC and transactions