Spring jdbcTemplate transaction, a variety of strange, package you just fill it !, Top filling

Spring jdbcTemplate transaction, a variety of strange, package you just fill it !, Top filling

Preface

The project framework is mainly spring. The persistence layer framework does not use mybtis, and spring jdbc is used;

Business Requirements: add fields to an application. (One field contains multiple applications. One application may belong to multiple fields. Generally, one application belongs to only one field ), the requirement is to delete all existing fields of the application before adding the selected fields to the database;

In order to reduce the preparation work, I used the previous Code and data modeling, so it became: when adding a person, first Delete the person with an existing name as the name of the newly added person, and then add a new person, to put it bluntly, add the person whose name is zhangsan. delete all the person information whose name is zhangsan in the database, and then add the person information of the new zhangsan to the database;

I will not write the environment setup process, and the complete code will be uploaded as attachments;

Note: For druid connection pools, jdbc is set to automatic submission. If this parameter is not set, it is automatically submitted by default. (If you are interested, you can check the druid connection pool source code)

 

JdbcTemplate automatic submission

First, verify whether the current jdbcTempalte is automatically submitted. If so, I can throw an exception after jdbcTemplate is executed. The Code is as follows:

Public int deleteOnePerson (String name) {int count = jdbcTemplate. update (DELETE_ONE_PERSON, new Object [] {name}); // jdbcTemplate execution completed count = count/0; // throw RuntimeException return count ;}

No transaction configured

<? Xml version = "1.0" encoding = "UTF-8"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: context = "http://www.springframework.org/schema/context" xmlns: aop = "http://www.springframework.org/schema/aop" xmlns: tx = "http://www.springframework.org/schema/tx" xsi: schemaLocation = "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans. Xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd "> <context: component-scan base-package =" com. lee. you. jdbc "/> <bean id =" propertyConfigurer "Class =" org. springframework. beans. factory. config. propertyPlaceholderConfigurer "> <property name =" location "> <value> mysqldb. properties </value> </property> </bean> <! -- Configure the data source --> <bean id = "dataSource" class = "com. alibaba. druid. pool. DruidDataSource"> <! -- Basic attribute url, user, password --> <property name = "driverClassName" value = "$ {jdbc. driverClassName} "/> <property name =" url "value =" $ {jdbc. url} "/> <property name =" username "value =" $ {jdbc. username} "/> <property name =" password "value =" $ {jdbc. password} "/> <property name =" initialSize "value =" $ {jdbc. initialSize} "/> <property name =" minIdle "value =" $ {jdbc. minIdle} "/> <property name =" maxActive "value =" $ {jdb C. maxActive} "/> <property name =" maxWait "value =" $ {jdbc. maxWait} "/> <! -- Whether to recycle after the time limit is exceeded --> <property name = "removeAbandoned" value = "$ {jdbc. removeAbandoned}"/> <! -- How long the time limit is exceeded; --> <property name = "removeAbandonedTimeout" value = "$ {jdbc. removeAbandonedTimeout}"/> <! -- Configure the interval for a test to detect idle connections that need to be closed, in milliseconds --> <property name = "timeBetweenEvictionRunsMillis" value = "$ {jdbc. timeBetweenEvictionRunsMillis} "/> <! -- Configure the minimum time for a connection to survive in the pool, in milliseconds --> <property name = "minEvictableIdleTimeMillis" value = "$ {jdbc. minEvictableIdleTimeMillis}"/> <! -- An SQL statement used to check whether the connection is valid. It must be a query statement --> <property name = "validationQuery" value = "$ {jdbc. validationQuery}"/> <! -- Detect connection request --> <property name = "testWhileIdle" value = "$ {jdbc. testWhileIdle}"/> <! -- When applying for a connection, run validationQuery to check whether the connection is valid. setting this parameter to true will reduce the performance. --> <property name = "testOnBorrow" value = "$ {jdbc. testOnBorrow}"/> <! -- Execute validationQuery to check whether the connection is valid when the connection is returned. Setting true will reduce the performance --> <property name = "testOnReturn" value = "$ {jdbc. testOnReturn} "/> <property name =" defaultAutoCommit "value =" $ {jdbc. defaultAutoCommit} "/> </bean> <bean id =" jdbcTemplate "class =" org. springframework. jdbc. core. jdbcTemplate "> <property name =" dataSource "ref =" dataSource "/> </bean> </beans>

View Code

 

If you want to delete and add data directly as follows, the database data is deleted but not added successfully.

Public int insertOnePerson (String name, int age) {int result = 0; int count = jdbcTemplate. update (DELETE_ONE_PERSON, new Object [] {name}); if (count> = 0) // = 0, the person information for this name does not exist before the database {result = jdbcTemplate. update (INSERT_ONE_PERSON, new Object [] {name, age});} return result ;}

 

Manually submit transactions

To ensure transaction consistency, I first thought about whether jdbcTemplate has transaction-related settings. However, I did not find it, but I found jdbcTemplate. getDataSource (). getConnection (), so the following code is quickly written:

　　Manual submission 1

Public int insertOnePerson (String name, int age) {int result = 0; try {jdbcTemplate. getDataSource (). getConnection (). setAutoCommit (false); int count = jdbcTemplate. update (DELETE_ONE_PERSON, new Object [] {name}); if (count> = 0) // = 0, the person information for this name does not exist before the database {result = jdbcTemplate. update (INSERT_ONE_PERSON, new Object [] {name, "1ac"});} jdbcTemplate. getDataSource (). getConnection (). commit ();} catch (SQLException e) {e. printStackTrace (); try {jdbcTemplate. getDataSource (). getConnection (). rollback ();} catch (SQLException e1) {e1.printStackTrace () ;}} finally {try {jdbcTemplate. getDataSource (). getConnection (). setAutoCommit (true);} catch (SQLException e) {e. printStackTrace () ;}} return result ;}

To achieve transaction consistency, the executable results are as follows:

Transaction consistency is not implemented. Why ??????? Here, I leave a suspense for everyone to think about. At that time, I did not study it carefully, because the task is the first thing to do, and then I wrote the following code:

　　Manual submission 2

Public int insertOnePerson (String name, int age) {int result = 0; Connection conn = null; PreparedStatement pstmt = null; try {conn = jdbcTemplate. getDataSource (). getConnection (); if (conn! = Null) {conn. setAutoCommit (false); pstmt = conn. prepareStatement (DELETE_ONE_PERSON); pstmt. setString (1, name); int count = pstmt.exe cuteUpdate (); pstmt. close (); if (count> = 0) {pstmt = conn. prepareStatement (INSERT_ONE_PERSON); pstmt. setString (1, name); pstmt. setString (2, "1adh"); // exception result = pstmt.exe cuteUpdate ();} conn. commit () ;}} catch (SQLException e) {e. printStackTrace (); try {conn. Rollback ();} catch (SQLException e1) {System. out. println ("rollback failed .. "); e1.printStackTrace () ;}} finally {try {conn. setAutoCommit (true); if (pstmt! = Null) {pstmt. close ();} if (conn! = Null) {conn. close () ;}} catch (SQLException e) {}} return result ;}

The strange thing is that the deletion is successful and the addition fails! My God, what is this ????

What's wrong with it? No code can be changed !!!

Mysql Engine

Check the database engine and find that the engine is MyISAM! Instant explosion !!!!

After the engine is changed to InnoDB, the Code submitted manually 2 can ensure transaction consistency. Can the Code submitted manually 1 also ensure transaction consistency? There is another suspense here. I hope you can think about it.

 

Automatic Transaction Management

Although the task has been completed, whether it is manual commit 2 or manual commit 1 (we think it can ensure transaction consistency), try catch of the Code is simply unacceptable; in the image, spring has transaction management, so let's take a look at how transactions are handed over to spring.

Configure the Transaction Manager:

<? Xml version = "1.0" encoding = "UTF-8"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xmlns: context = "http://www.springframework.org/schema/context" xmlns: aop = "http://www.springframework.org/schema/aop" xmlns: tx = "http://www.springframework.org/schema/tx" xsi: schemaLocation = "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans. Xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd "> <context: component-scan base-package =" com. lee. you. jdbc "/> <bean id =" propertyConfigurer "Class =" org. springframework. beans. factory. config. propertyPlaceholderConfigurer "> <property name =" location "> <value> mysqldb. properties </value> </property> </bean> <! -- Configure the data source --> <bean id = "dataSource" class = "com. alibaba. druid. pool. DruidDataSource"> <! -- Basic attribute url, user, password --> <property name = "driverClassName" value = "$ {jdbc. driverClassName} "/> <property name =" url "value =" $ {jdbc. url} "/> <property name =" username "value =" $ {jdbc. username} "/> <property name =" password "value =" $ {jdbc. password} "/> <property name =" initialSize "value =" $ {jdbc. initialSize} "/> <property name =" minIdle "value =" $ {jdbc. minIdle} "/> <property name =" maxActive "value =" $ {jdb C. maxActive} "/> <property name =" maxWait "value =" $ {jdbc. maxWait} "/> <! -- Whether to recycle after the time limit is exceeded --> <property name = "removeAbandoned" value = "$ {jdbc. removeAbandoned}"/> <! -- How long the time limit is exceeded; --> <property name = "removeAbandonedTimeout" value = "$ {jdbc. removeAbandonedTimeout}"/> <! -- Configure the interval for a test to detect idle connections that need to be closed, in milliseconds --> <property name = "timeBetweenEvictionRunsMillis" value = "$ {jdbc. timeBetweenEvictionRunsMillis} "/> <! -- Configure the minimum time for a connection to survive in the pool, in milliseconds --> <property name = "minEvictableIdleTimeMillis" value = "$ {jdbc. minEvictableIdleTimeMillis}"/> <! -- An SQL statement used to check whether the connection is valid. It must be a query statement --> <property name = "validationQuery" value = "$ {jdbc. validationQuery}"/> <! -- Detect connection request --> <property name = "testWhileIdle" value = "$ {jdbc. testWhileIdle}"/> <! -- When applying for a connection, run validationQuery to check whether the connection is valid. setting this parameter to true will reduce the performance. --> <property name = "testOnBorrow" value = "$ {jdbc. testOnBorrow}"/> <! -- Execute validationQuery to check whether the connection is valid when the connection is returned. Setting true will reduce the performance --> <property name = "testOnReturn" value = "$ {jdbc. testOnReturn} "/> <property name =" defaultAutoCommit "value =" $ {jdbc. defaultAutoCommit} "/> </bean> <bean id =" jdbcTemplate "class =" org. springframework. jdbc. core. jdbcTemplate "> <property name =" dataSource "ref =" dataSource "/> </bean> <! -- Transaction Manager --> <bean id = "transactionManager" class = "org. springframework. jdbc. datasource. dataSourceTransactionManager "> <property name =" dataSource "ref =" dataSource "/> </bean> <tx: annotation-driven transaction-manager =" transactionManager "/> </beans>

View Code

Configure transactions

@Transactional    public int insertOnePerson(String name, int age) {        int result = 0;        int count = jdbcTemplate.update(DELETE_ONE_PERSON, new Object[]{name});        if(count >= 0)                                                                            {            result = jdbcTemplate.update(INSERT_ONE_PERSON, new Object[]{name,"l123a"});        }        return result    ;    }

The execution result is as follows:

This code is much more refreshing and requires this feeling !! This is the feel!

 

Future and suspense answers

Lombok is used to build this project. If you don't know it, you can go to Baidu. Here I want to remind you that this is different from the general jar. He needs to install it, or the compilation fails! Jar files that are familiar with other tasks;

In addition, the druid connection pool has a version requirement for the mysql driver. mysql driver 5.1.10 reports an error when the connection pool is initialized. Specifically, I didn't try it one by one when no error is reported from which version, if you know it, you can leave a message. This project uses version 5.1.25;

Warning: Cannot resolve com. mysq. jdbc. connection. ping method. will use 'select 1' instead. java. lang. nullPointerException at com. alibaba. druid. pool. vendor. mySqlValidConnectionChecker. <init> (MySqlValidConnectionChecker. java: 50) at com. alibaba. druid. pool. druidDataSource. initValidConnectionChecker (DruidDataSource. java: 892) at com. alibaba. druid. pool. druidDataSource. init (DruidDataSource. java: 608) at com. alibaba. druid. pool. druidDataSource. getConnection (DruidDataSource. java: 934) at com. alibaba. druid. pool. druidDataSource. getConnection (DruidDataSource. java: 930) at com. alibaba. druid. pool. druidDataSource. getConnection (DruidDataSource. java: 102) at org. springframework. jdbc. datasource. dataSourceUtils. doGetConnection (performanceutils. java: 111) at org. springframework. jdbc. datasource. dataSourceUtils. getConnection (performanceutils. java: 77) at org.springframework.jdbc.core.JdbcTemplate.exe cute (JdbcTemplate. java: 386) at org. springframework. jdbc. core. jdbcTemplate. query (JdbcTemplate. java: 466) at org. springframework. jdbc. core. jdbcTemplate. query (JdbcTemplate. java: 471) at com. lee. you. jdbc. dao. impl. daoImpl. queryAllPerson (DaoImpl. java: 31) at com. lee. you. jdbc. jdbcTemplateTest. main (JdbcTemplateTest. java: 17)

View Code

Suspense answer:

Do you remember the two suspense stories? 1. Manual commit 1 does not guarantee transaction consistency because of the mysql engine. 2. If the mysql engine supports InnoDB of transactions, Can Manual commit 1 Ensure transaction consistency;

Regarding suspense 1, it is clear that if the mysql engine does not support transactions, no matter how the code is written, transaction consistency is empty talk;

Suspense 2: Transaction consistency cannot be guaranteed! Because jdbcTemplate. getDataSource (). the connection obtained by getConnection () and jdbcTemplate each time. the connection used by update is obtained from the connection pool and cannot be guaranteed as a connection. How can we ensure transaction consistency? Interested friends can read the source code, including various gold and various beautiful women!

Now, since jdbcTemplate obtains the connection from the connection pool every time it executes an operation, how does spring transaction management implement transaction consistency? For more highlights, please follow my next blog

Attachment