Basic use of JdbcTemplate

01 - Basic Use of JdbcTemplate - Overview (Understanding)

JdbcTemplate is an object provided in spring framework, which is a simple encapsulation of the original tedious Jdbc API objects. The spring framework provides us with many operation template classes. For example: JdbcTemplate and HibernateTemplate for manipulating relational data, RedisTemplate for manipulating nosql database, JmsTemplate for manipulating message queues, and so on.

02-JdbcTemplate Basic Usage-Development Steps (Understanding)

1. Import spring-jdbc and spring-tx coordinates

(2) Creating database tables and entities

Create JdbcTemplate objects

(4) Perform database operations

03-JdbcTemplate Basic Use-Quick Start Code Implementation (Application)

Import spring-jdbc and spring-tx coordinates

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.itheima</groupId>
  <artifactId>itheima_spring_jdbc</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>itheima_spring_jdbc Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>
  <dependencies>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.32</version>
    </dependency>
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.0.1</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>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.9.0</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.9.0</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-annotations</artifactId>
      <version>2.9.0</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>2.3</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.0.5.RELEASE</version>
    </dependency>
  </dependencies>
</project>

Create database tables and entities

[External Link Picture Transfer Failure (img-8wgV4gmG-1565868982805)(./img/1.png)]

package com.itheima.domain;

public class Account {

    private String name;
    private double money;

    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;
    }

    @Override
    public String toString() {
        return "Account{" +
                "name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

Create JdbcTemplate objects

Perform database operations

@Test
    //Testing JdbcTemplate development steps
    public void test1() throws PropertyVetoException {
        //Creating Data Source Objects
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        dataSource.setUser("root");
        dataSource.setPassword("root");

        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        //Set up the data source object to know where the database is
        jdbcTemplate.setDataSource(dataSource);
        //Perform operations
        int row = jdbcTemplate.update("insert into account values(?,?)", "tom", 5000);
        System.out.println(row);

    }

04-JdbcTemplate basically uses-spring to generate template object analysis (understanding)

We can give the right to create JdbcTemplate to Spring, and the right to create data source DataSource to Spring. We can inject data source DataSource into JdbcTemplate template object inside Spring container, and then obtain JdbcTemplate object through Spring container to perform operation.

05-JdbcTemplate is basically implemented using-spring to generate template object code (application)

The configuration is as follows:

<!--Data source object-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql:///test"></property>
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
    </bean>

    <!--jdbc Template object-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

Test code

 @Test
    //Test Spring to generate jdbcTemplate objects
    public void test2() throws PropertyVetoException {
        ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
        int row = jdbcTemplate.update("insert into account values(?,?)", "lisi", 5000);
        System.out.println(row);
    }

06-JdbcTemplate basically uses-spring to generate template object code (extract jdbc.properties) (application)

The connection information of database is extracted into external configuration file and separated from spring configuration file, which is helpful for later maintenance.

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

The configuration file was modified to read:

<?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 jdbc.properties-->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--Data source object-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--jdbc Template object-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

07-JdbcTemplate Basic Use-Common Operations-Update Operations (Applications)

package com.itheima.test;

import com.itheima.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;

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

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
	//Modify and update
    @Test
    public void testUpdate(){
        jdbcTemplate.update("update account set money=? where name=?",10000,"tom");
    }
	//delete
    @Test
    public void testDelete(){
        jdbcTemplate.update("delete from account where name=?","tom");
    }

}

08-JdbcTemplate Basic Use-Common Operations-Query Operations (Applications)

package com.itheima.test;

import com.itheima.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;

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

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
	//Aggregate queries
    @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), "tom");
        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);
    }

}

09-JdbcTemplate Basic Use-Knowledge Points (Understanding, Memory)

1. Import spring-jdbc and spring-tx coordinates

(2) Creating database tables and entities

Create JdbcTemplate objects

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

(4) Perform database operations

Update operation:

    jdbcTemplate.update (sql,params)

Query operation:

    jdbcTemplate.query (sql,Mapper,params)

jdbcTemplate.queryForObject(sql,Mapper,params)

Declarative transaction control

1. Programming Transaction Control Related Objects

1.1 PlatformTransactionManager

The Platform Transaction Manager interface is spring's transaction manager, which provides our usual methods of operating transactions.

[External Link Picture Transfer Failure (img-2f8EmPTd-1565868982806) (img2.png)]

Be careful:

Platform Transaction Manager is an interface type, while different Dao layer technologies have different implementation classes. For example, when the Dao layer technology is JDBC or mybatis: org. spring framework. jdbc. datasource. DataSource Transaction Manager

Dao layer technology is hibernate: org. spring framework. orm. hibernate 5. Hibernate Transaction Manager

1.2 TransactionDefinition

Transaction Definition is the definition information object of a transaction, which has the following methods:

[External Link Picture Transfer Failure (img-cGFGj6X3-1565868982807) (img 3.png)]

1. Transaction isolation level

Setting isolation level can solve the problems of transaction concurrency, such as dirty reading, non-repeatable reading and virtual reading.

• ISOLATION_DEFAULT

• ISOLATION_READ_UNCOMMITTED

• ISOLATION_READ_COMMITTED

• ISOLATION_REPEATABLE_READ

• ISOLATION_SERIALIZABLE

2. Transaction Communication Behavior

• REQUIRED: If there is no transaction at present, create a new transaction, and if there is already a transaction, join it. General selection (default value)

• SUPPORTS: Supports current transactions and executes in a non-transactional manner if there are no transactions at present.

• MANDATORY: Using the current transaction, throw an exception if there is no transaction at present

• REQUERS_NEW: Create a new transaction and suspend the current transaction if it is currently in the transaction.

• NOT_SUPPORTED: Performs operations in a non-transactional manner, suspending the current transaction if it exists

• NEVER: Runs in a non-transactional manner and throws an exception if a transaction currently exists

• NESTED: If a transaction currently exists, it executes within a nested transaction. If there are no transactions at present, perform REQUIRED-like operations

• Timeout: The default value is - 1, there is no timeout limit. If so, set it in seconds

• Read-only or not: It is recommended that queries be set to read-only

1.3 TransactionStatus

The TransactionStatus interface provides the specific running state of the transaction. The method is described below.

[External Link Picture Transfer Failure (img-yq3QvAZ9-1565868982807) (img 4.png)]

1.4 Key Points of Knowledge

Three Objects of Programming Transaction Control

• PlatformTransactionManager

• TransactionDefinition

• TransactionStatus

2 Declarative Transaction Control Based on XML

2.1 What is declarative transaction control

Spring's declarative transaction, as its name implies, deals with transactions in a declarative manner. Statements here refer to declarations in configuration files that are used to declaratively process transactions in Spring configuration files instead of code-based ones.

The Role of Declarative Transaction Processing

• Transaction management does not intrude into developed components. Specifically, business logic objects do not realize that they are in the process of transaction management. In fact, they should, because transaction management is a service at the system level, not a part of business logic. If you want to change transaction management planning, you only need to reconfigure it in the definition file.

• When there is no need for transaction management, the transaction management service can be removed by modifying the settings file without changing the code to recompile, which is very convenient to maintain.

Note: AOP is the underlying layer of Spring declarative transaction control.

2.2 Implementation of Declarative Transaction Control

Declarative Transaction Control Specifications:

• Who is the cut-off point?

• Who is the notice?

• Configuration section?

1. Introducing tx namespace

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

Configuration transaction enhancement

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

<!--Transaction Enhancement Configuration-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>

Configuration transaction AOP weaving

<!--Transactional aop Enhance-->
<aop:config>
    <aop:pointcut id="myPointcut" expression="execution(* com.itheima.service.impl.*.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="myPointcut"></aop:advisor>
</aop:config>

Testing Transaction Control Transfer Business Code

@Override
public void transfer(String outMan, String inMan, double money) {
    accountDao.out(outMan,money);
    int i = 1/0;
    accountDao.in(inMan,money);
}

2.3 Configuration of Transaction Parameters for Tangent Point Method

<!--Transaction Enhancement Configuration-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>

Among them,tx:method Configuration of transaction parameters representing the tangent method, such as:

<tx:method name="transfer" isolation="REPEATABLE_READ" propagation="REQUIRED" timeout="-1" read-only="false"/>

• Name: the name of the tangent method

• Isolation: transaction isolation level

• propogation: propagation behavior of transactions

• Timeout: timeout

• read-only: read-only or not

2.4 Key Points of Knowledge

Key Configuration Points of Declarative Transaction Control

• Platform Transaction Manager Configuration

• Configuration of transaction notifications

• Configuration of transaction aop weaving

3 Annotation-based Declarative Transaction Control

3.1 Configuration of declarative transaction control using annotations

1. Writing AccoutDao

@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);
    }
}

1. Writing AccoutService

@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);
    }
}

1. Write the application Context. XML configuration file

<! - Previously omitted the configuration of datsSource, jdbcTemplate, and platform transaction manager - >
<! - Component Scanning - >
<context:component-scan base-package="com.itheima"/>
<! - Annotation Driven Transaction
<tx:annotation-driven/>

3.2 Annotation Configuration Declarative Transaction Control Resolution

(1) Use @Transactional to modify the classes or methods that need transaction control, annotate the available attributes and xml configuration, such as isolation level, propagation behavior, etc.

(2) Annotations are used on classes, so all methods under this class use the same set of annotation parameters.

(3) Different methods can be used to configure different transaction parameters.

(4) Annotation-driven < tx: annotation-driven /> to open transactions in the Xml configuration file

3.3 Key Points of Knowledge

Configuration Points of Annotated Declarative Transaction Control

• Platform Transaction Manager Configuration (xml)

• Configuration of transaction notifications (@Transactional annotation configuration)

• Transaction annotation-driven configuration tx:annotation-driven/

/0;
accountDao.in(inMan,money);
}
}

3. Write the application Context. XML configuration file

```xml
 <! - Previously omitted the configuration of datsSource, jdbcTemplate, and platform transaction manager - >
<! - Component Scanning - >
<context:component-scan base-package="com.itheima"/>
<! - Annotation Driven Transaction
<tx:annotation-driven/>

3.2 Annotation Configuration Declarative Transaction Control Resolution

(1) Use @Transactional to modify the classes or methods that need transaction control, annotate the available attributes and xml configuration, such as isolation level, propagation behavior, etc.

(2) Annotations are used on classes, so all methods under this class use the same set of annotation parameters.

(3) Different methods can be used to configure different transaction parameters.

(4) Annotation-driven < tx: annotation-driven /> to open transactions in the Xml configuration file

3.3 Key Points of Knowledge

Configuration Points of Annotated Declarative Transaction Control

• Platform Transaction Manager Configuration (xml)

• Configuration of transaction notifications (@Transactional annotation configuration)

• Transaction annotation-driven configuration tx:annotation-driven/