jamilsonbatista/gs-relational-data-access

Accessing Relational Data using JDBC with Spring :: Learn how to access relational data with Spring.

tags	projects
spring-data jdbc	spring-framework spring-boot

Table of Contents

• What you’ll build
• What you’ll need
• 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’ll build

You’ll build an application using Spring’s JdbcTemplate to access data stored in a relational database.

What you’ll need

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

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

https://raw.githubusercontent.com/spring-guides/getting-started-macros/master/hide-show-gradle.adoc

https://raw.githubusercontent.com/spring-guides/getting-started-macros/master/hide-show-maven.adoc

https://raw.githubusercontent.com/spring-guides/getting-started-macros/master/hide-show-sts.adoc

Create a Customer object

The simple data access logic you will work with below manages first and last names of customers. To represent this data at the application level, create a Customer class.

src/main/java/hello/Customer.java

link:complete/src/main/java/hello/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.

src/main/java/hello/Application.java

link:complete/src/main/java/hello/Application.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 the hello package. In this case, there aren’t any.

The main() method uses Spring Boot’s SpringApplication.run() method to launch an application. Did you notice that there wasn’t a single line of XML? No web.xml file either. This web application is 100% pure Java and you didn’t have to deal with configuring any plumbing or infrastructure.

Spring Boot supports H2, an in-memory relational database engine, and automatically creates a connection. Because we are using 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 up.

First, you install some DDL using JdbcTemplate’s `execute method.

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

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

Note	For single insert statements, JdbcTemplate’s `insert method is good. But for multiple inserts, it’s better to use batchUpdate.

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

Finally you use the query method to search your table for records matching 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 used to convert each result row into a new Customer object.

Note	Java 8 lambdas map nicely onto single method interfaces, like Spring’s RowMapper. If you are using Java 7 or earlier, you can easily plug in an anonymous interface implementation and have the same method body as the lambda expresion’s body contains, and it will work with no fuss from Spring.

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:

2015-06-19 10:58:31.152  INFO 67731 --- [           main] hello.Application                        : Creating tables
2015-06-19 10:58:31.219  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for John Woo
2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Jeff Dean
2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Josh Bloch
2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Josh Long
2015-06-19 10:58:31.230  INFO 67731 --- [           main] hello.Application                        : Querying for customer records where first_name = 'Josh':
2015-06-19 10:58:31.242  INFO 67731 --- [           main] hello.Application                        : Customer[id=3, firstName='Josh', lastName='Bloch']
2015-06-19 10:58:31.242  INFO 67731 --- [           main] hello.Application                        : Customer[id=4, firstName='Josh', lastName='Long']
2015-06-19 10:58:31.244  INFO 67731 --- [           main] hello.Application                        : Started Application in 1.693 seconds (JVM running for 2.054)

Summary

Congratulations! You’ve 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. Please refer to the User 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/master/footer.adoc