写作时间：2019-08-15

Spring Boot: 2.1 ,JDK: 1.8, IDE: IntelliJ IDEA

说明

本文将实现JDBC单条操作数据库，批量操作数据库。所有的Spring JDBC 类分到四个packages里面:

core – 核心接口和类。

the core functionality of JDBC. Some of the important classes under this package include JdbcTemplate, SimpleJdbcInsert, SimpleJdbcCall and NamedParameterJdbcTemplate.
datasource – 数据源相关的辅助类。

utility classes to access a datasource. It also has various datasource implementations for testing JDBC code outside the Java EE container.
object – 将基本的JDBC操作封装成对象。

DB access in an object-oriented manner. It allows executing queries and returning the results as a business object. It also maps the query results between the columns and properties of business objects.
support – 错误码等其它辅助工具。

support classes for classes under core and object packages. E.g. provides the SQLException translation functionality.

常用的Bean注解

@Component
@Repository
@Service
@Controller
@RestController

JdbcTemplate

query
queryForObject
queryForList
update
execute

SQL 批处理

JdbcTemplate

batchUpdate
BatchPreparedStatementSeter

NamedParameterJdbcTemplate

batchUpdate
SqlParameterSourceUtils.createBatch

工程建立

参照教程【SpringBoot 2.1 | 第一篇：构建第一个SpringBoot工程】新建一个Spring Boot项目，名字叫demodbjdbc, 在目录

src/main/java/resources

下找到配置文件

application.properties

，重命名为

application.yml

。

在Dependency中选择

Developer Tools > Lombok

Web > Spring Web Starter

SQL > H2 DataBase / JDBC API

Ops > Spring Boot Actuator。

易筋SpringBoot 2.1 | 第十六篇：SpringBoot通过JDBC访问数据库说明常用的Bean注解JdbcTemplateSQL 批处理工程建立设置日志打印格式化ANSI创建表Foo插入一条记录创建Model创建FooDaoController插入数据，查询数据启动成功，查看单条操作数据批量操作数据Controller 调用批量操作数据启动成功，查看批量操作数据总结参考

设置日志打印格式化ANSI

ANSI - American National Standards Institute

Support classes to provide ANSI color output.

src > main > resources > application.yml

spring:
  output:
    ansi:
      enabled: always

创建表Foo

路径 src > main > resources > schema.sql

插入一条记录

路径 src > main > resources > data.sql

创建Model

com.zgpeace.demodbjdbc.Foo

package com.zgpeace.demodbjdbc;

import lombok.Builder;
import lombok.Data;

@Data
@Builder
public class Foo {
  private Long id;
  private String bar;
}

创建FooDao

com.zgpeace.demodbjdbc.FooDao

package com.zgpeace.demodbjdbc;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

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

@Slf4j
@Repository
public class FooDao {
  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private SimpleJdbcInsert simpleJdbcInsert;

  public void insertData() {
    Arrays.asList("b", "c").forEach(bar -> {
      jdbcTemplate.update("INSERT INTO FOO (BAR) VALUES (?)", bar);
    });

    HashMap<String, String> row = new HashMap<>();
    row.put("BAR", "d");
    Number id = simpleJdbcInsert.executeAndReturnKey(row);
    log.info("ID of d: {}", id.longValue());
  }

  public void listData() {
    log.info("Count: {}", jdbcTemplate.queryForObject("SELECT COUNT(*) FROM FOO", Long.class));

    List<String> list = jdbcTemplate.queryForList("SELECT BAR FROM FOO", String.class);
    list.forEach(s -> log.info("Bar: {}", s));

    List<Foo> fooList = jdbcTemplate.query("SELECT * FROM FOO", new RowMapper<Foo>() {
      @Override
      public Foo mapRow(ResultSet resultSet, int i) throws SQLException {
        return Foo.builder()
            .id(resultSet.getLong(1))
            .bar(resultSet.getString(2))
            .build();
      }
    });
    fooList.forEach(f -> log.info("Foo: {}", f));
  }
}

Controller插入数据，查询数据

com.zgpeace.demodbjdbc.DemodbjdbcApplication

package com.zgpeace.demodbjdbc;

import lombok.extern.slf4j.Slf4j;
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.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import javax.sql.DataSource;

@SpringBootApplication
@Slf4j
public class DemodbjdbcApplication implements CommandLineRunner {
  @Autowired
  private FooDao fooDao;

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

  @Bean
  @Autowired
  public SimpleJdbcInsert simpleJdbcInsert(JdbcTemplate jdbcTemplate) {
    return new SimpleJdbcInsert(jdbcTemplate)
        .withTableName("FOO").usingGeneratedKeyColumns("ID");
  }

  @Bean
  @Autowired
  public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
    return new NamedParameterJdbcTemplate(dataSource);
  }

  @Override
  public void run(String... args) throws Exception {
    fooDao.insertData();
    fooDao.listData();
  }
}

启动成功，查看单条操作数据

com.zgpeace.demodbjdbc.FooDao            : ID of d: 4
com.zgpeace.demodbjdbc.FooDao            : Count: 4
com.zgpeace.demodbjdbc.FooDao            : Bar: zgpeace
com.zgpeace.demodbjdbc.FooDao            : Bar: b
com.zgpeace.demodbjdbc.FooDao            : Bar: c
com.zgpeace.demodbjdbc.FooDao            : Bar: d
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=1, bar=zgpeace)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=2, bar=b)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=3, bar=c)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=4, bar=d)

批量操作数据

com.zgpeace.demodbjdbc.BatchFooDao

package com.zgpeace.demodbjdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class BatchFooDao {
  @Autowired
  private JdbcTemplate jdbcTemplate;
  @Autowired
  private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

  public void batchInsert() {
    jdbcTemplate.batchUpdate("INSERT INTO FOO (BAR) VALUES (?)",
        new BatchPreparedStatementSetter() {
          @Override
          public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            preparedStatement.setString(1, "b-" + i);
          }

          @Override
          public int getBatchSize() {
            return 2;
          }
        });

    List<Foo> list = new ArrayList<>();
    list.add(Foo.builder().id(100l).bar("b-100").build());
    list.add(Foo.builder().id(101l).bar("b-101").build());
    namedParameterJdbcTemplate
        .batchUpdate("INSERT INTO FOO (ID, BAR) VALUES (:id, :bar)",
            SqlParameterSourceUtils.createBatch(list));
  }
}

Controller 调用批量操作数据

com.zgpeace.demodbjdbc.DemodbjdbcApplication 更新类如下内容

@Autowired
  private BatchFooDao batchFooDao;

  @Override
  public void run(String... args) throws Exception {
    //fooDao.insertData();
    batchFooDao.batchInsert();
    fooDao.listData();
  }

启动成功，查看批量操作数据

com.zgpeace.demodbjdbc.FooDao            : Count: 5
com.zgpeace.demodbjdbc.FooDao            : Bar: zgpeace
com.zgpeace.demodbjdbc.FooDao            : Bar: b-0
com.zgpeace.demodbjdbc.FooDao            : Bar: b-1
com.zgpeace.demodbjdbc.FooDao            : Bar: b-100
com.zgpeace.demodbjdbc.FooDao            : Bar: b-101
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=1, bar=zgpeace)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=2, bar=b-0)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=3, bar=b-1)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=100, bar=b-100)
com.zgpeace.demodbjdbc.FooDao            : Foo: Foo(id=101, bar=b-101)

总结

恭喜你，学会了SpringBoot通过JDBC访问数据库。

代码下载：

https://github.com/zgpeace/Spring-Boot2.1/tree/master/db/demodbjdbc

参考

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

https://github.com/geektime-geekbang/geektime-spring-family/tree/master/Chapter%202/simple-jdbc-demo

说明

常用的Bean注解

JdbcTemplate

SQL 批处理

工程建立

设置日志打印格式化ANSI

创建表Foo

插入一条记录

创建Model

创建FooDao

Controller插入数据，查询数据

启动成功，查看单条操作数据

批量操作数据

Controller 调用批量操作数据

启动成功，查看批量操作数据

总结

参考

继续阅读

关于Gradle配置的小结

Java小案例——随机数猜测随机数猜测

nginx location中斜线的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的简单使用

neo4j之cypher使用文档

GitHub连夜封杀！这份阿里 10W 字内部 Java 字面试手册到底有多强？

spark/scala关于【资源文件】加载方法概述外部文件加载方案测试资源文件打包入jar包中小结

mybatis_入门程序Mybatis入门

AOP编程_Android优雅权限框架(1)概念基础，2021金三银四前言正文大纲正文

Effective Java 8:通用程序设计

OOM三种类型

工厂模式-三种类型

【递归】高效率求2的n次幂

win10本地scala和spark安装安装scala安装spark

scala (3) Function 和 Method