1Shares

Spring JdbcTemplate Basic Example

By Daniel Vladimirov / a few months ago

In this article we’ll go through some basic examples of using JdbcTemplate, the core JDBC class in Spring.

The JdbcTemplate class is the central piece of the JDBC core package. It simplifies the execution of SQL statements and promotes a clearly defined contract for developers to use the class in the form of callback interfaces. The main responsibility for the developer is to provide the SQL statement, to be executed and map the results to the desired model. In this article we’re going to throw some queries against a MySQL database, having a single table “Users”.

The methods we’re going to use for the example:

Test data

The test data in this example is the following

1 test_username someone@nowhere.comm New York male 32
2 johnny11 john.d@somewhere.comm Manchester male 24
3 daria daria@doesnotexist.nett Madrid female 28
4 janedoe janedoe@ineedtoautomatethis.comm Washington female 35

Dependencies

In order to use the JdbcTemplate class and also make a connection to our database, we need the following dependencies (Maven):

Model

To represent the users in our code, we need a model class, i.e. an entity

It’s a simple entity class that is directly mapped to our database table. If you need to map properties that are named differently than your table columns, you can use the @Column annotation on property level with an argument, specifying the column name.

Getting a single user by username

To get a single “User” entity by supplying a username, we’re going to use the JdbcTemplate#query(String sql, Object[] args, ResultSetExtractor<T> rse) method. This method accepts your SQL statement, an “Object” array, containing the query parameters (if such are needed, otherwise use the other overloaded method) and a ResultSetExtractor<T> class, which will help us map the result set to our entity.

Getting a single user by username as a map object

There’s also the possibility to fetch a single table row into a map object. In some cases this should prove useful where you don’t have a specific structure you can map to, or you have dynamic views and/or queries. To achieve this we’re going to use the JdbcTemplate#queryForMap(String sql, Object… args) method, which will map the result row into key-value pairs, using the column name as the key. This method expects a single result from the query, otherwise it will throw an exception.

Getting all users

To get all the user objects from the database, we’ll use the JdbcTemplate#query(String sql, RowMapper<T> rowMapper) method. The method is also overloaded if you need to pass arguments for your SQL query. The RowMapper interface is used to map each row that is returned from the query and add the results to a list.

Get all usernames

To get all usernames easily, we’re going to use the JdbcTemplate#queryForList(String sql, Class<T> elementType) method, which expects the result from the SQL query to be a single column. The results are then mapped to a list.

Get user city

In order to get a single object from the database, for example the city for a specified user, we’re going to use the JdbcTemplate#queryForObject(String sql, Object[] args, Class<T> requiredType) method, which also expects a single result from the SQL query, which will be directly returned as the specified class if possible.

The examples above are one of the most used methods in the JdbcTemplate toolkit. This class has a heavy method overloading so most of the methods provide a lot of flexibility when executing queries with or without parameters.


You can get this tutorial’s code from GitHub if you’d like to take a closer look. The tutorial is set up as a minimal Spring Boot application with a single controller, so the methods can be tested directly in your browser. The only thing you’ll need however is the test data.

About the author

Daniel Vladimirov

Software engineer and founder of simplyprogram.com

Click here to add a comment

Leave a comment: