SpringBoot H2configuration

What is an in-memory database

In-memory databases rely on system memory rather than disk data storage space. Since memory access is faster than disk access. When we do not need to persist data, we use in-memory databases. In-memory databases are embedded databases. By default, in-memory databases are volatile, and all stored data will be lost when we restart the application.

Widely used in-memory databases are H2, HSQLDB (HyperSQL database) ,and Apache Derby. It will automatically create the configuration.

Persistent and in-memory databases

Persistent databases store data persistently in physical memory. Even if the database server fails, the data will still be available. Some popular persistent databases include Oracle, MySQL, Postgres,et al.

Regarding in-memory database, and the data is stored in system memoryData is lost when the program is closed. It is suitable for in. POC H2.

(Proof of Concept) is very helpful, but not useful for production applications. Widely used in-memory databases are2configuration

H2 What is H isand embedded, open sourcein-memory database. It is a relational database management system written in Java. This is a/clientserver application. It is usually used forUnit test

. It stores data in memory instead of persistently storing data on disk.

Advantages Zero configuration Easy to use. Lightweight, fast. It provides simple configuration that can switch between real databases and in-memory databases. It supports standard SQL and JDBC API.

It provides a web console that can be maintained in the database.2configuration

database2If you want to use H

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

After adding the dependencies, we need to configure H2database data source URL, driver class name, usernameand Password. Spring Boot provides a simple way to configure application.properties These properties in the file.

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

In spring.datasource.url properties, mem is the name of the in-memory database, while testdb is the name of the in-memory database. By default, H2The provided schema. We can also define our own schema and database. The default username is sa , an empty password means Emptypassword. If you want to change the username and password, you can overwrite these values.

Keep the data in the2database

If you want to keep the data in the2In the database, we should store the data in a file. To do this, we need to change the URL attribute of the data source.

#Save data
spring.datasource.url=jdbc:h2:file:/data/sampledata
spring.datasource.url=jdbc:h2:C:/data/sampledata

In the above properties, sampledata is a filename.

Create Schema structure and fill in the data

We can define through the resource Create in the folder (src) SQL File creation schema/main/resource).

schema.sql

DROP TABLE if EXISTS CITY;
CREATE TABLE CITY (
City_code int AUTO_INCREMENT PRIMARY KEY,
city_name VARCHAR(50) NOT null,
city_pincode INT(8) NOT null,
);

We can do this by creating a resource folder (src/main/resource) to create a SQL a file to fill the data in the table.

data.sql

INSERT INTO CITY VALUES ('Delhi',　110001);
INSERT INTO CITY VALUES ('Kanpur',　208001);
INSERT INTO CITY VALUES ('Lucknow',　226001);

Spring Boot automatically picks up data.sql File and target H2database runs it.

H2console is disabled

By default, H2database console view. When accessing H2database, we must enable it using the following properties.

#Enable H2
consolespring.h2.console.enabled=true

Before enabling H2Now we can access the console by calling the URL http://localhost:8080/h2-console in the browser.2Console. The following figure shows how to access H2database console.

In the above screenshot, we defined a console view named w3codebox database.

Spring Boot H2Example

Let's set up a Spring Boot.

Step1: Open Spring Initializr http://start.spring.io .

Step2: Select Spring Boot version 2.3.0.M1.

Step2: Provide GroupName. We provide com.w3codebox.

Step3: Provide Artifact ID. We provide spring-boot-h2-database-example.

Step5: Add dependencies Spring Web, Spring Data JPA ,and H2database.

Step6: click Generate (Generate) button. When we click the 'Generate' button, it will package the project in Jar the file and download it to the local system.

Step7: Extract jar file and paste it into the STS workspace.

The8Step: Importproject folder to STS.

File-> Import-> Existing Maven project-> Browse-> Select folder spring-boot-h2-database-example-> Completed

Importing takes some time.

Step9: src/main/the name in the java folder com.w3codebox.model .

Step10: package com.w3in the codebox.model. We created a class named The Student class. In the 'Book' class, we performed the following operations:

Define four variables id, age, nameand Generate Getter and Setters.
Right-click on the file-> Source-> Generate Getters and Setters. Use annotation @Entity，marks the class as Entity . Use annotation @Tablemarks this class as Table name. by using annotations @Column defines each variable as Column .

Student.java

package com.w3codebox.model;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
//marks the class as an entity
@Entity
//defines the class name as the table name
@Table
public class Student　
{
　　　　//marks id as the primary key
　　　　@Id
　　　　//defines id as the column name
　　　　@Column
　　　　private int id;
　　　　//defines name as the column name
　　　　@Column
　　　　private String name;
　　　　//defines age as the column name
　　　　@Column
　　　　private int age;
　　　　//defines email as the column name
　　　　@Column
　　　　private String email;
　　　　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 int getAge()　
　　　　{
　　　　return age;
　　　　}
　　　　public void setAge(int age)　
　　　　{
　　　　this.age = age;
　　　　}
　　　　public String getEmail()　
　　　　{
　　　　return email;
　　　　}
　　　　public void setEmail(String email)　
　　　　{
　　　　this.email = email;
　　　　}
}

Step11: in the folder src/main/Create a named com.w3codebox.controller package. 　

package com.w3codebox.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import com.w3codebox.model.Student;
import com.w3codebox.service.StudentService;
//Creating RestController
@RestController
public class StudentController　
{
　　　　//Automatically assemble the StudentService class
　　　　@Autowired
　　　　StudentService studentService;
　　　　//Create a GET mapping to retrieve all student details from the database
　　　　@GetMapping("/student)
　　　　private List<Student> getAllStudent()　
　　　　{
　　　　　　　　return studentService.getAllStudent();
　　　　}
　　　　//Create a GET mapping to retrieve detailed information of a specific student
　　　　@GetMapping("/student/{id}")
　　　　private Student getStudent(@PathVariable("id") int id)　
　　　　{
　　　　　　　　return studentService.getStudentById(id);
　　　　}
　　　　//Create a delete mapping to delete a specific student
　　　　@DeleteMapping("/student/{id}")
　　　　private void deleteStudent(@PathVariable("id") int id)　
　　　　{
　　　　　　　　studentService.delete(id);
　　　　}
　　　　//Create a POST mapping to publish student details in the database
　　　　@PostMapping("/student)
　　　　private int saveStudent(@RequestBody Student student)　
　　　　{
　　　　studentService.saveOrUpdate(student);
　　　　return student.getId();
　　　　}
}

package com.w3codebox.service;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.w3codebox.model.Student;
import com.w3codebox.repository.StudentRepository;
@Service
public class StudentService　
{
　　　　@Autowired
　　　　StudentRepository studentRepository;
　　　　//Retrieve all student records
　　　　public List<Student> getAllStudent()　
　　　　{
　　　　List<Student> students = new ArrayList<Student>();
　　　　studentRepository.findAll().forEach(student　-> students.add(student));
　　　　return students;
　　　　}
　　　　//Retrieve specific records
　　　　public Student getStudentById(int id)　
　　　　{
　　　　return studentRepository.findById(id).get();
　　　　}
　　　　public void saveOrUpdate(Student student)　
　　　　{
　　　　studentRepository.save(student);
　　　　}
　　　　//Delete a specific record
　　　　public void delete(int id)　
　　　　{
　　　　studentRepository.deleteById(id);
　　　　}
}

package com.w3codebox.repository;
import org.springframework.data.repository.CrudRepository;
import com.w3codebox.model.Student;
public interface StudentRepository extends CrudRepository<Student, Integer>
{
}

spring.datasource.url=jdbc:h2:mem:w3codebox
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
#Enable H2
consolespring.h2.console.enabled=true

package com.w3codebox;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootH2DatabaseExampleApplication　
{
　　　　public static void main(String[] args)　
　　　　{
　　　　　　　　SpringApplication.run(SpringBootH2DatabaseExampleApplication.class, args);
　　　　}
}

{
　　　　"id": "00"1",
　　　　"age": ""23",
　　　　"name": "Amit",
　　　　"email": "[email protected]"
}

{"id": "002","age": ""24","name": "Vadik","email": "[email protected]"
}　
{
　　　　"id": "00"3",
　　　　"age": ""21",
　　　　"name": "Prateek",
　　　　"email": "[email protected]"
}　
{
　　　　"id": "00"4",
　　　　"age": ""25",
　　　　"name": "Harsh"
　　　　"email": "[email protected]"
}　
{
　　　　"id": "00"5",
　　　　"age": ""24",
　　　　"name": "Swarit",
　　　　"email": "[email protected]"
}