1. SpringData简介
对于数据访问层,无论是 SQL(关系型数据库) 还是 NOSQL(非关系型数据库),Spring Boot 底层都是采用 Spring Data 的方式进行统一处理。
Sping Data 官网:https://spring.io/projects/spring-data
数据库相关的启动器 :可以参考官方文档:
https://docs.spring.io/spring-boot/docs/2.2.5.RELEASE/reference/htmlsingle/#using-boot-starter
2. 搭建环境
2.1 导入依赖
pom.xml
<dependencies>
<!--web-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--Jdbc-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.4.6</version>
</dependency>
<!--MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<!--test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.4.5</version>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
2.2 配置数据源
application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
username: root
password: 123456
2.3 编写测试
Springboot04DataApplicationTests .java
@SpringBootTest
class Springboot04DataApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
// 查看默认的数据源:class com.zaxxer.hikari.HikariDataSource
System.out.println("查看默认的数据源============"+dataSource.getClass());
// 获取连接
Connection connection = dataSource.getConnection();
System.out.println("查看连接=================="+connection);
//关闭连接
connection.close();
}
}
3. JdbcTemplate使用
JDBCController.java
@RestController
public class JDBCController {
@Autowired
JdbcTemplate jdbcTemplate;
// 查询全部用户
@GetMapping("/userList")
public List<Map<String, Object>> userList() {
String sql = " select * from user ";
return jdbcTemplate.queryForList(sql);
}
//增加用户
@GetMapping("/addUser")
public String addUser() {
String sql = " insert into user(`id`,`name`,`pwd`) values (8,'哈哈8','124578')";
jdbcTemplate.update(sql);
return "addUserOk!";
}
// 修改用户
@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]="哈哈8修改";
objects[1]="159147258";*/
//方式二
Object[] objects = new Object[]{"哈哈8修改","159147258"};
jdbcTemplate.update(sql,objects);
return "updateUserOk!";
}
// 删除用户
@GetMapping("/deleteUser/{id}")
public String deleteUser(@PathVariable("id") int id) {
//方式一
/*String sql = " delete from user where id ="+id;
jdbcTemplate.update(sql);*/
//方式二
String sql = " delete from user where id = ? ";
jdbcTemplate.update(sql,id);
return "deleteUserOk!";
}
}
在浏览器分别输入地址进行测试:
http://localhost:8080/userList
http://localhost:8080/addUser
http://localhost:8080/updateUser/8
http://localhost:8080/deleteUser/7
总结:
JdbcTemplate主要提供以下几类方法:
- execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
- update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
- query方法及queryForXXX方法:用于执行查询相关语句;
- call方法:用于执行存储过程、函数相关语句。
内容参考:https://www.bilibili.com/video/BV1PE411i7CV
仅用于学习!