Spring Boot JDBC Example

By Arvind Rai, August 11, 2017

This page will walk through Spring boot JDBC example. Spring provides JdbcTemplate class for database operations using JDBC. JdbcTemplate class is auto-configured and we get its object using @Autowire annotation in our class that is annotated with spring stereotypes such as @Component. JdbcTemplate provides methods such as queryForObject(), query(), update() etc to perform database operations. In application.properties file we configure DataSource and connection pooling. Spring boot chooses tomcat pooling by default. Transaction management is performed by using spring @Transactional annotation either at class level or method level. Spring JDBC provides RowMapper interface that is implemented to map a database table row with java object. If table column name and java entity fields name are same, then we can directly use Spring JDBC BeanPropertyRowMapper to map a row with java object.
Here on this page we will provide a CRUD operation using JdbcTemplate. We will create a Spring boot REST application that will perform CREATE, READ, UPDATE and DELETE operation using JDBC in MySQL database. Now find the complete example step by step.

Technologies Used
JDBC Dependency using Maven
DataSource and Connection Pool
JdbcTemplate : Dependency Injection using @Autowired
RowMapper
JdbcTemplate : Run SQL Queries
Transaction Management
Spring Boot REST + JDBC + MySQL CRUD Example
Test Application
References
Download Source Code

Technologies Used

Find the technologies being used in our application.
1. Java 8
2. Spring Boot 1.5.6.RELEASE
3. Maven 3.3
4. MySQL 5.5
5. Eclipse Mars

JDBC Dependency using Maven

Spring JDBC dependencies can be resolved by using either spring-boot-starter-jdbc or spring-boot-starter-data-jpa spring boot starters. Find the maven dependencies for them. We can use following maven dependency.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Or we can use following maven dependency to resolve spring JDBC dependencies.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

DataSource and Connection Pool

DataSource and Connection Pool are configured in application.properties file using prefix spring.datasource. Spring boot uses javax.sql.DataSource interface to configure DataSource. Suppose we want to integrate MySQL then we will configure DataSource as following.
application.properties

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=

Now we will configure connection pooling. Spring Boot uses tomcat pooling by default for performance and concurrency. When we use spring-boot-starter-jdbc or spring-boot-starter-data-jpa for JDBC dependency injection then tomcat-jdbc is automatically resolved. We configure tomcat connection pooling as following.

spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15

We need to add above configurations in application.properties file.

JdbcTemplate : Dependency Injection using @Autowired

JdbcTemplate is the central class to handle JDBC. It executes SQL queries and fetches their results. To use JdbcTemplate we need to instantiate it in our application using dependency injection. We can autowire JdbcTemplate in the classes annotated with spring stereotypes such as @Component, @Service, @Repository and @Controller.
Find the dependency injection of JdbcTemplate using @Autowired with property.

@Transactional
@Repository
public class ArticleDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    -----------------
}

Now find the dependency injection using @Autowired with constructor.

@Transactional
@Repository
public class ArticleDAO {
    private final JdbcTemplate jdbcTemplate;
    @Autowired
    public ArticleDAO(JdbcTemplate jdbcTemplate) {
	  this.jdbcTemplate = jdbcTemplate;
    }

    -----------------
}

RowMapper

Spring JDBC provides RowMapper interface that is used to map row with a java object. We need to create our own class implementing RowMapper interface to map row with java object. Find the sample code to implement RowMapper interface.

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper; 
public class ArticleRowMapper implements RowMapper<Article> {
   @Override
   public Article mapRow(ResultSet row, int rowNum) throws SQLException {
	Article article = new Article();
	article.setArticleId(row.getInt("articleId"));
	article.setTitle(row.getString("title"));
	article.setCategory(row.getString("category"));
	return article;
   }
}

We can use our ArticleRowMapper with JdbcTemplate as given below.

public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new ArticleRowMapper();
   return this.jdbcTemplate.query(sql, rowMapper);
}

Spring JDBC provides BeanPropertyRowMapper that implements RowMapper. We can directly use it in place of custom RowMapper. We use BeanPropertyRowMapper in the scenario when table column name and our entity class fields name are same. Then we can change above code as following.

public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
   return this.jdbcTemplate.query(sql, rowMapper);
}

JdbcTemplate : Run SQL Queries

JdbcTemplate provides methods to run DML and DDL SQL queries. Find the example of some of them.
a. JdbcTemplate.queryForObject :

<T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args)

This method fetches data for a given SQL query as an object using RowMapper. SQL query can have bind parameters. Find the description of parameters.
sql: SQL containing bind parameter.
rowMapper: Object of RowMapper implemented class. RowMapper will map one object per row.
args: Arguments that bind to the query.

b. JdbcTemplate.query:

<T> List<T> query(String sql,RowMapper<T> rowMapper)

This method executes static query and maps rows to java objects using RowMapper. Find the description of parameters.
sql: SQL query to execute.
rowMapper: Object of RowMapper implemented class. RowMapper will map one object per row.

c. JdbcTemplate.update:

int update(String sql, Object... args)

This method executes insert, update and delete statements. Find the description of parameters.
sql: SQL containing bind parameter.
args: Arguments that bind to the query.

Now we will perform CREATE, READ, UPDATE and DELETE (CRUD) operation.
1. CREATE: Find sample code snippet for CREATE operation.

public void addArticle(Article article) {
   String sql = "INSERT INTO articles (articleId, title, category) values (?, ?, ?)";
   jdbcTemplate.update(sql, article.getArticleId(), article.getTitle(), article.getCategory());
}

2. READ: Find sample code snippet for READ operation.

public List<Article> getAllArticles() {
   String sql = "SELECT articleId, title, category FROM articles";
   RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);		
   return this.jdbcTemplate.query(sql, rowMapper);
}

The above method will return a list of objects. If we want to fetch a single object, we can write READ operation code as follows.

public Article getArticleById(int articleId) {
	String sql = "SELECT articleId, title, category FROM articles WHERE articleId = ?";
	RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);	
	Article article = jdbcTemplate.queryForObject(sql, rowMapper, articleId);
	return article;
}

3. UPDATE : Find sample code snippet for UPDATE operation.

public void updateArticle(Article article) {
    String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?";
    jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId());
}

4. DELETE : Find sample code snippet for DELETE operation.

public void deleteArticle(int articleId) {
	String sql = "DELETE FROM articles WHERE articleId=?";
	jdbcTemplate.update(sql, articleId);
}

Transaction Management

For transaction management we need to use Spring @Transactional annotation at class level or method level in which we use JdbcTemplate to run SQL queries. Using @Transactional means for any failure in insert or update operation, complete operation is roll backed. Spring @Transactional is used at class level as following.

@Transactional
@Repository
public class ArticleDAO {
-------------------
}

When we use @Transactional at class level, all methods of the class will become transactional. If we want to make selected methods transactional, we need to use @Transactional at method level as following.

@Transactional
public void addArticle(Article article) {
  //Database operation using JdbcTemplate
}

Spring Boot REST + JDBC + MySQL CRUD Example

Find the project structure in eclipse.

Find the MySQL table used in our example.
Table: articles

CREATE TABLE `articles` (
	`articleId` INT(5) NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(200) NOT NULL,
	`category` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`articleId`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

Now find the complete example.
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>com.concretepage</groupId>
	<artifactId>spring-boot-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<name>spring-demo</name>
	<description>Spring Boot Demo Project</description>
	<parent>
	        <groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.6.RELEASE</version>
	</parent>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-web</artifactId>
	    </dependency>
	    <dependency>
		 <groupId>org.springframework.boot</groupId>
		 <artifactId>spring-boot-starter-data-jpa</artifactId>
	    </dependency>	
	    <dependency>
		 <groupId>mysql</groupId>
		 <artifactId>mysql-connector-java</artifactId>
	    </dependency>	 
    	    <dependency>
                 <groupId>org.springframework.boot</groupId>
                 <artifactId>spring-boot-devtools</artifactId>
                 <optional>true</optional>
            </dependency> 
	</dependencies> 
	<build>
	   <plugins>
		<plugin>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-maven-plugin</artifactId>
		</plugin>
	    </plugins>
	</build>
</project>

application.properties

#DataSource Configuration
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=

#Connection Pool Configuration
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15

Article.java

package com.concretepage.entity;
public class Article { 
        private int articleId;  
        private String title;
	private String category;
	public int getArticleId() {
		return articleId;
	}
	public void setArticleId(int articleId) {
		this.articleId = articleId;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
}

ArticleRowMapper.java

package com.concretepage.entity;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class ArticleRowMapper implements RowMapper<Article> {
	@Override
	public Article mapRow(ResultSet row, int rowNum) throws SQLException {
		Article article = new Article();
		article.setArticleId(row.getInt("articleId"));
		article.setTitle(row.getString("title"));
		article.setCategory(row.getString("category"));
		return article;
	}
}

IArticleDAO.java

package com.concretepage.dao;
import java.util.List;
import com.concretepage.entity.Article;
public interface IArticleDAO {
    List<Article> getAllArticles();
    Article getArticleById(int articleId);
    void addArticle(Article article);
    void updateArticle(Article article);
    void deleteArticle(int articleId);
    boolean articleExists(String title, String category);
}

ArticleDAO.java

package com.concretepage.dao;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.concretepage.entity.Article;
import com.concretepage.entity.ArticleRowMapper;
@Transactional
@Repository
public class ArticleDAO implements IArticleDAO {
	@Autowired
        private JdbcTemplate jdbcTemplate;
	@Override
	public Article getArticleById(int articleId) {
		String sql = "SELECT articleId, title, category FROM articles WHERE articleId = ?";
		RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		Article article = jdbcTemplate.queryForObject(sql, rowMapper, articleId);
		return article;
	}
	@Override
	public List<Article> getAllArticles() {
		String sql = "SELECT articleId, title, category FROM articles";
                //RowMapper<Article> rowMapper = new BeanPropertyRowMapper<Article>(Article.class);
		RowMapper<Article> rowMapper = new ArticleRowMapper();
		return this.jdbcTemplate.query(sql, rowMapper);
	}	
	@Override
	public void addArticle(Article article) {
		//Add article
		String sql = "INSERT INTO articles (articleId, title, category) values (?, ?, ?)";
		jdbcTemplate.update(sql, article.getArticleId(), article.getTitle(), article.getCategory());
		
		//Fetch article id
		sql = "SELECT articleId FROM articles WHERE title = ? and category=?";
		int articleId = jdbcTemplate.queryForObject(sql, Integer.class, article.getTitle(), article.getCategory());
		
		//Set article id 
		article.setArticleId(articleId);
	}
	@Override
	public void updateArticle(Article article) {
		String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?";
		jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId());
	}
	@Override
	public void deleteArticle(int articleId) {
		String sql = "DELETE FROM articles WHERE articleId=?";
		jdbcTemplate.update(sql, articleId);
	}
	@Override
	public boolean articleExists(String title, String category) {
		String sql = "SELECT count(*) FROM articles WHERE title = ? and category=?";
		int count = jdbcTemplate.queryForObject(sql, Integer.class, title, category);
		if(count == 0) {
    		        return false;
		} else {
			return true;
		}
	}
}

IArticleService.java

package com.concretepage.service;
import java.util.List;
import com.concretepage.entity.Article;
public interface IArticleService {
     List<Article> getAllArticles();
     Article getArticleById(int articleId);
     boolean addArticle(Article article);
     void updateArticle(Article article);
     void deleteArticle(int articleId);
}

ArticleService.java

package com.concretepage.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.concretepage.dao.IArticleDAO;
import com.concretepage.entity.Article;
@Service
public class ArticleService implements IArticleService {
	@Autowired
	private IArticleDAO articleDAO;
	@Override
	public Article getArticleById(int articleId) {
		Article obj = articleDAO.getArticleById(articleId);
		return obj;
	}	
	@Override
	public List<Article> getAllArticles(){
		return articleDAO.getAllArticles();
	}
	@Override
	public synchronized boolean addArticle(Article article){
                if (articleDAO.articleExists(article.getTitle(), article.getCategory())) {
    	          return false;
                } else {
    	          articleDAO.addArticle(article);
    	          return true;
                }
	}
	@Override
	public void updateArticle(Article article) {
		articleDAO.updateArticle(article);
	}
	@Override
	public void deleteArticle(int articleId) {
		articleDAO.deleteArticle(articleId);
	}
}

ArticleController.java

package com.concretepage.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.util.UriComponentsBuilder;
import com.concretepage.entity.Article;
import com.concretepage.service.IArticleService;
@Controller
@RequestMapping("user")
public class ArticleController {
	@Autowired
	private IArticleService articleService;
	@GetMapping("article/{id}")
	public ResponseEntity<Article> getArticleById(@PathVariable("id") Integer id) {
		Article article = articleService.getArticleById(id);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@GetMapping("articles")
	public ResponseEntity<List<Article>> getAllArticles() {
		List<Article> list = articleService.getAllArticles();
		return new ResponseEntity<List<Article>>(list, HttpStatus.OK);
	}
	@PostMapping("article")
	public ResponseEntity<Void> addArticle(@RequestBody Article article, UriComponentsBuilder builder) {
                boolean flag = articleService.addArticle(article);
                if (flag == false) {
        	   return new ResponseEntity<Void>(HttpStatus.CONFLICT);
                }
                HttpHeaders headers = new HttpHeaders();
                headers.setLocation(builder.path("/article/{id}").buildAndExpand(article.getArticleId()).toUri());
                return new ResponseEntity<Void>(headers, HttpStatus.CREATED);
	}
	@PutMapping("article")
	public ResponseEntity<Article> updateArticle(@RequestBody Article article) {
		articleService.updateArticle(article);
		return new ResponseEntity<Article>(article, HttpStatus.OK);
	}
	@DeleteMapping("article/{id}")
	public ResponseEntity<Void> deleteArticle(@PathVariable("id") Integer id) {
		articleService.deleteArticle(id);
		return new ResponseEntity<Void>(HttpStatus.NO_CONTENT);
	}	
}

To start the web service, run following class as java application.
MyApplication.java

package com.concretepage;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class MyApplication {  
	public static void main(String[] args) {
	   SpringApplication.run(MyApplication.class, args);
        }       
}

To test the REST web service, run following class as java application.
RestClientUtil.java

package com.concretepage.client;
import java.net.URI;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;
import com.concretepage.entity.Article;
public class RestClientUtil {
    public void getArticleByIdDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article.class, 1);
        Article article = responseEntity.getBody();
        System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                 +", Category:"+article.getCategory());      
    }
    public void getAllArticlesDemo() {
	HttpHeaders headers = new HttpHeaders();
	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/articles";
        HttpEntity<String> requestEntity = new HttpEntity<String>(headers);
        ResponseEntity<Article[]> responseEntity = restTemplate.exchange(url, HttpMethod.GET, requestEntity, Article[].class);
        Article[] articles = responseEntity.getBody();
        for(Article article : articles) {
              System.out.println("Id:"+article.getArticleId()+", Title:"+article.getTitle()
                      +", Category: "+article.getCategory());
        }
    }
    public void addArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setTitle("Spring REST Security using Hibernate");
	objArticle.setCategory("Spring");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        URI uri = restTemplate.postForLocation(url, requestEntity);
        System.out.println(uri.getPath());    	
    }
    public void updateArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article";
	Article objArticle = new Article();
	objArticle.setArticleId(1);
	objArticle.setTitle("Update:Java Concurrency");
	objArticle.setCategory("Java");
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(objArticle, headers);
        restTemplate.put(url, requestEntity);
    }
    public void deleteArticleDemo() {
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.APPLICATION_JSON);
        RestTemplate restTemplate = new RestTemplate();
	String url = "http://localhost:8080/user/article/{id}";
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(headers);
        restTemplate.exchange(url, HttpMethod.DELETE, requestEntity, Void.class, 1);        
    }
    public static void main(String args[]) {
    	RestClientUtil util = new RestClientUtil();
        //util.getArticleByIdDemo();
    	//util.addArticleDemo();
    	//util.updateArticleDemo();
    	//util.deleteArticleDemo();
    	util.getAllArticlesDemo();    	
    }    
}

Test Application

To test the application, first create table in MySQL as given in the example. Now we can run REST web service in following ways.
1. Using Eclipse: Download the project source code using the download link given at the end of page. Import the project into eclipse. Using command prompt, go to the root folder of the project and run.

mvn clean eclipse:eclipse

and then refresh the project in eclipse. Run Main class MyApplication by clicking Run as -> Java Application. Tomcat server will be started.

2. Using Maven Command: Download the project source code. Go to the root folder of the project using command prompt and run the command.

mvn spring-boot:run

Tomcat server will be started.

3. Using Executable JAR: Using command prompt, go to the root folder of the project and run the command.

mvn clean package

We will get executable JAR spring-boot-demo-0.0.1-SNAPSHOT.jar in target folder. Run this JAR as

java -jar target/spring-boot-demo-0.0.1-SNAPSHOT.jar

Tomcat server will be started.

Now we are ready to test the application. To run client, go to the RestClientUtil class in eclipse and click on Run as -> Java Application.
We can also test application using Postman. Find the print screen.