Spring’s JdbcTemplate

What is the JdbcTemplate?

The JdbcTemplate is a template object provided in the Spring framework that is a simple wrapper around the original tedious Jdbc API objects.

Core object

JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSource dataSource);

Copy the code

Core method

int update(a); Execute add, delete, and modify statementsList<T> query(a); Query multipleT queryForObject(a); Query anewBeanPropertyRowMapper<>(); Implement ORM mapping encapsulationCopy the code

Take a chestnut

Example Query information about all accounts in the database to the Account entity

public class JdbcTemplateTest {

    @Test
    public void testFindAll(a) throws Exception {
        // Create a core object
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtils.getDataSource());
        / / write SQL
        String sql = "select * from account";
        / / SQL execution
        List<Account> list = jdbcTemplate.query(sql, newBeanPropertyRowMapper<>(Account.class)); }}Copy the code

Spring integration JdbcTemplate

• demand

Implement CRUD cases for accounts based on Spring’s XML configuration

• Step analysis

1. Create a Java project, import coordinates 2. Write the AccountDao interface and implementation class 4. Write the AccountService interface and implementation class 5. Write the Spring core configuration file. 6. Write test codeCopy the code

1) Create a Java project and import coordinates

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.15</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.8.13</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
</dependencies>
Copy the code

2) Write the Account entity class

public class Account {

    private Integer id;
    private String name;
    private Double money;
}
Copy the code

3) Write the AccountDao interface and implementation class

public interface AccountDao {

    public List<Account> findAll(a);

    public Account findById(Integer id);

    public void save(Account account);

    public void update(Account account);

    public void delete(Integer id);

}
Copy the code

@Repository
public class AccountDaoImpl implements AccountDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Account> findAll(a) {
        / / write SQL
        String sql = "select * from account";
        / / SQL execution
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Account.class));
    }

    @Override
    public Account findById(Integer id) {
        / / write SQL
        String sql = "select * from account where id = ?";
        / / SQL execution
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Account.class),id);
    }

    @Override
    public void save(Account account) {
        / / write SQL
        String sql = "insert into account values(null,? ,?) ";
        / / SQL execution
        jdbcTemplate.update(sql, account.getName(), account.getMoney());
    }

    @Override
    public void update(Account account) {
        / / write SQL
        String sql = "update account set name = ? ,money = ? where id = ?";
        / / SQL execution
        jdbcTemplate.update(sql, account.getName(),
        account.getMoney(),account.getId());
    }

    @Override
    public void delete(Integer id) {
        / / write SQL
        String sql = "delete from account where id = ?";
        / / SQL executionjdbcTemplate.update(sql, id); }}Copy the code

4) Write the AccountService interface and implementation class

public interface AccountService {

    public List<Account> findAll(a);

    public Account findById(Integer id);

    public void save(Account account);

    public void update(Account account);

    public void delete(Integer id);

}
Copy the code

@Service
public class AccountServiceImpl implements AccountService {

    @Autowired
    private AccountDao accountDao;

    @Override
    public List<Account> findAll(a) {
        return accountDao.findAll();
    }

    @Override
    public Account findById(Integer id) {
        return accountDao.findById(id);
    }

    @Override
    public void save(Account account) {
        accountDao.save(account);
    }

    @Override
    public void update(Account account) {
        accountDao.update(account);
    }

    @Override
    public void delete(Integer id) { accountDao.delete(id); }}Copy the code

5) Write the Spring core configuration file

      
<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">

    <context:component-scan base-package="com.lagou"/>

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>
</beans>
Copy the code

6) Write test code

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class AccountServiceTest {

    @Autowired
    private AccountService accountService;

    // Test save
    @Test
    public void testSave(a) {
        Account account = new Account();
        account.setName("lucy");
        account.setMoney(100d);
        accountService.save(account);
    }

    // Test the query
    @Test
    public void testFindById(a) {
        Account account = accountService.findById(3);
        System.out.println(account);
    }

    // Test query all
    @Test
    public void testFindAll(a) {
        List<Account> accountList = accountService.findAll();
        for(Account account : accountList) { System.out.println(account); }}// Test the modification
    @Test
    public void testUpdate(a) {
        Account account = new Account();
        account.setId(3);
        account.setName("rose");
        account.setMoney(2000d);
        accountService.update(account);
    }

    // Test delete
    @Test
    public void testDelete(a) {
        accountService.delete(3); }}Copy the code

Transfer Case

• Step analysis

1. Create a Java project, import coordinates 2. Write the AccountDao interface and implementation class 4. Write the AccountService interface and implementation class 5. Write the Spring core configuration file. 6. Write test codeCopy the code

1) Create a Java project and import coordinates

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.15</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.8.13</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.1.5. RELEASE</version>
    </dependency>
</dependencies>
Copy the code

2) Write the Account entity class

public class Account {

    private Integer id;
    private String name;
    private Double money;
    // setter getter....
}
Copy the code

3) Write the AccountDao interface and implementation class

public interface AccountDao {

    public void out(String outUser, Double money);

    public void in(String inUser, Double money);

}
Copy the code

@Repository
public class AccountDaoImpl implements AccountDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void out(String outUser, Double money) {
        jdbcTemplate.update("update account set money = money - ? where name = ?", money, outUser);
    }

    @Override
    public void in(String inUser, Double money) {
        jdbcTemplate.update("update account set money = money + ? where name = ?", money, inUser); }}Copy the code

4) Write the AccountService interface and implementation class

public interface AccountService {

    public void transfer(String outUser, String inUser, Double money);
}
Copy the code

@Service
public class AccountServiceImpl implements AccountService {

    @Autowired
    private AccountDao accountDao;

    @Override
    public void transfer(String outUser, String inUser, Double money) { accountDao.out(outUser, money); accountDao.in(inUser, money); }}Copy the code

5) Write the Spring core configuration file

      
<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">

    <! --IOC annotation scan -->
    <context:component-scan base-package="com.lagou"/>

    <! Load the JDBC configuration file -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <! Give the database connection pool to the IOC container
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <! Pass the JdbcTemplate to the IOC container -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg name="dataSource" ref="dataSource"></constructor-arg>
    </bean>

</beans>
Copy the code

6) Write test code

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class AccountServiceTest {

    @Autowired
    private AccountService accountService;

    @Test
    public void testTransfer(a) throws Exception {
        accountService.transfer("tom"."jerry".100d); }}Copy the code

Spring’s transaction

Transaction control in Spring

Spring’s transaction control can be divided into programmatic and declarative transaction control.

• programmatic

The developer couples the transactional code directly with the business code and does not use it in real development.

• declarative

Developers use the configuration of the way to achieve transaction control, business code and transaction code implementation decouple, using AOP ideas.

Programmatic transactions control related objects

PlatformTransactionManager

PlatformTransactionManager interface, it is spring’s transaction manager, provided we commonly used method of operation affairs.

methods	instructions
TransactionStatus getTransaction(TransactionDefinition definition);	Get the status information for the transaction
Void commit (TransactionStatus status);	Commit the transaction
Void the rollback (TransactionStatus status);	Roll back the transaction

• Note:

* PlatformTransactionManager is the interface type, different Dao layer technology has different implementation classes. * Dao layer technology is jdbcTemplate or mybatis: DataSourceTransactionManager * Dao layer technology is hibernate when: HibernateTransactionManager * Dao layer technology is: when the JPA JpaTransactionManagerCopy the code

TransactionDefinition

The TransactionDefinition interface provides the TransactionDefinition information (transaction isolation level, transaction propagation behavior, and so on)

methods	instructions
int getIsolationLevel()	Get the isolation level of the transaction
int getPropogationBehavior()	Get the propagation behavior of the transaction
int getTimeout()	Get timeout
boolean isReadOnly()	Whether the read-only

• 1) Transaction isolation level

The isolation level can be set to solve problems caused by concurrent transactions, such as dirty reads, unrepeatable reads, and virtual reads (phantom reads).

* ISOLATION_DEFAULT Uses the database default level * ISOLATION_READ_UNCOMMITTED Read uncommitted * ISOLATION_READ_COMMITTED Read committed * ISOLATION_REPEATABLE_READ * ISOLATION_SERIALIZABLE serializationCopy the code

• 2) Transaction propagation behavior

Transaction propagation behavior refers to how transaction control should occur when a business method is called by another business method.

parameter	instructions
REQUIRED	If there is no transaction, create a new one. If there is already one, join it. General selection (default)
SUPPORTS	Support for current transactions, non-transactionally if there is no transaction currently (no transaction)
MANDATORY	Use the current transaction and throw an exception if there is no transaction currently
REQUERS_NEW	Create a new transaction and suspend the current transaction if it is currently in one
NOT_SUPPORTED	Performs the operation nontransactionally, suspending the current transaction if one exists
NEVER	Runs nontransactionally and throws an exception if a transaction currently exists
NESTED	If a transaction currently exists, it is executed within a nested transaction. If there are no transactions currently, something like REQUIRED is performed

* Read-only: You are advised to set this parameter to read-only. * timeout: The default value is -1. There is no timeout limit. If yes, set the parameter in secondsCopy the code

TransactionStatus

The TransactionStatus interface provides a detailed running state of the transaction.

methods	instructions
boolean isNewTransaction()	Is it a new transaction
boolean hasSavepoint()	Is the rollback point
boolean isRollbackOnly()	Whether the transaction is rolled back
boolean isCompleted()	Transaction complete

The relationship can be understood simply: the transaction manager manages transactions by reading transaction definition parameters and then generates a series of transaction states.

The implementation code

1) Configuration files

<! -- Transaction manager to IOC-->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>
Copy the code

2) Business layer code

@Service
public class AccountServiceImpl implements AccountService {
    @Autowired
    private AccountDao accountDao;

    @Autowired
    private PlatformTransactionManager transactionManager;

    @Override
    public void transfer(String outUser, String inUser, Double money) {
        // Create a transaction definition object
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        // Set whether to read-only. False Supports transactions
        def.setReadOnly(false);
        // Set the transaction isolation level, which can repeat the mysql default level
        def.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
        // Set transaction propagation behavior, there must be a transaction
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        // Configure the transaction manager
        TransactionStatus status = transactionManager.getTransaction(def);

        try {
            / / transfer
            accountDao.out(outUser, money);
            accountDao.in(inUser, money);

            // Commit the transaction
            transactionManager.commit(status);
        } catch (Exception e) {
            e.printStackTrace();
            // Rollback the transactiontransactionManager.rollback(status); }}}Copy the code

Knowledge summary

Transaction control in Spring is primarily implemented through these three apis

* PlatformTransactionManager responsible for transaction management, it is an interface, Subclasses are responsible for the work * TransactionDefinition defines the parameters of a transaction * TransactionStatus represents a real-time state in which the transaction is runningCopy the code

Understand the relationship between the three: the transaction manager manages the transaction by reading transaction definition parameters and then generates a series of transaction states.

Declarative transaction control based on XML

Handle transactions declaratively instead of code in the Spring configuration file. The bottom layer uses AOP thought to achieve.

Declarative transaction control specifies matters:

• Core business code (target object) (Who is the pointcut?)
• Transaction enhancement code (Spring already provides a transaction manager) (Who is the notification?)
• Section configuration (how to configure the section?)

Quick start

• demand

Use Spring declarative transactions to control transfer transactions.

• Step analysis

Introduction of tx namespace 2. Transaction manager notification configuration 3. Transaction manager AOP configuration 4. Test transaction control transfer business codeCopy the code

1) Introduce the TX namespace

      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w2.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/s chema/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">

</beans>
Copy the code

2) Transaction manager notification configuration

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

<! -- Notification enhancement -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <! Define transaction attributes -->
    <tx:attributes>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>
Copy the code

3) Transaction manager AOP configuration

<! - aop configuration - >
<aop:config>
    <! -- Section Configuration -->
    <aop:advisor advice-ref="txAdvice"
                 pointcut="execution(* com.lagou.serivce.. *. * (..) )">
    </aop:advisor>
</aop:config>
Copy the code

4) Test transaction control transfer business code

@Override
public void transfer(String outUser, String inUser, Double money) {
    accountDao.out(outUser, money);
    // create an exception
    int i = 1 / 0;
    accountDao.in(inUser, money);
}
Copy the code

Transaction parameter configuration details

<tx:method name="transfer" isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="1" read-only="false"/>* name: name of the pointcut method * Isolation: isolation level of the transaction * propogation: propagation behavior of the transaction * timeout: timeout period * read-only: whether the transaction is read-onlyCopy the code

• CRUD common configuration

<tx:attributes>
    <tx:method name="save*" propagation="REQUIRED"/>
    <tx:method name="delete*" propagation="REQUIRED"/>
    <tx:method name="update*" propagation="REQUIRED"/>
    <tx:method name="find*" read-only="true"/>
    <tx:method name="*"/>
</tx:attributes>
Copy the code

Knowledge summary

* Platform transaction manager configuration * transaction notification configuration * transaction AOP woven into the configurationCopy the code

Annotation-based declarative transaction control

Commonly used annotations

Step analysis

1. Modify the Service layer to add transaction annotations 2. Modify the Spring core configuration file to enable transaction annotationsCopy the code

Step analysis

@Service
public class AccountServiceImpl implements AccountService {

    @Autowired
    private AccountDao accountDao;

    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ, timeout = -1, readOnly = false)
    @Override
    public void transfer(String outUser, String inUser, Double money) {
        accountDao.out(outUser, money);
        int i = 1 / 0; accountDao.in(inUser, money); }}Copy the code

2) Modify the Spring core configuration file to enable transaction annotation support

      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w2.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">

    <! -- Omitted datsSource, jdbcTemplate, component scan configuration -->

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

    <! Transaction annotation support -->
    <tx:annotation-driven/>
</beans>
Copy the code

Pure annotations

Core configuration class

@Configuration // Declare the configuration class for Spring
@ComponentScan("com.lagou") / / scan packages
@Import(DataSourceConfig.class) // Import other configuration classes
@EnableTransactionManagement // Transaction annotation driven
public class SpringConfig {

    @Bean
    public JdbcTemplate getJdbcTemplate(@Autowired DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean("transactionManager")
    public PlatformTransactionManager getPlatformTransactionManager(@Autowired DataSource dataSource) {
        return newDataSourceTransactionManager(dataSource); }}Copy the code

Data source configuration class

@PropertySource("classpath:jdbc.properties")
public class DataSourceConfig {
    @Value("${jdbc.driver}")
    private String driver;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;

    @Bean
    public DataSource getDataSource(a) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        returndataSource; }}Copy the code

Knowledge summary

* the platform transaction manager configuration (XML, annotations) * transaction notification configuration (@ Transactional annotation configuration) * transaction annotation driven configuration - driven / > < tx: an annotation, @ EnableTransactionManagementCopy the code

Spring integrated Web environment

ApplicationContext indicates how to obtain the ApplicationContext

Application context object is through a new ClasspathXmlApplicationContext (spring configuration file) way to get, But every beans from the container to write new ClasspathXmlApplicationContext (spring configuration file), the disadvantages of this configuration file is loaded multiple times, to create multiple application context object. Version:0.9 StartHTML:0000000105 EndHTML:0000002684 StartFragment:0000000141 EndFragment:0000002644

Solution analysis: In a Web project, you can use ServletContextListener to listen to the start of the Web application. When the Web application starts, you can load the Spring configuration file and create the ApplicationContext object ApplicationContext. Store it in the largest domain, the servletContext domain, so that the ApplicationContext ApplicationContext object can be retrieved from the domain anywhere.

Spring provides tools for obtaining application context

Spring provides a ContextLoaderListener that encapsulates the above functionality. This listener loads the Spring configuration file internally, creates the application context object, and stores it in the ServletContext domain. Provides a client tool WebApplicationContextUtils for users get application context object.

So there are only two things we need to do:

1. Configure ContextLoaderListener in web.xml (import Spring-Web coordinates)
2. Use application context object WebApplicationContextUtils ApplicationContext

implementation

1) Import the coordinates of Spring integrated Web

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.1.5. RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-web</artifactId>
    <version>5.1.5. RELEASE</version>
</dependency>
Copy the code

2) Configure the ContextLoaderListener

<! -- Global parameters -->
<context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:applicationContext.xml</param-value>•
</context-param>
<! --Spring listener -->
<listener>
    <listener-class>
        org.springframework.web.context.ContextLoaderListener
    </listener-class>
</listener>
Copy the code

3) Obtain the application context object through the tool

ApplicationContext applicationContext = WebApplicationContextUtils.getWebApplicationContext(servletContext);
Object obj = applicationContext.getBean("id");
Copy the code