spring boot and spring jdbc or jdbcTemplate with spring H2 database basic example

lets see how to create a simple project in spring boot and spring jdbcTemplate and using in memory H2 database provided by spring.

Lets go step by step. I am using intellij for this project you can also use eclipse or any other editor.

My project structure is as follows :

Step 1  : go to https://start.spring.io/

fill the details of the project as follows :



Step 2 : Add following in the pom.xml file

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

<modelVersion>4.0.0</modelVersion>

<parent>

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

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

<version>2.2.5.RELEASE</version>

<relativePath/> <!-- lookup parent from repository -->

</parent>

<groupId>com.cscoach</groupId>

<artifactId>SpringDemo</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>SpringDemo</name>

<description>Demo project for Spring Boot</description>

<properties>

<java.version>1.8</java.version>

</properties>

<dependencies>

<dependency>

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

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

</dependency>

<dependency>

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

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

<scope>test</scope>

<exclusions>

<exclusion>

<groupId>org.junit.vintage</groupId>

<artifactId>junit-vintage-engine</artifactId>

</exclusion>

</exclusions>

</dependency>

<dependency>

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

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<dependency>

<groupId>com.h2database</groupId>

<artifactId>h2</artifactId>

<scope>runtime</scope>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

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

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

</build>

</project>

Step 3 : Create Person class as follows



Person.java

package com.cscoach.SpringDemo.dto;

public class Person {

    private int id;
    private String name;
    private String location;
    private String birth_date;

    //this is required for the default mapping by rowmapper
    public Person(){

    }

    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 String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public String getBirth_date() {
        return birth_date;
    }

    public void setBirth_date(String birth_date) {
        this.birth_date = birth_date;
    }

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", location='" + location + '\'' +
                ", birth_date='" + birth_date + '\'' +
                '}';
    }
}

Step 4 : Create PersonDAO as follows

PersonDAO.java


package com.cscoach.SpringDemo.dao;

import com.cscoach.SpringDemo.dto.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class PersonDAO {

    @Autowired
    JdbcTemplate jdbcTemp;

    public List<Person> findAllPersons(){
      return jdbcTemp.query("select * from persons",new BeanPropertyRowMapper<>(Person.class));
    }

}

BeanPropertyRowMapper maps the Person class property to the columns obtained from the database if and only if properties in Person class is equal to the column in database in this case.

Step 5 : execute the DAO class method when spring boot application starts and application context is available

Make Changes to the SpringDemoApplication.java class as follows

package com.cscoach.SpringDemo;

import com.cscoach.SpringDemo.dao.PersonDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.Calendar;

@SpringBootApplication
public class SpringDemoApplication implements CommandLineRunner {

Logger logger = LoggerFactory.getLogger(this.getClass());

@Autowired
PersonDAO pdao;

public static void main(String[] args) {
SpringApplication.run(SpringDemoApplication.class, args);
}

@Override
public void run(String... args) throws Exception {
      logger.info("Time {} :: ALL PERSONS => {} ", Calendar.getInstance().getTime(), pdao.findAllPersons());
}
}

Step 6 : in application.properties set properties as follows 

spring.h2.console.enabled=true

spring.h2.console.path=/h2

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

spring.datasource.driverClassName=org.h2.Driver

spring.datasource.username=sa

spring.datasource.password=sa

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

now to access the h2 console we have to just start the application and access the h2 console on http://localhost:8080/h2


Step 7 : create data.sql file and add create and insert queries so that when application starts that file is executed by default and tables are created with data.



data.sql

create table persons
(
  id integer primary key,
  name varchar(255) not null,
  location varchar(255),
  birth_date timestamp
);

insert into persons values (100,'raj','mumbai',sysdate),
(101,'jack','new york',sysdate),
(102,'sam','canada',sysdate);

you can run your spring boot app in intellij by pressing on the button like below :



we can see the person list as follows :



you can see person details in h2 console by going on below link :



We can see data as follows :