Spring Boot learning notes - integrating JDBC

Spring Boot learning

Official website: https://spring.io/projects/spring-boot#overview

file: https://docs.spring.io/spring-boot/docs/2.2.6.RELEASE/reference/html/

Reference video: [crazy God says Java] the latest tutorial of SpringBoot, IDEA version, is easy to understand_ Beep beep beep_ bilibili

Project complete reference code: lexiaoyuan/SpringBootStudy: My Spring Boot study notes (github.com),Springboot study: my Spring Boot study notes (gitee.com)

Integrate JDBC

Introduction to Spring Data

Official website: https://spring.io/projects/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.

For database related initiators, please refer to the official documents: https://docs.spring.io/spring-boot/docs/2.2.6.RELEASE/reference/html/using-spring-boot.html#using-boot

Consolidated JDBC

Create a new Spring Boot project springboot_05_jdbc, add web and SQL dependencies

Run the MySQL service to ensure that you can connect to MySQL and connect to the previously established mybatis database in MySQL through IDEA

Create a new application. In the resources directory YML configuration connection information

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

After configuration, you can use the automatically generated test class springboot05jdbcapplicationtests. Net in the test directory Test it in Java

@SpringBootTest
class Springboot05JdbcApplicationTests {
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        // View the default data source: HikariDataSource
        System.out.println("dataSource===>"+dataSource);

        //Get database connection
        Connection connection = dataSource.getConnection();
        System.out.println("connection====>" + connection);

        // close
        connection.close();

    }

}

Output results:

The default data source configured for us is: class com zaxxer. hikari. HikariDataSource

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;

JDBCTemplate

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 the configured JdbcTemplate is placed in the container by default. Programmers only need to inject it themselves

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: it 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.

Use the JdbcTemplate in springboot05jdbcapplication Create a controller package in the Java peer directory, and create a JDBC controller under the controller package java

@RestController
public class JDBCController {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/all")
    // A Map in the List corresponds to a row of data in the table
    // The key in the Map corresponds to the field name, and the value corresponds to the field value
    public List<Map<String, Object>> getAll() {
        String sql = "select * from user;";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @GetMapping("/add")
    public String addUser() {
        String sql = "insert into user (id, name, pwd) values (4, 'Spring boot', 'boot');";
        jdbcTemplate.update(sql);
        return "add ok";
    }

    @GetMapping("/update/{id}")
    public String updateUser(@PathVariable("id") int id) {
        String sql = "update  user set name = ? , pwd = ? where id = "+id+";";
        Object[] objects = new Object[2];
        objects[0] = "beta";
        objects[1] = "beta2";
        jdbcTemplate.update(sql, objects);
        return "update ok";
    }

    @GetMapping("/delete/{id}")
    public String deleteUser(@PathVariable("id") int id) {
        String sql = "delete from user where id = ?";
        jdbcTemplate.update(sql, id);
        return "delete ok";
    }
}