Spring JDBC and transaction control

Xu Yuxi 2021-01-22 18:18:45
spring jdbc transaction control


==================

primary coverage

Spring Integrate JDBC Environmental Science

​ Spring In addition to providing IOC And AOP Beyond core functions , Also provided based on JDBC Data access function of , Make it easier to access persistence layer data . Use Spring JDBC Environmental Science , First of all, we need a set of Spring Integrate JDBC Environment .

Add dependent coordinates

<!-- Add related dependent coordinates -->
<!-- spring Frame coordinates depend on adding -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<!-- spring Test environment -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.2.4.RELEASE</version>
<scope>test</scope>
</dependency>
<!-- aop -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
<!-- spring jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<!-- spring thing -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.4.RELEASE</version>
</dependency>
<!-- mysql Drive pack -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
<!-- c3p0 Connection pool -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency> 

add to jdbc The configuration file

stay src/main/resources New under the directory jdbc.properties The configuration file , And set the corresponding configuration information

# Driver name
jdbc.driver=com.mysql.cj.jdbc.Driver
# Database connection
jdbc.url=jdbc:mysql://localhost:3306/( Database name )?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
# Database user name
jdbc.user=( Database account )
# Database user password
jdbc.password=( Database password ) 

The following is an optional configuration

# Specifies the number of initial connections to the connection pool . The value should be in minPoolSize And maxPoolSize Between .Default:3
initialPoolSize=20
# Specifies the maximum number of connections to keep in the connection pool . Default:15
maxPoolSize=100
# Specifies the minimum number of connections to keep in the connection pool
minPoolSize=10
# Maximum free time ,60 If not used within seconds, the connection is discarded . if 0 Then never abandon . Default:0
maxIdleTime=600
# When the connections in the connection pool are exhausted c3p0 Number of connections acquired at one time . Default:3
acquireIncrement=5
# JDBC Standards for , To control the loading in the data source PreparedStatements Number .
maxStatements=5
# Every time 60 Seconds to check all free connections in the connection pool .Default:0
idleConnectionTestPeriod=60 

modify spring The configuration file

<!-- load properties The configuration file , For reading jdbc.properties The data in the file -->
<context:property-placeholder location="jdbc.properties" /> 

spring.xml

<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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!-- Spring Scan annotation configuration -->
<context:component-scan base-package="com.xxxx" />
<!-- load properties The configuration file -->
<context:property-placeholder location="jdbc.properties" />
</beans> 

Configure data sources

​ Because establishing a database connection is a very time-consuming and resource consuming behavior , So establish some connections with the database in advance through the connection pool , In memory , When an application needs to establish a database connection, it can directly apply for one in the connection pool , Put it back after use .

C3P0 And DBCP One out of two

​ DBCP(DataBase connection pool), Database connection pool . yes apache On the one java Connection pool project , It's also tomcat Connection pool components used . Use alone dbcp need 2 A package :commons-dbcp.jar,commons-pool.jar dbcp, There is no automatic recycling of idle connections .

​ C3P0 It's an open source JDBC Connection pool , It implements the data source , Support JDBC3 Normative and JDBC2 Standard extension of . At present, the open source projects using it are Hibernate,Spring etc. .c3p0 It has the function of automatically reclaiming idle connections .

C3P0 Data source configuration

<!-- To configure c3p0 data source -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- property Labeled value Property corresponds to jdbc.properties The value in -->
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean> 

C3P0 Other extras ( The corresponding value is in jdbc.properties The document specifies )

<!-- Specifies the maximum number of connections to keep in the connection pool . Default:15-->
<property name="maxPoolSize" value="${maxPoolSize}"/>
<!-- Specifies the minimum number of connections to keep in the connection pool .-->
<property name="minPoolSize" value="${minPoolSize}"/>
<!-- Specifies the number of initial connections to the connection pool . The value should be in minPoolSize And maxPoolSize Between .Default:3-->
<property name="initialPoolSize" value="${initialPoolSize}"/>
<!-- Maximum free time ,60 If not used within seconds, the connection is discarded . if 0 Then never abandon . Default:0-->
<property name="maxIdleTime" value="${maxIdleTime}"/>
<!-- When the connections in the connection pool are exhausted c3p0 Number of connections acquired at one time . Default:3-->
<property name="acquireIncrement" value="${acquireIncrement}"/>
<!-- JDBC Standards for , To control the loading in the data source PreparedStatements Number .
But due to pre cached statements Belongs to a single connection, Not the entire connection pool, so setting this parameter needs to consider many factors . If maxStatements And maxStatementsPerConnection Are all 0, Then the cache is closed .Default:0-->
<property name="maxStatements" value="${maxStatements}"/>
<!-- Every time 60 Seconds to check all free connections in the connection pool .Default:0 -->
<property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}"/> 

DBCP Data source configuration

<!-- To configure dbcp data source -->
<bean id="myDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
<!-- Initial value when connection pool starts -->
<property name="initialSize" value="1"/>
<!-- Maximum free . After a rush hour , Connection pool can slowly release some of the connections that are no longer available , Down to maxIdle until -->
<property name="maxIdle" value="2"/>
<!-- Minimum free . When the number of idle connections is less than the threshold , The connection pool will pre request some connections , In order to avoid the performance cost caused by applying again when the flood peak comes -->
<property name="minIdle" value="1"/>
</bean> 

Template class configuration

​ Spring hold JDBC A template class is created by repeating operations in :org.springframework.jdbc.core.JdbcTemplate .

<!-- To configure JdbcTemplate example , And inject a dataSource data source -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean> 

JDBC test

Create the specified database

Select connection , The right choice " New database ", Set the name and encoding format of the database

Create data table

Use JUnit test

adopt junit test jdbcTemplate bean Whether or not to get

JUnit test
public class SpringJdbcTest01 {
@Test
public void testQueryCount() {
// obtain spring Context
ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
// Get the template class JdbcTemplate object
JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
// Definition sql sentence
String sql = "select count(1) from tb_account";
// Perform query operation ( No parameter )
Integer total= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(" Total number of records :" + total);
}
@Test
public void testQueryCountByUserId() {
// obtain spring Context
ApplicationContext ctx = new ClassPathXmlApplicationContext("spring.xml");
// Get the template class JdbcTemplate object
JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
// Definition sql sentence
String sql = " select count(1) from tb_account where user_id = ?";
// Perform query operation ( With parameters )
Integer total = jdbcTemplate.queryForObject(sql, Integer.class, 1);
System.out.println(" Total number of records :" + total);
}
} 
Simple packaging
public class SpringJdbcTest02 {
private JdbcTemplate jdbcTemplate;
@Before
public void init() {
// obtain Spring Context
ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
// Get the template class JdbcTemplate object
jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");
}
@Test
public void testQueryCount() {
// Definition sql sentence
String sql = "select count(1) from tb_account";
// Perform query operation ( No parameter )
Integer total= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(" Total number of records :" + total);
}
@Test
public void testQueryCountByUserId() {
// Definition sql sentence
String sql = " select count(1) from tb_account where user_id = ?";
// Perform query operation ( With parameters )
Integer total = jdbcTemplate.queryForObject(sql, Integer.class, 1);
System.out.println(" Total number of records :" + total);
}
} 
Annotation encapsulation
@RunWith
It's just a runner
@RunWith(JUnit4.class) It means using JUnit4 To run the
@RunWith(SpringJUnit4ClassRunner.class) Let the test run on Spring Test environment
@ContextConfiguration
Spring Integrate JUnit4 When testing , Use annotations to bring in multiple profiles
@ContextConfiguration(Locations="classpath:applicationContext.xml")
@ContextConfiguration(locations = {"classpath:spring.xml", "classpath:bean.xml"}) 
@RunWith(SpringJUnit4ClassRunner.class) // take junit The test is added to spring Environment
@ContextConfiguration(locations = {"classpath:spring.xml"}) // Set the resource file to load
public class SpringJdbcTest03 {
@Resource
private JdbcTemplate jdbcTemplate;
@Test
public void testQueryCount() {
// Definition sql sentence
String sql = "select count(1) from tb_account";
// Perform query operation ( No parameter )
Integer total= jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(" Total number of records :" + total);
}
} 
General packaging
  1. Define a parent class , Set general configuration information

    /**
 */
@RunWith(SpringJUnit4ClassRunner.class) // take junit The test is added to spring Environment
@ContextConfiguration(locations = {"classpath:spring.xml"}) // Set the resource file to load
public class BaseTest {
}
```
  1. Inherit common test classes

    public class SpringJdbcTest04 extends BaseTest {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    public void testQueryCount() {
    // Definition sql sentence
    String sql = "select count(1) from tb_account";
    // Perform query operation ( No parameter )
    Integer total= jdbcTemplate.queryForObject(sql, Integer.class);
    System.out.println(" Total number of records :" + total);
    }
    } 

Persistence layer account module operation

​ When finished Spring Jdbc After environment integration , Use here spring jdbc Complete the account sheet crud operation .

Account interface method definition

Defining entity classes

Account.java

package com.xxxx.entity;
import java.util.Date;
/**
* User account class
*/
public class Account {
private Integer accountId; // Account ID, Primary key
private String accountName; // title of account
private String accountType; // Account type
private Double money; // Amount of account
private String remark; // Account notes
private Integer userId; // user ID, Account user
private Date createTime; // Creation time
private Date updateTime; // Modification time
public Account() {
}
public Account(String accountName, String accountType, Double money,
String remark, Integer userId) {
this.accountName = accountName;
this.accountType = accountType;
this.money = money;
this.remark = remark;
this.userId = userId;
}
@Override
public String toString() {
return "Account{" +
"accountId=" + accountId +
", accountName='" + accountName + ''' +
", accountType='" + accountType + ''' +
", money=" + money +
", remark='" + remark + ''' +
", userId=" + userId +
", createTime=" + createTime +
", updateTime=" + updateTime +
'}';
}
public Integer getAccountId() {
return accountId;
}
public void setAccountId(Integer accountId) {
this.accountId = accountId;
}
public String getAccountName() {
return accountName;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public String getAccountType() {
return accountType;
}
public void setAccountType(String accountType) {
this.accountType = accountType;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
} 

Define interface classes

IAccountDao.java

package com.xxxx.dao;
import com.xxxx.entity.Account;
import java.util.List;
/**
* User module Interface definition
* 1. Add account
* Add account records , Returns the number of affected rows
* Add account records , Return the primary key of the record
* Batch add account records , Returns the number of affected rows
* 2. Check the account
* Query the total number of account records of the specified user , Number of records returned
* Query the specified account record details , Return account object
* Query the account list of the specified user by multiple conditions , Return account set
* 3. Update account
* Update account records , Returns the number of affected rows
* Batch update account records , Returns the number of affected rows
* 4. Delete account
* Delete account records , Returns the number of affected rows
* Delete account records in batch , Returns the number of affected rows
*/
public interface IAccountDao {
/**
* Add account
* Add account records , Returns the number of affected rows
* @param account
* @return
*/
public int addAccount(Account account) ;
/**
* Add account
* Add account records , Return the primary key of the record
* @param account
* @return
*/
public int addAccountHasKey(Account account);
/**
* Add account
* Batch add account records , Returns the number of affected rows
* @param accounts
* @return
*/
public int addAccountBatch(List<Account> accounts);
/**
* Check the account
* Query the total number of account records of the specified user , Number of records returned
* @param userId
* @return
*/
public int queryAccountCount(Integer userId);
/**
* Check the account
* Query the specified account record details , Return account object
* @param accountId
* @return
*/
public Account queryAccountById(Integer accountId);
/**
* Check the account
* Query the account list of the specified user by multiple conditions , Return account set
* @param userId
* @param accountName
* @param accountType
* @param createTime
* @return
*/
public List<Account> queryAccountsByParams(Integer userId, String accountName, String accountType, String createTime);
/**
* Update account
* Update account records , Returns the number of affected rows
* @param account
* @return
*/
public int updateAccountById(Account account);
/**
* Update account
* Batch update account records , Returns the number of affected rows
* @param accounts
* @return
*/
public int updateAccountBatch(List<Account> accounts);
/**
* Delete account
* Delete account records , Returns the number of affected rows
* @param accountId
* @return
*/
public Integer deleteAccoutById(Integer accountId);
/**
* Delete user
* Delete account records in batch , Returns the number of affected rows
* @param ids
* @return
*/
public int deleteAccountBatch(Integer[] ids);
} 

Define interface implementation classes

package com.xxxx.dao.impl;
import com.xxxx.dao.IAccountDao;
import com.xxxx.entity.Account;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.util.List;
/**
* Account module interface implementation class
*/
@Repository
public class AccountDaoImpl implements IAccountDao {
// JdbcTemplate Template class injection
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public int addAccount(Account account) {
return 0;
}
@Override
public int addAccountHasKey(Account account) {
return 0;
}
@Override
public int addAccountBatch(List<Account> accounts) {
return 0;
}
@Override
public int queryAccountCount(Integer userId) {
return 0;
}
@Override
public Account queryAccountById(Integer accountId) {
return null;
}
@Override
public List<Account> queryAccountsByParams(Integer userId, String accountName, String accountType, String createTime) {
return null;
}
@Override
public int updateAccountById(Account account) {
return 0;
}
@Override
public int updateAccountBatch(List<Account> accounts) {
return 0;
}
@Override
public Integer deleteAccoutById(Integer accountId) {
return null;
}
@Override
public int deleteAccountBatch(Integer[] ids) {
return 0;
}
} 

Add account records to achieve

​ In enterprise project development , The addition of records may involve a variety of adding methods , For example, add a single record , Batch add multiple records, etc . There are three ways to add account records : Add a single record to return the number of affected rows 、 Add a single record to return the primary key 、 Batch add multiple records .

Add account records

/**
* Add a single record , Returns the number of affected rows
* @param account
* @return
*/
@Override
public int addAccount(Account account) {
String sql = "insert into tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) values (?,?,?,?,?,now(),now())";
Object[] objs = {account.getAccountName(),account.getAccountType(),
account.getMoney(),account.getRemark(),account.getUserId()};
return jdbcTemplate.update(sql,objs);
} 

The test method

/**
* Add account records , Get the number of rows affected
*/
@Test
public void testAddAccount() {
// Prepare the data to be added
Account account = new Account(" Zhang San "," Bank for economic construction ",100.0," Pin money ",1);
// Call the add method of the object , Returns the number of affected rows
int row = accountDao.addAccount(account);
System.out.println(" Add the number of lines affected by the account :" + row);
} 

Add record to return primary key

/**
* Add a single record , Return primary key
* @param account
* @return
*/
@Override
public int addAccountHasKey(Account account) {
String sql = "insert into tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) values (?,?,?,?,?,now(),now())";
// Definition keyHolder object Get the record primary key value
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
// precompile sql sentence , And set the return primary key
PreparedStatement ps =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// Set parameters
ps.setString(1,account.getAccountName());
ps.setString(2,account.getAccountType());
ps.setDouble(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setInt(5,account.getUserId());
return ps;
},keyHolder);
// Get the returned primary key
Integer key = keyHolder.getKey().intValue();
return key;
} 

The test method

/**
* Add account records , Return primary key
*/
@Test
public void testAddAccountHasKey() {
// Prepare the data to be added
Account account = new Account(" Li Si "," China Merchants Bank ",200.0," Part time fee ",2);
// Call the add method of the object , Return primary key
int key = accountDao.addAccountHasKey(account);
System.out.println(" Add the primary key returned by the account :" + key);
} 

Batch add account records

/**
* Add multiple records , Returns the number of affected rows
* @param accounts
* @return
*/
@Override
public int addAccountBatch(final List<Account> accounts) {
String sql = "insert into tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) values (?,?,?,?,?,now(),now())";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i)
throws SQLException {
// Set parameters
preparedStatement.setString(1,accounts.get(i).getAccountName());
preparedStatement.setString(2,accounts.get(i).getAccountType());
preparedStatement.setDouble(3,accounts.get(i).getMoney());
preparedStatement.setString(4,accounts.get(i).getRemark());
preparedStatement.setInt(5,accounts.get(i).getUserId());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
} 

The test method

/**
* Batch add data , Returns the number of affected rows
*/
@Test
public void testAddAccountBatch() {
// Prepare the data to be added
Account account = new Account(" Wang Wu "," Agricultural bank of ",2000.0," Wages ",3);
Account account2 = new Account(" Zhao Liu "," The bank of China, ",280.0," Bonus ",3);
Account account3 = new Account(" Panax notoginseng "," Industrial and Commercial Bank of China ",800.0," Pin money ",3);
List<Account> accountList = new ArrayList<>();
accountList.add(account);
accountList.add(account2);
accountList.add(account3);
// Call the add method of the object , Return primary key
int rows = accountDao.addAccountBatch(accountList);
System.out.println(" Batch add the number of lines affected by the account :" + rows);
} 

Account record query implementation

​ There are three ways to query account records , Query the number of all account records of the specified user , Query the details of a single account record , Query the specified user account records by multiple conditions .

Query the total number of user account records

/**
* Query the total number of account records of the specified user , Number of records returned
* @param userId
* @return
*/
@Override
public int queryAccountCount(Integer userId) {
String sql = "select count(1) from tb_account where user_id = ?";
int count = jdbcTemplate.queryForObject(sql,Integer.class,userId);
return count;
} 

The test method

/**
* Query the total number of user account records , Returns the total number of records
*/
@Test
public void testQueryAccountCount(){
// Inquire about ID by 1 The total number of account records for users of
int total = accountDao.queryAccountCount(1);
System.out.println(" Total number of records :" + total);
} 

Query the specified account record details

/**
* Query the details of an account record , Return account object
* @param accountId
* @return
*/
@Override
public Account queryAccountById(Integer accountId) {
String sql = "select * from tb_account where account_id = ?";
Account account = jdbcTemplate.queryForObject(sql, new Object[]{accountId}, (resultSet, i) -> {
Account acc = new Account();
acc.setAccountId(resultSet.getInt("account_id"));
acc.setMoney(resultSet.getDouble("money"));
acc.setAccountName(resultSet.getString("account_name"));
acc.setAccountType(resultSet.getString("account_type"));
acc.setRemark(resultSet.getString("remark"));
acc.setCreateTime(resultSet.getDate("create_time"));
acc.setUpdateTime(resultSet.getDate("update_time"));
acc.setUserId(resultSet.getInt("user_id"));
return acc;
});
return account;
} 

The test method

/**
* Query the record details of the specified account , Return account object
*/
@Test
public void testQueryAccountById(){
// Inquire about ID by 1 The details of the account records of
Account account = accountDao.queryAccountById(1);
System.out.println(" Account details :" + account.toString());
} 

Query user account records by multiple conditions

/**
* Query the account list of the specified user by multiple conditions , Return account set
* @param userId user Id
* @param accountName title of account ( Fuzzy query )
* @param accountType Account type
* @param createTime Account creation time
* @return
*/
@Override
public List<Account> queryAccountsByParams(Integer userId, String accountName, String accountType,
String createTime) {
String sql = "select * from tb_account where user_id = ? ";
List<Object> params = new ArrayList<>();
params.add(userId);
// Judge whether there is a conditional query
// If the account name is not empty , Fuzzy matching by account name
if (StringUtils.isNotBlank(accountName)) {
sql += " and account_name like concat('%',?,'%') ";
params.add(accountName);
}
// If the account type is not empty , By specifying the query type
if (StringUtils.isNotBlank(accountType)) {
sql += " and account_type = ? ";
params.add(accountType);
}
// If the creation time is not empty , Query account records with creation time greater than the specified time
if (StringUtils.isNotBlank(createTime)) {
sql += " and create_time > ? ";
params.add(createTime);
}
// Convert a collection to an array
Object[] objs = params.toArray();
List<Account> accountList = jdbcTemplate.query(sql, objs, (resultSet, rowNum) -> {
Account acc = new Account();
acc.setAccountId(resultSet.getInt("account_id"));
acc.setMoney(resultSet.getDouble("money"));
acc.setAccountName(resultSet.getString("account_name"));
acc.setAccountType(resultSet.getString("account_type"));
acc.setRemark(resultSet.getString("remark"));
acc.setCreateTime(resultSet.getDate("create_time"));
acc.setUpdateTime(resultSet.getDate("update_time"));
acc.setUserId(resultSet.getInt("user_id"));
return acc;
});
return accountList;
} 

The test method

/**
* Query user's account records by multiple conditions , Return account set
*/
@Test
public void testQueryAccountByParams(){
// Query the user's account list
List<Account> accountList = accountDao.queryAccountsByParams(3,null,null,null);
// Query the user's account list by specified conditions
List<Account> accountList02 = accountDao.queryAccountsByParams(3," Zhang ",null,null);
System.out.println(accountList.toString());
System.out.println(accountList02.toString());
} 

Account record update implementation

Update account records

/**
* Update designated account records , Returns the number of affected rows
* @param account
* @return
*/
@Override
public int updateAccountById(Account account) {
String sql = "update tb_account set account_name = ?, account_type = ?, " +
" money = ? ,remark = ?,user_id = ? ,update_time = now() " +
" where account_id = ? ";
Object[] objs = {account.getAccountName(),account.getAccountType(),
account.getMoney(), account.getRemark(),account.getUserId(),
account.getAccountId()};
return jdbcTemplate.update(sql,objs);
} 

The test method

/**
* Update designated account records , Returns the number of affected rows
*/
@Test
public void testUpdateAccount(){
// Prepare the data to be modified
Account account = new Account(" Zhang San 1"," Bank for economic construction 1",500.0," Double your allowance ",1);
account.setAccountId(1);
int row = accountDao.updateAccountById(account);
System.out.println(" Modify the account to return the number of affected lines :" + row);
} 

Batch update account records

/**
* Batch new account records , Returns the number of affected rows
* @param accounts
* @return
*/
@Override
public int updateAccountBatch(List<Account> accounts) {
String sql = "update tb_account set account_name = ?, account_type = ?, " +
" money = ? ,remark = ?,user_id = ? ,update_time = now() " +
" where account_id = ? ";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
// Set parameters
ps.setString(1,accounts.get(i).getAccountName());
ps.setString(2,accounts.get(i).getAccountType());
ps.setDouble(3,accounts.get(i).getMoney());
ps.setString(4,accounts.get(i).getRemark());
ps.setInt(5,accounts.get(i).getUserId());
ps.setInt(6,accounts.get(i).getAccountId());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
} 

The test method

/**
* Batch update account records , Returns the number of affected rows
*/
@Test
public void testUpdateAccountBatch(){
// Prepare the data to be modified
Account account = new Account("a3"," Bank for economic construction 3",300.0," Double your allowance 3",3);
account.setAccountId(3);
Account account2 = new Account("a4"," Bank for economic construction 4",400.0," Double your allowance 4",3);
account2.setAccountId(4);
List<Account> accountList = new ArrayList<>();
accountList.add(account);
accountList.add(account2);
int rows = accountDao.updateAccountBatch(accountList);
System.out.println(" Batch modify account records and return the number of affected lines :" + rows);
} 

Account record deletion

Delete account records

/**
* Delete account records , Returns the number of affected rows
* @param accountId
* @return
*/
@Override
public Integer deleteAccoutById(Integer accountId) {
String sql = "delete from tb_account where account_id= ? ";
Object[] objs = {accountId};
return jdbcTemplate.update(sql,objs);
} 

The test method

/**
* Delete account records , Returns the number of affected rows
*/
@Test
public void testDeleteAccount(){
// Delete ID by 1 Account records of
int row = accountDao.deleteAccoutById(1);
System.out.println(" The number of account rows returned is affected by the deletion :" + row);
} 

Delete account records in batch

 /**
* Delete account records in batch , Returns the number of affected rows
* @param ids
* @return
*/
@Override
public int deleteAccountBatch(Integer[] ids) {
String sql = "delete from tb_account where account_id = ?";
int row = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1,ids[i]);
}
@Override
public int getBatchSize() {
return ids.length;
}
}).length;
return row;
} 

The test method

/**
* Delete account records in batch , Returns the number of affected rows
*/
@Test
public void testDeleteAccountBatch(){
// To delete multiple id Account records of
Integer[] ids = new Integer[]{2,3};
int rows = accountDao.deleteAccountBatch(ids);
System.out.println(" Delete account records in batch and return the number of affected lines :" + rows);
} 

Spring Transaction control

Transfer scenario simulation implementation

Interface method definition

/**
* income
* @param tarAid Income amount account ID
* @param money The amount of income
* @return
*/
public int inAccount(Integer tarAid, Double money);
/**
* spending
* @param outAid The account of the amount spent ID
* @param money The amount of expenditure
* @return
*/
public int outAccount(Integer outAid, Double money); 

Implement the corresponding interface

​ For the transfer involving both parties' accounts and the corresponding transfer amount , So there are two ways to enter accounts and pay accounts .

/**
* Account income
* @param tarAid Account ID
* @param money The amount of income
* @return
*/
@Override
public int inAccount(Integer tarAid, Double money) {
// Modify designation ID Amount of ( Add the amount )
String sql = "update tb_account set money = money + ? where account_id = ? ";
Object[] objs = {money, tarAid};
return jdbcTemplate.update(sql,objs);
}
/**
* Account expenses
* @param outAid Account ID
* @param money The amount of expenditure
* @return
*/
@Override
public int outAccount(Integer outAid, Double money) {
// Modify designation ID Amount of ( Less the amount )
String sql = "update tb_account set money = money - ? where account_id = ? ";
Object[] objs = {money, outAid};
return jdbcTemplate.update(sql,objs);
} 

Transfer method to achieve

package com.xxxx.service;
import com.xxxx.dao.IAccountDao;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
@Service
public class AccountService {
@Resource
private IAccountDao accountDao;
/**
* Transfer business operation
* @param outAid Expense account
* @param inAid Income account
* @param money The amount of expenditure / The amount of income
* @return
*/
public int updateAccountByTransfer(Integer outAid, Integer inAid, Double money){
int row = 0;
/**
* Zhang San transfers money to Li Si's account 100 element
* The amount of Zhang San's account - 100
* The amount of Li Si's account + 100
*/
// spending , Modify the amount and return the number of lines affected
int outRow = accountDao.outAccount(1,100.0);
// income , Modify the amount and return the number of lines affected
int inRow = accountDao.inAccount(2,100.0);
// When both operations are successful , Transfer succeeded
if (outRow == 1 && inRow == 1) {
row = 1; // success
}
return row;
}
} 

​ Think about the code and you'll see , There is no guarantee that the program is running service There is no exception in layer business code , If you pass jdbc The way to handle transactions , You need to control the transaction manually , In this way, all business methods involving transaction control need to be handled manually by developers , Unable to meet the needs of production .

Spring The concept of affairs

Four characteristics of transactions (ACID)

  • Atomicity (Atomicity)

    ​ Life and death , All or nothing , All or nothing !

  • Uniformity (Consistency)

    ​ The transaction is before and after execution , The data in the database should be consistent .( Such as the transfer process Data must be consistent after account operation )

  • Isolation, (Isolation)

    ​ Transactions and the execution of transactions should be isolated from each other .( Multiple roles must be able to operate the unified record without any interference ), Of course, it's impossible without influence , In order to minimize the impact level , Limit by isolation level :

    ​ 1. READ_UNCOMMITTED ( Read uncommitted )

    ​ The transaction level with the lowest isolation level . At this level of isolation , Will cause dirty reading 、 No repeated reading or phantom reading .

    ​ 2. READ_COMMITTED ( Read submitted )

    ​ What I read is the submitted value of others . At this level of isolation , It will lead to non repetition and unreal reading , But avoid dirty reading .

    ​ 3. REPEATABLE_READ ( Repeatable )

    ​ At this level of isolation , It will trigger unreal reading , But avoid dirty reading 、 It can't be read repeatedly .

    ​ 4. SERIALIZABLE ( Serialization )

    ​ The strictest level of isolation . stay Serializable Under isolation level , All transactions are executed in order .

    ​ Dirty reading 、 It can't be read repeatedly 、 Unreal reading doesn't show up .

  • persistence (Durability)

    ​ After the transaction is committed , Changes to the data in the database are permanent .

Spring Transaction core interface

​ Spring The implementation of transaction management has many details , If we have a general understanding of the whole interface framework, it will be very helpful for us to understand transactions , Let's talk about Spring To understand Spring Specific strategies for implementing transactions .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-TopwzVC2-1611309424483)(/SpringJDBC-03.png)]

​ Spring Not directly in charge of affairs , Instead, there are multiple transaction managers , They delegated responsibility for the management of affairs to Hibernate perhaps JTA And other related platform framework transactions provided by the persistence mechanism .

​ Spring The interface of the transaction manager is org.springframework.transaction.PlatformTransactionManager, Through this interface ,Spring For various platforms such as JDBC、Hibernate The corresponding transaction manager is provided , But the specific implementation is the business of each platform . The contents of this interface are as follows :

public interface PlatformTransactionManager(){
// from TransactionDefinition obtain TransactionStatus object
TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;
// Submit
void commit(TransactionStatus status) throws TransactionException;
// Roll back
void rollback(TransactionStatus status) throws TransactionException;
} 

​ From here we can see the specific transaction management mechanism Spring It's transparent , It doesn't care about those , Those are the concerns of each platform , therefore Spring One of the advantages of transaction management is that for different transactions API Provide a consistent programming model , Such as JTA、JDBC、Hibernate、JPA. The following describes the mechanism of each platform framework to achieve transaction management .

JDBC Business

​ If you use JDBC To persist , At this time to use DataSourceTransactionManager To handle transaction boundaries . In order to use DataSourceTransactionManager, You need to use the following XML Assemble it into the context definition of the application :

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean> 

​ actually ,DataSourceTransactionManager By calling java.sql.Connection To manage things , The latter is through DataSource Acquired . Connected by calling commit() Method to commit a transaction , Again , If the transaction fails, it will call rollback() Method to roll back .

Hibernate Business

​ If the persistence of an application is done through Hibernate Realized , Then you need to use HibernateTransactionManager. about Hibernate3, Need to be in Spring Add the following statement to the context definition :

<bean id="transactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean> 

​ sessionFactory Property needs to assemble a Hibernate Of session factory ,HibernateTransactionManager The implementation details of is that it delegates the responsibility of transaction management to org.hibernate.Transaction object , The latter is from Hibernate Session Obtained in . When the transaction completes successfully ,HibernateTransactionManager Will call Transaction Object's commit() Method , conversely , Will call rollback() Method .

Java Persistence API Business (JPA)

​ Hibernate It's been... For years Java Persistence standards , But now Java Persistence API As the real Java Persistence standards come into view . If you plan to use JPA Words , Then you need to use it Spring Of JpaTransactionManager To deal with affairs . You need to Spring Configured as in JpaTransactionManager:

<bean id="transactionManager"
class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean> 

​ JpaTransactionManager Just assemble one JPA Physical management factory (javax.persistence.EntityManagerFactory Any implementation of the interface ). JpaTransactionManager Will be associated with the JPA EntityManager Work together to build a business .

Java Native API Business

​ If the application does not use the transaction management described above , Or across multiple transaction management sources ( For example, two or more different data sources ), You need to use JtaTransactionManager:

<bean id="transactionManager"
class="org.springframework.transaction.jta.JtaTransactionManager">
<property name="transactionManagerName" value="java:/TransactionManager" />
</bean> 

​ JtaTransactionManager Delegate responsibility for the management of affairs to javax.transaction.UserTransaction and javax.transaction.TransactionManager object , Where the transaction is successfully completed through UserTransaction.commit() Method submission , Transaction failed through UserTransaction.rollback() Method rollback .

Spring Transaction control configuration

​ adopt jdbc Persistent transactions , There are two ways to implement transaction configuration, namely :Xml To configure , Annotation configuration .

XML To configure

Add namespace

stay spring.xml Add transaction of configuration file and aop The namespace of

Business

xmlns:tx="http://www.springframework.org/schema/tx"
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd 

AOP

xmlns:aop="http://www.springframework.org/schema/aop"
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd 

The configuration is as follows

<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:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://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/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd"> 
Set up aop agent
<!-- Turn on AOP agent -->
<aop:aspectj-autoproxy /> 
Configure transaction manager
<!-- Transaction manager definition -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!-- data source -->
<property name="dataSource" ref="dataSource"></property>
</bean> 
Configure transaction related notifications

Generally speaking, adding, deleting and modifying methods are adopted propagation=Required, For query methods, use read-only=“true”

<!-- Configure transaction notifications transaction-manager Property indicates which transaction manager manages this transaction notification -->
<!--
tx:method Properties of :
name
Is a must , Represents the method name associated with the transaction property ( Business method name ), Refine the entry point .
wildcard (*) Can be used to specify a batch of methods associated with the same transaction properties .
Such as :'get*'、'handle*'、'on*Event' wait .
propagation
It's not necessary , The default value is REQUIRED
Indicates the behavior of transaction propagation , Include :
REQUIRED,SUPPORTS,MANDATORY,NEVER
REQUIRES_NEW,NOT_SUPPORTED,NESTED
isolation
It's not necessary , The default value is DEFAULT
Indicates the transaction isolation level ( Isolation level of database )
timeout
It's not necessary , The default value is -1( Never time out )
Indicates the time that the transaction timed out ( In seconds )
read-only
It's not necessary , The default value is false It's not read-only
Indicates whether the transaction is read-only
rollback-for
It's not necessary
Indicates that a rollback will be triggered Exception(s); Separated by commas .
Such as :'com.foo.MyBusinessException,ServletException'
no-rollback-for
It's not necessary
Indicates that it is not triggered to roll back Exception(s); Separated by commas .
Such as :'com.foo.MyBusinessException,ServletException'
whatever RuntimeException Will trigger transaction rollback
-->
<tx:advice id="txAdvice" transaction-manager="txManager">
<!-- To add update delete query All the methods in the beginning are transacted -->
<tx:attributes>
<!-- Define what methods need to use transactions name Represents the method name ( Or method matching )-->
<!-- Match with add All the methods at the beginning join the transaction -->
<tx:method name="add*" propagation="REQUIRED" />
<!-- Match with update All the methods at the beginning join the transaction -->
<tx:method name="update*" propagation="REQUIRED" />
<!-- Match with delete All the methods at the beginning join the transaction -->
<tx:method name="delete*" propagation="REQUIRED" />
<!-- Match with query All the methods at the beginning join the transaction -->
<tx:method name="query*" read-only="true" />
</tx:attributes>
</tx:advice> 
 Introduction to transaction propagation behavior :
@Transactional(propagation=Propagation.REQUIRED)
If there is a transaction , Then join the transaction , If not, create a new one ( By default )
@Transactional(propagation=Propagation.NOT_SUPPORTED)
The container does not open a transaction for this method
@Transactional(propagation=Propagation.REQUIRES_NEW)
Whether there is a transaction or not , Create a new transaction , Original suspend , New execution completed , Carry on with the old business
@Transactional(propagation=Propagation.MANDATORY)
Must be executed in an existing transaction , Otherwise, throw an exception
@Transactional(propagation=Propagation.NEVER)
Must be executed in a transaction that does not exist , Otherwise, throw an exception ( And Propagation.MANDATORY contrary )
@Transactional(propagation=Propagation.SUPPORTS)
If other bean Call this method , Among others bean Transaction declared in , Then use the business .
If other bean No transaction declared , No business then .
@Transactional(propagation=Propagation.NESTED)
Support current transaction , If the current transaction exists , A nested transaction is executed , If there is no current transaction , Just create a new transaction . 
To configure aop
<!-- aop Section definition ( Entry point and notification ) -->
<aop:config>
<!-- Set entry point Set the method that needs to be intercepted -->
<aop:pointcut expression="execution(* com.xxxx.service..*.*(..) )" id="cut" />
<!-- Set up notifications Notice of affairs -->
<aop:advisor advice-ref="txAdvice" pointcut-ref="cut"/>
<aop:adviso 

Annotation configuration

Configure transaction manager
<!-- spring Annotated transaction statement -->
<!-- Transaction manager definition -->
<bean id="txManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean> 
Configure annotation support
<tx:annotation-driven transaction-manager="txManager"/> 
Add transaction annotation to the method

Service Methods add transaction annotations to the methods that need to add transactions

@Override
@Transactional(propagation=Propagation.REQUIRED)
public void saveUser(String userName,String userPwd){
User user1=new User();
user1.setUserName(userName);
user1.setUserPwd(userPwd);
userDao.saveUser(user1);
userDao.delUserById(2);
} 

remarks : Default spring The transaction is only in the event of an uncaught runtimeexcetpion Roll back when .

spring aop The principle of exception capture :

The intercepted method needs to throw an exception explicitly , It can't be dealt with in any way , such aop Agents can catch exceptions to methods , To roll back , By default aop Capture only runtimeexception It's abnormal , However, specific exceptions can be caught and rolled back through configuration, in other words, in service Do not use try catch Or in catch Add... At the end throw new RunTimeexcetpion(), So that when the program is abnormal, it can be aop Capture and roll back .

版权声明
本文为[Xu Yuxi]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210122181210214z.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云