Spring Boot RESTful Web services with JdbcTemplate (Spring Boot + JDBC + MySql)

In this article we will see, how to Integrate Spring Boot and Spring JdbcTemplate using MySql Database, we will create a spring Boot RESTful Web services project with Spring JdbcTemplate.

We will create a Spring Boot project to demonstrate how to insert, update, get and delete records from and to MySql using JdbcTemplate, let's first Create a spring maven project and import that in eclipse.

Mysql Query


CREATE DATABASE  IF NOT EXISTS `exampledb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `exampledb`;

DROP TABLE IF EXISTS `STUDENT`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `STUDENT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FIRST_NAME` varchar(45) DEFAULT NULL,
  `LAST_NAME` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Dependencies (pom.xml)

In order to create a Spring Boot JdbcTemplate project to work with MySql we have to add spring-boot-starter-jdbc and mysql-connector-java in pom.xml as shown below:
<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/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.tb.java</groupId>
	<artifactId>SpringBoot2Maven</artifactId>
	<packaging>jar</packaging>
	<version>1.0-SNAPSHOT</version>
	<name>SpringBoot2Maven</name>
	<url>http://maven.apache.org</url>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.3.RELEASE</version>
	</parent>

	<properties>
		<java.version>1.8</java.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</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>
			<exclusions>
				<exclusion>
					<groupId>org.apache.tomcat</groupId>
					<artifactId>tomcat-jdbc</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

	<repositories>
		<repository>
			<id>repository.spring.release</id>
			<name>Spring GA Repository</name>
			<url>http://repo.spring.io/release</url>
		</repository>
	</repositories>
</project>


Add MySql properties configuration

We have to add mysql properties, url, username and password to application.properties file, based on dependencies added to pom.xml and database url passed in application.properties Spring Boot will auto-configure "datasource". /src/main/resources/application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/exampledb?zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=root

spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000 

Create Spring boot application launch class

/src/main/java/com/tb/java/App.java

Here, @SpringBootApplication does all the magic, a class having main() and annotated with @SpringBootApplication is called Spring boot application launch class.
package com.tb.java;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.ComponentScan;

/**
 * Hello world!
 *
 */
@SpringBootApplication
@ComponentScan("com.tb.*")
public class App 
{
    public static void main( String[] args )
    {
    	ApplicationContext ctx = SpringApplication.run(App.class, args);
    }
}

Create Resource Class

We will create add, edit/update, get, list and delete api's for a resource Student.java, lets create the resource model class as shown below:

/src/main/java/com/tb/model/Student.java
package com.tb.model;

public class Student {
	private int id;
	private String firstName;
	private String lastName;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
}

Create Dao layer

This is where we will write actual JDBC code using Spring's JDBCTemplate to interact with MySql database, we have added all create, retrive, update and delete methods, implementation is shown below:

/src/main/java/com/tb/dao/DataDao.java
package com.tb.dao;

import java.util.List;

import com.tb.model.Student;

public interface DataDao {
	public int add(Student student);

	public int update(Student student);

	public Student get(int id);

	public List list();

	public int delete(int id);
}


While using SpringBoot there is no need to configure the DataSource bean, Spring Boot's auto-configuration feature do that work internally based on database properties written in application.properties.

/src/main/java/com/tb/dao/DataDaoImpl.java
package com.tb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import com.tb.model.Student;

@Repository
public class DataDaoImpl implements DataDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int add(final Student student) {
		String query = "INSERT INTO STUDENT(FIRST_NAME,LAST_NAME) VALUES(?,?)";

		KeyHolder holder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, student.getFirstName());
				ps.setString(2, student.getLastName());
				return ps;
			}
		}, holder);

		int newUserId = holder.getKey().intValue();
		return newUserId;
	}

	@Override
	public int update(Student student) {
		String query = "UPDATE STUDENT SET FIRST_NAME = ?, LAST_NAME = ? WHERE ID = ?";
		int row = jdbcTemplate.update(query, student.getFirstName(), student.getLastName(), student.getId());
		return row;
	}

	@Override
	public Student get(int id) {
		String query = "SELECT * FROM STUDENT WHERE ID = ?";
		return jdbcTemplate.queryForObject(query, new Object[] { id }, new StudentMapper());
	}

	@Override
	public List<Student> list() {
		String query = "SELECT * FROM STUDENT";
		return jdbcTemplate.query(query, new StudentMapper());
	}

	class StudentMapper implements RowMapper<Student> {
		@Override
		public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
			Student student = new Student();
			student.setId(rs.getInt("ID"));
			student.setFirstName(rs.getString("FIRST_NAME"));
			student.setLastName(rs.getString("LAST_NAME"));

			return student;
		}
	}

	@Override
	public int delete(int id) {
		String query = "DELETE FROM STUDENT WHERE ID = ?";
		int row = jdbcTemplate.update(query, id);

		return row;
	}
}


Create Service layer

This is simple service layer, we have not done anything here just passing values recieved from Dao to Controller, in real application environment this layer can be handy to apply one more abstraction on the data recieved from Dao layer.

/src/main/java/com/tb/service/DataService.java
package com.tb.service;

import java.util.List;

import com.tb.model.Student;

public interface DataService {
	public int add(Student student);

	public int update(Student student);

	public Student get(int id);

	public List<Student> list();

	public int delete(int id);
}

/src/main/java/com/tb/service/DataServiceImpl.java
package com.tb.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.tb.dao.DataDao;
import com.tb.model.Student;

@Service
public class DataServiceImpl implements DataService {

	@Autowired
	private DataDao dataDao;

	@Override
	public int add(Student student) {
		return dataDao.add(student);
	}

	@Override
	public int update(Student student) {
		return dataDao.update(student);
	}

	@Override
	public Student get(int id) {
		return dataDao.get(id);
	}

	@Override
	public List<Student> list() {
		return dataDao.list();
	}

	@Override
	public int delete(int id) {
		return dataDao.delete(id);
	}

}


Create API Controller

Now lets create actual endpoints for our APIs, StudentApiController is annotated with @RestController, this will make this class work as a controller and there is no need to specify @ResponseBody on each method of the class.

/src/main/java/com/tb/controller/StudentApiController.java
package com.tb.controller;

import java.util.Collection;

import org.springframework.beans.factory.annotation.Autowired;
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 com.tb.model.Student;
import com.tb.service.DataService;

@RestController
@RequestMapping("student")
public class StudentApiController {

	@Autowired
	private DataService dataService;

	/* Adding a resource */
	@PostMapping("/")
	private int add(@RequestBody Student student) {
		return dataService.add(student);
	}

	/* Updating a resource */
	@PutMapping("/")
	private int update(@RequestBody Student student) {
		return dataService.update(student);
	}

	/* Getting a resource */
	@GetMapping("/{id}")
	private Student get(@PathVariable int id) {
		return dataService.get(id);
	}

	/* Getting a list of resources */
	@GetMapping("/")
	private Collection<Student> list() {
		return dataService.list();
	}

	/* Deleting a resource */
	@DeleteMapping("/{id}")
	private int delete(@PathVariable int id) {
		return dataService.delete(id);
	}

}

Run and launch application

Now we are all done with creating "Spring Boot JdbcTemplate CRUD project with MySql", lets run the application from application.

If everything goes right, you will see something like this in Eclipse console, along with log messages printed in the end:

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

2019-03-04 15:06:58.292  INFO 10194 --- [           main] com.tb.java.App                          : Starting App on techie-ThinkPad-13-2nd-Gen with PID 10194 (/home/techie/git/SpringBootJDBC/target/classes started by techie in /home/techie/git/SpringBootJDBC)
2019-03-04 15:06:58.295  INFO 10194 --- [           main] com.tb.java.App                          : No active profile set, falling back to default profiles: default
2019-03-04 15:06:59.713  INFO 10194 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2019-03-04 15:06:59.743  INFO 10194 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2019-03-04 15:06:59.744  INFO 10194 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.16]
2019-03-04 15:06:59.753  INFO 10194 --- [           main] o.a.catalina.core.AprLifecycleListener   : The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/usr/java/packages/lib/amd64:/usr/lib64:/lib64:/lib:/usr/lib]
2019-03-04 15:06:59.826  INFO 10194 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2019-03-04 15:06:59.827  INFO 10194 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1433 ms
2019-03-04 15:07:00.242  INFO 10194 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2019-03-04 15:07:00.627  INFO 10194 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2019-03-04 15:07:00.631  INFO 10194 --- [           main] com.tb.java.App 

Test Rest Api's


1) Add a resource

Method : POST
URL : http://localhost:8080/student/
Content-Type : application/json
Body :
{
  "id": 1,
  "firstName": "Tech",
  "lastName": "Burps"
}


2) Update a resource

Method : PUT
URL : http://localhost:8080/student/
Content-Type : application/json
Body :
{
  "id": 1,
  "firstName": "Tech-New",
  "lastName": "Burps-New"
}


3) Get a resource

Method : GET
URL : http://localhost:8080/student/1


4) Get list of resources

Method : GET
URL : http://localhost:8080/student/


5) Delete resource

Method : DELETE URL : http://localhost:8080/student/1

We are all done with creating a "Spring Boot 2 RESTful Web services project with Spring JDBCTemplate", in upcoming articles we will see more about Spring Boot.

Download "Spring Boot JdbcTemplate CRUD with MySql" source code from: GitHub.

About The Author

Nagesh Chauhan

Nagesh Chauhan has 8+ years of software design and development experience in variety of technologies like - Core Java, Java 8 (Streams, Lambda), J2EE (Servlet, JSP), Spring Framework (MVC, IOC, JDBC, SECURITY etc), Spring Boot and Microservices, Kafla, Redis, Cassandra and Spark.