安装

1
2
3
4
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

配置

修改配置文件 \src\main\resources\application-dev.yaml

1
2
3
4
5
spring:
datasource:
url: jdbc:mysql://$URI:$PORT/wiki?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
username: null
password: null

链接

  1. 新建一个控制器并初始化jdbc属性
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

@RequestMapping("/users")
@RestController
public class JdbcController
{
private final JdbcTemplate jdbc;

public void JdbcController(JdbcTemplate jdbc)
{
// 初始化一个jdbc实例
this.jdbc = jdbc;
}
}

使用

新建表

1
2
3
4
create table users (
id bigint not null primary key auto_increment,
email varchar(60) not null default
) character set utf8 engine innodb

实体类 User.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public class User
{
private Long id;
private String email;

public String getEmail()
{
return email;
}

public void setEmail(String email)
{
this.email = email;
}

public String getId()
{
return id;
}

public void setId(Long id)
{
this.id = id;
}

@Override
public String toString()
{
return "User{" + "email='" + email + '\'' + ", id='" + id + '\'' + '}';
}
}

使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import org.devttl.entitys.User;

@RequestMapping("/users")
@RestController
public class JdbcController
{
private final JdbcTemplate jdbc;

public void JdbcController(JdbcTemplate jdbc)
{
// 初始化一个jdbc实例
this.jdbc = jdbc;
}

/**
* 获取所有用户
* 路由:GET /users
*/
@GetMapping("")
public List<User> queryUsers()
{
String sql = "select * from users";
return jdbc.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(User.class));
}

/**
* 查询某个用户
* 路由 GET /users/{主键ID}
*/
@GetMapping("/{id}")
public User queryUser(@PathVariable Long id)
{
String sql = "select * from users where id = ?";
return jdbc.queryForObject(sql, new Object[]{ id }, new BeanPropertyRowMapper<>(User.class));
}

/**
* 删除某个用户
* 路由 DELETE /users/{主键ID}
*/
@DeleteMapping("/{id}")
public int deleteUser(@PathVariable("id") String id)
{
String sql = "delete from users where id = ?";
return jdbc.update(sql, id);
}

/**
* 修改某用户
* 路由 PUT /users/{主键ID}
* {
* password : "123456"
* }
*/
@PutMapping("/{id}")
public int changeUser(@PathVariable Long id, @RequestBody User user)
{
String sql = "update users set password = ? where id = ?";
return jdbc.update(sql, user.getPassword(), id);
}
}