Sakila- Dagger 2 Dependency Injection

REST web server and dependency injection

The code from this article is available here.

I described basic usage of Dagger 2 already in this article, now we need to implement dependency injection mechanism in the web application. Typical web application would have at least two layers, one for the web server itself and another for client request processing.

Spark Java web server internally use embedded Jetty web server. To setup and start the server we provide some central services like configurations, authentication, statistics etc. Those services are usually instantiated once per whole application.

For each client request a new thread is allocated for the whole duration until the request is processed. Each client request trigger a method defined in the router.

Depend on the design decisions but we usually want that each request will be processed by new object instance (an example is ActorResource in the picture). We will probably have multi threaded problems or simply data leaks from one user to another if we don’t create new instance each time the request is received.

Some objects needed in the typical request processing scenario have different lifecycle requirements, for example database transaction object must be the same for the whole duration of the started transaction but different for each user. There are times when we need two independent transactions in one service request for example. Transactions are span over multiple service objects for example.

On the other side when we do not require transaction (read only processing) we would be better of if we use first available connection allocated to us from the connection pool for the smallest amount of time possible.

As we see from the use cases above there are very different lifecycle scenarios and dependency injection must support them all.

Scopes

We will need at least two scopes for our web application. First scope is application level scope. In the dagger this scope is on by default. Each time we tag a class as a “@Singleton”, the object will be instantiated on the application level and all subsequent requests for this object will return the same instance. So the singleton representing an “application scope” by default. No need for specific scope definitions.

Classes without any scope annotations (no @Singleton or any other scope) are always provided with a new instance.

To manage injection on the application level we create ApplicationComponent interface and ApplicationModule class.

package com.bisaga.sakila.dagger;

import com.bisaga.sakila.Application;
import com.bisaga.sakila.server.ConfigProperties;
import com.bisaga.sakila.server.RequestSession;

import dagger.Component;
import javax.inject.Singleton;

@Singleton
@Component(modules = {ApplicationModule.class})
public interface ApplicationComponent {

    // we first need to create instance of Application on which we will inject dagger into
    Application application();

    // Parent component is obliged to declare sub components getters inside its interface (RequestScope component)
    RequestComponent requestComponent();

    // We will need new instance of RequestSession for each user requests (save instance to the request attributes)
    String REQUEST_SESSION_ATTR_NAME = "requestSession";
    RequestSession requestSession();

    ConfigProperties configProperties();

}

Application module class:

package com.bisaga.sakila.dagger;

import com.bisaga.sakila.server.ConfigProperties;
import com.bisaga.sakila.server.HikariProperties;
import com.bisaga.sakila.server.RuntimeEnvironment;
import com.bisaga.sakila.server.GsonTransformer;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import dagger.Module;
import dagger.Provides;
import spark.ResponseTransformer;

import javax.inject.Named;
import javax.inject.Singleton;
import java.util.Properties;


@Module
public class ApplicationModule {
    // this is a parameter member variable
    private RuntimeEnvironment runtimeEnvironment;

    // we need an constructor to take parameter in
    public ApplicationModule(RuntimeEnvironment runtimeEnvironment) {
        this.runtimeEnvironment = runtimeEnvironment;
    }


    // This provide method at the creation of the instance of ConfigProperties object take the parameter from the local member variable.
    @Provides
    @Singleton
    public ConfigProperties provideConfigProperties() {
        return new ConfigProperties(runtimeEnvironment);
    }

    // All provide methods should be static, generated dagger code is much simpler and smaller,
    // except when module member variables are needed to create new instances
    // Gson constructor is not available to be annotated with inject (not our source), instead we write provider method
    // the method is static, because we do not need any module member variable to build new instance
    @Provides
    @Singleton
    public static Gson provideGson(){
        return new GsonBuilder().setPrettyPrinting().create();
    }


    // Here we provide an instance of the ResponseTransformer interface. Because we didn't define which implementation
    // we need at injecting place we write provide method with the injected parameter expressed with a required interface type
    // If we have more then one possible implementation, we put on "Named" annotation and associate the same name at
    // injected constructor variable
    @Provides
    @Singleton
    public static ResponseTransformer provideResponseTransformer(GsonTransformer gsonTransformer){
        return gsonTransformer;
    }

    // here we provide a String instance. We need the provide method, because many "String" objects are possible.
    // We use "Named" annotation to be able to differentiate this one from other possible provide methods.
    // At injecting place we also define same "Named" annotation to connect the proper one
    // The method could be static, because is independent of local member variables
    @Provides
    @Singleton
    @Named("api_key")
    public static String provideApiKey(){
        return "01657172-ecc0-4cb6-8486-5e7e05a0876f";
    }


    // Here we provide external library class as singleton with our extended properties class as constructor parameter
    @Provides
    @Singleton
    public static HikariConfig provideHikariConfig(HikariProperties properties) {
        return new HikariConfig(properties);
    }

    // Here we provide external library class as singleton (Hikari Connection Pool Data source, holder of pooled connections)
    @Provides
    @Singleton
    public static HikariDataSource provideHikariDataSource(HikariConfig config) {
        return new HikariDataSource(config);
    }


}

At the application level example we present next use cases:

  • creating an instance with the supplied constructor parameter (ConfigProperties service)
  • instantiate objects  from the external dependencies with provide method (Gson)
  • instantiate specific implementation for an interface (ResponseTransformer interface)
  • instantiate an String object with named annotation (using name as differentiator)

Request scope

To create “request scope” we write one annotation interface (“@interface”) one component (RequestComponent) and at least one module class (RequestModule). The component must have sub-component annotation.

To manage DI on the request scope level we create annotation type interface RequestScope , RequestComponent interface and RequestModule class.

@Scope
@Retention(RetentionPolicy.RUNTIME)
public @interface RequestScope {
}

Just to be clear I want to emphasize that each class annotated with the “@RequestScope”  annotation will be instantiated exactly once per created instance of RequestComponent class.  It means that scope annotations represent local singletons.

package com.bisaga.sakila.dagger;

import com.bisaga.sakila.resource.ActorResource;
import com.bisaga.sakila.server.*;
import dagger.Subcomponent;

import java.sql.Connection;

@RequestScope
@Subcomponent(modules = {RequestModule.class})
public interface RequestComponent {

    ActorResource actorResource();
}

Module class:

package com.bisaga.sakila.dagger;

import com.bisaga.sakila.dbmodel.tables.daos.ActorDao;
import com.bisaga.sakila.server.JooqConfigurationBuilder;
import com.bisaga.sakila.server.Transaction;
import com.bisaga.sakila.server.TransactionBuilder;
import dagger.Module;
import dagger.Provides;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;

import javax.inject.Named;

@Module
public class RequestModule {

    // Provides primary transaction/connection for the whole time request ran (loaded at the first component needs)
    // Because it is request scoped, it will be called only once, any other time the request scoped instance will return
    @Provides
    @RequestScope
    public static Transaction provideTransaction(TransactionBuilder transactionBuilder) {
        return transactionBuilder.create(false);    // default autoCommit=False, commit/rollback must be called manually
    }

    // Default configuration with transactions enabled, this is RequestScoped default database connection
    @Provides
    @RequestScope
    public static Configuration provideConfiguration(JooqConfigurationBuilder jooqConfigurationBuilder) {
        return jooqConfigurationBuilder.build();
    }

    // Default DSLContext with default configuration
    @Provides
    @RequestScope
    public static DSLContext provideDSLContext(Configuration configuration) {
        return DSL.using(configuration);
    }

    @Provides
    public static ActorDao provideActorDao(Configuration configuration){
        return new ActorDao(configuration);
    }

}

We use localized singletons especially for the transaction and jooq data access support.

Provide methods are optional, we can decorate classes in the source code with the corresponding annotations.

Service classes

If we analyze the code in the consumer classes, it become ridiculously simple.  All externalized requirements are created by the dagger code almost hassle free.

In the ActorResource class for example we analyze received request,  extract parameters and start business logic. The transaction object is created on the request scope and pass down to all service objects in need to collaborate in the same database transaction.

package com.bisaga.sakila.resource;

import com.bisaga.sakila.dagger.RequestScope;
import com.bisaga.sakila.dbmodel.tables.pojos.Actor;
import com.bisaga.sakila.server.Transaction;
import com.bisaga.sakila.service.ActorService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import spark.Request;
import spark.Response;

import javax.inject.Inject;
import java.util.List;

@RequestScope
public class ActorResource {
    private static final Logger LOG = LoggerFactory.getLogger(ActorResource.class);
    private final Transaction transaction;
    private final ActorService actorService;

    @Inject
    public ActorResource(
            Transaction transaction,
            ActorService actorService
    ) {
        this.transaction = transaction;
        this.actorService = actorService;
    };

    public List<Actor> getActors(Request request, Response response) {
        try {
            // Analyse & extract request parameters

            // multiple SQL statements within single transaction, first we need to start the transaction
            transaction.begin();

            // Call services with parameters (if any)
            List<Actor> actors = actorService.getActors();

            // Commit transaction and release the underline connection to the pool
            transaction.commit();

            // Return list, it will be transformed by GsonTransformer and returned to the browser as Json
            return actors;

        // Catch expected business exceptions and throw them with meaningful messages and present them to the customer
        } catch (Exception e) {
            // Rollback transaction and release the underline connection to the pool
            transaction.rollback();
            // rethrow all exceptions to master exception handler which create response for the customer
            throw e;
        }
    }
}

In the ActorService class we receive all constructor parameters from the dagger automatically.

package com.bisaga.sakila.service;

import com.bisaga.sakila.dagger.RequestScope;
import com.bisaga.sakila.dbmodel.tables.daos.ActorDao;
import com.bisaga.sakila.dbmodel.tables.pojos.Actor;
import org.jooq.DSLContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.inject.Inject;
import java.util.List;

@RequestScope
public class ActorService {
    private static final Logger LOG = LoggerFactory.getLogger(ActorService.class);

    private final DSLContext db;
    private final ActorDao actorDao;

    @Inject
    public ActorService(DSLContext db, ActorDao actorDao) {
        this.db = db;
        this.actorDao = actorDao;
    }

    public List<Actor> getActors() {
        return actorDao.findAll();
    }

    public Actor getActor(int id) {
        return actorDao.fetchOneByActorId(id);
    }
}

The class ActorService require two objects at the constructor: jooq DSLContext  class and ActorDao class.

DSLContext class is part of the Jooq data access library and is instantiated with the provider method “provideDSLContext”. It is annotated as @RequestScope it means the RequestComponent will keep single instance of it for the duration of one request cycle.

ActorDao object is also generated by Jooq library so we couldn’t tag it with scope annotation in the source (so we wrote the provideActorDao method in the request module).

Summary

Dagger calculate all dependencies in the compile time and generate the required code for the whole graph of dependencies and is able to instantiate appropriate objects at appropriate times really fast.

The code from this article is available here.

Other resources:

More about dagger scopes, sub-components .

Sakila: Sample app project setup

This sample application will integrate quite a few very nice open source tools available to every developer:

  • Postgresql – database
  • Flyway – database migration tool
  • Jooq – Java object oriented querying + HikariCP connection pool
  • Dagger 2 – dependency injection
  • SparkJava – fast and simple web server + GSON Json serializer
  • JavaScript Polymer SPA application framework
  • Vaadin Elements components

The application will consist of many modules :

Postgresql – database

Initialize sample database

For start we will install sample sakila database  in the local Postgresql database. Restore downloaded file into locally created database.

C:\Program Files\PostgreSQL\9.6\bin\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "sakila" --no-owner --no-tablespaces --verbose "C:\\DVDRENTAL.TAR"

Sakila sample database is open source demo database and represent database model of DVD rental store. It  consist of  15 relational tables, 7 views and few other database details and it’s full of data.

Well that’s database with full of test data for development environment. If we need empty database (for production for example) we need to start initialization DDL script to build the database.

To create the script from the existing database use the command pg_dump which is capable of exporting database in the form of sql commands :

pg_dump -s -U postgres sakila > V1.0.1__sakila_init.sql

To export database without any data , only schema definitions we use “schema only” (-s) option.

Flyway migrations

Create flyway config file and “migrations” folder under the project root.

flyway.url=jdbc:postgresql://localhost:5432/sakila
flyway.user=postgres
flyway.password=postgres
flyway.schemas=public
flyway.locations=filesystem:./migrations

Add “fw” command somewhere on the path.

#!/bin/sh
/c/Programs/flyway-4.2.0/flyway $@

Put the “V1.0.1__sakila_init.sql” file in the migrations folder. If everything works as expected the “info” command should report the pending migration.

Flyway migration and initial database state after database restore

After restoring the database with test data in it we need to “baseline” initial migration. Initial sql script to create empty database was bypassed with restore. The V1.0.1__sakila_init.sql migration script was still pending.

With the baseline command we agree that the migration is not needed and you mark the migration version as migrated.

 

Setup java server project

In the IDE (IntelliJ IDEA Community 2017.2) create new console type project “sakilaweb/server”.

Setup git-bash terminal as default intellij terminal

Jooq – object oriented querying

Create jooq config file and add jooq command somewhere on the path.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.10.0.xsd">
    <!-- Configure the database connection here -->
    <!-- TRACE, DEBUG, INFO, WARN, ERROR, FATAL -->
    <logging>INFO</logging>
    <jdbc>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://localhost:5432/sakila</url>
        <user>postgres</user>
        <password>postgres</password>
    </jdbc>

    <generator>
        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.util.JavaGenerator
             - org.jooq.util.ScalaGenerator
             Defaults to org.jooq.util.JavaGenerator -->
        <name>org.jooq.util.JavaGenerator</name>

        <database>
            <!-- The database type. The format here is:
                 org.util.[database].[database]Database -->
            <name>org.jooq.util.postgres.PostgresDatabase</name>

            <!-- The database schema (or in the absence of schema support, in your RDBMS this
                 can be the owner, user, database name) to be generated -->

            <inputSchema>public</inputSchema>

            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->
            <includes>.*</includes>

            <!-- All elements that are excluded from your schema
                 (A Java regular expression. Use the pipe to separate several expressions).
                 Excludes match before includes -->
            <excludes></excludes>
        </database>

        <generate>
            <pojos>true</pojos>
            <daos>true</daos>
        </generate>

        <target>
            <!-- The destination package of your generated classes (within the destination directory) -->
            <packageName>com.bisaga.sakila.dbmodel</packageName>

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->
            <directory>./database</directory>
            <encoding>UTF-8</encoding>
        </target>
    </generator>
</configuration>

Bash command:

#!/bin/sh
DIR="C:/Programs/jOOQ-3.10.1/jOOQ-lib/"
DRIVER="C:/Programs/drivers/postgresql-42.1.4.jar"
VER="3.10.1"
CP=$DIR"jooq-"$VER".jar"
CP=$DIR"jooq-meta-"$VER".jar;"$CP
CP=$DIR"jooq-codegen-"$VER".jar;"$CP
CP=$DRIVER";"$CP
java -classpath $CP org.jooq.util.GenerationTool jooq.xml

Add “jooq-3.10.1.jar” library to project dependencies. Add “postgresql-42.1.4.jar” if you use the same database.

Run code generation tool with “jooq” command in the terminal at the project root.

After code was successfully generated in the “./database” folder you will get a bunch of database related code ready made ( database schema, POJOs, and DAOs).

The project with generated code will now look like :

Setup Dagger 2

Configure IDEA for annotations processor.

Add dagger dependencies (dagger-compiler only as “Provided” because it is used only for code generation ).

Setup SparkJava web server

Add few references to the project dependencies and setup “hello-world” web sample just to be sure everything is setup us expected before start real coding.

com.sparkjava:spark-core:2.0.0
org.slf4j:slf4j-simple:1.7.25

com.google.code.gson:gson:2.8.2
com.google.dagger:dagger:2.12
com.google.dagger:dagger-compiler:2.12

Create main procedure as :

package com.bisaga.sakila;

import static spark.Spark.*;

public class Main {

    public static void main(String[] args) {
        get("/hello", (req, res)-> "Hello !");
    }
}

Now if you run the application you should already get the first page:

Publish to the github

First enable VSC support in the local project and add .gitignore file to the project. Next we add files to the local git repository created in the project.

If we want  to push code to the remote repository we need to create it to have repository to commit to. Login to the github and create new empty repository.

The code for the server side project is available here.

 

 

Next : In the next installment I will put the generated database layer into the use and expose first REST service.

 

 

 

 

 

Java Spring Boot project setup

You can get source code of this project from github repository “SpringBootMyApp” .

Create Spring Boot Maven project

Go to Spring Boot project generator web site and select minimal project definition with dependencies :

  • Web:  dependency for embedded tomcat server
  • jOOQ: integrated SQL query language and data model code generator
  • Flyway: database model migration tool
  • Postgresql JDBC driver

After downloading ZIP project file, unzip it to some folder and open folder in IntelliJ IDE environment.

Startup class

The project will automatically include embedded tomcat server and spring application start will configure whole application at startup.

package com.bisaga.myapp;

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

@SpringBootApplication(scanBasePackages = { "com.bisaga" })
public class MyappApplication {

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

Setup project properties

Under src/main/resources find file application.properties and add configuration for database access  and flyway configuration. Your application will start migration procedure at every application start and syncronize to the correct version automatically.

#Debug
debug=false

# Database connection
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/myappdb
spring.datasource.username=..........
spring.datasource.password=..........

# Jooq dialect
spring.jooq.sql-dialect=POSTGRES

# Flyway
flyway.locations=classpath:db/migration

Flyway migration files

You need a folder where your SQL migration files will reside:

src/main/resources/db/migration

Add first migration file, for example “create currency table” will be saved in file V1_0_0__currency.sql:

/*
 * Currency
 */
CREATE TABLE currency (
	code 						VARCHAR(60) not null,
	abbreviation 				varchar(60),
	description 				text,
	row_id 						serial primary key,					-- row identifier
	created_at 					timestamptz not null default now(),
	created_by 					varchar(60) not null,
	modified_at 				timestamptz,
	modified_by 				varchar(60)
);
CREATE INDEX currency_created_at ON currency (created_at) ;
CREATE INDEX currency_modified_at ON currency (modified_at) ;

-- DEMO DATA
INSERT INTO public.currency (code, abbreviation, description, created_by)
VALUES('EUR', 'Eur', 'Euro', 'admin');

INSERT INTO public.currency (code, abbreviation, description, created_by)
VALUES('USD', 'Usd', 'US Dollar', 'admin');

Be extra careful with names , first part (V1_0_0) is version of migration file, second part of the name is a description, separated with two underline characters.

The scripts must be correct SQL! Validate it first in database environment. If script is not valid, migration procedure will break and you will probably have hard time to figure out what was wrong.

Now you run your application and your database must be prepared according to migration script changes automatically. Flyway start automatically with your application.

More information on this video tutorial for flyway and spring boot..

Maven plugin settings

If you want more control around code generation and migration life cycle, you add flyway and jooq plugins:

Add this properties to the properties section in pom.xml file:

        <!-- Database -->
        <db.url>jdbc:postgresql://localhost:5432/myappdb</db.url>
        <db.username>........</db.username>
        <db.password>........</db.password>
        <!-- FlyWayDB -->
        <org.flywaydb.schema>public</org.flywaydb.schema>
        <org.flywaydb.location>classpath:db/migration</org.flywaydb.location>
        <org.flywaydb.version>4.2.0</org.flywaydb.version>
        <!-- jOOQ -->
        <org.jooq.codegen.namespace>com.bisaga.myapp.database.model</org.jooq.codegen.namespace>
        <org.jooq.input_schema>public</org.jooq.input_schema>
        <org.jooq.target_dir>src/main/java/</org.jooq.target_dir>
        <org.jooq.version>3.9.2</org.jooq.version>
  • Database connection settings in pom.xml will be used by jooq code generator and flyway migration maven plugins.

Flyway and Jooq dependency and version

You probably already have it from generated pom.xml, add it if you don’t have it.

        <dependency>
            <version>${org.flywaydb.version}</version>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <version>${org.jooq.version}</version>
            <artifactId>jooq</artifactId>
        </dependency>

Version was not required by spring boot but I put version in because I encounter differences between plugin version and spring boot version of the libraries.

Now you need to define plugin definitions as :

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <plugin>
                <groupId>org.flywaydb</groupId>
                <version>${org.flywaydb.version}</version>
                <artifactId>flyway-maven-plugin</artifactId>
                <!-- Note that we're executing the Flyway plugin in the "generate-sources" phase -->
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>migrate</goal>
                        </goals>
                    </execution>
                </executions>
                <!-- Note that we need to prefix the db/migration path with filesystem:
                    to prevent Flyway from looking for our migration scripts only on the classpath -->
                <configuration>
                    <url>${db.url}</url>
                    <user>${db.username}</user>
                    <password>${db.password}</password>
                    <locations>
                        <location>${org.flywaydb.location}</location>
                    </locations>
                    <schemas>${org.flywaydb.schema}</schemas>
                </configuration>
            </plugin>

            <plugin>
                <!-- Use org.jooq for the Open Source Edition org.jooq.pro for commercial
                    editions, org.jooq.pro-java-6 for commercial editions with Java 6 support,
                    org.jooq.trial for the free trial edition Note: Only the Open Source Edition
                    is hosted on Maven Central. Import the others manually from your distribution -->
                <groupId>org.jooq</groupId>
                <version>${org.jooq.version}</version>
                <artifactId>jooq-codegen-maven</artifactId>
                <!-- The jOOQ code generation plugin is also executed in the generate-sources phase,
                     prior to compilation -->
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>

                <!-- This is a minimal working configuration. See the manual's section about the code generator
                     for more details -->
                <configuration>
                    <jdbc>
                        <url>${db.url}</url>
                        <user>${db.username}</user>
                        <password>${db.password}</password>
                    </jdbc>
                    <generator>
                        <database>
                            <includes>.*</includes>
                            <inputSchema>${org.jooq.input_schema}</inputSchema>
                        </database>
                        <target>
                            <packageName>${org.jooq.codegen.namespace}</packageName>
                            <directory>${org.jooq.target_dir}</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>

Flyway and Jooq maven commands

After you add plugin definitions to maven pom.xml file you get new lifecycle commands :

When flyway commands are used directly they search for SQL files in the target “classes” folder and not in the source tree (“src”). When project compiled the files, target folder is synchronized with the current version of code in the source tree.

After compiling project you could run “flyway:migrate” command for example. You can always check files in the target folder and delete it if you are not sure you have the latest version of the files.

Jooq code generation

Jooq generate data model code from your database. The code is added to the project in the namespace as you defined in plugin definition.  It’s wise to split plugin definition to properties and plugin definition part.

<!-- jOOQ -->
<org.jooq.input_schema>public</org.jooq.input_schema>
<org.jooq.codegen.namespace>com.bisaga.myapp.database.model</org.jooq.codegen.namespace>
<org.jooq.target_dir>src/main/java/</org.jooq.target_dir>

Every time you compile project, database model code is regenerated. Check here for more information about jooq.

You can check how database model code is regenerated simply by deleting a line from one of generated files and recompile the project again.

More about jooq & spring boot can be found here.

Create JSON service

Now I need to test the environment if everything is set in place as should be. I will create simple currency service as sample application and test basic CRUD operations.

Create simple data container class

To be able to send JSON payload back and forth from client to server we create a simple transport class:

package com.bisaga.myapp;

/**
 * Created by igorb on 17. 06. 2017.
 */
public class CurrencyDto {
    private String code;
    private String abbreviation;
    private String description;
    private Integer rowId;

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getAbbreviation() {
        return abbreviation;
    }

    public void setAbbreviation(String abbreviation) {
        this.abbreviation = abbreviation;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getRowId() {
        return rowId;
    }

    public void setRowId(Integer rowId) {
        this.rowId = rowId;
    }
}

Service class and database interaction with Jooq

Now we create service class with which we will interact with database :

/*
*  MIT License
*  Copyright (c) 2017 Igor Babic
*/
package com.bisaga.myapp;

import static com.bisaga.myapp.database.model.tables.Currency.CURRENCY;

import com.bisaga.myapp.database.model.tables.records.CurrencyRecord;
import org.jooq.DSLContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


@Service
@Transactional
class CurrencyService {
    private static final Logger LOG = LoggerFactory.getLogger(CurrencyService.class);

    @Autowired
    private DSLContext db;

    /**
     * List all currencies in the currency table
     * @return Return list of currency records
     */
    List<CurrencyDto> getAll() {
        List<CurrencyDto> currList;
        currList = db.select().from(CURRENCY)
                .fetch().into(CurrencyDto.class);

        return currList;
    }

    /**
     * Search for a currency record by currency code
     * @param code  currency code as parameter
     * @return Return currency record
     */
    CurrencyDto getByCode(String code) {
        List<CurrencyDto> currList;
        currList = db.select().from(CURRENCY)
                .where(CURRENCY.CODE.eq(code))
                .fetch().into(CurrencyDto.class);
        
        return currList.get(0);
    }

    /**
     * Delete currency record by row identifier
     * @param rowId rowId row identifier
     * @return Return number of deleted records
     */
    Integer delete(Integer rowId) {
        return db.delete(CURRENCY).where(CURRENCY.ROW_ID.eq(rowId)).execute();
    }

    /**
     * Save currency execute insert or update command if currency already exist.
     * @param currency instance of CurrencyDto class as parameter
     */
    CurrencyDto saveCurrency(CurrencyDto currency) {
        if (currency.getRowId() == null) {
            return this.insert(currency);
        } else {
            return this.update(currency);
        }
    }

    /**
     * Insert new currency record to database
     * @param currency Currency record as parameter
     * @return Return currency record filled with database defaults after insert
     */
    private CurrencyDto  insert(CurrencyDto currency)  {
        CurrencyRecord record = db.newRecord(CURRENCY, currency);
        record.setCreatedBy("admin");
        record.insert();                // insert to database
        record.into(currency);			// return changes made by database
        return currency;
    }

    /**
     * Update currency record in database
     * @param currency currency record as parameter
     * @return Return currency record filled with changes from database update
     */
    private CurrencyDto update(CurrencyDto currency) {
        CurrencyRecord record = db.newRecord(CURRENCY, currency);
        record.update();                // update to database
        record.into(currency);			// return changes made by database
        return currency;
    }

}

REST controller with router mapping

On the end we need a controller class to expose access points for REST service:

/*
*  MIT License
*  Copyright (c) 2017 Igor Babic
*/
package com.bisaga.myapp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import java.net.Authenticator;
import java.util.List;

@RestController
@RequestMapping(value = "/api/currency", produces = "application/json")
public class CurrencyController {

    @Autowired private CurrencyService service;

    @RequestMapping(method=RequestMethod.GET)
    @ResponseStatus(HttpStatus.FOUND)
    public List<CurrencyDto> findAll() {
        return service.getAll();
    }

    @RequestMapping(value="/{code}", method=RequestMethod.GET)
    @ResponseStatus(HttpStatus.FOUND)
    public CurrencyDto findByCode(@PathVariable("code") String code) {
        return service.getByCode(code);
    }

    @RequestMapping(method=RequestMethod.POST)
    @ResponseStatus(HttpStatus.OK)
    public CurrencyDto saveCurrency(@RequestBody CurrencyDto currency) {
        return service.saveCurrency(currency);
    }

    @RequestMapping(method=RequestMethod.DELETE)
    @ResponseStatus(HttpStatus.OK)
    public Integer deleteCurrency(@RequestParam(value = "rowid", required = true) Integer rowId ) {
        return service.delete(rowId);
    }


}

Don’t forget to instantiate services with DI (dependency injection) with @Autowire annotation. This simplify development a lot !

Interactive testing

The service should in this point work as expected, just run application and navigate to “localhost:8080/api/currency”.

You can search for a specific currency with added path variable appended to Url address:

Postman application for Http API testing

To inspect service in more details on the client side, you can use Postman application.

Look at example ofadding a currency with a POST message:

In the headar I add “content-type” variable with value “application/json” and in the payload a json message with a new currency json structure. On the right side I received “200-OK” and a currency record with a record and new row identifier registered in the database.

 

TomEE: Java EE server database configuration

Configure database access

I use Apache TomEE server and therefore I need to configure it for database purpose before first use.

Install database driver

Drop database driver jar file in tomee/lib folder.

Configure datasource

Resources are usually defined in  server configuration file.

Add datasource resource definition in configuration file located in  tomee/conf/tomee.xml.

<?xml version="1.0" encoding="UTF-8"?>
<tomee>  
  <Resource id="jdbc/db" type="javax.sql.DataSource">
    jdbcDriver org.postgresql.Driver
    jdbcUrl jdbc:postgresql://localhost:5432/bsg_taskmgr_db
    userName postgres
    password postgres
  </Resource>
</tomee>

Verify configuration

After server (tomee service) restart, search for your datasource in log (example log file: tomee/logs/catalina.2016-11-26.log). You will find log entry with your resource id there:

Configuring Service(id=jdbc/db, type=Resource, provider-id=Default JDBC Database)

If you restart server from inside netbeans,  just search in output window where log entries are shown.

Inject datasource in java code and use it

To inject instance of datasource where connection is needed you simply add annotation “@Resource” above variable declaration:

@Resource(name = "jdbc/db")
private DataSource ds;

Let’s see whole example with select statement (jOOQ):

package com.bisaga.demo;

import static com.bisaga.demo.generated.tables.Book.BOOK;
import com.bisaga.demo.generated.tables.records.BookRecord;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
/**
 * @author igorb
 */
public class HelloService {

    @Resource(name = "jdbc/db")
    private DataSource ds;    

    public String createHelloMessage(String name) {
        String allBooks = "";
        try {
            DSLContext db = DSL.using(ds, SQLDialect.POSTGRES);
            for(BookRecord b : db.selectFrom(BOOK).fetch() ) {
                allBooks = allBooks.equals("") ? "My books: " : allBooks+";";
                allBooks += b.getTitle();
            }
        } catch (DataAccessException e) {
            System.out.println(e.toString());
        }
        return allBooks;
    }    
}

This example code is called from REST JSON service and result in the browser looks like this:

2016-11-26-16_38_26-localhost_8080_helloworld_webresources_hello

JOOQ – database access layer

Add Java object oriented querying – JOOQ – to project

I love “database first” principle when talking to database. Why would I sacrifice majority of SQL powers, just to talk to database with some ORM technology ? Of course it depend on so many factors, but if you need something very close to database, JOOQ is a way to go.

JOOQ is open source if you use it with open source databases (MySql, Postgres, etc.) !

Configuration and setup

You need to download jooq library and unzip it to some folder (H:\JavaLib\jOOQ-3.8.6).

Code generation config

You need to add jooq config xml file to your project. I added jooq.xml to the root folder.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.8.0.xsd">
  <!-- Configure the database connection here -->
  <jdbc>
    <driver>org.postgresql.Driver</driver>
    <url>jdbc:postgresql://localhost:5432/bsg_taskmgr_db</url>
    <user>postgres</user>
    <password>postgres</password>
  </jdbc>

  <generator>
    <!-- The default code generator. You can override this one, to generate your own code style.
         Supported generators:
         - org.jooq.util.JavaGenerator
         - org.jooq.util.ScalaGenerator
         Defaults to org.jooq.util.JavaGenerator -->
    <name>org.jooq.util.JavaGenerator</name>

    <database>
      <!-- The database type. The format here is:
           org.util.[database].[database]Database -->
      <name>org.jooq.util.postgres.PostgresDatabase</name>

      <!-- The database schema (or in the absence of schema support, in your RDBMS this
           can be the owner, user, database name) to be generated -->
           
      <inputSchema>public</inputSchema>
 		
      <!-- All elements that are generated from your schema
           (A Java regular expression. Use the pipe to separate several expressions)
           Watch out for case-sensitivity. Depending on your database, this might be important! -->
      <includes>.*</includes>

      <!-- All elements that are excluded from your schema
           (A Java regular expression. Use the pipe to separate several expressions).
           Excludes match before includes -->
      <excludes></excludes>
    </database>

    <target>
      <!-- The destination package of your generated classes (within the destination directory) -->
      <packageName>com.bisaga.demo.generated</packageName>

      <!-- The destination directory of your generated classes. Using Maven directory layout here -->
      <directory>./src/java</directory>
      <encoding>UTF-8</encoding>
    </target>
  </generator>
</configuration>

Run code generator

The simplest way is with bash command, this way will always work, even without any special build tool or IDE.

#!/bin/sh
DIR="C:/Programs/jOOQ-3.10.1/jOOQ-lib/"
DRIVER="C:/Programs/drivers/postgresql-42.1.4.jar"
VER="3.10.1"
CP=$DIR"jooq-"$VER".jar"
CP=$DIR"jooq-meta-"$VER".jar;"$CP
CP=$DIR"jooq-codegen-"$VER".jar;"$CP
CP=$DRIVER";"$CP
echo $CP
java -classpath $CP org.jooq.util.GenerationTool jooq.xml

This script is compatible with git-bash on windows. The libraries and folders are of latest version, change to your specific version before run.

“jooq” script is saved in the “bin” folder which is on the path. Now I can run it from everywhere.

Inside Netbeans, open the terminal and type jooq command, you should be in project root folder, where configuration for jooq is.

Generated code

Jooq will generate code for all your tables. You will find new files in packages with name “*.generated.*” :

2016-11-25-17_44_00-start