13-整合JDBC使用

整合JDBC使用

创建一个新项目

连接数据库

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

先测试数据库连接

package com.tian;

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 Springboot05DataApplicationTests {
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //查看一下默认的数据源
        System.out.println(dataSource.getClass());
        //class com.zaxxer.hikari.HikariDataSource

        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        //xxxxtemplate : springboot已经配置好的模板bean，拿来即用CRUD
        //jdbctemplate

        connection.close();
    }

}

controller测试

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

    //查询数据库的所有信息
    //没有实体类，数据库中的东西，怎么获取？ Map
    @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(id,name,pwd) values(4,'小明','123456')";
        jdbcTemplate.update(sql);
        return "add-ok";
    }

    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id")int id){
        String sql="update user set name=?,pwd=? where id="+id;
        Object[] objects = new Object[2];
        objects[0]="小明2";
        objects[1]="zzzzzzz";
        jdbcTemplate.update(sql,objects);
        return "updateUser-ok";
    }

    @GetMapping("/delUser/{id}")
    public String delUser(@PathVariable("id")int id){
        String sql="delete from user where id=?";
        jdbcTemplate.update(sql,id);
        return "delUser-ok";
    }
}