Spring Boot JDBC Example

By Arvind Rai, November 29, 2023

On this page, I will create an application using Spring Boot and JDBC that will perform CRUD operation. Spring provides JdbcTemplate class for database operations. JdbcTemplate class is auto-configured and we get its object using @Autowire annotation in our components classes annotated with stereotypes annotations. JdbcTemplate provides methods such as queryForObject(), query(), update() to perform database operations. In application.properties file, we need to configure datasource and connection pooling. 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 create Spring Boot REST CRUD application using JdbcTemplate.

1. Maven Dependencies
2. Configuiring DataSource and Connection Pool
3. Using JdbcTemplate
4. Using RowMapper
5. Run SQL Queries
6. Transaction Management
7. Complete Example
8. Reference
9. Download Source Code

1. Maven Dependencies

Spring JDBC dependencies is resolved by using either spring-boot-starter-jdbc or spring-boot-starter-data-jpa starters.

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

Or use following maven dependency.

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

2. Configuiring DataSource and Connection Pool

DataSource and Connection Pool are configured in application.properties file using prefix spring.datasource. Spring Boot uses javax.sql.DataSource 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= cp

#configure tomcat connection pooling
spring.datasource.tomcat.max-wait=20000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-idle=20
spring.datasource.tomcat.min-idle=15

3. Using JdbcTemplate

Spring 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 annotations.
Find the code snippet.

@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;
    }

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

4. Using RowMapper

Spring JDBC provides RowMapper 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. In this case, 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);
}

5. 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)

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)

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)

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

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

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 below.

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

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

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

6. 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. Any failure in insert or update operation, complete operation is rollbacked for transactional methods. Spring @Transactional is used at class level as well as method level as following.

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

When we use @Transactional at class level, all methods of the class becomes 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
}

7. Complete 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`)
)

Now find the complete code.
Article.java

public class Article { 
        private int articleId;  
        private String title;
        private String category;
       // Setters and Getters
}

ArticleRowMapper.java

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

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

@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

public interface IArticleService {
     List<Article> getAllArticles();
     Article getArticleById(int articleId);
     boolean addArticle(Article article);
     void updateArticle(Article article);
     void deleteArticle(int articleId);
}

ArticleService.java

@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

@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

@SpringBootApplication
public class MyApplication {  
	public static void main(String[] args) {
	   SpringApplication.run(MyApplication.class, args);
        }       
}

Find the print screen of the output.