Database connection management

ActiveJDBC provides two classes for connection management: Base.java and DB.java.

Thread connection propagation

ActiveJDBC models when operate, utilize a connection found on a current thread. This connection is put on the local thread by Base or DB class before any DB operation. This approach allows for more concise API where there is no need for DB Session or persistent managers as in other Java ORMs.

Here is a simple program:

public static void main(String[] args) {
   Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "the_user", "the_password");
   Employee.findAll().dump();
   Base.close();
}

On line 2, class Base will open a new connection and attach it to the current thread. This connection will also be marked with name default.

On line 3, connection is looked up from the thread and used by the model(and result dumped to STDIO)

On line 4, connection is closed and cleared from thread.

Database names

ActiveJDBC has a concept of a logical database. However, an application can be connected to multiple databases at the same time. In this case, ActiveJDBC allows for assigning different logical names to different databases.

For example, one might have an Oracle database with accounting data, and a MySQL database with inventory control data. In this case, you might want to have an accounting database and an inventory database as logical names assigned to these databases.

DB and Base classes

Opening and closing connections is done with classes Base or DB. The DB class is used in cases where you have more than one database in the system, such as accounting and inventory.

Example:

new DB("inventory").open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "dbuser", "...");

In this code example, there is a database connection opened, and attached to a local thread under name inventory.

The classes Base and DB mirror one another, having exactly the same APIs, except:

• All methods on DB are instance methods, while all methods on class Base are static ones.
• class DB constructor accepts a DB name, while Base always operates with DB name: default

This means that these lines are equivalent:

new DB("default").open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "root", "p@ssw0rd");

and:

Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "root", "p@ssw0rd");

Use Base class if you have only one database in the system, otherwise, use DB.

Use Try-with-resources

You can use the try-with-resources to automatically close a connection regardless if your code causes exception or not:

try(DB db = new DB()){
  db.open();
  // your code here
}

Use with Lambdas

If you static-import Base.withDB(), you can execute code in the context of a connection using a simple block like this:

withDb(() -> {
        // some code that uses a connection
}); 

There are various versions of withDB on classes DB and Base.

Models associated with multiple databases

ActiveJDBC allows to have a mix of models in the application representing tables from different databases. By default a model belongs to a database default, but an association of a model to a database can be overriden with annotation @DbName:

@DbName("corporation")
public class Employee extends Model {}

Multiple database example

See sources here: multimple-db-example.

For this example, we will have two models, one representing a table in Oracle database, while the other in MySQL

The two models are defined like this:

@DbName("corporation")
public class Employee extends Model {}

and:

@DbName("university")
public class Student  extends Model {}

and the main class looks like this:

public class Main {
    public static void main(String[] args) {
        new DB("corporation").open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "root", "p@ssw0rd");
        new DB("university").open("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@localhost:1521:xe", "activejdbc", "activejdbc");

        Employee.deleteAll();
        Student.deleteAll();

        Employee.createIt("first_name", "John", "last_name", "Doe");
        Employee.createIt("first_name", "Jane", "last_name", "Smith");

        Student.createIt("first_name", "Mike", "last_name", "Myers");
        Student.createIt("first_name", "Steven", "last_name", "Spielberg");

        System.out.println("*** Employees ***");
        Employee.findAll().dump();
        System.out.println("*** Students ***");
        Student.findAll().dump();

        new DB("corporation").close();
        new DB("university").close();
    }
}

At the start of this app the two named connections are opened, then we proceed to use the models associated with these connections. At the end of the app, the two named connections are closed. The class DB is lightweight, and it is OK not to retain a reference to it, but rather to create a new instance each time. If you do want to retain a reference, there is no harm done though.

Database connection pools

ActiveJDBC does accepts a JNDI connection URL to an existing pool. It provides a few DB.open() and Base.open() methods to open pool connections. If a version of a method used that takes standard JDBC parameters, then no pool is used. This is only a convenience method to open a brand new connection, such as:

Base.open("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test", "root", "pwd");

If however, this call is used:

Base.open("java:comp/env/jdbc/testdb");

then it will use a JDNI name to lookup a connection from a pool. Usually this is called from within a container and the name points to a pooled JNDI DataSource configured at a container level.

If you want to work directly with some connection pool, you can do so by feeding a datasource to Base/DB class:

new DB("default").open(datasourceInstance);
//or:
Base.open(datasourceInstance);

Multiple environments (property file method)

The easiest way to configure multiple connections for different environments is to use a property file. By convention, this file is called database.properties and located at the root of classpath.

Here is an example of such a file:

development.driver=com.mysql.jdbc.Driver
development.username=user1
development.password=pwd
development.url=jdbc:mysql://localhost/acme_development

test.driver=com.mysql.jdbc.Driver
test.username=user2
test.password=pwd
test.url=jdbc:mysql://localhost/acme_test

production.jndi=java:comp/env/jdbc/acme

In order for this to work, you need to configure an environment variable ACTIVE_ENV to a value that is equal to a property set key. According to a file above, the ACTIVE_ENV can take on values development and production. The test is special because it is used in development environment, but for running tests (test mode).

Once the file is configured and placed at the root of classpath, you would open connections with a no-argument method like this:

org.javalite.activejdbc.connection_config.DBConfiguration.loadConfiguration("/database.properties); 
// The previous line is necessary starting with version 2.3.2-j8.

new DB("default").open();
//or: 
Base.open();

The first line needs to be called just once at the start for loading configuration from a file.

A configuration related to the current environment will be selected and used to open a connection. This makes it easy to develop applications that live on different environments, and simply know where to connect on each.

If environment variable ACTIVE_ENV is not defined, the framework defaults to environment development.

Location of property file

The file can be located on a classpath or on a file system. For instance, if the path looks like this: /opt/database.properties, then if it is found on classpath, it is loaded first. If not found on classpath, it will look for the file on te file system using the same path.

Environment variables override

In some cases you will need to specify parameters as environment variables. This may happen on cloud based-environments such as Heroku, Jenkins CI, etc. If you use direct JDBC parameters, there are 4 environments variables you can use:

ACTIVEJDBC.URL
ACTIVEJDBC.USER
ACTIVEJDBC.PASSWORD
ACTIVEJDBC.DRIVER

If you use JNDI connection, you can use 1 environment variable:

ACTIVEJDBC.JNDI

These are self-explanatory JDBC connection parameters.

Environment variable - based parameters will override any configuration provided in the database.properties file for current environment.

System properties override

In some cases you will need to specify connection parameters as JVM system properties. If you are using standard JDBC parameters, there are 4 system properties you can use:

activejdbc.url
activejdbc.user
activejdbc.password
activejdbc.driver

If you are using JNDI, the system property is:

activejdbc.jndi

System properties - based configuration will override any configuration provided as environment variables as well as by the database.properties file for current environment.

Specifying DB Schema

In most cases you do not need to worry about this. However, for Oracle and PostgreSQL, some schema elements may leak into your account if a schema is not specified. If you find that your model for instance has an attribute(column) that is not part of this table description, it means that the DB mixed in this column from another table (public schema) that has the same name as your table.

In a case like this, you need to specify a schema for a database. If you use a default database, simply add this system property:

-Dactivejdbc.default.schema=myschema

where default is a name of a database and myschema is a name of your schema in Oracle or PostgreSQL.

In case you do use multiple connections to different databases and you use DbName annotation, replace default to your DB name. For example:

@DbName("university")
public class Student extends Model{}

then you can add a property like this:

-Dactivejdbc.university.schema=myschema

The schema specification is used in order to retrive metadata for tables at start time, and not used in generating queries.