Database Access - Skullabs/kikaha GitHub Wiki

Connection Pool is a convenient way to deal with JDBC connections and provide nice and efficient performance to the application at all. ViburDBCP is an impressively fast and lightweight Database Connection Pool which Kikaha's Database module provides tight integration with. Include kikaha-db module on your project in order to have this feature enabled.

<dependency>
  <groupId>io.skullabs.kikaha</groupId>
  <artifactId>kikaha-db</artifactId>
</dependency>

All DataSource configurations are made through the server.db.datasources entry point at the application.conf. Note that, on 2.0.x and earlier versions this configuration were available at server.datasources.

server:
  db:
    datasources:
      default:
        log-query-execution-longer-than-ms: 20
        log-stacktrace-for-long-query-execution: false
        default-auto-commit: true
        jdbc-url: "jdbc:mysql://db.corp.com/test_db"
        username: "username"
        password: "p@5zw0rd"

      production:
        jdbc-url: "jdbc:mysql://db.corp.com/test_db"
        username: "username"
        password: "p@5zw0rd"

      development:
        jdbc-url: "jdbc:mysql://localhost/test_db"
        username: "root"
        password: ""

Above we have a sample DataSource configuration named production, another one named development, and a third one named default. As you can see, both have the jdbc-url, username and password attributes defined: they are required parameters. Any other parameter is optional as it uses Vibur's default values.

The code bellow makes use from the production DataSource.

import kikaha.core.api.*;
import javax.inject.*;
import javax.sql.*;

@Singleton
public class EmployeeSearchService {

  @Inject
  @Named("production") // could be omitted if using the 'default' one.
  DataSource db;

  public void printAllEmployees()
  {
    try (Connection con = ds.getConnection())
    {
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select empid, name from Employee");

      while(rs.next()){
        String msg = "Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name");
        System.out.println( msg );
      }
    }
  }
}

You can notice that, in order to inject "production" DataSource, you should specify the source name through the @javax.inject.Named annotation. You will have to use that annotation for every DataSource injection point, except when you intent to inject the default DataSource - in this case, the @Named annotation is optional.

Most of Viburs configuration was brought to the Kikaha Database module and are available to developers use. Bellow is a list of available parameters and its default values. For more informations, please refer to the Vibur's documentation.

• pool-initial-size: 10
• pool-max-size: 100
• pool-fair: true
• pool-enable-connection-tracking: false
• jdbc-url:
• username: "root"
• password: "root"
• connection-timeout-in-ms: 500
• login-timout-in-seconds: 10
• acquire-retry-delay-in-ms: 1000
• acquire-retry-attempt: 3
• connection-idle-limit-in-seconds: 15
• validate-timeout-in-seconds: 3
• test-connection-query: "isValid"
• init-sql: "isValid"
• log-query-execution-longer-than-ms: 80
• log-stacktrace-for-long-query-execution: true
• log-large-resultset: 500
• log-stacktrace-for-large-resultset: true
• log-connection-longer-than-ms: 100
• clear-sql-warnings: false
• reset-defaults-after-use: false
• default-auto-commit: true
• default-read-only: false
• statement-cache-max-size: 0
• pool-reducer-class: "org.vibur.dbcp.pool.PoolReducer"
• reducer-time-interval-in-seconds: 60
• reducer-samples: 20