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
-
About 15 minutes
-
A favorite text editor or IDE
-
JDK 1.8 or later
-
You can also import the code straight into your IDE:
How to complete this guide
Like most Spring Getting Started guides, you can start from scratch and complete each step, or you can bypass basic setup steps that are already familiar to you. Either way, you end up with working code.
To start from scratch, move on to Build with Gradle.
To skip the basics, do the following:
-
Download and unzip the source repository for this guide, or clone it using Git:
git clone https://github.com/spring-guides/gs-relational-data-access.git
-
cd into
gs-relational-data-access/initial
-
Jump ahead to Create a Customer object.
When you’re finished, you can check your results against the code in gs-relational-data-access/complete
.
Build with Gradle
Build with Gradle
First you set up a basic build script. You can use any build system you like when building apps with Spring, but the code you need to work with Gradle and Maven is included here. If you’re not familiar with either, refer to Building Java Projects with Gradle or Building Java Projects with Maven.
Create the directory structure
In a project directory of your choosing, create the following subdirectory structure; for example, with mkdir -p src/main/java/hello
on *nix systems:
└── src └── main └── java └── hello
Create a Gradle build file
Below is the initial Gradle build file.
build.gradle
buildscript {
repositories {
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:2.0.1.RELEASE")
}
}
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'
bootJar {
baseName = 'gs-relational-data-access'
version = '0.1.0'
}
repositories {
mavenCentral()
}
sourceCompatibility = 1.8
targetCompatibility = 1.8
dependencies {
compile("org.springframework.boot:spring-boot-starter")
compile("org.springframework:spring-jdbc")
compile("com.h2database:h2")
testCompile("junit:junit")
}
The Spring Boot gradle plugin provides many convenient features:
-
It collects all the jars on the classpath and builds a single, runnable "über-jar", which makes it more convenient to execute and transport your service.
-
It searches for the
public static void main()
method to flag as a runnable class. -
It provides a built-in dependency resolver that sets the version number to match Spring Boot dependencies. You can override any version you wish, but it will default to Boot’s chosen set of versions.
Build with Maven
Build with Maven
First you set up a basic build script. You can use any build system you like when building apps with Spring, but the code you need to work with Maven is included here. If you’re not familiar with Maven, refer to Building Java Projects with Maven.
Create the directory structure
In a project directory of your choosing, create the following subdirectory structure; for example, with mkdir -p src/main/java/hello
on *nix systems:
└── src └── main └── java └── hello
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.springframework</groupId>
<artifactId>gs-relational-data-access</artifactId>
<version>0.1.0</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
The Spring Boot Maven plugin provides many convenient features:
-
It collects all the jars on the classpath and builds a single, runnable "über-jar", which makes it more convenient to execute and transport your service.
-
It searches for the
public static void main()
method to flag as a runnable class. -
It provides a built-in dependency resolver that sets the version number to match Spring Boot dependencies. You can override any version you wish, but it will default to Boot’s chosen set of versions.
Build with your IDE
Build with your IDE
-
Read how to import this guide straight into Spring Tool Suite.
-
Read how to work with this guide in IntelliJ IDEA.
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
package hello;
public class Customer {
private long id;
private String firstName, lastName;
public Customer(long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return String.format(
"Customer[id=%d, firstName='%s', lastName='%s']",
id, firstName, lastName);
}
// getters & setters omitted for brevity
}
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
package hello;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
@SpringBootApplication
public class Application implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(Application.class);
public static void main(String args[]) {
SpringApplication.run(Application.class, args);
}
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void run(String... strings) throws Exception {
log.info("Creating tables");
jdbcTemplate.execute("DROP TABLE customers IF EXISTS");
jdbcTemplate.execute("CREATE TABLE customers(" +
"id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");
// Split up the array of whole names into an array of first/last names
List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
.map(name -> name.split(" "))
.collect(Collectors.toList());
// Use a Java 8 stream to print out each tuple of the list
splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));
// Uses JdbcTemplate's batchUpdate operation to bulk load data
jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);
log.info("Querying for customer records where first_name = 'Josh':");
jdbcTemplate.query(
"SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
(rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
).forEach(customer -> log.info(customer.toString()));
}
}
@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 thehello
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.
For single insert statements, JdbcTemplate’s `insert method is good. But for multiple inserts, it’s better to use batchUpdate . |
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.
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. |
Build an executable JAR
You can run the application from the command line with Gradle or Maven. Or you can build a single executable JAR file that contains all the necessary dependencies, classes, and resources, and run that. This makes it easy to ship, version, and deploy the service as an application throughout the development lifecycle, across different environments, and so forth.
If you are using Gradle, you can run the application using ./gradlew bootRun
. Or you can build the JAR file using ./gradlew build
. Then you can run the JAR file:
If you are using Maven, you can run the application using ./mvnw spring-boot:run
. Or you can build the JAR file with ./mvnw clean package
. Then you can run the JAR file:
The procedure above will create a runnable JAR. You can also opt to build a classic WAR file instead. |
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.
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:
Want to write a new guide or contribute to an existing one? Check out our contribution guidelines.
All guides are released with an ASLv2 license for the code, and an Attribution, NoDerivatives creative commons license for the writing. |