Database Operations Using Java JDBC Driver

Creating a NuoDB DataSource

A JDBC data source provides access to connections. NuoDB’s DataSource supports connection pooling for efficiency.

A data source can be created in several ways. In each case, the examples below create a data source for the database named test on the local machine, with username dba, password goalie and setting the default schema to Hockey.

Java provides the base interface javax.sql.DataSource that NuoDB’s DataSource implements. Be careful to import the NuoDB class not the Java interface. The examples below use explicit packages to make this clear.

  1. Purely in code:

    /* Configuring a NuoDB datasource directly */
com.nuodb.jdbc.DataSource dataSource = new com.nuodb.jdbc.DataSource();
dataSource.setUrl("jdbc:com.nuodb://localhost/test");
dataSource.setUser("dba");
dataSource.setPassword("goalie");
dataSource.setDefaultSchema("Hockey");

  2. Using a properties instance (also in code):

    /* Using a Java properties instance */
Properties properties = new Properties();
properties.put(DataSource.PROP_URL, "jdbc:com.nuodb://localhost/test");
properties.put(DataSource.PROP_USER, "dba");
properties.put(DataSource.PROP_PASSWORD, "goalie");
properties.put(DataSource.PROP_SCHEMA, "Hockey");

javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);

  3. Externalize the properties into a file, for example nuodb.properties. Has the advantage that non-developers can change the properties after deployment:

    url=jdbc:com.nuodb://localhost/test
user=dba
password=goalie
defaultSchema=Hockey

    Then, in your code:

    /* Using a Java properties file */
Properties properties = new Properties();
properties.load(new FileInputStream("nuodb.properties"));
javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);

For a list of NuoDB DataSource connection properties refer to DataSource Properties.

To create a connection is standard JDBC:

java.sql.Connection dbConnection = dataSource.getConnection();

NuoDB Connection URL

Connection String

The NuoDB JDBC connection string syntax is:

jdbc:com.nuodb://db-spec?connection-properties

Where db-spec is defined as:

ap-host[:port][,ap-host[:port]].../database_name

  • ap-host is a machine on which a NuoDB Admin Process is running.

    • Host can be an IP address, a hostname, a DNS name or (as in the earlier examples) localhost

    • IPv4 literal addresses are specified as a.b.c.d

    • IPv6 literal addresses are specified as [a:b:…]

  • port is the (optional) port number on which the NuoDB Admin Process is listening.

    • The default port number is 48004.

  • database_name is the name of the database.

  • All connections are via an Admin Process. This allows the AP to determine the TE for the Java client to connect to according to the load-balancing rules in place.

  • The host and port specified are the host and port of an AP, not of any TE.

High Availability

To support high-availability, as shown in db-spec above, you can specify more than one AP in the connection string. If the client is unable to contact the first AP, it will try the next AP in the list, then next and so on to find an AP to talk to. Separate multiple AP specifications with a comma.

For example, three APs on ap-host1 (default port 48004), ap-host2 (port 58001) and ap-host3 (port 48104).

jdbc:com.nuodb://ap-host1,ap-host2:58001,ap-host3:48104/test

These host names and port numbers are just random examples. Your DBA will need to supply you with the hosts and port numbers of the APs in your domain.

Alternatively, if your APs are available via a third-party load-balancer (such as F5), then you only need put the IP-address/DNS name, and port, of the load-balancer in the URL string. Failover between APs will be handled transparently by the load-balancer.

Connection Properties

Property-value pairs may be added to the end of the URL string. For example, an alternative way to specify the default schema is:

jdbc:com.nuodb://localhost/test?schema=Hockey

Multiple properties should be separated by an ampersand (&) character.

Only a small number of connection properties and DataSource properties overlap, schema is the most commonly used example.

See also Specifying Properties on the Connection URL.

Using the Connection

The following is standard JDBC, nothing specific to NuoDB:

try (java.sql.Connection dbConnection = dataSource.getConnection()) {

    /* Find player with ID = aaltoan01 */
    String id = "aaltoan01";

    try (PreparedStatement stmt = dbConnection.prepareStatement
                 ("SELECT firstname, lastname FROM Players WHERE playerid=?")) {
        stmt.setString(1, id);
        ResultSet rs = stmt.executeQuery();

        if (rs.next())
            System.out.println("Found: " + rs.getString(1) + ' ' + rs.getString(2));

     } // End block automatically runs stmt.close() which in turn closes rs

} // End block automatically runs dbConnection.close();

Note the use of the try blocks to ensure the result-set, statement and connection are closed after use. Forgetting to close these resources is a common error using the JDBC API as it is not subject to automatic garbage collection.

Transaction Control

By default, every SQL statement is executed and committed as soon as it is received by a TE. This is known as auto-commit.

To switch to transactional mode, disable auto-commit.

dbConnection.setAutoCommit(false);

To commit persistent work, you should call the commit() method on the Connection object:

dbConnection.commit();

To rollback persistent work, you should call the rollback() method on the Connection object:

dbConnection.rollback();
You must either commit or rollback otherwise your transaction will never end, will hold onto any locks and block other transactions.

Using Spring’s JdbcTemplate

If you use the Spring Framework, its JdbcTemplate does all this (and more) for you. JdbcTemplate can be used standalone, without requiring Spring configuration (Spring Beans) if you wish. For an example, refer to the examples page.

JdbcTemplate allows you to run SQL without having to manage connections, statements or result-sets. All the methods on JdbcTemplate (that access the database) create a connection, use it and close it immediately after use to avoid resource leakage. The template never keeps an unused connection open.

If you also use Spring Configuration (Spring Beans), with or without Spring Boot, JdbcTemplate is integrated with Spring’s transactional support.

  • If a transaction is already in place, it automatically joins that transaction, using the transaction’s underlying connection.

  • Only if there is no transaction running in the current thread does JdbcTemplate create and manage a connection of its own.

However this aggressive opening and closing of connections requires the use of a connection pool to make it efficient - 'opening' a connection borrows it from the pool and 'closing' it returns it back to the pool again.

Using a DriverManager

Described for completeness, but this is no longer the preferred approach as it does not provide built-in connection pooling:

import java.sql.DriverManager;
import java.sql.Connection;

...

/* Register the driver class */
DriverManager.registerDriver(new com.nuodb.jdbc.Driver());

/* Create a connection, specifying URL, username and password */
Connection dbConnection = DriverManager.getConnection(
                "jdbc:com.nuodb://localhost/test?schema=Hockey",  "dba", "goalie");

In the above example, we are connecting to a database named test on the local machine, with username dba, password goalie and setting the default schema to Hockey.