Spring boot integrates JDBC notes

Introduction to spring data

For the data access layer, whether SQL (relational database) or NoSQL (non relational database), the bottom layer of Spring Boot adopts the way of Spring Data for unified processing.

The bottom layer of Spring Boot uses Spring Data to uniformly process various databases. Spring Data is also a well-known project in spring as well as Spring Boot and Spring Cloud.

Spin data official website: https://spring.io/projects/spring-data

For database related initiators, please refer to the official documents:


Consolidated JDBC

Create test project test data source

1. Create database tables

CREATE TABLE `users` (
  `id` INT(11) NOT NULL,
  `birthday` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)

INSERT INTO users (id, NAME ,PASSWORD,email,birthday) VALUES (1,'xxxx','xxxxx','xxxxxxxxx@qq.com','1582-05-04'

2. Create a new springboot project and introduce the corresponding modules

3. After the project was completed, we found that the following initiators were automatically imported for us




4. Write application Yaml profile connection database

    username: root
    password: peng
    #? serverTimezone=UTC resolves the error in the time zone
    url: jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

5. After configuration, we can use it directly, because SpringBoot has automatically configured it for us by default to test it

class DemoApplicationTests {
    //DI injection data source
    DataSource dataSource;
    public void contextLoads() throws SQLException {
        //Take a look at the default data source
        //Get connection
        Connection connection = dataSource.getConnection();
        //Close connection



6. You can see that the default data source configured for us is: class com zaxxer. hikari. Hikaridatasource, we did not configure it manually

Let's search globally and find all the automatic configurations of the data source in the DataSourceAutoConfiguration file:

	@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
			DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class,
			DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
	protected static class PooledDataSourceConfiguration {


The classes imported here are all under the DataSourceConfiguration configuration class. It can be seen that the HikariDataSource data source is used by default in Spring Boot 2.6.2, while the org.org data source is used by default in previous versions, such as Spring Boot 1.5 apache. tomcat. jdbc. pool. Datasource as the data source.

HikariDataSource is known as the fastest data source of Java WEB, which is better than traditional connection pools such as C3P0, DBCP and Tomcat jdbc;

You can use spring datasource. Type specifies the custom data source type, and the value is the fully qualified name of the connection pool implementation to be used.

JDBC Template

1. With the data source (com.zaxxer.hikari.HikariDataSource), you can get the database connection (java.sql.Connection). With the connection, you can use the native JDBC statement to operate the database

2. Even without using a third-party database operation framework, such as MyBatis, Spring itself makes a lightweight encapsulation of the native JDBC, that is, JdbcTemplate

3. All CRUD methods for database operations are in the JdbcTemplate

4. Spring Boot not only provides the default data source, but also has configured the JdbcTemplate by default and placed it in the container. You only need to inject it yourself

5. The automatic configuration of JdbcTemplate depends on org springframework. boot. autoconfigure. JdbcTemplateConfiguration class under JDBC package

JdbcTemplate mainly provides the following methods:

  • Execute method: can be used to execute any SQL statement, generally used to execute DDL statements

  • Update method and batchUpdate method: the update method is used to execute new, modify, delete and other statements; The batchUpdate method is used to execute batch related statements;

  • Query method and queryForXXX method: used to execute query related statements;

  • call method: used to execute stored procedures, functions and related statements.


Write a Controller, inject JDBC template, and write test methods for access testing

package com.peng.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;
public class JdbcController {
     * Spring Boot The data source is provided by default, and org.org is provided by default springframework. jdbc. core. JdbcTemplate
     * JdbcTemplate Data sources are injected into the database to simplify JDBC operations
     * It can also avoid some common errors, and you don't have to close the database connection yourself
    JdbcTemplate jdbcTemplate;
    //Query all data in users table
    //One Map in the List corresponds to one row of data in the database
    //The key in the Map corresponds to the field name of the database, and the value corresponds to the field value of the database
    public List<Map<String, Object>> userList(){
        String sql = "select * from users";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    //Add a new user
    public String addUser(){
        //Insert statement, pay attention to time
        String sql = "insert into users(id, NAME ,PASSWORD,email,birthday)" +
                " values (4,'Liu Bei','456789','21321323@qq.com','1582-05-04')";
        return "addok";
    //Modify user information
    public String updateUser(@PathVariable("id") int id){
        //Insert statement
        String sql = "update users set NAME=?,PASSWORD=? where id="+id;
        Object[] objects = new Object[2];
        objects[0] = "Fei Zhang";
        objects[1] = "123789";
        return "updateok";
    //delete user
    public String delUser(@PathVariable("id") int id){
        //Insert statement
        String sql = "delete from users where id=?";
        return "deleteok";

Test request, the result is normal

So far, the basic operation of CURD can be done by using JDBC.


Keywords: Java JDBC Spring Spring Boot

Added by rgermain on Sun, 09 Jan 2022 12:43:05 +0200