SpringBoot整合JDBC详解

SpringBoot整合JDBC

@​​TOC​​

---

前言

对于数据访问层，无论是关系型数据库（SQL）还是NOSQL（非关系型数据库），SpringBoot的底层都是采用SpringData的方式来进行统一处理。SpringData其实是一个用于简化数据库访问，并支持云服务的开源框架。其主要目标是使得对数据的访问变得方便快捷。

下面我们通过一个简单的数据库访问例子来了解一下​​Spring Boot整合JDBC​​

Spring Boot整合JDBC

版本：
mysql8.0，springboot：2.7.8-SNAPSHOT

准备测试数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint NOT NULL COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (2, 'Jack', 20, '[e-mail]');
INSERT INTO `user` VALUES (3, 'Tom', 28, '[e-mail]');
INSERT INTO `user` VALUES (4, 'yc', 0, '[e-mail]');
INSERT INTO `user` VALUES (5, 'Billie', 24, '[e-mail]');

SET FOREIGN_KEY_CHECKS = 1;

创建项目导入依赖

<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>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

导入jdbc，和mysql依赖

配置文件

由于我的库名为：mybatis_plus，且是mysql8.0

spring:
  datasource:
    username: root
    password: qwer123.
    url: jdbc:mysql://127.0.0.1:3306/mybatis_plus?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver

测试连接

配置文件里的信息自动装配

package com.example.springboot_jdbc;

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 SpringbootJdbcApplicationTests {
    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() throws SQLException {
        //查看默认数据源
        System.out.println(dataSource.getClass());

        //获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        //关闭
        connection.close();
    }
}

crud的使用

编写JdbcController类

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

/**
 * @Author 秋名山码神
 * @Date 2023/1/11
 * @Description
 */
@RestController
public class JdbcController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询数据库的所有信息
    //没有实体类，数据库中的东西，怎么获取
    @GetMapping("/userlist")
    public List<Map<String, Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @GetMapping("/addUser")
    public String addUser(){
        String sql = "INSERT into `user` VALUES (19,'yss',50,'sdfa')";
        jdbcTemplate.update(sql);
        return "update-ok";
    }
    @GetMapping("/updateUser")
    public String updateUser(){
        String sql = "update `uesr` set name = ww  where id = 1";
        jdbcTemplate.update(sql);
        return "update-ok";
    }
    
    @GetMapping("/deleteUser")
    public String deleteUser(){
        String sql = "delete from user where id = 1 ;";
        jdbcTemplate.update(sql);
        return "update-ok";
    }
}

至此，我们关于springboot整合JDBC的使用就到此为止了