gs-relational-data-access

gs-relational-data-access copied to clipboard

:toc: :icons: font :source-highlighter: prettify :project_id: gs-relational-data-access

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

:java_version: 17 include::https://raw.githubusercontent.com/spring-guides/getting-started-macros/main/prereq_editor_jdk_buildtools.adoc[]

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

[[scratch]] == Starting with Spring Initializr

You can use this https://start.spring.io/#!type=maven-project&language=java&packaging=jar&jvmVersion=11&groupId=com.example&artifactId=relational-data-access&name=relational-data-access&description=Demo%20project%20for%20Spring%20Boot&packageName=com.example.relational-data-access&dependencies=jdbc,h2[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:

. 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. . Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java. . Click Dependencies and select JDBC API and H2 Database. . Click Generate. . 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.

[[initial]] == 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:

==== [source,java,tabsize=2]

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

====

== 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:

==== [source,java,tabsize=2]

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

====

@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 https://en.wikipedia.org/wiki/SQL_injection[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.

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

You should see the following output:

==== [source,bash]

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 http://docs.spring.io/spring-boot/docs/2.2.1.RELEASE/reference/htmlsingle/#boot-features-configure-datasource[Reference Guide] for more detail.

== See Also

The following guides may also be helpful:

• https://spring.io/guides/gs/accessing-data-jpa/[Accessing Data with JPA]
• https://spring.io/guides/gs/accessing-data-mongodb/[Accessing Data with MongoDB]
• https://spring.io/guides/gs/accessing-data-gemfire/[Accessing Data with GemFire]
• https://spring.io/guides/gs/accessing-data-neo4j/[Accessing Data with Neo4j]
• https://spring.io/guides/gs/accessing-data-mysql/[Accessing data with MySQL]
• https://spring.io/guides/gs/spring-boot/[Building an Application with Spring Boot]

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