Boot

Spring Boot JDBC Example

Do you want to learn about using Spring Boot with JDBC? Yes? Then you have come to the right place. This example is very straight forward and simple, even a 12 year old can understand. Let’s begin with our Spring Boot JDBC example.

1. Tools

  1. Apache Maven
  2. Mars Eclipse
  3. Spring Boot
  4. H2 Database Engine

2. Assumptions

This article assumes that you know your way around Eclipse. You are familiar with Maven. And you are familiar with SQL statements. Basically, you have done some coding. This project has been created using Eclipse Mars so all instructions are based on this IDE.

3. Project Setup

To start, we create our project. This can be done by going to File -> New -> Maven Project and fill up what is required. Alternatively, we can import the Maven project by going to File -> Import… and picking the project. Download the project here: spring-boot-jdbc.zip.

4. Project Object Model

Our pom.xml should look like the one below:

pom.xml

<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>com.javacodegeeks.example</groupId>
  <artifactId>spring-boot-jdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.9.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>

As shown above, our project has 2 dependencies. We are using spring-boot-starter-jdbc which means that we can use all the Spring modules included in it. For example, the Spring Core and Spring JDBC modules will be available for us to use plus many more. The next dependency is the H2 Database Engine. We will be utilizing H2’s in-memory database for this example.
The Spring Boot Maven plugin enables us to package the project as an executable jar.

5. Code Walkthrough

Our code below performs the four basic operations of persistent storage. That is create, read, update, delete, in short CRUD. Skim through the code below but peruse the explanation after it.

Main.java

package com.javacodegeeks.example;

import java.sql.ResultSet;
import java.sql.SQLException;

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 org.springframework.jdbc.core.RowCallbackHandler;

@SpringBootApplication
public class Main implements CommandLineRunner {
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	public static void main(String[] args) {
		SpringApplication.run(Main.class, args);
	}

	public void run(String... arg0) throws Exception {
		System.out.println("Building tables");
		jdbcTemplate.execute("DROP TABLE movies IF EXISTS");
		jdbcTemplate.execute("CREATE TABLE movies(id SERIAL, title VARCHAR(255), description VARCHAR(255))");
		
		System.out.println("\nCreating 3 movie records...");
		jdbcTemplate.update("INSERT INTO movies(title, description) VALUES (?, ?)", "Mr. Deeds", "Comedy");
		jdbcTemplate.update("INSERT INTO movies(title, description) VALUES (?, ?)", "Mad Max Fury Road", "Science Fiction");
		jdbcTemplate.update("INSERT INTO movies(title, description) VALUES (?, ?)", "We Were Soldiers", "War");
		
		readRecords();
		
		System.out.println("\nUpdating Mad Max Fury Road record...");
		jdbcTemplate.update("UPDATE movies SET description = ? WHERE title = ?", "Action/Adventure", "Mad Max Fury Road");
		
		readRecords();
		
		System.out.println("\nDeleting Mr. Deeds record...");
		jdbcTemplate.update("DELETE FROM movies WHERE title = ?", "Mr. Deeds");
		
		readRecords();
	}
	
	private void readRecords() {
		System.out.println("Reading movie records...");
		System.out.printf("%-30.30s  %-30.30s%n", "Title", "Description");
		jdbcTemplate.query("SELECT * FROM movies", new RowCallbackHandler() {

			public void processRow(ResultSet rs) throws SQLException {
				System.out.printf("%-30.30s  %-30.30s%n", rs.getString("title"), rs.getString("description"));
			}
			
		});
	}

}

Instead of annotating our class with @Configuration, @EnableAutoConfiguration, and @ComponentScan, we use the @SpringBootApplication annotation as a convenient alternative. This annotation tells Spring Boot to scan for other components, add beans based on the classpath, and tags the class as a source of bean definitions.

We implemented the CommandLineRunner because we want to execute the run method after the application context is loaded.

Spring Boot automatically creates JdbcTemplate because we are using the Spring JDBC module (remember the spring-boot-starter-jdbc dependency?). @Autowired automatically loads JdbcTemplate.

The main method uses SpringApplication.run() to run the application.

Walking through the run method, we first created the table. Next we added the movie records. For simplicity’s sake, we’re inserting records one at time. There is a batchUpdate method which is best used for multiple inserts.

We then used the query method to execute a SELECT statement and our RowCallbackHandler handles the printing of the movie records. Lastly, the records are printed in a nice column.

The update method is for the UPDATE and DELETE SQL statements. We use the ? to avoid SQL injection attacks. JDBC binds the variables for us.

6. Spring Boot JDBC Output

After running the code above (Run As -> Java Application), we should have an output that looks like the one below.

Console Output

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.9.RELEASE)

2018-01-13 14:47:41.385  INFO 3880 --- [           main] com.javacodegeeks.example.Main           : Starting Main on asus_k43s with PID 3880 (D:\javacodegeeks_com\spring-boot-jdbc\spring-boot-jdbc\target\classes started by jpllosa in D:\javacodegeeks_com\spring-boot-jdbc\spring-boot-jdbc)
2018-01-13 14:47:41.393  INFO 3880 --- [           main] com.javacodegeeks.example.Main           : No active profile set, falling back to default profiles: default
2018-01-13 14:47:41.626  INFO 3880 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@6fb0d3ed: startup date [Sat Jan 13 14:47:41 GMT 2018]; root of context hierarchy
2018-01-13 14:47:45.153  INFO 3880 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
Building tables

Creating 3 movie records...
Reading movie records...
Title                           Description                   
Mr. Deeds                       Comedy                        
Mad Max Fury Road               Science Fiction               
We Were Soldiers                War                           

Updating Mad Max Fury Road record...
Reading movie records...
Title                           Description                   
Mr. Deeds                       Comedy                        
Mad Max Fury Road               Action/Adventure              
We Were Soldiers                War                           

Deleting Mr. Deeds record...
Reading movie records...
Title                           Description                   
Mad Max Fury Road               Action/Adventure              
We Were Soldiers                War                           
2018-01-13 14:47:45.816  INFO 3880 --- [           main] com.javacodegeeks.example.Main           : Started Main in 5.252 seconds (JVM running for 6.102)
2018-01-13 14:47:45.820  INFO 3880 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@6fb0d3ed: startup date [Sat Jan 13 14:47:41 GMT 2018]; root of context hierarchy
2018-01-13 14:47:45.844  INFO 3880 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown

The output shows the CRUD operations being performed. We created 3 movie records. We read the movie records from the in-memory database. After that, we updated the “Mad Max Fury Road” description, changing it from “Science Fiction” to “Action/Adventure”. Then we deleted the “Mr. Deeds” movie record. Easy peasy lemon squeezy.

7. Spring Boot JDBC Summary

In summary, we include the spring-boot-starter-jdbc dependency to make available all the Spring modules we need to make JDBC operations. We then add the database dependency, in this case H2. We then use JdbcTemplate to perform our SQL commands. That’s all there is to it.

8. Download the Source Code

This is an example about Spring Boot JDBC.

Download
You can download the source code of this example here: spring-boot-jdbc.zip.

Joel Patrick Llosa

I graduated from Silliman University in Dumaguete City with a degree in Bachelor of Science in Business Computer Application. I have contributed to many Java related projects at Neural Technologies Ltd., University of Southampton (iSolutions), Predictive Technologies, LLC., Confluence Service, North Concepts, Inc., NEC Telecom Software Philippines, Inc., and NEC Technologies Philippines, Inc. You can also find me in Upwork freelancing as a Java Developer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button