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.
Contents
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.
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:
<dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> </dependency> |
H2 supports a convenient web based data console to interact with database while the development:
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}:
... <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 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<Customer, Long>{ List<Customer> 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() + "</br>"; } 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() + "</br>"; } 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<Customer> 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
Input login’s info as above image. Then press Connect, and makes a first select for customer table:
Make a request: http://localhost:8080/save
Now again select customer table:
Make a request: http://localhost:8080/findall,
Make a request http://localhost:8080/findbyid?id=5,
Now, do restart the SpringBoot App, then select customer table again:
Why?
-> Because we use Disk-memory database by setting: spring.datasource.url=jdbc:h2:file:~/h2/testdb.
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
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
i could not see the table i;e entity in my embedded datbase? Plz help me.
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!
Thank you for this. Helped me quite a bit!