Fully working prototypes with Spring Boot and H2

We do use a lot of h2 with spring especially for unit tests. However instead of unit tests we might want to have a fully functional prototype with data to display.
H2 is the perfect candidate for that, it works great with spring, it has good syntax compatibility with most databases out there and it also provides you with a ui to check your data.

Imagine the scenario of an interview assignment. You want your example to work out of the box with as minimum as possible configuration for the reviewer.
The plan is to have an application up and running with some data.
Before accessing the application we might as well want to add some data to it. Then we need to have a proper way to display the data added without adding extra code.

The first step is to go to the spring initializr and add the Web and H2 dependencies. Also we shall add the jdbc property.

The end result will give a build.gradle file like this.

buildscript {
	ext {
		springBootVersion = '2.0.6.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group = 'com.gkatzioura.springbooth2'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8

repositories {
	mavenCentral()
}

dependencies {
	implementation('org.springframework.boot:spring-boot-starter-jdbc')
	implementation('org.springframework.boot:spring-boot-starter-web')
	runtimeOnly('com.h2database:h2')
	testImplementation('org.springframework.boot:spring-boot-starter-test')
}

Since we added the jdbc property we can have some schema scripts executed once the application is started.
Thus we need to create a schema.sql file containing the sql statements which create the schema.

CREATE TABLE application_user (ID INT, USER_NAME VARCHAR(50), PASSWORD VARCHAR(255));
INSERT INTO application_user (ID,USER_NAME, PASSWORD) values (1,'test','password-hash');

The next step is to enable the h2 console. We will go with the yaml approach however you can do it either using a properties file or environmental variables.

spring:
  h2:
    console:
      enabled: true

Now once we get our spring application running we can navigate at the http://localhost:8080/h2-console endpoint.
We shall be presented with the default credentials needed

Once we have logged in, we can query for the user we had inserted on our startup sql script.

Screen Shot 2018-11-05 at 07.36.20.png

That’s it! This can make wonders for prototypes, interview assignments and blog posts like this!

Spring Boot and Database initialization

Spring boot is hands down a great framework, saving the developer a lot of time and energy when developing a spring application.

One of its great features is database initialization.
You can use spring boot in order to initialize your sql database.

We will start with the gradle file

group 'com.gkatzioura'
version '1.0-SNAPSHOT'

apply plugin: 'java'

sourceCompatibility = 1.5

buildscript {
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:1.3.3.RELEASE")
    }
}

apply plugin: 'idea'
apply plugin: 'java'
apply plugin: 'spring-boot'

repositories {
    mavenCentral()
} 

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web") {
        exclude module: "spring-boot-starter-tomcat"
    }
    compile("org.springframework.boot:spring-boot-starter-jetty")
    compile("org.springframework:spring-jdbc")
    compile("org.springframework.boot:spring-boot-starter-actuator")
    compile("com.h2database:h2:1.4.191")
    testCompile group: 'junit', name: 'junit', version: '4.11'
}

Pay special attention to the org.springframework:spring-jdbc dependency. Actually this is the dependency that assists with the database initialization.
H2 database is more than enough for this example.

The applications main class

package com.gkatzioura.bootdatabaseinitialization;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

/**
 * Created by gkatzioura on 29/4/2016.
 */
@SpringBootApplication
public class Application {

    public static void main(String[] args) {

        SpringApplication springApplication = new SpringApplication();
        ApplicationContext applicationContext = springApplication.run(Application.class,args);
    }

}

The next step is to specify the datasource

package com.gkatzioura.bootdatabaseinitialization.config;

import org.h2.jdbcx.JdbcDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * Created by gkatzioura on 29/4/2016.
 */
@Configuration
public class DataSourceConfig {

    private static final String TEMP_DIRECTORY = System.getProperty("java.io.tmpdir");

    @Bean(name = "mainDataSource")
    public DataSource createMainDataSource() {

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:"+TEMP_DIRECTORY+"/testdata;MODE=MySQL");
        return ds;
    }

}

We will add a schema.sql file to the resource folder so it would be loaded to classpath. The schema.sql file would contain all the table definitions needed for our database.

CREATE TABLE IF NOT EXISTS `Users` (
    `user_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(200) NOT NULL,
    PRIMARY KEY (`user_id`)
);

Next file to add is data.sql on the resources folder. This file will contain the sql statements needed to populate our database.

INSERT INTO `Users` (`user_id`,`name`) VALUES (null,'nick');
INSERT INTO `Users` (`user_id`,`name`) VALUES (null,'george');

On initialization spring boot will search for the data.sql and schema.sql files and execute them with the Database initializer.

So far so good, however when you have two datasources defined, things get complicated.
We shall add a secondary datasource

package com.gkatzioura.bootdatabaseinitialization.config;

import org.h2.jdbcx.JdbcDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * Created by gkatzioura on 29/4/2016.
 */
@Configuration
public class DataSourceConfig {

    private static final String TEMP_DIRECTORY = System.getProperty("java.io.tmpdir");

    @Bean(name = "mainDataSource")
    public DataSource createMainDataSource() {

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:"+TEMP_DIRECTORY+"/testdata;MODE=MySQL");
        return ds;
    }

    @Bean(name = "secondaryDataSource")
    public DataSource createSecondaryDataSource() {

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:"+TEMP_DIRECTORY+"/secondarydata;MODE=MySQL");
        return ds;
    }
}

By starting the application we get an error

Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.sql.DataSource] is defined: expected single matching bean but found 2: mainDataSource,secondaryDataSource

The problem is that the datasource initializer gets injected with a datasource. So we have to specify the datasource inject or else we will get an exception.
A workaround is to specify which datasource bean is the primary one.

    @Bean(name = "mainDataSource")
    @Primary
    public DataSource createMainDataSource() {

        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL("jdbc:h2:"+TEMP_DIRECTORY+"/testdata;MODE=MySQL");
        return ds;
    }

By doing so the initializer will run the schema.sql and data.sql scripts using the mainDataSource bean.

Another great feature of spring boot database is initialization is that it can be integrated with flyway. Get more information on flyway here.

You can find the project source code here

Use stored procedures with Hibernate

This article shows different ways to call stored procedures from hibernate.
JDBC actions will not be covered.

Postgresql will be used as a database.

CREATE DATABASE example;

CREATE TABLE company (
  company_id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE benefit (
  benefit_id BIGSERIAL PRIMARY KEY,
  name TEXT,
  company_id BIGINT,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
  REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee (
  employee_id BIGSERIAL PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  company_id BIGINT,
  CONSTRAINT fk_company FOREIGN KEY (company_id)
    REFERENCES company (company_id) MATCH SIMPLE
);

CREATE TABLE employee_benefit (
  employee_id BIGINT,
  benefit_id BIGINT,
  CONSTRAINT fk_employee FOREIGN KEY (employee_id)
  REFERENCES employee (employee_id) MATCH SIMPLE ,
  CONSTRAINT fk_benefit FOREIGN KEY (benefit_id)
  REFERENCES benefit (benefit_id) MATCH SIMPLE
);

INSERT INTO company (name) VALUES ('TestCompany');
INSERT INTO employee (first_name, last_name, company_id) VALUES ('Emmanouil','Gkatziouras',1);
INSERT INTO benefit (name,company_id) VALUES ('gym',1);
INSERT INTO benefit (name,company_id) VALUES ('lunch',1);

Our postgresql function will return a set of employee benefits

CREATE OR REPLACE FUNCTION add_all_company_benefits(employeeId BIGINT,companyid BIGINT)
  RETURNS TABLE(benefit_id BIGINT,name TEXT,company_id BIGINT) AS $$
  DECLARE benefitid BIGINT;
  BEGIN

    FOR benefitid IN (SELECT benefit.benefit_id FROM benefit WHERE benefit.company_id=companyid) LOOP
      IF (SELECT COUNT(*) FROM employee_benefit as eb
            WHERE eb.employee_id=employeeid
            AND eb.benefit_id=benefitid) = 0
      THEN
        INSERT INTO employee_benefit (employee_id, benefit_id)
        VALUES (employeeId,benefitId);
      END IF;
    END LOOP;

    RETURN QUERY
    SELECT benefit.benefit_id,benefit.name,benefit.company_id FROM benefit
      INNER JOIN employee_benefit ON employee_benefit.benefit_id = benefit.benefit_id
      WHERE employee_benefit.employee_id=employeeId;
  END;
$$ LANGUAGE plpgsql;

The entity mappings using JPA annotations follow

The company entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "company")
public class Company {

    @Id
    @GeneratedValue
    @Column(name = "company_id")
    private Long Id;

    @Column
    String name;

    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.LAZY,mappedBy = "company")
    private Set<Benefit> benefits = new HashSet<Benefit>();

    public Long getId() {
        return Id;
    }

    public void setId(Long id) {
        Id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<Benefit> getBenefits() {
        return benefits;
    }

    public void setBenefits(Set<Benefit> benefits) {
        this.benefits = benefits;
    }
}

The employee entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue
    @Column(name = "employee_id")
    private Long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @ManyToOne(cascade = CascadeType.ALL,fetch = FetchType.LAZY)
    @JoinColumn(name = "company_id",referencedColumnName = "company_id")
    private Company company;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
}

The benefit entity.

package com.gkatzioura.example.entity;

import javax.persistence.*;

@Entity
@Table(name = "benefit")
public class Benefit {

    @Id
    @GeneratedValue
    @Column(name = "benefit_id")
    private Long id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "company_id")
    private Company company;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Company getCompany() {
        return company;
    }

    public void setCompany(Company company) {
        this.company = company;
    }
}

Our first approach would be by calling the procedure as a hibernate sql query.

        Session session = sessionFactory.openSession();

        List<Company> companies = session.createCriteria(Company.class)
                .add(Restrictions.eq("name", companyName))
                .list();

        List<Employee> employees = session.createCriteria(Employee.class)
                .add(Restrictions.eq("firstName",employeeName))
                .list();

        for(Company company:companies) {

            for(Employee employee:employees) {

                LOGGER.info("The company is "+company.getId()+" the employee is "+employee.getId());

                SQLQuery query = (SQLQuery) session.createSQLQuery("SELECT*FROM add_all_company_benefits(:employeeId,:companyId)")
                        .addEntity(Benefit.class)
                        .setParameter("employeeId", employee.getId())
                        .setParameter("companyId", company.getId());


                List result = query.list();

                for(Integer i=0;i<result.size();i++) {

                    LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
                }
            }
        }

        session.close();

We will update the company entity by adding a NameQuery in the company entity.

@Entity
@Table(name = "company")
@NamedNativeQueries({
        @NamedNativeQuery(
                name = "AddAllCompanyBenefits",
                query = "SELECT*FROM add_all_company_benefits(:employeeId,:companyId)",
                resultClass = Benefit.class
        )
})
public class Company {
}

Our hibernate actions will be refactored to

 
Query query = session.getNamedQuery("AddAllCompanyBenefits")
    .setParameter("employeeId", employee.getId())
    .setParameter("companyId", company.getId());

List result = query.list();

for(Integer i=0;i<result.size();i++) {
    LOGGER.info("The benefit is "+((Benefit) result.get(i)).getName());
}
 

Another way is to use hibernate’s ProcedureCall which is used on hibernate’s implementation of JPA’s StoredProcedureQuery


ProcedureCall procedureCall = session.createStoredProcedureCall("add_all_company_benefits");
procedureCall.registerParameter("EMPLOYEE", Long.class, ParameterMode.IN);
procedureCall.registerParameter("COMPANY", Long.class, ParameterMode.IN);
procedureCall.getParameterRegistration("EMPLOYEE").bindValue(employee.getId());
procedureCall.getParameterRegistration("COMPANY").bindValue(company.getId());                

ProcedureOutputs procedureOutputs = procedureCall.getOutputs();
ResultSetOutput resultSetOutput = (ResultSetOutput) procedureOutputs.getCurrent();


List results = resultSetOutput.getResultList();

for(Integer i=0;i<results.size();i++) {

    Object[] objects = (Object[]) results.get(i);

    LOGGER.info("The benefit is "+objects[1]);
}

JDBC basic actions.

This is a basic set of database actions when using jdbc.

Issuing queries with prepared statements

/**
 * Will fetch only one result
 */
public String justQuery() throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT a FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            String a = null;
            if(resultSet.next()) {
                a = resultSet.getString("a");
            }
            resultSet.close();
            return a;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
        
    }

Fetching more the one results.

/**
 * Fetching many results
 */
public List<Long> justQuery() throws SQLException {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement("SELECT id FROM abc");
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Long> ids = new ArrayList<>();
            if(resultSet.next()) {
                Long id = resultSet.getLong("id");
                ids.add(id);
            }
            resultSet.close();
            return ids;
        } finally {
            //Close the statements
            if(preparedStatement!=null) preparedStatement.close();
            //Relase the connection resource back to the pool
            if(connection!=null) connection.close();
        }
    }

Calling stored procedures with callable statements

public long callProcedure(String something) throws SQLException {
        
        Connection connection = null;
        CallableStatement callableStatement = null;
        try {
            connection = dataSource.getConnection();
            callableStatement = connection.prepareCall("CALL doSomething(?,?)");
            callableStatement.setString(1, something);
            callableStatement.registerOutParameter(2, Types.NUMERIC);
            callableStatement.execute();
            Long lastInsertId = callableStatement.getLong("resultvar");
            return lastInsertId;
        } finally {
            if(callableStatement!=null) callableStatement.close();
            //Release back to the connection pool
            if(connection!=null) connection.close();
        }   
    }

Batch processing

public void doBatchOpeation(List<Long> userIds) throws SQLException {
        Connection connection = null;
        Statement batchStmt = null;
        try {
            connection = DatasourceProvider.getConnection();
            batchStmt = connection.createStatement();
            for(Long userId:userIds) {
                batchStmt.addBatch("UPDATE user SET active=true WHERE userid="+userId);
            }
            batchStmt.executeBatch();
        } finally {
            if(batchStmt!=null) batchStmt.close();
            if(connection!=null) connection.close();
        }
    }

And last but not least transactions!!!

public void doTransaction(Long userId,BigDecimal ammount) throws SQLException{

        Connection connection = null;
        PreparedStatement insertStatemt = null;
        PreparedStatement updateStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);

            insertStatemt = connection.prepareStatement("INSERT INTO transfers (USERID,AMMOUNT) VALUES (?,?)");
            insertStatemt.setLong(1, userId);
            insertStatemt.setBigDecimal(2, ammount);
            insertStatemt.executeUpdate();

            updateStatement = connection.prepareStatement("UPDATE user SET tnum=tnum+1 WHERE userid=?");
            updateStatement.setLong(1, userId);
            updateStatement.executeUpdate();
            
            //Don't forget to commit
            connection.commit();
            
        } catch (SQLException e) {
            if(connection!=null) {
                connection.rollback();
            } else throw e;
        } finally {
            if(insertStatemt!=null) insertStatemt.close();
            if(updateStatement!=null) updateStatement.close();
            if(connection!=null) {
                //Setting autocommit true before sending connection back to the pool
                connection.setAutoCommit(true);
                //And release the connection back to the pool
                connection.close();
            }
        }
    }

All in all using jdbc has a lot of boilerplate and also you have to be carefull. While many other frameworks like spring provide tools such as a transaction manager or jdbc template, when using jdbc you have to do everything on your own. You are responsible for everything, for example releasing the connections back to the pool or setting the connection back to autocommit mode.