壁立千仞，无欲则刚

Spring Boot 集成 JdbcTemplate 操作数据库

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 4.4k 阅读时长 ≈ 4 分钟

本示例使用 Spring Boot 集成 Druid 数据库连接池和 JdbcTemplate 操作数据库，包括对数据的增删改查操作。

添加相关依赖

添加 spring-boot-starter-jdbc 依赖、MySQL 依赖、Druid 数据库连接池依赖。

1
<dependency>
2
  <groupId>org.springframework.boot</groupId>
3
  <artifactId>spring-boot-starter-jdbc</artifactId>
4
</dependency>
5
<dependency>
6
  <groupId>mysql</groupId>
7
  <artifactId>mysql-connector-java</artifactId>
8
  <version>5.1.35</version>
9
</dependency>
10
<dependency>
11
  <groupId>com.alibaba</groupId>
12
  <artifactId>druid</artifactId>
13
  <version>1.0.14</version>
14
</dependency>

配置数据源

添加配置信息

  • application.properties
1
# 数据源
2
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
3
spring.datasource.druid.url=jdbc:mysql://localhost:3306/game?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
4
spring.datasource.druid.username=root
5
spring.datasource.druid.password=root
6
# 配置阿里巴巴提供的 Druid 数据库连接池
7
spring.datasource.druid.type=com.alibaba.druid.pool.DruidDataSource
8
spring.datasource.druid.initial-size=1
9
spring.datasource.druid.min-idle=1
10
spring.datasource.druidmax-active=20
11
spring.datasource.druid.test-on-borrow=true
12
spring.datasource.druid.stat-view-servlet.allow=true

创建 DataSource 和 JdbcTemplate

1
@Configuration
2
public class DatabaseConfig {
3
4
    @Bean
5
    @ConfigurationProperties(prefix = "spring.datasource.druid")
6
    public DataSource dataSource(){
7
        return new DruidDataSource();
8
    }
9
10
    @Bean
11
    public JdbcTemplate jdbcTemplate() {
12
        // 这里要将配置的 DruidDataSource 数据源注入 JdbcTemplate 中，不然默认注入 Spring Boot 自带的 HikariDatasource。
13
        return new JdbcTemplate(dataSource());
14
    }
15
}

使用 JdbcTemplate 操作数据库

User 实体类

实体类的字段名要和数据库的字段名一一对应。

1
public class User {
2
3
    private Long id;
4
5
    private String username;
6
7
    private String password;
8
9
    public Long getId() {
10
        return id;
11
    }
12
13
    public void setId(Long id) {
14
        this.id = id;
15
    }
16
17
    public String getUsername() {
18
        return username;
19
    }
20
21
    public void setUsername(String username) {
22
        this.username = username;
23
    }
24
25
    public String getPassword() {
26
        return password;
27
    }
28
29
    public void setPassword(String password) {
30
        this.password = password;
31
    }
32
}

DAO 层

1
@Repository
2
public class UserRepository {
3
4
    @Autowired
5
    private JdbcTemplate jdbcTemplate;
6
7
    public User getUser(String username) {
8
        String sql = "SELECT id, username, password FROM user WHERE username = ?";
9
        Map<String, Object> resultMap = jdbcTemplate.queryForMap(sql, username);
10
        User user = new User();
11
        user.setId(DataConvertUtils.getLong(resultMap.get("id")));
12
        user.setUsername(DataConvertUtils.getString(resultMap.get("username")));
13
        user.setPassword(DataConvertUtils.getString(resultMap.get("password")));
14
15
        return user;
16
    }
17
18
    public List<User> listUser() {
19
        String sql = "SELECT id, username, password FROM user";
20
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
21
        List<User> users = maps.stream().map(e -> {
22
            User user = new User();
23
            user.setId(DataConvertUtils.getLong(e.get("id")));
24
            user.setUsername(DataConvertUtils.getString(e.get("username")));
25
            user.setPassword(DataConvertUtils.getString(e.get("password")));
26
            return user;
27
        }).collect(Collectors.toList());
28
        return users;
29
    }
30
31
    /**
32
     * 不存在则插入，存在则更新
33
     * 注：被插入的数据中需要存在 UNIQUE 索引或 PRIMARY KEY 字段，这里使用 username 字段作为唯一索引 (UNIQUE)
34
     *
35
     * @param user
36
     */
37
    public int saveOrUpdateUser(User user) {
38
        String sql = "INSERT INTO user(username, password) VALUES(?,?)" +
39
                " ON DUPLICATE KEY" +
40
                " UPDATE password = ?";
41
        int count = jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getPassword());
42
43
        return count;
44
    }
45
46
    public int deleteUser(String username) {
47
        String sql = "DELETE FROM user WHERE username = ?";
48
        int count = jdbcTemplate.update(sql, username);
49
50
        return count;
51
    }
52
}

Service 层

1
@Service
2
public class UserService {
3
4
    private static final Logger logger = LoggerFactory.getLogger(UserService.class);
5
6
    @Autowired
7
    private UserRepository userRepository;
8
9
    public User getUser(String username) {
10
        User user = userRepository.getUser(username);
11
12
        return user;
13
    }
14
15
    public List<User> listUser() {
16
        List<User> users = userRepository.listUser();
17
18
        return users;
19
    }
20
21
    public void saveOrUpdateUser(User user) {
22
        userRepository.saveOrUpdateUser(user);
23
        logger.debug("Update user success");
24
    }
25
26
    public void deleteUser(String username) {
27
        userRepository.deleteUser(username);
28
        logger.debug("Delete user for username: {}", username);
29
    }
30
}

源码地址

springboot-jdbctemplate