This guide walks you through the process of accessing relational data with Spring.
You will build an application that uses Spring’s JdbcTemplate to access data stored in a
relational database.
For all Spring applications, you should start with the Spring Initializr. The Initializr offers a fast way to pull in all the dependencies you need for an application and does a lot of the setup for you. This example needs the JDBC API and H2 Database dependencies.
The following listing shows the pom.xml file that is created when you choose Maven:
link:initial/pom.xml[role=include]
The following listing shows the build.gradle file that is created when you choose Gradle:
link:initial/build.gradle[role=include]
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]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 thecom.example.relationaldataaccesspackage. 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/master/build_an_executable_jar_mainhead.adoc https://raw.githubusercontent.com/spring-guides/getting-started-macros/master/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']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. |
The following guides may also be helpful: