Back to Spring Boot Hub

Nearby lessons

9 of 10
Spring Boot - JDBCSpring Boot - Connection PoolingSpring Boot - In-Memory DatabasesSpring Boot - MCQ Quiz

Spring Boot - In-Memory Databases (H2)

📌 What You Will Learn
  • Understand Spring Boot - In-Memory Databases (H2)
  • See working code examples
  • Learn from common mistakes and Q&A

Learn Spring Boot - In-Memory Databases (H2) step by step — simple explanations, complete programs with their output, common beginner mistakes, and exam-style MCQs.

run(String... args) method

package com.durgasoft;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication

@ComponentScan

public class StudentappApplication implements CommandLineRunner{

@Autowired

private DataSource dataSource;

public static void main(String[] args) {

SpringApplication.run(StudentappApplication.class, args);

}

@Override

public void run(String... args) throws Exception {

System.out.println("Connection Pooling : "+dataSource);

  • Apply a particular Connection pooling Mechanism:

a)case-1: only Hikary Connection Pooling, No Tomcat JDBC and No Apache DBCP2

In pom.xml

-------------

No changes , normal pom.xml.

Bydefault, Spring boot will search for Hikary Connection pooling mechanism.

If we run the application then we will get the following output.

---Spring Boot logo----

----

Connection Pooling : HikariDataSource (HikariPool-1)

run(String... args) method

Apache DBCP2 Connection pooling

In pom.xml: Exclude Hikary Connection Pooling in spring-boot-starter-jdbc and provide

tomcat-jdbc dependency.

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

<exclusions>

<exclusion>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

</exclusion>

</exclusions>

</dependency>

<dependency>

<groupId>org.apache.tomcat</groupId>

<artifactId>tomcat-jdbc</artifactId>

<version>9.0.10</version>

</dependency>

------

</dependencies>

If we run the application then we will get the following output.

---Spring Boot logo----

----

Connection Pooling : org.apache.tomcat.jdbc.pool.DataSource@6438a7fe{----}

Example02
JCode Cell
1 
2case-2:Only Tomcat Jdbc Connection pooling, No Hikary Connection Pooling and No
3

run(String... args) method

In pom.xml: Exclude "Hikary" and "Tomcat Jdbc" Connection Pooling mechanisms in

"Spring-Boot-Starter-jdbc". and include Apache DBCP2 dependency.

EX:

---

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

<exclusions>

<exclusion>

<groupId>org.apache.tomcat</groupId>

<artifactId>tomcat-jdbc</artifactId>

</exclusion>

<exclusion>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

</exclusion>

</exclusions>

</dependency>

<dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-dbcp2</artifactId>

<version>2.1</version>

</dependency>

-------

</dependencies>

If we run the application then we will get the following output.

---Spring Boot logo----

----

Connection Pooling : org.apache.commons.dbcp2.BasicDataSource@7c8f9c2e

For all the above cases we have to use the following content in application.properties file.

application.properties

------------------------

server.port=1234

spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe

spring.datasource.username=system

spring.datasource.password=durga

If we want to use any other Connection Pooling Mechanisms like C3PO or proxool in Spring

boot applications then we have to use the following steps.

  • Provide Connection pooling mechanisms dependency in pom.xml file.
  • Provide "spring.datasource.type" property in application.properties file.

EX: C3P0 Connection Pooling Mechnaims:

----------------------------------------

  • Prepare c3p0.properties file under "src/main/resources"

c3p0.properties

----------------

c3p0.initialPoolSize = 10

c3p0.maxPoolSize = 10

c3p0.maxStatements = 20

c3p0.maxIdleTime = 20000

  • Provide "spring.datasource.type" property in application.properties file:

application.properties

-----------------------

spring.datasource.type = com.mchange.v2.c3p0.ComboPooledDataSource

----

----

  • Add c3p0 dependency in pom.xml file and exclude Hikary in pom.xml:

pom.xml

------

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-jdbc</artifactId>

<exclusions>

<exclusion>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

</exclusion>

</exclusions>

<dependency>

<groupId>c3p0</groupId>

<artifactId>c3p0</artifactId>

<version>0.9.1.2</version>

</dependency>

-----

</dependecies>

EX:

----

c3p0.properties

-----------------

c3p0.initialPoolSize = 10

c3p0.maxPoolSize = 10

c3p0.maxStatements = 20

c3p0.maxIdleTime = 20000

------------------------

server.port=1234

spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.datasource.url==jdbc:oracle:thin:@localhost:1521:xe

spring.datasource.username=system

spring.datasource.password=durga

spring.datasource.type = com.mchange.v2.c3p0.ComboPooledDataSource

CustomConnectionPoolingApplication.java

----------------------------------------

package com.durgasoft;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class CustomconnectionpoolingApplication implements CommandLineRunner {

@Autowired

private DataSource dataSource;

public static void main(String[] args) {

SpringApplication.run(CustomconnectionpoolingApplication.class, args);

}

@Override

public void run(String... args) throws Exception {

System.out.println("Connection Pooling Data Source : "+dataSource);

}

}

Example03
JCode Cell
1 
2Case-3: Only Apache DBCP2, No Hikary Connection Pooling and No Tomcat-JdbcConnection pooling.
3

run(String... args) method

sd/maven-4.0.0.xsd">

Example04
JCode Cell
1 
2<?xml version="1.0" encoding="UTF-8"?>
3<project xmlns="http://maven.apache.org/POM/4.0.0"
4xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/x
6

run(String... args) method

on>

Example05
JCode Cell
1 
2<modelVersion>4.0.0</modelVersion>
3<parent>
4<groupId>org.springframework.boot</groupId>
5<artifactId>spring-boot-starter-parent</artifactId>
6<version>2.1.9.RELEASE</version>
7<relativePath /> <!-- lookup parent from repository -->
8</parent>
9<groupId>com.durgasoft</groupId>
10<artifactId>customconnectionpooling</artifactId>
11<version>0.0.1-SNAPSHOT</version>
12<name>customconnectionpooling</name>
13<description>Spring boot application on Customized Connection Pooling</descripti
14

run(String... args) method

Example06
JCode Cell
1 
2<properties>
3<java.version>1.8</java.version>
4</properties>
5

run(String... args) method

Example07
JCode Cell
1 
2<dependencies>
3<dependency>
4<groupId>org.springframework.boot</groupId>
5<artifactId>spring-boot-starter-jdbc</artifactId>
6<exclusions>
7

run(String... args) method

Example08
JCode Cell
1 
2<!-- <exclusion>
3<groupId>org.apache.tomcat</groupId>
4<artifactId>tomcat-jdbc</artifactId>
5</exclusion> -->
6<exclusion>
7<groupId>com.zaxxer</groupId>
8<artifactId>HikariCP</artifactId>
9</exclusion>
10</exclusions>
11</dependency>
12<dependency>
13<groupId>com.oracle</groupId>
14<artifactId>ojdbc6</artifactId>
15<version>11.2.0</version>
16</dependency>
17

run(String... args) method

Example09
JCode Cell
1 
2<dependency>
3<groupId>c3p0</groupId>
4<artifactId>c3p0</artifactId>
5<version>0.9.1.2</version>
6</dependency>
7<dependency>
8<groupId>org.springframework.boot</groupId>
9<artifactId>spring-boot-starter-web</artifactId>
10</dependency>
11

run(String... args) method

Example10
JCode Cell
1 
2<dependency>
3<groupId>org.springframework.boot</groupId>
4<artifactId>spring-boot-starter-test</artifactId>
5<scope>test</scope>
6</dependency>
7</dependencies>
8

run(String... args) method

Example11
JCode Cell
1 
2<build>
3<plugins>
4<plugin>
5<groupId>org.springframework.boot</groupId>
6<artifactId>spring-boot-maven-plugin</artifactId>
7</plugin>
8</plugins>
9</build>
10

run(String... args) method

Example12
JCode Cell
1 
2</project>
3

In Memory Databases in Spring Boot

------------------------------------

IN enterprise applications, if we want to use traditional Databases then we have to provide

lot of configurations like

  • Install Database
  • Providing Database setups
  • Create Schema
  • Create table
  • Populating Data.
  • Creating dataSource to connect with database

-----

-----

To overcome the above problems, we will use In Memory databases.

In Memory Databases are providing the following Advantages.

Zero project setup or infrastructure

Zero Configuration

Zero Maintainance

Easy to use for Learning, POCs and Unit Tests

Spring Boot provides Simple Configuration to switch between a real database and an in

memory database like H2

In general, In Memory databases are started when application is started and destroyed

when application is stopped.

EX: H2

H2 is a relational database management system written in Java. It can be embedded in Java

applications or run in the client-server mode.

If we want to use H2 Im Memory Database for our applications then we have to use the

following steps.

  • Prepare Spring boot starter project with "web, JDBC" "h2" Dependencies in STS.
  • Provide the following properties in application.properties file:

spring.h2.console.enabled=true

spring.datasource.url=jdbc:h2:mem:testdb

spring.datasource.driverClassName=org.h2.Driver

spring.datasource.username=sa

spring.datasource.password=

  • Prepare SQL script files under "src/main/resources".

schema.sql

-------------

drop table if exists emp1;

create table emp1(ENO number(5) primary key, ENAME varchar2(10), ESAL float(5),

EADDR varchar2(10));

data.sql

--------

insert into emp1 values(111, 'AAA', 5000, 'Hyd');

insert into emp1 values(222, 'BBB', 6000, 'Hyd');

insert into emp1 values(333, 'CCC', 7000, 'Hyd');

insert into emp1 values(444, 'DDD', 8000, 'Hyd');

insert into emp1 values(555, 'EEE', 9000, 'Hyd');

Spring boot application will load and execute these sql scripting files.

EX:

----

schema.sql

-----------

drop table if exists emp1;

create table emp1(ENO number(5) primary key, ENAME varchar2(10), ESAL float(5), EADDR

varchar2(10));

data.sql

----------

insert into emp1 values(111, 'AAA', 5000, 'Hyd');

insert into emp1 values(222, 'BBB', 6000, 'Hyd');

insert into emp1 values(333, 'CCC', 7000, 'Hyd');

insert into emp1 values(444, 'DDD', 8000, 'Hyd');

insert into emp1 values(555, 'EEE', 9000, 'Hyd');

application.properties

-----------------------

server.port=1234

spring.mvc.view.prefix=/WEB-INF/

spring.mvc.view.suffix=.jsp

spring.h2.console.enabled=true

spring.datasource.url=jdbc:h2:mem:testdb

spring.datasource.driverClassName=org.h2.Driver

spring.datasource.username=sa

spring.datasource.password=

H2DatabaseApplication.java

---------------------------

package com.durgasoft;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class H2databaseApplication //implements CommandLineRunner

{

public static void main(String[] args) {

SpringApplication.run(H2databaseApplication.class, args);

}

}

EmployeeController.java

------------------------

package com.durgasoft.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.ui.ModelMap;

import org.springframework.web.bind.annotation.RequestMapping;

import com.durgasoft.dao.EmployeeDao;

import com.durgasoft.dto.Employee;

@Controller

public class EmployeeController {

@Autowired

private EmployeeDao employeeDao;

@RequestMapping("getallemployees")

public String getAllEmployees(ModelMap map) {

List<Employee> allEmployees = employeeDao.getAllEmployees();

map.addAttribute("allEmployees", allEmployees);

return "employeesdetails";

}

}

EmployeeDao.java

-----------------

package com.durgasoft.dao;

import java.util.List;

import com.durgasoft.dto.Employee;

public interface EmployeeDao {

public List<Employee> getAllEmployees();

}

Employee.java:

-------------------

package com.durgasoft.dto;

In Memory Databases in Spring Boot

Example14
JCode Cell
1 
2public class Employee {
3private int eno;
4private String ename;
5private float esal;
6private String eaddr;
7

In Memory Databases in Spring Boot

EmployeeDaoImpl.java

---------------------

package com.durgasoft.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

import com.durgasoft.dto.Employee;

@Repository

public class EmployeeDaoImpl implements EmployeeDao {

@Autowired

private JdbcTemplate jdbcTemplate;

@Override

public List<Employee> getAllEmployees() {

List<Employee> list = jdbcTemplate.query("select * from emp1", (rs, rowNum)

-> {

Employee emp = new Employee();

emp.setEno(rs.getInt("ENO"));

emp.setEname(rs.getString("ENAME"));

emp.setEsal(rs.getFloat("ESAL"));

emp.setEaddr(rs.getString("EADDR"));

return emp;

});

return list;

}

}

employeedetails.jsp:

--------------------------

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>Insert title here</title>

</head>

<body>

<h2 style="color: red;" align="center">Employees Details</h2>

<table align="center" border="1">

<tr><th>ENO</th><th>ENAME</th><th>ESAL</th><th>EADDR</th></tr>

<c:forEach var="emp" items="${allEmployees}">

<tr>

<td>${emp.eno}</td>

<td>${emp.ename}</td>

<td>${emp.esal}</td>

<td>${emp.eaddr}</td>

</tr>

</c:forEach>

</table>

</body>

</html>

pom.xml

-----------

Example15
JCode Cell
1 
2public int getEno() {
3return eno;
4}
5public void setEno(int eno) {
6this.eno = eno;
7}
8public String getEname() {
9return ename;
10}
11public void setEname(String ename) {
12this.ename = ename;
13}
14public float getEsal() {
15return esal;
16}
17public void setEsal(float esal) {
18this.esal = esal;
19}
20public String getEaddr() {
21return eaddr;
22}
23public void setEaddr(String eaddr) {
24this.eaddr = eaddr;
25}
26}
27

In Memory Databases in Spring Boot

sd/maven-4.0.0.xsd">

Example16
JCode Cell
1 
2<?xml version="1.0" encoding="UTF-8"?>
3<project xmlns="http://maven.apache.org/POM/4.0.0"
4xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
5xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/x
6

In Memory Databases in Spring Boot

Example17
JCode Cell
1 
2<modelVersion>4.0.0</modelVersion>
3<parent>
4<groupId>org.springframework.boot</groupId>
5<artifactId>spring-boot-starter-parent</artifactId>
6<version>2.1.9.RELEASE</version>
7<relativePath /> <!-- lookup parent from repository -->
8</parent>
9<groupId>com.durgasoft</groupId>
10<artifactId>h2database</artifactId>
11<version>0.0.1-SNAPSHOT</version>
12<name>h2database</name>
13<description>Spring boot application on H2 In Memory Database</description>
14

In Memory Databases in Spring Boot

Example18
JCode Cell
1 
2<properties>
3<java.version>1.8</java.version>
4</properties>
5

In Memory Databases in Spring Boot

Example19
JCode Cell
1 
2<dependencies>
3<dependency>
4<groupId>org.springframework.boot</groupId>
5<artifactId>spring-boot-starter-web</artifactId>
6</dependency>
7

In Memory Databases in Spring Boot

Example20
JCode Cell
1 
2<dependency>
3<groupId>org.apache.tomcat</groupId>
4<artifactId>tomcat-jasper</artifactId>
5<version>9.0.26</version>
6</dependency>
7

In Memory Databases in Spring Boot

Example21
JCode Cell
1 
2<dependency>
3<groupId>javax.servlet</groupId>
4<artifactId>jstl</artifactId>
5<version>1.2</version>
6</dependency>
7

In Memory Databases in Spring Boot

Example22
JCode Cell
1 
2<dependency>
3<groupId>org.springframework.boot</groupId>
4<artifactId>spring-boot-starter-jdbc</artifactId>
5</dependency>
6

In Memory Databases in Spring Boot

Example23
JCode Cell
1 
2<dependency>
3<groupId>com.h2database</groupId>
4<artifactId>h2</artifactId>
5<scope>runtime</scope>
6</dependency>
7<dependency>
8<groupId>org.springframework.boot</groupId>
9<artifactId>spring-boot-starter-test</artifactId>
10<scope>test</scope>
11</dependency>
12</dependencies>
13

In Memory Databases in Spring Boot

Example24
JCode Cell
1 
2<build>
3<plugins>
4<plugin>
5<groupId>org.springframework.boot</groupId>
6<artifactId>spring-boot-maven-plugin</artifactId>
7</plugin>
8</plugins>
9</build>
10

In Memory Databases in Spring Boot

To get H2 In memory Database use the following End Point.

http://localhost:1234/h2-console and click on "Connect" button.

To get all Employees list use the following End Point.

http://localhost:1234/getallemployees

Example25
JCode Cell
1 
2</project>
3
📝 Key Takeaways
  • Key ideas of Spring Boot - In-Memory Databases (H2) explained simply
  • Ready-to-use code examples
  • Exam-style questions at the end