JdbcTemplate

JdbcTemplate is an object provided in the spring framework, which functions as QueryRunning and is a simple encapsulation of the original Jdbc API object. The spring framework provides many template classes that are facing different database types.

Operational relationship data:

JdbcTemplate
HibernateTemplate

Operate nosql database

RedisTemplate

Operation message queue

JmsTemplate

Two ways to use JdbcTemplate

Method 1: define JdbcTemplate in dao
XML configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd">
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!--Configure password, connection, etc-->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test02"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>

    </bean>
    <!-- To configure a database operation template: JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

DaoImpl code

@Repository
public class AccountDaoImpl2 implements IAccountDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> accounts = jdbcTemplate.query("select * from account where id = ?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }

    @Override
    public Account findAccountByName(String accountName) {
        List<Account> accounts = jdbcTemplate.query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("Result set is not unique");
        }
        return accounts.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        jdbcTemplate.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

analysis

Method 2: let dao inherit JdbcDaoSupport

The sample code is as follows:

/**
 * Account persistence layer implementation class
 */
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {

    @Override
    public Account findAccountById(Integer accountId) {
        List<Account> accounts = super.getJdbcTemplate().query("select * from account where id = ?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }

    @Override
    public Account findAccountByName(String accountName) {
        List<Account> accounts = super.getJdbcTemplate().query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("Result set is not unique");
        }
        return accounts.get(0);
    }

    @Override
    public void updateAccount(Account account) {
        super.getJdbcTemplate().update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

Transaction control in Spring

Transaction control is to ensure consistency when we interact with the database.

Introduction to the API of transaction control in Spring

PlatformTransactionManager

This interface is spring's transaction manager, which provides our common methods to operate transactions. Here are three main methods.

• Get transaction status information
  • TransactionStatus getTransaction(TransactionDefinition definition)
• Commit transaction
  • void commit(TransactionStatus status)
• Rollback transaction
  • void rollback(TransactionStatus status)

TransactionDefinition

• Get transaction object name
  • String getName()
• Get transaction isolation level
  • int getlsolationLevel()
• Get transaction propagation behavior
  • int getPropagationBehavior()
• Get transaction timeout
  • int getTimeout()
• Get whether the transaction is read-only
  • boolean isReadOnly()

Communication behavior of affairs

• REQUIRED: if there is no transaction at present, create a new transaction. If there is already a transaction, join it. General selection (default)
• SUPPORTS: SUPPORTS the current transaction. If there is no current transaction, it will be executed in a non transactional way (no transaction)
• MANDATORY: use the current transaction. If there is no transaction, an exception will be thrown
• REQUERS_NEW: create a new transaction. If it is currently in a transaction, suspend the current transaction.
• NOT_SUPPORTED: perform the operation in a non transactional way, and suspend the current transaction if it currently exists
• NEVER: run in non transactional mode, throw an exception if there is currently a transaction
• NESTED: if a transaction currently exists, it is executed within a NESTED transaction. If there is no transaction at present, perform the operation similar to REQUIRED.

Read only transaction or not

XML based control of declarative transactions

bean.xml

1. Configure transaction manager
2. Configure notifications for transactions
3. Configure AOP
4. Configuration cut in method
5. Establish expressions for pointcuts and transactions

<?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: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/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">
    <!--to configure bean object-->
    <bean id="accountService" class="com.gzgs.service.impl.AccountServiceImpl">
        <property name="accountDao" ref="accountDao"></property>
    </bean>
    <bean id="accountDao" class="com.gzgs.dao.impl.AccountDaoImpl">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--Configure data sources-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test02"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
    <!-- spring Based on XML Declarative transaction control configuration steps for
        1,Configure transaction manager
        2,Configure notifications for transactions
                //At this point, we need to import constraints of transaction tx namespace and constraints, and also need aop's
                //Using tx:advice Tag configuration transaction notification
                    //Properties:
                        id: Give transaction notification a unique ID
                        transaction-manager: Provide a transaction manager reference for transaction notifications
        3,to configure AOP Common pointcut expressions in
        4,Establish correspondence between transaction notification and pointcut expression
        5,Configure the properties of a transaction
               //Is the notification tx in the transaction:advice Inside of label
     -->

    <!--Configure transaction manager-->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- Configure notifications for transactions-->
   <tx:advice id="txAdvice">
       <tx:attributes>
           <tx:method name="*" read-only="false" propagation="REQUIRED"/>
           <tx:method name="find*" read-only="true" propagation="SUPPORTS"></tx:method>
       </tx:attributes>
   </tx:advice>

    <!--to configure AOP-->
    <aop:config>
        <!--Configuration cut in method-->
        <aop:pointcut id="pt1" expression="execution(* com.gzgs.service.impl.*.*(..))"></aop:pointcut>
        <!--Establish expressions for pointcuts and transactions-->
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pt1">

        </aop:advisor>
    </aop:config>
</beans>

Control method of declaration transaction based on annotation

AccountDaoImpl code

package com.gzgs.dao.impl;

import com.gzgs.bean.Account;
import com.gzgs.dao.AccountDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("accountDao")
public class AccountDaoImpl  implements AccountDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    public Account findAccountById(int accountId) {
        List<Account> accounts = jdbcTemplate.query("select * from account where id = ?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
        return accounts.isEmpty()?null:accounts.get(0);
    }


    public Account findAccountByName(String accountName) {
        List<Account> accounts = jdbcTemplate.query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
        if(accounts.isEmpty()){
            return null;
        }
        if(accounts.size()>1){
            throw new RuntimeException("Result set is not unique");
        }
        return accounts.get(0);
    }


    public void updateAccount(Account account) {
         jdbcTemplate.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
    }
}

AccountServiceImpl code

package com.gzgs.service.impl;

import com.gzgs.bean.Account;
import com.gzgs.dao.AccountDao;
import com.gzgs.service.AccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;


/**
 * Business layer implementation class of account
 *
 * Transaction control should be at the business level
 */
@Service("accountService")
//Configuration of read-only transactions
@Transactional(propagation = Propagation.SUPPORTS,readOnly = true)
public class AccountServiceImpl implements AccountService {
    @Autowired
    private AccountDao accountDao;



    public Account findAccountById(int accountId) {
        return accountDao.findAccountById(accountId);
    }
    //Read write transaction configuration is required
    @Transactional(propagation = Propagation.REQUIRED,readOnly = false)
    public void transfer(String sourceName, String targetName, double money) {
        //Query transfer out account
        Account source = accountDao.findAccountByName(sourceName);
        //Query transfer in account
        Account target=accountDao.findAccountByName(targetName);
        //Deduction from transfer out account
        source.setMoney(source.getMoney()-money);
        //Add money to transfer in account
        target.setMoney(target.getMoney()+money);
        //Update transfer out account
        accountDao.updateAccount(source);
//         int a=2/0;
        //Update transfer in account
        accountDao.updateAccount(target);
    }
}

bean.xml code

<?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:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       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/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
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <!--Configure path for scan-->
    <context:component-scan base-package="com.gzgs"></context:component-scan>
    <!--open Spring Support for annotation transactions-->
    <tx:annotation-driven transaction-manager="txManager"></tx:annotation-driven>
    <!--Configure data sources-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test02"></property>
        <property name="username" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
    <!-- to configure JdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>


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


</beans>

Control method of declaration transaction based on pure annotation

Define configuration class

SpringConfigruation

package config;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.context.annotation.PropertySource;
import org.springframework.transaction.annotation.EnableTransactionManagement;

/**
 * spring Configuration class, equivalent to bean.xml
 */
@Configuration
@ComponentScan("com.gzgs")
@PropertySource("jdbcConfig.properties")
@Import({jdbcConfig.class,TransactionConfig.class})
@EnableTransactionManagement//Turn on Spring's support for annotation transactions
public class SpringConfiguration {
}

jdbcConfig

package config;

import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

public class jdbcConfig {
    @Value("${jdbc.driver}")
    private String driver;
    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.username}")
    private String username;
    @Value("${jdbc.password}")
    private String password;


    /**
     * Returns the JdbcTemplate object used for data query
     * @param dataSource
     * @return
     */
    @Bean(name = "jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
    /**
     *
     * create data source
     * @return
     */
    @Bean(name = "dataSource")
    public DataSource createDataSource(){
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(driver);
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        return ds;
    }
}

TransactionConfig

package config;

import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionManager;

import javax.sql.DataSource;

/**
 * Transaction related configuration classes
 */

public class TransactionConfig {
    /**
     * Used to create a TransactionManager
     * @param dataSource
     * @return
     */
    @Bean(name = "txManager")
    public PlatformTransactionManager createTransactionManager(DataSource dataSource){
        return new DataSourceTransactionManager(dataSource);
    }
}

Learning code: https://download.csdn.net/download/weixin_45680962/12539194
This blog is a personal learning note. Learning resources are from black horse training camp. If there are any mistakes, please correct them