Spring Boot JDBC Example
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.
Contents
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 eitherspring-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>
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
DataSource and Connection Pool
DataSource and Connection Pool are configured inapplication.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=
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
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; ----------------- }
@Autowired
with constructor.
@Transactional @Repository public class ArticleDAO { private final JdbcTemplate jdbcTemplate; @Autowired public ArticleDAO(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } ----------------- }
RowMapper
Spring JDBC providesRowMapper
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; } }
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); }
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)
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)
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)
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()); }
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); }
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; }
public void updateArticle(Article article) { String sql = "UPDATE articles SET title=?, category=? WHERE articleId=?"; jdbcTemplate.update(sql, article.getTitle(), article.getCategory(), article.getArticleId()); }
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 { ------------------- }
@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.
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
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>
#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
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; } }
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; } }
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); }
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; } } }
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); }
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); } }
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); } }
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); } }
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
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
3. Using Executable JAR: Using command prompt, go to the root folder of the project and run the command.
mvn clean package
java -jar target/spring-boot-demo-0.0.1-SNAPSHOT.jar
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.

I am done now. Happy Spring Boot Learning!
References
Spring Boot Reference GuideSpring Boot REST + JPA + Hibernate + MySQL Example