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

H2 Dependency
<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.

application.properties
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/

Spring Boot H2 Database console login

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.

Spring Boot H2 Database console

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:

Spring Boot H2 Database Jdbc 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_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

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.

schema.sql
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.

data.sql
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:

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

Create Item Repository :

ItemRepository.java
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 :

ItemController.java
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:

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

 

Spring Boot H2 Database 4

Access Application :

Get All Items :

Spring Boot H2 Database GetAllItems

Get Single Item :

Spring Boot H2 Database GetItem

Delete Item From H2 :

Spring Boot H2 Database DeleteItem

Add Item to H2 :

Spring Boot H2 Database AddItem

Get All Items after Adding and Deleting:

Spring Boot H2 Database GetAllItems 2

 

References:

Spring Boot JDBC Template Example

Spring Boot H2 Console ref

Happy Learning 🙂

Download Example