Spring Boot JdbcTemplate CRUD Operations Mysql

By | 2018-02-21T17:05:25+00:00 February 22nd, 2018|Spring Boot|0 Comments

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>

application.properties

Defining all necessary data source, authentication credentials.

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

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

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
plicationContext@1ac73b13: 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
nConfigEmbeddedWebApplicationContext@1ac73b13: 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=}" 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

About the Author:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment