Spring Boot JDBC访问数据库

Spring Boot JDBC访问数据库

导入依赖

<!--导入JDBC的场景启动器-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

导入数据库驱动

<!--导入数据库驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

配置数据源：application.yml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/studentdb?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
    driver-class-name: com.mysql.cj.jdbc.Driver

JdbcTemplate 来实现对数据库的访问

@SpringBootTest
class SpringBootJdbcApplicationTests {
    //数据源组件
    @Autowired
    DataSource dataSource;
    //用于访问数据库的组件
    @Autowired
    JdbcTemplate jdbcTemplate;
    @Test
    void contextLoads() throws SQLException {
        System.out.println("默认数据源为：" + dataSource.getClass());
        System.out.println("数据库连接实例：" + dataSource.getConnection());
        //访问数据库
        Integer i = jdbcTemplate.queryForObject("SELECT count(*) from `user`", Integer.class);
        System.out.println("user 表中共有" + i + "条数据。");
    }
}

实现简单的增删改查

JDBCController

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

/**
 * JDBC访问数据库实现简单的CURD
 */
@RestController
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询
    @GetMapping("/StudentList")
    public List<Map<String,Object>> list(){
       String sql = "SELECT * from student";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }
    //新增
    @GetMapping("/StudentAdd")
    public String Add(){
        String sql = "INSERT INTO `studentdb`.`student`( `stuName`, `stuemail`, `stuPhone`) VALUES ('hhh', '2222222@qq.com', '1383838438')";
        jdbcTemplate.update(sql);
      return "Student_Add_Ok";
    }
    //修改
    @GetMapping("/StudentUpade")
    public String Update() {
        String sql = "UPDATE studentdb.student SET stuName = ?, stuemail=?  WHERE stuId = ?";
        Object[] object=new Object[3];
        object[0] = "小黑";
        object[1]="1111@qq.com";
        object[2] = 18;
        jdbcTemplate.update(sql,object);
        return "Update_OK";
    }
     //删除
     @GetMapping("/StudentDelete")
     public String Delete() {
        String sql = "DELETE FROM student WHERE stuId=?";
         int id=20;
         jdbcTemplate.update(sql, id);
          return "Delete_OK";
     }
}