配置数据库
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";
}
}