SpringBoot使用JdbcTemplate多种方式介绍

最近在一个springboot项目中使用到了JdbcTemplate，现将积累的一部分使用经验分享给大家。本文总结了springboot使用JdbcTemplate的三种方式。

第一种：

依赖：

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.1.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1</version>
            <scope>runtime</scope>
        </dependency>

application.properties配置文件：

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@10.30.10.15:1521:orcl
spring.datasource.username=test
spring.datasource.password=test

启动类：

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = { "com.read" })
public class Starter {

    public static void main(String[] args) {
        SpringApplication.run(Starter.class, args);
    }
}

测试类：

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.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Map;

@SpringBootTest(classes = Starter.class)
@RunWith(SpringRunner.class)
public class JdbcTemplateTest {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test
    public void selectTest() {
        Map result = jdbcTemplate.queryForMap("select * from test.order a where a.id = '2019091800001487' ");
        System.out.println(result.toString());
    }
}

 该种方式比较关键的地方是下图中的依赖：

注意： 此依赖一定要指定好版本，并注意与其他依赖版本的一致性，否则可能导致依赖的jar包没有下载或者版本冲突，最终导致启动报错。

比较重要的几个jar包：

HikariCP-3.2.0.jar；spring-boot-autoconfigure-2.1.2.RELEASE.jar；spring-jdbc-5.1.4.RELEASE.jar

第二种：

依赖不再使用spring-boot-starter-jdbc，如下：

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.1.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test-autoconfigure</artifactId>
            <version>2.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1</version>
            <scope>runtime</scope>
        </dependency>

启动类：

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.core.env.Environment;

import javax.sql.DataSource;

@SpringBootApplication
@ComponentScan(basePackages = { "com.read" })
public class Starter {

    @Autowired
    private Environment env;

    @Bean
    public DataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
        dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return dataSource;
    }

    public static void main(String[] args) {
        SpringApplication.run(Starter.class, args);
    }
}

第三种：

启动类：

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@SpringBootApplication
@ComponentScan(basePackages = { "com.read" })
public class Starter {

    @Autowired
    private Environment env;

    @Bean
    public JdbcTemplate setBean() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
        dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        return new JdbcTemplate(dataSource);
    }

    public static void main(String[] args) {
        SpringApplication.run(Starter.class, args);
    }
}

依赖等与第二种相同。

总结：推荐用第一种方式，比较方便而且更符合springboot的理念，但是要注意配置文件里面的配置项一定是spring.datasource开头的，因为默认以spring.datasource开头的配置项装配数据源，如下图：

第二种和第三种方式基本一样 ，但在多数据源的情况下，第三种方式比较有优势，如下：

application.properties配置文件：

spring.datasource1.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource1.url=jdbc:oracle:thin:@10.30.0.15:1521:orcl
spring.datasource1.username=test
spring.datasource1.password=test

spring.datasource2.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource2.url=jdbc:oracle:thin:@10.1.50.118:1521:orcl
spring.datasource2.username=system
spring.datasource2.password=oracle

启动类：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@SpringBootApplication
@ComponentScan(basePackages = { "com.read" })
public class Starter {

    @Autowired
    private Environment env;

    @Bean(name = "jdbcTemplate1")
    @Primary
    public JdbcTemplate setJdbcTemplate1() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("spring.datasource1.url"));
        dataSource.setUsername(env.getProperty("spring.datasource1.username"));//用户名
        dataSource.setPassword(env.getProperty("spring.datasource1.password"));//密码
        dataSource.setDriverClassName(env.getProperty("spring.datasource1.driver-class-name"));
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "jdbcTemplate2")
    public JdbcTemplate setJdbcTemplate2() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("spring.datasource2.url"));
        dataSource.setUsername(env.getProperty("spring.datasource2.username"));//用户名
        dataSource.setPassword(env.getProperty("spring.datasource2.password"));//密码
        dataSource.setDriverClassName(env.getProperty("spring.datasource2.driver-class-name"));
        return new JdbcTemplate(dataSource);
    }

    public static void main(String[] args) {
        SpringApplication.run(Starter.class, args);
    }
}

 

测试类：

import org.junit.Test;
import org.junit.runner.RunWith;
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.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Map;

@SpringBootTest(classes = Starter.class)
@RunWith(SpringRunner.class)
public class JdbcTemplateTest {

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

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

    @Test
    public void selectTest() {
        Map result = jdbcTemplate1.queryForMap("select * from test.order a where a.id = '10000726' ");
        System.out.println(result.toString());
    }
}