Spring Boot H2 Database + JDBC Template Example

By | 2018-04-21T10:30:58+00:00 February 23rd, 2018|Spring Boot|0 Comments

In this tutorials, 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 H2 dependency in pom.xml

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

Step 2: Enable the H2-console as part of your Spring boot application by setting the spring.h2.console.enabled property in application.properties file.

spring.h2.console.enabled=true

which enables 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 own 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 used :

  • 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

spring.h2.console.enabled=true

Preparing Database Schema :

Create schema.sql and data.sql files under [highlight color=”pink”]/resources/[/highlight] 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 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.

 

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

This way we can use H2 Database in Spring Boot.

References :

Spring Boot JDBC Template Example

Spring Boot H2 Console ref

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