Table of Contents

• What You Will Build
• What You Need
• Starting with Spring Initializr
• Create a Customer Object
• Store and Retrieve Data
• Summary
• See Also

This guide walks you through the process of accessing relational data with Spring.

What You Will Build

You will build an application that uses Spring’s JdbcTemplate to access data stored in a relational database.

What You Need

https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/prereq_editor_jdk_buildtools.adoc

https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/how_to_complete_this_guide.adoc

Starting with Spring Initializr

You can use this pre-initialized project and click Generate to download a ZIP file. This project is configured to fit the examples in this tutorial.

To manually initialize the project:

1. Navigate to https://start.spring.io. This service pulls in all the dependencies you need for an application and does most of the setup for you.

2. Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java.

3. Click Dependencies and select JDBC API and H2 Database.

4. Click Generate.

5. Download the resulting ZIP file, which is an archive of a web application that is configured with your choices.

Note	If your IDE has the Spring Initializr integration, you can complete this process from your IDE.

Note	You can also fork the project from Github and open it in your IDE or other editor.

Create a Customer Object

The simple data access logic you will work with manages the first and last names of customers. To represent this data at the application level, create a Customer class, as the following listing (from src/main/java/com/example/relationaldataaccess/Customer.java) shows:

link:complete/src/main/java/com/example/relationaldataaccess/Customer.java[role=include]

Store and Retrieve Data

Spring provides a template class called JdbcTemplate that makes it easy to work with SQL relational databases and JDBC. Most JDBC code is mired in resource acquisition, connection management, exception handling, and general error checking that is wholly unrelated to what the code is meant to achieve. The JdbcTemplate takes care of all of that for you. All you have to do is focus on the task at hand. The following listing (from src/main/java/com/example/relationaldataaccess/RelationalDataAccessApplication.java) shows a class that can store and retrieve data over JDBC:

link:complete/src/main/java/com/example/relationaldataaccess/RelationalDataAccessApplication.java[role=include]

@SpringBootApplication is a convenience annotation that adds all of the following:

• @Configuration: Tags the class as a source of bean definitions for the application context.

• @EnableAutoConfiguration: Tells Spring Boot to start adding beans, based on classpath settings, other beans, and various property settings.

• @ComponentScan: Tells Spring to look for other components, configurations, and services in the com.example.relationaldataaccess package. In this case, there are none.

The main() method uses Spring Boot’s SpringApplication.run() method to launch an application.

Spring Boot supports H2 (an in-memory relational database engine) and automatically creates a connection. Because we use spring-jdbc, Spring Boot automatically creates a JdbcTemplate. The @Autowired JdbcTemplate field automatically loads it and makes it available.

This Application class implements Spring Boot’s CommandLineRunner, which means it will execute the run() method after the application context is loaded.

First, install some DDL by using the execute method of JdbcTemplate.

Second, take a list of strings and, by using Java 8 streams, split them into firstname/lastname pairs in a Java array.

Then install some records in your newly created table by using the batchUpdate method of JdbcTemplate. The first argument to the method call is the query string. The last argument (the array of Object instances) holds the variables to be substituted into the query where the ? characters are.

Tip	For single insert statements, the insert method of JdbcTemplate is good. However, for multiple inserts, it is better to use batchUpdate.

Important

Use ? for arguments to avoid SQL injection attacks by instructing JDBC to bind variables.

Finally, use the query method to search your table for records that match the criteria. You again use the ? arguments to create parameters for the query, passing in the actual values when you make the call. The last argument is a Java 8 lambda that is used to convert each result row into a new Customer object.

Note	Java 8 lambdas map nicely onto single method interfaces, such as Spring’s RowMapper. If you use Java 7 or earlier, you can plug in an anonymous interface implementation and have the method body be the same as the lambda expression’s body.

https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/build_an_executable_jar_mainhead.adoc https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/build_an_executable_jar_with_both.adoc

You should see the following output:

2019-09-26 13:46:58.561  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Creating tables
2019-09-26 13:46:58.564  INFO 47569 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-09-26 13:46:58.708  INFO 47569 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-09-26 13:46:58.809  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Inserting customer record for John Woo
2019-09-26 13:46:58.810  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Inserting customer record for Jeff Dean
2019-09-26 13:46:58.810  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Inserting customer record for Josh Bloch
2019-09-26 13:46:58.810  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Inserting customer record for Josh Long
2019-09-26 13:46:58.825  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Querying for customer records where first_name = 'Josh':
2019-09-26 13:46:58.835  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Customer[id=3, firstName='Josh', lastName='Bloch']
2019-09-26 13:46:58.835  INFO 47569 --- [           main] c.e.r.RelationalDataAccessApplication    : Customer[id=4, firstName='Josh', lastName='Long']

Summary

Congratulations! You have just used Spring to develop a simple JDBC client.

Note	Spring Boot has many features for configuring and customizing the connection pool — for instance, to connect to an external database instead of an in-memory one. See the Reference Guide for more detail.

See Also

The following guides may also be helpful:

• Accessing Data with JPA

• Accessing Data with MongoDB

• Accessing Data with GemFire

• Accessing Data with Neo4j

• Accessing data with MySQL

• Building an Application with Spring Boot

https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/footer.adoc