Skip to main content

JdbcTemplate

Hint

This article is generated by AI translation.

Getting Ready​

JdbcTemplate is dbVisitor's SQL-centric helper class. Its APIs wrap numerous utility methods that cover the majority of database operations.

  • Prepare a data source before use. It can be a javax.sql.DataSource or a concrete java.sql.Connection.
  • JdbcTemplate is stateless, so you can create and dispose of it freely.

The examples below use javax.sql.DataSource backed by HikariCP.

1. Create a data source
HikariConfig config = new HikariConfig();
config.setAutoCommit(false);
config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test");
config.setUsername("root");
config.setPassword("123456");
config.setDriverClassName("com.mysql.jdbc.Driver");
config.setMaximumPoolSize(20);
config.setMinimumIdle(1);
config.setConnectionTimeout(30000);

DataSource dbPool =  new HikariDataSource(config);

Next, instantiate JdbcTemplate and pass the dbPool into its constructor.

2. Create JdbcTemplate
JdbcTemplate jdbc = new JdbcTemplate(dbPool);

How It Works​

JdbcTemplate follows the Template Method pattern. A core template method named execute looks like this:

Core template method
// T is the template method return type
T result = jdbc.execute((ConnectionCallback<T>) con -> {
   ...
});

This execute(ConnectionCallback) method handles acquiring/closing connections and catching exceptions. Upper layers focus solely on using the Connection.

Usage Guide​

  • Query: run SQL that returns results, e.g., SELECT or any statement with a result set.
  • Update: run SQL with no result set, e.g., INSERT, UPDATE, DELETE, or DDL.
  • Batch: execute a batch of operations together.
  • Stored Procedures: call stored procedures or stored functions.
  • Rules: augment SQL with rule-based enhancements.
  • Multi Statements: execute SQL strings containing multiple statements and collect all results.
  • Scripts: run multi-statement SQL or load queries from external resources.
  • Templates: work with the database via template methods.
  • Advanced Features: explore special properties and behavior of JdbcTemplate.
  • Arguments: learn the different ways to bind arguments.
  • Receiving Results: learn the options for consuming result sets.