1, SpringData

Spring Data is an open source framework for simplifying database access and enabling cloud services. Its main goal is to make access to data easy and quick.

For Data access layer (DAO), no matter SQL(relational database) or NOSQL(non-relational database), Spring Boot bottom layer is unified processing in the way of Spring Data.

Sping Data website

Sping Data official document

Maven depends on:

<dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
Copy the code

2. Integrate JDBC

1. Create a SpringBoot project and introduce the Web, JDBC, and Mysql Driver modules

2. The corresponding initiator is:

       <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>
Copy the code

3. Write application. Yml configuration file to connect to database

spring:
  datasource:
    username: root
    password: 19990802
    url: jdbc:mysql://localhost:3306/mybatis? useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
    driver-class-name: com.mysql.jdbc.Driver
   This type can specify the data source type
   #type: 

Copy the code

4. Test class tests

    // Automatic data source assembly
    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads(a) {
        // View the default data source
        System.out.println(dataSource.getClass());//class com.zaxxer.hikari.HikariDataSource

        // With a data source, you can get a database connection
        try {
            Connection connection = dataSource.getConnection();
            // Check the connection
            System.out.println(connection);//HikariProxyConnection@2028767654 wrapping com.mysql.cj.jdbc.ConnectionImpl@3596b249
            // Close the connection
            connection.close();

        } catch(SQLException e) { e.printStackTrace(); }}Copy the code

With a database connection, you can obviously CRUD the database. But first we need to look at an object, JdbcTemplate

JDBCTemplate

  • A data source (com zaxxer. Hikari. HikariDataSource), then you can get a database Connection (Java, SQL Connection), have a Connection, you can use the native JDBC statements to manipulate the database;

  • Even without using a third party database manipulation framework such as MyBatis, Spring itself provides a lightweight wrapper around native JDBC, the JdbcTemplate.

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

  • Not only does Spring Boot provide the default data source, but the JdbcTemplate is already configured by default and placed in the container so that the programmer can inject it himself

  • JdbcTemplate automatic configuration is dependent on org. Springframework. Boot. The autoconfigure. Under the JDBC package JdbcTemplateConfiguration classes

JdbcTemplate provides the following classes of methods:

  • Execute method: can be used to execute any SQL statement, generally used to execute DDL statements.
  • Update method and batchUpdate method: Update method is used to execute new, modify, delete 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 procedure, function related statements.

Test SpringBoot to encapsulate JDBC

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

    // Query all information
    @GetMapping("/queryUser")
    // There is no entity class to write, use Map to encapsulate data
    public List<Map<String,Object>> useList(){
        String sql = "select * from user";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        return mapList;
    }
    // Add user information
    @GetMapping("/addUser")
    public String addUser(a){
        String sql = "Insert into user (id,name, PWD) values(3,' wanli ','123456')";
        jdbcTemplate.execute(sql);
        return "Added successfully";
    }
    // Delete user information
    @GetMapping("/deleteUser")
    public String deleteUser(a){
        String sql = "delete from user where id=4";
        jdbcTemplate.execute(sql);
        return "Deleted successfully";
    }
    // Modify user information
    @GetMapping("/updateUser/{id}")// Add an ID after the access path to modify the information
    public String updateUser(@PathVariable("id") int id){
        String sql = "update  user set name=?,pwd=? where id="+id;
        Object[] objects = new Object[2];
        objects[0] = "Xiao Ming";
        objects[1] = "123456";
        jdbcTemplate.update(sql,objects);
        return "Modified successfully"; }}Copy the code