Integrate H2 database with SpringBoot & Spring JPA in Embedded mode

H2 database is a pure Java, open source and very fast engine. We can use H2 for development, testing and performance benchmarking. So in the tutorial, JavaSampleApproach will show how to integrate H2 database with SpringBoot and Spring JPA in Embedded mode.

I. H2 database and SpringBoot integration

H2 database has small footprint (smaller than 1.5 MB) with low memory requirements. It supports for multiple schemas and standard SQL, JDBC API. We can use H2 with disk based or in-memory databases.

H2 can be built by following mode:
– Embedded mode (local connections)
– Server mode (remote connections)
– Mixed mode (local and remote connections)

In the tutorial, We focus on Embedded mode to integrate H2 database and SpringBoot Application.

H2 Embedded

With Embedded Mode, an application uses JDBC to create a H2 database within the same JVM so it’s very fast to exchange data.

How to configure it with SpringBoot?
-> Spring Boot has a built in H2 database, so We just add below dependency:


    com.h2database
    h2

H2 supports a convenient web based data console to interact with database while the development:

H2 Database - console

II. Practice

In the tutorial, We create a Spring JPA Web application which uses Spring JPA to interact with H2 database in Embedded mode.

Step to do:
– Create SpringBoot project
– Configure H2 database
– Implement Spring JPA Web Application
– Run & check results

1. Create SpringBoot project

– Using SpringToolSuite, create a SpringBoot project. Then add list dependencies {web, jpa, h2}:

...


	org.springframework.boot
	spring-boot-starter-web


	org.springframework.boot
	spring-boot-starter-data-jpa



    com.h2database
    h2


...
2. Configure H2 database

Open application.properties file, add configurations:

spring.h2.console.enabled=true
spring.h2.console.path=/h2_console
spring.datasource.url=jdbc:h2:file:~/h2/testdb
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver
spring.jpa.hibernate.ddl-auto = update
spring.jpa.show-sql=true

Set spring.h2.console.enabled is true to enable web console of H2 database.
spring.h2.console.path is used to define H2 console’s url.
Default setting values of H2 database:
username/password: sa/’blank’
driverClassName: org.h2.Driver
datasource.url: jdbc:h2:mem:testdb

Important!:
jdbc:h2:mem is used to define In-memory databases.
jdbc:h2:file is used to define disk-based databases.

3. Implement Spring JPA Web Application
3.1 Create Customer model
package com.javasampleapproach.h2database.model;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
 
@Entity
@Table(name = "customer")
public class Customer implements Serializable {
 
    private static final long serialVersionUID = -3009157732242241606L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
 
    @Column(name = "firstname")
    private String firstName;
 
    @Column(name = "lastname")
    private String lastName;
 
    protected Customer() {
    }
 
    public Customer(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }
 
    @Override
    public String toString() {
        return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);
    }
}

Annotation @Entity indicates that Customer is an Entity and @Table specifies the primary table (name customer) for the annotated @Entity.

@ID specifies the primary key and @GeneratedValue indicates generation strategy for value of primary key.

@Column: mapped column (in the table) for persistent fields (in Java class).

3.2 Create CustomerRepository

This interface helps us do all CRUD functions for class Customer.

package com.javasampleapproach.h2database.repository;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

import com.javasampleapproach.h2database.model.Customer;

public interface CustomerRepository extends CrudRepository{
    List findByLastName(String lastName);
} 
3.3 Create a WebController

Create 3 @RequestMapping:
– @RequestMapping(“/save”)
– @RequestMapping(“/findall”)
– @RequestMapping(“/findbyid”)

package com.javasampleapproach.h2database.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.javasampleapproach.h2database.model.Customer;
import com.javasampleapproach.h2database.repository.CustomerRepository;

@RestController
public class WebController {
    @Autowired
    CustomerRepository repository;
       
    @RequestMapping("/save")
    public String process(){
        repository.save(new Customer("Jack", "Smith"));
        repository.save(new Customer("Adam", "Johnson"));
        repository.save(new Customer("Kim", "Smith"));
        repository.save(new Customer("David", "Williams"));
        repository.save(new Customer("Peter", "Davis"));
        return "Done";
    }
       
       
    @RequestMapping("/findall")
    public String findAll(){
        String result = "";
           
        for(Customer cust : repository.findAll()){
            result += cust.toString() + "
"; } return result; } @RequestMapping("/findbyid") public String findById(@RequestParam("id") long id){ String result = ""; result = repository.findOne(id).toString(); return result; } @RequestMapping("/findbylastname") public String fetchDataByLastName(@RequestParam("lastname") String lastName){ String result = ""; for(Customer cust: repository.findByLastName(lastName)){ result += cust.toString() + "
"; } return result; } }

In the web controller methods which are annotated by @RequestMapping, we have used some methods of autowired repository which are implemented interface CrudRepository:

 S save(S entity); //for @RequestMapping("/save")
T findOne(ID id); //for @RequestMapping("/findbyid")
Iterable findAll(); //for @RequestMapping("/findall")

and the method findByLastName that we create in our interface CustomerRepository.

List findByLastName(String lastName);
4. Run & check results

Build and Run the SpringBoot with commandlines: mvn clean install and mvn spring-boot:run.
Make a request: http://localhost:8080/h2_console

H2 database - make console request

Input login’s info as above image. Then press Connect, and makes a first select for customer table:

H2 database - first view

Make a request: http://localhost:8080/save

Now again select customer table:

h2 database - select customers

Make a request: http://localhost:8080/findall,

h2 database - findall

Make a request http://localhost:8080/findbyid?id=5,

h2 database - find by id

Now, do restart the SpringBoot App, then select customer table again:

h2 database - customer after restart

Why?
-> Because we use Disk-memory database by setting: spring.datasource.url=jdbc:h2:file:~/h2/testdb.

III. Sourcecode

SpringBootH2Integration

50 thoughts on “Integrate H2 database with SpringBoot & Spring JPA in Embedded mode”

  1. I imported the project in STS and used the option “Run as Spring Boot App”. Then ran the “Select * from Customer” in H2Console. I received error “Table Customer not found”. Please advise , whether there is a step to create the table

    1. Hi Sudhir,

      Spring Boot chooses a default value for you based on whether it thinks your database is embedded:
      spring.jpa.hibernate.ddl-auto = create-drop

      So it will auto create tables for H2 database

      Regards,
      JSA

  2. I have a question I’m wondering if you know the answer to..

    This code auto increments perfectly when adding the customers from the /save endpoint…
    but if i start by populating the table with a data.sql file in the resources folder, then the /save enpoint doesn’t add the customers.
    Any idea how to solve this?
    Thanks!

  3. I have seen lots of useful things on your web-site about computers. However, I’ve got the judgment that notebook computers are still less than powerful enough to be a option if you typically do projects that require plenty of power, like video modifying. But for net surfing, word processing, and quite a few other popular computer functions they are okay, provided you may not mind small screen size. Appreciate sharing your opinions.

  4. Please let me know if you’re looking for a writer for your
    blog. You have some really good articles and I think I would
    be a good asset. If you ever want to take some of the
    load off, I’d love to write some material for your blog in exchange for a link back to
    mine. Please shoot me an e-mail if interested. Kudos!

  5. When I initially commented I clicked the “Notify me when new comments are added” checkbox
    and now each time a comment is added I get four e-mails with the same comment.
    Is there any way you can remove people from that service?
    Appreciate it!

  6. Heya i’m for the first time here. I found this board and I find It truly helpful & it helped me out a lot.
    I’m hoping to present something again and help others
    such as you aided me.

  7. It’s the best time to make some plans for the future and it’s
    time to be happy. I have read this post and if I
    could I desire to suggest you few attention-grabbing issues or tips.
    Perhaps you could write next articles referring to
    this article. I want to learn more things about it!

  8. 623726 391163Following study several with the content in your web site now, we genuinely such as your technique of blogging. I bookmarked it to my bookmark web website list and will also be checking back soon. Pls take a look at my web-site likewise and make me aware what you believe. 986968

  9. hello!,I like your writing very a lot! percentage we keep up a correspondence extra about your article on AOL?
    I require an expert in this area to resolve my problem. May be that is you!
    Taking a look forward to peer you.

  10. I blog quite often and I genuinely appreciate your
    information. This article has truly peaked my interest.
    I’m going to bookmark your site and keep checking for new information about once a week.
    I opted in for your Feed as well.

  11. When someone writes an piece of writing he/she maintains the idea of a user in his/her brain that how a user can know it.

    So that’s why this article is outstdanding. Thanks!

  12. 715910 532500Hi, Neat post. Theres a difficulty along with your website in web explorer, could test this IE nonetheless will be the marketplace leader and a good portion of people will omit your superb writing because of this difficulty. 33466

  13. It’s truly a nice and helpful piece of information. I am
    satisfied that you simply shared this helpful information with us.

    Please keep us up to date like this. Thank you for
    sharing.

  14. Awesome website you have here but I was wanting to know
    if you knew of any message boards that cover the same topics talked about
    here? I’d really love to be a part of online community where I
    can get opinions from other experienced people that share the same interest.
    If you have any suggestions, please let me know. Bless you!

  15. My relatives always say that I am killing my time here at web, except I know I am getting familiarity all the time
    by reading such good articles or reviews.

  16. I really love your blog.. Great colors & theme. Did you create this amazing site yourself?

    Please reply back as I’m trying to create my very own website and would like to find out where
    you got this from or what the theme is named. Kudos!

  17. It is in point of fact a nice and helpful piece of info. I’m glad that you simply shared this helpful information with us.
    Please keep us informed like this. Thank you for sharing.

  18. Hi! This post could not be written any better!
    Reading through this post reminds me of my old room mate!

    He always kept chatting about this. I will forward this write-up to him.
    Pretty sure he will have a good read. Many thanks for sharing!

  19. Hmm is anyone else encountering problems with the pictures on this blog loading?
    I’m trying to find out if its a problem on my end or if it’s the blog.
    Any feedback would be greatly appreciated.

  20. Howdy! I know this is kind of off-topic but
    I needed to ask. Does managing a well-established blog like yours take a lot of work?
    I am brand new to running a blog however I do write in my journal
    everyday. I’d like to start a blog so I can easily share my own experience and views online.
    Please let me know if you have any suggestions or tips for brand new aspiring blog owners.
    Appreciate it!

  21. Your style is so unique in comparison to other people I have read stuff from.
    Thanks for posting when you’ve got the opportunity,
    Guess I will just book mark this web site.

  22. Thanks for some other great article. The place else could anybody get that type
    of info in such a perfect manner of writing? I have a presentation next week, and I’m on the look for such information.

  23. Hello there, I discovered your blog via Google at the same time as
    searching for a comparable topic, your site came up,
    it looks good. I have bookmarked it in my google bookmarks.

    Hi there, just become alert to your blog thru Google, and found that it is really informative.
    I’m gonna watch out for brussels. I will appreciate for
    those who continue this in future. Lots of people can be benefited from your writing.
    Cheers!

  24. Have you ever considered about including a
    little bit more than just your articles? I mean, what you
    say is important and all. Nevertheless imagine if you added some great pictures or videos
    to give your posts more, “pop”! Your content is excellent but with
    pics and videos, this site could certainly be one of the best in its field.
    Terrific blog!

  25. Excellent post. I was checking constantly this
    blog and I am impressed! Extremely helpful info specially the last part :
    ) I care for such information much. I was looking for this certain info for a very long time.
    Thank you and best of luck.

  26. That is very fascinating, You are an overly skilled blogger.

    I’ve joined your rss feed and sit up for in search of
    extra of your great post. Additionally, I’ve shared your site
    in my social networks

  27. My spouse and I stumbled over here different web page and thought I might check things out.
    I like what I see so i am just following you.
    Look forward to going over your web page yet again.

  28. Thank you for the auspicious writeup. It in fact was a amusement account it.

    Look advanced to far added agreeable from you! By the way, how can we communicate?

Leave a Reply

Your email address will not be published. Required fields are marked *