【SpringBoot+JDBC对数据库CRUD】

文章目录

• 连接配置数据库
• 编写controller
• 测试文件
• 启动主类
• 前端调用接口，查询数据库成功

连接配置数据库

导入依赖

    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-jdbcartifactId>
        dependency>

        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <scope>runtimescope>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
    dependencies>

application.yaml

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
    driver-class-name: com.mysql.cj.jdbc.Driver

编写controller

@RestController
public class JDBCController {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @GetMapping("/user")
    public List<Map<String,Object>> userList(){
        String sql = "select * from mybatis.user";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;

    }
    @GetMapping("/add")
    public String adduser(){
        String sql = "insert into mybatis.user(id, name, pwd) VALUES (7,'小明','123456')";
        jdbcTemplate.update(sql);
        return "insert-ok";
    }
    @GetMapping("/update/{id}")
    public String updateuser(@PathVariable("id") int id){
        String sql = "update mybatis.user set name=?,pwd=? where id="+id;
        Object[] objects = new Object[2];
        objects[0] = "小红";
        objects[1] = "123456";
        jdbcTemplate.update(sql,objects);
        return "update-ok";
    }
    @GetMapping("/delete/{id}")
    public String deleteuser(@PathVariable("id") int id){
        String sql = "delete from mybatis.user where id=?";
        jdbcTemplate.update(sql,id);
        return "delete-ok";
    }
}

测试文件

@SpringBootTest
class Springboot05DataApplicationTests {
    //获取数据源
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //查看默认数据源，hikari
        System.out.println(dataSource.getClass());
        //获得数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        // xxxx Template：springboot已经配置好的模板bean，拿来即用
        //关闭连接
        connection.close();
    }

}

启动主类

@SpringBootApplication
public class Springboot05DataApplication {

    public static void main(String[] args) {

        SpringApplication.run(Springboot05DataApplication.class, args);
    }

}

前端调用接口，查询数据库成功