Springboot 整合JDBC

配置数据库

spring:
  datasource:
    username: admin
    password: 8098
    # 如果时区报错了，加一个时区的配置：serverTimezone=UTC
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8&useSSL=false
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

测试类，传统方法与数据库建立连接

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class Spring04DataApplicationTest {

    //自动按照yaml导入数据库资源
    @Autowired
    DataSource dataSource;

    @Test
    public void contextLoads() throws Exception {
        //查看一下默认的数据源
        System.out.println(dataSource.getClass());

        //获得数据库连接
        Connection connection = dataSource.getConnection();

        //xxxxTemplateC Springboot已经配置好模板bean，拿来即用CRUD

        //可以进行各种操作了
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM mybatis.student");
        while (resultSet.next()){ //是个链表
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("tid="+resultSet.getObject("tid"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("=============================");
        }

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

JDBCController

package com.peng.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; //JdbcTemplate类里，啥都有！

    @GetMapping("/userlist")
    //查询数据库的所有信息
    public List<Map<String,Object>> userList(){
        String sql = "select * from user";
        List<Map<String, Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }

    //增
    @GetMapping("adduser")
    public String addUser(){
        String sql = "insert into mybatis.user(id,name,pwd) values(4,'peng',8098)";
        jdbcTemplate.update(sql);
        return "update-ok";
    }

    //改
    @GetMapping("/updateuser/{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] = "peng2";
        objects[1] = "123456";
        jdbcTemplate.update(sql,objects);
        return "update-ok";
    }

    //删
    @GetMapping("/deleteuser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        String sql = "DELETE FROM mybatis.user WHERE id=?";
        jdbcTemplate.update(sql,id);
        return "delete-ok";
    }
}