Spring JdbcTemplate 快速入门

#Spring#

如果使用纯粹的 JDBC 编写业务逻辑，重复代码太多了。Spring 提供了 JdbcTemplate ，对 JDBC 封装了一层，可以有效减少样板代码。

示例

项目结构

.
├── build.gradle
├── settings.gradle
└── src
    └── main
        ├── java
        │   ├── demo01
        │   │   └── Main01.java
        │   ├── demo02
        │   │   └── Main02.java
        │   ├── demo03
        │   │   ├── AppConfig.java
        │   │   ├── Main03.java
        │   │   └── UserManager.java
        │   ├── demo04
        │   │   ├── AppConfig.java
        │   │   ├── Main04.java
        │   │   └── UserManager.java
        │   ├── demo05
        │   │   ├── AppConfig.java
        │   │   ├── Main05.java
        │   │   └── UserManager.java
        │   ├── demo06
        │   │   ├── AppConfig.java
        │   │   ├── Main06.java
        │   │   └── UserManager.java
        │   └── model
        │       └── User.java
        └── resources

demo01 到 demo05 分别对应一个示例。

build.gradle 中配置的依赖如下：

compile group: 'org.springframework', name: 'spring-core', version: '5.0.6.RELEASE'
compile group: 'org.springframework', name: 'spring-context', version: '5.0.6.RELEASE'
compile group: 'org.springframework', name: 'spring-jdbc', version: '5.0.6.RELEASE'
compile group: 'org.springframework', name: 'spring-aop', version: '5.0.6.RELEASE'

compile group: 'org.aspectj', name: 'aspectjweaver', version: '1.9.1'
compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.47'
compile group: 'org.projectlombok', name: 'lombok', version: '1.18.0'

compile group: 'junit', name: 'junit', version: '4.12'

User 类内容：

package model;

import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = false)
public class User {

    private Long id;
    private String name;

}

数据库使用 MySQL，database 名为 blog_db，里面一张user 表，建表语句和表中内容如下：

mysql> use blog_db;

mysql> show create table `user`;
+-------+-----------------------------------------------------------+
| Table | Create Table                                              |
+-------+-----------------------------------------------------------+
| user  | CREATE TABLE `user` (                                     |
|       |   `id` bigint(20) NOT NULL AUTO_INCREMENT,                |
|       |   `name` varchar(45) NOT NULL,                            |
|       |   `email` varchar(45) NOT NULL,                           |
|       |   `password` varchar(45) NOT NULL,                        |
|       |   PRIMARY KEY (`id`),                                     |
|       |   UNIQUE KEY `uk_name` (`name`)                           |
|       | ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------+

mysql> select * from user;
+----+--------+----------------+----------+
| id | name   | email          | password |
+----+--------+----------------+----------+
| 1  | letian | letian@111.com | 123      |
| 2  | xiaosi | xiaosi@111.com | 123      |
+----+--------+----------------+----------+

示例1：JdbcTemplate 可以不在 Spring 上下文中使用

demo01.Main01 类内容如下：

package demo01;

import model.User;
import org.junit.Test;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class Main01 {

    // 这个 dataSource 可以换成其他的实现，比如dbcp，c3p0等
    DataSource getDataSource() {
         DriverManagerDataSource dataSource=new DriverManagerDataSource(); // 这个是spring实现的一个简单的 dataSource，并没有连接池的特性，不适合生产环境
         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
         dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
         dataSource.setUsername("root");
         dataSource.setPassword("123456");
         return dataSource;
    }

    @Test
    public void test() {
        JdbcTemplate jdbcTemplate=new JdbcTemplate(getDataSource());
        List<User> userList = jdbcTemplate.query("select  id, name from user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
        System.out.println(userList);
    }
}

DataSource 使用 spring 自带的 DriverManagerDataSource 。DriverManagerDataSource本身没有连接池的特性，所以不适合生产环境。若要带连接池，可以使用 dbcp，c3p0 等提供的DataSource 实现。

运行 test 方法，输出：

[User(id=1, name=letian), User(id=2, name=xiaosi)]

示例2：在 Spring 上下文中使用 JdbcTemplate

这个示例和示例1基本相同。

demo02.Main02 类内容如下：

package demo02;

import model.User;
import org.junit.Test;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Configuration
@ComponentScan
public class Main02 {

    @Bean
    public DataSource dataSource() {
         DriverManagerDataSource dataSource=new DriverManagerDataSource();
         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
         dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
         dataSource.setUsername("root");
         dataSource.setPassword("123456");
         return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }

    public static void main(String[] args) {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(Main02.class);
        JdbcTemplate jdbcTemplate = ctx.getBean(JdbcTemplate.class);
        List<User> userList = jdbcTemplate.query("select  id, name from user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
        System.out.println(userList);
    }

}

运行后，输出：

[User(id=1, name=letian), User(id=2, name=xiaosi)]

示例3：插入和删除数据

demo03 中有三个类：

.
├── AppConfig.java
├── Main03.java
└── UserManager.java

AppConfig 类内容如下：

package demo03;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }

}

UserManager 类内容如下：

package demo03;

import model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class UserManager {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void add(String name, String email, String password) {
        jdbcTemplate.update("insert into user(name, email, password) values(?, ?, ?)", name, email, password);
    }

    public List<User> queryAllUser() {
        return jdbcTemplate.query("select  id, name from user order by id", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
    }

    public void delete(String name) {
        jdbcTemplate.update("delete from user where name=?", name);
    }
}

Main03 类内容如下：

package demo03;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;

@ComponentScan
public class Main03 {

    public static void main(String[] args) {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(Main03.class);
        UserManager userManager = ctx.getBean(UserManager.class);

        System.out.println("开始时：");
        System.out.println(userManager.queryAllUser());

        // 插入数据
        userManager.add("test", "test@letiantian.xyz", "test");
        System.out.println("插入新数据后：");
        System.out.println(userManager.queryAllUser());

        // 删除数据
        userManager.delete("test");
        System.out.println("删除刚才插入的数据后：");
        System.out.println(userManager.queryAllUser());

    }

}

运行结果如下：

开始时：
[User(id=1, name=letian), User(id=2, name=xiaosi)]
插入新数据后：
[User(id=1, name=letian), User(id=2, name=xiaosi), User(id=21, name=test)]
删除刚才插入的数据后：
[User(id=1, name=letian), User(id=2, name=xiaosi)]

示例4：使用 PlatformTransactionManager 事务管理器处理事务

在非事务的情况下，JdbcTemplate 执行每一个sql前，会去datasource 获取数据库连接，执行后，会释放连接。

但在事务中，它不会这样做。在事务开始时，事务管理器管理从 datasource 获取一个连接，JdbcTemplate 在事务中的操作都会使用该连接，事务结束后，事务管理器将连接关闭。

demo04 中有三个类：

.
├── AppConfig.java
├── Main04.java
└── UserManager.java

AppConfig 类内容如下：

package demo04;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }

    // 事务配置
    @Bean
    public TransactionDefinition transactionDefinition() {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        def.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);  // 指定隔离级别为可重复读
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);  // 指定事务传播方式
        return def;
    }

}

UserManager 类内容如下：

package demo04;

import model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class UserManager {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void add(String name, String email, String password) {
        jdbcTemplate.update("insert into user(name, email, password) values(?, ?, ?)", name, email, password);
    }

    public List<User> queryAllUser() {
        return jdbcTemplate.query("select  id, name from user order by id", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
    }

    public void delete(String name) {
        jdbcTemplate.update("delete from user where name=?", name);
    }
}

Main04 类内容：

package demo04;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;

@ComponentScan
public class Main04 {

    public static void main(String[] args) {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(Main04.class);

        UserManager userManager = ctx.getBean(UserManager.class);
        PlatformTransactionManager txManager = ctx.getBean(PlatformTransactionManager.class);
        TransactionDefinition txDef = ctx.getBean(TransactionDefinition.class);

        System.out.println("开始时：");
        System.out.println(userManager.queryAllUser());

        System.out.println("进入事务：");
        TransactionStatus txStatus = txManager.getTransaction(txDef); // 若参数为null，则为默认的事务配置
        try {
            userManager.add("test", "test@letiantian.xyz", "test");
            System.out.println("插入新数据后：");
            System.out.println(userManager.queryAllUser());
            throw new RuntimeException("ex");  // 
        } catch (Exception ex) {
            txManager.rollback(txStatus);
        }

        System.out.println("事务已经回滚，再查看数据：");
        System.out.println(userManager.queryAllUser());
    }

}

运行后输出：

开始时：
[User(id=1, name=letian), User(id=2, name=xiaosi)]
进入事务：
插入新数据后：
[User(id=1, name=letian), User(id=2, name=xiaosi), User(id=24, name=test)]
事务已经回滚，再查看数据：
[User(id=1, name=letian), User(id=2, name=xiaosi)]

示例5：使用 TransactionTemplate 精简事务代码

TransactionTemplate 会帮助我们自动开始事务，提交事务，回滚事务。只要抛出异常，就会回滚。注意，这个和@Transcational默认在遇到 RuntimeException 时回滚不同。

demo05 中有三个类：

.
├── AppConfig.java
├── Main05.java
└── UserManager.java

AppConfig 类内容：

package demo05;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }


    @Bean
    public TransactionTemplate transactionTemplate() {
        TransactionTemplate txTpl = new TransactionTemplate();
        txTpl.setTransactionManager(transactionManager());
        txTpl.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);   // 设置隔离别为为可重复读
        txTpl.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);   // 传播方式
        return txTpl;
    }

}

UserManager 类内容如下：

package demo05;

import model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class UserManager {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void add(String name, String email, String password) {
        jdbcTemplate.update("insert into user(name, email, password) values(?, ?, ?)", name, email, password);
    }

    public List<User> queryAllUser() {
        return jdbcTemplate.query("select  id, name from user order by id", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
    }

    public void delete(String name) {
        jdbcTemplate.update("delete from user where name=?", name);
    }
}

Main05 类内容如下：

package demo05;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

@ComponentScan
public class Main05 {

    public static void main(String[] args) {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(Main05.class);

        UserManager userManager = ctx.getBean(UserManager.class);
        TransactionTemplate txTemplate = ctx.getBean(TransactionTemplate.class);

        System.out.println("开始时：");
        System.out.println(userManager.queryAllUser());

        try {
            txTemplate.execute(new TransactionCallbackWithoutResult() {
                @Override
                protected void doInTransactionWithoutResult(TransactionStatus status) {

                    userManager.add("test", "test@letiantian.xyz", "test");
                    System.out.println("插入新数据后，查看数据：");
                    System.out.println(userManager.queryAllUser());
                    throw new RuntimeException("抛出异常");
                }
            });
        } catch (Exception ex) {
            System.out.println("异常信息: "+ex.getMessage());
        }

        System.out.println("事务已经回滚，再查看数据：");
        System.out.println(userManager.queryAllUser());

    }

}

运行结果：

开始时：
[User(id=1, name=letian), User(id=2, name=xiaosi)]
插入新数据后，查看数据：
[User(id=1, name=letian), User(id=2, name=xiaosi), User(id=26, name=test)]
异常信息: 抛出异常
事务已经回滚，再查看数据：
[User(id=1, name=letian), User(id=2, name=xiaosi)]

示例6：使用 @Transactional 注解

@Transactional 注解之所以生效，是因为 spring-tx 库内置了对它的代理逻辑，效果等同于切面。

demo06 中有三个类：

.
├── AppConfig.java
├── Main06.java
└── UserManager.java

AppConfig 类内容：

package demo06;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog_db");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }

    @Bean
    public TransactionTemplate transactionTemplate() {
        TransactionTemplate txTpl = new TransactionTemplate();
        txTpl.setTransactionManager(transactionManager());
        txTpl.setIsolationLevel(TransactionDefinition.ISOLATION_REPEATABLE_READ);
        txTpl.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        return txTpl;
    }

}

UserManager 类内容：

package demo06;

import model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Component
public class UserManager {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void add(String name, String email, String password) {
        jdbcTemplate.update("insert into user(name, email, password) values(?, ?, ?)", name, email, password);
    }

    public List<User> queryAllUser() {
        return jdbcTemplate.query("select  id, name from user order by id", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                return user;
            }
        });
    }

    public void delete(String name) {
        jdbcTemplate.update("delete from user where name=?", name);
    }

    // 该方法用到了 @Transactional 注解
    @Transactional
    public void addWithRuntimeException() {
        add("test", "test@letiantian.xyz", "test");
        System.out.println("插入新数据，查看数据");
        System.out.println(this.queryAllUser());
        throw new RuntimeException("抛出异常");
    }
}

Main06 类内容：

package demo06;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@ComponentScan
@EnableTransactionManagement // 必须加
public class Main06 {

    public static void main(String[] args) {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(Main06.class);

        System.out.println("开始时：");
        UserManager userManager = ctx.getBean(UserManager.class);

        System.out.println(userManager.queryAllUser());

        try {
            userManager.addWithRuntimeException();
        } catch (Exception ex) {
            System.out.println("异常信息: "+ex.getMessage());
        }

        System.out.println("事务已经回滚，再查看数据：");
        System.out.println(userManager.queryAllUser());

    }

}

运行结果：

开始时：
[User(id=1, name=letian), User(id=2, name=xiaosi)]
插入新数据，查看数据
[User(id=1, name=letian), User(id=2, name=xiaosi), User(id=28, name=test)]
异常信息: 抛出异常
事务已经回滚，再查看数据：
[User(id=1, name=letian), User(id=2, name=xiaosi)]

( 本文完 )