Database Operations Using Java JDBC Driver

Creating a DataSource

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

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

Jave 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:

    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):

    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:

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

    Then, in your code:

    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://dbName?connection-properties

Where dbName is defined as:

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

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

    • Host can be an IP address, a hostnname, a DNS name or (as above) 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.

Please note that:

  • 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 the AP, not of any TE.

High Availability

To support high-availability, 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 host1 (default port 48004), host2 (port 58001) and host3 (port 48104).

"jdbc:com.nuodb://host1,host2:58001,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()) {

    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();

Using Spring’s JdbcTemplate

If you use the Spring Framework, its JdbcTemplate does all this (and more) for you:

@Bean
public DataSource dataSource() {
   return dataSource;
}

...

@Transactional
public void findPlayer1() {
    String id = "aaltoan01";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    Map<String, Object> result = jdbcTemplate.queryForMap
            ("SELECT firstname, lastname FROM Players WHERE playerid=?", id);

    System.out.println("Found: " + result.get("FIRSTNAME")
             + ' ' + result.get("lastname"));
}

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.

The JdbcTemplate is integrated with Spring’s transactional support. If a transaction is 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 it create and manage a connection of its own.

However this agressive 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

This is no longer the preferred approach as it does not support connection pooling:

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

...

DriverManager.registerDriver(new com.nuodb.jdbc.Driver());

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.