SpringData Jdbc JdbcTemplate.queryForStream metodu

Giriş

Açıklaması şöyle

In the Spring Framework, the QueryByStream feature provides a powerful mechanism to fetch data from a database and process it in a streaming fashion, offering significant advantages in terms of efficiency and performance. This approach becomes particularly valuable when dealing with large datasets or requiring continuous data processing.

Açıklaması şöyle

Using the try-with-resources block ensures that the necessary resources are properly managed and released after processing the stream.

queryForStream - String sql, RowMapper +  Object... args

İmzası şöyle

<T> Stream<T> queryForStream(String sql, RowMapper<T> rowMapper, Object... args)

Örnek

Şöyle yaparız

public Optional<User> findById(Integer id) {
  try (Stream<User> stream = jdbcTemplate
        .queryForStream("select * from users where id=?", userMapper, id)) {
   return stream.findAny();
  }
}

SpringData Jdbc JdbcTemplate.queryForObject metodu

1. queryForObject metodu - sql + Object[] + RowMapper
İmzası şöyle

@Deprecated
@Override
Nullable
public <T> T queryForObject(String sql, @Nullable Object[] args,
  RowMapper<T> rowMapper) throws DataAccessException {

Açıklaması şöyle. Tek bir sonuç nesnesi dönmeli, yoksa IncorrectResultSizeDataAccessException fırlatır

IncorrectResultSizeDataAccessException - if the query does not return exactly one row, or does not return exactly one column in that row

RowMapper arayüzü ile kullanılır. RowMapper ve ParameterizedRowMapper sınıfları sorgu sonuçlarını nesneye çevirmek için kullanılır. Spring'in sağladığı BeanPropertyRowMapper ile de kullanılabilir.

Örnek
Şöyle yaparız

String sql = "SELECT * FROM users WHERE email = ?";
User user = jdbcTemplate.queryForObject(
  sql,
  new Object[]{email},
  new BeanPropertyRowMapper<>(User.class));

Örnek
Eğer döndürülen nesne tek bir sütun ise RowMapper olmadan kullanılır.  Select Name From Employee Where ID = ? sorgusu bir String yani nesne döndürür.

Örnek
Şöyle yaparız.

public Person findById(Integer id) {
  return this.template.queryForObject(this.findByIdSql, new Object[]{id},
    this.personRowMapper);
}

IncorrectResultSizeDataccessException 

Eğer veri tabanı boş sonuç dönerse exception fırlatır. Bunu yakalamak gerekir. 

Örnek

Şöyle yaparız

public Optional<User> findById(Integer id) {
  try {
   return Optional.ofNullable(jdbcTemplate
    .queryForObject("SELECT * FROM users WHERE id=?", userMapper,id)):
  } catch (IncorrectResultSizeDataccessException e) {
   return Optional.empty();
  }
}

SpringData Jdbc HikariDataSourcePoolMetadata Sınıfı

Giriş
Şu satırı dahil ederiz.

import org.springframework.boot.autoconfigure.jdbc.metadata.HikariDataSourcePoolMetadata;

Örnek

Şöyle yaparız

new HikariDataSourcePoolMetadata(dataSource).getActive();

SpringData Jdbc JdbcTemplate Multiple Databases Kullanımı

Örnek

application.properties şöyle olsun

spring.datasource.primary.url=jdbc:mysql://localhost:3306/db1
spring.datasource.primary.username=root
spring.datasource.primary.password=password

spring.datasource.secondary.url=jdbc:mysql://localhost:3306/db2
spring.datasource.secondary.username=root
spring.datasource.secondary.password=password

İki DataSource tanımlarız

@Configuration
public class DataSourceConfig {
    
  @Bean
  @Primary
  @ConfigurationProperties(prefix="spring.datasource.primary")
  public DataSource primaryDataSource() {
    return DataSourceBuilder.create().build();
  }
    
  @Bean
  @ConfigurationProperties(prefix="spring.datasource.secondary")
  public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
  }
}

İki JdbcTemplateConfig tanımlarız

@Configuration
public class JdbcTemplateConfig {
    
  @Bean
  public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") 
    DataSource primaryDataSource) {
    return new JdbcTemplate(primaryDataSource);
  }
    
  @Bean
  public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") 
    DataSource secondaryDataSource) {
    return new JdbcTemplate(secondaryDataSource);
  }
}

Kullanmak için şöyle yaparız

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

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

public List<User> getAllUsers() {
    String sql = "SELECT * FROM users";
    return primaryJdbcTemplate.query(sql, new UserMapper());
}

public List<Order> getAllOrders() {
    String sql = "SELECT * FROM orders";
    return secondaryJdbcTemplate.query(sql, new OrderMapper());
}

SpringData Jdbc JdbcTemplate.execute metodu

Giriş

execute metodu parametresiz şekilde veya callback şeklinde parametreler alacak şekilde kullanılabilir

execute metodu

İmzası şöyle

public void execute(final String sql) throws DataAccessException

Örnek

Şöyle yaparız

jdbc.execute("create table if not exist
  Player(id int primary key, name varchar(30), team int)");

execute metodu - ConnectionCallback
Şöyle yaparız. JDBC Connection nesnesi verir.

jdbcTemplate.execute(new ConnectionCallback<String>() {
  public String doInConnection(Connection con) throws SQLException {
    ...
  }     
}

execute metodu - CallableStatementCreator
Stored Procedure çağırmak için kullanılır. Şöyle yaparız.

jdbcTemplate.execute(
  new CallableStatementCreator() {
    public CallableStatement createCallableStatement(Connection con) throws SQLException{
      CallableStatement cs = con.prepareCall("{call sys.dbms_stats.gather_table_stats
        (ownname=>user, tabname=>'" + cachedMetadataTableName +
        "', estimate_percent=>20, method_opt=>'FOR ALL COLUMNS SIZE 1', degree=>0,
        granularity=>'AUTO', cascade=>TRUE, no_invalidate=>FALSE, force=>FALSE) }");
      return cs;
    }
  },
  new CallableStatementCallback() {
    public Object doInCallableStatement(CallableStatement cs) throws SQLException{
      cs.execute();
      return null; // returned from the jdbcTemplate.execute method
    }
  }
);

execute metodu - PreparedStatementCreator + PreparedStatementCallback

İmzası şöyle

public <T> T execute(PreparedStatementCreator psc, 
                     PreparedStatementCallback<T> action) 
throws DataAccessException

Örnek

Şöyle yaparız

public void updateOrderDateByUserId(String userId, String newOrderDate) 
  throws SQLException {

  final String sql = "UPDATE order_details SET order_date=? WHERE user_id=?";

  PreparedStatementCreator psc = new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
      PreparedStatement ps = con.prepareStatement(sql);
      ps.setString(1, newOrderDate);
      ps.setString(2, userId);
      return ps;
    }
  };

  this.jdbcTemplate.execute(psc, new PreparedStatementCallback<String>() {
    @Override
    public String doInPreparedStatement(PreparedStatement ps) 
      throws SQLException, DataAccessException {
      ps.executeUpdate();
      return "SUCCESS";
    }
  });
}

SpringData Jdbc AbstractRoutingDataSource Sınıfı - Separate Database İle Multitenant Yapı İçindir

Giriş

Şu satırı dahil ederiz

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

Kısaca

1. AbstractRoutingDataSource sınıfından kalıtan bir bean kodlarız. Bu sınıfta determineCurrentLookupKey() metodunu olmalıdır

2. AbstractRoutingDataSource sınıfından kalıtan bir bean nesnemize setTargetDataSources() ile hedef DataSource nesnelerini atarız.

3. Bu sınıf bir ThreadLocal ile birlikte kullanılır. ThreadLocal.set() ile bir enum veya string verilir. Bu enum veya string'e denk gelen DataSource nesnesi AbstractRoutingDataSource içinde setTargetDataSources() ile atanmıştır.

Açıklaması şöyle. Yani aslında AbstractRoutingDataSource sınıfından kalıtsak bile yine bir DataSource yaratıyoruz.

Abstract DataSource implementation that routes getConnection() calls to one of various target DataSources based on a lookup key. The latter is usually (but not necessarily) determined through some thread-bound transaction context.

determineCurrentLookupKey metodu

Açıklaması şöyle. Bu metod Spring tarafından çağrılır ve hangi DataSource'un kullanılacağını döner. Spring'de kendi içindeki Map'i arayarak ilgili DataSource nesnesini kullanır

A component that extends AbstractRoutingDataSource and is responsible to provide the list of datasources and also to provide the implementation of the determineCurrentLookupKey() method which will help in determining the current datasource.

Örnek

Şöyle yaparız

@Configuration
public class DataSourceConfig {

  @Bean
  public DataSource dataSource() {
    TenantRoutingDataSource customDataSource = new TenantRoutingDataSource();
    Map<Object, Object> targetDataSources = new HashMap<>();
    // Populate targetDataSources map with tenant's DataSource
    // ...
    customDataSource.setTargetDataSources(targetDataSources);
    return customDataSource;
  }
}

Açıklaması şöyle

In this example, TenantRoutingDataSource extends AbstractRoutingDataSource from Spring and overrides determineCurrentLookupKey() method to provide routing based on tenant context.

Örnek - Enum

Şöyle yaparız

public class DataSourceRouter extends AbstractRoutingDataSource {
  
  @Override
  protected Object determineCurrentLookupKey() {
    if (AsyncContextHolder.getAsyncContext() != null) {
      return AsyncContextHolder.getAsyncContext().get(READTYPE);
    }
    return null;
  }  
}

Elimizde şöyle bir enum olsun

public enum ClientDatabase {
  CLIENT_A, CLIENT_B
}

DataSourceRouter yaratmak için şöyle yaparız. Bu nesneye iki tane DataSource atanıyor

@Bean
public DataSource clientDatasource() {
  Map<Object, Object> targetDataSources = new HashMap<>();
  DataSource clientADatasource = clientADatasource();
  DataSource clientBDatasource = clientBDatasource();

  targetDataSources.put(ClientDatabase.CLIENT_A,clientADatasource);
  targetDataSources.put(ClientDatabase.CLIENT_B, clientBDatasource);

  DataSourceRouter datasourceRouter   = new ClientDataSourceRouter();
  datasourceRouter.setTargetDataSources(targetDataSources);
  datasourceRouter.setDefaultTargetDataSource(clientADatasource);
  return clientRoutingDatasource;
}

Örnek - Enum

Elimizde şöyle bir kod olsun. Burada enum içeren ThreadLocal nesne tanımlanıyor

import org.springframework.beans.factory.config.ConfigurableBeanFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

@Component
@Scope(value = ConfigurableBeanFactory.SCOPE_SINGLETON)
public class DataSourceContextHolder {
  private static ThreadLocal<DataSourceEnum> threadLocal; 

  public DataSourceContextHolder() {
    threadLocal = new ThreadLocal<>();
  }

  public void setDataSourceEnum(DataSourceEnum dataSourceEnum) {
    threadLocal.set(dataSourceEnum);
  }

  public DataSourceEnum getDataSourceEnum() {
    return threadLocal.get();
  }

  public static void clearDataSourceEnum() {
    threadLocal.remove();
  }
}

Şöyle yaparız. Burada constructor içinde setTargetDataSources ile her enum'a denk gelen DataSource atanıyor. Ayrıca varsayılan DataSource ta atanıyor.

import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

@Component
public class DataSourceRouting extends AbstractRoutingDataSource {
  private DataSourceOneConfig dataSourceOneConfig;
  private DataSourceTwoConfig dataSourceTwoConfig;
  private DataSourceContextHolder dataSourceContextHolder;

  public DataSourceRouting(DataSourceContextHolder dataSourceContextHolder,
                           DataSourceOneConfig dataSourceOneConfig,
    		     	   DataSourceTwoConfig dataSourceTwoConfig) {
    this.dataSourceOneConfig = dataSourceOneConfig;
    this.dataSourceTwoConfig = dataSourceTwoConfig;
    this.dataSourceContextHolder = dataSourceContextHolder;

    Map<Object, Object> dataSourceMap = new HashMap<>();
    dataSourceMap.put(DataSourceEnum.DATASOURCE_ONE, dataSourceOneDataSource());
    dataSourceMap.put(DataSourceEnum.DATASOURCE_TWO, dataSourceTwoDataSource());
    this.setTargetDataSources(dataSourceMap);
    this.setDefaultTargetDataSource(dataSourceOneDataSource());
  }

  @Override
  protected Object determineCurrentLookupKey() {
    return dataSourceContextHolder.getBranchContext(); //Thread local object
  }
}

Controller içinde şöyle yaparız. Burada ThreadLocal nesneye değer tanıyor

@RestController
@RequiredArgsConstructor
public class DetailsController {

  private final DataSourceContextHolder dataSourceContextHolder;

  @GetMapping(value="/getEmployeeDetails/{dataSourceType}")
  public List<Employee> getAllEmployees(@PathVariable("dataSourceType")
                                        String dataSourceType){
    if(DataSourceEnum.DATASOURCE_TWO.toString().equals(dataSourceType)){
      dataSourceContextHolder.setBranchContext(DataSourceEnum.DATASOURCE_TWO);
    } else {
      dataSourceContextHolder.setBranchContext(DataSourceEnum.DATASOURCE_ONE);
    }
    return employeeService.getAllEmployeeDetails(); 
  }
}

Örnek - AOP + Anotasyon

Şöyle yaparız. Burada isme sahip iki tane DataSource tanımlanıyor. Bir tanesi varsayılan DataSource

public class AbstractRoutingDataSourceImpl extends AbstractRoutingDataSource {

  private static final ThreadLocal<String> DATABASE_NAME = new ThreadLocal<>();

  public AbstractRoutingDataSourceImpl(DataSource defaultTargetDatasource, 
                                       Map<Object,Object> targetDatasources) {
    super.setDefaultTargetDataSource(defaultTargetDatasource);
    super.setTargetDataSources(targetDatasources);
    super.afterPropertiesSet();
  }
  public static void setDatabaseName(String key) {
    DATABASE_NAME.set(key);
  }

  public static String getDatabaseName() {
    return DATABASE_NAME.get();
  }

  public static void removeDatabaseName() {
    DATABASE_NAME.remove();
  }

  @Override
  protected Object determineCurrentLookupKey() {
    return DATABASE_NAME.get();
  }
}

İki DataSource şöyle yaratılır

@Configuration
@EnableJpaRepositories(basePackages = "com.dynamicdatasource.demo",entityManagerFactoryRef = "entityManager")
public class DynamicDatabaseRouter {

    public static final String PROPERTY_PREFIX = "spring.datasource.";

    @Autowired
    private Environment environment;

    @Bean
    @Primary
    @Scope("prototype")
    public AbstractRoutingDataSourceImpl dataSource() {
        Map<Object, Object> targetDataSources = getTargetDataSources();
        return new AbstractRoutingDataSourceImpl((DataSource)targetDataSources.get("default"), targetDataSources);
    }

    @Bean(name = "entityManager")
    @Scope("prototype")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(dataSource()).packages("com.dynamicdatasource").build();
    }

    private Map<Object,Object> getTargetDataSources() {
        
        //loading the database names to a list from application.properties file
        List<String> databaseNames = environment.getProperty("spring.database-names.list",List.class);
        Map<Object,Object> targetDataSourceMap = new HashMap<>();

        for (String dbName : databaseNames) {

                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                dataSource.setDriverClassName(envioronment.getProperty(PROPERTY_PREFIX + dbName + ".driver"));
                dataSource.setUrl(environment.getProperty(PROPERTY_PREFIX + dbName + ".url"));
                dataSource.setUsername(environment.getProperty(PROPERTY_PREFIX + dbName + ".username"));
                dataSource.setPassword(environment.getProperty(PROPERTY_PREFIX + dbName + ".password"));
                targetDataSourceMap.put(dbName,dataSource);

        }
        targetDataSourceMap.put("default",targetDataSourceMap.get(databaseNames.get(0)));
        return targetDataSourceMap;
    }
}

Burada bir aspect kodlanıyor

@Aspect
@Component
@Order(-10)
public class DataSourceAspect {
    private final Logger logger = LoggerFactory.getLogger(getClass());

    //defininining where the jointpoint need to be applied
    @Pointcut("@annotation(com.dynamicdatasource.demo.config.SwitchDataSource)")
    public void annotationPointCut() {
    }

    // setting the lookup key using the annotation passed value
    @Before("annotationPointCut()")
    public void before(JoinPoint joinPoint){
        MethodSignature sign =  (MethodSignature)joinPoint.getSignature();
        Method method = sign.getMethod();
        SwitchDataSource annotation = method.getAnnotation(SwitchDataSource.class);
        if(annotation != null){
            AbstractRoutingDataSourceImpl.setDatabaseName(annotation.value());
            logger.info("Switch DataSource to [{}] in Method [{}]",
                annotation.value(), joinPoint.getSignature());
        }
    }
    
    // restoring to default datasource after the execution of the method
    @After("annotationPointCut()")
    public void after(JoinPoint point){
        if(null != AbstractRoutingDataSourceImpl.getDatabaseName()) {
            AbstractRoutingDataSourceImpl.removeDatabaseName();
        }
    }
}

Aspect için anotasyon şöyle olsun

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SwitchDataSource {

    String value() default "";

}

Kullanırken şöyle yaparız

@SwitchDataSource(value = "college")
public List<College> getAllColleges(){
  return collegeRepository.findAll();
}

@SwitchDataSource(value = "student")
public List<Student> getAllStudents(){
  return studentRepository.findAll();
}

Örnek - TransactionSynchronizationManager

Burada amaç @Transactional ve @Transactional(readOnly = true) olarak işaretli çağrıları farklı veri tabanlarına göndermek. Şeklen şöyle

AbstractRoutingDataSource şöyledir

public class TransactionRoutingDataSource extends AbstractRoutingDataSource {

  @Override
  protected Object determineCurrentLookupKey() {
    return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ?
      DataSourceType.READ_ONLY :
      DataSourceType.READ_WRITE;
  }
}

Bu nesneyi yaratmak ve doldurmak için şöyle yaparız

public class TransactionRoutingConfiguration 
        extends AbstractJPAConfiguration {

   ...
  @Bean
  public DataSource readWriteDataSource() {
    ...
  }

  @Bean
  public DataSource readOnlyDataSource() {
    ...
  }

  @Bean
  public TransactionRoutingDataSource actualDataSource() {
    TransactionRoutingDataSource routingDataSource = 
      new TransactionRoutingDataSource();

    Map<Object, Object> dataSourceMap = new HashMap<>();
    dataSourceMap.put(
      DataSourceType.READ_WRITE, 
      readWriteDataSource()
    );
    dataSourceMap.put(
      DataSourceType.READ_ONLY, 
      readOnlyDataSource()
    );

    routingDataSource.setTargetDataSources(dataSourceMap);
    return routingDataSource;
  }
}

SpringData Jdbc ResourceDatabasePopulator Sınıfı

Giriş

Şu satırı dahil ederiz

import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;

execute metodu

Örnek

Şöyle yaparız

PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource[] resources = resolver.getResources(ExecuteSqlScriptRunner.BASE_SCRIPT_FOLDER +
  dirName + "/*.sql");

public void executeScript(Resource script) {
  ResourceDatabasePopulator databasePopulator = new ResourceDatabasePopulator(script);
  boolean success = true;
  String error = null;
  try {
    databasePopulator.execute(this.dataSource);
  } catch (ScriptException e) {
    ...
  }
 }

SpringData Jdbc JdbcTemplate Sınıfı - Kullanmayın

Giriş
Şu satırı dahil ederiz.

import org.springframework.jdbc.core.JdbcTemplate;

Spring ile gelen ve sonu Template ile biten sınıflardan bir tanesidir. JMSTemplate gibi.

Parametre Alanı
Parametrelerde placeholder olarak "?" karakteri yani soru işareti kullanılır. Açıklaması şöyle. Bu sınıf yerine NamedParameterJdbcTemplate daha iyi olabilir.

With JdbcTemplate, we generally do pass the parameter values with "?" (question mark). However, it is going to introduce the SQL injection problem. So, Spring provides another way to insert data by the named parameter. In that way, we use names instead of "?". So it is better to remember the data for the column. This can be done using NamedParameterJdbcTemplate.

Benzer bir açıklama şöyle.

JdbcTemplate supports only positioned parameters (?). Replace JdbcTemplate with NamedParameterJdbcTemplate

Yani JdbcTemplate SQL cümlesi içindeki "?" boşluklarının parametrelerle doldurulmasını ister.

NamedParameterJdbcTemplate SQL cümlesi içindeki ":" ile başlayan parametrelerin belirtilen Map içindeki değerler ile doldurur.

SpringBoot İçinde Kullanım
Bu sınıfı sadece Autowire etmek yeterli.

DAO İçinde Kullanım
JdbcTemplate sınıfı genellikle bir DAO içinde kullanılır. Bu sınıfı sadece Autowire etmek yeterli.

Örnek
Şöyle yaparız.

public class UserDAOImpl implements UserDAO
{
  @Autowired
  private JdbcTemplate jdbcTemplate;
  ...
}

Tanımlama - DriverManagerDataSource
Örnek
Şöyle yaparız.

<bean  name="dataSource" id="dataSource"
  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url" value="jdbc:postgresql://localhost:5432/testdbnew" /> 
  <property name="username" value="admin1" />
  <property name="password" value="admin1" />
</bean>    

<bean  id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean> 

Örnek
Şöyle yaparız.

<?xml version="1.0" encoding="UTF-8"?>
<beans ...>

  <bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://127.0.0.1:3306/" />
    <property name="username" value="root" />
    <property name="password" value="admin" />
  </bean>
  ...
</beans>

Tanımlama - ComboPooledDataSource
Örnek
Şöyle yaparız.

<bean id="abstractDataSource" abstract="true">
  <property name="driverClass" value="com.mysql.jdbc.Driver" />
  <property name="initialPoolSize" value="@initial.pool.size@" />
  <property name="minPoolSize" value="@min.pool.size@" />
  <property name="maxPoolSize" value="@max.pool.size@" /> 
</bean>
<bean id="masterDS" class="com.mchange.v2.c3p0.ComboPooledDataSource"
  parent="abstractDataSource">
    <property name="jdbcUrl" value="jdbc:mysql://@host@/" />
    <property name="user" value="@user@" />
    <property name="password" value="@pwd@" />
    <property name="dataSourceName" value="@dbName@" />
</bean>

Örnek
Şöyle yaparız.

<bean id="jdbcDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
  destroy-method="close">

  <property name="driverClass" value="com.mysql.cj.jdbc.Driver" />
  ...

  <property name="minPoolSize" value="10" />
  <property name="maxPoolSize" value="60" />
  <property name="initialPoolSize" value="12" />
  <property name="maxConnectionAge" value="1800" />
  <property name="maxIdleTime" value="600" />
  <property name="maxIdleTimeExcessConnections" value="300" />
  <property name="idleConnectionTestPeriod" value="100" />
  <property name="acquireIncrement" value="5" />
  <property name="acquireRetryAttempts" value="30" />
  <property name="acquireRetryDelay" value="1000" />
  <property name="breakAfterAcquireFailure" value="false" />
  <property name="checkoutTimeout" value="10000" />
  <property name="testConnectionOnCheckout" value="false" />
  <property name="preferredTestQuery" value="SELECT 1;" />
  <property name="numHelperThreads" value="10" />
  <property name="maxStatements" value="1000" />
  <property name="maxStatementsPerConnection" value="25" />
  ...

</bean>

constructor - DataSource

DataSource olarak test ortamında DriverManagerDataSource verilebilir.
Örnek
Şöyle yaparız.

DataSource dataSource = ...;
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); 

Örnek
Şöyle yaparız.

@Bean
JdbcTemplate jdbcTemplate(){return new JdbcTemplate(datasource());}

@Bean
public DataSource datasource(){
  BasicDataSource dataSource=new BasicDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl("jdbc:mysql://localhost:3306/quizzes");
  dataSource.setUsername("root");
  dataSource.setPassword("dbpass");
  return dataSource;
}

batchUpdate metodu
Şöyle yaparız.

List<Map<String, String>> map = ...;

String sql = " insert into  your_table " + "(  aa,bb  )"
                    + "values " + "(  ?,? )";
BatchPreparedStatementSetter bpss = new BatchPreparedStatementSetter() {
  @Override
  public void setValues(PreparedStatement ps, int i)
  throws SQLException {
    Map<String, String> bean = map.get(i);

    ps.setString(1, bean.get("aa"));
    ps.setString(2, bean.get("bb")); 
    //..
    //..

    }

  @Override
  public int getBatchSize() {
    return map.size();
  }
};

jdbcTemplate.batchUpdate(sql, bpss);

Eğer exception olursa hangi satırda olduğunu anlamak için şöyle yaparız.

catch (Exception e) {
  if (e.getCause() instanceof BatchUpdateException) {
    BatchUpdateException be = (BatchUpdateException) e.getCause();
    int[] batchRes = be.getUpdateCounts();
    if (batchRes != null && batchRes.length > 0) {
      for (int index = 0; index < batchRes.length; index++) {
        if (batchRes[index] == Statement.EXECUTE_FAILED) {
          ...
        }
      }
    }
  }  
}

execute metodu

execute metodu yazısına taşıdım

query metodu
query() metodu yazısına taşıdım.

queryForInt metodu
queryForInt(), queryForLong(), queryForObject() gibi metodlar tek bir satır döndürürler. Eğer birden fazla veya boş satır gelirse, IncorrectResultSizeDataAccessException veya bundan türeyen EmptyResultDataAccessException exceptionları atılır.

queryForObject metodu

queryForObject metodu yazısına taşıdım.

queryForList metodu
Birden fazla sonuç nesnesi dönülecekse kullanılır. List <Map <String,Object>> nesnesi döndürür. Şöyle yaparız

String queryString = "SELECT * FROM userInformation";
List<Map<String, Object>> listOfUsers = jdbcTemplate.queryForList(queryString);

queryForList metodu - string + arguments
Şöyle yaparız.

String number = ...
List<Map<String, Object>> result =
  jdbcTemplate.queryForList(sql, new Object[]{number, number});

queryForStream metodu

queryForStream metodu yazısın taşıdım

setMaxRows metodu - int

JdbcTemplate sınıfının setMaxRows(int maxRows) metod kullanılırsa SQL içindeki LIMIT/TOP seçenekleri  gibi çalışır.

update metodu  - sql + args
update metodu yazısına taşıdım

update metodu  - sql + PreparedStatementCreator

update metodu yazısına taşıdım