Comprehensive Handwritten Detailed Explanation of Spring JdbcTemplate & Declarative Transactions

Basic Usage of JdbcTemplate#

1 - Overview of Basic Usage of JdbcTemplate (Understanding)#

JdbcTemplate is an object provided by the Spring framework, which is a simple encapsulation of the cumbersome Jdbc API objects. The Spring framework provides us with many operation template classes. For example: JdbcTemplate for relational data operations, HibernateTemplate, RedisTemplate for NoSQL databases, JmsTemplate for message queues, and so on.

2 - Development Steps for Basic Usage of JdbcTemplate (Understanding)#

① Import spring-jdbc and spring-tx dependencies
② Create database tables and entities
③ Create JdbcTemplate object
④ Execute database operations

3 - Quick Start Code Implementation for Basic Usage of JdbcTemplate (Application)#

Import spring-jdbc and spring-tx dependencies#

<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring‐context</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring‐test</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring‐jdbc</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring‐tx</artifactId>
<version>5.2.8.RELEASE</version>
</dependency>
<!‐‐ AspectJ weaving package ‐‐>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.4</version>
</dependency>
<!‐‐ Data source related ‐‐>
<!‐‐ Druid connection pool ‐‐>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
<!‐‐ MySQL driver ‐‐>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql‐connector‐java</artifactId>
<version>5.1.39</version>
</dependency>
<!‐‐ Servlet related ‐‐>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet‐api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp‐api</artifactId>
<version>2.2.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>

Create database tables and entities

DROP TABLE IF EXISTS account;
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
money DOUBLE NOT NULL
);
INSERT INTO account VALUES (NULL,'Wangcai',1000);
INSERT INTO account VALUES (NULL,'Xiaoqiang',1000);
SELECT * FROM account;

package com.summer.domain;
public class Account {
private int id;
private String name;
private double money;
public int getId() {
return id;
} 
public void setId(int id) {
this.id = id;
} 
public String getName() {
return name;
} 
public void setName(String name) {
this.name = name;
} 
public double getMoney() {
return money;
} 
public void setMoney(double money) {
this.money = money;
}
}

Create JdbcTemplate object
Execute database operations

// Test the development steps of JdbcTemplate
@Test
public void test1(){
// Create data source
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/test");
dataSource.setUsername("root");
dataSource.setPassword("root");
// Create template object
JdbcTemplate template = new JdbcTemplate();
// Set data source
template.setDataSource(dataSource);
// Execute update operation (add, modify, delete)
int i = template.update("INSERT INTO account VALUES (NULL,?,?);", "Ruhua", 1000);
System.out.println(i);
}

4 - Analysis of Spring Generated Template Objects for Basic Usage of JdbcTemplate (Understanding)#

We can delegate the creation of JdbcTemplate to Spring and also delegate the creation of the DataSource to Spring. Inside the Spring container, the DataSource is injected into the JdbcTemplate template object, and then we obtain the JdbcTemplate object from the Spring container to perform operations.

5 - Code Implementation of Spring Generated Template Objects for Basic Usage of JdbcTemplate (Application)#

Configuration as follows:

<!‐‐ Configure data source, delegate data creation to Spring container ‐‐>
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/test" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<!‐‐ Create jdbc template object ‐‐>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
<!‐‐ Configure data source ‐‐>
<property name="dataSource" ref="dataSource" />
</bean>

Test code

// Integrate Spring and JUnit
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateTest {
@Autowired
private JdbcTemplate jdbcTemplate;
// Test Spring-managed JdbcTemplate
@Test
public void test2(){
int i = jdbcTemplate.update("INSERT INTO account VALUES (NULL,?,?);", "Qiuxiang", 1000);
System.out.println(i);
}
}

6 - Code Implementation of Spring Generated Template Objects for Basic Usage of JdbcTemplate#

Extract the database connection information to an external configuration file, separating it from the Spring configuration file, which is beneficial for future maintenance.

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

Modify the configuration file to:

<?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"
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
">
<!‐‐ Load properties configuration file ‐‐>
<context:property‐placeholder location="classpath:jdbc.properties"/>
<!‐‐ Configure data source, delegate data creation to Spring container ‐‐>
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!‐‐ Create jdbc template object ‐‐>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
<!‐‐ Configure data source ‐‐>
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>

7 - Common Operations for Basic Usage of JdbcTemplate - Update Operations (Application)#

// Integrate Spring and JUnit
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
// Inject jdbc template
@Autowired
private JdbcTemplate jdbcTemplate;
// Test update operation
@Test
public void testUpdate(){
jdbcTemplate.update("update account set money = ? where id = ?;",800,1);
}
// Test delete
@Test
public void testDelete(){
jdbcTemplate.update("DELETE from account where id = ?",1);
}
}

8 - Common Operations for Basic Usage of JdbcTemplate - Query Operations (Application)#

package com.summer.test;
import com.summer.domain.Account;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
// Integrate Spring and JUnit
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {
// Inject jdbc template
@Autowired
private JdbcTemplate jdbcTemplate;
// Test update operation
@Test
public void testUpdate(){
    jdbcTemplate.update("update account set money = ? where id = ?;",800,1);
}
// Test delete
@Test
public void testDelete(){
jdbcTemplate.update("DELETE from account where id = ?",1);
} 
// Aggregate query
@Test
public void testQueryCount(){
Long count = jdbcTemplate.queryForObject("select count(*) from account", Long.class);
System.out.println(count);
} 
// Query one
@Test
public void testQueryOne(){
Account account = jdbcTemplate.queryForObject("select * from account where name=?", new
BeanPropertyRowMapper<Account>(Account.class), "Wangcai");
System.out.println(account);
} 
// Query all
@Test
public void testQueryAll(){
List<Account> accountList = jdbcTemplate.query("select * from account", new
BeanPropertyRowMapper<Account>(Account.class));
System.out.println(accountList);
}
}

9 - Knowledge Points for Basic Usage of JdbcTemplate (Understanding, Memorization)#

① Import spring-jdbc and spring-tx dependencies
② Create database tables and entities
③ Create JdbcTemplate object

JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);

④ Execute database operations

Update operation:

jdbcTemplate.update(sql, params)

Query operation:

jdbcTemplate.query(sql, Mapper, params)

jdbcTemplate.queryForObject(sql, Mapper, params)

Declarative Transaction Control#

1. Concept of Transactions#

Concept:

A transaction is an execution unit of a group of operations. In terms of database operations, transactions manage a group of SQL statements, such as adding, modifying, deleting, etc. The consistency of a transaction requires that all operations within this transaction must be executed successfully. If an error occurs during this process, such as a SQL statement failing to execute, then all operations will be rolled back.
Four words to explain transactions (ACID):

atomic (Atomicity): Either all occur or none occur.
consistent (Consistency): Data should not be corrupted.
isolate (Isolation): Operations between users should not be confused.
durable (Durability): Permanently saved, for example, saved in the database, etc.

2. Annotation-Based Declarative Transaction Control#

2.1 What is Declarative Transaction Control#

Spring's declarative transaction control, as the name suggests, uses a declarative approach to handle transactions. The declaration here refers to declaring in the configuration file, using declarative transaction handling in the Spring configuration file instead of code-based transaction handling.

The role of declarative transaction handling:

Transaction management does not intrude on the components being developed. Specifically, business logic objects will not be aware that they are under transaction management, and in fact, they should not be, because transaction management is a system-level service, not part of the business logic. If you want to change the transaction management strategy, you only need to reconfigure it in the definition file.
When transaction management is not needed, it can be removed simply by modifying the configuration file, without changing the code and recompiling, making maintenance extremely convenient.

Note: The underlying implementation of Spring's declarative transaction control is AOP.

Introduce AOP and tx namespaces

<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/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
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring‐beans.xsd">

2.2 Using Annotations to Configure Declarative Transaction Control#

Write AccountDao

@Repository("accountDao")
public class AccountDaoImpl implements AccountDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public void out(String outMan, double money) {
jdbcTemplate.update("update account set money=money‐? where name=?",money,outMan);
} 
public void in(String inMan, double money) {
jdbcTemplate.update("update account set money=money+? where name=?",money,inMan);
}
}

Write AccountService

@Service("accountService")
@Transactional
public class AccountServiceImpl implements AccountService {
@Autowired
private AccountDao accountDao;
@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public void transfer(String outMan, String inMan, double money) {
accountDao.out(outMan,money);
int i = 1/0;
accountDao.in(inMan,money);
}
}

Write applicationContext.xml configuration file

<!‐‐ Previously omitted dataSource, jdbcTemplate ‐‐>
<!‐‐ Platform transaction manager ‐‐>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!‐‐ Transaction annotation-driven ‐‐>
<tx:annotation‐driven/>

2.3 Explanation of Annotation-Based Declarative Transaction Control#

① Use @Transactional to annotate classes or methods that require transaction control. The attributes available for the annotation are the same as those in the XML configuration method, such as isolation level, propagation behavior, etc.
② If the annotation is used on a class, then all methods in that class will use the same set of annotation parameter configurations.
③ If the annotation is used on a method, different methods can adopt different transaction parameter configurations.
④ The XML configuration file must enable transaction annotation-driven with <tx:annotation-driven />

2.4 Knowledge Points#

Key points for configuring annotation-based declarative transaction control

Configuration of transaction notifications (@Transactional annotation configuration)
Configuration of transaction annotation-driven <tx:annotation-driven/>