avatar
Articles
63
Tags
11
Categories
10
Home
Archives
Tags
Categories
Link
About
List
ThinkDeeper
Home
Archives
Tags
Categories
Link
About
List
SpringBoot整合JdbcTemplate
|JavaSpringboot
Post View:|Comments:

摘要：本文介绍了如何在SpringBoot中使用JdbcTemplate以及JdbcTemplate添加多数据源。

整合JdbcTemplate

添加依赖

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

application.properties配置文件中进行配置

1
2
3
4
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=11215858
spring.datasource.url=jdbc:mysql://localhost:3306/javaboy?serverTimezone=Asia/Shanghai

创建实体类User

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
package com.example.jdbctemplate.bean;

/**
 * @author: WJZheng
 * @date: 2020/3/18 14:59
 * @description:
 */
public class User {
    private Integer id;
    private String name;
    private String address;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

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

创建UserService，并注入JdbcTemplate

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
package com.example.jdbctemplate.service;

import com.example.jdbctemplate.bean.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author: WJZheng
 * @date: 2020/3/18 15:00
 * @description:
 */

@Service
public class UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public Integer addUser(User user) {
        String sql = "insert into user (name, address) value (?,?)";
        return jdbcTemplate.update(sql, user.getName(), user.getAddress());
    }

    public Integer updateUserById(User user) {
        String sql = "update user set name = ?, address = ? where id = ?";
        return jdbcTemplate.update(sql, user.getName(), user.getAddress(), user.getId());
    }

    public Integer deleteUserById(User user) {
        String sql = "delete from user where id = ?";
        return jdbcTemplate.update(sql, user.getId());
    }

    public List<User> selectAll() {
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String address = rs.getString("address");
                user.setId(id);
                user.setName(name);
                user.setAddress(address);
                return user;
            }
        });
    }

    //当数据库表的列名和实体类的属性名相同时，可以直接使用这种方式
    public List<User> selectAllUser() {
        String sql = "select * from user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

}

在Test中进行测试

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
package com.example.jdbctemplate;

import com.example.jdbctemplate.bean.User;
import com.example.jdbctemplate.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class JdbctemplateApplicationTests {

    @Autowired
    UserService userService;

    @Test
    void contextLoads() {
        User user = new User();
        user.setName("ZWJ");
        user.setAddress("wellenzheng.github.io");
        userService.addUser(user);
    }

    @Test
    void updateUser(){
        User user = new User();
        user.setId(1);
        user.setName("hahaha");
        user.setAddress("home");
        userService.updateUserById(user);
    }

    @Test
    void deleteUser(){
        User user = new User();
        user.setId(1);
        userService.deleteUserById(user);
    }

    @Test
    void selectAllUser(){
        List<User> userList = userService.selectAllUser();
        for(User user:userList)
            System.out.println(user);
    }
}

JdbcTemplate多数据源

application.properties配置文件中进行配置

1
2
3
4
5
6
7
8
9
10
11
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.username=root
spring.datasource.one.password=11215858
spring.datasource.one.url=jdbc:mysql://localhost:3306/javaboy?serverTimezone=Asia/Shanghai

spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.username=root
spring.datasource.two.password=11215858
spring.datasource.two.url=jdbc:mysql://localhost:3306/javaboy2?serverTimezone=Asia/Shanghai

# 在我们加了.one和.two之后，自动话配置就失效了，因此需要我们需要手动去配置

创建配置类

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
package com.example.jdbctemplate2.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

/**
 * @author: WJZheng
 * @date: 2020/3/18 17:02
 * @description:
 */

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    DataSource dataSourceOne() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    DataSource dataSourceTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}
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
package com.example.jdbctemplate2.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @author: WJZheng
 * @date: 2020/3/18 17:05
 * @description:
 */

@Configuration
public class JdbcTemplateConfig {

    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dataSourceOne") DataSource one) {
        return new JdbcTemplate(one);
    }

    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dataSourceTwo") DataSource two) {
        return new JdbcTemplate(two);
    }
}

在Test中进行测试

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
package com.example.jdbctemplate2;

import com.example.jdbctemplate2.bean.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import java.util.List;

@SpringBootTest
class Jdbctemplate2ApplicationTests {

    @Autowired
    @Qualifier("jdbcTemplateOne")
    JdbcTemplate jdbcTemplateOne;

    //两种方式
    @Resource(name = "jdbcTemplateTwo")
    JdbcTemplate jdbcTemplateTwo;

    @Test
    void contextLoads() {
        String sql = "select * from user";
        List<User> userList1 = jdbcTemplateOne.query(sql, new BeanPropertyRowMapper<>(User.class));
        System.out.println(userList1);
        List<User> userList2 = jdbcTemplateTwo.query(sql, new BeanPropertyRowMapper<>(User.class));
        System.out.println(userList2);
    }
}
Author: WJZheng
Link: https://wellenzheng.github.io/2020/03/23/SpringBoot%E6%95%B4%E5%90%88JdbcTemplate/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
JavaSpringboot
Recommend
2020-03-16
@ControllerAdvice的使用
2020-03-27
SpringBoot整合MongoDB
2020-03-23
SpringBoot中实现系统启动任务
2020-03-23
SpringBoot整合Jpa
2020-03-16
SpringBoot文件上传
2020-03-23
SpringBoot整合Mybatis
Comment
简