Spring Boot and Spring JDBC With H2

this guide will help you create a simple project with spring boot. you will add code to the project to connect to a database using spring jdbc. you will also learn to write all the crud methods.

project code structure

following screenshot shows the structure of the project we will create. image

a few details:

tools you will need

complete maven project with code examples

our github repository has all the code examples: https://github.com/in28minutes/spring-boot-examples/tree/master/spring-boot-2-jdbc-with-h2

a little bit of theory

jdbc:

update todo

connection connection = datasource.getconnection();

preparedstatement st = connection.preparestatement(
"update todo set user=?, desc=?, target_date=?, is_done=? where id=?");

st.setstring(1, todo.getuser());
st.setstring(2, todo.getdesc());
st.settimestamp(3, new timestamp(
todo.gettargetdate().gettime()));
st.setboolean(4, todo.isdone());
st.setint(5, todo.getid());

st.execute();

st.close();

connection.close();


spring jdbc

creating the project with spring initializr

creating a rest service with spring initializr is a cake walk. we will use spring web mvc as our web framework.

spring initializr is a great tool to bootstrap your spring boot projects.

image

as shown in the image above, the following steps have to be done.

starter projects in pom.xml

below is the list of starter projects in pom.xml.

<dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-starter-jdbc</artifactid>
</dependency>
<dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-starter-web</artifactid>
</dependency>
<dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-devtools</artifactid>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupid>com.h2database</groupid>
    <artifactid>h2</artifactid>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-starter-test</artifactid>
    <scope>test</scope>
</dependency>


initialize the h2 in-memory database with the schema

we will use h2 as the database.

h2 provides a web interface called the h2 console to see the data. let’s enable that console in the application.properties

/src/main/resources/application.properties

# enabling h2 console
spring.h2.console.enabled=true


when you reload the application, you can launch the h2 console at http://localhost:8080/h2-console .

image

tip: make sure that you use jdbc:h2:mem:testdb as the jdbc url.

when you use the right jdbc url given above, you should see an empty schema when you click the connect button. image

create schema and data

we will create a table called student with a few simple columns. we can initialize a schema by creating a schema.sql file in the resources.

/src/main/resources/schema.sql

create table student
(
   id integer not null,
   name varchar(255) not null,
   passport_number varchar(255) not null,
   primary key(id)
);


let’s also populate some data into the student table.

/src/main/resources/data.sql

insert into student
values(10001,'ranga', 'e1234567');

insert into student
values(10002,'ravi', 'a1234568');


when the application reloads, you will see following statements in the log, indicating that the sql files are picked up.

2017-11-29 21:12:20.780  info 20747 --- [  restartedmain] o.s.jdbc.datasource.init.scriptutils     : executing sql script from url [file:/in28minutes/git/spring-boot-examples/spring-boot-2-jdbc-with-h2/target/classes/schema.sql]
2017-11-29 21:12:20.801  info 20747 --- [  restartedmain] o.s.jdbc.datasource.init.scriptutils     : executed sql script from url [file:/in28minutes/git/spring-boot-examples/spring-boot-2-jdbc-with-h2/target/classes/schema.sql] in 20 ms.
2017-11-29 21:12:20.804  info 20747 --- [  restartedmain] o.s.jdbc.datasource.init.scriptutils     : executing sql script from url [file:/in28minutes/git/spring-boot-examples/spring-boot-2-jdbc-with-h2/target/classes/data.sql]
2017-11-29 21:12:20.807  info 20747 --- [  restartedmain] o.s.jdbc.datasource.init.scriptutils     : executed sql script from url [file:/in28minutes/git/spring-boot-examples/spring-boot-2-jdbc-with-h2/target/classes/data.sql] in 2 ms.


when you log into the h2 console ( http://localhost:8080/h2-console ), you can see that the student table is created and the data is populated. image

creating the student bean

let's create a simple student bean with basic student information along with getters, setters, and a tostring method.

package com.in28minutes.springboot.jdbc.h2.example.student;

public class student {
    private long id;
    private string name;
    private string passportnumber;

    public student() {
        super();
    }

    public student(long id, string name, string passportnumber) {
        super();
        this.id = id;
        this.name = name;
        this.passportnumber = passportnumber;
    }

    public student(string name, string passportnumber) {
        super();
        this.name = name;
        this.passportnumber = passportnumber;
    }

    public long getid() {
        return id;
    }

    public void setid(long id) {
        this.id = id;
    }

    public string getname() {
        return name;
    }

    public void setname(string name) {
        this.name = name;
    }

    public string getpassportnumber() {
        return passportnumber;
    }

    public void setpassportnumber(string passportnumber) {
        this.passportnumber = passportnumber;
    }

    @override
    public string tostring() {
        return string.format("student [id=%s, name=%s, passportnumber=%s]", id, name, passportnumber);
    }

}


create a repository method to read student information

we would want to start by creating a simple repository. to talk to the database, we will use a jdbctemplate.

@repository
public class studentjdbcrepository {
    @autowired
    jdbctemplate jdbctemplate;


spring boot auto configuration sees h2 in the classpath. it understands that we want to talk to an in-memory database. it auto configures a datasource and also a jdbctemplate connecting to that datasource.

let’s create the findbyid method to retrieve a student by id in studentjdbcrepository.

public student findbyid(long id) {
    return jdbctemplate.queryforobject("select * from student where id=?", new object[] {
            id
        },
        new beanpropertyrowmapper < student > (student.class));
}


notes:

we would want to execute the findbyid method. to keep things simple, we will make the springboot2jdbcwithh2application class implement commandlinerunner and implement the run method to call the findbyid method on the repository.

/src/main/java/com/in28minutes/springboot/jdbc/h2/example/springboot2jdbcwithh2application.java

@springbootapplication
public class springboot2jdbcwithh2application implements commandlinerunner {

    private logger logger = loggerfactory.getlogger(this.getclass());

    @autowired
    studentjdbcrepository repository;

    public static void main(string[] args) {
        springapplication.run(springboot2jdbcwithh2application.class, args);
    }

    @override
    public void run(string...args) throws exception {

        logger.info("student id 10001 -> {}", repository.findbyid(10001 l));
    }
}


notes:

when the application reloads, you will see this in the log.

student id 10001 -> student [id=10001, name=ranga, passportnumber=e1234567]


congratulations on executing the first spring jdbc method! you can see how easy it is with spring boot.

let’s now add another method to retrieve details of all the students to studentjdbcrepository.

class studentrowmapper implements rowmapper < student > {
    @override
    public student maprow(resultset rs, int rownum) throws sqlexception {
        student student = new student();
        student.setid(rs.getlong("id"));
        student.setname(rs.getstring("name"));
        student.setpassportnumber(rs.getstring("passport_number"));
        return student;
    }

}

public list < student > findall() {
    return jdbctemplate.query("select * from student", new studentrowmapper());
}


notes:

we can add a call to find all methods in the run method of springboot2jdbcwithh2application.java

logger.info("all users 1 -> {}", repository.findall());


you can see the following output in the log when the project reloads.

all users 1 -> [student [id=10001, name=ranga, passportnumber=e1234567], student [id=10002, name=ravi, passportnumber=a1234568]]


implementing spring jdbc delete, insert, and update methods

the code below shows the delete, insert and update methods.

public int deletebyid(long id) {
    return jdbctemplate.update("delete from student where id=?", new object[] {
        id
    });
}

public int insert(student student) {
    return jdbctemplate.update("insert into student (id, name, passport_number) " + "values(?,  ?, ?)",
        new object[] {
            student.getid(), student.getname(), student.getpassportnumber()
        });
}

public int update(student student) {
    return jdbctemplate.update("update student " + " set name = ?, passport_number = ? " + " where id = ?",
        new object[] {
            student.getname(), student.getpassportnumber(), student.getid()
        });
}


notes

we can add a call to all the above methods in the run method of springboot2jdbcwithh2application.java

logger.info("inserting -> {}", repository.insert(new student(10010 l, "john", "a1234657")));

logger.info("update 10001 -> {}", repository.update(new student(10001 l, "name-updated", "new-passport")));

repository.deletebyid(10002 l);

logger.info("all users 2 -> {}", repository.findall());


you can see following output in the log when the project reloads.

inserting -> 1
update 10001 -> 1
all users 2 -> [student [id=10001, name=name-updated, passportnumber=new-passport], student [id=10010, name=john, passportnumber=a1234657]]


awesome ! you’ve implemented all the crud methods using spring jdbc and spring boot.

next steps

image

complete code example

pom.xml

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelversion>4.0.0</modelversion>
    <groupid>com.in28minutes.springboot.rest.example</groupid>
    <artifactid>spring-boot-2-jdbc-with-h2</artifactid>
    <version>0.0.1-snapshot</version>
    <packaging>jar</packaging>
    <name>spring-boot-2-jdbc-with-h2</name>
    <description>spring boot 2, jdbc and h2 - example project</description>
    <parent>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-starter-parent</artifactid>
        <version>2.0.0.m6</version>
        <relativepath />
        <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceencoding>utf-8</project.build.sourceencoding>
        <project.reporting.outputencoding>utf-8</project.reporting.outputencoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-jdbc</artifactid>
        </dependency>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-web</artifactid>
        </dependency>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-devtools</artifactid>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupid>com.h2database</groupid>
            <artifactid>h2</artifactid>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupid>org.springframework.boot</groupid>
            <artifactid>spring-boot-starter-test</artifactid>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupid>org.springframework.boot</groupid>
                <artifactid>spring-boot-maven-plugin</artifactid>
            </plugin>
        </plugins>
    </build>
    <repositories>
        <repository>
            <id>spring-snapshots</id>
            <name>spring snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <name>spring milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>
    <pluginrepositories>
        <pluginrepository>
            <id>spring-snapshots</id>
            <name>spring snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginrepository>
        <pluginrepository>
            <id>spring-milestones</id>
            <name>spring milestones</name>
            <url>https://repo.spring.io/milestone</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginrepository>
    </pluginrepositories>
</project>


/src/main/java/com/in28minutes/springboot/jdbc/h2/example/springboot2jdbcwithh2application.java

package com.in28minutes.springboot.jdbc.h2.example;

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 com.in28minutes.springboot.jdbc.h2.example.student.student;
import com.in28minutes.springboot.jdbc.h2.example.student.studentjdbcrepository;

@springbootapplication
public class springboot2jdbcwithh2application implements commandlinerunner {

    private logger logger = loggerfactory.getlogger(this.getclass());

    @autowired
    studentjdbcrepository repository;

    public static void main(string[] args) {
        springapplication.run(springboot2jdbcwithh2application.class, args);
    }

    @override
    public void run(string...args) throws exception {

        logger.info("student id 10001 -> {}", repository.findbyid(10001 l));

        logger.info("all users 1 -> {}", repository.findall());

        logger.info("inserting -> {}", repository.insert(new student(10010 l, "john", "a1234657")));

        logger.info("update 10001 -> {}", repository.update(new student(10001 l, "name-updated", "new-passport")));

        repository.deletebyid(10002 l);

        logger.info("all users 2 -> {}", repository.findall());

    }
}


/src/main/java/com/in28minutes/springboot/jdbc/h2/example/student/student.java

package com.in28minutes.springboot.jdbc.h2.example.student;

public class student {
    private long id;
    private string name;
    private string passportnumber;

    public student() {
        super();
    }

    public student(long id, string name, string passportnumber) {
        super();
        this.id = id;
        this.name = name;
        this.passportnumber = passportnumber;
    }

    public student(string name, string passportnumber) {
        super();
        this.name = name;
        this.passportnumber = passportnumber;
    }

    public long getid() {
        return id;
    }

    public void setid(long id) {
        this.id = id;
    }

    public string getname() {
        return name;
    }

    public void setname(string name) {
        this.name = name;
    }

    public string getpassportnumber() {
        return passportnumber;
    }

    public void setpassportnumber(string passportnumber) {
        this.passportnumber = passportnumber;
    }

    @override
    public string tostring() {
        return string.format("student [id=%s, name=%s, passportnumber=%s]", id, name, passportnumber);
    }

}


/src/main/java/com/in28minutes/springboot/jdbc/h2/example/student/studentjdbcrepository.java

package com.in28minutes.springboot.jdbc.h2.example.student;

import java.sql.resultset;
import java.sql.sqlexception;
import java.util.list;

import org.springframework.beans.factory.annotation.autowired;
import org.springframework.jdbc.core.beanpropertyrowmapper;
import org.springframework.jdbc.core.jdbctemplate;
import org.springframework.jdbc.core.rowmapper;
import org.springframework.stereotype.repository;

@repository
public class studentjdbcrepository {
    @autowired
    jdbctemplate jdbctemplate;

    class studentrowmapper implements rowmapper < student > {
        @override
        public student maprow(resultset rs, int rownum) throws sqlexception {
            student student = new student();
            student.setid(rs.getlong("id"));
            student.setname(rs.getstring("name"));
            student.setpassportnumber(rs.getstring("passport_number"));
            return student;
        }

    }

    public list < student > findall() {
        return jdbctemplate.query("select * from student", new studentrowmapper());
    }

    public student findbyid(long id) {
        return jdbctemplate.queryforobject("select * from student where id=?", new object[] {
                id
            },
            new beanpropertyrowmapper < student > (student.class));
    }

    public int deletebyid(long id) {
        return jdbctemplate.update("delete from student where id=?", new object[] {
            id
        });
    }

    public int insert(student student) {
        return jdbctemplate.update("insert into student (id, name, passport_number) " + "values(?,  ?, ?)",
            new object[] {
                student.getid(), student.getname(), student.getpassportnumber()
            });
    }

    public int update(student student) {
        return jdbctemplate.update("update student " + " set name = ?, passport_number = ? " + " where id = ?",
            new object[] {
                student.getname(), student.getpassportnumber(), student.getid()
            });
    }

}


/src/main/resources/application.properties

# enabling h2 console
spring.h2.console.enabled=true

#turn statistics on
spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=debug

# show all queries
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=trace


/src/main/resources/data.sql

insert into student
values(10001,'ranga', 'e1234567');

insert into student
values(10002,'ravi', 'a1234568');


/src/main/resources/schema.sql

create table student
(
   id integer not null,
   name varchar(255) not null,
   passport_number varchar(255) not null,
   primary key(id)
);


/src/test/java/com/in28minutes/springboot/jdbc/h2/example/springboot2jdbcwithh2applicationtests.java

package com.in28minutes.springboot.jdbc.h2.example;

import org.junit.test;
import org.junit.runner.runwith;
import org.springframework.boot.test.context.springboottest;
import org.springframework.test.context.junit4.springrunner;

@runwith(springrunner.class)
@springboottest
public class springboot2jdbcwithh2applicationtests {

    @test
    public void contextloads() {}

}


 

 

 

 

Top