SpringBoot系列: JdbcTemplate 快速入门

 对于一些小的项目, 我们没有必要使用MyBatis/JPA/Hibernate等重量级技术, 直接使用Spring JDBC 即可, Spring JDBC 是对 jdbc的简单封装, 很容易掌握.

============================
Spring JdbcTemplate 和 JDBC API 对比
============================
JDBC API的缺点:
1. 我们的代码中包含充斥着很多这样的代码: 创建connection, 创建statement , 关闭connection, 关闭resultset.
2. 对于 resultset 操作也不太方便, 需要使用 while 循环 + 客户端游标.next() 等.
3. 实现 Row -> Object 的映射比较玛法.
3. 我们需要自己控制 transaction(老实说, 这既是缺点又是优点)

Spring JdbcTemplate的特点:
1. 不需要我们关注各种资源的打开/关闭
2. 很容易将 resultset 转成 Pojo 集合.
3. 自动能利用上 DB 连接池

============================
Spring Jdbc主要类
============================
JdbcTemplate 类: 这是Spring JDBC中最常用的类.
NamedParameterJdbcTemplate 类: Jdbc中传统的参数占位符是问号, 这个类提供对命名参数的支持.
SimpleJdbcInsert 类: 不需要写Insert语句,即可完成单表插入操作, 该类支持链式调用(fluent style)
SimpleJdbcCall 类: 简化的调用存储过程的类, 该类支持链式调用(fluent style)

一般情况下, 我们使用 JdbcTemplate 或 NamedParameterJdbcTemplate 类就足够了.

============================
JdbcTemplate 主要方法
============================
query(),最通用的Select查询,  如果要返回一个  List<UserDefinedClass)  形式的结果集, 必须使用 query() 方法. 
queryForList()/queryForMap()/queryForRowSet(), 以集合的形式保存Select查询结果, queryForList()的结果不是 List<UserDefinedClass) 类型, 而是 List<Map> 类型, 每行记录一个 Map 对象, 字段和字段值以 K-V的形式保存. 
queryForObject(): 用来获得Select 标量值, 结果必须是一列一行.
execute(): 多用来执行DDL操作, 也可以用来执行 DML 语句.
update(): 用来执行Insert/Update/Delete操作
call():执行存储过程
batchUpdate(): 批量执行增删改操作

============================
pom.xml & application.properties & DB
============================

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

#application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/world?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=toor
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

在示例中使用了MySQL 官方提供的 sakila 样本数据库, 该数据用来模拟DVD租赁业务. 

先 clone 一个actor_new 新表.

CREATE TABLE `actor_new` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8

insert into actor_new  select * from actor ;

============================
java 示例代码
============================

package com.example.demo;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

@SpringBootApplication
public class JdbcSampleApplication implements CommandLineRunner {
    @Autowired
    DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    TransactionTemplate transTemplate;

    /*
     * 该方法会被Spring自动在合适的时机调用, 用来初始化一个 TransactionTemplate 对象. 参数 dataSource 被自动注入.
     */
    @Autowired
    private void transactionTemplate(DataSource dataSource) {
        transTemplate = new TransactionTemplate(new DataSourceTransactionManager(dataSource));
    }

    public static void main(String[] args) throws Exception {
        SpringApplication.run(JdbcSampleApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        runSimpleSamples();
        runRowToObjectSample();
        runTransactionSamples();
        runBatchSamples();
    }

    /*
     * 将resultset 转成 Java 对象列表
     */
    public void runRowToObjectSample() {
        String querySql = "select * from actor_new where actor_id<?";
        int id = 20;

        // 查询DB,
        List<Actor> actors = jdbcTemplate.query(querySql, new Object[] { id }, new ActorRowMapper());
        for (Actor actor : actors) {
            System.out.println(actor.getFirstName());
        }
    }

    /*
     * Query/DML执行
     */
    public void runSimpleSamples() throws SQLException {
        // 标量查询
        Integer count = jdbcTemplate.queryForObject("Select count(*) from actor_new", Integer.class);
        System.out.println(String.format("actor_new record count:%d", count));

        // DML执行
        jdbcTemplate.update("Delete from actor_new where actor_id=?", 2);

        count = jdbcTemplate.queryForObject("Select count(*) from actor_new", Integer.class);
        System.out.println(String.format("actor_new record count:%d", count));
    }

    /*
     * 带有事务控制的DML
     * 将DML操作放到 TransactionCallback类的doInTransaction()方法中.
     * 只有在下面两种情况下才会回滚:
     * 1. 通过设置 transactionStatus 为 RollbackOnly
     * 2. 抛出任何异常
     * */
    public void runTransactionSamples() throws SQLException {

        transTemplate.execute(new TransactionCallback<Object>() {
            @Override
            public Object doInTransaction(TransactionStatus transactionStatus) {
                // DML执行
                jdbcTemplate.update("Delete from actor_new where actor_id=?", 3);

                // 回滚
                transactionStatus.setRollbackOnly();
                return null;
            }
        });

    }


    /*
     * 批量update
     * */
    public void runBatchSamples() throws SQLException {
        //多个SQL批量提交
        String sql1="delete from actor_new where actor_id>=1000";
        String sql2="Insert into actor_new(actor_id, first_name, last_name) values (1000,'Harry','Liu')";
        jdbcTemplate.batchUpdate(sql1,sql2);

        //将 List<Actor> 对象集合插入的DB中.
        List<Actor> actors=new ArrayList<Actor>();
        actors.add(new Actor("A1","B1"));
        actors.add(new Actor("A2","B2"));
        actors.add(new Actor("A3","B3"));
        actors.add(new Actor("A4","B4"));
        actors.add(new Actor("A5","B5"));
        String sql3="Insert into actor_new(first_name, last_name) values (?,?)";
        jdbcTemplate.batchUpdate(sql3, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // TODO Auto-generated method stub
                //设置 PreparedStatement 的参数, 参数下标从 1 开始
                ps.setString(1, actors.get(i).getFirstName());
                ps.setString(2, actors.get(i).getLastName());
            }

            @Override
            public int getBatchSize() {
                // TODO Auto-generated method stub
                return 3;
            }
        });
        
        //使用 batchArgs 集合将多个 actor 插入DB. 
        String sql3="Insert into actor_new(first_name, last_name) values (?,?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"A6","B6"});
        batchArgs.add(new Object[]{"A7","B7"});        
        jdbcTemplate.batchUpdate(sql3,batchArgs); 
    }

}

/*
 * Actor entity 类
 */
class Actor {
    private int actorId;
    private String firstName;
    private String lastName;
    private Timestamp lastUpdateTimestamp;

    public Actor() {
    }

    public Actor(String firstName, String lastName) {
        this.firstName=firstName;
        this.lastName=lastName;
    }

    public int getActorId() {
        return actorId;
    }

    public void setActorId(int actorId) {
        this.actorId = actorId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Timestamp getLastUpdateTimestamp() {
        return lastUpdateTimestamp;
    }

    public void setLastUpdateTimestamp(Timestamp lastUpdateTimestamp) {
        this.lastUpdateTimestamp = lastUpdateTimestamp;
    }
}

/*
 * 将Row 转成 Actor 的Mapper类
 */
class ActorRowMapper implements RowMapper<Actor> {

    @Override
    public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
        Actor actor = new Actor();
        actor.setActorId(rs.getInt("actor_id"));
        actor.setFirstName(rs.getString("First_name"));
        actor.setLastName(rs.getString("last_name"));
        actor.setLastUpdateTimestamp(rs.getTimestamp("last_update"));
        return actor;
    }

}

============================
参考
============================
https://blog.csdn.net/zq9017197/article/details/6321391?utm_source=blogxgwz0

Spring JDBC
https://www.baeldung.com/spring-jdbc-jdbctemplate

Spring JdbcTemplate方法详解
https://blog.csdn.net/dyllove98/article/details/7772463

TRANSACTIONS WITH JDBCTEMPLATE
http://www.javacreed.com/transactions-with-jdbctemplate/