Springboot --- 配置多数据源,使用JdbcTemplate以及NamedParameterJdbcTemplate

整理不易，不喜勿喷。谢谢

SpringBoot — 整合Ldap.
SpringBoot — 整合Spring Data JPA.
SpringBoot — 整合Elasticsearch.
SpringBoot — 整合spring-data-jpa和spring-data-elasticsearch.
SpringBoot — 整合thymeleaf.
SpringBoot — 注入第三方jar包.
SpringBoot — 整合Redis.
Springboot — 整合slf4j打印日志.
Springboot — 整合定时任务，自动执行方法.
Springboot — 配置多数据源,使用JdbcTemplate以及NamedParameterJdbcTemplate.
Sprignboot — 详解pom.xml中build和profile.
SpringBoot — 监控.
SpringBoot — 缓存Cache/Redis.
SpringBoot与Zookeeper.
Git的使用.

部分内容抄这个大神的 => Spring Boot多数据源配置之JdbcTemplate.

1.dependencys

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--influx-->
        <dependency>
            <groupId>org.influxdb</groupId>
            <artifactId>influxdb-java</artifactId>
            <version>2.15</version>
        </dependency>
    </dependencies>

2.application.properties

spring.datasource.one.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.one.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.one.jdbc-url=jdbc:sqlserver://10.xxx.xxx.xxx:1433;DatabaseName=xxxx
spring.datasource.one.username=xxxx
spring.datasource.one.password=xxxx

spring.datasource.two.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.two.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.two.jdbc-url=jdbc:sqlserver://10.xxx.xxx.xxx:1433;DatabaseName=xxxx
spring.datasource.two.username=xxxx
spring.datasource.two.password=xxx

3.Config类

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.one")
    public DataSource dsOne(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.two")
    public DataSource dsTwo(){
        return DataSourceBuilder.create().build();
    }

}

@Configuration
public class JdbcTemplateConfig {

     @Bean
     @Primary
     public JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne")DataSource dsOne){
          return new JdbcTemplate(dsOne);
     }

     @Bean
     public JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo")DataSource dsTwo){
          return new JdbcTemplate(dsTwo);
     }
}

4.service

@Service
public class DataMapperService {

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

    @Autowired
    @Resource(name = "jdbcTemplateTwo")
    private JdbcTemplate jdbcTemplateTwo;

    public List<BusinessTermEntity> getAllTerms(String sql) {
        return jdbcTemplateOne.query(sql, new BusinessTermMapper());
    }

    private class BusinessTermMapper implements RowMapper<BusinessTermEntity> {
        @Override
        public BusinessTermEntity mapRow(ResultSet resultSet, int i) throws SQLException {
        	
            BusinessTermEntity bt = new BusinessTermEntity();
            bt.setBusinessTermId(checkResultOfQueryBigInte(resultSet.getObject("business_term_id")));
            bt.setIsCurrentVersion(resultSet.getNString("is_current_version"));
            bt.setBatchStatus(resultSet.getNString("batch_status"));
            bt.setHasExtendedText(resultSet.getNString("has_extended_text"));
            bt.setEndDt(resultSet.getTimestamp("end_dt"));
            return bt;
        }
    }

@Service
public class DataPipelineService {

    @Autowired
    private DataPipelineDao dataPipelineDao;
    @Autowired
    private DataMapperService dataMapperService;
    @Value("${name}")
    private String environment;

    public Long getTermByName(String name) {
    		
				
        String getName = dataPipelineDao.getTermName("xxxx");
        List<BusinessTermEntity> allTerms = dataMapperService.getAllTerms(getName);
        long businessTermId = allTerms.get(0).getBusinessTermId().longValue();
        return businessTermId;
    }

5.NamedParameterJdbcTemplate

NamedParameterJdbcTemplate类是基于JdbcTemplate类，并对它进行了封装从而支持命名参数特性。

NamedParameterJdbcTemplate主要提供以下三类方法：execute方法、query及queryForXXX方法、update及batchUpdate方法

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Test
    void contextLoads() {
        String sql = "select count(employId) from user where deparement_id =:deptId order by age desc";
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValue("deptId", 3);
        Integer count = namedParameterJdbcTemplate.queryForObject(sql, mapSqlParameterSource, Integer.class);
    }

    //insert
    @Test
    void test() {
        User user = new User();
        user.setId(UUID.randomUUID().toString());
        user.setName("旺财");
        user.setHomeAddress("唐伯虎家");

        namedParameterJdbcTemplate.update("insert into student(id,name,home_address) values (:id,:name,:homeAddress)",
                new BeanPropertySqlParameterSource(user));
    }

    //update
    @Test
    void test1() {
        User user = new User();
        user.setId(1);
        user.setName("旺财");
        user.setHomeAddress("唐伯虎家");

        String sql = "update user set name =:name and homeAddress =:homeAddress where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(user);
        namedParameterJdbcTemplate.update(sql, source);
    }


    //query entity
    @Test
    void test2() {
        User user = namedParameterJdbcTemplate.queryForObject(
                "select * from student limit 1", new HashMap<>(), new BeanPropertyRowMapper<User>(User.class));
    }

    @Test
    void test3() {
        List<User> userList = namedParameterJdbcTemplate.query(
                "select * from USER",
                new BeanPropertyRowMapper<>(User.class)
        );
    }    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Test
    void contextLoads() {
        String sql = "select count(employId) from user where deparement_id =:deptId order by age desc";
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValue("deptId", 3);
        Integer count = namedParameterJdbcTemplate.queryForObject(sql, mapSqlParameterSource, Integer.class);
    }

    //insert
    @Test
    void test() {
        User user = new User();
        user.setId(UUID.randomUUID().toString());
        user.setName("旺财");
        user.setHomeAddress("唐伯虎家");

        namedParameterJdbcTemplate.update("insert into student(id,name,home_address) values (:id,:name,:homeAddress)",
                new BeanPropertySqlParameterSource(user));
    }

    //update
    @Test
    void test1() {
        User user = new User();
        user.setId(1);
        user.setName("旺财");
        user.setHomeAddress("唐伯虎家");

        String sql = "update user set name =:name and homeAddress =:homeAddress where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(user);
        namedParameterJdbcTemplate.update(sql, source);
    }


    //query entity
    @Test
    void test2() {
        User user = namedParameterJdbcTemplate.queryForObject(
                "select * from student limit 1", new HashMap<>(), new BeanPropertyRowMapper<User>(User.class));
    }

    @Test
    void test3() {
        List<User> userList = namedParameterJdbcTemplate.query(
                "select * from USER",
                new BeanPropertyRowMapper<>(User.class)
        );
    }