Java Create JDBCTemplate Singleton Bean without Writing DataSource

Question

I am attempting to make JdbcTemplate a Singleton Bean, instead of having it called in multiple repositories .

In the current code base, I didn't need to even write DataSource config.

For my new code singleton Config Bean attempt, is there a way to set the JDBCTemplate, without going through DataSource config?

Current Code:

@Repository
public class CustomerRepositoryImpl implements CustomerRepository {

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public CustomerRepositoryImpl(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

}

it would automatically read the DataSource using application.properties file

spring.datasource.url= jdbc:sqlserver://localhost;encrypt=true;databaseName=Test
spring.datasource.username= sauser
spring.datasource.password= sauserpw

New Code Attempt:

@Configuration
public class DatabaseConfig {
    
    @Bean
    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource)
    {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

// trying to prevent writing this code in for Datasource if possible, and importing a new maven package com.microsoft.sqlserver.jdbc.SQLServerDataSource;

SQLServerDataSource dataSource = new SQLServerDataSource();
dataSource.setUser("sauser");
dataSource.setPassword("sauserpw");
dataSource.setServerName("localhost");
dataSource.setDatabaseName("Test");


@Repository
public class CustomerRepositoryImpl implements CustomerRepository {

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public CustomerRepositoryImpl(DatabaseConfig databaseConfig) {
        this.namedParameterJdbcTemplate = databaseConfig.namedParameterJdbcTemplate();

Note: There is no error, trying to reduce the code base and simplify it

Answer 1

You can directly use JdbcTemplate and inject it as a bean and spring will automatically handle everything. Since in your case you only need to connect with single data source, mention the mandatory dependencies and relevant properties, declaring JdbcTemplate as bean will solve all your problems.

 import org.springframework.context.annotation.Bean;
 import org.springframework.context.annotation.Configuration;
 import org.springframework.jdbc.core.JdbcTemplate;
 import javax.sql.DataSource;

 @Configuration
 public class JdbcTemplateConfig {

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
       return new JdbcTemplate(dataSource);
    }
 }

this would be your JdbcConfig. Note : you don't need JdbcConfig class, you can initialize JdbcTemplate bean directly in your application's config.

Include spring-boot-starter-data-jpa dependency in your project.

You can use this bean in your repository in the following way.

   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.jdbc.core.JdbcTemplate;
   import org.springframework.stereotype.Repository;

   @Repository
   public class YourRepository {

   private final JdbcTemplate jdbcTemplate;

   @Autowired
   public YourRepository(JdbcTemplate jdbcTemplate) {
      this.jdbcTemplate = jdbcTemplate;
   }

   public String fetchData() {

    String sql = "SELECT your_column FROM your_table WHERE some_condition";
    return jdbcTemplate.queryForObject(sql, String.class);
   }
}

  

Hope this resolves your problem.