【SpringBoot】整合jdbc

SpringBoot项目整合JDBC

参考连接

1. 新建项目

2. Spring Web; JDBC API,MySQL Driver;

3. 创建好后，发现pom.xml已经导入了以下配置：

   
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-jdbc</artifactId>
       </dependency>
   
   
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <!-- 注意版本问题，本地安装的是5.几的版本，而springboot默认导入的是最新的，需要手动指定版本号-->
           <version>5.1.46</version>
           <scope>runtime</scope>
       </dependency>
   

4. 配置连接参数

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

5. 创建连接实例

   @SpringBootTest
   class DemoApplicationTests {
       @Autowired
       DataSource dataSource;
   
       @Test
       void contextLoads() throws SQLException {
           //输出默认数据源
           System.out.println("测试数据源" + dataSource.getClass());
           //获得连接
           Connection connection = dataSource.getConnection();
           System.out.println("测试连接" + connection);
           //关闭连接
           connection.close();
   
       }
   
   }
   

6. 运行测试
   

7. crud测试

package com.example.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping("/jdbc")
public class JDBCController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    // 获取所有数据
    @GetMapping("/list")
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String,Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    //增加一条数据
    @GetMapping("/add")
    public String addUsr(){
        String sql = "insert into user(id,name,age) values(1,'qd',21)";
        jdbcTemplate.update(sql);
        return "addOK";
    }

    //修改用户信息
    @GetMapping("/update/{id}")
    public String updateUsr(@PathVariable("id") int id){
        //更新语句
        String sql = "update user set name = ? where id = " + id;
        Object object = "qddemo";
        jdbcTemplate.update(sql,object);
        return "updateOk";
    }

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

8. 运行截图

   • jdbc/list
     

   • jdbc/add
     查看操作结果
     

   • jdbc/update/{id}
     
     查看结果
     

   • jdbc/delete/{id}
     
     查看结果