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:

https://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/htmlsingle/#using-boot-starter

Consolidated JDBC

Create test project test data source

1. Create database tables

CREATE TABLE `users` (
  `id` INT(11) NOT NULL,
  `NAME` VARCHAR(40) DEFAULT NULL,
  `PASSWORD` VARCHAR(40) DEFAULT NULL,
  `email` VARCHAR(60) DEFAULT NULL,
  `birthday` DATE DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

4. Write application Yaml profile connection database

spring:
  datasource:
    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

@SpringBootTest
class DemoApplicationTests {
    
    //DI injection data source
    @Autowired
    DataSource dataSource;
    
    @Test
    public void contextLoads() throws SQLException {
        
        //Take a look at the default data source
        System.out.println(dataSource.getClass());
        //Get connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        //Close connection
        connection.close();

    }

}

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.

test

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;
 
 
@RestController
@RequestMapping("/jdbc")
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
     */
    @Autowired
    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
    @GetMapping("/list")
    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
    @GetMapping("/add")
    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')";
        jdbcTemplate.update(sql);
        //query
        return "addok";
    }
 
 
    //Modify user information
    @GetMapping("/update/{id}")
    public String updateUser(@PathVariable("id") int id){
        //Insert statement
        String sql = "update users set NAME=?,PASSWORD=? where id="+id;
        //data
        Object[] objects = new Object[2];
        objects[0] = "Fei Zhang";
        objects[1] = "123789";
        jdbcTemplate.update(sql,objects);
        //query
        return "updateok";
    }
 
 
    //delete user
    @GetMapping("/delete/{id}")
    public String delUser(@PathVariable("id") int id){
        //Insert statement
        String sql = "delete from users where id=?";
        jdbcTemplate.update(sql,id);
        //query
        return "deleteok";
    }
    
}

Test request, the result is normal

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