In this tutorials I am going to show you how to work with Spring Boot JdbcTemplate using MySql Database.

Spring Boot JdbcTemplate :

Technologies :

  • Spring Boot-1.5.10
  • Spring-Boot-Starter-jdbc
  • Java 8
  • MySql 5.5

Project Structure :

Spring Boot JdbcTemplate Example

Spring Boot JdbcTemplate Example :

As part of this tutorials, I am going to implement a complete CRUD operations using Spring Boot JdbcTemplate.

Recommended : Spring JDBCTemplate 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.onlinetutorialspoint</groupId>
  <artifactId>SpringBoot-JDBC-MySQL</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>SpringBoot-JDBC-MySQL</name>
  <description>Demo project for Spring Boot JdbcTemplate</description>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.10.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </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>
</project>

Defining all necessary data source, authentication credentials.

application.properties
# Database
server.port=8080
spring.datasource.driver-class-name: com.mysql.jdbc.Driver
spring.datasource.url: jdbc:mysql://localhost:3306/otp
spring.datasource.username: root
spring.datasource.password: 123456

Database Preparation :

Create your mysql database (otp) and create below item table under otp database.

item Table
CREATE TABLE `item` (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(50) NULL DEFAULT NULL,
  `category` VARCHAR(50) NULL DEFAULT NULL
)
ENGINE=InnoDB;

Created an item table, which represents all items and I am going to do all CRUD operations on this table.

Create Item Model to represent the above table.

Item.java
package com.onlinetutorialspoint.model;

public class Item {
    private int id;
    private String name;
    private String category;

    public Item() {
    }

    public Item(int id, String name, String category) {
        this.id = id;
        this.name = name;
        this.category = category;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }
}

Creating Item Repository :

This is the key class of our example, under which all CRUD operations are happening.

ItemRepository.java
package com.onlinetutorialspoint.repo;

import com.onlinetutorialspoint.model.Item;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class ItemRepository {

    @Autowired
    JdbcTemplate template;

    /*Getting all Items from table*/
    public List<Item> getAllItems(){
        List<Item> items = template.query("select id, name,category from item",(result,rowNum)->new Item(result.getInt("id"),
                result.getString("name"),result.getString("category")));
        return items;
    }
    /*Getting a specific item by item id from table*/
    public Item getItem(int itemId){
        String query = "SELECT * FROM ITEM WHERE ID=?";
        Item item = template.queryForObject(query,new Object[]{itemId},new BeanPropertyRowMapper<>(Item.class));

        return item;
    }
    /*Adding an item into database table*/
    public int addItem(int id,String name,String category){
        String query = "INSERT INTO ITEM VALUES(?,?,?)";
        return template.update(query,id,name,category);
    }
    /*delete an item from database*/
    public int deleteItem(int id){
        String query = "DELETE FROM ITEM WHERE ID =?";
        return template.update(query,id);
    }
}

Create RestController to provide an endpoint to access from outside.

ItemController.java
package com.onlinetutorialspoint.controller;

import com.onlinetutorialspoint.model.Item;
import com.onlinetutorialspoint.repo.ItemRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class ItemController {
    @Autowired
    ItemRepository itemRepo;

    @RequestMapping("/getAllItems")
    @ResponseBody
    public List<Item> getAllItems(){
        return itemRepo.getAllItems();
    }

    @RequestMapping("/getItem")
    @ResponseBody
    public Item getItem(@RequestParam("itemId") int itemId){
        return itemRepo.getItem(itemId);
    }

    @RequestMapping("/addItem")
    @ResponseBody
    public String addItem(@RequestParam("id") int id,@RequestParam("name") String name,
                              @RequestParam("category") String category){
        if(itemRepo.addItem(id,name,category) >= 1){
            return "Item Added Successfully";
        }else{
            return "Something went wrong !";
        }
    }
    @RequestMapping("/deteteItem")
    @ResponseBody
    public String deteteItem(@RequestParam("itemId") int itemId){
        if(itemRepo.deleteItem(itemId) >= 1){
            return "Item Deleted Successfully";
        }else{
            return "Something went wrong !";
        }
    }
}

Application.java

Application.java
package com.onlinetutorialspoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

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

Run it !

mvn clean install
mvn spring-boot:run


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

2018-02-21 07:08:32.553  INFO 5416 --- [           main] com.onlinetutorialspoint.Application     : Starting Application on DESKTOP-RN4SMHT with PID 5416 (E:\work\SpringBoot-JDBC-MyS
QL\target\classes started by Lenovo in E:\work\SpringBoot-JDBC-MySQL)
2018-02-21 07:08:32.559  INFO 5416 --- [           main] com.onlinetutorialspoint.Application     : No active profile set, falling back to default profiles: default
2018-02-21 07:08:32.644  INFO 5416 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebAp
[email protected]: startup date [Wed Feb 21 07:08:32 IST 2018]; root of context hierarchy
2018-02-21 07:08:35.791  INFO 5416 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-02-21 07:08:35.818  INFO 5416 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2018-02-21 07:08:35.820  INFO 5416 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.27
2018-02-21 07:08:36.100  INFO 5416 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2018-02-21 07:08:36.101  INFO 5416 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 3463 ms
2018-02-21 07:08:36.440  INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]
2018-02-21 07:08:36.450  INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-02-21 07:08:36.451  INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2018-02-21 07:08:36.452  INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2018-02-21 07:08:36.453  INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2018-02-21 07:08:37.674  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.Annotatio
[email protected]: startup date [Wed Feb 21 07:08:32 IST 2018]; root of context hierarchy
2018-02-21 07:08:37.814  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/getItem]}" onto public com.onlinetutorialspoint.model.Item com.onlinetu
torialspoint.controller.ItemController.getItem(int)
2018-02-21 07:08:37.816  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/getAllItems]}" onto public java.util.List<com.onlinetutorialspoint.mode
l.Item> com.onlinetutorialspoint.controller.ItemController.getAllItems()
2018-02-21 07:08:37.817  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/addItem]}" onto public java.lang.String com.onlinetutorialspoint.contro
ller.ItemController.addItem(int,java.lang.String,java.lang.String)
2018-02-21 07:08:37.818  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/deteteItem]}" onto public java.lang.String com.onlinetutorialspoint.con
troller.ItemController.deteteItem(int)
2018-02-21 07:08:37.824  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.
Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2018-02-21 07:08:37.826  INFO 5416 --- [           main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servl
et.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2018-02-21 07:08:37.898  INFO 5416 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.
servlet.resource.ResourceHttpRequestHandler]
2018-02-21 07:08:37.899  INFO 5416 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.
resource.ResourceHttpRequestHandler]
2018-02-21 07:08:37.988  INFO 5416 --- [           main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.
web.servlet.resource.ResourceHttpRequestHandler]
2018-02-21 07:08:38.393  INFO 5416 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-02-21 07:08:38.520  INFO 5416 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-02-21 07:08:38.528  INFO 5416 --- [           main] com.onlinetutorialspoint.Application     : Started Application in 6.638 seconds (JVM running for 16.176)

Access the Application :

Getting All Items :

http://localhost:8080/getAllItems

Spring Boot JdbcTemplate Example getAllItems

Add an Item :

http://localhost:8080/addItem?id=4&name=Refrigerator&category=Refrigerator

Spring Boot JdbcTemplate Example addItem

Getting All Items After adding :

http://localhost:8080/getAllItems

Sping Boot JDBCTemplate getAllItems again

Delete an Item from the list :

Sping Boot JDBCTemplate deleteItem

References :

Spring JDBC Template Example

JDBC Template Docs

Happy Learning 🙂

Download Example