Spring Boot Tomcat JDBC 连接池示例

在这个Spring Boot Tomcat JDBC 连接池 示例中，我们将学习如何在Spring Boot应用程序中实现Tomcat JDBC 连接池。Tomcat JDBC 连接池是Apache Commons DBCP 连接池的替代品。Tomcat JDBC 连接池由于非常简化的实现而极其简单，与其他连接池库相比，行数和源文件数非常低。 

每当我们使用spring-boot-starter-jdbc 模块时，它都会隐式提取  用于配置 DataSource  bean的tomcat-jdbc-{version}.jar 。

我们的Restful Web 服务执行以下操作

• 创建学生（POST）        ：创建一个新学生（/学生） 
• Get Student By Id (GET)     : 根据 id (/student/1)获取学生
• List of All Students (GET)   : 获取所有学生(/students)
• 更新学生(PUT)         ：更新学生(/student)
• 删除学生(DELETE) : 删除学生(/student/1) 

创建表

创建 STUDENT 表，只需在查询编辑器中复制并粘贴以下 SQL 查询即可创建表。

CREATE TABLE "STUDENT" 
 ( "ID" NUMBER(10,0) NOT NULL ENABLE, 
   "NAME" VARCHAR2(255 CHAR), 
   "AGE" NUMBER(10,0) NOT NULL ENABLE, 
   PRIMARY KEY ("ID")
 );

 insert into "STUDENT" values (1,'JIP1',11);
 insert into "STUDENT" values (2,'JIP2',22);
 insert into "STUDENT" values (3,'JIP3',33);
 insert into "STUDENT" values (4,'JIP4',44);

文件夹结构：

• 创建一个Maven 项目 （maven-archetype-quickstart） “SpringBootTomcatJDBC”并在  src/main/java 下为我们的源文件“ com.javainterviewpoint ” 创建一个包
• 现在在POM.xml中添加以下依赖项

  <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.javainterviewpoint</groupId>
  	<artifactId>SpringBootTomcatJDBC</artifactId>
  	<version>0.0.1-SNAPSHOT</version>
  	<packaging>jar</packaging>
  
  	<name>SpringBootTomcatJDBC</name>
  	<url>http://maven.apache.org</url>
  
  	<properties>
  		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  		<oracle.connector.version>11.2.0</oracle.connector.version>
  	</properties>
  
  
  	<parent>
  		<groupId>org.springframework.boot</groupId>
  		<artifactId>spring-boot-starter-parent</artifactId>
  		<version>1.5.9.RELEASE</version>
  	</parent>
  	<dependencies>
  
  		<dependency>
  			<groupId>org.springframework.boot</groupId>
  			<artifactId>spring-boot-starter</artifactId>
  		</dependency>
  		<dependency>
  			<groupId>org.springframework.boot</groupId>
  			<artifactId>spring-boot-starter-web</artifactId>
  		</dependency>
  		<dependency>
  			<groupId>org.springframework.boot</groupId>
  			<artifactId>spring-boot-starter-jdbc</artifactId>
  		</dependency>
  
  		<!-- Oracle Dependency -->
  		<dependency>
  			<groupId>com.oracle</groupId>
  			<artifactId>ojdbc14</artifactId>
  			<version>${oracle.connector.version}</version>
  		</dependency>
  
  		<!-- <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> 
  			<version>3.8.1</version> <scope>test</scope> </dependency> -->
  	</dependencies>
  
  	<build>
  		<finalName>SpringBootTomcatJDBC</finalName>
  		<plugins>
  			<plugin>
  				<groupId>org.apache.maven.plugins</groupId>
  				<artifactId>maven-compiler-plugin</artifactId>
  				<configuration>
  					<source>1.8</source>
  					<target>1.8</target>
  				</configuration>
  			</plugin>
  		</plugins>
  	</build>
  </project>

• 在com.javainterviewpoint文件夹下创建 Java 类StudentApp.java、Student.java、StudentDAO.java、StudentDAOImpl.java、StudentController.java和 StudentMapper.java 。 
• 在src/main/resources目录下创建application.properties文件

# Datasource settings
spring.datasource.initialize=true
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@rsh2:40051:dev
spring.datasource.username=root
spring.datasource.password=root

# Tomcat JDBC settings
spring.datasource.tomcat.initial-size=10
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.max-wait=2000
spring.datasource.tomcat.test-on-connect=true
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-on-return=true

Spring Boot Tomcat JDBC 连接池示例

依赖树

[INFO] com.javainterviewpoint:SpringBootConnectionPool:jar:0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] Building SpringBootTomcatJDBC 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-dependency-plugin:2.10:tree (default-cli) @ SpringBootTomcatJDBC ---
[INFO] com.javainteriviewpoint:SpringBootTomcatJDBC:jar:0.0.1-SNAPSHOT
[INFO] +- org.springframework.boot:spring-boot-starter:jar:1.5.9.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot:jar:1.5.9.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-context:jar:4.3.13.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-autoconfigure:jar:1.5.9.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-logging:jar:1.5.9.RELEASE:compile
[INFO] |  |  +- ch.qos.logback:logback-classic:jar:1.1.11:compile
[INFO] |  |  |  +- ch.qos.logback:logback-core:jar:1.1.11:compile
[INFO] |  |  |  \- org.slf4j:slf4j-api:jar:1.7.25:compile
[INFO] |  |  +- org.slf4j:jcl-over-slf4j:jar:1.7.25:compile
[INFO] |  |  +- org.slf4j:jul-to-slf4j:jar:1.7.25:compile
[INFO] |  |  \- org.slf4j:log4j-over-slf4j:jar:1.7.25:compile
[INFO] |  +- org.springframework:spring-core:jar:4.3.13.RELEASE:compile
[INFO] |  \- org.yaml:snakeyaml:jar:1.17:runtime
[INFO] +- org.springframework.boot:spring-boot-starter-web:jar:1.5.9.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-tomcat:jar:1.5.9.RELEASE:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-core:jar:8.5.23:compile
[INFO] |  |  |  \- org.apache.tomcat:tomcat-annotations-api:jar:8.5.23:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-el:jar:8.5.23:compile
[INFO] |  |  \- org.apache.tomcat.embed:tomcat-embed-websocket:jar:8.5.23:compile
[INFO] |  +- org.hibernate:hibernate-validator:jar:5.3.6.Final:compile
[INFO] |  |  +- javax.validation:validation-api:jar:1.1.0.Final:compile
[INFO] |  |  +- org.jboss.logging:jboss-logging:jar:3.3.1.Final:compile
[INFO] |  |  \- com.fasterxml:classmate:jar:1.3.4:compile
[INFO] |  +- com.fasterxml.jackson.core:jackson-databind:jar:2.8.10:compile
[INFO] |  |  +- com.fasterxml.jackson.core:jackson-annotations:jar:2.8.0:compile
[INFO] |  |  \- com.fasterxml.jackson.core:jackson-core:jar:2.8.10:compile
[INFO] |  +- org.springframework:spring-web:jar:4.3.13.RELEASE:compile
[INFO] |  |  +- org.springframework:spring-aop:jar:4.3.13.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-beans:jar:4.3.13.RELEASE:compile
[INFO] |  \- org.springframework:spring-webmvc:jar:4.3.13.RELEASE:compile
[INFO] |     \- org.springframework:spring-expression:jar:4.3.13.RELEASE:compile
[INFO] +- org.springframework.boot:spring-boot-starter-jdbc:jar:1.5.9.RELEASE:compile
[INFO] |  +- org.apache.tomcat:tomcat-jdbc:jar:8.5.23:compile
[INFO] |  |  \- org.apache.tomcat:tomcat-juli:jar:8.5.23:compile
[INFO] |  \- org.springframework:spring-jdbc:jar:4.3.13.RELEASE:compile
[INFO] |     \- org.springframework:spring-tx:jar:4.3.13.RELEASE:compile
[INFO] \- com.oracle:ojdbc14:jar:11.2.0:compile

StudentDAO.java

package com.javainterviewpoint;

import java.util.List;

public interface StudentDAO
{
    List<Student> getAllStudents();
    Student getStudentById(int id);
    void addStudent(Student student);
    void updateStudent(Student student);
    void deleteStudent(int id);
}

StudentDAO接口包含执行CRUD操作的方法，实现将由StudentDAOImpl提供。

StudentDAOImpl.java

package com.javainterviewpoint;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class StudentDAOImpl implements StudentDAO
{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public List<Student> getAllStudents()
    {
        String sql = "select id, name, age from Student";
        List studentList = jdbcTemplate
                .query(sql, new StudentMapper());
        return studentList;
    }

    @Override
    public Student getStudentById(int id)
    {
        String sql = "select id, name, age from Student where id =?";
        Student student = jdbcTemplate
                .queryForObject(sql, new StudentMapper(), id);
        return student;
    }

    @Override
    public void addStudent(Student student)
    {
        String sql = "insert into Student (id, name, age) values (?, ?, ?)";
        jdbcTemplate.update(sql, student.getId(), student.getName(), student.getAge());
    }

    @Override
    public void updateStudent(Student student)
    {
        String sql = "update Student set name = ?, age = ? where id = ?";
        jdbcTemplate.update(sql, student.getName(), student.getAge(), student.getId());
    }

    @Override
    public void deleteStudent(int id)
    {
        String sql = "delete from Student where id = ?";
        jdbcTemplate.update(sql, id);
    }
}

• 我们已经实现了StudentDAO接口并覆盖了它的方法。
• 使用@Autowired注释，我们注入了jdbcTemplate，我们将使用它来查询数据库。

StudentController.java

package com.javainterviewpoint;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class StudentController
{
    @Autowired
    StudentDAOImpl studentDAOImpl;

    @GetMapping("/students")
    public ResponseEntity<List<Student>> getAllStudents()
    {
        List<Student> studentList = studentDAOImpl.getAllStudents();
        return new ResponseEntity<List<Student>>(studentList, HttpStatus.OK);
    }

    @GetMapping("/student/{id}")
    public ResponseEntity<Student> getStudentById(@PathVariable("id") Integer id)
    {
        Student student = studentDAOImpl.getStudentById(id);
        return new ResponseEntity<Student>(student, HttpStatus.OK);
    }

    @PutMapping("/student")
    public ResponseEntity<Student> updateArticle(@RequestBody Student student)
    {
        studentDAOImpl.updateStudent(student);
        return new ResponseEntity<Student>(student, HttpStatus.OK);
    }

    @PostMapping("/student")
    public ResponseEntity<Void> addArticle(@RequestBody Student student)
    {
        studentDAOImpl.addStudent(student);
        return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
    }

    @DeleteMapping("/student/{id}")
    public ResponseEntity<Void> deleteArticle(@PathVariable("id") Integer id)
    {
        studentDAOImpl.deleteStudent(id);
        return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
    }
}

• 我们已经用@RestController注释了我们的“StudentController”类，在Spring 4中引入了@RestController 注释，它是@Controller + @ResponseBody的组合。所以当使用@RestController时，你不需要使用@ResponseBody它现在是可选的 
• @RequestBody： 这个注解告诉 Spring绑定传入的HTTP请求体（请求中传递的对象）。HTTP消息转换器根据请求中存在的Accept标头将HTTP请求正文转换为域对象。
• @PathVariable：此注解将方法参数绑定到 URI 模板变量。

StudentMapper.java

package com.javainterviewpoint;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<Student>
{
    @Override
    public Student mapRow(ResultSet row, int rowNum) throws SQLException 
    {
         Student student = new Student();
         student.setId(row.getInt("id"));
         student.setName(row.getString("name"));
         student.setAge(row.getInt("age"));
         return student;
    }
}

在我们的StudentMapper类中，我们实现了Spring JDBC RowMapper接口，RowMapper用于将ResultSet行与Java对象映射。我们将在使用 JdbcTemplate 查询数据库时传递StudentMapper

StudentApp.java

package com.javainterviewpoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
public class StudentApp
{
    public static void main(String[] args)
    {
        SpringApplication.run(StudentApp.class, args);
    }
}

StudentApp类 的main()方法是我们应用程序的触发点，它依次调用 Spring Boot 的SpringApplication类run()方法来引导我们的StudentApp应用程序并启动 tomcat 服务器。我们需要将StudentApp .class作为参数传递给我们的run()方法。

Student.java

package com.javainterviewpoint;

public class Student
{
    private int id;
    private String name;
    private int age;
    
    public Student()
    {
        super();
    }
    public Student(int id, String name, int age)
    {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }
    
    public int getId()
    {
        return id;
    }
    public void setId(int id)
    {
        this.id = id;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public int getAge()
    {
        return age;
    }
    public void setAge(int age)
    {
        this.age = age;
    }
    
    @Override
    public String toString()
    {
        return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    }
}

我们的Student类是一个简单的POJO ，由Student属性id、name、age的 getter 和 setter 组成。

输出：

使用“mvn spring-boot:run”运行 Spring Boot 应用程序

使用 Postman 点击 url 进行验证

StudentRestTemplateClient.java – RestTemplate

Spring RestTemplate使用REST principals与HTTP服务器通信。它使用HTTP动词，例如GET、POST、HEAD、PUT、DELETE 等。RestTemplate 提供了不同的通信方法，这些方法将接受 URI 模板、URI 变量、响应类型和请求对象作为参数。

package com.javainterviewpoint;

import java.util.List;
import java.util.Map;

import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.web.client.RestTemplate;

public class StudentRestTemplateClient
{
    public static final String REST_BASE_URI = "http://localhost:8080";

    static RestTemplate restTemplate = new RestTemplate();

    /** POST **/
    public static void createStudent()
    {
        Student student = new Student();
        student.setId(4);
        student.setName("JIP4");
        student.setAge(44);

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        // headers.setAccept(Arrays.asList(MediaType.APPLICATION_JSON));

        HttpEntity entity = new HttpEntity<>(student, headers);
        restTemplate.postForObject(REST_BASE_URI + "/student", entity, Student.class);
    }

    /** GET **/
    private static void getStudent(int id)
    {
        Student student = restTemplate.getForObject(REST_BASE_URI + "/student/" + id, Student.class);
        System.out.println("**** Student with id : " + id + "****");
        System.out
                .println("Id :" + student.getId() + "    Name : " + student.getName() + "   Age : " + student.getAge());
    }

    public static void getAllStudents()
    {

        List<Map<String, Object>> studentList = restTemplate.getForObject(REST_BASE_URI + "/students", List.class);
        if (studentList != null)
        {
            System.out.println("**** All Students ****");
            for (Map<String, Object> map : studentList)
            {
                System.out.println(
                        "Id : id=" + map.get("id") + "   Name=" + map.get("name") + "   Age=" + map.get("age"));
            }
        } else
        {
            System.out.println("No Students exist!!");
        }
    }

    /** PUT **/
    public static void updateStudent()
    {
        Student student = new Student();
        student.setId(5);
        student.setName("JIP555555");
        student.setAge(55);

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);

        HttpEntity entity = new HttpEntity<>(student, headers);

        restTemplate.put(REST_BASE_URI + "/student", entity, Student.class);
    }

    /** DELETE **/
    public static void deleteStudent(int id)
    {
        restTemplate.delete(REST_BASE_URI + "/student/" + id);
    }

    public static void main(String args[])
    {
        createStudent();

        getAllStudents();

        getStudent(2);

        updateStudent();

        deleteStudent(5);
    }
}

输出 ：

16:29:41.159 [main] DEBUG org.springframework.web.client.RestTemplate - Created POST request for "http://localhost:8080/student"
16:29:41.227 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:29:41.258 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Student [id=4, name=JIP4, age=44]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@91e32e]
16:29:41.402 [main] DEBUG org.springframework.web.client.RestTemplate - POST request for "http://localhost:8080/student" resulted in 204 (null)
16:29:41.404 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/students"
16:29:41.446 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:29:41.460 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/students" resulted in 200 (null)
16:29:41.460 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [interface java.util.List] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@91e32e]
**** All Students ****
Id : id=1 Name=JIP1 Age=11
Id : id=2 Name=JIP2 Age=22
Id : id=3 Name=JIP3 Age=33
Id : id=4 Name=JIP4 Age=44
16:29:41.490 [main] DEBUG org.springframework.web.client.RestTemplate - Created GET request for "http://localhost:8080/student/2"
16:29:41.490 [main] DEBUG org.springframework.web.client.RestTemplate - Setting request Accept header to [application/json, application/*+json]
16:29:41.496 [main] DEBUG org.springframework.web.client.RestTemplate - GET request for "http://localhost:8080/student/2" resulted in 200 (null)
16:29:41.497 [main] DEBUG org.springframework.web.client.RestTemplate - Reading [class com.javainterviewpoint.Student] as "application/json;charset=UTF-8" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@91e32e]
**** Student with id : 2****
Id :2 Name : JIP2 Age : 22
16:29:41.498 [main] DEBUG org.springframework.web.client.RestTemplate - Created PUT request for "http://localhost:8080/student"
16:29:41.499 [main] DEBUG org.springframework.web.client.RestTemplate - Writing [Student [id=5, name=JIP555555, age=55]] as "application/json" using [org.springframework.http.converter.json.MappingJackson2HttpMessageConverter@91e32e]
16:29:41.532 [main] DEBUG org.springframework.web.client.RestTemplate - PUT request for "http://localhost:8080/student" resulted in 200 (null)
16:29:41.533 [main] DEBUG org.springframework.web.client.RestTemplate - Created DELETE request for "http://localhost:8080/student/5"
16:29:41.540 [main] DEBUG org.springframework.web.client.RestTemplate - DELETE request for "http://localhost:8080/student/5" resulted in 204 (null)

   下载源代码

 您可能喜欢的其他帖子……

• Spring Boot 日志记录——Logback
• Spring Boot Log4j2 示例
• Spring Boot HikariCP 连接池示例
• Spring Boot 连接池 – Commons dbcp2
• 在 Spring Restful Web 服务中启用 CORS
• 在 Spring Boot 中集成 Spring Data ElasticSearch
• 使用 Maven 的 Spring Boot Hello World 示例
• Eclipse 中的 Spring Boot Hello World 示例
• Spring Boot RESTful Web 服务示例
• Spring Boot Kotlin RESTful Web 服务 CRUD 示例
• 如何创建可部署的 WAR | 弹簧靴 | SpringBootServletInitializer
• Spring Boot – 如何更改嵌入式 Tomcat 默认端口
• 修复 Eclipse Maven 项目中缺少的 src/main/java 文件夹
• 使用 MySql + JdbcTemplate 的 Spring MVC CRUD 示例
• 带有 Jersey 客户端的 RESTful Java 客户端
• JAX-RS REST @生成 XML 和 JSON 示例
• JAX-RS REST @Consumes XML 和 JSON 示例
• 带有 Jersey 客户端的 RESTful Java 客户端
• 使用 Apache Tomcat 的 RESTEasy Hello World 示例
• 带有 RESTEasy 客户端的 RESTful Java 客户端
• Spring RESTful Web 服务 Hello World XML 示例
• Springfox Swagger 2 用于 Spring RESTful Web 服务
• Spring REST Hello World 示例 – JSON 和 XML 响应
• ClassNotFoundException：org.springframework.web.context.ContextLoaderListener
• 修复 Eclipse 中缺少的 src/main/java 文件夹
• 使用 MySql + JdbcTemplate 的 Spring MVC CRUD 示例