HOWTO: Setting Up and Using an Amber Database Connection Pool

Step 1: Setup JDBC drivers

Install the JDBC drivers for your database, and set them up so they are in your classpath.

Step 2: Configure config/database/DatabaseManager.config

Open this file in a text editor. This file can be found under the Amber installation root. For example, if you installed Amber into /opt/amber, the path to this file will be /opt/amber/config/database/DatabaseManager.config

The name of this file and all it's contents are case sensitive (even in Windows).

Variables in this file are specified as name=value pairs. Each of these is of the form

DatabaseManager.ConnectionPoolX.variableName=value

The X refers to the number of the connection pool. In the examples below, connection pool 0 has already been set up, and we are configuring connection pool 1. You must number your connection pools 0, 1, 2, 3... and you must not miss any numbers.

These are the values you need to set up:

Variable Set to
DatabaseManager.ConnectionPool1.PoolName

The name of the connection pool. This is how you will refer to the connection pool in your Java code.

eg. Dictionary

DatabaseManager.ConnectionPool1.DatabaseName

The JDBC URL of the database you are connecting to. Different JDBC drivers have different syntaxes. Check your driver's documentation.

eg. jdbc:mysql://myserver.bogus.com/dictionary

DatabaseManager.ConnectionPool1.DatabaseDriver

The fully qualified class name of the JDBC driver. See your driver's documentation for this information.

eg. org.gjt.mm.mysql.Driver

DatabaseManager.ConnectionPool1.DatabaseUsername

Database username

eg. myusernamegoeshere

DatabaseManager.ConnectionPool1.DatabasePassword

Database password

eg. mypasswordgoeshere

DatabaseManager.ConnectionPool1.MinConnectionNumber

The default number of connections to maintain in the connection pool

eg. 2

DatabaseManager.ConnectionPool1.MaxConnectionNumber

The maximum number of connections to maintain in the connection pool. If more than MinConnectionNumber of database connections are requested concurrently, Amber will allocate connections up to this maximum. If you try and allocate more connections than this number, Amber will block until a connection comes free.

eg. 5

DatabaseManager.ConnectionPool1.
ThreadInactivityDuration

Some JDBC drivers will time out and close a connection if it is not used for a particular interval. Set this value to periodically "ping" the connection to ensure that it stays alive. Some drivers do not require this. This value is specified in milliseconds.

eg. 60000

 

Gotchas:

  1. Some JDBC drivers do not support the DatabaseUsername and DatabasePassword parameters. These drivers use the database URL to specify the username and password. For example, for these drivers, your database URL would be something like

    jdbc:mysql://mysqlserver.boguscom/dictionary?user=dbuser&password=dbpassword

  2. Ensure that your name/value pairs all have the same ConnectionPoolX number in the variable names. It's easy to miss one and misconfigure your connection pool.

  3. After changing this file, you will need to restart your Amber Server. When Amber starts up, it will say "<name> ConnectionPool initialising". Look for this message. Any problems will be reported directly after this.

Step 3: Using the Connection Pool in your Java code

To check a connection out of the connection pool, use code like this:

import amber.server.manager.* ;
import amber.server.manager.database.* ;
import java.sql.* ;

public Connection checkOutConnection(String poolname, int timeout) throws SQLException
{
    Connection databaseConnection = null ;
    // Attempt to access the connection pool
    DatabaseManager manager = Core.getCore().getDatabaseManager() ;
    ConnectionPool pool = manager.getConnectionPool ( poolname) ; 
    // Get a connection from the pool
    databaseConnection = pool.getConnection ( timeout ) ; 
    if ( databaseConnection == null )
      throw new SQLException ("Could not connect to connection pool " 
          + poolname + " timeout: " + timeout) ;
 
    return databaseConnection ;
}

When you
have a connection, use it just like a normal JDBC connection.
To check a connection back into the connection pool, use code similar to this:
 protected void checkInConnection ( String poolname, Connection conn )
 {
     DatabaseManager manager = Core.getCore().getDatabaseManager() ;
     ConnectionPool pool = manager.getConnectionPool ( poolname ) ;
     // Close the database connection if any
     if ( conn != null )
         pool.releaseConnection ( conn ) ;
 }
             

Gotchas:

    1. Always make sure you check in every connection you check out. This means using the Java try/finally construct to ensure that all check outs have a corresponding check in. If you don't do this, you may find that your program eventually checks out all the connections, and starts failing when it can find none free.
 




HowTo
Downloads