Home > Spring 5

Spring JDBC Example

By Arvind Rai, January 29, 2018

This page will walk through Spring JDBC example. Spring provides JdbcTemplate for database operations using JDBC. JdbcTemplate is configured using DataSource in JavaConfig or XML configuration. Then we inject JdbcTemplate in our DAO using @Autowire annotation. JdbcTemplate provides methods such as queryForObject(), query(), update() etc to perform database operations. For transaction management we can create DataSourceTransactionManager bean in JavaConfig or XML configuration which is the PlatformTransactionManager implementation for a single JDBC DataSource. To use transaction management, we need to use @Transactional annotation either at class level or method level in our DAO. Spring JDBC provides RowMapper interface that we need to implement to map a database table column names with java class fields. If database table columns and java class fields are same name, 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 REST application that will perform CREATE, READ, UPDATE and DELETE operations using JDBC in MySQL database. Now find the complete example step by step.

1. Technologies Used
2. Maven and Gradle for JDBC
3. JdbcTemplate
4. Transaction Management
5. Spring REST + JDBC + MySQL CRUD Example using JavaConfig
6. Spring REST + JDBC + MySQL CRUD Example using XML Configuration
7. REST Client with RestTemplate
8. Run Application
9. References
10. Download Source Code

1. Technologies Used

Find the technologies being used in our example.
1. Java 9
2. Spring 5.0.5.RELEASE
3. Spring Boot 2.0.1.RELEASE
4. Gradle 4.3.1
5. Maven 3.5.2
6. Tomcat 8.5
7. MySQL 5.5
8. Eclipse Oxygen

2. Maven and Gradle for JDBC

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 and Gradle dependencies for them.
a. Maven dependency for spring-boot-starter-jdbc .

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

Or we can use spring-boot-starter-data-jpa .

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

b. Gradle dependency for spring-boot-starter-jdbc .

compile 'org.springframework.boot:spring-boot-starter-jdbc:2.0.1.RELEASE'

Or we can use spring-boot-starter-data-jpa .

compile 'org.springframework.boot:spring-boot-starter-data-jpa:2.0.1.RELEASE'

3. JdbcTemplate

JdbcTemplate is the central class to handle JDBC operations. This class executes SQL queries or updates. JdbcTemplate simplifies use of JDBC and avoids common errors. To create JdbcTemplate instance, we need to pass DataSource and then we can use JdbcTemplate methods to run SQL queries. We will discuss here JdbcTemplate configuration, RowMapper and running SQL queries.

3.1 JdbcTemplate Configuration

We configure JdbcTemplate in JavaConfig or XML configuration. Find the JavaConfig to configure JdbcTemplate .

@Configuration 
@EnableTransactionManagement
@PropertySource("classpath:database.properties")
public class DBConfig {
	@Autowired
        private Environment env;	
        @Bean
	public DataSource getDataSource() {
	    BasicDataSource dataSource = new BasicDataSource();
	    dataSource.setDriverClassName(env.getProperty("database.driverClassName"));
	    dataSource.setUrl(env.getProperty("database.url"));
	    dataSource.setUsername(env.getProperty("database.username"));
	    dataSource.setPassword(env.getProperty("database.password"));
	    return dataSource;
	}
	@Bean
	public JdbcTemplate jdbcTemplate() {
	    return new JdbcTemplate(getDataSource());
	}
	@Bean(name="transactionManager")
	public PlatformTransactionManager txManager(){
	    DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
	    return transactionManager;
	}	
}

Find the XML configuration to configure JdbcTemplate .

<context:property-placeholder location="classpath:database.properties"/> 
<bean id="basicDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
   <property name="driverClassName" value="${database.driverClassName}" />
   <property name="url" value="${database.url}" />
   <property name="username" value="${database.username}" />
   <property name="password" value="${database.password}" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
   <property name="dataSource" ref="basicDataSource" />
</bean>	
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
   <property name="dataSource" ref="basicDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />

Now in our DAO, we can inject JdbcTemplate using @Autowired.

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

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

3.2 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 database table column names and our class fields name are of 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);
}

3.3 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);
}

4. Transaction Management

For transaction management we will use DataSourceTransactionManager. We will create a bean of it. Find the code snippet using JavaConfig.

@Configuration 
@EnableTransactionManagement
@PropertySource("classpath:database.properties")
public class DBConfig {
        ------
	@Bean(name="transactionManager")
	public PlatformTransactionManager txManager(){
	    DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
	    return transactionManager;
	}	
}

Find the code snippet using XML configuration.

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	<property name="dataSource" ref="basicDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />

DataSourceTransactionManager is the PlatformTransactionManager implementation for single JDBC DataSource. DataSourceTransactionManager works with any JDBC driver as long as connection factory mechanism uses javax.sql.DataSource.
For transaction management in our DAO, we need to use Spring @Transactional annotation at class level or method level in which we use JdbcTemplate to run SQL queries. 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
}

5. Spring REST + JDBC + MySQL CRUD Example using JavaConfig

Find the project structure with JavaConfig in Eclipse.

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

CREATE DATABASE IF NOT EXISTS `concretepage`;
USE `concretepage`;
-- Dumping structure for table concretepage.articles
CREATE TABLE IF NOT EXISTS `articles` (
  `articleId` bigint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `category` varchar(100) NOT NULL,
  PRIMARY KEY (`articleId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data for table concretepage.articles
INSERT INTO `articles` (`articleId`, `title`, `category`) VALUES
	(1, 'Java Concurrency', 'Java'),
	(2, 'Spring Boot Getting Started', 'Spring Boot');

Now find the complete example.
build.gradle

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'war'
war.archiveName 'spring-app.war'

sourceCompatibility = 9

repositories {
    mavenCentral()
}
dependencies {
    compile 'org.springframework.boot:spring-boot-starter-web:2.0.1.RELEASE'
    compile 'org.springframework.boot:spring-boot-starter-jdbc:2.0.1.RELEASE'
    compile 'mysql:mysql-connector-java:6.0.5'
    compile 'org.apache.commons:commons-dbcp2:2.1.1'
    compile 'javax.xml.bind:jaxb-api:2.3.0'
    providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat:2.0.1.RELEASE'    
}

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-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>Spring</name>
	<description>Spring Demo Project</description>
	<parent>
	    <groupId>org.springframework.boot</groupId>
  	    <artifactId>spring-boot-starter-parent</artifactId>
	    <version>2.0.1.RELEASE</version>
 	    <relativePath/>
	</parent>
	<properties>
	    <context.path>spring-app</context.path>
	    <java.version>9</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-jdbc</artifactId>
	  </dependency>
	  <dependency>
		 <groupId>mysql</groupId>
		 <artifactId>mysql-connector-java</artifactId>
		 <version>6.0.5</version>
	  </dependency>	  	  	  
	  <dependency>
		 <groupId>org.apache.commons</groupId>
		 <artifactId>commons-dbcp2</artifactId>
		 <version>2.1.1</version>
	  </dependency>
	  <dependency>
		 <groupId>javax.xml.bind</groupId>
		 <artifactId>jaxb-api</artifactId>
		 <version>2.3.0</version>
	  </dependency>  	      	  	  	  
	  <dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-tomcat</artifactId>
		<scope>provided</scope>
	  </dependency>      
	</dependencies>
	<build>
	  <plugins>
	    <plugin>
		  <groupId>org.apache.maven.plugins</groupId>
		  <artifactId>maven-war-plugin</artifactId>
		  <version>3.2.0</version>
		  <configuration>
			 <warName>${context.path}</warName>
		  </configuration>
		</plugin>
	  </plugins>
	</build>
</project>

DBConfig.java

package com.concretepage.config;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration 
@EnableTransactionManagement
@PropertySource("classpath:database.properties")
public class DBConfig {
	@Autowired
        private Environment env;	
        @Bean
	public DataSource getDataSource() {
	    BasicDataSource dataSource = new BasicDataSource();
	    dataSource.setDriverClassName(env.getProperty("database.driverClassName"));
	    dataSource.setUrl(env.getProperty("database.url"));
	    dataSource.setUsername(env.getProperty("database.username"));
	    dataSource.setPassword(env.getProperty("database.password"));
	    return dataSource;
	}
	@Bean
	public JdbcTemplate jdbcTemplate() {
	    return new JdbcTemplate(getDataSource());
	}
	@Bean(name="transactionManager")
	public PlatformTransactionManager txManager() {
	    DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
	    return transactionManager;
	}	
}

database.properties

database.driverClassName=com.mysql.cj.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/concretepage
database.username=root
database.password=cp

AppConfig.java

package com.concretepage.config;  
import java.util.List;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.http.converter.json.Jackson2ObjectMapperBuilder;
import org.springframework.http.converter.json.MappingJackson2HttpMessageConverter;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
@Configuration 
@ComponentScan("com.concretepage") 
@EnableWebMvc   
public class AppConfig implements WebMvcConfigurer {
    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
        Jackson2ObjectMapperBuilder builder = new Jackson2ObjectMapperBuilder();
        builder.indentOutput(true);
        converters.add(new MappingJackson2HttpMessageConverter(builder.build()));
    }
}

WebAppInitializer.java

package com.concretepage.config;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;
public class WebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer  {
	@Override
	protected Class<?>[] getRootConfigClasses() {
	     return new Class[] { AppConfig.class };
	}
	@Override
        protected Class<?>[] getServletConfigClasses() {
             return null;
        }
        @Override
        protected String[] getServletMappings() {
             return new String[]{ "/" };
        } 
}

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.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.bind.annotation.RestController;
import org.springframework.web.util.UriComponentsBuilder;
import com.concretepage.entity.Article;
import com.concretepage.service.IArticleService;
@RestController
@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);
	}	
}

6. Spring REST + JDBC + MySQL CRUD Example using XML Configuration

Find the project structure with XML configuration in Eclipse.

Here we will provide only XML files. Other files will be same as given in project using JavaConfig.
db-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/tx 
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd">

	<context:property-placeholder location="classpath:database.properties"/> 
	<bean id="basicDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
	    <property name="driverClassName" value="${database.driverClassName}" />
	    <property name="url" value="${database.url}" />
	    <property name="username" value="${database.username}" />
	    <property name="password" value="${database.password}" />
	</bean>
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	    <property name="dataSource" ref="basicDataSource" />
	</bean>	
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
	    <property name="dataSource" ref="basicDataSource" />
	</bean>
	<tx:annotation-driven transaction-manager="transactionManager" /> 		
</beans>

dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context.xsd"> 
        
	<context:component-scan base-package="com.concretepage" />
	<bean name="jackson2ObjectMapper" class="org.springframework.http.converter.json.Jackson2ObjectMapperFactoryBean">
	    <property name="indentOutput" value="true"/>
	</bean>    
	<mvc:annotation-driven>
	    <mvc:message-converters>
	        <bean class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter">
	            <property name="objectMapper" ref="jackson2ObjectMapper" />
	        </bean>
	    </mvc:message-converters>
	</mvc:annotation-driven>
</beans>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
    http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">

	<display-name>Spring Demo Project</display-name>
	<context-param>
	    <param-name>contextConfigLocation</param-name>
	    <param-value>
	       /WEB-INF/dispatcher-servlet.xml
	       /WEB-INF/db-config.xml
	    </param-value>		    
	</context-param>	
	<servlet>
	    <servlet-name>dispatcher</servlet-name>
	    <servlet-class>
		  org.springframework.web.servlet.DispatcherServlet
	    </servlet-class>
	    <load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
	    <servlet-name>dispatcher</servlet-name>
	    <url-pattern>/</url-pattern>
	</servlet-mapping>
	<listener>
	    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
</web-app>

7. REST Client with RestTemplate

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/spring-app/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/spring-app/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/spring-app/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/spring-app/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/spring-app/user/article/{id}";
        HttpEntity<Article> requestEntity = new HttpEntity<Article>(headers);
        restTemplate.exchange(url, HttpMethod.DELETE, requestEntity, Void.class, 3);        
    }
    public static void main(String args[]) {
    	RestClientUtil util = new RestClientUtil();
        //util.getArticleByIdDemo();
    	//util.addArticleDemo();
    	//util.updateArticleDemo();
    	//util.deleteArticleDemo();
    	util.getAllArticlesDemo();    	
    }    
}

8. Run Application

To build and run the demo application, follow the steps.
1. Create table in MySQL database given above on this page.
2. Download the source code from the link given below on this page.
3. Go to the root directory of the project using command prompt.
4. Build the project using gradle with following command.

gradle clean build

We can find WAR file in the build\libs directory. If we want to build the project using maven, use following command.

mvn clean package

We can find WAR file in the target directory.
5. Deploy the WAR file using tomcat.
6. To test the application, use RestClientUtil.java file given in the demo project. We can also test application using Postman. To get all articles, we need to run following URL.

http://localhost:8080/spring-app/user/articles

Find the print screen.

9. References

Data access with JDBC
Spring Boot JDBC Example

10. Download Source Code

Source Code using JavaConfig

Source Code using XML Configuration

POSTED BY

ARVIND RAI

Popular Tutorials: Java 8 | Spring 4 | Angular | Struts 2 | Android

FIND MORE TUTORILAS

Spring Boot Hibernate PrimeFaces RESTEasy FreeMarker