In this tutorial, we are going to show how to integrate Spring Boot H2 Database using JDBC Template.

Spring Boot H2 Database:

Spring boot provides an in-memory database called H2 database, which is mostly used while developing quick POCs and unit level testing.

How to Enable Spring Boot H2 Database:

Step 1:

Add the following H2 dependency in pom.xml

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

Step 2:

Enable the H2-console in your Spring boot application by setting the spring.h2.console.enabled property in application.properties file.

spring.h2.console.enabled=true

Which allows the h2-console after running your spring boot application. After completing these two steps, you can see the H2 console on your browser like below.

Access application url: http://localhost:8080/h2-console/

Enter JDBC URL as jdbc:h2:mem:testdb and click on Connect.

Note: Leave password as black.

Then you can able to see the below H2 home console with default schema.

Now we can use this H2 DB and create our schema under testdb.

As part of this tutorial I am going to create a simple Item table under testdb and will do necessary CRUD operations on Item table.

Spring Boot H2 Database Example :

Technologies:

• Spring Boot 1.5.10.RELEASE
• H2 Database
• Spring Boot Starter JDBC
• Java8

Project Structure:

<?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_H2_Database</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>SpringBoot_H2_Database</name>
  <description>Demo project for Spring Boot H2 Database</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>com.h2database</groupId>
      <artifactId>h2</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

spring.h2.console.enabled=true

Preparing Database Schema:

Create schema.sql and data.sql files under /resources folder, so that spring boot will pick these files to prepare database while loading application.

schema.sql

Create schema.sql which having database schema.

CREATE TABLE `item` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(50) NULL DEFAULT NULL,
  `category` VARCHAR(50) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

data.sql

Create data.sql which having data of schema.

INSERT INTO `item` (`id`, `name`, `category`) VALUES (1, 'IPhone 6S', 'Mobile');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (2, 'Samsung Galaxy', 'Mobile');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (3, 'Lenovo', 'Laptop');
INSERT INTO `item` (`id`, `name`, `category`) VALUES (4, 'LG', 'Telivision');

Create Item Model:

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

Create Item Repository :

package com.onlinetutorialspoint.repository;

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 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 Rest Controller :

package com.onlinetutorialspoint.controller;
import java.util.List;
import com.onlinetutorialspoint.model.Item;
import com.onlinetutorialspoint.repository.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 !";
        }
    }
}

Run the Application:

mvn clean install
mvn spring-boot:run

2018-02-22 08:34:26.024  INFO 7644 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/E:/work/SpringBoot_H2_Database/target/classes
/schema.sql]
2018-02-22 08:34:26.040  INFO 7644 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/E:/work/SpringBoot_H2_Database/target/classes/
schema.sql] in 16 ms.
2018-02-22 08:34:26.040  INFO 7644 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/E:/work/SpringBoot_H2_Database/target/classes
/data.sql]
2018-02-22 08:34:26.055  INFO 7644 --- [           main] o.s.jdbc.datasource.init.ScriptUtils     : Executed SQL script from URL [file:/E:/work/SpringBoot_H2_Database/target/classes/
data.sql] in 0 ms.

You can observe the script executions while running application like above.

Access H2-console again to see our Item table and data.

Access Application :

Get All Items :

Get Single Item :

Delete Item From H2 :

Add Item to H2 :

Get All Items after Adding and Deleting:

References:

Spring Boot JDBC Template Example

Spring Boot H2 Console ref

Happy Learning 🙂