Kotlin – Integrate H2 database with Spring JPA( by Spring Boot) 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 Kotlin 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.

kotlin h2 springboot jpa - architecture - H2-Embedded

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

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

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

kotlin h2 springboot jpa - architecture - 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 Kotlin SpringBoot project
– Configure H2 database
– Implement Kotlin Spring JPA Web Application
– Run & check results

1. Create Kotlin SpringBoot project

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

...
 
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
 
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>
 
...

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 Kotlin Spring JPA Web Application

3.1 Create Customer model


package com.javasampleapproach.kotlin.h2.model

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(
    @Id
    val id: Long = -1,
 
    @Column(name = "firstname")
    val firstName: String = "",
 
    @Column(name = "lastname")
    val lastName: String = ""		
){
	override fun toString(): String{
        return "Customer[id=${id}, firstName=${firstName}, lastName=${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.kotlin.h2.repository

import org.springframework.data.repository.CrudRepository
import com.javasampleapproach.kotlin.h2.model.Customer

interface CustomerRepository: CrudRepository{
    fun findByLastName(lastName: String): List
} 

3.3 Create a WebController

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


package com.javasampleapproach.kotlin.h2.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.kotlin.h2.model.Customer
import com.javasampleapproach.kotlin.h2.repository.CustomerRepository
 
@RestController
class WebController {
	
    @Autowired
    lateinit var repository: CustomerRepository
       
    @RequestMapping("/save")
    fun process(): String{
        repository.save(Customer(1, "Jack", "Smith"))
        repository.save(Customer(2, "Adam", "Johnson"))
        repository.save(Customer(3, "Kim", "Smith"))
        repository.save(Customer(4, "David", "Williams"))
        repository.save(Customer(5, "Peter", "Davis"))
        return "Done"
    }
       
       
    @RequestMapping("/findall")
    fun findAll(): String{
        var result = ""
		
        for(cust in repository.findAll()){
            result += cust.toString() + "
" } return result } @RequestMapping("/findbyid") fun findById(@RequestParam("id") id: Long): String{ return repository.findOne(id).toString() } @RequestMapping("/findbylastname") fun fetchDataByLastName(@RequestParam("lastname") lastName: String): String{ var result = "" for(cust in 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 extends T> S save(S entity); //for @RequestMapping("/save")
T findOne(ID id); //for @RequestMapping("/findbyid")
Iterable<T> 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

kotlin h2 springboot jpa - architecture - H2-database-make-console-request

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

kotlin h2 springboot jpa - architecture - H2-database-first-view

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

kotlin h2 springboot jpa - architecture - h2-database-save-request

Now again select customer table:

kotlin h2 springboot jpa - architecture - h2-database-select-customers

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

kotlin h2 springboot jpa - architecture - h2-database-findall

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

kotlin h2 springboot jpa - architecture - h2-database-find-by-id

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

kotlin h2 springboot jpa - architecture - h2-database-customer-after-restart

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

III. Sourcecode

KotlinSpringBootH2Integration



By grokonez | February 12, 2018.

Last updated on April 16, 2021.



Related Posts


Got Something To Say:

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

*