【WEEK12】 【DAY1】Integrating JDBC【English Version】

2024.5.13 Monday

11. Integrating JDBC

11.1. Introduction to Spring Data

• For the data access layer, whether it’s SQL (relational databases) or NoSQL (non-relational databases), Spring Boot internally uses Spring Data to handle them uniformly.
• Spring Boot internally uses Spring Data to handle various databases uniformly, and Spring Data is also a well-known project in Spring, along with Spring Boot, Spring Cloud, etc.
• Sping Data official website: https://spring.io/projects/spring-data
• Database-related starters: You can refer to the official documentation: https://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/htmlsingle/#using-boot-starter

11.2. Create a New Project named springboot-04-data

Add Maven support (click the plus sign in Project Structure->Modules to add the corresponding project), as usual, modify the Maven, JDK, and Java versions in settings, and the JDK and Java versions in Project Structure. Modify the spring framework version in the pom.xml to 2.7.13, reload Maven, and delete redundant files.
For illustrated version, see: Configuration After Creating a New Spring Boot Project

11.3. Create application.yaml

Edit the code according to the existing database:

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

11.4. Connect to the Database

Choose a database table with parameters.

11.5. Modify Springboot04DataApplicationTests.java

package com.P31;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootTest
class Springboot04DataApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        // View the default data source (can be seen at the last line after running): com.zaxxer.hikari.HikariDataSource
        System.out.println(dataSource.getClass());

        // Get a database connection
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

		// See xxxxTemplate -> This is a pre-configured template bean provided by Spring Boot, ready to use

        // Close
        connection.close();
    }

}

The red warning about the outdated version can be ignored by default. The default data source is the one that runs faster compared to other data sources:

Refreshing in the new version may result in the following error:

At this time, you need to modify the time zone setting in application.yaml:

spring:
  datasource:
    username: root
    password: 123456
    # If there is a time zone error, add a time zone configuration, connected with other configurations using & like this: serverTimezone=UTC
    url: jdbc:mysql://localhost:3306/p37jdbc?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver

11.5.1. View DataSourceProperties.java and DataSourceAutoConfiguration.java

This is the principle of automatic configuration for databases:

11.6. JDBCTemplate

1. With a data source (com.zaxxer.hikari.HikariDataSource), you can get a database connection (java.sql.Connection), and with a connection, you can use native JDBC statements to operate the database.
2. Even without using third-party database operation frameworks, such as MyBatis, Spring itself provides lightweight encapsulation for native JDBC statements, namely JdbcTemplate.
3. All CRUD methods for database operations are in JdbcTemplate.
4. Spring Boot not only provides a default data source but also already configures JdbcTemplate in the container by default. Programmers only need to inject it to use it.
5. The automatic configuration of JdbcTemplate relies on the JdbcTemplateConfiguration class in the org.springframework.boot.autoconfigure.jdbc package.

JdbcTemplate mainly provides the following types of 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 insert, update, delete, and other statements; the batchUpdate method is used to execute batch processing-related statements.
• query method and queryForXXX method: used to execute query-related statements.
• call method: used to execute stored procedures and function-related statements.

11.7. Testing

Create a new controller folder.

11.7.1. Create JDBCController.java

Query all data in a specified table:

package com.P31.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.RestController;

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

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;  //You can click on JdbcTemplate to view

    //Query all information from the database
    //Without an entity class, how to get information from the database? -> Use Map
    @GetMapping("/userList")
    public List<Map<String,Object>> userList(){
        String sql = "SELECT * FROM users";
        List<Map<String,Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }
}

11.7.2. Run springboot-04-data

Remember to choose:

Clear the browser cache, then access: http://localhost:8080/userList

Same as in the database:

11.7.3. Modify JDBCController.java (Add, Update, Delete)

package com.P31.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.RestController;

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

@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;  //You can click on JdbcTemplate to view

    //Query all information from the database
    //Without an entity class, how to get information from the database? -> Use Map
    @GetMapping("/userList")
    public List<Map<String,Object>> userList(){
        String sql = "SELECT * FROM users";
        List<Map<String,Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }

    //Add
    @GetMapping("/addUser")
    public String addUser(){
        String sql = "INSERT INTO p37jdbc.users(id,NAME,PASSWORD,email,birthday) VALUES(6,'ZHAOSI','312321','111@outlook','2024-05-05')";
        jdbcTemplate.update(sql);
        return "completed add";
    }

    //Update
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql = "UPDATE p37jdbc.users SET NAME=?,PASSWORD=? WHERE id="+id;

        //Encapsulation
        Object[] objects = new Object[2];
        objects[0] = "ZHENGSAN";
        objects[1] = "12121212";
        jdbcTemplate.update(sql,objects);
        return "completed update";
    }

    //Delete
    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql = "DELETE FROM p37jdbc.users WHERE id=?";
        jdbcTemplate.update(sql,id);
        return "completed delete";
    }
}

11.7.3.1. Add

http://localhost:8080/addUser

11.7.3.2. Update

http://localhost:8080/updateUser/5

11.7.3.3. Delete

http://localhost:8080/deleteUser/4

11.8. View Source Code

11.8.1. DataSourceAutoConfiguration.java

Data sources:

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

These data sources will be automatically referenced after importing the JDBC package in pom.xml.
If you want to customize: modify the type.

11.8.2. ConditionalOnProperty.java

Click on the name in DataSourceAutoConfiguration.java to jump to this file (due to different versions, it should ideally jump to DataSourceConfiguration.java), where custom methods and automatic injection settings are provided.