Database connection configuration

Configures database connection properties using the application's zero.config file.

Contents

Using a connection pool
Connection properties
- Externalizing named SQL statements
- Obfuscating passwords
Vendor examples

A zero.data.Manager or zero.data.groovy.Manager instance can be configured to connect to a database using either a javax.sql.DataSource or java.sql.Connection as follows:

Manager data = new Manager(connection);
Manager data = new Manager(dataSource);

When you obtain a Manager instance using a DataSource, opening and closing database connections are managed on your behalf. Conversely, when a Manager is obtained using a Connection, the connection is held open and it is your responsibility to open the connection before you create a new Manager instance and close after using the Manager methods.

Thus, if you application is sensitive to how and when database connections are open and closed, you should manage your own connections.

Using a connection pool

zero.data does not provide any connection pooling. If you want to pool your database connections, you will need to either solve this yourself with your own implementation or use a third-party library such as Apache DBCP or C3PO.

Connection properties

If you choose to create Manager instances using a DataSource, Manager provides a the create() convenience method to assist you. First, provide database connection properties in your application's zero.config file as follows:

/config/db/reviewDB = {
    "class" : "org.apache.derby.jdbc.EmbeddedDataSource",
    "databaseName" : "./db/review"
}

You obtain a Manager configured to connect using these properties by using the create() method as follows:

Manager data = Manager.create("reviewDB");

Externalizing named SQL statements

SQL statements can be externalized from your Java and Groovy code into the config zone of the GlobalContext. The convention is to place a JSON object at /config/db/{dbKey}/statements. For example, using the reviewDB configuration example from above, the following snippet illustrates how to provide named SQL statements:

/config/db/reviewDB/statements = {
    "SELECT_ALL"      : "SELECT * FROM table",
    "SELECT_WHERE_IN" : "SELECT * FROM table WHERE id IN (?,?,?)",
    "INSERT" : "INSERT INTO table (col1, col2) VALUES (?,?)"
}

For more information about using named SQL statements, see zero.data's API documentation.

Obfuscating passwords

You can obfuscate passwords for database connection properties in your application's zero.config file by using the XOR encoding utility and set the password property prefixed with "<xor>" in your database's configuration like the following example:

/config/db/votingDb = {
    "class" : "com.mysql.jdbc.jdbc2.optional.MysqlDataSource",
    "serverName" : "localhost",
    "portNumber" : 3306
    "databaseName" : "voting",
    "password" : "<xor>Lz4sLCgwLTs="
}

This simply obfuscates the password and does not securely protect your password. XOR encoding only keeps the password from being stored in plain text. For more details on how to generate a password with this encoding, consult the XOR Encoder documentation.

Vendor examples

View the troubleshooting guide for zero.data to see specific examples for supported vendors.