Dropwizard JDBI3

How to Configure?

For creating a managed and instrumented JDBI instance, add a DataSourceFactory Instance in your configuration class.

public class Example extends Configuration {
    @Valid
    @NotNull
    private DataSourceFactory db = new DataSourceFactory();
    @JsonProperty("database")
    public void setDataSourceFactory(DataSourceFactory factory) {
        this.db= factory;
    }
    @JsonProperty("database")
    public DataSourceFactory getDataSourceFactory() {
        return db;
    }
}

You can also try this code with Online Java Compiler

Run Code

Create a JDBIFactory in your service’s run method after the configuration.

public void run(Example config, Environment envir) {
    final JdbiFact factory = new JdbiFact();
    final Jdbi jdbi = factory.build(envir, config.getDataSourceFactory(), "postgresql");
    envir.jersey().register(new UserResource(jdbi));
}

You can also try this code with Online Java Compiler

Run Code

After you follow the above-stated steps, you will encounter the following

• Managed Connection to Database
• Health Check for connectivity to the database
• JDBI instance

The format of the service configuration file will resemble the following-

Database:

# Specifies the JDBC driver’s name
  driverClass: org.postgresql.Driver


  # mentions the username
  user: user123


  # states the password
  password: passcode123


  # The url of the JDBC
  url: jdbc:postgresql://datab.exampleconfig.com/db-prod


  # specific properties of JDBC driver
  properties:
    charSet: UTF-8


  # The waiting time before throwing an exception 
  maxWaitForConnection: 2s


  # the SQL query to run when validating a connection's liveness
  validationQuery: "/* MyService Health Check */ SELECT 1"


  # the timeout 
  validationQueryTimeout: 3s


  # the minimum open connections
  minSize: 8


  # the maximum open connections
  maxSize: 28


  # boolean value for connections’ validation
  checkConnectionWhileIdle: true


  # the idle time
  evictionInterval: 20s


  # the time for which a connection is supposed to sit idle before eviction
  minIdleTime: 2 minute

Plugins in Dropwizard JDBI3

Plugins add more functionality to the parent implementation. Sqlobject,jodatime, and guava plugins are introduced by default. You can add more plugins as per the requirement.

Usage 

JDBI’s SQL Object API provides the facility to write DAO classes as interfaces. This is why they are used.

public interface MyDAOMy {
  @SqlUpdate("create table abc (id int primary key, name varchar(100))")
  void createabc();


  @SqlUpdate("insert into abc (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);


  @SqlQuery("select name from abc where id = :id")
  String findNameById(@Bind("id") int id);
}


final MyDAOMy dao = database.onDemand(MyDAOMy.class);

You can also try this code with Online Java Compiler

Run Code

How to Handle Exceptions?

For handling the exceptions, you need to add JDBIExceptionBundle to your application. Dropwizard will automatically take care of SQLException or JDBIException instances. This is a vital feature for debugging purposes.

Prepended Comments in JDBI3

Since you must be using SQL Objects API, Dropwizard JDBI3 will prepend the name of the object class of SQL and the method name. This should be done as an SQL comment.

This helps to ascertain the slow queries.

/* com.example.service.dao.UserDAO.findByName */
SELECT id, name
FROM usertable
WHERE name = 'CN';

Frequently Asked Questions 

What is DAO?

DAO stands for Data Access Object. It is a standard API to access data.

Name three default plugins in Dropwizard JDBI3.

Guava, sqlobject, and jodatime are three default plugins.

Why is SQL Objects API preferred?

SQL Objects API is preferred as it allows us to write DAO classes as interfaces.

What is the job of prepended comments?

They notify about the slow and misbehaving queries.

What happens when we do not add JDBIExceptionBundle to the application?

If JDBIExceptionBundle, only the standard wrapper exception stack’s trace is logged.

Conclusion

In this blog, we became familiar with the concept of Dropwizard JDBI3 and covered various horizons like configuration, plugins, usage, and exception handling.

For more such articles on Dropwizard, refer-

• Dropwizard Client
• Dropwizard-Testing Resources
• Dropwizard-Testing Representations

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Happy Coding!